Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fact dimension join in multiple levels

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

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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:

  • for Fact1 add the SKU, Group and Family.
  • for Fact2 add null for the SKU, then Group and Family
  • for Fact3 add null for the SKU, null for the Group and then Family


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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks Jonathan for your help.

Cheers,

Haneesh