Analyse your memory consumption in PowerBI

Itโ€™s downing a bit slowly on me how awesome the direct retrieval of DMVs in PowerBI and PowerQuery actually is. Just see how easy we can build a cool view of our memory consumption in PowerBI:

Left: Tables – Right: Columns

RAM/Memory Consumption of whole data model

Filter a table:

Filter a table to see its columns only

 

Just copy this code and paste it into the advanced editor: MCodeDMVMemory.txt

DAXers dream: Combine your DMVs on multiple columns ๐Ÿ™‚

Credits go toย Scott, Kasper, Mimoune and Igor

Things to watch for:

  • The query retrieves the name of the current user by referencing C:\Users in the second step (“CurrentUser”). You might have to adjust that folder to your settings.
  • Make sure that you only have one pbix-file open, as only one instance ID will be fetched and you cannot influence which one that will be. So if you just keep one file open, that will be it.
  • You might get a warning on privacy levels. Either set them to public,ย ignore them or adjust the code like Ken Puls hasย described.

If you experience RAM-problems already, make sure you didn’t fell into this trap.

Enjoy & stay queryious ๐Ÿ˜‰

Comments (15) Write a comment

      • Oh, I know this problem with arrivals data. They didnt sum up because one tourist can visit several islands, thats why you should use separate value of “Statewide” to show up correct data on state level, and then also Hilo and Kona is the two sides of one Hawaii Island, so they 1) didn’t sums up to the total of Hawaii Island and 2) are just details. I buried inside all these agregations and granularities and almost get lost there ๐Ÿ™‚

        Reply

        • Yes, pretty asymmetrical aggregations there. Will be interested to see how others solved it. Hope they will be published ๐Ÿ™‚

          Reply

    • Yes, sorry – forgot to mention: The query retrieves the name of the current user by referencing C:\Users in the second step (“CurrentUser”). You might have to adjust that path to your settings.

      Reply

  1. Great blog post and really helpful. One question that I have is that do you have to only have one Power BI Desktop file open when running the above?

    I tried it with one open and it worked perfectly, but if I have 2 Power BI Desktop files open it comes back with incorrect data?

    Reply

    • Thank you! Yes, it should be only one pbix open.
      I believe it will fetch the ID of the file that has been opened last, but am not sure and haven’t analysed it systematically. So the data will probably not be incorrect – just belonging to the wrong model ๐Ÿ™‚

      Reply

      • If you have more than one pbix open, you can add a step in the query that sorts the results in the folder before you select the port ID, thereby choosing the ID of the second most recently opened file, allowing you have your actual report open, then open the memory report and have it analyze the pbix you are building.

        Reply

  2. Pingback: BI-RoundUp โ€“ Power BI (Service Update April & May โ€“ Personal Gateway Update โ€“ How to Purchase Power BI Premium โ€“ Managing Power BI Premium โ€“ Memory Consumption in Power BI File – Power BI โ€“ Exposing M Code) – Gilbert Quevauvill

  3. Pingback: # Excel Super Links #62 โ€“ shared by David Hager | Excel For You

  4. Pingback: BI-RoundUp โ€“ Power BI (Service Update April & May โ€“ Personal Gateway Update โ€“ How to Purchase Power BI Premium โ€“ Managing Power BI Premium โ€“ Memory Consumption in Power BI File – Power BI โ€“ Exposing M Code) – Power BI Business

Leave a Reply