How to Automate Processing of Azure Analysis Services Models

I’ve been working on a proof of concept for a customer that involved using Azure Analysis Services as a cache for some data in an Azure Data Warehouse instance. One of the things I’ve been working on is scheduling the automatic processing of the Azure AS database. I did find the following documentation on the process, but the screenshots of the Azure portal are out of date and I did find some errors in the instructions. I also found this very extensive project for partition management in Azure AS, but this was a little overkill for my purposes and I was just interested in the very basics.

These previously mentioned resources led me to write this blog post. In this post I’m going to leverage the previously mentioned article and walk through creating an Azure Function App to automatically refresh my Azure Analysis Services model, while correcting a few errors and updating the screenshots.

If you’re new to Azure Analysis Services, take a look at this documentation. For the purposes of this post, I’m going to assume you have a basic understanding of Analysis Services.

I created a Tabular Model with a connection to an Azure SQL Database with one table that had a couple columns. I visualized the data in Power BI so I could verify the data was being refreshed after I processed the Azure AS database.

The Steps for Automating Processing of an Azure Analysis Services Model

1. Create an Azure Function App

The first step is to create an Azure App Function. Navigate to the Azure portal and create a Function App.

2. Create a new Function

After you’ve created the Function App, we need to add a new Timer function. Click the + button next to Functions, select Timer, and click Create this function.

3. Configure the Timer

Give your Timer a name by filling in the textbox for Timestamp parameter name. The default name is myTimer, but you can change this. Just make sure you remember what the name of your timer is because we will need it later.

The Schedule text box expects a CRON expression to define the days and times that the function should execute. Click the little Documentation button on the screen above to read about CRON expressions. But a CRON expression similar to what I’m using would execute the script every 4 hours of every day. Click Save when you’re done.

Paste in the following code (you can also download the .cs file I used with the script here). The highlighted sections of the code represent the piece you need to change specific to your function app. The green text represents the commands you should change based on the type of processing operation you wish to execute. I’m processing the whole model with a ProcessFull command so I’ve left the first green line uncommented. Just a note of caution here: If you’re copying and pasting my code here as an example, make sure you check that all the characters copy and paste correctly so you don’t bang your head against a wall for a couple hours like Mike.

That’s a good question. I haven’t tried that yet but I know from experience that you can’t use a SQL Agent Job to process an AAS database using a stored credential, proxy account, and an Analysis Services command. But the SSIS Analysis Services command would be something to try.

Thanks for this write up. It was extremely helpful. There are two areas where I encountered issues implementing based on your logic and sample code. I thought I would share my experience to save someone else some headaches.

1. Adding the bin folder for the SSAS assemblies – Pressing “add” thru the file maintenance in Azure only created a bin file but did not provide an option to create a bin folder. Attempting to upload the SSAS assemblies then dropped them into the function directory at the same level as the run.csx. Opening the site thru the diagnostic dashboard in KUDU allowed me to create an actual bin folder. I then went back to the file maintenance pane and was able to upload the SSAS assemblies into the newly created bin folder.

2. Copying your sample code for run.csx from your blog was a quick short cut, but I started geting lots of errors, such as “Quoted file name expected” when included the assembly references. It turns out that copying the code from the blog changed the double quotes to something that the C# editor did not recoginize. In the code snippet below, the first entry works, but the quotes are subtlety different on the second, pasted entry.

I’ve hit this before but it was about 2 hours of swearing before I remembered the problem with pasting code and identified the culprit. Changing the double quotes cleared up almost all of the compile errors.

Thx again, hope this helps some one else avoid a couple of wasted hours.

Thanks for the feedback, Mike. This is a challenge with pasting code directly into the blog. I had a LiveWriter plugin that I was using for code snippets but I’ve lost it and can’t seem to find it anymore.