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:

• AdventureWorksDW Database
• AdventureWorksCube Cube
• SSASProject project

• 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.

WHERE OrderDateKey BETWEEN -1 AND 0

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 !!! :).

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