SSIS: Creating Dynamic Data-driven SSAS Partitions

Partitions improve the query response of SSAS cube by narrowing the MDX query to a pre-defined subset of data. Usually cubes are developed with few basic partitions to begin with and new partitions are added over time. Partitions are created using XMLA commands. The rest of this post explains the steps to create partitions using metadata and SSIS package.

Metadata

First step is to identify the required data elements. This is done by scripting out the XMLA from an existing partition. The XMLA will be similar to the one shown below

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>AdventureWorksDW</DatabaseID>
        <CubeID>Adventure Works</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <ID>Internet_Sales_2007</ID>
            <Name>Internet_Sales_2007</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>Adventure Works DW</DataSourceID>
                <QueryDefinition>SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]
		FROM [dbo].[FactInternetSales]
                                WHERE OrderDateKey &gt;= '20070101' and  OrderDateKey &lt;= '20071231'</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <AggregationStorage>MolapOnly</AggregationStorage>
                <Source xsi:type="ProactiveCachingInheritedBinding">
                    <NotificationTechnique>Server</NotificationTechnique>
                </Source>
            </ProactiveCaching>
            <EstimatedRows>1013</EstimatedRows>
            <AggregationDesignID>Internet Sales 1</AggregationDesignID>
        </Partition>
    </ObjectDefinition>
</Create>

The nodes, elements and attributes of the create partition XMLA command will form the metadata of the data-driven model as shown below.

SSASPartition

DatabaseID SSAS database ID. Not name
CubeID Cube ID. Not name
MeasureGroupID Measure group ID. Not name
PartitionID Partition ID. Not name
Partition Name Partition Name
PartitionSourceType Query Binding, Table Binding
DataSourceID Data source ID
PartitionQuery SQL query of partition. Take note that SQL operators >,>=,< and <= have to be replaced with their html equivalents

  • &gt is html equivalent of >
  • &gt;= is html equivalent of  >= 
  • &lt is html equivalent of <
  • &lt;= is html equivalent of <=
StorageMode Molap, Rolap, Holap
ProcessType ProcessData, ProcessFull, ProcessDefault, ProcessIndex
AggregationDesignID Aggregation Design ID. Null if aggregation design is not used
EstimatedRows Null if aggregation design is not used
RequiresProcessFlag Flag=1 – partition requires data refreshFlag=0 – partition already processed and in refreshed state
CreatedDateTime Partition created timestamp. Null if partition is yet to be created
LastProcessedDateTime Partition last processed timestamp. Null if partition is yet to be processed

Populate this table with new partitions. Take note that Database ID, Cube ID, Measure Group ID and Partition ID may not be same as their corresponding names (for instance if the objects are renamed after they are first deployed) although in most instances they are same. Besides creating new partitions, this table also stores information to dynamically process the new and existing partitions.

Create Partition XMLA Command

Next step is to generate the XMLA command that creates partition. The following stored procedure uses FOR XML and XMLNAMESPACES to generate the XMLA command for each partition.

create procedure getPartitionCommand
	 @DatabaseID as varchar(50)
	,@CubeID as varchar(100)
	,@MeasureGroupID as varchar(50)
	,@PartitionID as varchar(500)
	,@PartitionCommand as varchar(max) output
