Process Azure Analysis Services databases from Azure Automation

Posted by

In my last blog post I showed how to trigger Azure Data Factory (ADF) pipelines from Azure Automation. I also mentioned the option to process an Azure Analysis Services cube from Azure Automation. For example right after your ADF data processing finishes, which will probably be a common use case. In this blog post I show you how you can use the Analysis Services PowerShell provider, also known as SQLASCDMLETS, from Azure Automation.

Create custom SQLASCMDLETS module
The SQLASCDMLETS are not (yet) available in the PowerShell Gallery so unfortunately it’s not possible to import the cmdlets straight into Automation like I did with the ADF cmdlets in my previous blog post. Instead we have to create our own module which will contain the SQLASCMDLETS and its dependencies.

UPDATE: You can now use the SqlServer PowerShell Gallery module. Creating a custom module like described below in the blue text is not required anymore. Instead, follow the 3 steps below and continue reading below the blue text.

1. Navigate to https://www.powershellgallery.com/packages/SqlServer/

2. Click the “Deploy to Azure Automation” button

3. Import the SqlServer module to your Azure Automation account


The required files come with SQL Server Management Studio (SSMS) which you can download and install for free. It’s important to note you need the latest version (140) of the SQLASCDMLETS which is shipped with the latest Release Candidate of SSMS. Download and install it.

If you try to use the previous version of the SQLASCDMLETS (130) you will get an error in Automation because it tries to authenticate with a claims token while only windows authentication is supported by the 130 version of SQLASCDMLETS: “The value ‘ClaimsToken’ is not supported for the connection string property”.

After installing SSMS you should now be able to see the following directory: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLASCMDLETS

Copy the SQLASCMDLETS folder to a temporary location, for example C:\SQLASCMDLETS.
You will need the following files:

    • Microsoft.AnalysisServices.PowerShell.Cmdlets.dll
  • SQLASCMDLETS.PSD1

We also need the libraries SQLASCMDLETS depends on. Search your computer for the following files and copy paste them to the C:\SQLASCMDLETS folder. Make sure you copy them from a folder that has “140” in the path so you are sure you have the correct version.

    • Microsoft.AnalysisServices.Core.dll
  • Microsoft.AnalysisServices.Tabular.dll
  • Microsoft.AnalysisServices.dll

Now zip the entire folder, make sure the name is “SQLASCMDLETS.zip”.
Import custom SQLASCMDLETS module to Azure Automation
Navigate to your Azure Automation account in the Azure portal.
Click Assets:
Click Modules:
Click Add a module to import a custom module:
Now upload the SQLASCMDLETS.zip file:
The zip file will be extracted:
Wait until the extraction finished and the status changes to Available. Click on the module name:
You now see the available activities including the one we will use to process the Azure Analysis Services Database.


 Create Azure Automation Credential
Now we need to create a Credential to be able to automatically login and run our PowerShell script unattended from Azure Automation.

Navigate to Assets again and then click Credentials:
clip_image016

Click “Add a credential” and enter an organization account that has permissions to process your Azure Analysis Services database. Make sure you enter the User Principal Name (UPN) and not a Windows AD account. It is often the email address and may look like jorgk@yourorganizationalaccountname.com. Give the new Credential a name, I chose “adpo-auto-cred”. It will be referenced in the PowerShell script below.
clip_image018


Create Automation Runbook
You can use the simple PowerShell script below to process your Azure Analysis Services database from Azure Automation. It will use the “adpo-auto-cred” credential to authenticate and will process your database using the Invoke-ProcessASDatabase SQLASCMDLETS function.
Replace “dbname” with your database name and “server” with your server, e.g. asazure://westeurope.asazure.windows.net/yourserver and you are good to go.

Copy/paste the script below to a Windows PowerShell Script (.ps1) file and name it “ProcessASDatabase.ps1”.


$AzureCred = Get-AutomationPSCredential -Name “adpo-auto-cred”

Add-AzureRmAccount -Credential $AzureCred | Out-Null

Invoke-ProcessASDatabase -databasename “dbname” -server “server” -RefreshType “Full” -Credential $AzureCred 


Navigate to your Azure Automation account in the Azure Portal and click “Runbooks”:
clip_image020

Click “Add a runbook”:
clip_image022

Click “Import an existing workbook” and select the ProcessASDatabase.ps1 file to import the PowerShell script as Runbook:
clip_image024

Runbook ProcessASDatabase is created. Click it to open it:
clip_image026

A Runbook must be published before you are able to start or schedule it. Click Edit:
clip_image027

Before publishing, test the Runbook first. Click on the “Test pane” button and then click Start:
clip_image029

The script executed successfully:
clip_image031

Connect to your Azure AS sever with SSMS and check the database properties to be sure processing succeeded:
clip_image033

