Implementing AS Partition Processing via Azure Functions

Last year, Christian Wade released an open source solution to automate partition management and tabular model processing. As someone who’s spent a lot of time setting up similar solutions for clients over the years, I can assure you this is a great contribution to the community and will save you and/or your clients a ton of time going forward. If you are not already familiar with this open source solution, then you might find the rest of this post difficult to follow… so I urge you to read through the whitepaper. 

Getting Setup in Azure

Before we tackle Azure Functions, let’s get our demo environment setup in Azure:

Azure SQL DB:

• ASPP_AdventureWorksDW: sample data warehouse
• ASPP_ConfigurationLogging: this is database hold the ASPP configuration and logging tables

Azure AS:

• ASPP_AdventureWorks: tabular model that sits on top of our sample data warehouse

Next we’ll use the Sample Client included in the ASPP solution to test our setup.

Step 1: update the App.config file in the SampleClient project

Step 2: run the executable created after building the SampleClient project

In this case, because my data stores are all in azure, I’m unable to use integrated authentication which means I have to enter user/pw for the configuration Azure DB and the target Azure AS instance…

Once that’s done, the rest of the code executes according to the configuration instructions…

Now that we know the sample code works, we just need to “re-create” the functionality of the SampleClient as an Azure Function.

Creating the Azure Function

I’m not going to waste time walking through the process of creating an Azure Function to process an Azure Analysis Services database… Dusty Ryan has already done a great job detailing the steps so start there and come back here after you get that working.

…

Now that you have a timer-triggered Azure Function, the next step is to build the AsPartitionProcessing project…

…which generates the AsPartitionProcessing.dll

This file needs to be uploaded to the same “bin” directory you created in Step 4 of Dusty’s blog post where he had you upload the other 2 Analysis Services DLLs: Microsoft.AnalysisServices.Core.DLL and Microsoft.AnalysisServices.Tabular.DLL

Next, we’ll need to create the following 6 “connection strings” in the Application settings…

…these are not connection strings per se, more like variables that get referenced in function code… this was Step 5 in Dusty’s blog post

Finally, we can replace the Azure Function code w/ the following…

#r "Microsoft.WindowsAzure.Storage"
#r "System.Configuration"
#r "Microsoft.AnalysisServices.Core.DLL"
#r "Microsoft.AnalysisServices.Tabular.DLL"
#r "AsPartitionProcessing.DLL"

using System;
using System.Configuration;
using AsPartitionProcessing;

private static string _modelConfigurationIDs;

public static void Run(TimerInfo myTimer, TraceWriter log)
{

    log.Info($"C# Timer trigger function (ProcessAzureAS) started at: {DateTime.Now}");

    try
    {
        /* read from ASPP_ConfigurationLoggingDB */
        List<ModelConfiguration> modelsConfig = InitializeFromDatabase();

        /* loop through Model Config */
        foreach (ModelConfiguration modelConfig in modelsConfig)
        {
            /* grab user/pw for AzureAS authentication */
            modelConfig.UserName = ConfigurationManager.ConnectionStrings["connstr_AzureAS_Target_USER"].ConnectionString;
            modelConfig.Password = ConfigurationManager.ConnectionStrings["connstr_AzureAS_Target_PW"].ConnectionString;

            /* perform processing */
            PartitionProcessor.PerformProcessing(modelConfig, ConfigDatabaseHelper.LogMessage);
        }
    }
    catch (Exception e)
    {
        log.Info($"C# Timer trigger function (ProcessAzureAS) exception: {e.ToString()}");
    }

    log.Info($"C# Timer trigger function (ProcessAzureAS) finished at: {DateTime.Now}");

}

private static List<ModelConfiguration> InitializeFromDatabase()
{
    ConfigDatabaseConnectionInfo connectionInfo = new ConfigDatabaseConnectionInfo();

    connectionInfo.Server = ConfigurationManager.ConnectionStrings["connstr_ASPP_ConfigurationLogging_SERVER"].ConnectionString;
    connectionInfo.Database = ConfigurationManager.ConnectionStrings["connstr_ASPP_ConfigurationLogging_DB"].ConnectionString;
    connectionInfo.UserName = ConfigurationManager.ConnectionStrings["connstr_ASPP_ConfigurationLogging_USER"].ConnectionString;
    connectionInfo.Password = ConfigurationManager.ConnectionStrings["connstr_ASPP_ConfigurationLogging_PW"].ConnectionString;

    return ConfigDatabaseHelper.ReadConfig(connectionInfo, _modelConfigurationIDs);
}

Now we can test our function by clicking the run button…

…and confirm that it processed the model successfully by checking the log in the ASSP_Configuration database…

Final Thoughts

There are a ton of ways to process Azure AS databases and this may not be the best approach for your specific scenario. Furthermore, you may want to consider using a service principal (the Azure AS equivalent of a managed service account) and adjusting the code accordingly.