Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Quy_Nguyen
Contributor III

Storing Fact

Dear expert,

I have 1 fact table with many dimension and measures like this: (TYPE 1)

DateDimension 1Dimension 2Dimesion nTOTALTotal ATotal A1Total A1-aTotal A1-bTotal A2Total A2-aTotal BTotal B1Total B1-aTotal B1-bTotal B2Other measures

In detail:

TOTAL = Total A  + Total B

Total A = Total A1 + Total A2

Total A1 = Total A1-a + Total A1-b

It stores data horizontally. If i change to vertical, it will be like this: (TYPE 2).

     

DateDimension 1Dimension 2Dimension nLevel 1Level 2Level 3Level 4ValueOther measures
TOTALTotal ATotal A1Total A1-a
TOTALTotal ATotal A1Total A1-b
TOTALTotal ATotal A2Total A2-a

I want to ask about the storage/performance between 2 types above. Because I am working with larger amount of data. For storing data as TYPE 1, it takes more than 20 millions rows and 2.3 GB on disk per day. If i change it into TYPE 2, does it need more space to store the data? (i didnt try). If you have experience with this, please share with me.

5 Replies
bwisealiahmad
Valued Contributor

Re: Storing Fact

Hi,

These dimension columns. Are they filled with text or IDs pointing to dimension tabels?

I would load them into QlikView and look at the memory info, but from a sheer amount of rows I would think that if you change it to vertical and these columns are filled with dimension info that would take more memory storage space.

Check out Qlik Document Analyzer.

vinieme12
Esteemed Contributor II

Re: Storing Fact

it actually depends largely on the data itself,

things like strings ie long lines of text and also the number of unique values in a field

the results will not be the same for everyone; so go ahead and try it to find what works best for your dataset

Quy_Nguyen
Contributor III

Re: Storing Fact

Dear all,

Thanks for your response.

I tested with the vertical and it nearly double the size of storage (i have nearly 50 measures), it's not good at all.

If i keep using TYPE 1 to store the data, how can i create drill down dimension, e.g. a pie chart that show total A , B -> when i click in A, it shows A1 and A2...

I just think about create a dimension hierarchy in text file and maybe using pick, match to get the corresponding expression.

Any idea?

bwisealiahmad
Valued Contributor

Re: Storing Fact

Quy,

Replace the dimension columns with ID's pointing to tables and that should take down the storage needed to store this significantly seeing as you replace text with numbers and store much less information in the fact table.

This is for the table that is vertical.

You can create ID's using AutoNumber and load distinct from fact table to create the dimension tables needed.

Quy_Nguyen
Contributor III

Re: Storing Fact

Hi Ali,

My daily data is aggregated to the set of dimension that i need. So 20M rows are 20M distinct Keys, maybe when i load monthy data into my app, AutoNumber key is needed.

Thank you.

Community Browser