My first step with Azure Analysis Service

Image for post
Image for post

Azure Analysis Service (AAS) is an enterprise grade semantic analytical data modelling tool for BI & reporting. In this short blog, we’ll document the steps required to configure AAS and will create a sample report using Power BI Desktop.

To start with, we’ll first provision an Azure SQL Database to host the data. AAS supports various data sources, full list is here.

Create a SQL Database

We’ll create an Azure SQL Database Server and a database.

Image for post
Image for post
Select a Subscription, Resource group, input Database name and Server.
Image for post
Image for post
For this blog, we’ll select ‘Public endpoint’ as ‘Connectivity method’.
Image for post
Image for post
We’ll select some sample data instead of a blank database.

Installing Azure Analysis Service

In this section, we’ll create an instance of Analysis Service.

Image for post
Image for post
Select the ‘Location’ as same as the data source i.e. SQL Database in our case to avoid any data transfer-out cost.
Image for post
Image for post
Image for post
Image for post
We’ll note down the server name.
Image for post
Image for post
We can create a new sample model from the portal; however in actual case we’ll skip this.

Connect AAS Using SQL Server Management Studio

SQL Server Management Studio (SSMS) is used to manage AAS & any models created inside AAS.

Image for post
Image for post
Select Server type = Analysis Services with appropriate authentication procedure and connect.
Image for post
Image for post
Once we login, we can find the models (if already created).

Connect AAS Using Visual Studio

Visual Studio is used to develop models. Download & install it. Install Microsoft Analysis Services Projects if not installed by default.

Now open Visual Studio > Create a new project > select Analysis Services Tabular Project as a project template > Configure your new project.

Image for post
Image for post
Image for post
Image for post
Connect to the AAS Workspace server.

Once we connect to our AAS instance, we need to configure the required data sources. We can bring the data from data source (Import mode), model with Visual Studio and publish into AAS or, AAS models can have direct connection with the underlying data source (DirectQuery mode).

DirectQuery Mode = Off / Import

Tabular model by default store data in-memory Vertipaq cache and queries from it.

Image for post
Image for post
Select ‘New Data Source’ from the Tabular Model Explorer.

Select Get Data > Azure > Azure SQL Database. Enter the Server, Database name & authentication information. Select the tables and load.

Image for post
Image for post
Check the no. of rows transferred (DirectQuery Mode = Off).
Image for post
Image for post
Once the selected tables have been loaded into Visual Studio.
Image for post
Image for post
We can transform, change data types or remove unnecessary columns.
Image for post
Image for post
Build the project. If successful, we can deploy the model into Azure Analysis Service.
Image for post
Image for post
After successfully deployment into Azure Analysis Service.

We can now go to the Azure Analysis Service and find the deployed models.

Image for post
Image for post
We can open the models in Power BI Desktop.

DirectQuery Mode = On

This option will be useful in case of very large datasets.

Image for post
Image for post
Select the model & change ‘DirectQuery Mode’ to ‘On’ (if required).

Once we add the data source and select table(s), we’ll see the following message.

Image for post
Image for post
Note, for direct query no data will be transferred into Visual Studio or AAS.

Connecting AAS using Power BI Desktop

Once we have pushed our models into AAS, we can access the data using Power BI.

Image for post
Image for post
Launch Power BI Desktop > Get Data > Azure Analysis Services database.
Image for post
Image for post
Input AAS server details, select ‘Import’ or ‘Connect live’.
Image for post
Image for post
We can browse and load the models.
Image for post
Image for post
Once loaded into Power BI Desktop.

To Note

  • Azure Analysis Service often requires proper data modelling techniques, entities may not be performant as-is.
  • We should avoid very wide data sets — small number of columns works better.
  • Analysis Service yet to support data warehouses built with Databricks Delta Lake or Parquet files with Apache Hive.
  • In case we’re enriching our data assets heavily inside Analysis Service we may struggle to reuse the enriched assets from other big data tools/components. So, we need to balance in between data modelling outside AAS vs modeling inside AAS.

Thanks for reading!! If you have enjoyed, Clap & Share it!! To see similar posts, follow me on Medium & LinkedIn.

Written by

Tech enthusiast, Azure Big Data Architect.