Get started with Azure Analysis Services
I was looking for documentation online that would help me run Azure Analysis Services on an Azure SQL DB using Visual Studio 2019. Documentation is sparse so I thought I would collate my findings into a single page.
Here I describe how to setup Azure Analysis Services on Visual Studio 2019. I also describe how to model on a live Azure SQL DB.
- Install SQL Server Data Tools (SSDT
- Install Analysis Services Project Templates
- Create a New Analysis Services Tabular Project
- Connect to Azure SQL Online
- Import New Tables
- Add Analysis Services Admin
- Deploy to Azure
- Confirm Deployment in Azure Portals
Install SQL Server Data Tools (SSDT)
Use the Visual Studio Installer to install this.
Install Microsoft Analysis Services Projects Extension
The Analysis Services Projects templates need to be installed. Beware that several Users have reported on the official extension Market Place page that the extension is very buggy. My advice is to carefully install by restarting the machine, install and then reboot.
Create a New Analysis Services Tabular Project
Once installed, it should be possible to create a new Tabular Project.
Connect to Azure SQL Online
The next step is to connect to the Azure SQL DB for semantic modelling. Right-click on the Project icon, and then click Import From Data Source.
Select Azure SQL DB.
Add the appropriate credentials.
Select the Azure SQL database.
Import New Tables
Once the Azure SQL Database connection has been made, import the relevant tables.
Add the appropriate tables/transformation.
Add Analysis Services Admin
Before you can deploy the model to Azure Analysis Services, add the appropriate Users. I missed this step which gave me the following error:
user does not have permissions to create a new object in 'expanalysis' or the object does not exist.
To add a User as an admin, go to the Azure Portal, and from the navigation blade select Analysis Services Admins. From here add a new Admin.
Deploy to Azure
Once all the above has been completed, right-click the project and hit deploy. After a short while you should receive a Success message.
Confirm Deployment in Azure Portals
Your newly created Model should now be in Azure Analysis Services Portal.
From this point, you can force a Sync and get your Users to access Analysis Services through Excel or Power BI.
The Analysis Services Extension itself seems to be stable for me. A lot of Users have reported crashes, I haven’t experienced any of these myself. Either way, I hope Microsoft fixes these issues soon.