Azure Analysis Services is a fully-managed Azure-hosted version of SQL Server Analysis Services. In this series of posts, I’ll show you how to;
- Use SQL Server Data Tool (SSDT) to build a Tabular Model against an on-prem SQL Server Data Source
- Deploy the model to Azure Analysis Services
- Refresh the model data using the On-Premises Data Gateway
- Connect Power BI to the deployed model
- Setup Diagnostic Monitoring
The major benefits of Azure Analysis Services, when compared to a traditional on-prem version using SQL Server, include the ability to scale up/down/pause/resume on-demand to control costs, easily scale-out up to 8 model replicas to enable consistent performance during processing operations with dedicated processing nodes, and to serve Power BI queries for a super-fast dashboard experience, fully hosted and managed inside Azure data centers.
First, a couple of setup tasks. We’ll create an Azure Analysis Services instance, and configure the On-Premises Data Gateway.
Creating an Azure Analysis Services Instance
Login to your Azure Portal, and create a new resource of type “Analysis Services”. If you need one, you can get a free Azure account here, which includes 12 months of free services such as Windows and Linux VMs, Azure Storage and SQL Databases. For other services, you get $200 credit to explore any Azure Service, including Azure Analysis Services.
There’s a couple of important settings to consider when creating the service. Firstly, the location. The location of your Azure Analysis Services (AAS) instance needs to be in the same location as the Gateway resource (which I’ll cover below). In my example below, I’ve chosen Australia Southeast (Melbourne). Secondly, choose a pricing tier. In my case, I’ve chosen D1, which costs ~ $100 USD per month.
Installing and Configuring the On-Premises Data Gateway
While our AAS instance is provisioning, let’s download, install and configure the On-Premises Data Gateway from here. As always, ensure you have the latest version of the Gateway, as the team are making enhancements on a regular basis.
The Gateway is the software used by Azure Analysis Services to contact the On-Premises source database when re-processing models to refresh the data. It’s the same gateway used by Power BI for refreshing data.
After downloading the gateway, run the setup file to begin the installation and configuration process. An overview of how the gateway works, including required network and account settings, can be found here.
During the install, you will be prompted for your work or school account which will be setup as a gateway administrator in the gateway service. In order to associate your gateway to an Azure resource, you will need to be an administrator in Azure.
After I signed in with my Microsoft account, I’m presented with the option to register a new gateway, or take over an existing gateway. In my case, I’ll setup a new one.
The next step is an important one. In the previous steps, we logged into our Azure account. The Gateway install will use the default Azure region for your subscription, so if you’ve configured your AAS instance in a different region, make sure you change the region for the gateway install, as it must be in the same region as the AAS instance. Note; You can use the same Gateway for both Power BI and AAS, but in both cases, the gateway needs to be installed in the same region as the Power BI tenant or AAS location. If they’re in different regions, then you can install multiple gateways, one for Power BI and one for AAS.
In my case, I clicked “Change Region” and selected Australia Southeast, to match the location of my AAS region. This means that I’ll need to install another Gateway to use for Power BI.
After changing regions (if required), the next step is to create a gateway in Azure, per below. This is effectively the “Azure half” of the gateway installation, which will work in conjunction with the on-premises install we just completed.
Again, make sure the location of the gateway in Azure matches the location of the AAS instance, in my case, Australia Southeast.
In some cases, you may receive an error on this step “No installed instances were found for the current user“. If this is the case, check that the user that installed the Gateway software on-prem is associated with the same Azure Active Directory tenant as the Azure subscription containing the AAS instance. To check this, export the logs from the on-premises gateway and open the GatewayProperties file. Check the TenantId value matches the TenantId in the Azure subscription containing the AAS instance. From the Azure Portal, click on the Help icon in the upper right and then choose ‘Show Diagnostics’. You can find the tenant id in the diagnostic JSON file. If they’re different, install a new Gateway, making sure to use an account in the same Azure tenant as the AAS instance.
The final step in the configuration is to associate the Gateway with our AAS instance. To do this, select the AAS instance in the Azure portal, click on the On-Premises Data Gateway property, and select the recently configured gateway, per below. Finally, click “Connect selected gateway” to complete the connection.
In the next post, we’ll deploy an Azure Analysis Services model from SSDT, and use the Gateway to refresh the data from the On-Prem SQL Server source.