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