The effect of Summarize by Default on a Tabular Model


Another one of my experiences recently was this strange looking query generated off my Power BI reports. These reports were running off live a live connection to a tabular model on Analysis Services. And of course these queries at up my CPU, leaving it at 100% for quite a long while, with the reports not responding. One thing about the reports were that they had quite a number of parameters (which of course were a client requirement which we couldn’t do much about).

Here’s just a tip of the ice berg sample of the huge query that is generated:

Big Query

The data model was quite a large one with 30+ measures, and 10+ dimensions, and one thing that we had missed out was that all numeric fields that were not used as measures such as the Year, MonthNumber and WeekNumber fields of the Calendar dimension, and EmployeeAge field of the Employee dimension had their Summarize By property set to Default. But this was not indicated in any way in Power BI especially in the Fields section, where usually fields that are summarized show up with a Σ to indicate that the field is summarized when the data model is a Power BI data model.

The phenomenon is explained in detail in Pinal’s SQLAuthority. Make sure you take a look. Happy New Year!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s