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 data maintenance. Each partition can be aggregated by a separate process.
Simplifies removal of old data from storage. Old partitions can be dropped as a unit, and new partitions can be added.
The number of partitions affects the database resources that can be allocated to loading and aggregating the data in a cube. Partitions can be aggregated simultaneously when sufficient resources have been allocated.
Cube Partitioning Advisor
Runs the Cube Partitioning Advisor, which examines the characteristics of the mapped data sources and determines a partitioning strategy that will support the best overall performance. You can choose between partitioning on Time or allowing the Cube Partitioning Advisor to select a the partitioning dimension. The cube must be fully mapped before you can use the Cube Partitioning Advisor.
Partition Cube
Select this option to partition the cube manually. You can specify the partitioning you want while creating the cube. Afterward, you can only change the partitioning strategy by using the Cube Partitioning Advisor or by deleting and re-creating the cube.
Dimension
The dimension for partitioning the cube. The dimension must have at least one level-based hierarchy and its members should be distributed evenly, such that every parent at a particular level has roughly the same number of children.
Hierarchy
The hierarchy to be used for partitioning. If the dimension has multiple hierarchies, choose the one that has the most members; it should be defined as the default hierarchy.
Level
The level to be used for partitioning. Each dimension member at that level is stored in a separate partition, along with its descendants. Any dimension members that are at higher levels or are not in the hierarchy are stored together in the top partition. The size of the top partition should not exceed the size of the level-based partitions.
Choose the level with care so that you do not create a surfeit of partitions. For example, if the Time dimension has 10 years of data at the year, quarter, month, and day levels, then you might partition at the quarter level. This choice creates 40 partitions, one for each quarter and its descendants (months and days). The 10 members at the year level are stored together in the top partition, and can be retrieved quickly for a top-level view of the data. However, if the data is very sparse, then you might partition at the year level.
The goal is to create partitions that fit in memory, which optimizes performance. The more memory you computer has, the larger the partitions can be and still achieve this goal.
Copyright © 2003, 2007 Oracle. All rights reserved.