Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to setup a tabular data model in SSAS Azure


By:   |   Read Comments   |   Related Tips: > 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 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].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.

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].aszure.windows.net/[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.


Last Update:






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

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools