New SSAS memory usage report using Power BI

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 Smile.

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).

image

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:

  1. “Databases”. This table contains all the databases on the server that are compatibility level 1200 or higher (to future proof it)
  2. “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:

  1. Databases on server by Record Count, which shows all the databases on the server by recordcount
    image
  2. And then a similar one that show Databases by size in MB
    image
  3. 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.
    image
    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
    image
  4. In the next report you can look at the top 10 partitions for either the entire server or per database:
    image
  5. 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
    image

 

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.

  • Erwin de Kreuk

    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

      • Erwin de Kreuk

        Thanks Kasper, Report is working now. Have a nice weekend

  • John G Hohengarten

    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.

  • Mike Kostuch

    Is there a way to see or get users using the cube(s)?

  • Laura Johnson-Morris

    This is amazing! Thanks for sharing.