Dynamic Partition Management SSAS - 2 Part

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 steps to follow step by step 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 one will do the partitioning of the Cube. For our example, we have chosen to partition each measurement group according to the year, for this it is necessary to have a separate list of the years of each fact table concerned. Ex:
SELECT DISTINCT YEAR (fis.OrderDate) AS year FROM [dbo]. [FactInternetSales] fis

Here is the request 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 en '
  • 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 modifications, we are sure that our measurement groups will be empty, but we do not 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 aExecute SQL Task which we will callEST - Get MeasureGroupsto 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 goal is to go through the list of objects that we have inUser: PartitionListand to map them with the variables that one has to create before, of course, with each iteration the values ​​of these variables will be changed, It is 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 (EAST - Get Partition Slice) inside of Foreach Loop Container.


Our new Execute SQL Task will be configured as follows:

12 13


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

The execution of this query returns the list of distinct years of each fact table and which make 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 Containerjust after the EST - Get Slice Score.

17 18

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 connection information to the cube as well as other information as shown below:


Now I'm going to explain the implementation of the c # script. The principle is simple, for each partition and year passed in the input of 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 score between Between the key of the start date and end date of the input year.

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 functions of AMO, it is just a problem of lack of reference, we must add a reference to the DLL of AnalysisServices 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 {/// <summary> /// ScriptMain is the entry point class of the script. Do not change the name, attributes, or parent of this class. /// </ summary> [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {/// <summary> /// 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. /// </ summary> public void Main () {try {TODO: Add your code here string OlapServerName = Dts.Variables ["OlapServerName"]. Value.ToString (); string OlapDbName = Dts.Variables ["OlapDbName"]. Value.ToString (); string CubeName = Dts.Variables ["CubeName"]. Value.ToString (); string MeasureGroupName = Dts.Variables ["MeasureGroupName"]. Value.ToString (); string PartitionName = Dts.Variables ["PartitionName"]. Value.ToString (); string Slice = Dts.Variables ["Slice"]. Value.ToString (); string DefaultPartitionName = Dts.Variables ["DefaultPartitionName"]. Value.ToString (); string SlicePeriode = Dts.Variables ["Period"]. Value.ToString (); Server olapServer = new Server (); olapServer.Connect (OlapServerName); if (olapServer.Connected) {// connected to server ok, so obtain reference to the OLAP database Database db = olapServer.Databases.FindByName (OlapDbName); if (db! = null) {Cube cube = db.Cubes.FindByName (CubeName); MeasureGroup mg = cube.MeasureGroups.FindByName (MeasureGroupName); Partition part; PartClone Partition; part = mg.Partitions.FindByName (PartitionName); partClone = mg.Partitions.FindByName (DefaultPartitionName) .Clone (); if (part! = null) part.Drop (); part = mg.Partitions.Add (PartitionName); part.StorageMode = StorageMode.Molap; QueryBinding QB = (QueryBinding) partClone.Source.Clone (); QB.QueryDefinition = QB.QueryDefinition.Replace ("Between -1 and 0", "Between '" + (int.Parse (SlicePeriode) * 10000 + 101) .ToString () + "' AND '" + (int.Parse (SlicePeriode) * 10000 + 1231) .ToString () + "'"); part.Source = QB; part.Slice = Slice; part.Update (); mg.Refresh (); olapServer.Disconnect (); }}} catch (Exception e) {Dts.Events.FireError (0, "Task Name", e.Message + "\ r" + e.StackTrace, String.Empty, 0); Dts.TaskResult = (int) ScriptResults.Failure; }}}}

Our SSIS package is ready to run !!!


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


The entire project is available for download: PartitionManager


2 thoughts about "Dynamic Partition Management SSAS - 2 Part»

  1. First of all I want to say great blog! I had a quick
    question which I would like to ask if you do not 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.


Leave a comment

Your email address will not be published. Required fields are marked with *