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
<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 >= '20070101' and OrderDateKey <= '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
- > is html equivalent of >
- >= is html equivalent of >=
- < is html equivalent of <
- <= 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.
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
<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 >= '20070101' and OrderDateKey <= '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
|
| 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')
);
)
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.
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) outputasbegin 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') ); ) 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 2 – Get partitions to be created 
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
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
The SSIS package and database objects used in this post can be downloaded from here
No comments:
Post a Comment