Configuring the SSAS Partition Manager

To get yourself up and running with the SSAS Partition Manager this is all you have to do:
  1. Edit the SsasPartitionManager.exe.Config file to change the database connection string and identify the SsasServerType (i.e. DEV, UAT or PROD).
  2. Change the SsasDateDimWrapper view to work with your own date dimension table or equivalent
  3. If you are using SSAS multidimensional, change the SsasPartitionPeriods view so that it contains the correct MDX members for your date dimension. You can also add your own partitioning schemes if you need something special.
  4. Change your OLAP cube or tabular model project to have template partitions with the where clause set to WHERE 1=0
  5. Finally you may need to change the SsasPartitions view if you have some special requirements for the way the PartitionWhereClause is generated
That is it! About one hour of work.

Note, there is no need to change the C# code. The SSAS Partition Manager simply works, with no problems, out of the box.

Listed below are more notes on precise configuration details. However, if you are a good SQL dev, I suggest you just try doing the deploy. The following covers everything in detail just in case you are struggling to get to grips with T-SQL or some finer points of the configuration.

The SsasPartitionManager.exe.Config file

This only contains three configurable elements
  1. The connection string to the SQL Server database in which all the SSAS Partition Manager database objects reside
  2. The SsasServerType which identifies the type of server we should connect to (i.e. DEV, UAT or PROD).
  3. The level of logging which should be performed and to where the logging information should be written
  4. The SQL string used to query the SsasServer table. This allows you to change the schema if required.
  5. The SQL string used to query the SsasPartitions view. This allows you to change the schema if required.
By default, the SSAS Partition Manager is set up for two logging outputs: logging to the console and to a text file. Optionally you can switch on a rolling log file one where new log files are started when the current log file gets to a certain size. You can also log to a SQL Server table, either using an INSERT statement or by calling a stored procedure. This is clearly commented in the config file and sample logging tables and stored procedures are provided in the source code.

The huge list of other logging options and how to configure each of them is beyond the realms of this page. Full documentation of the configuration options can be found on the Apache log4net™ web site: Apache log4net™ Config Examples

SsasDateDimWrapper view

In the sample code, the OlapDateDimWrapper view is configured to work with the DateDim table from the AdventureWorks DW database (shown below). You simply need to change this view to work with your own date dimension table or equivalent. What you must ensure is that the datatypes and column names remain the same. That is it!
SsasDateDimWrapper.png

SsasPartitionPeriods View

If you are using the multidimensional model, the two columns you need to change in this view are the PartitionSliceMember and PartitionSliceKey. These should be adjusted to match the date dimension in your cube definition. For example, in the sample code which works against the AdventureWorks cube, the PartitionSliceMember has been set as follows: Similarly in the sample code which works against the AdventureWorks cube, the PartitionSliceKey has been set as follows: Note that this view obtains start and end dates for the partition using window functions introduced in SQL Server 2012. If you are still in the dark ages of using SQL Server 2008 or earlier, you will need to adjust this view to replace the min/max window functions.

Below is a screenshot of the SsasPartitionPeriods view configured to work with the AdventureWorks multidimensional cube.
SsasPartitionPeriods.png

Adding template partitions to your cube project

For every measure group you wish to have partitioned, simply add a partition containing the word template anywhere in its name and adjust its where clause set to be WHERE 1=0. That is it!

The screenshot below shows the template for the Customer Orders partition in the AdventureWorks cube.
TemplatePartition.png

The template partition can have an associated aggregation design which will be assigned to any new partitions based on this template.

You can leave other partitions in place for development purposes (i.e. for quick deploys of your project for dev testing) as these will be deleted by the SSAS Partition Manager if the name does not match the partition naming convention.

Implementing a hybrid partitioning scheme

A hybrid partitioning scheme is where some time periods use one scheme and other time periods use another. For example, previous years could be partitioned by quarter whereas the current year is partitioned by day. Another example would be where the current month is partitioned by day, the previous months of the current year partitioned by month and previous year by quarter (or year).

To implement such a scheme, simply edit the SsasPartitionPeriods view and take a look at how I have implemented the hybrid scheme (PartitionPeriod='H') for the AdventureWorks cube. You can implement your own hybrid partitioning scheme by extending this methodology.

Changing the SsasPartitions View

For most partitioning schemes, you will have no need to change the SsasPartitions view. However, if you are implementing an advance matrix partitioning scheme or using an unsupported data type in the WHERE clause, you may need to edit this view. The screenshot below shows the standard results from the SsasPartitions view. Note how the view creates the relevant PartitionWhereClause for each partition.
SsasPartitions.png