Create a data model using Azure Analysis Service and PowerBI

Sanajit Ghosh
Aug 31, 2018 · 5 min read

Azure Analysis Service

In this post we are going to learn how to setup a data model using Azure Analysis Services. We have first created Analysis Services server in the Azure portal and added a sample tabular model database named “Adventure Works”. Next we have created an intelligent data model using Azure Analysis Web Designer and finally visualized the resultant dataset using PowerBI Business intelligence tool.

Note-Before proceeding you need to have some basic understanding of SQL,DAX query and an active Azure subscription

Azure Analysis Services is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. Using advanced modeling features to combine data from multiple data sources, define metrics, and secure your data in a single, trusted tabular semantic data model. The data model provides an easier and faster way for users to browse massive amounts of data for ad-hoc data analysis without using expensive tool like Visual studio/SSMS.

1.Create Azure Analysis server from Portal

Click + Create a resource > Data + Analytics > Analysis Services.

Give a specific server name, Resource Group and select the pricing Tier. For development purpose we have used the minimum tier subscription i.e. D1 or developers preview. One can use higher versions depending on the volume of data.

2.Adding a Sample data-set

We have added a sample Adventure Works tabular model database to our server. The sample model is a completed version of the Adventure Works Internet Sales (1200) sample data model. A sample model is useful for testing model management, connecting with tools and client applications, and querying model data.

In server Overview, click New model.

In New model > Choose a datasource, verify Sample data is selected, and then click Add.

In Overview, verify the adventureworks sample model is added.

Note: You can also choose the dataset from SQL data warehouse/azure SQLDB or from powerBI file. You can easily add multiple dataset from various external sources and create models using Azure Analysis web designer.

3. Setting up models with DAX query

Once the model is loaded toggle though the Model and Query tabs. You will find that the data-set is already loaded with a table schema.

Click on Arrange All to get a model view of all the tables. You can also add/delete/change relationship to any of the tables based on the requirement.

Click on the Query tab and select the columns in the right. Here we want to calculate the Yearly income, Total Children and Number of children at home in the customer table. Once the columns are checked click on Run button to query the output.

Next click on the Show DAX button to get the DAX query. Copy the query so that we can use it in the model.

4.Adding Measures to the model

Click on the table Customer>Measure>+Add Measure

Here we have added three measures and pasted the DAX query which we saw in the previous section. Once it is done click on save model to register the changes to the Azure Analytics Server.In the next section we are going to visualize the data from this newly created model.

4.Install the PowerBI and get connected to the AAS

To visualize the model we have created in the designer you need to install the PowerBI desktop.Click here to download it. After installing login with your organization/personal credentials.

Note:You can also use any other reporting tools like tableau,D3js, High Charts etc. To know more about Business Intelligence and reporting tool visit this link

Click on Get Data>Azure>Azure Analysis Services Database

You will be asked to provide the AAS server name. Copy paste the server name from azure portal and select option connect live.

Click on adventure works model to get all the tables with their associated columns.

Congratulations! You have successfully connected the model present in the AAS with the PowerBI. Now you just need to drag and drop the columns to view the data. As soon as you drag any column to the work-space, the DAX queries and logics implemented in the model runs behind the scene.

With PowerBi one can easily share the data model to any Business Unit without exposing the internal structure of the data model.The client or business can now easily analyse data without doing any internal query or extra operations inside the model.

This architecture is highly scalable and useful as it eliminates the dependencies from using enterprise-grade application software like Visual Studio/SSMS/SSRS/ other Microsoft tools.

Sanajit Ghosh

Written by

Exploring Azure, writing mostly on Data Science, IoT and Analytics.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade