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:

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):

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.

Hierarchical collation property