I was reading through the blog post Announcing on-demand loading capabilities for large models in Power BI and I got a thought would it not be great to better understand which columns and tables are being used in my Power BI Premium/Premium per user datasets?

To do this, using the new DMV I could now look at the temperature of the tables-column.

The higher the temperature the more the table-column is being used in my reports!

Before I show you how I did this I would like to ensure that you as the reader who it works and what to look out for.

• This ONLY applies to Power BI Premium and Premium Per User where large dataset mode is enabled.
• Temperature:
  • As far as I understand the Temperature is calculated by how often a visual or query is used in a report. The more it is used in the report, the higher the number which means a higher temperature.
  • I like to think of it as the hotter it is the more the column is being used!
• To ensure that all columns can be used to calculate temperature, I had to ensure that I had large dataset mode enabled. And then I HAD to re-process/refresh the tables for the Temperature to be enabled.
• Columns which have a low temperature could be evicted from memory because they are not being used.

How I created my report

Below are the steps which I did to create my report for me to understand which columns are being used the most.

• I connected to my PPU dataset and put in the following DMV query below
• CODE:

  • Select * from SYSTEMRESTRICTSCHEMA ($System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS, [DATABASE_NAME] = 'Sales Dataset')

• Which you can also see below
  • 
• I then did some basic Power Query steps where I removed some columns, renamed some columns, and filtered out the data to only show it for the temperature.
  • The one thing I did was to combine the Table – Column Name into a single column.
  • This allowed me to then understand which table and column has the highest temperature.
• I then loaded this data to my dataset.
• Finally, I then created my Power BI report as shown below.
• 

What does the Temperature mean in the report?

The higher the temperature the more the column is being used in the report as I alluded to before.

If I had to use the example above, the columns “Sales-language key” and “Sales-DaysPastLaunch” would be potentially evicted from the dataset.

Summary

What I have shown in this blog post is how to find and understand the temperature in your Power BI Premium datasets.

This allowed me to see which tables and columns are actively being used by report visuals and queries. This gives me some great insights into how the datasets are being used.

Here is a link to the PBIT file: On-Demand Loading – Temperature.pbit

You will need to put in the Premium Workspace Name and the dataset name.

Thanks for reading, any comments and suggestions are most welcome!