Sunday, 21 May 2017

Azure - Setting Up Azure Analysis Service (AAS)

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

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

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:// 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

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.

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.

Monday, 15 May 2017

Azure - On-premises data gateways

If you want to use on-premises data in Azure or Power BI you need to install a so called gateway. A quick search resulted in four possible gateway downloads! Which one do you need? It's so confusing!

Microsoft Gateways for Azure and Power BI

There are two different gateways but with several names, versions and purposes:
  • Data Management Gateway
    This gateway is used for Azure Data Factory and Azure Machine Learning, but it was also used for Power BI. However, Power BI now uses the On-premises data gateway! I think / hope that this gateway will be deprecated and replaced by the On-premises data gateway in the near future.
    Download (32 and 64bit):
  • On-premises data gateway
    This gateway comes in three flavors, but you can only install one per server.

Use source for multiple online services
Since you can only install one gateway per server, the On-premises data gateway has some challenges if you want to use your on-premises source for multiple online services like Power BI and AAS or for two AAS servers. In that case you install the first gateway on the source server itself and a second gateway on a separate on-premises server that can reach the first source server. A bit expensive to a have a server running for a data gateway only, but for now this is the only solution.
One source and two online services: two servers with their own gateway

Note: details per gateway will be posted in separate posts (AAS)
Related Posts Plugin for WordPress, Blogger...