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
- the memory usage of your Power BI Desktop data model, and
- 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.
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.
Next you need to create a new Excel Workbook using the template you installed above.
The first time you use it you may need to “Enable Content” as shown in 1 below.
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.
And then click Connect as shown below when asked about the credentials.
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).
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.
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).
There will be another native query that you will have to run – just click the button.
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).
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.
Good luck with this tool – I hope you find it useful.
This looks amazing and I’m really keen to use it. However, when I click the Refresh SSAS Connection button, I get the following error:
Run-time error ‘1004’:
[DataFormat.Error] The file name
‘C:\Users\Username\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace1989’
is longer than the system-defined maximum length.
Any help would be much appreciated!
Don’t worry, solved it! I had another model saved in AnalysisServicesWorkspaces, which is strange because I haven’t used that model in years! Deleted it and now it works. Amazing!
mmmm, I don’t like the look of that error. I don’t know if it is a bug, or something else. If you contact me via the contact form on my site, I can find some time tomorrow to take a look for you.
For future versions, extra bonus points if you find a way to extract the Display folder too? To vulgarise our data models & create ‘cheatsheets’ for less Advanced users, this will clearly help !
Thanks again
What do you mean “display folder”?
Additional question on this great tool:
how can i delete the identified tables like LocalDateTable_393620f4-2328-4472-9134-18980caa86d1 that are eating a lot of memory?
Thanks, ALexandre
You need to go into file, options, options and settings and turn off the feature “auto time I telling everyone for the current workbook. You can also turn it off in the global settings.
Great, perfect – thanks a lot !
Hello, Great tool – very insightful. The memory usage works fine with the usual tips & tricks shared.
In the measure sheet, when i refresh, i have a bug ‘DataSource.Error’ – unsupported dependency node discovered’.
Related or not, when I click on the Refresh Measures code, i see the code is stopping at Sheets(“Measures”).Range(“A4”).ListObject.QueryTable.Refresh BackgroundQuery:=False
Can you please help out? I could not find the solution online
Thanks in advance to all of you
Sorry, i don’t know the problem and don’t have time to investigate atm. Do you get the same error if you manually refresh the measure table? Right click, hit refresh.
I Indeed tried the 3 ways and none of them worked.
I may have a fundemental misunderstanding of how this works, but is there any way this can *add* measures to Power BI?
No. This is not a tool to add measures. I suggest you look at Tabulatur Editor.
https://exceleratorbi.com.au/copy-measures-between-2-power-bi-files/
https://exceleratorbi.com.au/introduction-to-tabular-editor-for-business-users/
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?
Please read the instructions in the provided readme file
Is there any chance you can add a ‘Column’ worksheet that mirrors the ‘Measures’ worksheet, but displays only the Calculated Columns DAX Expressions?
Are you asking if you can extract the DAX formulas? You can do that with DAX studio. My article showstopper how to extract measures. You can do the same for columns. https://exceleratorbi.com.au/getting-started-dax-studio/
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
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!
I certainly haven’t covered all the use cases I am sure. But that error is normally triggered when there is a second instance of Power BI in the temp folder. On the connection tab there is an open folder button. If you click on that it will open the temp folder and you can check for any orphaned instances – just delete them. Let me know how it goes
Regarding what Jan Verdickt wrote, I deleted all the power bi temp files. Sitll doesn’t work. It seems that the PowerBI Aug udpate is the problem (it also worked with Jun version). Thanks
It works for me with the Store and Direct Download Aug versions. Make sure you have read the readme.txt and followed the instructions about the issues you can encounter. If you still can’t get it to work, contact me via my website and I will see what I can do.
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
Try closing a Excel, then reopen and do it again. Make sure there are no other workbooks open. Does that fix it?
Hell Matt
I tried your suggestion and the error persists. 🙁 maybe my Excel version? How can I debug it or send you some debug to understand the error?
Thanks
I don’t guarantee support, but I will try to help you. Please contact me via my website contact form and provide your email address.
I had this problem and eventually figured out how to fix it.
The answer is in Matt’s post from April 7 earlier this year.
Just to clarify for newbies like me, the options Matt is referring to is the one in the Query Editor, not excel itself. So it’s File/Options and Settings/Query Options/Privacy (in the Current Workbook section). When I selected Ignore the Privacy Levels and potentially improve performance option, the Memory and Measures queries started working.
Also, further details are available here http://tinylizard.com/script-update-what-is-eating-up-my-memory-in-power-pivot/
@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?
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
Rob
Try rebooting your pc first. Sometimes there can be an instance of power bi that has not closed down properly. Let me know.
Unfortunately rebooting seems to have made no difference.
What version of Excel are you using?
2016
OK, well as I said in the post, I don’t warrant it will work and I don’t provide support. But I am curious as to why this is not working. I am willing to connect to your PC and take a look if you are interested. If so, send me your email address via the contact form on my website and we can work out a time.
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
Sorry, I didn’t realise this question related to my query – I thought it was a general question. Now I get it. This is interesting as I don’t get this error. What version of Excel are you using? If you go into Query Options\Current Workbook\Privacy, what is the setting? I have mine set to “ignore privacy levels” and this fixes the issues. I wonder if this is different in other versions of Excel
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 symbols for the Memory_Usage and Measures queries, which are for example “Formula.Firewall: Query ‘Memory_Usage’ (step ‘Source’) references other queries or steps, so it may not directly a data source. Please rebuild this data combination.”
Do you know how to fix this?
Cheers
Chris
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
Chris and Leslie, have a look here http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Hi Matt, Like Chris and Leslie, I am getting the Run-time error 1004 “Query… references other queries or steps, so it may not direclty access a data source. Please rebuild this data combination.” I don’t have any other excel files open and have not otherwise edited your template. I’ve also read the article you referenced, but am not clear what query I’d need to change in your file. I get the same basic error for both the Memory Usage and Measures query refresh. Any specific pointers?
Have you read the information in the readme.txt file? The common issues, including this one, are covered in the file.
Useful one, Matt!
Thanks
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?
Answering my own questions – the first dign of madness??
https://exceleratorbi.com.au/64-bit-power-bi-desktop-32-bit-office-can-do/
Nice, thanks, Matt!
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..
The trick to my tool is that it finds the running instance of Power BI Desktop on your PC and connects to it automatically. If you open the Power Queries in my workbook you will see the DMV to extract the measures. You can copy these queries and use it to extract from SSAS. Just first connect a new Excel workbook to the SSAS Server and then run the query.