Processing an Azure AS Database

This post contains a list of various methods that can be used to process (i.e. load data into) an Azure AS tabular model. As you will see – not much has changed from the regular on-premise version (which is a very good thing as it softens the learning curve).

Note: In these examples, I’m using a very basic model comprised of 3 tables from the Adventure Works database running on a local (on-premise) VM – so in order for this to work (i.e. Azure AS model processing data from on-prem SQL database) I first had to install the AzureASGateway.

Management Studio – GUI

Management Studio – JSON/TMSL

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "AdventureWorksAzureAS"
      }
    ]
  }
}

PowerShell

Again, same as it ever was… just remember to use the new syntax/arguments specific for 1200-compatibility models (i.e. RefreshType instead of ProcessType)… if you don’t you will get an error.

Invoke-ProcessASDatabase `
    -Server "asazure://southcentralus.asazure.windows.net/ssas01" `
    -DatabaseName "AdventureWorksAzureAS" `
    -RefreshType Full

Invoke-ProcessASDatabase `
    -Server "asazure://southcentralus.asazure.windows.net/ssas01" `
    -DatabaseName "AdventureWorksAzureAS" `
    -RefreshType ClearValues

Or, alternatively, you can use the Invoke-ASCmd function w/ a TMSL command (like the one shown above in the management studio section)…which in my opinion isn’t quite as pretty but may save some time if you’re already using TMSL commands.

Invoke-ASCmd `
    -Server "asazure://southcentralus.asazure.windows.net/ssas01" `
    -Database "AdventureWorksAzureAS" `
    -Query "{
              `"refresh`": {
                `"type`": `"full`",
                `"objects`": [
                  {
                    `"database`": `"AdventureWorksAzureAS`"
                  }
                ]
              }
            }" 

.NET

In some cases it will make sense to use .NET (w/ TOM) – for example if you’re dealing with a complex partitioning scenario and already have the .NET skills in your toolbox.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.AnalysisServices.Tabular;

namespace ProcessASDatabase_CSharp
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                Server as_svr = new Server();
                String as_conn_str = "Provider=MSOLAP;Data Source=asazure://southcentralus.asazure.windows.net/ssas01;";
                as_svr.Connect(as_conn_str);

                Database as_db = as_svr.Databases.FindByName("AdventureWorksAzureAS");

                as_db.Model.RequestRefresh(RefreshType.Full);
                as_db.Model.SaveChanges();

            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
                Console.ReadKey();
            }

        }
    }
}

For a more elaborate example (from someone who actually knows how to write .NET code) check out Christian Wade’s AsPartitionProcessing project over on GitHub.

Quick Word on Scheduling

In more traditional scenarios, processing will continue to be handled the same as it always was… e.g. scheduled agent job (on-prem or Azure IaaS), SSIS, etc. However, for those who’ve already made a break for the cloud, there are few new options worth considering.

Azure Data Factory

This is a cloud-based data integration service that automates the movement and transformation of data. The Data Factory service creates data integration solutions that can ingest data from various stores, transform and process the data, and publish the result data back to the data stores.

Long story short, if you’re building a complex data solution in Azure, you’ll most likely be using this feature to coordinate. Processing an Azure AS database will hook right in just like any other step… .NET, Azure PowerShell, etc.

Here are a few references to help get you started:

• https://docs.microsoft.com/en-us/azure/data-factory/data-factory-introduction
• https://docs.microsoft.com/en-us/azure/data-factory/data-factory-sdks
• Orchestrate Azure Data Factory pipelines and other Azure Data Platform management tasks using Azure Automation

Azure Functions

This is a new – and very interesting – option that I saw for the first time in the Azure AS presentation at PASS Summit last month. It’s described as an event-based serverless compute experience to accelerate your development that can scale based on demand where you pay only for the resources you consume.

Check back in the coming weeks (once I’ve had more time to play/explore/learn).  In the meantime, here are a few references to help you get started:

• https://functions.azure.com
• https://azure.microsoft.com/en-us/get-started/ (see very bottom: “Serverless Computing”)