Dynamic management of SSAS partitions - part 2

Here we are in the 2th part of this article where there is less blah blah but more practice (putting our hands in the leg !!)

In this part, I will explain the step by step operations to create the cube partitions in a dynamic way.

  • Step 1 :

First, prepare the test data, for that, we will work with the database and the cube AdventureWorksDW, you can download the sources as well as the deployment scripts from the links below:

  • Step 2 :

First, we will create a parameter table in the schema [par] that we will call Par.PartitionManager.

We need a record for each group of measures we want to partition.

CREATE TABLE [by]. [PartitionManager] ([IdMeasureGroup] [smallint] IDENTITY (1,1) NOT NULL, [CubeName] [varchar] (50) NULL, [MeasureGroupName] [varchar] (50) NULL, [DefaultPartitionName] [varchar] (50) NULL, [SliceQuery] [varchar] (500) NULL) ON [PRIMARY] GO
  • CubeName : Name of the cube
  • MeasureGroupName : Name of the measurement group
  • DefaultPartitionName : Name of the partition created by default in the SSAS project for each measurement group
  • SliceQuery : The request which allows to have the elements on which we will do the partitioning of the Cube. For our example, we have chosen to partition each measurement group according to the year, for this we must have a separate list of the years of each fact table concerned. Eg:
SELECT DISTINCT YEAR (fis.OrderDate) AS year FROM [dbo]. [FactInternetSales] fis

Here is the query to fill this table (The 3 groups of measures that we will partition are: Internet Sales, Internet Orders and Reseller Sales):

INSERT INTO par.PartitionManager (CubeName, MeasureGroupName, DefaultPartitionName, SliceQuery) VALUES ('Adventure Works', 'Internet Sales', 'Internet_Sales', 'SELECT DISTINCT YEAR (fsi.OrderDate) AS year FROM dbo.FactInternetSales fsi'), ( 'Adventure Works',' Internet Orders', 'Internet_Orders',' SELECT DISTINCT YEAR (fsi.OrderDate) AS year FROM dbo.FactInternetSales fsi '), (' Adventure Works', 'Reseller Sales',' Reseller_Sales', 'SELECT DISTINCT YEAR (frs.OrderDate) AS year FROM dbo.FactResellerSales frs')
  • Step 3 :

In the solution AdventureWorksDW2012Multidimensional-EE, Open the Partitions tab and modify the source query of the 3 Partitions Internet_Sales, Internet_Orders and Reseller_Sales by adding it at the end of each request.



With these changes, we are sure that our measure groups will be empty, but don't worry, we will see in the next step the secret behind these changes.

  • Step 4 :

Now we will attack the most important step is the creation of our SSIS package that will manage the partition of the Cube.

Starting by creating a new SSIS project that we will call AdventureWorksCubePartition, in this project add an SSIS package DWH_CreatePartitionOLAP with a connection to the database AdventureWorksDW2012.


We will need the following variables with their values ​​and expressions as shown below:


Next step is to add a  Execute SQL Task we will call EST - Get MeasureGroups to retrieve the data from the table par.PartitionManager  and pass them in the variable User: PartitionList.

EST - Get MeasureGroups will be configured as follows:



We're going this time to go User: PartitionList, for that we will set up a Foreach Loop Container whose objective is to browse the list of objects that we have in User: PartitionList and to map them with the variables that we created before, of course, at each iteration the values ​​of these variables will be changed, that's the objective not !!! :).


In the editor Foreach Loop Container, select as type of enumeration  Foreach ADO Enumerator et User: PartitionList in the source variable of the ADO object.


Select the variables to map with the values ​​in the collection.


Adding now a Execute SQL Task (EST - Get Partition Slice) inside of Foreach Loop Container.


Our new Execute SQL Task will be configured as follows:




The Execute SQL Task (EST - Get Partition Slice) will allow us to execute the request User: QuerySlice that we retrieved at each iteration of the loop and which corresponds to the SliceQuery column of the par.PartitionManager table.

Executing this query returns the list of years distinct from each fact table and which make up our partitioning element of each group of measures.

Now, you have to loop on this list to create a partition by year. For this we will add another component Foreach Loop Container just after the EST - Get Partition Slice.



The partitioning of the Cube in SSIS is done by setting up a Script Task (STC - AMO Partitionning) which will contain a C # Script.


As input, the script needs the cube connection information as well as other information as shown below:


Now I will explain the implementation of the C # script. The principle is simple, for each partition and year spent as input to the script, we will create a partition dedicated to the year, replacing the 'Between -1 and 0' that we put at the end of the source query of our partition by the Between between the key of the start date and end date of the year as input.

For this we use the features of AMO (Analysis Management Objects). Below the C # code that will allow you to reach your goals:

NB: Visual studio may not recognize the AMO functions, it's just a problem of lack of reference, you must add a reference to the AnalysisServices dll that you will find in this directory:

C: \ Program Files (x86) Microsoft SQL Server \ 120 \ SDK \ Assemblies \ Microsoft.AnalysisServices.DLL

#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using Microsoft.AnalysisServices; #endregion namespace ST_377f706deec74825b03f6cc85452c8e0 {/// /// ScriptMain is the entry point class of the script.  Do not change the name, attributes, /// or parent of this class.
 /// [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {/// /// This method is called when this script task executes in the control flow.
 /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
 /// To open Help, press F1.

Our SSIS package is ready to run !!!


After the execution of our SSIS package, we can clearly see the new partitions created for each year of analysis.


The entire project is available for download: PartitionManager


4 thoughts on "Dynamic management of SSAS partitions - part 2"

  1. First of all I want to say great blog! I had a quick
    question which I'd like to ask if you don't mind.
    I was interested to know you
    clear your head prior to writing. I have had difficulty clearing my thoughts
    in getting my thoughts out there. I do enjoy writing it
    just seems like the first 10 to 15 minutes were wasted just trying to figure out
    how to begin. Any suggestions or hints? Thank you!


    1. The only advice I can give you to read and write before you start writing and being focused.


  2. Hello,

    When we test, we see that the source partition remains, it makes a duplicate data right?

    And besides, the SQL of the partition is not updated, we stay with the WHERE OrderDateKey BETWEEN -1 AND 0 in all the new partitions ... I do not understand why because the code seems correct


    1. Hello,
      It is normal that the source partition remains because as you can see it in the C # code, it is used to retrieve the source request in order to create a new partition by replacing the BETWEEN -1 AND 0 with the correct dates.
      On the other hand, it is not normal that the new partitions do not change, I think you have missed something.


Leave a Reply

Your email address Will not be published.

This site uses Akismet to reduce spam. Learn how your comment is processed.