Automatically pause/resume and scale up/down Azure Analysis Services using AzureRM.AnalysisServices

One of the big advantages of Azure Analysis Services is the ability to pause/resume and scale up/down as needed, this will allow you to pay only for what you use and greatly reduce costs.

Azure Analysis Services team released a PowerShell module “AzureRM.AnalysisServices” with cmdlets to manage your Azure Analysis Services resources and they could be more easy to use:

  • Get-AzureRmAnalysisServicesServer – To get your server metadata and current status
  • Suspend-AzureRmAnalysisServicesServer – To suspend a server
  • Resume-AzureRmAnalysisServicesServer – To Resume a server
  • Set-AzureRmAnalysisServicesServer – To update a server, ex: change the SKU

More details here.

But this is only effective if we somehow automate this operation, it’s not feasible if someone on the team or customer is actively pausing/resuming or scaling up/down the instance

With that in mind we build a very simple PowerShell script where you configure in which time and days the Azure AS should be on and on which SKU.

Download the full script here.

The script is configured by a JSON metadata:

image

The above metadata will configure Azure AS to:

Days Hours SKU
Mon-Fri 8 AM to 18 PM (peak hours) S1
Mon-Fri 18 PM to 00 AM (off peak) S0
Sat-Sun 8 AM to 00 AM S0
Other Other Pause

The powershell script has the following parameters:

-resourceGroupName The name of the Azure Resource Group your Azure AS server is deployed:

clip_image002[5]

-serverName The name of the Azure AS Server:

clip_image004

-configStr The JSON metadata config string
-azureProfilePath

(optional)

The path to an Azure profile stored locally using the “Save-AzureRmContext” cmdlet.

This is useful to test the script locally.

-azureRunAsConnectionName

(optional)

The name of Azure Connection if you want to run this script in a Azure Automation RunBook:

clip_image006

Probably most of you will want to run this script on an PowerShell Runbook in Azure Automation, learn how to setup the Azure Automation here.

Next you will need to register the module “AzureRM.AnalysisServices” as an Asset of your automation account:

clip_image002[7]

After that just create a new PowerShell runbook:

clip_image004[4]

Paste the script and remember to set the parameter -azureRunAsConnectionName:

clip_image006[4]

Publish the script and create a schedule:

clip_image008

clip_image010

That’s it! You know have your Azure AS automatically pausing/resuming and scalling up/down using a configuration file you defined.

Now just lay back and measure the savings at the end of the month!

Automate Azure Analysis Services Pause/Resume using PowerShell

This is a guest post from my colleague Filipe Sousa from DevScope who will share is very recent findings in automating management tasks in Azure Analysis Services, take it away Filipe:

Recently we came across the need to use one of the newest Azure services – Azure Analysis Services (AS). This lead us to an awesome Software as a Service (SaaS), dazzling query speed, stunning scalability…and a new administration paradigm, administer SaaS in the cloud.

Since Azure Analysis Services is charged hourly and we know that we will not use the service 24/7, how could we automate the pause/resume feature of the Azure Analysis Service so that we could optimize savings?

Couldn’t be more straightforward, except for some lack of documentation/examples, thanks Josh Caplan for pointing us in the right direction: Azure Analysis Services REST API

First, and so that the REST calls to the ARM API can be authenticated, we need to create an app account in the Azure AD. This can be done manually, as a standalone act or, better yet, as part of an Azure Automation Account with a Run as Account creation. The last will deploy a new service principal in Azure Active Directory (AD) for us, a certificate, as well as assigns the contributor role-based access control so that ARM can use it in further runbooks.

Recap, we will need:

An Azure Automation Account so that we can have:

· Runbook(s) – for the exercise, specifically a powershell runbook;

· A run as account so that the script can authenticate against Azure AD;

· Schedules to run the runbook.

This is how you can achieve it:

clip_image002

(If you already have automation account and don’t have a run as account, create an Application Account in Azure AD.)

Having created the azure automation account, we can peek at the new run as account with the service principal already created for us:

clip_image004

Additionally, we can take the opportunity to gather the application, tenant and subscription id’s, it will serve us latter.

Having the Automation Account in Place is time to create a key for it, go to your app account in Azure AD, in the all settings menu select keys and create a new key with the desired duration. Copy the key value and save it somewhere safe, you won’t be able to get it later!

clip_image006

For now, all we have to do is to collect:

· ApplicationID: in Azure AD –> App Registratons –> The name of app we just created

· Application Key: Collected from the previous steps

· TennantID: Azure Active Directory –> Properties –> Directory ID value

· SubscriptionID: From the Azure URL: https://portal.azure.com/#resource/subscriptions/e583134e-xxxx-xxxx-xxxx-bb8c398a8116/…

· Resource group name: From the Azure URL: https://portal.azure.com/…/resourceGroups/xxxResourceGroup/…

· SSAS server name: Analysis Services -> YourServerName

