Power BI Desktop - Memory Usage - Excelerator BI

Power BI Desktop – Memory Usage

Some time ago I developed a tool that allows you to automatically connect a local Excel Pivot Table to a local running instance of Power BI Desktop.   You can read that original article here if you like.  It works kind of like Analyze in Excel but using Power BI Desktop running on your local PC instead of PowerBI.com. 

I recently updated the template I created back then so that it now also provides you with

  1. the memory usage of your Power BI Desktop data model, and
  2. the list of measures from your Power BI Desktop Data Model

This article explains how to install the tool and what it does.

First download the template

You can download the Excel template file here.

After downloading the Excel template, you should place the template in the location of your default personal templates.  You may or may not have this location set (it is not set by default).  To check, first go into Excel Options

File\Options\Save

and then make sure you have a location set (see mine below).  You can set your location to what ever you like (somewhere in your documents folder makes the most sense.

image

Then simply place the Excel template you downloaded in this same folder as you set in the options above (make sure you unzip the file too).

Instructions for Usage

First open an instance of Power BI Desktop on your PC.  There must be 1 and only 1 instance running on your PC for this to work.

image

Next you need to create a new Excel Workbook using the template you installed above.

image

The first time you use it you may need to “Enable Content” as shown in 1 below.

image

Then click “Refresh SSAS Connection” as shown in 2 above on the first tab of the workbook.

Because of the way I extract the data using Power Query, you will get a dialog like shown below.  Just click Run to execute the query.

image

And then click Connect as shown below when asked about the credentials.

image

You will now have a Pivot Table that is directly connected to your instance of Power BI Desktop running on your PC (see my example in 1 below).  This feature worked in my earlier version of the Local Host workbook, but now there are 2 new tabs – Memory Usage (see 2 below) and Measures (3 below).

image

Memory Usage

The memory usage tab will give you a pivot table similar in approach and experience as has been developed before by Kasper de Jonge, Scott Senkeresty, Bertrand d’Arbonneau among others.

image

You can just expand and collapse the pivot table to see what columns in your data model are taking up the most space.

Measures

If you want to access a list of measures, simply switch to the Measures tab, then right click in the table (shown as 1) and then select Refresh (shown as 2 below).

image

There will be another native query that you will have to run – just click the button.

image

After the query is executed, you will have a list of all the measures in your Power BI Desktop file (see my example shown below).

image

And if you have Bertrand’s Power Pivot Utilities, you can even use the DAX Formatter button in that addin to format your measures as shown below.

Video_2017-04-01_134945

Good luck with this tool – I hope you find it useful.

avatar
13 Comment threads
21 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
  Subscribe  
Notify of
Chad Nixon
Guest
Chad Nixon

downloaded and attempted to run against the store app. I am getting the 1004 error “Memory_Usage” (step ‘AutoRemovedColumns1’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

After clicking debug – the failure is on Sheets(“Memory Usage”).PivotTables(“PivotTable1”).PivotCache.Refresh in Module 2 RefreshSSaSConnection Sub.

Anyone else seen this problem?

Jay
Guest
Jay

Is there any chance you can add a ‘Column’ worksheet that mirrors the ‘Measures’ worksheet, but displays only the Calculated Columns DAX Expressions?

Govind reddy
Guest

Failed to save modifications to the server. error returned: ‘there’s not enough memory to complete this operation. please try again later when there may be more memory available

Jan Verdickt
Guest
Jan Verdickt

FYI, since August PowerBI Desktop update I always get prompted with the “Max 1 Instance of PowerBI open” error when using the “Refresh SSAS” button. Downgrade to June PowerBI (didn’t test July) solved the issue so probably a new configuration in PowerBI. Would be great if you could have a look at this Matt!

Rui
Guest
Rui

Thanks for the tool.

I’m getting this error:
Run-time error 1004

and the debug points to this:
Sheets(“Memory Usage”).PivotTables(“PivotTable1”).PivotCache.Refresh

Any help?

I can see some fields and queries on the right. Maybe too many tables?

Thanks

Jørgen
Guest

@Matt, Lesley and Chris
Changing the settings for the query as suggested by Matt solved the exact same problem, that Lesley and Chris describes for me.

But then I experienced another problem. My Pivot tables was converted to values, while my cube values is still working great. Anybody, that has experienced the same?

Any solutions for that?

Rob
Guest
Rob

Need Help. After complaining that I’m supposed to only have 1 instance open (which is the case so I don’t understand why it thinks I have more than 1 open) I’m getting the following errors in the Memory_Usage query and the Measures query:
DataSource.Error: AnalysisServices: The connection string is not valid

Chris
Guest
Chris

Hi Matt

I couldn’t see how this applied to the problem with the Memory_Usage query, it doesn’t seem to have anywhere I could “strip” an external query. After the db and server steps, I get the error on this code, which you are no doubt familiar with?

let
db = DB_Name,
server = “localhost:”&PortNumber,
Source = AnalysisServices.Database(server, db, [Query=”SELECT dimension_name as TableName, attribute_name as ColumnName, DataType, (dictionary_size/1024) AS Size_KB FROM $system.DISCOVER_STORAGE_TABLE_COLUMNS #(lf) WHERE dictionary_size > 0 “])
in
Source

Chris
Guest
Chris

Thanks for this Matt, it is a great use of query and understanding! I haven’t been able to get the Memory Usage and Measures to run, first of all I got a run-time error 1004 “Exception has been thrown by the target of an invocation” on this line in the RefreshSSASConnection macro: Sheets(“Memory Usage”).PivotTables(“PivotTable1”).PivotCache.Refresh If I try to refresh the Measures I get an exception thrown by Query: Query ‘Measures’ (step ‘AutoRemovedColumns1’) references other queries or steps, so it may not directly a data source. Please rebuild this data combination. If I look into the queries editor I find warning… Read more »

Leslie Guelcher
Guest
Leslie Guelcher

Matt,

I’m experiencing the same issue as Chris. I’m getting VBA error: Run-time error ‘1004’: Query ‘Memory_Usage’ (step ‘AutoRemovedColumns1’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Any help would be greatly appreciated.

Thanks
Leslie

Ramana Varanasi
Member
Ramana Varanasi

Useful one, Matt!
Thanks

Adrian Smethurst
Guest
Adrian Smethurst

Hi Matt, just ‘getting into’ all that Power Pivot & Power BI can do for our Company. I have Windows 10 64Bit laptop, but running with 32 Bit Excel 2016 – would the X64 version of Power BI work work with the Excel template or shou;d I use 32 bit for both?

Adrian Smethurst
Guest
Adrian Smethurst

Answering my own questions – the first dign of madness??
https://exceleratorbi.com.au/64-bit-power-bi-desktop-32-bit-office-can-do/

Maxim Zelensky
Guest

Nice, thanks, Matt!

harsha547
Guest
harsha547

Thank You !!

Can I use this tool to extract all the measures from SSAS rather than PowerBI.

If not, is there any option to extract all the measures and dimensions to excel from SSAS.

I am talking about only metadata of the cube..

Scroll to Top