Automating Azure Analysis Services processing with Azure Functions

In this post, we’ll walk through a simple example on how you can setup Azure Functions to process Azure Analysis Services tables.

Azure functions are perfect for running small pieces of code in the cloud. To learn more about Azure Functions, see Azure Functions Overview and Azure Functions pricing.

Create an Azure Function

To get started, we first need to create an Azure Function

1. Go to the portal and create a new Function App.

2. Type a unique Name for your new function app, choose the Resource Group and Location. For the Hosting plan use App Service Plan.

Note: As the duration of processing Analysis Services tables and models may vary, use a Basic or Standard App Service Plan and make sure that the Always On setting is turned on, otherwise the Function may time out if the processing takes longer.

Click Create to deploy the Function App.

3. In the Quickstart tab, click Timer and C#, and then click Create this function.

Configure timer settings

Now that we’ve created our new function, we need to configure some settings. First, let’s configure a schedule.

1. Go to the Integrate > Timer > Schedule.

The default schedule has a CRON expression for every 5 minutes.

Change this to any setting you would like. In the example below I used an expression to trigger the function at 3AM every day. Click Documentation to see a description and some examples for CRON expressions.

2. Click Save.

Configure application settings

Before we begin writing our code, we need to configure the application.

Important: Make sure you have the latest data providers installed on your computers. To get more info and download, see Data providers for connecting to Azure Analysis Services.

After installing the providers, you’ll need these two files in the next step:

C:Program FilesMicrosoft SQL Server130SDKAssembliesMicrosoft.AnalysisServices.Core.dll
C:Program FilesMicrosoft SQL Server130SDKAssembliesMicrosoft.AnalysisServices.Tabular.dll

1. In the new Azure Function, go to Function app settings > Go to Kudu, to open the debug console.

2. Navigate to the function in D:homesitewwwrootyourfunctionname, and then create a folder named bin.

3. Navigate to the newly created bin folder, and then drop the two files specified in the previews point. It should look like this:

4. Refresh your browser. In Develop > bin, you should see the two files in it (If you don’t see the file structure, click View files).

5. Before we write our code, we need to create a connection string. In Function app settings, click Configure app settings.

6. Scroll to the end of the Application settings view, to the Connection strings section, and then create a Custom connection string.

Provider=MSOLAP;Data Source=asazure://region.asazure.windows.net/servername; Initial Catalog=dbname;User ID=user@domain.com;Password=pw

Add code

Now that we have our function’s configuration settings in-place, we can enter the code. You’ll need to reference the DLLs we uploaded, but other than that it looks like any other .Net code.

Note: In this example, I included some commented lines to only process a table or the model.

#r “Microsoft.AnalysisServices.Tabular.DLL”

#r “Microsoft.AnalysisServices.Core.DLL”

#r “System.Configuration”

using System;

using System.Configuration;

using Microsoft.AnalysisServices.Tabular;

public static void Run(TimerInfo myTimer, TraceWriter log)

{

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

    try

            {

                Microsoft.AnalysisServices.Tabular.Server asSrv = new Microsoft.AnalysisServices.Tabular.Server();

                var connStr = ConfigurationManager.ConnectionStrings[“AzureASConnString”].ConnectionString;

                asSrv.Connect(connStr);

                Database db = asSrv.Databases[“AWInternetSales2”];

                Model m = db.Model;

                //db.Model.RequestRefresh(RefreshType.Full);     // Mark the model for refresh

                //m.RequestRefresh(RefreshType.Full);     // Mark the model for refresh

                m.Tables[“Date”].RequestRefresh(RefreshType.Full);     // Mark only one table for refresh

                db.Model.SaveChanges();     //commit  which will execute the refresh

                asSrv.Disconnect();

            }

            catch (Exception e)

            {

                log.Info($”C# Timer trigger function exception: {e.ToString()}”);

            }

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

}

Click Save to save the changes, and then click Run to test the code. You’ll get an output window where you will be able to see the log information and exceptions.

Learn more on Azure Analysis Services and Azure Functions.