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!
Have fun!
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.
Kasper, nice blog and started directly with it, but getting an error :
Formula.Firewall: Query ‘DatabaseSize’ (step ‘Invoked Custom Function’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. The 3th step in de databasesize query. I did enter a servername and one SSAS databases. Any idea?
Hi Erwin,
This is the infamous PowerQuery firewall. You can fix this by going to options, Privacy, Always ignore Privacy Level settings.
That should do it.
Kasper
Thanks Kasper, Report is working now. Have a nice weekend
Thanks!
Does this work only on 1200 models?
Or will this work with 1103 and 1100 models too?
I noticed that the “Databases” query uses the below code:
select * from $system.DBSCHEMA_CATALOGS
where compatibility_level >= ‘1200’
I tried manually changing that 1200 to 1103, but it still failed because compatibility_level column doesn’t seem to exist in 1103. I tried removing the where clause and at least it didn’t error on that part, but errored later with:
Expression.Error: The column ‘DATE_QUERIED’ of the table wasn’t found.
Details:
DATE_QUERIED
this only works on 1200 models as mentioned in the beginning of the post. The DMV’s before that were very different.
Is there a way to see or get users using the cube(s)?
xEvents can do this, but in Azure AS you will face challenges saving the data off that stream.
This is amazing! Thanks for sharing.
Hello Kasper,
Im trying to connect to the server but i get an error saying the server parameter must have a valid host name or it must be a URL HTTP address.
But creating a new file and connecting to the server works…
Hi,
I keep getting “Native Database Query” warning pop-up! am I the only one? is there’s a way to suppress that or I’m missing something? thanks
unfortunately not 🙁
This is very cool, thanks a lot =)