May 2015 - Posts - Sparks from the Anvil

May 2015 - Posts

Dynamic Partition Creation in SSAS multidimensional using the SSAS Partition Manager

<<Updated blog entry to reflect the new release of SSAS Partition Manager>>

There are plenty of blog entries out there that outline how to dynamically create partitions in SSAS multidimensional databases. However, all of them suffer the same drawback: they always hard-code some part of the logic into the programming language in the form of hard-coded names or hard-coded partition queries. None of them provide a generic solution that you could just pick up and apply to any project. Perhaps this is a reflection of the usual implementation language of choice: SSIS. Let's face it, SSIS is famous for been brittle; packages break when moved to a new environment. They are rarely reusable without modification. Generating SSIS packages from BIML has solved some of these problem, but even BIML does not handle C# script tasks well; it is rather like painting a room through a keyhole!

Like many business intelligence consultants, I take example scripts from project to project, each time extending their functionality a little more or adapting them to meet new requirements. For many years, I have used C# and AMO to create partitions and process cubes. Indeed, I have presented on this at SQLBits.  I do find the AMO object model extremely intuitive and C# much easier to code than doing the equivalent partition creation logic in an SSIS package.

After several iterations, I now have an entirely generic piece of C# code which will work in any situation.  The only configuration required is to change the content of a set of tables and edit two views so they generate the correct MDX member keys to match the date dimension in your cube. Fortunately, this is very simple and very much in the realm of the average business intelligence consultant as it is pure T-SQL.

Once configured the SSAS Partition Manager will add partitions to all cubes in all databases listed in the SsasPartitions view, so it will work out of the box even in multi-cube, multi-database environments.  The application will also delete partitions that are no longer required, thus removing redundant data from the cube.

To illustrate, I have chosen to partition each measure group in the AdventureWorks sample cube by different criteria.  So the Internet Customers measure group is partitioned by year, the Internet Sales partition by quarter, Internet Orders and Reseller Orders partitioned by month, the Reseller Orders partitioned by week.  Finally, the Sales Order measure group is partitioned by a hybrid scheme whereby years 2014 onwards have monthly partitions and prior years are partitioned by quarter.  Of course, all this partitioning is completely over the top for the tiny amount of data held by the AdventureWorks sample cube, but does illustrate the power of the SSAS Partition Manager.

All this is encapsulated in the SsasPartitionDefinitions view which allows you to review your configuration (shown below).

SsasPartitionDefinitions

Note that the PartitionStartDate is set to 2011-01-01 for all partition schemes, but this could easily be changed by editing the SsasMeasureGroup table.

The PartitionSliceIsRange column is a powerful new feature of my latest implementation and discussed below.

Partition Elimination

Correctly setting the partition slice property of each partition helps the storage engine eliminate the partitions that do not contain relevant data.  Referred to as partition elimination, this greatly increases the performance of MDX queries as not as much data needs to be read by the SSAS storage engine. This is one of the main reasons why cubes should be partitioned.  However, this does rely on the end-user using the dimension upon which the partitioning is based in their MDX queries.  Partitioning by the date dimension is the typical approach.  But be warned, cubes often contain several date dimensions all playing different roles (e.g. order date, delivery date, ship date etc.), so be careful to base your partitioning strategy on the right date dimension.  I have known situations where the cube designer inadvertently picked the wrong date dimension and so the storage engine could never employ partition elimination.  Big mistake!  Huge!

The problems with Auto-Slice

The auto-slice functionality in SSAS does not always get it right.  During processing, SSAS internally identifies the range of data that is contained in each partition by using the Min and Max DataIDs to set the partition slice.  Unfortunately, DataIDs are assigned during dimension processing as new members are encountered and we have no explicit control over how they are allocated, so because SSAS just looks at the minimum and maximum value of the DataID in the partition, you can end up reading partitions that don’t contain relevant data.  Thus it is always best practice to explicitly assign a value to the partition slice property of each partition.

Setting a valid Partition Slice

You can set the partition slice property of a partition to be a single MDX member, or a set of members.  However, MDX ranges operators (i.e. :) are not allowed.  So, for example, the following two statements work fine:

