9 Replies Latest reply: Jan 6, 2015 2:02 PM by neetha P RSS

    Intentional Circular Loops in Data Model (Issue)

    Diwakar Nahata

      Hi All,

       

      I am stuck at a critical data model issue.

      Please refer the attached excel and the qvw where I am attempting the data model.

      The data model has two fact tables Fact1 and Fact2, one relate table Rel which relates the two fact tables on keys F1Key and F2Key respectively, and it has two common Dimension tables Prod and Geo shared by the two fact tables through a linked table.

       

      Now, there are two scenarios we need to address while reporting as below (please refer the attached qvw):

       

      View 1:

      We create a straight table with Desc column from the Relate table, and Vol1 and Vol2 metrics from the respective fact tables.

      Now this should give the volumes associated with the descriptions. This scenario is working `fine, but I had to deliberately create a circular loop so that both the fact tables are connected to the relate table.

      Note: The Relate, Product and Geo columns are expected to work as filters here.

       

      View 2:

      We create a straight table with PDesc column from the Product table, and Vol1 and Vol2 metrics from the respective fact tables.

      Now this should give the volumes associated with the product descriptions. This scenario is not working fine for Vol2 metrics where the volume is coming as a result of association with the Rel table instead of the Product volume.This would have worked fine if there was no Rel table or if the circular loop was removed.

      Note: The Relate, Product and Geo columns are expected to work as filters here.

       

      Let me know what could be the best solution for the above two scenarios.

       

      Thanks,

      Diwakar

        • Re: Intentional Circular Loops in Data Model (Issue)
          Marcus Sommer

          Without looking in your data and app - avoid circular loops! Most often it's the best (and easiest way) to concatenate the fact-tables to a single fact-table.

           

          http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

          http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

           

          - Marcus

            • Re: Intentional Circular Loops in Data Model (Issue)
              Diwakar Nahata

              Hi Marcus,

               

              I agree to what you are saying. But, I cannot concatenate the fact tables as they are associated to each other from a relate table, and we show desc from the relate table against the Volume from the two fact tables.

              Concatenating will give null output.

               

              I  tried another approach, to split the Prod and Geo hierarchies for the two fact tables (shown below), but this gives unexpected results in some scenarios, especially when we are showing Prod/Geo columns in the straight table. For e.g. if we want to have Product desc as a dimension, then i have to chose that columns from Prod1 or Prod2 and also the volume will come via the relate table association, which is an issue in this case.

               

              I had thought of another approach (shown below) where i had created a copy of Fact2.

              The idea was to use the Vol2 metric from Fact2 Copy if the dimension in the straight tables is coming from the relate table, and if the dimension is coming from the prod/geo table then the Vol2 from the original Fact2 table could be used. But even this approach has a limitation that the Product filters will not apply to Fact2 Copy table.

              ''

              Let me know if any more info is required.

              Any suggestions here will be really helpful.

               

              Regards,

              Diwakar

            • Re: Intentional Circular Loops in Data Model (Issue)
              neetha P

              Hi,

               

              Try to concatenate fact tables:

               


              Rel:
              LOAD SubField([F1Key,F2Key,DESC],',',+1) as F1Key,
              SubField([F1Key,F2Key,DESC],',',+2) as F2Key,
              SubField([F1Key,F2Key,DESC],',',-1) as DESC
              FROM
              [C:\qlikview\QlikViewDataModelScenario.xlsx]
              (
              ooxml, embedded labels, header is 1 lines, table is Data);

              Fact1:
              LOAD SubField([PKey,GKey,F1Key,Vol1],',',+1) as PKey,
              SubField([PKey,GKey,F1Key,Vol1],',',+2) as GKey,
              SubField([PKey,GKey,F1Key,Vol1],',',+3) as F1Key,
              SubField([PKey,GKey,F1Key,Vol1],',',-1) as Vol1,
              1
              as Fact1
              FROM
              [C:\qlikview\QlikViewDataModelScenario.xlsx]
              (
              ooxml, embedded labels, header is 1 lines, table is Data);

              Concatenate
              Fact2:
              LOAD SubField([PKey,GKey,F2Key,Vol2],',',+1) as PKey,
              SubField([PKey,GKey,F2Key,Vol2],',',+2) as GKey,
              SubField([PKey,GKey,F2Key,Vol2],',',+3) as F2Key,
              SubField([PKey,GKey,F2Key,Vol2],',',-1) as Vol2,
              1
              as Fact2
              FROM
              [C:\qlikview\QlikViewDataModelScenario.xlsx]
              (
              ooxml, embedded labels, header is 1 lines, table is Data);

              Geo:
              LOAD SubField([GKey,GDesc],',',+1) as GKey,
              SubField([GKey,GDesc],',',-1) as GDesc
              FROM
              [C:\qlikview\QlikViewDataModelScenario.xlsx]
              (
              ooxml, embedded labels, header is 1 lines, table is Data);

              Product:
              LOAD SubField([PKey,PDesc],',',+1) as PKey,
              SubField([PKey,PDesc],',',-1) as PDesc
              FROM
              [C:\qlikview\QlikViewDataModelScenario.xlsx]
              (
              ooxml, embedded labels, header is 1 lines, table is Data);

               

              Datamodel.png

               

              if you have two rel tables concatenate them and create composite key for F1key and F2key columns with autonumber function,it will save lot of space

               

              Regards

              Neetha