Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How could one record in a fact table be linked to multiple dimension values? E.g. if the same sales record is associated with two countries, is it possible to create a fact table without duplicating that record and link it to country via a dimension table?
Input:
ID | Country | Sales |
1 | UK | 100 |
1 | Germany | 100 |
2 | US | 200 |
3 | Germany | 300 |
Output:
fact:
ID | Country key | Sales |
1 | 100 | |
2 | 200 | |
3 | 300 |
dimension:
Country Key | Country |
UK | |
US | |
Germany |
Thank you,
Laura
Absolutely. Each Qlik "entity" remains independent of all of the others and is simply associated. You will have 1 "row" per fact and 1 "row" per dimensional value.
HOWEVER, if the purpose of your trying to do that is to try and remove redundancy like Relational Databases because you will have 50 million fact records, take heart ... you don't need to do that.
Qlik is a Columnar Data Store. So your fact "table" is actually just a group of pointers to a table for ID that stores the unique values, and a table of countries that stores the unique values and a table of Sales that store the unique values.
The reason for DIMENSION tables for Qlik would be if you had several other values for Countries from a source table that may not have sales. And you wanted to see a total sales by COUNTRIES including those that would show NO VALUE to know that they had no sales. Or if you had other fields for each country that you also wanted to access for some reason.
What I have seen in real world use cases is up to 95% compression which is why Qlik can hold so much data in memory and performs so incredibly.
See this post from the guru of Qlik Storage
https://community.qlik.com/t5/Qlik-Design-Blog/Symbol-Tables-and-Bit-Stuffed-Pointers/ba-p/1475369