{[Date].[Calendar].[Month].&[2014]&[4]}

{[Date].[Calendar].[Date].&[20140101],[Date].[Calendar].[Date].&[20140102],[Date].[Calendar].[Date].&[20140103],[Date].[Calendar].[Date].&[20140104],[Date].[Calendar].[Date].&[20140105],-- etc. --,[Date].[Calendar].[Date].&[20140131]}

Whereas the following range statement will cause the partition processing to fail:

{[Date].[Calendar].[Date].&[20140101] : [Date].[Calendar].[Date].&[20140131]}

Of course the above MDX statements were generated from the Adventure Works cube date dimension.

If you get your partition slice wrong, you will get an error such as:  "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated".  Note that you will ONLY get such errors if the partition contains over 4096 rows which is the IndexBuildThreshold defined in msmdsrv.ini file.  In the Adventure Works database, partitions rarely get above 5000 rows, so the partition slice setting is ignored.  In reality, the Adventure Works database has such low data volumes there is no need to partition the cube, but then it is just a sample database!

Partition Definition Tables

For many years now, I have used a set of SQL tables to hold the definition of each partitioning scheme required in the cube.  A single view then combines the content of these tables with the date dimension to create a list of partitions that need to be created in the cube.  This partition definition view even generates the MDX required to set the partition slice property correctly.  Thus the SSAS Partition Manager program is entirely generic; it simply connects to the SSAS database and compares the partitions it finds in each measure group with the list of desired partitions in the partition definition view: SsasPartitions

Instead of holding one row per partition in a static table, we get the partition definition view to combine the underlying partition definition tables to generate the list of partitions we need.  A CROSS JOIN to the date dimension simply fills in the gaps.  The view is entirely dynamic, giving you all the partitions you need for today.  Tomorrow the view will add on any new partitions as they are required by the partitioning scheme: monthly, quarterly, yearly.

SsasPartitions view

This view is read by the SSAS Partition Manager program which compares the partitions in the cube with the ones listed by the view.  If the partition does not exist in the cube, then it is created.  Note that the C# program does not update existing partitions.  It simply creates new ones or deletes redundant partitions.

The clever thing about this view is not only how it uses date dimension to generate a list of all partitions up to the present day, but also uses it again to generate the list of keys for MDX ranges.  Note the content of the PartitionSlice column in the case of the Internet Orders measure group. This was generated because the PartitionSliceIsRange flag is set to True (1).

image

Note that the view is entirely dynamic, giving you all the partitions you need for today.  Tomorrow the view will add on any new partitions as they are required by the partitioning scheme: monthly, quarterly, yearly.  This is because the SsasPartitions view only generates partitions between the PartitionStartDate and today's date.  Unfortunately the AdventureWorks date dimension table ends in 2014, so we never get any new partitions been created as we roll over to the new week, month, quarter or year.  Clearly this would not be the case in a true production cube!

Once the SSAS Partition Manager has been run against the AdventureWorks cube, this is what you see in SSMS:

PartitionedMeasureGroupsInAdventureWorks

I provide the T-SQL for these database objects as a SQL Server Data Tools (SSDT) project called SsasPartitionDefinition which is part of the download for the SSAS Partition Manager hosted on Codeplex.  Note that I include DimDate from the AdventureWorksDW2014 database just so my SSDT project compiles correctly.

The Populate_Partition_Definitions.sql file populates the three partition definition tables with the partitioned measure groups from the AdventureWorks cube.  This can easily be adapted to your own cube(s).

Note that this table structure supports many measure groups in many cubes in many databases, so with one run of the SSAS Partition Manager you can create partitions in hundreds of cubes, all with the correct partition slice set!

Configuring the SSAS Partition Manager to work with your project

To get yourself up and running with the SSAS Partition Manager this is all you have to do:

  1. Edit the SsasPartitonManager.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. 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 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

All this is explained in detail on the following page: Configuring the SSAS Partiton Manager

Full validation logging

