Using SSIS to Automate the Creation of Cube Partitions

Partitions are a feature in Analysis Services to help optimize cube performance.  In a previous article I explained why partitions are so helpful and how you can create them to be used in your cube.  For the purposes of this article though most of the demonstration will be done in Integration Services.

Many companies find the need to create partitions on a monthly, weekly, or even daily basis depending on how large their fact table loads are.  With some companies having billions of new records loaded into a fact table daily it creates an obvious need for multiple partitions.  Having a DBA or Developer manually create these partitions can be time consuming and means you have to rely on a human being to remember to create the partition daily.  A great alternative is to have Integration Services do all the work for you.  You can create an SSIS package to handle the partition creation, use the SSIS expression language to dynamically change the partition name and storage date range and then finally you can schedule it to run the appropriate number of times.  It just seems natural to take another great BI tool that already has a task that can handle the work to automate a tedious job. 

In this example I will make the assumption that your company needs partitions to be created on a daily basis.  If it needs to be less frequent then adjust accordingly.  This example will also assume that you have created partitions previously so if you haven’t follow the link at the top of this article to learn how to create partitions from BIDS (Business Intelligence Development Studio).

Example

1.       Connect to the Analysis Server through Management Studio to script out the XMLA used to create a partition. 

2.       Expand the Analysis Services database you wish to partition.  Next, select the appropriate cube and finally measure group.  Expand the partitions folder then right-click on one of the available partitions and select Script Partition as -> CREATE To -> New Query Editor Window (These partitions were created in the previous article linked above if you wish to follow the same example)

3.       When the XMLA creation script is created do a find and replace so all double quotes (“) are replaced with \”.  This will help in preparing the script to be used in the SSIS expression language.  Read this blog post to better understand why the \” is needed.  Leave this query here for now, you will come back to it.

4.       Create a new SSIS package and bring an Analysis Services Execute DDL Task into the Control Flow of the package. 

5.       Add two variables to the package one called strCurrentDate and one called strXmlaStatement both with String data type.

6.       The strCurrentDate variable should have EvaluateAsExpression set to True and the expression should read:

REPLACE( ( DT_WSTR, 10) (DT_DBDATE) GETDATE(), "-", "" )

Which returns the current date as a string without hyphens like this:

20100202

7.       The strXmlaStatement variable should also have EvaluateAsExpression set to True but the expression should use the XMLA query that you have in Management Studio.  This expression should look like this (with the changes highlighted):

"<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">

    <ParentObject>

        <DatabaseID>PASS</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\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">

            <ID>Internet Sales " +@[User::strCurrentDate]+ " </ID>

            <Name>Internet Sales " +@[User::strCurrentDate]+ "</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],CONVERT ( CHAR ( 10 ), SalesOrderNumber )  + 'Line '  +

CONVERT ( CHAR ( 4 ), SalesOrderLineNumber ) AS [SalesOrderDesc]

                                FROM [dbo].[FactInternetSales]

                                WHERE OrderDateKey = "+  @[User::strCurrentDate] +"</QueryDefinition>

            </Source>

            <StorageMode>Molap</StorageMode>

            <ProcessingMode>Regular</ProcessingMode>

            <ProactiveCaching>

                <SilenceInterval>-PT1S</SilenceInterval>

                <Latency>-PT1S</Latency>

                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>

                <Source xsi:type=\"ProactiveCachingInheritedBinding\" />

            </ProactiveCaching>

            <EstimatedRows>1303980</EstimatedRows>

            <AggregationDesignID>InternetSaleAgg</AggregationDesignID>

        </Partition>

    </ObjectDefinition>

</Create>"

The double quotes surrounding the whole XMLA statement will pass in the value as a string literal.  The ID and Name section that is highlighted will change the name of the partition that is created to include the date as part of the name.  Lastly, the Where clause section that is highlighted will change what data is stored in the created partition.

8.       Next open the editor for the Analysis Services Execute DDL Task and establish a connection to the desired Analysis Server on the DDL page.

9.       On the Expression page create a new expression on the Source property and use the strXmlaStatement variable for the expression.  Hit OK twice to confirm the expression and then the Task editor.

10.   Run the package and when the package lights up green you should have successfully created a new partition with a dynamic name and query using SSIS.

11.   Verify the new partition exists in Management Studio.

12.       Last create a SQL Agent job to run this SSIS package and schedule it to run daily.