How to setup a tabular data model in SSAS Azure

By: Siddharth Mehta   |   Updated: 2017-04-13   |   Comments   |   Related: > Analysis Services Development

Problem

Microsoft announced the availability of SQL Server Analysis Services (SSAS) on Azure late last year. SSAS Azure is available in a Platform as a service model. Presently it supports tabular models, with a possibility of support for multidimensional models in the future. Organizations and practitioners would want to port their existing models on to SSAS Azure or may want to create a new model in SSAS Azure to evaluate the features. We will do a walk-through of steps on how to quickly setup a tabular data model in SSAS Azure.

Solution

SQL Server Data Tools and SQL Azure can be used to quickly setup a tabular model in SSAS Azure. First you need to setup a SSAS Azure server by logging on to your Azure portal. The Analysis Services Preview menu option is available in the Intelligence + Analytics section of the new resources menu.

Following are the options required to be provided to setup a new SSAS Azure server. If you intend to start with a lower price tier, consider using D1 as shown below.

SSAS Azure supports a wide variety of data sources, SQL Azure database being one of them. I have a SQL Azure database named AdventureWorks containing AdventureWorksDW data. We will be using this data as the data source and will create a data model using this database.

Create a new blank Tabular project using SQL Server Data Tools. For the workspace server, consider using a local tabular server as your workspace server. Click on the Model menu and select the "Import from Data Source" menu item. As we are going to import data from SQL Azure, select this as shown below and click Next.

Provide the SQL Azure Server address, which should be in the format of [servername].database.windows.net. Provide your server credentials and select the database which you intend to use as a data source. Once you have set the data source, click Next.

Generally you can use the service account for impersonation. Depending upon your configuration, select an appropriate impersonation method and click Next.

As we intend to select tables from our database, select the first option as shown below and click Next.

Select appropriate tables as required for your tabular model. For the purpose of demonstration, I have selected one dimension and one fact table from the data source. After selecting the required database objects, click Finish.

Now you have the required data from the SQL Azure database. After you are done with modeling your tabular data model, its time to deploy the model. Right click the project in solution explorer and select the properties. In the deployment properties, set the server property to the name of the SSAS Azure Server name which should be in the format of asazure://[hosting-region].aszure.windows.net/[ssas-instance-name]. After setting, deploy the model and this should deploy data to your SSAS Azure instance.

Once deployment is complete and successful, connect to the SSAS Azure instance using SSMS. You can now query the model like a regular on-premises SSAS Tabular model using DAX queries as shown below.

In this way, we can quickly setup a tabular model in SSAS Azure using SSDT and SQL Azure.

Next Steps

• Consider creating a complex database model and deploy to SSAS Azure to evaluate different features of SSAS on Azure.
• Learn more about Azure.
• Learn more about DAX.
• Learn more about SQL Server Business Intelligence.

About the author

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips