Today I am sharing an easy way to use Power BI Desktop as a SSAS tabular server on your local PC. I find increasingly that I am building data models in Power BI Desktop and yet I still want to use Excel to analyse the data some of the time. If I load the PBIX workbook to the Power BI Service, it is easy to use “Analyze in Excel” to query the data model. But there is currently no standard supported method to do the same thing using Power BI Desktop as a server (see my suggested idea to Microsoft at the bottom of this page for more about that). Until then, we need to use a “hack” to complete this task.
I have previously shared a way to:
- Open DAX Studio on your PC
- Use DAX Studio to extract the Port Number of a running instance of Power BI Desktop
- Manually enter this information into Excel to connect to Power BI Desktop and use it as a local SSAS Server instance
- Query the data model that exists in the open Power BI Desktop file with a Pivot Table in Excel.
The problem with the above approach is that once the Power BI Desktop file is closed, the connection string becomes invalid. Next time you want to use Power BI Desktop as a server you must go through the entire process again plus you need to alter the connection string that was previously created – hardly a scalable solution.
An Excel VBA Workbook Template to Solve This Problem
Today I am sharing a tool that I built recently that completes the entire process automatically in Excel using VBA and Power Query.
Disclaimer: I am providing this tool free of charge, without support, and do not warrant that it will work for you. This is not supported by Microsoft and could stop working anytime. If it works for you, then great – if not then it is unfortunate :-(. Maybe you can deconstruct what I have done and make it work for you. I built this using Excel 2016 and I have tested it on 2010 and 2013 and it works (Please make sure you have the latest version of Power Query installed).
You create a new blank Excel Workbook using my template (from personal templates), then click the “Refresh SSAS Connection” button. The tool will then:
- Detect an instance of Power BI Desktop running on your PC (there must be 1 and only 1)
- It finds the port number and database name of the running instance
- it modifies the existing connection string in the Excel Workbook to repoint it to the current running instance of Power BI Desktop.
- If the workbook already contains pivot tables that were previously built using the same data source, they will simply reconnect and work as you expect.
- If this is the first time you have used the template, you will have a blank Pivot Table connected to the Power BI Desktop data model.
- You should only have 1 Excel Workbook and 1 Power BI Desktop file open at any one time (as they all use the same connection string).
I am not planning on covering in detail how the tool works. I am making it available to use and modify as you see fit. If you want to look into the VBA code and the Power Query to see what I did then of course this is fine by me. If you want to improve it and give me back the improvements under the same “share and share alike” rules, then please do so as long as you leave my name comments in the file untouched.
How to use the Template
You can download the template here. I have put it in a zip file so that I can keep the same link but change the name of the actual file over time (to manage version numbers).
The file is a macro enabled Excel Template workbook. You need to place it in your Personal Templates location. To find this location, in Excel go to File\Options, then navigate to save (1 below) then check the location (shown in 2). If you don’t have a current template location, you can set your own. Note Excel 2010 has a different file location (Google it).
To create a new workbook that uses this template, in Excel go to File\New, select the personal templates option (shown as 2 below) and then select the template.
I would love to hear what you think and find out if this is a useful tool for you.
Let’s Ask Microsoft to Make this a Standard Feature
I was talking to Avi Singh and he suggested that I request this as a standard feature. I have set up an idea that you can find and vote for here. If we get enough votes for this feature, Microsoft may build out this capability into the core product(s) so it is easier for everyone and fully maintained for future releases.
Relevant Articles
This is the first post in a series of articles. You can read the other post in this series from the following link.
just awesome. Now I am using Power BI tool as a database. I wanted to take this a step further as to how to connect it to python so that we can create charts in jupyter notebook or python spyder as well.
But this was a big help as well. thanks Matt
Hey Matt,
I’m trying to connect my SSAS but it is not located in the loaction as what is mentioned in cell “B2” on sheet “Connection”.
I checked the path in cell “B2” but when activiting the macro “Refresh SSAS Connection” the path is rest to the old location.
How can this be solved?
The macro stops at below:
“UpdateUserPath
Range(“SSAS_Data”).ListObject.QueryTable.Refresh BackgroundQuery:=False”
Regards,
Wouter
Are you using the correct version of the Excel file. There is one for the Shop version of Power BI Desktop and another for the downloadable version of Power BI Desktop. I do not have a version that works with Power BI Desktop for Power BI Report Server
I’m using Power BI desktop for Power BI Report Server. So unfortunately I will have to look for an other solution.
Hi Matt,
Thanks for the template, really useful! Question – do we need to have DAX Studio installed to run the queries in the template?
Thanks
You don’t need DAX studio installed. Both DS and my template run the same query over the data model, so they work independently.
I voted to have this a standard feature.
Awesome….Thank you for providing us with this.
This is absolutely great, Thanks, Matt!
Strange, however, I can’t seem to find the PBI hierarchies in Excel. Somebody else experiencing the same issue and maybe has a solution?
I don’t believe I explicitly extracted the hierarchies.
Hi Matt
Sorry, I was not specific enough, meaning the hierarchies in the PivotTable Fields menu in Excel, not a specific extraction from the DMVs.
We have a SSAS Tabular at work with many hierarchies and they are usable from Excel. Also “Analyze in Excel” from the PBI Service comes with hierarchies. In the template connecting to the local PBI Desktop, they are absent.
I could not find any difference in the MSOLAP.8 connection string. I’m wondering whether other users are also missing the hierarchies? Maybe the PBI Desktop engine just doesn’t provide the hierarchies?
Anyway, Thanks!
Mmm, I’ve never tested this. Desktop definitely supports it, but clearly something is wrong. Does it work with Analyze in Excel?
With PBI installed as an app I had to change the variable user in visual basic module to the below for the template to work:
user = Environ(“USERPROFILE”)
I recently switched to the store version of Power BI, and I now receive an “Analysis Services Connection 14.0” dialog that I don’t recall seeing previously, and if cancelled, the 1004 error. Privacy settings on template are set to Ignore
dialog ss: https://pasteboard.co/JbsdnJe.png
There is a different version of my Excel file for the Store Version. It is in the zip file
lol, was wondering why one was named differently, but didn’t put it together
thanks so much matt for a wonderful tool
Just. Brilliant. Thank you!
Have you tried the solutions in the readme file?
Hi Matt,
Hi Matt,
I get same error as Ivan R.
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
After yet another otherwise great update from Microsoft, the Power BI Desktop Store App changed the AnalysisServicesWorkspace folder location. I posted a solution to get Matt’s great Excel template to work again on the Power BI Community at https://community.powerbi.com/t5/Desktop/PBI-Desktop-App-Version-is-missing-AnalysisServicesWorkspace/m-p/547583/highlight/true#M257465
I just tried the link and it works fine. It is a dropbox link, so check that your company does not block dropbox access. If it does, just use an open internet connection.
Hi the download link doesnt work anymore. I am very interested in your solution, can you reupload please?
Thanks for the guide! When I try this though I am having the following issue and maybe you can help:
“The following system error occurred: No connection could be made because the target machine actively refused it.”
Do you happen to know how to get around that?
Sorry Claudio, I’ve never heard of this one. It could be some settings made by your IT folk on your PC.
Am also getting the same error. Any inputs on how to get around it?
I have never seen this error before. Have you followed all the steps in the readme.txt file? Can you share some more information about how you are using the tools?
Thank you, this solution seems to work only in Excel 2016, couldn’t find the way to make it work in 2013, so just graded my office and now it’s just fine )
Hi Matt,
I get following error when i try to connect.
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
I’m using Windows (use my current credentials) to connect.
Do you know what might be the problem?
I get the same message.
It happens on the VBA code line
Sheets(“Memory Usage”).PivotTables(“PivotTable1”).PivotCache.Refresh
In the list of queries I see a warning sign for these:
– Memory_Usage (Connection only)
– Measures (0 rows loaded)
– Measure_Dependenses (0 rows loaded)
– Reference_Count (Connection only)
– Max_Depth (Connection Only)
I am using excel 365
Solved, using the steps in the readme file
Hello Matt!
Have tried to use this tool and got an error::
Run-time error 1004
can’t connect, bla-bla-bla
The Debug button leads to this line of code:
Range(“SSAS_Data”).ListObject.QueryTable.Refresh BackgroundQuery:=False
SSAS folder with AnalysisServicesWorkspaces was detected correctly, I can open it from Excel sheet Connection.
May it be related to different language of my Excel (RU) and PBI Desktop (EN) ?
Please refer to the readme.txt file included in the download
i really do not not how to thank you !
we recently moved to power BI and i hade various excel file built with multiple pivot and then graph.. i need to use those off line also.. and wow you solved my need! thank you really much!
Hi Matt, this looks like an awesome tool, but I am now on the Windows Store version of Power BI Desktop and it appears that the workbook does not detect that instance of PBI Desktop. Any chance this will get updated for the new application?
Ooooh, tricky. I will have to take a look. Thanks for letting me know
Thanks for the Tip Off Mike. I did a bit of super sleuth work and I found the new temporary location for the instance of Analysis Services. I have created a second copy of the template (in the download link) and it now has one for the old version and one for the store version. I could consolidate it but it would create some potential issues that I think I will avoid.
Change PowerPivot & PowerQuery data sources from local dataset to Power BI Service?
I have a very large Excel workbook with dozens of Pivot Tables that we use for daily sales reporting. The data model has numerous tables with calculated columns and measures that have been built over time. Many worksheets have multiple slicers so the worksheet is huge and takes forever to refresh. We have replicated the data model in Power BI Desktop and published it to Power BI where it now refreshes daily without a hitch.
So, how do I redirect all of the data sources links in my Excel workbook to now point to the Power BI service instead of the local dataset? I can do it manually but that will take forever to accomplish. I’m hoping someone has an idea how to automate that process.
Have you tried using Power Pivot utilities on a local copy of the workbook?
I work on the same kind of worksheets at the moment. As long as you are working with cubereferences it is quite easy.
Based on your Power BI servicemodel, you can analyse in Excel, then create a small pivottabel and convert that to a cubeformula.
Next you can copy all sheets from the old Excelfile, that only includes cube formulas to your new Excelfile and do a search and replace of the “ThisWorkbookDataModel” to the name of your PowerBI Datamodel (in my case called “PBIDatamodel”.
That works great until I copy a full Pivot Table, where I for some reason is not allowed to change the datasource, but is told that the PivotTable is only a connection string. If somebody can tell how to change that connection string to the Power BI Service datamodel, I think that is the full answer to your challenges.
I have spoken directly to the Excel Dev team about doing this (repointing the pivot table connection to Power BI). They looked into it and replied that “it can’t be done”. I do however know it is possible because the guys at https://www.power-planner.com know how to do it. They will do it for you for a fee. You can contact Per at the URL above
Hi Jørgen,
You can change the connection of a pivot table if you go to Data–Connections–Properties (of the connection to the service) and copy the entire connection string, then go to Data–Connections–Properties (of the local connection which the pivot table(s) are using), and overwrite the connection string with the one from the service.
Forgot to mention that is is under “Definition” once you are in the Connection Properties. This method in fact is easier than find/replacing in your cube formulas. Just copy the string from a connection to the service and overwrite the connection on your locally-connected workbook and the whole thing (pivot tables, cube formulas, etc) is done at once.
Hey Matt! Thank you so much for sharing! App is brilliant.
Do you happen to know, what can cause such problem:
when I hit “refresh SSAS connection” button it gives me message “you must have exactly 1 instance of power bi”.
and I’m sure that I have only one power bi opened. I rebooted my comp but the problem still stays there.
I work on windows 10 64 with Excel 64 and Power BI 64 (april 2017 release).
I use your file: Local Host Workbook ver 2.4.xltm
Any Ideas?
I’ve never seen that before. My best guess is you have an old instance of SSAS in the temp folder that didn’t get deleted. You could try doing a disk cleanup and delete temp files. If that doesn’t work, send me an email an I will give you a further suggestion.
As luck (unluck) would have it, this same thing just happened to me then. My hunch is right, it happens if Power BI ends unexpectedly and doesn’t clean up after itself. You need to delete the old temp files. Simply go Start\Run and paste the following folder into the run dialog, then click OK. Delete all the sub folders you can see. If there is one that is still active it wont allow you to delete that one, so just click cancel for that one. Then it should work.
I’m probably a bit late with my comment, but trying your excel right now and facing the issue that it mentions you must have exactly 1 instance of Power BI desktop open. You mentioned the below in the comments, but you didnt mention which command I should fill in 🙂 Do you happen to remember?
” Simply go Start\Run and paste the following folder into the run dialog, then click OK. Delete all the sub folders you can see.”
Hi Stef. Yes I solved this some time ago – it is covered in the readme.txt file I believe. There is a button on the last worksheet that will open the folder for you so you can delete the rouge instances.
Matt, this is awesome. got some runtime error ‘1004’ [datasource.notfound] file or folder.
I’m using excel 2013, 64bit, PB desktop 64 bit. thanks for the help.
Hey, Matt. Your dropbox link for the template file seems expired. Do you mind re-upload? Cheers!
I just shared an excel file that leverage Matt’s approach with another approach, let’s call it Imke’s approach, and the use of Pivot cache /Cube formula to keep the data persistent 🙂 aka can be shared without the data model
https://datamonkeysite.com/2017/01/01/how-to-use-excel-as-a-front-end-to-power-bi-desktop/
Just for the record: “The .ODC file has an MSOLAP connection string that connects to your dataset in Power BI. ”
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/
That’s brilliant! As Many have pointed out, now you have a way of evaluating DAX equations created in Power BI by seeing the underlying data.
Awesome.. I would ABSOLUTLEY love this to become a standard feature!!!
It’s very interesting, hope this becomes a new standard feature ad soon…
@Matt – This is fantastic – you are a Genius – and you have my 3 Votes.
I have been waiting for this for a very long time. The PQ Team keep promising that this is coming soon….
I wish Avi had pushed MS to allow people to connect to the PBI Desktop first and Cloud Later
1) Is there now way to freeze the Port No or Is there a way to connect to the File (DB) rather than the Port #
2) Will this work if the File is kept on a Shared Folder
1) There is no way to freeze the port number, nor the GUID for the DB actually. And no there is no other way to do it (that I know of).
2) which file are you referring to? The Excel file? I don’t see any reason, as long as the Power BI Desktop instance is running on the same PC as the user that is accessing the Excel file. As for running the PBI Desktop file on a server – that is a very different thing and no this won’t work (under the current design).
Sam
there is a way to to lock the port number, by launching the ssas server independenly from Power Bi desktop, but then you have to load the model separately ( at least that what i understood) anyway this is the link goof luck
https://github.com/akavalar/SSAS-on-a-shoestring
Hi Matt,
Another great post.
I am having trouble getting this to work with Excel 2010 (32 bit) and I am running Power BI Desktop (64 bit).
When I click the button “Refresh SSAS Connection” I get a VBA Run-time error -2147417848 (80010108) and it says Method ‘Refresh’ of object ‘Workbook.Connection’ failed.
When I debug the error it highlights the line of code: ActiveWorkbook.Connections(“PBID”).Refresh as the line of code causing the issue.
Any idea what may be causing this error issue?
Thanks
That’s interesting. I wonder if it is related to the 32/64 bit issue. Imke Feldman alerted me to a way to complete this task without the step you mention that failed. I have a note to look into that. I will let you know when it is done
it was about time that someone do it 🙂 Excellent Job mate specially for the VBA code
one practical scenario is to used this with cube formula, as the data will be persistent, although static which is good for fixed report format.
kind of personal SSRS server 🙂
Also, I’m clearly noticing that the performance of same data model in Power BI is far better than the one in Power Pivot. Sounds strange to me but yes.
Maybe because the compatibility level of models from PowerBI Desktop is 1200 the same of the SSAS Tabular engine of SQL Server 2016 Analysis Services (SSAS)and the compatibility level from Power Pivot models is 1100 or 1103.
Server 2016 Analysis Services (SSAS), compatibility level 1200, includes many new enhancements providing improved performance.
It’s working fine after some renewed attempts. I guess some bug with excel. Kindly ignore my earlier post. Thanks.
Hi Matt. Just noticed that “Freeze Panes” causes Excel to crash after connecting to Power BI model. I’m using Excel 2016 64-bit (Version 1610, Build 7466.2038)
Thanks Matt. The problem when I conversted my PBIX file to excel pivot table, it is not letting any thing in values category.
Nazim. Do you have measures? You can’t use implicit measures when you have a pivot table in a Excel connected to a power bi desktop file. Write a measure like total sales = sum(sales[qty]) in power bi desktop and then refresh the connection to Excel. You will see it there.
I remember one of the guys at Microsoft showing me how to do something similar but manually (think it was Jeff Wang) but this template will help no end. Yes, you definitely need some kind ease of debugging as I find I’m publishing constantly to discover my results at the user (web) end so this pain point has me all ears
This is amazing, Matt. Thanks a lot for making this automatic. This feature is so much desirable as it eliminates the boundaries between Power BI Desktop and Excel. Now, I can say that I’ve got a perfect testing environment for all my Power BI models, just like SSAS. Thanks again. Wonderful work!!! (:
Good stuff Matt! I love Power BI. And Power BI Desktop is great, but often while building the model want to quickly dive into Excel to debug something (usually a DAX measure or sometimes investigate Power Query issues). No better way to do that than Excel.
And you have my 3 votes for the feature!
Wow! That’s a great tool. Powerbi.com does have “Analyse in excel”, but the connection is always slow. Yes, we should all vote for having this as a standard feature.