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 OlapPartitionManager 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 OlapPartitionManager

I introduced the OlapPartitionManager in my previous post: Dynamic Partition Creation in SSAS multidimensional using the OlapPartitionManager generic C# AMO app.  The OlapPartitionManager 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 OlapPartitionManager.

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:

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

There is no need for you to delve into the C# code behind the OlapPartitionManager as all the functionality is easily configurable as explained on Configuring the OlapPartitionManager.  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:

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

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

OlapPartitionManager.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:

OlapPartitionManager.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:

OlapPartitionManager.exe /p ProcessAll

All of the arguments are explained on OlapPartitionManager Command-Line Options

So download your copy and start using the OlapPartitionManager today!

http://sqlblogcasts.com/blogs/drjohn/archive/2015/05/18/ssas-automating-olap-cube-deployment-dynamic-partitioning-and-processing.aspx