In the previous post, we ran through the setup steps including installing an Azure Analysis Services instance and the On-Premises Gateway. In this post, let’s continue with the next steps, including using SSDT to create a new Analysis Services project which uses a local SQL Server data source and deploying to Azure Analysis Services.
Setting up SSDT
Firstly, if you haven’t already, you’ll need to install Visual Studio with the SQL Server Data Tools add-in. The suggested installation order to avoid any errors is;
- Install SQL Server (unless your data source is running on a different machine). You can get a free trial edition of SQL Server here.
- Install Visual Studio 2017 from here. You can choose the free Community Edition. Note, during install, make sure you chose the Data Storage and Processing option
- Install SQL Server Data Tools for Visual Studio 2017 from here. This will enable you to create projects for SQL Server Integration Services, Analysis Services and Reporting Services
After installation, open Visual Studio 2017 and create a new Analysis Services Tabular Project.
Creating a Tabular Model Project
Next, choose the Integrated workspace option and SQL Server 2017 / Azure Analysis Services (1400) for the compatibility level.
If you’re unfamiliar with creating Tabular model projects, there’s a full tutorial available here. Note; when adding the data source to the project, make sure you use the full servername, rather than “localhost” or “.”, to ensure the Gateway machine can resolve the SQL Server name when refreshing the source.
When development is complete, ensure the Deployment Server property contains the Azure Analysis Services servername, which you can get from the Overview page of your AAS instance in the Azure portal.
To deploy to Azure Analysis Services, right-click on the project and choose deploy. When deploying, you may be prompted to enter your account. Enter your organizational account and password, for example firstname.lastname@example.org. This account must be an Administrator in AAS, which you can check in the Azure Portal by opening the Analysis Services Admins section of your AAS instance.
Once deployed, you can browse your deployed models. In the Azure portal, click the Manage blade under Models in your AAS instance, and chose to open the model in the tool of your choice.
If you’re doing following this as a learning exercise, make sure you pause AAS when you’re finished, to stop the billing. Another great benefit of Azure PaaS services!
In the next post, we’ll configure the deployed model to automatically refresh using the On-Premises Data Gateway.