Dynamic management of SSAS partitions with SSIS

Problem

The default behavior of SSAS is to create a single partition for a measures group. This certainly not a good practice when you have very large fact tables and can cause us performance problems to process or query the SSAS cube.
To fix this kind of problem, you will have to partition the cube, but not just any way, you really need to have a well-defined dynamic SSAS partitioning plan.
How can we design this plan in a BI project?

Before starting the implementation of our partitioning plan, we will talk first about the advantages of SSAS partitioning.

Advantages of SSAS partitioning

• Improved performance: SSAS can query a smaller amount of data by isolating one or more partitions to query rather the entire metric group. SSAS can also query multiple partitions in parallel.
• Flexible configuration: each partition has its own storage mode, for example, MOLAP, HOLAP or ROLAP and aggregation design. You can choose the optimal storage mode and the aggregation according to the frequency with which the data are queried (amount of data available, frequency of data changes, etc ...)
• Flexible processing:  a partition can be processed separately or in parallel, you can delete a partition without having to deal with the cube. If you have a score of the last week, month, quarter, and so on, you can only process the rows in the fact table that belong to the score, rather than processing the entire fact table.
• Multiple source tables: each partition can optionally specify its own fact table, which allows it to physically divide large fact tables into multiple tables.

Implementation

The example we will use in this article is about the AdventureworksDW database, knowing that you can follow the same steps to create the same partitioning plan on your project according to your needs.

1. Install the AdventureworksDW sample

The AdventureWorksDW sample data consists of :

• The AdventureWorksDW database.
• The AdventureWorksDW OLAP cube

To deploy the AdventureWorksDW database :

1. Download AdventureWorksDW.
2. Copy AdventureWorksDW2012_Data.mdf to your default database directory (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data) or other location as designated by your database administrator.
3. Connect to the database engine in SQL Server Management Studio.
4. Right-click Databases, and then click Attach.
5. On the Attach Databases dialog box, click Add.
6. Navigate to the location where you copied AdventureWorksDW2012_Data.mdf, select the file, and then click OK.
7. Under “AdventureWorksDW2012” database details, select the row where File Type is Log.
8. Click Remove.
9. Click OK.

To configure the AdventureWorksDW OLAP Cube :

1. Download AdventureWorksDW Multidimensional OLAP Cube.
2. Unzip the file to a location on the computer running Analysis Services.
3. In the Enterprise folder, double-click AdventureWorksDW2012Multidimensional-EE.sln.
4. If the Choose Default Environment Settings dialog box appears, choose the Business Intelligence Settings option, and then click Start Visual Studio.
5. In Visual Studio, at the top of the Solution Explorer window, right-click AdventureWorksDW2012Multidimensional-EE and click Deploy.

2. Create the PartitionManager table

Now that everything is installed, we can start the creation of our solution, the first thing to do is to create a parameter table that we will call par.PartitionManager in the [par] schema. We need a record for each group of measures we want to partition.

CREATE SCHEMA [par]

CREATE TABLE  [par].[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 measures group.
DefaultPartitionName: Name of the partition created by default in the SSAS project for each measures group.
SliceQuery: The query that allows having the elements on which we will do the partitioning. For our example, we have chosen to partition each measures 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 dbo.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')

3. Edit the partition query

In the AdventureWorksDW SSAS solution, open the Partitions tab and edit the source query for the 3 measures group: Internet_Sales, Internet_Orders, and Reseller_Sales by adding the query below at the end of each query.

WHERE OrderDateKey BETWEEN -1 AND  0

With these changes, we are sure that our measures groups will be empty, but we do not worry, we will see in the next step the secret behind these changes.

4. Create the SSIS Solution

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

Starting with creating a new SSIS project called AdventureWorksCubePartition, in this project add a new SSIS package (DWH_CreatePartitionOLAP.dtsx) with a connection to the AdventureWorksDW database.

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

Name	Scope	Data type	Value	Expression
CubeName	DWH_CreatePartitionOLAP	String
DefaultPartitionName	DWH_CreatePartitionOLAP	String
MeasureGroupName	DWH_CreatePartitionOLAP	String
OlapDbName	DWH_CreatePartitionOLAP	String	<OLAP Database name>
OlapServerName	DWH_CreatePartitionOLAP	String	<Server name>
PartitionList	DWH_CreatePartitionOLAP	Object	System.Object
PartitionName	DWH_CreatePartitionOLAP	String		REPLACE(@[User::MeasureGroupName], " ","_") + "_" + (DT_STR, 4, 1252) @[User::Period]
Period	DWH_CreatePartitionOLAP	Int32
PeriodList	DWH_CreatePartitionOLAP	Object	System.Object
QuerySlice	DWH_CreatePartitionOLAP	String
Slice	DWH_CreatePartitionOLAP	String		"[Date].[Calendar Year].&["+ (DT_STR, 4, 1252) @[User::Period]+"]"

Next step is to add an Execute SQL Task called EST - Get MeasureGroups to retrieve the data from the par.PartitionManager table and pass them in the variable User:PartitionList. EST - Get MeasureGroups will be configured as follows :

 

This time we will go through User: PartitionList, for that we will set up a Foreach Loop Container to browse the list of objects we have in User: PartitionList and map them with the variables that we have created before, of course, at each iteration the values of these variables will be changed.

In the Foreach Loop Container Editor, select the Foreach ADO Enumerator type and the User: PartitionList in the source variable of the ADO object.

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

Now add an Execute SQL Task (EST - Get Partition Slice) inside the Foreach Loop Container.

The new Execute SQL Task will be configured as follows:

 

The Execute SQL Task (EST - Get Partition Slice) will allow us to execute the User: QuerySlice request that we retrieved at each iteration of the loop and which 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 measures group.

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.

 

Partitioning the Cube in SSIS can be done by setting up a Script Task (STC - AMO Partitioning) which will contain a C # Script.

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

**
User::CubeName,
User::DefaultPartitionName,
User::MeasureGroupName,
User::OlapDbName,
User::OlapServerName,
User::PartitionName,
User::Period,User::Slice**

The principle of the c# script is simple, for each partition and year passed as input, we will create a partition dedicated to the year, by replacing the 'Between -1 and 0' we put at the end of the source query with “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, you must add a reference to the DLL 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;
                        Partition partClone;
                        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; 
            }
        }
    }
}

5. Run the project

The SSIS package is ready to run.

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

Conclusion

Creating partitions in Analysis Services by integrating services isn't a difficult task. In this article, we explained how you can dynamically create and configure partitions for a measure group.

Download

The entire project is available for download: 
https://gallery.technet.microsoft.com/Dynamic-management-of-SSAS-b0f4fe0f 

See also

---

Back to top