Today 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!

http://sqlblogcasts.com/blogs/drjohn/archive/2015/05/26/ssas-partition-manager-for-both-ssas-tabular-models-and-ssas-multidimensional-cubes.aspx