Hi all,
I wanted to seek some advice on what are the best practices when it comes to data modelling for Fact tables with same primary keys but are split into different tables based on their business context.
For example, I have the following tables in my SQL database:
Dimension tables:
- Geography
- City (PK)
- Country
- Continent
- ...
- Time
Fact tables:
- Population
- Timestamp (PK)
- City (PK)
- Population Size, etc
- Climate/Weather
- Timestamp (PK)
- City (PK)
- etc, etc
If I adhere to this data model in Qlik I will have the issue of circular references. Some ways to overcome this is to create a "Master" dimension table, which can be formed via 1) Cross Join "Geography" and "Time" table 2) Select all distinct combinations of "Timestamp" and "City" from the Fact tables to form the new dimension table.
However, while these 2 approaches works in terms of functionality, it seems really cumbersome and not efficient, especially if the number of dimensions increase, or if measures are based off different hierarchies (e.g. Population Table's Primary Keys are based on "Timestamp" and "Country", while that of Climate is based on "Day" and "City").
Are there any best practices to handle such situations?
Thanks!
