Home > About Dimensional Database ... > About Cubes > Cube Partitioning
![]() Previous |
![]() Next |
Partitioning is a method of physically storing the measures in a cube. It improves the performance of large measures in the following ways:
Improves scalability by keeping data structures small. Each partition functions like a smaller measure.
Keeps the working set of data smaller both for queries and maintenance, since the relevant data is stored together.
Enables parallel aggregation during a build. Each partition can be aggregated by a separate process. These processes can run simultaneously when sufficient resources are available.
Simplifies the removal of old data from storage when partitions are created on a Time dimension (default).
You can partition a cube on one or more levels of a cube dimension.
New cubes are partitioned by default on the second highest level of each hierarchy in the time dimension. If the cube does not have a time dimension, then the dimension with the most hierarchies is used for partitioning.
For example, if a time dimension has levels for day, week, quarter, and year, then by default all cubes dimensioned by time have a partition for each quarter.
The OLAP engine automatically creates and drops partitions during the data loads, as members are added and deleted from the partitioning dimension. A data refresh typically creates new time periods and deletes old ones. For example, whenever a Calendar Quarter value is loaded into the Time dimension, a corresponding partition is added to the cube. Whenever a Calendar Quarter value is deleted from the Time dimension, the corresponding empty partition is deleted from the cube.
A Time dimension is typically used for partitioning for life-cycle management considerations. Old time periods are dropped as a unit and new time periods are added as a new partition. Moreover, the hierarchy is completely balanced, with the same number of children for each aggregate value at all levels.
When life-cycle maintenance is not a factor, you may choose the most dense dimension for partitioning. The most dense dimension is frequently the one with the fewest members.