Wide or Deep Fact Table?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have quite a few lines of about 8 Mio data records in my fact table. Currently I have about 51 fields in the fact table whereas 8 of those are dimensional keys. So I have around 40 KPIs (some fields in the fact table are additional filters, like toggles).

I now need to extend my data model and have the following principal question where I could not find a definite answer yet when reading some of the performance tuning blogs, tips, etc.

Imagine all of my KPIs are distinguished by a currency type (group currency vs. local currency). Now, I have build this into different KPIs like 'Sales GC' and 'Sales LC'. When accessing the KPIs in the dashboard I dynamically build the KPIs to be calculated using a variable. So if GC or LC is toggled via a button the KPI name gets dynamically created like

Sum(Sales $(vL.Currencytype)), vL.Currencytype gets filled using a trigger on a button with either LC or GC.

It all works fine, but I am a bit concerned if for instance I have to add additional 10 KPIs which would lead into additional 20 columns of my fact table.

The key question for me is: What impacts QV performance more:

Option 1) doubling the number or rows in a fact table by having a field with the value LC or GC and using the field within Set Analysis statements as additional filter?

Option 2) having double number of columns in the fact table? And even more interesting is if there is a performance impact when reaching a certain number of columns (e.g. >100).

I assume there should be no (or only less) difference on the file size and memory as the amount of data (facts) is the same on both options. I guess it would be interesting to learn how selects are performed and how tables are scanned or accessed within a calculation.

I mostly use set analysis and would also have one more field like the currency type. If I would put both fields into the rows I would increase the number of rows by factor of approx. 4 but would have some 25 columns less

Thanks for sharing any experiences.

Best regards

Marcel

Re: Wide or Deep Fact Table?

marcus_sommer — Mon, 06 Mar 2017 19:45:57 GMT

I think you will need to try it practically which way worked best related to your biggest bottleneck (probably the UI performance). I hope this is useful for you: Re: Data Model Question - Fat or Thin Fact table?

- Marcus

topic Re: Wide or Deep Fact Table? in QlikView

Wide or Deep Fact Table?

Re: Wide or Deep Fact Table?