Connecting to Azure Analysis Services via Excel – Easy Mode
Power BI is certainly picking up speed, but the number 1 ad-hoc reporting client in Analysis Services environments continues to be Excel.
Not much of a surprise really… Excel is a super popular tool across a wide-variety of users. Once you train someone how to use a pivot table, there’s no going back! In fact, that was the biggest complaint across many of the clients I worked with when we discussed Power BI as a reporting tool – where’s the pivot table?
Thankfully, Microsoft listened to the community and implemented this functionality via the “Analyze in Excel” feature. Initially, I was hoping for / expecting a new (pivot table) visualization for Power BI reports, but what we actually got is something much better.
Note: there’s still very much a need for having a pivot table’esque visualization in Power BI for reporting/dashboard purposes.
Over the years, one of the most difficult tasks when training new users to work with Analysis Services cubes and/or tabular models in Excel has been “creating the connection” from within Excel. Azure Analysis Services has further compounded this problem by requiring the latest data providers.
Thankfully there’s a simplified way to create the connection!
What follows is a set of instructions for creating a Power BI data set enabling users to quickly and easily create a connection in Excel to an Azure AS database without having to know anything about data providers or server names. More advanced users, who don’t mind the details, might want to check out this post by Josh Caplan.
Creating the Connection
(backend piece to be completed ahead of time by you or someone else in IT w/ an understanding of SSAS, server names, etc)
Open Power BI Desktop –> Get Data –> SQL Server Analysis Services:
Enter the server address for the Azure AS instance and choose “Connect Live”:
(at this point you may be prompted for Azure credentials)
Choose the model (or perspective) to which you’d like to connect. In this case the demo database I’m using doesn’t have any perspectives defined so we only have the option to connect to the model.
After clicking OK on the screen above, you should see a blank report canvas and the field explorer with objects in your target Azure AS database:
Save this file (giving it a descriptive name about the data source) and click the Publish button on the far right of ribbon:
After selecting a destination (i.e. workspace) the report will be published to the Power BI service.
The publishing process creates a data set and report in the selected workspace. However, because the purpose this exercise is simply to create a means for other users to use the “Analyze In Excel” feature to slice/dice data in an Azure Analysis Services database (and because the report is blank), we can remove the report object as it’s just going to add to the clutter and (potentially) confuse.
From a “setup” perspective, that’s it. The next set of steps should be viewed from the end-user perspective!
Using the Connection
We’re now ready to click on the “…” menu to the right of our new dataset and choose the “Analyze In Excel” option:
Choose “Open in Excel”:
If this is the first time you’re doing this (and/or you haven’t already installed the latest/greatest AS-OLEDB provider), you’ll want to click the Download button:
Download and install the SQL_AS_OLEDB:
Now, try again… only this time, click the “I’ve already installed these updates” and download the ODC (office data connection) file.
Double click the ODC file to open Excel and click “Enable” at the Excel Security Notice about “data connections”:
Enter in your Azure credentials (if prompted) and your done!
Couple of additional notes…
- Ensure the end-users have access to the workspace where you publish the Power BI workbook!
- I’m using the click-to-run version (16.0.6965.2117) of Excel through my MSDN office 365 subscription
- If you open the ODC file in a text editor, you’ll see it’s already specifying the MSOLAP.7 provider. This has been a very common issue for folks on the Azure AS MSDN forums and in many cases requires manually editing the connection string (changing MSOLAP.5 to MSOLAP.7)