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
<br>
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><br>
<ParentObject><br>
<DatabaseID>AdventureWorksDW</DatabaseID><br>
<CubeID>Adventure Works</CubeID><br>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID><br>
</ParentObject><br>
<ObjectDefinition><br>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><br>
<ID>Internet_Sales_2007</ID><br>
<Name>Internet_Sales_2007</Name><br>
<Source xsi:type="QueryBinding"><br>
<DataSourceID>Adventure Works DW</DataSourceID><br>
<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]<br>
FROM [dbo].[FactInternetSales]<br>
WHERE OrderDateKey &gt;= '20070101' and OrderDateKey &lt;= '20071231'</QueryDefinition><br>
</Source><br>
<StorageMode>Molap</StorageMode><br>
<ProcessingMode>Regular</ProcessingMode><br>
<ProactiveCaching><br>
<SilenceInterval>-PT1S</SilenceInterval><br>
<Latency>-PT1S</Latency><br>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><br>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval><br>
<AggregationStorage>MolapOnly</AggregationStorage><br>
<Source xsi:type="ProactiveCachingInheritedBinding"><br>
<NotificationTechnique>Server</NotificationTechnique><br>
</Source><br>
</ProactiveCaching><br>
<EstimatedRows>1013</EstimatedRows><br>
<AggregationDesignID>Internet Sales 1</AggregationDesignID><br>
</Partition><br>
</ObjectDefinition><br>
</Create><br>
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
|
| 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.
<br>
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>
as<br>
begin<br>
declare @ParentObjectXmla as varchar(max),@PartitionXmla as varchar(max)</p>
<!-- /wp:paragraph -->
<!-- wp:paragraph -->
<p> select @ParentObjectXmla =<br>
(<br>
select @DatabaseID as DatabaseID, @CubeID as CubeID, @MeasureGroupID as MeasureGroupID<br>
for xml path ('ParentObject')<br>
);</p>
<!-- /wp:paragraph -->
<!-- wp:paragraph -->
<p> with xmlnamespaces('http://www.w3.org/2001/XMLSchema' as xsd<br>
,'http://www.w3.org/2001/XMLSchema-instance' as xsi<br>
)<br>
select @PartitionXmla =<br>
(<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>
);</p>
<!-- /wp:paragraph -->
<!-- wp:paragraph -->
<p> set @PartitionCommand=<br>
'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">'<br>
+@ParentObjectXmla<br>
+'<ObjectDefinition>'<br>
+ @PartitionXmla<br>
+ '</ObjectDefinition></Create>'<br>
end<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
a) The “Get Partitions to be created” SQL execute task gets the list of partitions to be created as record set.
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
Benny Austin 
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
SELECT
[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] >=20060101 and
[dbo].[FactPremiumView].[Column Name1] <=20061231
Varun, are you missing quotes around the date values?
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
‘SELECT
[dbo].[Table1].[Column1]
,[dbo].[Table1].[Column2]
,[dbo].[Table1].[Column3]
,[dbo].[Table1].[Column4]
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
SELECT
[dbo].[Table1].[Column1]
,[dbo].[Table1].[Column2]
,[dbo].[Table1].[Column3]
,[dbo].[Table1].[Column4]
FROM [dbo].[Table1]
WHERE [dbo].[Table1].[Column1] & amp;gt;= 20060101 AND [dbo].[Table1].[Column1] <= 20061231
And when I check inside Partition query inside the partitions tab in Visual Studio
SELECT
[dbo].[Table1].[Column1]
,[dbo].[Table1].[Column2]
,[dbo].[Table1].[Column3]
,[dbo].[Table1].[Column4]
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;
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
Hey Benny, can you please tell me how to go about partitioning for Tabular model? any links for the same would be of great help
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 ?
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.
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 http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Create/ObjectDefinition/Partition/Source.
Can’t seem to figure out what’s wrong.
Dan, are you able to execute the output of the stored proc from SSMS ? If no check your metadata
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.
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.
partition got created in my OLAP DB but when I process the cube partition getting deleted from the OPLAP DB…why ?
Rajendra, check your cube processing package. If you need a reference https://bennyaustin.wordpress.com/2014/02/20/dynamic-processing/ and https://bennyaustin.wordpress.com/2013/08/19/processcube/
Once Partition got created How to process that created partition … could please help me
Take a look at SSAS Partition Manager. Does everything plus more! https://ssaspartitionmanager.codeplex.com/
If you are looking to automate creation of partitions in your SSAS multidimensional cubes, then take a look at the OlapPartitionManager, a free utility which does it all. https://olappartitionmanager.codeplex.com/
Awesome!
Benny, if you can send me the package, that will be great. No clue for variables in post. Thanks.
Stacie, it’s here https://onedrive.live.com/redir?resid=9952823828123E97!176&authkey=!ABC1TRHAY4-p1ng&ithint=file%2c.zip