The first step taken by the SSAS Partition Manager is to validate your configuration.  Thus it will check that all the measure groups exist in the relevant cubes in the relevant databases.  It even checks the measure groups have a template partition with its where clause set to WHERE 1=0.   All this is logged along with the creation/deletion of each partition.  So check the log files to ensure your initial configuration is correct.

Download from Codeplex

The SSAS Partition Manager is available for download from Codeplex. I would be glad to have your feedback on the project via the project’s discussions tab.

SSAS Partition Manager for both SSAS Tabular Models and SSAS Multidimensional Cubes

SsasPartitionManagerLogoToday I am proud to announce the release of SSAS Partition Manager that can dynamically create partitions in both SSAS Tabular models and SSAS Multidimensional cubes using a wide variety of partitioning schemes (yearly, monthly, weekly etc.).  

What is more, for OLAP cubes it correctly sets the MDX in the PartitionSlice property of the partition so you don't have to rely on SQL Server Analysis Services' auto-slice functionality which has known bugs and limitations.

SSAS Partition Manager is a production ready application which is very easy to configure and can be easily integrated into your deployment scripts and regular data warehouse processing jobs. Indeed, a full set of unit and integration tests has been prepared for the SSAS Partition Manager to ensure the quality of the code.

Deploy and Partition Your Tabular Model or Multidimensional Cube

SSAS Partition Manager provides the ability to both deploy a database (either Tabular or Multidimensional), add the partitions and process the newly deployed database all with one simple command.

Using command-line options you can:

  • Deploy an OLAP cube or Tabular model
  • Create partitions using a wide variety of partitioning schemes
  • Process the newly deployed OLAP or Tabular database using ProcessFull
  • Create partitions in all databases on the server and then process them all using ProcessFull
See SSAS Partition Manager Command-Line Options for further information.

Supports multiple Partitioning Schemes

Each measure group can have its own partitioning scheme and out of the box it supports the following partitioning schemes.
  • Yearly
  • Quarterly
  • Monthly
  • Weekly
  • Daily
  • Hybrid
  • Matrix
Hybrid partitioning schemes are where some years use one scheme and other years use another. For example, previous years could be partitioned by quarter whereas the current year is partitioned by day. Matrix partitioning schemes are where you partition by date and some other dimension.

Easy to configure

The SSAS Partition Manager is an entirely generic piece of C# code which will work in any situation. With a tiny amount of configuration, you can start using the SSAS Partition Manager with your multidimensional cube and/or tabular models very quickly. You should never need to modify the C# code. All configuration is done in T-SQL, so it is very much in the realm of the average business intelligence consultant. No specialist skills required!

The key to understanding the SSAS Partition Manager is to know that it creates partitions based on the content of a view in the SQL database called SsasPartitions which tells it what type of partitions to create and how many. When it connects to the cube or model, it expects to find a template partition which has its where clause set to WHERE 1=0 which it clones to form the new partition. That's it! The SSAS Partition Manager does the rest.

Program Logic

The SSAS Partition Manager starts by reading from its config file the connection string to the SQL Server database and a flag which indicates which server environment to connect to (i.e. DEV, UAT or PROD). It then connects to the SQL Server database and queries the SsasServer table for the name of the SSAS server using the server environment flag. Thus, DEV would connect to your localhost, UAT to your UAT server etc. etc.

The program then connects to the SSAS server using the Analysis Management Objects (AMO) library Microsoft.AnalysisServices.
The SSAS Partition Manager then queries the SsasPartitions view to find out what partitions are needed. This view lists all the partitions in all the measure groups in all the cubes/models in all the databases on the SSAS server. Thus the SSAS Partition Manager can handle a multi-cube/model, multi-database environment very easily.

The SSAS Partition Manager will add partitions to all cubes/models in all databases listed in the SsasPartitions view, so it will work out of the box even in multi-cube, multi-database environments. The SSAS Partition Manager will also delete partitions which are not listed in the SsasPartitions view with the exception of any template partition (i.e. a partition with the word template in its name). Thus you can implement a rolling-partition scheme whereby old data is removed from the cube automatically.

