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.
This is the first post in a series of articles. You can read the other post in this series from the following link.