First published on TECHNET on Feb 03, 2012
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:
Figure: Flow Diagram of steps performed during Cube Partitioning step of DW Maintenance
A couple of design notes:
-
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.
-
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:
- Part 1: OLAP Cubes in the SCSM Data Warehouse: Introduction
- Part 2: OLAP Cubes in the SCSM Data Warehouse : Key Concepts
- Part 3: OLAP Cubes in the SCSM Data Warehouse : Modeling the OLAP Cube in Management Packs
- Part 4: OLAP Cubes in the SCSM Data Warehouse : OLAP Cube Partitioning
- Part 5: OLAP Cubes in the SCSM Data Warehouse: OLAP Cube Processing
- Part 6: OLAP Cubes in the SCSM Data Warehouse: OLAP Cube Deployment
- Part 7: OLAP Cubes in the SCSM Data Warehouse: Troubleshooting
- Part 8: OLAP Cubes in the SCSM Data Warehouse : Maintenance and Administration
Updated Mar 11, 2019
Version 4.0System-Center-Team
Microsoft
Joined February 15, 2019
System Center Blog
Follow this blog board to get notified when there's new activity