Azure - Setting Up Azure Analysis Service (AAS)

Case
I want my BI Semantic Layer in the Cloud. How can I use Azure for this?

Azure Analysis Services (AAS) as your BI Semantic Layer

Solution
On-premises we, as Microsoft BI specialists, use SQL Server Analysis Server (SSAS) for this, but we now have an Analysis Service in Azure. This is called Azure Analysis Services (AAS). Just like SSAS, you can choose between multiple data sources (both on-premises and Cloud) and use all the known tools for presenting your data, like SQL Server Reporting Services (SSRS) and Power BI.

In this case, I will use a SQL Server on-premises. On this local server we have a database called World Wide Importers. This is the new sample database of Microsoft. It replaces the 'good old' AdventureWorks. Because we are building an Analysis Cube, I choose the database WorldWideImportersDW. This is the full sample database for OLAP (OnLine Analytical Processing). You can download this database and more here.

1) Create an Azure Analysis Service server
Go to your Azure portal and search for Analysis Services. Give your server a suitable name and choose a resource group or create a new one. For now, we are setting up a Developer server (D1). This version is very suitable for development and demo scenarios. More pricing details and feature differences between the tiers (Developer, Basic and Standard) here. Fill in the Administrator, in this case my own credentials of my company account (part of Azure AD).

We also create a new Storage account. We choose Locally-redundant storage (LRS). More information about the different storage options can be found here.

Azure - Create the Azure Analysis Server

2) Create an Analysis Services Tabular Project
Open in Visual Studio a new Analysis Services Tabular Project. Now fill your Azure Analysis Service (AAS) server that you have created earlier in combination with Compatibility level SQL Server 2016 RTM (1200). This one is supported by AAS. More information about compatibility levels for Analysis Services Tabular models here. Finally click on Test Connection and sign in with your credentials (which also has access to the World Wide Importers database). Now we can build our Tabular model.

Visual Studio - Create Tabular project

In this case we build a small model for testing purposes.We import the fact table Order and the associated dimensions: City, Customer, Date, Employee and Stock Item. We give the tables appropriate names.

Visual Studio - Build the model

'On-Premises data gateway'
Make sure you have installed the 'On-Premise Gateway'. Click here for more information.

2) Deploy the Tabular model
Now we that have created the model, we can deploy this to the AAS server. This works exactly the same as when you build and deploy a Tabular model for an on-premises Analysis Server. So right click on your Tabular project and deploy.

Visual Studio - Deploy the model

Result
Let's see if we can find the model. To do this, connect to the AAS server in SQL Server Management Studio (SSMS). Fill in the server (asazure://westeurope.asazure.windows.net/bitools) and choose Active Directory Password Authentication. For now we use the same credentials as the Analysis Services admin (which you must fill in when creating the AAS server). Once connected we can find our dimension and fact tables under 'bitools AAS', great!

SSMS - Connect to the model

Note:
Off course you can manage your AAS server in the portal and give, for example, other users access to the server. This will be part of a future post.

Summary
For this blog we have set up a small Tabular model in the Cloud. It is not difficult (it works the same as developing an on-premise Tabular model), but you need to install a gateway for on-premises data and the big difference is now that your AAS (Azure Analysis Service) server is hosted in Azure. Good to know is that you can also turn on/off the AAS with PowerShell. Click here for more information.

Future AAS posts will show how to connect to AAS with Excel and Power BI or how to add additional users.