Open in app
Prosenjit Chakraborty
231 Followers
About

Sign in

Open in app

My first step with Azure Analysis Service

Prosenjit Chakraborty

Jun 23, 2020·4 min read

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.

Select a Subscription, Resource group, input Database name and Server.
For this blog, we’ll select ‘Public endpoint’ as ‘Connectivity method’.
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.

Select the ‘Location’ as same as the data source i.e. SQL Database in our case to avoid any data transfer-out cost.
We’ll note down the server name.
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.

Select Server type = Analysis Services with appropriate authentication procedure and connect.
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.

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).

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

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.

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

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

We can open the models in Power BI Desktop.

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

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.

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.

Launch Power BI Desktop > Get Data > Azure Analysis Services database.
Input AAS server details, select ‘Import’ or ‘Connect live’.
We can browse and load the models.
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.

Prosenjit Chakraborty

Tech enthusiast, Azure Big Data Architect.

About

Help

Legal

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store