3 Replies Latest reply: Aug 5, 2016 7:19 AM by Mark Little RSS

    Data Model Best Practice to Prevent Loops

    Riley MacDonald

      Hi,

       

      I am having difficulties setting up a data model as I was continuously getting loop errors. I have 4 different tables which I am loading at the moment which you can see below.

       

      The goal of this application is to be able to see the sales per product ID (Quantity field from salesFact) and also compare these sales against the sales target (Target Quantity from salesTarget).


      The only way I have got this to work so far is creating a mapping load and mapping the Product Group 1 and Product Group 2 into the salesFact table, however this does not feel like the best approach as the Product ID -> Product Group 1 & 2 relationships are known to change so this means I will often have to continuously map historical data which would not lead to QVD optimized loads.


      Does anyone have a better approach for my data model? I have attached a sample excel file and a test QVD document.

       

      salesFact:
      Month
      Country
      Product ID
      Quantity

       

      productDimensions:
      Product ID
      Product Family
      Product Group 1
      Product Group 2
      Product Supplier

       

       

      calendarDimensions:
      Month
      Quarter
      Year

       

       

      salesTarget
      Month
      Country
      Product Group 1
      Product Group 2
      Target Quantity

       

      My Script so far:

       

      // Mapping tables for Product Groups

      productGroupOneMap:

      Mapping LOAD [Product ID],

           [Product Group 1]

      FROM

      [QlikDMTest.xlsx]

      (ooxml, embedded labels, table is productDimensions);

       

      productGroupTwoMap:

      Mapping LOAD [Product ID],

           [Product Group 2]

      FROM

      [QlikDMTest.xlsx]

      (ooxml, embedded labels, table is productDimensions);

       

      calendarDimensions:

      LOAD Month,

           Quarter,

           Year

      FROM

      [QlikDMTest.xlsx]

      (ooxml, embedded labels, table is calendarDimensions);

       

      productDimensions:

      LOAD [Product ID],

           [Product Family],

           [Product Group 1],

           [Product Group 2],

           [Product Supplier]

      FROM

      [QlikDMTest.xlsx]

      (ooxml, embedded labels, table is productDimensions);

       

      salesFact:

      LOAD Month,

           Country,

           [Product ID],

           ApplyMap('productGroupOneMap', [Product ID], null()) as [Product Group 1],

           ApplyMap('productGroupOneMap', [Product ID], null()) as [Product Group 2],

           Quantity

      FROM

      [QlikDMTest.xlsx]

      (ooxml, embedded labels, table is salesFact);

       

      salesTarget:

      LOAD Month,

           Country,

           [Product Group 1],

           [Product Group 2],

           [Target Quantity]

      FROM

      [QlikDMTest.xlsx]

      (ooxml, embedded labels, table is salesTarget);