How to setup a tabular data model in SSAS Azure

By:   |   Comments   |   Related: > Analysis Services Development


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.


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 Server in Azure

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.

SSAS Data Source

Provide the SQL Azure Server address, which should be in the format of [servername] 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.

SSAS Azure Server Connection

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

Service Account for Impersonation

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

Data Selection Options

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.

Select Tables for SSAS Azure

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][ssas-instance-name]. After setting, deploy the model and this should deploy data to your SSAS Azure instance.

Deploy Model

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.

Query SSAS Azure in SSMS with DAX

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta 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

Comments For This Article

get free sql tips
agree to terms