You can connect to Azure Analysis Services from Power BI, Excel and many other third party client tools. This blog will focus on how to connect to your Azure Analysis Services server from Microsoft Excel.
Before getting started, you’ll need:
- A data model deployed at an Azure Analysis Services server – Creating your first data model in Azure Analysis Services.
- Microsoft Excel – If you have the latest version of Excel 2016 from Office 365, you do not need to install any additional updates. For non Office 365 or older versions of Excel, the MSOLAP.7 provider is required.
- In Excel 2016, on the Data ribbon, click Get External Data > From Other Sources > From Analysis Services.
In the Data Connection Wizard, in Server name, enter the name of your Azure Analysis Services server. Then, in Logon credentials, select Use the following User Name and Password, and then type the organizational user name, for example firstname.lastname@example.org, and password.
In Select Database and Table, select the database and model or perspective, and then click Finish.
Select OK to create a PivotTable report.
A pivot table will be created and you will now see your field list on the side. You can drag and drop different fields to build out your pivot table.