Data Model Best Practice to Prevent Loops

rileymd88 — Fri, 05 Aug 2016 08:38:57 GMT

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);

Re: Data Model Best Practice to Prevent Loops

Mark_Little — Fri, 05 Aug 2016 09:06:42 GMT

how about this?

Mark

Re: Data Model Best Practice to Prevent Loops

rileymd88 — Fri, 05 Aug 2016 10:03:41 GMT

Hi Mark,

Thanks for your help. This works, however it is similar to what I have implemented so far (but a bit more cleaned up by creating one key). Is this the only way I can get my model to work? As I mentioned above I would like to stay away from having to use ApplyMap to populate the productDimension data as the relationships have been known to change frequently (which means I will always have to ensure I reload a lot of history to ensure the key is correct).

Of course this could be the only way due to the tricky scenario I have, but I just wanted to check this before building the document further.

Re: Data Model Best Practice to Prevent Loops

Mark_Little — Fri, 05 Aug 2016 11:19:03 GMT

Hi,

Another option would looking to joining your product and target to you fact table.

So load the sales fact first, then join the Product table and finally the target.

Mark

topic Re: Data Model Best Practice to Prevent Loops in QlikView

Data Model Best Practice to Prevent Loops

Re: Data Model Best Practice to Prevent Loops

Re: Data Model Best Practice to Prevent Loops

Re: Data Model Best Practice to Prevent Loops