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:

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!

Report and Dashboard Usage Metrics in Power BI

One thing we tend to overlook as technical folk working in the business intelligence arena is the importance of analytics about analytics. However, to business folk, it is quite important that they know how valuable their reports and dashboards are to others, if not what’s the purpose in them doing what they do if nothing is being used. The same applies to reports and dashboards in Power BI.

Power BI offers something known as Usage Metrics for each report when you access reports or dashboards off the Power BI portal. You can find it above the top left of each report, which when clicked on gives you a summary of how your report or dashboard has been used.

It includes quite a few useful insights as to the main report’s usage. However, as humans can never be satisfied, the business user naturally will ask for more analysis on top of what they see on the summary. For instance, “I need to see at-a-glance which user looks at which page of the report more often”. That’s when you wished you had a way of providing users with customization options of report usage metrics. But there is actually a way.

Each app workspace gets its own report usage metrics data set, it’s just that you don’t see it when you are in the portal. In order to access it (at least for now) you need to use Power BI Desktop. When you open Power BI Desktop, you need to sign-in with the appropriate login, and then choose Power BI service from Get Data menu item. You then get listed with a set of app workspaces; within each you would find a list of all the datasets that were every published to each of the workspaces. Additionally, Power BI will also give you two more datasets: Report Usage Metrics Model and Dashboard Usage Metrics Model. However, these data models will only show up if you had attempted to view usage metrics at least once on one of the reports of the app workspace. The moment you click on the Usage Metrics link and the following happens; that is when the model is created:

And now, it’s your playground, a whole new data model that gives you analytics on the produce of another data model.