Now publish the Runbook:
clip_image035

That’s it, you now have an Azure Automation Runbook that you can schedule, monitor and integrate with your other data platform related tasks!

23 comments

  1. I’ve tried installing both the 16.5.3 and the 17RC1 versions of SSMS and while I get the “C:\Program Files (x86)\Microsoft SQL Server\140\Tools” directory, there is no “PowerShell” directory

    Like

  2. Hi Ryan,
    If you search your computer for file “SQLASCMDLETS.PSD1”, do you find it in some folder with “140” in the path? If you do, you can take the files from there.

    Like

  3. I could get the dll’s from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLASCMDLETS and C:\Windows\Microsoft.NET\assembly\GAC_MSIL
    But strange part is when i add module to Azure automation, Extracting activities fail with below error.
    Error extracting the activities from module SQLASCMDLETS. Extraction failed with the following error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException:
    While importing the module, an error occurred while processing the module content. Internal error message:
    Could not load file or assembly ‘Microsoft.AnalysisServices.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified..
    Can any one help?

    Like

  4. I try to import the module but I get his “Activity Extraction Failed” message:
    Error extracting the activities from module SQLASCMDLETS. Extraction failed with the following error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: While importing the module, an error occurred while processing the module content. Internal error message: Could not load file or assembly ‘Microsoft.AnalysisServices.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified..
    Any idea why?

    Like

  5. I installed new SSMS from above mentioned link but cant find 140 directory under Microsoft SQL Server in Program Files(x86) as well as Program Files.

    Like

  6. I wanted to say thanks Jorg. I got this to work great.  The first problem I had was getting the right files for the module.  With the various versions of SSMS I had running I was including files from different versions.  I narrowed that problem and all the files I used had the same date. Once I got through that my issue was entirely in a typo of mine.  Anyway, Thanks!

    Like

  7. This thing works only problem i am facing is script once executed in Azure is not ending model gets refreshed but script is still on going. Can anyone help me for this?

    Like

  8. Great article, works like a charm! the only thing is that location of the DLLs changed to C:\Windows\Microsoft.NET\assembly\GAC_MSIL
    Thanks!

    Like

  9. This article was extremely helpful for automating our cube processing in Azure. There isn’t a lot of helpful resources out right now, but this was exactly what we needed. To fit our use case, I had to change the command to Invoke-ASCmd and execute a TMSL query on the tabular model.

    Like

  10. I try to import the module but I get the following error:
    “Error extracting the activities from module SQLASCMDLETS. Extraction failed with the following error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: Failure processing module import runbook while getting module content. Error: End of Central Directory record could not be found..”
    Has any one a idea?
    thx!

    Like

  11. Excellent blog, this is exactly what i needed in order to get some scripting working for automated Azure AS deployment.
    Cheers!

    Like

  12. Hi Jorg,
    I am able to process my cube using the above said setup. Would like to know how to send the output of runbook to email in case of a failure ?

    Like

  13. I did not get the Microsoft.AnalysisServices.PowerShell.Cmdlets.dll
    SQLASCMDLETS.PSD1 files in SQLASCMDLETS fiolder

    Like

  14. Hey,
    I did follow all the steps you mentioned, however I always end up with this error:
    Invoke-ProcessTable : Exception has been thrown by the target of an invocation.
    At line:25 char:1
    + Invoke-ProcessTable -Credential $AzureCred -DatabaseName DBNAME -R …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       + CategoryInfo          : NotSpecified: (:) [Invoke-ProcessTable], TargetInvocationException
       + FullyQualifiedErrorId :
    System.Reflection.TargetInvocationException,Microsoft.AnalysisServices.PowerShell.Cmdlets.ProcessTable
    Do you have any idea on what it may cause?

    Like

  15. When i tried testing the runbook it gave me this error :
    Invoke-ProcessASDatabase : A connection cannot be made. Ensure that the server is running.
    At line:5 char:1
    + Invoke-ProcessASDatabase -databasename “MultidimensionalProject2” -se …

    Like

  16. How can we achieve the same using a service principal instead of user principal? I run into some errors when I try to use a service principal.

    Listing down what I have done if that would be helpful –

    1. App created and assigned to AAS, made the App as an admin on Analysis Services Server.
    2. Created credential based on app ID and key
    3. Called the credential in the powershell script
    4. Logging onto Azure and then calling Invoke-ProcessASDatabase command to process

    Error Details –
    Invoke-ProcessASDatabase : Exception has been thrown by the target of an invocation.
    + CategoryInfo : NotSpecified: (:) [Invoke-ProcessASDatabase], TargetInvocationException
    + FullyQualifiedErrorId :
    System.Reflection.TargetInvocationException,Microsoft.AnalysisServices.PowerShell.Cmdlets.ProcessASDatabase

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s