as
begin
	declare @ParentObjectXmla as varchar(max),@PartitionXmla  as varchar(max)

		select @ParentObjectXmla =
		(
		select @DatabaseID as DatabaseID, @CubeID as CubeID, @MeasureGroupID as MeasureGroupID 
		for xml path ('ParentObject')
		);


		with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as xsd
							,'http://www.w3.org/2001/XMLSchema-instance' as xsi
							)
		select @PartitionXmla =
		(
		select PartitionID as ID 
			 , PartitionName as Name
			 , PartitionSourceType as "Source/@xsi:type"
			 , DataSourceID as "Source/DataSourceID"
			 , PartitionQuery as "Source/QueryDefinition"
			 , StorageMode as StorageMode
			 , 'Regular' as ProcessingMode
			 ,'-PT1S' as "ProactiveCaching/SilenceInterval"
			 ,'-PT1S' as "ProactiveCaching/Latency"
			 ,'-PT1S' as "ProactiveCaching/SilenceOverrideInterval"
			 ,'MolapOnly' as "ProactiveCaching/AggregationStorage"
			 ,'ProactiveCachingInheritedBinding' as "ProactiveCaching/Source/@xsi:type"
			 ,'Server' as "ProactiveCaching/Source/NotificationTechnique"
			 ,EstimatedRows as EstimatedRows
			 ,AggregationDesignID as AggregationDesignID
		from dbo.SSASPartition
		where DatabaseID =@DatabaseID 
			and CubeID = @CubeID
			and MeasureGroupID = @MeasureGroupID
			and PartitionID = @PartitionID
			and CreatedDateTime is null
		for xml path ('Partition')
		);

		set @PartitionCommand= 
		'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">'
		+@ParentObjectXmla
		+'<ObjectDefinition>'
		+ @PartitionXmla
		+ '</ObjectDefinition></Create>'
end 

Take note to return the XMLA as output parameter instead of a record set (SELECT @PartitionCommand). If returned as record set, SQL Server implicitly converts the XMLA string into XML, which SSIS interprets as object data type instead of string. SSIS has limited operations for object data type and an object data type variable cannot be assigned to task expressions.

SSIS Package

Create a SSIS package as shown below in screen capture 1

Screen Capture 1 - SSIS Package to Create Partitions
Screen Capture 1 – SSIS Package to Create Partitions

a) The “Get Partitions to be created” SQL execute task gets the list of partitions to be created as record set.

Screen Capture 2 - Get partitions to be created
Screen Capture 2 – Get partitions to be created

b)The “For each new partition” is an ADO enumerator which loops through the list of partitions.

c) “Get Partition Command” is the Execute SQL task which calls the getPartitionCommand stored procedure and returns the create partition XMLA command.

Screen Capture 3 – Get Partition Command

d) “SSAS Execute DDL Task – Create Partition” is an Analysis Services Execute DDL Task that is configured to use the XMLA command returned by the stored procedure.

Screen Capture 4 – SSAS Execute DDL Task – Create Partition

The new partition is created and processed using Process Default option. It would require Process Data in next run.

e) Finally update the metadata table and set the processing flag so that the cube processing package can automatically include this partition for processing next time it runs.

Screen Capture 5 – Update SSASPartition Table

Download Source Code

The SSIS package and database objects used in this post can be downloaded from here

Related Posts

  • SSIS: Dynamic Processing of SSAS Partitions
  • SSIS Package to Process SSAS Cube
  • SSAS: Process Incremental – What it is & isn’t
  • 15 thoughts on “SSIS: Creating Dynamic Data-driven SSAS Partitions

    1. Hi Benny. Very good article. At the begining you wrote the stored procedure works with manually entered data for one partition only (so for static partition – not dynamic nor current partition). Could you tell me how to generate the list of ALL partitions which exists in particular MeasureGroup (FactTable) and load such data to the SSASPartitions table ?

      1. Tomasz, I would automate the population of the metadata table as part of ETL and let the ETL decide which partitions to create or refresh by setting appropriate flags. I haven’t explained that bit in this post and left it to the ETL developer to implement that. Usually I use a reference/config table to store all required object ID’s and base partition query with replaceable parameters. Then I run a simple stored procedure that generate the metadata from the reference/config table which drives the foreach loop in the package.

    2. Thanks Benny!

      How would you handle an existing cube with existing partitions? Your code does not take into consideration deletes of existing partitions.

      During development I have partitions with table binding and I don’t want to manually delete them every time we deploy cubes.

      1. Per Hederos, this is what I usually do. Once the measure group is built, I would change the partition to query binding from table binding. For build, the cube only needs the metadata,so you could create a query something like this

        Select dim sk, measures from fact where 1=1

        In this way you don’t have to delete this partition when deployed as it has no data, but remember to process this partition.

    3. partition got created in my OLAP DB but when I process the cube partition getting deleted from the OPLAP DB…why ?

      1. Benny, if you can send me the package, that will be great. No clue for variables in post. Thanks.

    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