Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tommy_yong
Partner
Partner

Data Modelling: Best Practices for Handling Tables with Same Primary Keys but Different Business Context

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
    • Timestamp (PK)
    • Day
    • Month
    • Year
    • ...

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!

1 Reply
dilipranjith
Partner
Partner

2 ways of handling multiple fact tables

- Concatenate fact tables - Fact Table with Mixed Granularity

- Generic keys Generic keys

     - this is not recommended if data size is big