SSIS: Dynamic Processing of SSAS Partitions

SSAS Partitions are primarily geared towards better query performance by narrowing the MDX query to a pre-defined subset of data. Each partition must be in processed state for the cube to be available for browsing. However in a delta ETL run, it is more than likely that only some partitions would be refreshed with data while the rest remain unchanged. For example in a hospital admissions cube partitioned by month, data movements are frequent for admissions made during the current/last month compared to earlier months. This means if the partitions updated by the ETL run can be identified, then we can tailor SSIS package to dynamically process only those partitions that require data refresh while leaving the remaining partitions untouched.

XMLA Command to Process Partitions

SSAS partitions are processed by XMLA commands similar to the one shown below:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Obj>
<DatabaseID>AdventureWorks</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2005</PartitionID>
</Obj>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

Create XMLA Command to Dynamically Process Partitions

The key to dynamic processing of partitions is to generate the <Process> XMLA fragment for each partition that requires data refresh. To get to that, first create a table that has information about the SSAS partitions as shown below: SSASPartitions

DatabaseID	SSAS database ID. Not name
CubeID	Cube ID. Not name
MeasureGroupID	Measure group ID. Not name
PartitionID	Partition ID. Not name
PartitionQuery	SQL query of partition
ProcessType	ProcessData, ProcessFull, ProcessDefault, ProcessIndex
RequiresProcessFlag	Flag=1 – partition requires data refreshFlag=0 – partition already processed and in refreshed state
CreatedDateTime	Audit field
LastProcessedDateTime	Audit field

Then populate this table with all SSAS partitions. Note that Database ID, Cube ID, Measure Group ID and Partition ID may not be same as their corresponding names (especially if the objects are renamed after they are first deployed) although in most instances they are same. Usually the ETL Framework would give an indication of the fact records inserted/updated in the last run. From this information it is possible to determine which partitions should be refreshed. Update those partition records with RequiresProcessFlag=1. Create a stored procedure to return the XMLA as shown below:

create procedure [dbo].[getProcessingCommand]
@DatabaseID as varchar(200), @CubeID as varchar(200),@ProcessingCommand as varchar(max) output
as
begin

declare @ProcessXmla  as varchar(max);
declare @ProcessBatchXmla as xml;

with XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' as xsd
,'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
select @ProcessXmla =
(

select DatabaseID as  "Object/DatabaseID"
,CubeID as "Object/CubeID"
,MeasureGroupID as "Object/MeasureGroupID"
,PartitionID as "Object/PartitionID"
,ProcessType as "Type"
,'UseExisting' as "WriteBackTableCreation"
from dbo.SSASPartitions
where DatabaseID =@DatabaseID
AND CubeID = @CubeID
AND RequiresProcessFlag=1
for xml path ('Process')
) ;

set @ProcessBatchXmla= Convert(xml,
'<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>'
+@ProcessXmla
+  '</Parallel>
</Batch>'
,1)

set @ProcessingCommand = Convert(varchar(max),@ProcessBatchXmla,1)

end

This stored procedure uses FOR XML with XMLNAMESPACES to generate the <Process> XMLA fragment which is then encapsulated by the <Parallel> and <Batch> XMLA fragments. It is better to return the output XMLA as output parameter instead of a record set (SELECT @ProcessingCommand). If returned as record set, SQL Server implicitly converts the XMLA string into an XML which to SSIS is object data type and not a string. With limited operations around SSIS object data type, it is convenient to work with string especially when setting task expressions. The output of the stored procedure will be:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Obj>
<DatabaseID>AdventureWorks</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Internet Orders</MeasureGroupID>
<PartitionID>Internet_Orders_2008</PartitionID>
</Obj>
<Type>ProcessData</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Process xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Obj>
<DatabaseID>AdventureWorks</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Internet Orders</MeasureGroupID>
<PartitionID>Internet_Orders_2009</PartitionID>
</Obj>
<Type>ProcessData</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Process xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Obj>
<DatabaseID>AdventureWorks</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Internet Orders</MeasureGroupID>
<PartitionID>Internet_Orders_2011</PartitionID>
</Obj>
<Type>ProcessData</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

The output of the stored procedure can be used in SSIS either by Analysis Services Processing Task or Analysis Services Execute DDL Task to dynamically process SSAS partitions.

Using XMLA Command in SSIS Analysis Services Processing Task

The Analysis Services Processing Task can be used as shown in screen capture 1

Screen Capture 1 – Using XMLA Command in Analysis Services Processing Task

The stored procedure getProcessingCommand is called in execute SQL task and the output XMLA returned to a string variable.

Screen Capture 2 – Stored Procedure Call

The XMLA string variable is then assigned to the ProcessingCommands attribute of Analysis Services Processing Task from the Expressions option

Screen Capture 3 -Analysis Services Processing Task

At design time, Analysis Services Processing Task does require a partition to build the package. This partition will be overridden by the XMLA string at run time. Finally the RequiresProcessFlag is set to 0 on successful execution.

Using XMLA Command in Analysis Services Execute DDL Task

Another way to process SSAS partitions is to use the Analysis Services Execute DDL Task and supplying XMLA commands as shown in Screen Capture 4

Screen Capture 4- Using XMLA Command in Analysis Services Execute DDL Task

From the DDL options, set the Source Type attribute to Variable and Source to the XMLA string generated by the getProcessingCommand stored procedure

Screen Capture 5 – Analysis Services Execute DDL Task Editor

There is no difference between the two SSIS Analysis Service tasks as far as SSAS processing is concerned and is only a matter of preference to use one over another.

Download Source Code

You can download the SSIS package and Stored Procedure used in this post from http://1drv.ms/1avLkl7

Related Posts

SSIS: Creating Dynamic Data-driven SSAS Partitions

SSIS Package to Process SSAS Cube

SSAS: Process Incremental – What it is & isn’t

@BennyAustin Nice article.#MSBI #SSAS #SQLServer #BI #PowerBI #powerpivot #tabular

— Suhas Kudekar (@SuhasKudekar) February 20, 2014

SSIS: Creating Dynamic Data-driven SSAS Partitions http://t.co/dmC9RdnZ1H #SSIS #SSAS

— Benny Austin (@BennyAustin) April 10, 2014

SSIS: Dynamic Processing of SSAS Partitions http://t.co/Weft4JWGiK via @BennyAustin

— Tiago Quaresma (@TQuar3sma) March 6, 2014