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.


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

&lt;Create xmlns=""&gt;<br>
        &lt;CubeID&gt;Adventure Works&lt;/CubeID&gt;<br>
        &lt;MeasureGroupID&gt;Fact Internet Sales 1&lt;/MeasureGroupID&gt;<br>
        &lt;Partition xmlns:xsd="" xmlns:xsi=""&gt;<br>
            &lt;Source xsi:type="QueryBinding"&gt;<br>
                &lt;DataSourceID&gt;Adventure Works DW&lt;/DataSourceID&gt;<br>
                &lt;QueryDefinition&gt;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]<br>
		FROM [dbo].[FactInternetSales]<br>
                                WHERE OrderDateKey &amp;gt;= '20070101' and  OrderDateKey &amp;lt;= '20071231'&lt;/QueryDefinition&gt;<br>
                &lt;Source xsi:type="ProactiveCachingInheritedBinding"&gt;<br>
            &lt;AggregationDesignID&gt;Internet Sales 1&lt;/AggregationDesignID&gt;<br>

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


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<br>
	 @DatabaseID as varchar(50)<br>
	,@CubeID as varchar(100)<br>
	,@MeasureGroupID as varchar(50)<br>
	,@PartitionID as varchar(500)<br>
	,@PartitionCommand as varchar(max) output<br>
	declare @ParentObjectXmla as varchar(max),@PartitionXmla  as varchar(max)</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>		select @ParentObjectXmla =<br>
		select @DatabaseID as DatabaseID, @CubeID as CubeID, @MeasureGroupID as MeasureGroupID<br>
		for xml path ('ParentObject')<br>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>		with xmlnamespaces('' as xsd<br>
							,'' as xsi<br>
		select @PartitionXmla =<br>
		select PartitionID as ID<br>
			 , PartitionName as Name<br>
			 , PartitionSourceType as "Source/@xsi:type"<br>
			 , DataSourceID as "Source/DataSourceID"<br>
			 , PartitionQuery as "Source/QueryDefinition"<br>
			 , StorageMode as StorageMode<br>
			 , 'Regular' as ProcessingMode<br>
			 ,'-PT1S' as "ProactiveCaching/SilenceInterval"<br>
			 ,'-PT1S' as "ProactiveCaching/Latency"<br>
			 ,'-PT1S' as "ProactiveCaching/SilenceOverrideInterval"<br>
			 ,'MolapOnly' as "ProactiveCaching/AggregationStorage"<br>
			 ,'ProactiveCachingInheritedBinding' as "ProactiveCaching/Source/@xsi:type"<br>
			 ,'Server' as "ProactiveCaching/Source/NotificationTechnique"<br>
			 ,EstimatedRows as EstimatedRows<br>
			 ,AggregationDesignID as AggregationDesignID<br>
		from dbo.SSASPartition<br>
		where DatabaseID =@DatabaseID<br>
			and CubeID = @CubeID<br>
			and MeasureGroupID = @MeasureGroupID<br>
			and PartitionID = @PartitionID<br>
			and CreatedDateTime is null<br>
		for xml path ('Partition')<br>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>		set @PartitionCommand=<br>
		'&lt;Create xmlns=""&gt;'<br>
		+ @PartitionXmla<br>
		+ '&lt;/ObjectDefinition&gt;&lt;/Create&gt;'<br>

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
  • 20 thoughts on “SSIS: Creating Dynamic Data-driven SSAS Partitions

    1. Thank you Benny for the great article.
      I am trying to implement the same. I successfully created the partitions, however, while trying to process the same I am getting the following error

      OLE DB error: OLE DB or ODBC error: An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.; 42000.

      The partition query embedded in XMLA is as below

      [dbo]. [Table Name].[Column Name1]
      ,[dbo].[Table Name].[Column Name2]
      ,[dbo].[Table Name].[Column Name3]
      ,[dbo].[Table Name].[Column Name4]
      ,[dbo].[Table Name].[Column Name5]
      ,[dbo].[Table Name].[Column Name6]
      ,[dbo].[Table Name].[Column Name7]
      ,[dbo].[Table Name].[Column Name8]
      ,[dbo].[Table Name].[Column Name9]

      FROM [dbo].[Table Name]
      WHERE [dbo].[Table Name].[Column Name1] &gt;=20060101 and
      [dbo].[FactPremiumView].[Column Name1] &lt;=20061231

        1. Hi Benny, that doesn’t seem to be the problem as the date values are int.

          The query I am inserting through stored proc is


          FROM [dbo].[Table1]
          WHERE [dbo].[Table1].[Column1] & gt;= ‘+ ltrim(rtrim(cast(@fromdate as char))) + ‘AND [dbo].[Table1].[Column1] <= ‘ + ltrim(rtrim(cast (@todate as char)))’

          This is getting converted into the following query in Partition XMLA script

          FROM [dbo].[Table1]
          WHERE [dbo].[Table1].[Column1] & amp;gt;= 20060101 AND [dbo].[Table1].[Column1] &lt;= 20061231

          And when I check inside Partition query inside the partitions tab in Visual Studio


          FROM [dbo].[Table1]
          WHERE [dbo].[Table1].[Column1] & gt;= 20060101 AND [dbo].[Table1].[Column1] <= 20061231

          and the final query is what sbeing executes during processing and that s failing since compiler is not probably able to understand < and & gt;

        2. I was able to rectify the issue, In the query inserted into table, I kept >= and <= in its XMLA format ie > and < that caused it get converted in a weird way. when i replace < and > with = in the table the partition query came out correct and I was able to process the partitions

    2. 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.

    3. When using this SSIS package, I’m receiving an error in the Execute DDL portion. Here is the error:

      [Analysis Services Execute DDL Task] Error: The DatasourceID element at line 7, column 497 (namespace cannot appear under Envelope/Body/Execute/Command/Create/ObjectDefinition/Partition/Source.

      Can’t seem to figure out what’s wrong.

    4. 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.

    5. 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: Logo

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

    Facebook photo

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

    Connecting to %s