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.
Installing Azure Analysis Service
In this section, we’ll create an instance of Analysis Service.
Connect AAS Using SQL Server Management Studio
SQL Server Management Studio (SSMS) is used to manage AAS & any models created inside AAS.
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.
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.
Select Get Data > Azure > Azure SQL Database. Enter the Server, Database name & authentication information. Select the tables and load.
We can now go to the Azure Analysis Service and find the deployed models.
DirectQuery Mode = On
This option will be useful in case of very large datasets.
Once we add the data source and select table(s), we’ll see the following message.
Connecting AAS using Power BI Desktop
Once we have pushed our models into AAS, we can access the data using Power BI.
- 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.