The program logic is comprehensively tested by a full set of unit and integration tests before each release to ensure the quality of the code, so you can rest assured it won't go wrong on your server.

Validation Phase

The SSAS Partition Manager has a validation phase which checks your configuration is correct. So for example, if you misspell the name of a measure group, a warning message will be written to the log. The validation covers every aspect of the configuration, including checking your template partition contains WHERE 1=0, so just check the log file to see if everything is correctly set up in your environment.

Database Objects to support the SSAS Partition Manager

The SSAS Partition Manager relies on the presence of a few key database objects. You can choose which database you put these in yourself. The main thing is that you customize the SsasDateDimWrapper view to work with your own date dimension or equivalent. All this is explained in Configuring the SSAS Partition Manager.

Replacement for OlapPartitionManager

SSAS Partition Manager replaces the OlapPartitionManager which I discussed in previous posts as it is more generic and caters for both Tabular and Multidimensional databases.

So read the documentation and go to the download page and start using it today!

SSAS: Automating OLAP cube deployment, dynamic partitioning and processing

Imagine been able to deploy your OLAP database, dynamically partition the cube and then process the new database all in one step from an .asDatabase file with minimal configuration.  Well now you can!  Today’s release of the SSAS Partition Manager provides a set of features which allow you to do just that.  What is more, for the first time you can easily integrate the build and deployment of OLAP databases into your continuous integration tasks so you can write regression tests that check the figures in your cube.

OLAP cube deployment is usually done using the Analysis Services Deployment Wizard. However, the wizard only deploys the partitions that were part of the original cube design; it will not dynamically add the new partitions required by your partitioning scheme.

The SSAS Partition Manager

I introduced the SSAS Partition Manager in my previous post: Dynamic Partition Creation in SSAS multidimensional using the SSAS Partition Manager generic C# AMO app.  The SSAS Partition Manager allows each measure group to have its own partitioning scheme and out of the box it supports the following partitioning schemes.

  • Yearly
  • Quarterly
  • Monthly
  • Weekly
  • Daily
  • Hybrid
  • Matrix

Hybrid partitioning schemes are where some years use one scheme and other years use another. For example, previous years could be partitioned by quarter whereas the current year is partitioned by day. Matrix partitioning schemes are where you partition by date and some other dimension.  Note that the last two options require some configuration on your part which is all explained on Configuring the SSAS Partition Manager.

Easy to configure

All of this power is easy to configure by changing two SQL views and the content of three tables - well within the realms of the average business intelligence consultant!

Deploying your cube

Today’s release takes the project a step further by allowing you to deploy a fresh copy of your OLAP database, partition all of the cubes and then process the database using one simple command-line such as:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase" /p ProcessDeployed

The above command-line will deploy a cube called AdventureWorks using the AdventureWorks.asdatabase OLAP database definition file onto the SSAS server listed in the OlapServer table.  The SSAS Partition Manager will then dynamically add all the partitions listed in the OlapPartitions view.  Finally it will process the new OLAP database.  That’s it!  Dead simple!  Exactly how to set everything up is all explained on Configuring the SSAS Partition Manager.

There is no need for you to delve into the C# code behind the SSAS Partition Manager as all the functionality is easily configurable as explained on Configuring the SSAS Partition Manager.  However, if you do wish to delve a little deeper, you will notice that the new DeployDatabase method is simply a new implementation of some code I released as part of this blog post: SSIS: Deploying OLAP cubes using C# script tasks and AMO

Example command-lines

Deploy a new OLAP database and add partitions:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase"

Deploy a new OLAP database, add partitions and process the newly deployed OLAP database:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase" /p ProcessDeployed

Deploy a new OLAP database, override the standard connection string to the Data Mart, add partitions and process the newly deployed OLAP database:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase" /c "Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2014" /p ProcessDeployed

Perform a full process on all OLAP databases found in the OlapPartitions view:

SSASPartitionManager.exe /p ProcessAll

All of the arguments are explained on SSAS Partition Manager Command-Line Options

So download your copy and start using the SSAS Partition Manager today!

Posted by DrJohn with 1 comment(s)