Simple optimization for analysis based on Tabular database (disable totals and subtotals)

When building TARGIT data objects that query a Tabular Database (on-premise or Azure Analysis Services) excluding subtotals and totals can have a great impact on performance.

A crosstable holding multiple dimensions on the vertical axis like this is a perfect example:

mceclip0.png

The query to fetch data for this crosstab, will by default include subtotals on all levels.

Even though a certain salesperson belongs to only 1 department and only has one age - there will still be subtotals also on these levels, which makes the result set in the crosstab include at least 3 times as many rows compared to if subtotals were disabled.

If you can do without subtotals (and totals) it will have a huge impact on performance.

To avoid subtotals and total you need to disable these in the properties section for the data object (crosstab in this case):

mceclip1.png

Then are enabled by default, so you need to click them to disable those.

This will make the query much more "lean" and return the result set much faster.

 

NOTE:

If you have sorted the crosstab and hierarchical collation is enabled, disabling totals and subtotals will have no effect on the query and subtotals will still be included.

mceclip2.png

Hierarchical collation property

 

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.