Forum Discussion
Row or Column for performance and usability
Hello,
I have a report already built for which I am trying to look into performance improvement. I have a simple star schema and my fact table is originall in row storage.
| Month | Market | Customer | Department | Center | Measure | Value |
| M01 | DZ | CustA | SS | GGH | Sales | 100 |
| M01 | DZ | CustA | SS | GGH | Returns | 200 |
| M01 | DZ | CustA | SS | GGH | Volumes | 150 |
| M01 | DZ | CustA | SS | GGH | Amounts | 50 |
| M01 | DZ | CustA | SS | GGH | Price | 30 |
I have pivoted the columns to have all measures in columns instead of the above. What is better in terms of performance and usability? I find it counterproductive to calculate measures in such format
Sales = CALCULATE(SUM(TABLE[Value]), TABLE[Measure] = "Sales")
| Month | Market | Customer | Department | Center | Sales | Returns | Volumes | Amounts | Price |
| M01 | DZ | CustA | SS | GGH | 100 | 200 | 150 | 50 | 30 |
Instead in this case I can do
Sales = Sum(TABLE[Value])
Could you please advise what is better and why? Initially my dataset is 5M rows and expected to grow and doing the pivot reduces is to 250K rows but of course with the extra columns.
3 Replies
Anonymous as a best practice you always want to keep your data unpivoted and there are many reasons:
- you can filter on any of the attributes (measure in your case)
- you need only one measure and use the attribute column on column well of the matrix visual, or can be used on legends in other visuals.
- ability for drill through on attribute
- custom tooltip pages will be easy to build
There are may more reasons but the above list is a good starting point.
Anonymous not sure what you mean? The option I talked about is keeping DAX simpler, not more complex.