Having those, replace this values in the below script and save it somewhere for now – we encourage you to develop and test your powershell scripts in powershell IDE –, and yes, this script will also work in an on-premises machine.



#region parameters
    param(
            [Parameter(Mandatory = $true)]
            [ValidateSet('suspend','resume')]
            [System.String]$action = 'suspend',

            [Parameter(Mandatory = $true)]
            [System.String]$resourceGroupName = 'YouResourceGroup',

            [Parameter(Mandatory = $true)]
            [System.String]$serverName = 'YourAsServerName'
    )
#endregion


#region variables 
    $ClientID       = 'YourApplicationId'
    $ClientSecret   = 'YourApplicationKey'
    $tennantid      = 'YourTennantId' 
    $SubscriptionId = 'YourSubsciptionId'
#endregion
 

#region Get Access Token
    $TokenEndpoint = {https://login.windows.net/{0}/oauth2/token} -f $tennantid 
    $ARMResource = "https://management.core.windows.net/"

    $Body = @{
            'resource'= $ARMResource
            'client_id' = $ClientID
            'grant_type' = 'client_credentials'
            'client_secret' = $ClientSecret
    }

    $params = @{
        ContentType = 'application/x-www-form-urlencoded'
        Headers = @{'accept'='application/json'}
        Body = $Body
        Method = 'Post'
        URI = $TokenEndpoint
    }

    $token = Invoke-RestMethod @params
#endregion


#region Suspend/Resume AS -> depending on the action parameter
    
    #URI TO RESUME
    #POST /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.AnalysisServices/servers/{serverName}/resume?api-version=2016-05-16

    #URI TO SUSPEND
    #POST /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.AnalysisServices/servers/{serverName}/suspend?api-version=2016-05-16

    $requestUri = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.AnalysisServices/servers/$serverName/$action ?api-version=2016-05-16"

    $params = @{
        ContentType = 'application/x-www-form-urlencoded'
        Headers = @{
        'authorization'="Bearer $($Token.access_token)"
        }
        Method = 'Post'
        URI = $requestUri
    }

    Invoke-RestMethod @params

#endregion

With the powershell script assembled – note that one of script parameters is the action (suspend/resume), that we want the script to execute against the SSAS – the next steps are:

· Create a runbook within the early created automation account with the type powershell, paste the previous script, save it and…voilà, ready to test, publish and automate!

clip_image008

· Next step is to publish the runbook so that it can be used in a schedule, fully automating the suspend/resume procedure. After publishing the runbook, create/assign it schedules – one to suspend and other to resume the AS server:

clip_image010

Afterwards configure the desired script parameters for each schedule:

clip_image012

The final result should look like this and give us the desired suspend/resume Azure AS automation.

clip_image014

Hope that you’ve learned from our post, have a nice Azure automation, leave your comments below!

Filipe Sousa

Excel Automation, Sync all DataSources Synchronously

Another day doing some Excel Automation i needed to Refresh All Workbook connections, but do it synchronously, and avoiding the nasty error “Call was rejected by Callee”.

As far as i know this error appens becouse when you do a RefreshAll() on the book some connections refresh assynchronously and when you try to access a worksheet it will launch that error.

The solution for my case was foreach all the workbook connections change the Background query property to false and doing the Refresh.

Code:

foreach (Excel.WorkbookConnection conn in book.Connections)
{
    if (conn.Type == Microsoft.Office.Interop.Excel.XlConnectionType.xlConnectionTypeOLEDB)
    {       
        if (!conn.OLEDBConnection.OLAP)
            conn.OLEDBConnection.BackgroundQuery = false;
    }
    else if (conn.Type == Microsoft.Office.Interop.Excel.XlConnectionType.xlConnectionTypeODBC)
    {
        conn.ODBCConnection.BackgroundQuery = false;
    }

    conn.Refresh();
}

excelApp.CalculateUntilAsyncQueriesDone();

Notes

  • Checking BackgroundQuery on OLAP gives me a ArgumentException, but in my testings the refresh is done synchronously even without changing this property.
  • Reading the MSDN documentation it should be sufficient to execute the “CalculateUntilAsyncQueriesDone();” after the RefreshAll but it didnt work for me… But i still call this method on the end.

Hope it helps.

Excel Automation Locale Issues

If you are doing Excel Automation, and are having errors like "Exception from HRESULT: 0x800A03EC" or "Old format or invalid type library" probably it’s a location problem.
 
One solution is to force a culture change when using excel objects, but there’s a better and simplified way, that only requires to instantiate the Excel.Application object like this:
 

Excel.Application excelApp = (Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), new Excel.Application());

With this all the comunication with excel via this object will be done in locale 1033, it basically creates a proxy that comunicate with Excel always in locale 1033 like in VBA.

For more detail check:

http://blogs.msdn.com/eric_carter/archive/2005/06/15/429515.aspx

http://blogs.msdn.com/eric_carter/archive/2005/08/25/453680.aspx