Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Deploying Azure Analysis Services using PowerShell cmdlets


By:   |   Read Comments   |   Related Tips: More > Azure

Problem

There has been a growing trend during the last few years in which companies are moving away from on-premises software to cloud, platform or software as a service (PAAS | SAAS) offerings. 

There are five reasons why companies are jumping on this band wagon.  First, the initial cost and time to deploy a system is lower.  Second, all maintenance such as hardware repairs, software upgrades, backups and restores are provided by the vendor.  Third, the ability to scale up or down depending upon the usage patterns is attractive.  Fourth, you are always using the latest version of the software.  Fifth, the performance of the system is guaranteed with a service level agreement (SLA) between you, the customer, and the vendor.  In summary, companies are lowing the total cost of ownership (TCO) by using a PAAS offering.

Microsoft announced the preview version of Analysis Services in October of 2016.  The tabular model is completely supported with this release.  Many enhancements have been added to the service since its GA release in April of 2017.  As a data platform administrator, how do we deploy and manage our models using this Azure service?

Solution

PowerShell has always been the language of choice to automate system deployment and configuration. It is not surprising that we have a series of cmdlets to help us deploy Azure Analysis Services using the resource manager model. Here is an msdn reference to the cmdlets.

Business Problem

One of the business lines in your company is looking at using Azure Analysis Services to create a Business Intelligence (BI) model for their data.  They have asked you to get up to speed on how to deploy and test this PAAS service.

Azure Subscription

This article assumes that you have an existing Azure subscription.  If you do not, Microsoft offers new customers a 30 day trial subscription with a $200 dollar spending limit.  Use this link to sign up now.

List Azure Subscriptions

We must log in as a valid subscription owner before we can do any work. The Add-AzureRmAccount cmdlet will prompt you for credentials. 

The new sign in process has two dialog boxes.  The first box asks for a user name.

New Sign In Dialog Box - User Name - Description: The Add-AzureRmAccount prompts you for a user name and password.

The second dialog box asks for a password.

New Sign In Dialog Box - Password - Description: The Add-AzureRmAccount prompts you for a user name and password.

An account might have several subscriptions associated with it. Use the Get-AzureRmSubscription cmdlet to list the subscriptions associated with [email protected] email address.

List all enabled subsciptions - Description: The Get-AzureRmSubsciption cmdlet lists all subscriptions.

The above output from this command shows one Microsoft Partner Network subscription associated with my outlook account.  My employer gave me this account for research.  If there was more than one account, we could use the Set-AzureRmContext cmdlet to choose the correct subscription.

The PowerShell code below logs into the portal, lists the subscriptions and selects the correct subscription.  Of course, manual intervention is needed for the sign on.

# 
# Azure Subscriptions 
# 
  
# Log in to your Azure account 
Login-AzureRmAccount 
  
# Select a subscription 
Set-AzureRmContext -SubscriptionId 'cdfb69bf-3533-4c89-9684-1ba6ba81b226' 
  
# Clear the screen 
Clear-Host 
  
# List all the subscriptions associated to your account 
Get-AzureRmSubscription 

Choosing a data center

Not all data centers support each platform as a service (PAAS) offering. Regardless of what service you want to deploy, you should always check for the availability of a service at a particular data center location.

The Get-AzureRmResourceProvider cmdlet can be filtered to list all data centers for a given service. Executing the PowerShell script below identifies which data centers we can deploy Azure Analysis Service to.

# 
# Data Centers with my service 
# 
  
