8 Replies Latest reply: Oct 6, 2010 5:34 AM by haneesh iqbal noormohammed RSS

    Fact dimension join in multiple levels

    haneesh iqbal noormohammed

      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

        • Fact dimension join in multiple levels
          Jonathan Dienst

          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

           

            • Fact dimension join in multiple levels
              haneesh iqbal noormohammed

              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

                • Fact dimension join in multiple levels
                  Jonathan Dienst

                  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

                    • Fact dimension join in multiple levels
                      haneesh iqbal noormohammed

                      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

                        • Fact dimension join in multiple levels
                          Jonathan Dienst

                          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

                            • Fact dimension join in multiple levels
                              haneesh iqbal noormohammed

                              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