OLAP Cubes in the SCSM Data Warehouse : OLAP Cube Partitioning

OLAP Cubes in the SCSM Data Warehouse : OLAP Cube Partitioning

  • Comments 6
  • Likes

This is the fourth post in a series of posts detailing the capabilities of OLAP cubes in the Data Warehouse, a new feature that enables self-service reporting functionality added in SCSM 2012.

OLAP Cube Partitioning

Each measure group in a cube is divided into partitions, where a partition defines a portion of the fact data that is loaded into a measure group. SQL Analysis Services (AS) Standard Edition only allows one partition per measure group, while multiple partitions are allowed in Enterprise Edition. Partitions are completely transparent to the end user, but they have an important impact on performance and scalability. For example, partitions can be processed separately and in parallel, and can have different aggregation designs (this is not implemented in SCSM 2012). You can reprocess a partition without affecting all the other partitions in a measure group. AS also will automatically only scan partitions that contain the necessary data for a query, which can vastly improve query performance.

Cube partitioning is performed on every DW Maintenance job run (hourly by default). The specific process module that is run is called "ManageCubePartitions" and it is always run after the "CreateMartPartitions" step. This dependency data is stored in the infra.moduletriggercondition table.

The main dll which handles partitioning is actually in the warehouse utility dll (Microsoft.EnterpriseManagement.Warehouse.Utility) in the PartitionUtil class. More specifically, there is a ManagePartitions() method in the class that handles all partition maintenance. The DW Maintenance (Microsoft.EnterpriseManagement.Warehouse.Maintenance) and the Warehouse Olap (Microsoft.EnterpriseManagement.Warehouse.Olap) dll's both call into this dll to handle partitions (during maintenance and cube deployment, respectively). This is why the actual partition handling was put in the common WH utility dll in order to avoid duplicating logic/code.

Cube Partitioning Maintenance performs the following tasks:

  • Creates Partitions
  • Deletes Partitions
  • Updates Partition boundaries

To do this, the SQL table etl.TablePartition is read to determine all the fact partitions that have been created for a measure group. The following diagram illustrates the steps that are taken:

 Cube Partitioning
Figure: Flow Diagram of steps performed during Cube Partitioning step of DW Maintenance

A couple of design notes:

  1. Only measure groups targeted at facts will contain multiple partitions (only in AS Enterprise Edition). By default, all measure groups and dimensions will only contain one partition and therefore the partition will not have any boundary conditions.

  2. The partition boundaries defined by a query binding based on datekeys which match up to the datekeys for the corresponding fact partition in etl.TablePartition.

Here is an index list of the entire series:

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Great series ..still reading. I however cannot see the images. They seem to be pointing to a SharePoint location. http://sharepoint/sites/msdsc/SM/scsmr2/smarchown/PublishingImages/OLAP/Cube%20Partitioning.jpg

  • Hi Samuel, the images have been uploaded.  Sorry about the inconvenience.

  • No problem Danny ... I can see all images for all posts now ..thank you for this series.