Qlikview Data Model: Row or column based expressions?

[rileymd88]

Hi All,

I am in the process of creating a new data model for a new project and was wondering what the pros and cons would be of using rows or columns for my expressions in my main table. Here is a bit of background info about my project:

• I am merging together 2-3 sources of data into one large table which will hold key dimension information like the product and day and key expression information like stock, sales and forecasts
• I will then set up separate master data tables which gives me more information about the products/calendar
• The majority of data will be coming in on table 1 format ~85%
• I expect to have about 50 million records per year based on a column based expressions table with 40 columns

Table1 file structure:

Data Measure	Product	Day	Quantity
Stock	Product 1	21.03.2016	102
Sales	Product 1	21.03.2016	47
Forecast	Product 1	21.03.2016	50

Table2 file structure:

Product	Day	Stock	Sales	Forecast
Product 1	21.03.2016	102	47	50

What are the pros/cons of having my main table set up like Table1 versus Table2?

Thanks,

R

[jonathandienst · Accepted solution]

The second structure is a more conventional fact table design and will usually be quicker and easier in terms of the front end expressions. The generic structure of the first table tends to make the front end expressions more complex (as well as  possibly leading to poor performance in a larger data model).

The Generic Load feature in QV will simplify the conversion from the generic form to a flatter fact table form. Search this site for "Generic Load" for more guidance.

[Gysbert_Wassenaar]

The advantage of the second structure is that you will likely have a smaller document since you will have three fields with probably lower cardinality then if all the quantity values are in one field. And it's easier to do calculations that involve values of the 'same' record, i.e. Stock - Sales for a product over a period. The advantage of the first is that most of your data has that structure.

[jonathandienst]

Henric Cronstrom's explanation of generic loading is very helpful:

The Generic Load

[Digvijay_Singh]

Your second structure appears more connected/familair to dimensional modelling, The facts are expressed against the Product and Day dimensions. The measures can be easily aggregated against dimensions through required expressions with no structural change as you would need in first case using generic load.