Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
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.
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.
Henric Cronstrom's explanation of generic loading is very helpful:
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.