Refresh Cube using Function App

Function Apps (Option 1)

Azure Function is a server-less compute service that enables you to run code on-demand without having to explicitly provision or manage infrastructure. Use Azure Functions to run a script or piece of code in response to a variety of events.

Azure Menu Item – Function Apps

Default settings – Create a Function App for Automating Azure Analysis Services processing.

Create an Azure Function App –

1. Go to Azure Portal and create a new Function App.
2. Type a unique Name for your function, choose the Resource Group and Location and choose the best Hosting Plan.

*Azure Function can run in two different modes: Consumption plan and Azure App Service Plan. The Consumption plan is the default hosting plan and Azure provides all of the necessary computational resources. In the App Service Plan, Function App run on dedicated VM’s on Basic, Standard, Premium and Isolated SKUs.

3. Click Create to deploy the Function app.

Create a new Function

1. In Azure menu, click on Function Apps. Expand Function App that have been created. Click on Plus to create a new Function. Select Timer and click Create this function.

Configure application settings:

To configure application, download the latest data providers on local machine.

1. Download the latest client libraries for Analysis Services.
2. After installing the providers, these two files are necessary for the next step:
   

C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.AnalysisServices.Core.DLL
C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.AnalysisServices.Tabular.DLL

3. Select Function App, click Platform features and under Development Tools, click Advanced tools (Kudu).

4. In Kudu, click Debug console and select Cmd. Navigate to the site\wwwroot\TimeTriggerCSharp1 folder and add the “Bin” folder here by clicking the + button.

5. Select function TimerTriggerCSharp1 and expand the View files windows. Open the Bin folder and click on Upload button to add the two previously mentioned DLLs to the Bin folder.

Add the connection string for Azure Analysis Services database

Connection string includes Azure Analysis Service server name and a user ID and password that has access to the Azure Analysis Service database.

1. Select Function app, click Platform features and under General Settings , click Application Settings.

2. Under the Application Settings navigate to the Connection string and click on Add new connection string to add custom connection string. After entering the Connection string click Save near the top. Connection string should look like –
   Provider=MSOLAP;Data Source=asazure://region.asazure.windows.net/servername; Initial Catalog=dbname;User ID=user@domain.com; Password=yourPass;

Configure the Timer

1. Expand the function. Go to Integrate > Timer > Schedule. The Schedule textbox expects a CRON expression to define the days and times that the function should execute. The default name for the Timer is myTimer and the default schedule has a CRON expression for every 5 minutes. Documentation section includes description and examples of CRON expressions.

Scheduling a Full Tabular Model Refresh

To programmatically process the tabular model select the Function and in the run.csx window paste these code and click Save.

#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 < TheNameOfYourTimer >, 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[“YourConnectionName”].ConnectionString;
asSrv.Connect(connStr);
Database db = asSrv.Databases[“YourDatabaseName”];
Model m = db.Model;
db.Model.RequestRefresh(RefreshType.Full); // Mark the model for refresh
m.RequestRefresh(RefreshType.Full); // Mark the model for refresh

db.Model.SaveChanges(); asSrv.Disconnect(); }
catch (Exception e)
{
log.Info($“C# Timer trigger function exception: {e.ToString()}”);
}
log.Info($“C# Timer trigger function finished at: {DateTime.Now}”);
}

Scheduling a Partial Tabular Model Refresh

To programmatically process the table or group of the tables, select the Function and in the run.csx window paste these code and click Save.

*In one try catch block you can mark only one table for refresh.

Code to process only one table:

#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 < TheNameOfYourTimer >, 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[“YourConnectionName”].ConnectionString;
asSrv.Connect(connStr);
Database db = asSrv.Databases[“YourDatabaseName”];
Model m = db.Model;
m.Tables[“YourTableName”].RequestRefresh(RefreshType.Full); //Mark only one table for refresh

db.Model.SaveChanges();
asSrv.Disconnect();
}
catch (Exception e)
{
log.Info($“C# Timer trigger function exception: {e.ToString()}”);
}
log.Info($“C# Timer trigger function finished at: {DateTime.Now}”);
}

Code to process group of the tables –

#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 , 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[“YourConnectionName”].ConnectionString;
asSrv.Connect(connStr);
Database db = asSrv.Databases[“YourDatabaseName”];
Model m = db.Model;
m.Tables[“YourTableName”].RequestRefresh(RefreshType.Full); //Mark only one table for refresh
db.Model.SaveChanges();
asSrv.Disconnect();
}
catch (Exception e)
{
log.Info($“C# Timer trigger function exception: {e.ToString()}”);
}
try
{
Microsoft.AnalysisServices.Tabular.Server asSrv = new Microsoft.AnalysisServices.Tabular.Server();
var connStr = ConfigurationManager.ConnectionStrings[“YourConnectionName”].ConnectionString;
asSrv.Connect(connStr);
Database db = asSrv.Databases[“YourDatabaseName”];
Model m = db.Model;
m.Tables[“YourTableName”].RequestRefresh(RefreshType.Full); //Mark only one table for refresh
db.Model.SaveChanges();
asSrv.Disconnect();
}
catch (Exception e)
{
log.Info($“C# Timer trigger function exception: {e.ToString()}”);
}
log.Info($“C# Timer trigger function finished at: {DateTime.Now}”);
}

Scheduling a Partition based Refresh

To programmatically process partition, select the Function and in the run.csx window paste these code and click Save –

p{font-family:Courier New;color:green;font-size:85%;}.#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 , 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[“YourConnectionName”].ConnectionString;
asSrv.Connect(connStr);
Database db = asSrv.Databases[“YourDatabaseName”];
Model m = db.Model;
m.Tables[“YourTableName”].Partitions[[“YourPartitionName”].RequestRefresh(RefreshType.Full);
db.Model.SaveChanges();
asSrv.Disconnect();
}
catch (Exception e)
{
log.Info($“C# Timer trigger function exception: {e.ToString()}”);
}
log.Info($“C# Timer trigger function finished at: {DateTime.Now}”);
}