Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to correctly model Imbricated Dimensions with Fact Table Metrics of Varying Granularity


I am debugging a data model that has 3 imbricated "location" dimensions (left side of diagram):


1) Continent (eg SOUTH AMERICA) that contains
2) OperatingCountryCompany (eg BRAZIL/ this corresponds to a database) that manages
3) CountryCompany, eg OperatingCountryCompany BRAZIL manages CountryCompanies BRAZIL, URUGUAY and PARAGUAY.

Imbricated Dimensions.jpg

Only the lowest granularity, CountryCompany, connects to the other tables. Continent is just a grouping value.

I JOINED Continent and OperatingCountryCompany into just CountryCompany (not in the diagram).


I notice that the Calendar table similarly contains 3 imbricated levels of granularity, day, month and year and also connects to other through its lowest granularity field, Date.


The fact table is a concatenation of Order(Header), Products(OrderDetail) and Client History monthly metrics.

(The common date field was constituted artificially by “MakeDate(Year, Month) as Date”, BTW, since we have no day in these metrics, only months).


My problem is that Client History is not defined by CountryCompany, only by OperatingCountryCompany.


How should I change the data model so it works correctly with Client History as well as Product and Order?


For example, should I have a CountryCompany AND an OperatingCountryCompany dimension and put OperatingCountryCompany into the fact table (but it looks like this would create a cycle and/or synthetic key)?


Or should I duplicate the Calendar strategy and substitute the missing CountryCompany part of the key with some generic value, perhaps just blank ?  But would that still work with User Queries (There is a multibox to choose Continent, OperatingCountryCompany and/or CountryCompany )?


The Client Dimension does have the CURRENT CountryCompany of each Client so it could be used to create Client History Metrics by CountryCompany but this is not very desirable since one very large client that changes OperatingCountryCompany/CountryCompany changes metrics retroactively dramatically.


How would you model the Client History Metrics and Location dimension tables in this example?


BTW, the data model has concatenated natural keys instead of UIDs, eg BRA-URY is an OperatingCountryCompany-CountryCompany key. Should I just replace these with UIDs (almost a rhetorical question but not really) or can it still work this way?


Also I am thinking that the year and month fields are redundant in the fact table since the date field should be adequate. Am I right?


Thanks

0 Replies