Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need some advice on joining multiple fact tables to one dimension where each fact table has data in different levels of the dimension. The scenario is explained below:
- I have 3 fact tables (Fact 1, Fact 2, Fact 3) as source
- I have designed my Qlikview model to concat these three fact tables into one fact table
- There is a product dimension with multiple levels (SKU, Product Group, Product Family) and this should be joined with the fact table
- Each fact table has data in different product levels i.e Fact 1 has data in SKU level, Fact 2 in Product Group Level, Fact 3 in Product Family level.
How can I join the dimension with the final fact table (Fact 1 + fact 2 + fact 3 data)? I could not use a key with combination of SKU, Group and Family, since the values of Group Key and Family Key will be empty for Fact 1 data and vice-versa for fact 2.
Let me know your thoughts on this.
The current solution I have thought is as follows:
Create a new Key field in the dimension and fact table. the key field will be SKU for fact 1, Group for Fact 2 and Family for Fact 3. Similarly, I will create these keys in dimension table by concatenating the same data thrice for generating keys for each level. Then I will join the fact and dimension with this new key.
Can somebody advice me if this is an efficient way to resolve this issue or is there a better approach.
Thanks,
Haneesh
Haneesh
What you propose may work, but the new key field will be a mashup of SKUs, groups and families which could cause problems later on with charts and selections (for example if you want to aggregate data by Group or Family).
I suggest that you join the SKU/Group/Family fields to the combined fact table as follows:
This means that when you select a Group, you will get all records from the combined fact table relate to that group and like wise when you select Family.
You could probably also use the hierarchy load function in QV, but that is for n-level hierarchies and I have no experience with its use.
Jonathan
Thanks Jonathan.
I can understand the things to be done in the fact table (adding null SKU, Group and Family for corresponding tables).
What should I do in the dimension table? what should be the key field there? Can you please elaborate.
Thanks,
Haneesh
Haneesh
What I am proposing would put the fields for the SKU dimension in the fact table. rather than a separate fact dimension table.
You can do this with a separate fact dimension table if you want, but I think the load is slightly more complex, and you will need a dummy ID in the dimension table and link the fact and dimension with that dummy (as you cannot link to the null SKUs).
Jonathan
I am not very clear on this 😞
I understood that the fact table will have SKU column (values for fact 1 and Null for fact 2 and fact 3). Similarly the fact will also have Group and Family ID values for corresponding source fact and NULL for the remaining fact source.
In this case, which field should be used to join the dimension table?
can you elaborate?
Thanks,
haneesh
Haneesh
What I am trying to say is that once you have put the SKU, Group and Family fields in the fact table, you dont need the dimension table any more. The attached spreadsheet shows what the fact table would look like (does not include the actual fact fields).
I made two errors on my earlier post, perhaps you read it before I corrected them.
Jonthan
Thanks Jonathan, got the idea.
I have an issue in including all product dimension information into the fact table. The fact table has around 200 million rows (fact 1 + fact 2 + fact 3) and there are so many other attributes in the product dimension (around 12 fields).
I cannot add all the product dimension attributes into the fact table, since it would result in a very big table consuming more space. So, we would need a product dimension table.
Which solution will be most apt for this case? nothing strikes me other than concatenating the dimension table with same data thrice (1. SKu as key, 2. Group as Key, 3. Family as Key) and doing the same in fact table end.
Please note that we are not getting the same fact information from the 3 fact tables. Each gives value for a different measure and I think, we should not get any issue in aggregation as the value for a particular measure will be empty in other fact source.
Can you throw some more lights.
Thanks,
haneesh
Haneesh
You can keep the product dimension separate by having adding a Product ID (which is not the same as the SKU), and linking the facts to the dimension with the product ID. The SKU, Group and Family fields would behave in the same way, but would be in the product dimension table.
The attached spreadsheet shows a sample of the two tables to show how they would be structured.
Jonathan
Thanks Jonathan for your help.
Cheers,
Haneesh