SSIS Methods to Process SSAS Tabular Partitions

I’ve been working on a way to process SSAS Tabular Partitions using SSIS. At the current company I’m working at we need to use SSIS to process our tabular models weather it’s a full process on the database or just a partition. This blog focuses on processing a partition because I only want to process the most current data to my model so I base the partition on a date range. I’m using the Adventure Works 2012 tabular model for my example in this article. I want to focus on 2 methods of which either one can be used it’s just based on your comfort with the technology.

The 2 SSIS Methods to process the tabular Model:

  • C# Script using AMO
  • SSIS Analysis Services Execute DDL Task

The first thing I will mention is that you need to look at how the SSAS Tabular Partition is processed manually to see how you can use these 2 methods in SSIS.

1. Get the XMLA (XML for Analysis) from SQL Server Management Studio. To access the SSAS Tabular instance you right click on the table with the partition and select Partitions.

ssas1

Select the Partition you want and the Process button.

ssas2

Select the Process Mode in our case you want to Process Full then select the Script option and choose the Script Action to New Query Window.

ssas3

This gives us the XMLA script that you can execute to process the partition selected.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessFull</Type>
  <Object >
    <DatabaseID>AdventureWorks Tabular Model SQL 2012</DatabaseID>
    <CubeID>Model</CubeID>
    <MeasureGroupID>Internet Sales_fdac9a13-4019-4773-b193-7cca3a4883eb</MeasureGroupID>
    <PartitionID>Internet Sales_eda26cf6-7bda-42ad-b31b-d7dc3ec08e6f</PartitionID>
 </Object >
</Process>

2. Create a C# script within SSIS that will allow you to generate the XMLA script and add variables so that it can be dynamic.
Create several variables to allow us to make dynamic changes to the script because you may change databases, models, or partitions.

ssas5

In our script task you want to add the ServerID, DatabaseID, CubeID, PartitionID, Type, and MeasureGroupID to the ReadOnlyVariables. You also want to add the XMLA variable to the ReadWriteVariables.

The following is a code snippet of the XMLA generated dynamically from the variables in a C# script and sent to the XMLA variable.

public void Main()
{
 try
 {
 StringBuilder xmla = new StringBuilder();
 xmla.AppendLine("<Process xmlns=" + "\"http://schemas.microsoft.com/analysisservices/2003/engine\"" + ">");
 xmla.AppendLine("<Type>" + Dts.Variables["Type"].Value.ToString() + "</Type>");
 xmla.AppendLine("<Object >");
 xmla.AppendLine("<DatabaseID>" + Dts.Variables["DatabaseID"].Value.ToString() + "</DatabaseID>");
 xmla.AppendLine("<CubeID>" + Dts.Variables["CubeID"].Value.ToString() + "</CubeID>");
 xmla.AppendLine("<MeasureGroupID>" + Dts.Variables["MeasureGroupID"].Value.ToString() + "</MeasureGroupID>");
 xmla.AppendLine("<PartitionID>" + Dts.Variables["PartitionID"].Value.ToString() + "</PartitionID>");
 xmla.AppendLine("</Object >");
 xmla.AppendLine("</Process>");

 Dts.Variables["XMLA"].Value = xmla.ToString();
 }
 catch (Exception e)
 {
 Dts.Variables["ScriptError"].Value = e.Message.ToString();
 }

 Dts.TaskResult = (int)ScriptResults.Success;
}

The idea is that you use the StringBuilder library to design our same XMLA script but you add the all the variables in the right places and send it the XMLA variable for use in the next piece.

3. Method 1: C# Script using AMO

With the XMLA script above created you just need a way to execute it. First you need to add the Microsoft.AnalysisServices.XMLA assembly to your script references so you can access the Microsoft.AnalysisServices.Xmla namespace. This can be a pain in the butt to find but I found it on my machine under C:\windows\assembly\gac_msil\microsoft.analysisservices.xmla and I picked the latest version of the microsoft.analysisservices.xmla.dll.

ssas8

ssas10

Finally you just add to the script we used to generate the XMLA script and execute it
with AMO commands. You instantiate the XmlaClient and connect with the Server
variable. Then you execute the send command and disconnect from the server.

public void Main()
{
 try
 {
 StringBuilder xmla = new StringBuilder();
 xmla.AppendLine("<Process xmlns=" + "\"http://schemas.microsoft.com/analysisservices/2003/engine\"" + ">");
 xmla.AppendLine("<Type>" + Dts.Variables["Type"].Value.ToString() + "</Type>");
 xmla.AppendLine("<Object >");
 xmla.AppendLine("<DatabaseID>" + Dts.Variables["DatabaseID"].Value.ToString() + "</DatabaseID>");
 xmla.AppendLine("<CubeID>" + Dts.Variables["CubeID"].Value.ToString() + "</CubeID>");
 xmla.AppendLine("<MeasureGroupID>" + Dts.Variables["MeasureGroupID"].Value.ToString() + "</MeasureGroupID>");
 xmla.AppendLine("<PartitionID>" + Dts.Variables["PartitionID"].Value.ToString() + "</PartitionID>");
 xmla.AppendLine("</Object >");
 xmla.AppendLine("</Process>");        

 Dts.Variables["XMLA"].Value = xmla.ToString();

 XmlaClient clnt = new XmlaClient();
 clnt.Connect(Dts.Variables[&amp;quot;ServerID&amp;quot;].Value.ToString());
 clnt.Send(xmla.ToString(), null);
 clnt.Disconnect();
 }
 catch (Exception e)
 {
 Dts.Variables["ScriptError"].Value = e.Message.ToString();
 }

 Dts.TaskResult = (int)ScriptResults.Success;
}

This is one way of processing the partition and a preferred preference if you feel comfortable with C# and Analysis Management Objects.

4. Method 2: Analysis Service Execute DDL Task

This method uses the SSIS task designed to execute XMLA scripts. Again you are using the original C# script to generate the XMLA Script. You simply make a connection to the SSAS Tabular instance via a connection string. You add the XMLA variable to the DDL command so that we pass in the generated XMLA script to the SSIS task.

ssas12

Once you add the connection string and the XMLA variable set in the task you are good to go. You have a second method to process the XMLA Script using a SSIS Analysis Service Execute DDL Task.

ssas11

5 thoughts on “SSIS Methods to Process SSAS Tabular Partitions

  1. Great post Jesse! Thank you for sharing this solution. 🙂 This is really helpful for us who just starting. Is there a way for example to create the partition dynamically in SSIS? In our case we need to process the model everyday. What I wanted to do is to create the partition name by date and use SSIS for it.

    • Hey how are you? Well i like to use c# AMO to create the partition so you would have a script task that would create that dynamic partition in ssis. I may right a blog about this you’ve sparked my interest 🙂

  2. Just wondering why not skip the step of Create XMLA, and instead using the Direct Input as the SourceType for the Analysis Service Execute DDL Task?

    • I was designing it to create xmla and use c# script to execute that way because the place I worked at had an issue their etl framework and the as ddl task didn’t work so we used this method. But to answer your question yes you can do it that way as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s