I have created several version of the SSAS memory usage report here and here. But with the new 1200 compatibility level these memory reports stopped working due to the DMV’s not working anymore for these databases. So I decided it would be time to create a new one for all models with compatibility 1200 and up (so not for anything smaller then 1200), this time of course using Power BI. Here I can also use Power Query to do some additional things I couldn’t do before .
So let’s look at the reports and data available we have available, in a follow up post we’ll look at how I build it.
To start you can download the Power BI Template here on GitHub (more on GitHub later).
Double clicking the file gives you a prompt where you can enter your server name and a database name (pick any, unfortunately PQ mandates us to connect to AS with a database even though it is not needed).
This will start loading the report with the data from your server (you do have to be admin on the server to run this report).
In the report we will have two tables:
- “Databases”. This table contains all the databases on the server that are compatibility level 1200 or higher (to future proof it)
- “DatabaseSize”. This table contains the low level detail with databasename, partition, column, rowcount, size in memory and much interesting stuff more.
Using that I created a few reports:
- Databases on server by Record Count, which shows all the databases on the server by recordcount
- And then a similar one that show Databases by size in MB
- Then one that show Tables and partitions with the potential to select a database so you can filter the results down to a single database.
Here you can see the report filtered by a single database.
The treemap show the tables and within them the partitions so you can quickly identify either one. When you hover over the partition you get more information like size, recordcount and the size a single row takes
- In the next report you can look at the top 10 partitions for either the entire server or per database:
- In the last report you will be able to look at the most “expensive” column based on the memory it uses per row of data
That’s it. Lot’s of interesting information already but there is more hidden in the model that I haven’t explored yet.
As mentioned before I put this Power BI desktop template on GitHub. Anyone can download it there or even better make updates to it with nifty new things they discovered!
PS If you get the “Formula.Firewall” error you are hitting the infamous PowerQuery firewall. You can fix this by going to options, Privacy, Always ignore Privacy Level settings. PowerQuery thinks I am doing query injection and hacking your system.