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;

  1. Install SQL Server (unless your data source is running on a different machine). You can get a free trial edition of SQL Server here.
  2. 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
  3. 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.

Next, we’ll create our Tabular model project using our SQL Server data source. In my case, I used an AdventureWorks sample database, available here. I chose the AdventureWorksDW2017.bak.

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 nancy@adventureworks.com. 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.

Advertisements