topic Re: Efficiently including multiple facts in a model in QlikView

Efficiently including multiple facts in a model

nsm1234567 — Fri, 21 Oct 2016 13:17:58 GMT

Hi There,

I'm hoping someone might have some optimisation ideas for an app I'm working on. I've attached an approximation of the issue I have. I have a very large fact table (2.5 billion rows) called "Summary" that appears in an existing app. I've been requested to add a new dataset called "Planning" to this model (planning only contains three million rows, but has a number of columns which don't appear in "Summary." The grain of the planning table is also different. Summary is per Branch, Product and Week whereas "Planning" is only per Product Category and Week.

My current solution is to concatenate the tables together as illustrated in the attached. The problem I have is that because the "Summary" table is so large, concatenating the "Planning" data is using a huge amount of memory (400 gigs, in case you're interested) even though most of the rows in the added columns are "Null."

I attempted a solution with a link table, but because Branch/Product/Week is the lowest grain in the model I ended up with a link table just as large as the "summary" table which means that calculations on the relatively small "Planning Data" measures are very slow.

So my question: given the respective size of my tables, is the way I'm currently implementing the best balance of calculation speed for the new table as well as efficiency in memory use (I have 1 terabyte of memory available, but obviously it's not ideal to use most of that in one app)?

Re: Efficiently including multiple facts in a model

marcus_sommer — Fri, 21 Oct 2016 13:51:13 GMT

Concatenating the fact-tables is often a good way to get a good performance (significantly better than a linktable model). But in your case I would also try to map the planning data to the Summary table with Week and Product as Key (which could be removed after them - also a joining might be possible but I assume that mapping won't need as much as RAM for it and also be faster).

- Marcus

Re: Efficiently including multiple facts in a model

Anonymous — Fri, 21 Oct 2016 14:04:08 GMT

How many distinct values do you have for each of ?

Branch
Product
Week

And how many distinct values for ?

Branch | Product concatenated pairs

Re: Efficiently including multiple facts in a model

nsm1234567 — Mon, 24 Oct 2016 12:15:43 GMT

Hi Marcus,

Thanks for the response. If I'm understanding you correctly, you're suggesting having the two tables in the data model linked by a "Product/Week" composite key? The issue I had previously was that it was very costly to run things back through the Summary table due to its size. So if I wanted to see a measure from the Planning table per a dimension in the product table I was often getting calculation timeouts even though the Planning table is pretty small relatively speaking.

Re: Efficiently including multiple facts in a model

marcus_sommer — Mon, 24 Oct 2016 12:23:51 GMT

No, I didn't meant to use two associated tables else to merge them but not per concatenating else through a mapping or maybe a joining.

- Marcus