# Get data centers with Azure Analysis Services 
$AzureLocations = (Get-AzureRmResourceProvider -ListAvailable | ` 
    Where-Object {$_.ProviderNamespace -eq 'Microsoft.AnalysisServices'}).Locations 
  
# Show the results 
$ AzureLocations 

Output from PowerShell script points out that East US 2 location is the data center closest to Boston, MA.

Choosing a data center - Description: The Get-AzureRmResourceProvider cmdlet lists all locations with a service.

Resource Group

The concept behind the Resource Manager model is that all Azure components that make up a solution are created or destroyed as a group. Thus, the key object that binds all the items together is a resource group.

When working with resource groups, there are three cmdlets you should know:

The PowerShell script below creates a resource group named rg4tips18 in the East US 2 location.

# 
# Create a resource group 
# 
  
# New resource group 
New-AzureRmResourceGroup -Name "rg4tips18" -Location "East US 2" 
  
# List resource groups 
Get-AzureRmResourceGroup 

Output for PowerShell script showing newly created resource group.

Using a resource group - Description: The Get-AzureRmResourceGroup cmdlet lists all resource groups.

Azure Analysis Service (AS)

The PowerShell cmdlets for this service take the usual parameters:  new service name, resource group name, and location name.

When working with this service, there are three cmdlets you should know:

Just like Azure SQL database, you must choose a service tier.  Each tier has a limit on query processing units (QPU) and how much memory the model can use.  The pricing of the service is based on these two metrics. 

The Developer tier has all the possible modeling features, but canít be used for production.  The Basic tier does not support perspectives, partitions, and direct query.  The Standard tier is a fully functional, product ready offering.  I am going to select the developer tier for this deployment.

The PowerShell code below deploys our Azure AS server to the East US 2 location.

# 
# Create the service 
# 
  
# New AAS server 
New-AzureRmAnalysisServicesServer -ResourceGroupName "rg4tips18" ` 
   -Name "aas4tips18" -Location "East US 2" -Sku D1 
  
# Clear the screen 
Clear-Host 
  
# List Azure AS server 
Get-AzureRmAnalysisServicesServer -ResourceGroupName "rg4tips18" -Name "aas4tips18" 

Output for PowerShell script showing newly created server.

List all AS Servers - Description: The Get-AzureRmAnalysisServicesServer cmdlet lists all AS Servers.

Choosing an Administrator

Right now, we have successfully deployed the Analysis Services server. However, we have not defined an administrator.  I rolled out the server this way to show how tightly bounded the service is to Azure Active directory.

Find the aas4tips18 object in the all resources panel.  Double click and open the object in a new panel.  Scroll down on the left menu to find the Analysis Services Admins option under the settings group.  The image below was taken from the portal.  Double click this option to make changes.

Manage administrators - Description: Use the Azure portal to add or remove administrators.

The next panel shows the two users that are part of my Azure Active directory.  The first user owns the subscription and the second B2B user has been asked to join our domain.  Make the [email protected] account a server administrator.

Current AD Users - Description: The Azure portal can be used to add users.  This picture shows both a subscription owner and B2B user.

If we take a look at the administrator accounts after the selection, we can see a weird account name showing up.  This is due to the fact that the outlook account is external to the domain you defined within your subscription.

External User - Description: Since the email address does not have the same domain name as Active Directory, the user is considered external to the domain.

Here is my first warning to new users of the service.  I have had issues in the past authenticating to the model when an Azure subscription domain was used.  I think this occurs when you log into a local Corporate domain and try to use a separate Azure domain at the same time.  I have successfully deployed models when the Corporate domain is federated (synchronized) with the Azure domain.

If we execute the Get-AzureRmAnalysisServicesServer cmdlet again, we can see this outlook account listed as an administrator.

List AS Server Settings - Description: The Get-AzureRmAnalysisServicesServer cmdlet can be used to list important server settings.

Creating a Sample Model

One nifty feature of the Azure Portal is the ability to deploy a sample model.  If we navigate to the manage panel and choose new model, we can select sample data.

Managing models via the portal - Description: The portal can be used to manage BI models.

Upon clicking the add button, the adventureworks model is deployed to the server.

Sample BI Model - Description: The adventureworks tabular model can be deployed to test the service.

Let us start using the new sample model using a familiar tool.

Management Studio (SSMS)

SQL Server Management Studio is the chosen tool of administrators to manage all the following types of servers: database engine, integration services, reporting services and analysis services.  Make sure you have the latest release of this tool.  As of today, version 17.4 is the latest version that can be downloaded here.

SSMS Login Prompt - Description: Select the analysis services option with the correct server and user name.

Copy the server name off the overview panel in the Azure Portal.  Supply this internet address as the server name.  I chose to use Active Directory authentication with MFA support.  Enter in the email address and user name at this time

If you successfully connect, you should see the adventureworks model.  See image below.  This model has seven tables and three different roles.  Since we are not doing any management today, please close SQL Server Management Studio at this time. 

SSMS - Analysis Services - Description: We can manage a Azure AS server with SSMS.

I usually explore the data inside the model using either Microsoft Excel or Power BI.  Let us try both those tools next. 

Model exploration with Microsoft Excel

I am going to create a spreadsheet named AdvWrk.xlsx in my local directory.  I am using the 32 bit version of Microsoft Office 2016. 

MS Excel File - Description: Local spreadsheet for exploration.

It is suggested that you download and install the latest client drivers from here.  There are three client libraries:  AMO, ADOMD, and MSOLAP.

The next logical step is to get an external data connection for Azure Analysis services.  The image below shows how use the data menu and tool bar to get external data.

MS Excel File - Description: Use the get external data option to pull in Azure AS data.

Here is my second warning to new users of the service.  The new client drivers, MSOLAP 8.0, do not interface correctly with Microsoft Office 2016.  There are some forum threads you can look at which suggest a registry hack for a given CLSID.  This change should be handled by the client driver install!

Drivers do not work with Office - Description: There is an issue with the MS OLAP drivers and MS Office.

If you really need to get this working for your organization, I suggest you put in a support ticket in with Microsoft on day one of your project.

I happen to have a local version of this tabular model installed with SQL Server 2016.  I will use windows authentication when connecting since I know I am a local administrator of both my laptop and analysis services.

Data Connection Wizard - connection info - Description: The drivers with MS Office work with Analysis Services 2016.

The data connect wizard successfully connects to my local model.

Data Connection Wizard - connection info - Description: Choose the database (model) that you want to work with.

The image below shows a pivot table showing internet sales for various States in the region named United States.  If we look real hard at the data, we can identify the top three States that have the most sales. 

MS Excel File - Description: Creating a pivot table from the Azure AS Server data.

In short, I wish I could say that the client drivers worked this easily for the cloud service. 

Model exploration with Power BI

I am going to create a Power BI solution named AdvWrk.pbix in my local directory.  I am using the latest version of the desktop tool.  Again, we want to use the get data menu option to define our connection.  Choose the Azure Analysis Services database option.

Power BI - Get Data - Description: Choose the Azure Analysis Services database.

Paste in the internet address that identifies our Azure Analysis server.  Choose the adventureworks model as a direct query (connect live).

Power BI - Setup Azure AS connection - Description: The dialog box can be used to define the connection.  The direct query, connect live, is used this time instead of pulling down the data.

We need to supply credentials for the connection.  I am going to select the Outlook account for my subscription.  This account is an administrator of Azure Analysis Services.

Power BI - prompting for credentials - Description: Supply the server administrator as the login account.

I am going to look at the same query that we did for the pivot table in Microsoft Excel.  The visuals in Power BI allow the user to gain insight right away from the data.  The tree view is a great visual aid for our data.  Again, I want to find the US States that have the biggest sales last year.

Power BI - Tree view visual - Description: The visual can be used to find the top three states in our internet sales department.

We can see right away that California, Washington and Oregon were the states with the biggest sales numbers.

Next Steps

Today, we deployed an Azure Analysis Service server using the Power Shell cmdlets.  The deployment of the service is straight forward.  I wished that Microsoft allowed standard security with this service.  Why not allow a user name and password over a secured connection?  Instead, the service is tightly bound to Azure Active Directory.  This may or may not cause your problems.

The Azure Portal allows an administrator to deploy a sample model for testing.  The SQL Server Management Studio can be used to manage the model.  Other ways to deploy the model are Visual Studio and SQL Server Data Tools.  I will leave that discussion for another time.

Two popular tools to explore that data within the tabular model are Microsoft Excel and Power BI.  There are issues with the client drivers and Microsoft Office.  This is a real disappointment since a majority of the business community uses this tool.  Power BI has a beta connection for this service and it works like expected.

In conclusion, Microsoft has released a PAAS offering of Analysis Services that might be of use to your company.  There are two perceived defects which might cause issue when you try to deploy your model to the cloud.  The overall goal of companies using this service is either to get the model closer to the data or lower the total cost of ownership:

  • Pausing and resuming the service to save money.
  • Scaling up and down resources to satisfy business requirements.
  • Backing up, restoring and processing the model as day-to-day operations.
  • Using scale-out to distribute queries across multiple servers.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Blue Metal helping corporations solve their business needs with various data platform solutions.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools