Home > OLAP Dialog Boxes and Wizards > Create/Edit Cube: Partition
![]() Previous |
![]() Next |
Partitioning is a method of physically storing the measures in a cube. 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.
Cubes are partitioned by default.
Use these subpages to define partitions for the cube:
Related Topics
Use this subpage to identify the dimension and levels to be used for partitioning.
Dimension: A dimension for partitioning the cube. The dimension must have at least one level-based hierarchy. In developing a partitioning strategy, you typically want the members to be distributed evenly, such that each partition has about the same amount of data as the others, to support the best performance. You can switch among dimensions without losing your selections in Aggregation Hierarchies, and so you can freely explore your data. By default, partitions are created on a time dimension.
Aggregation Hierarchies: Lists the hierarchies and their levels for the selected dimension. If the dimension has multiple hierarchies and you are partitioning on only one of them, choose the one that has the most members; it should be defined as the default hierarchy. After you make a selection, brackets enclose the levels that will be stored in the same partition.
Each dimension member at the selected 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, unless you select multiple levels for partitioning.
Choose the levels with care to distribute the data evenly across the 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 a separate partition. If the data is very sparse, then you might partition by year instead of quarter.
Another example is a time dimension with two hierarchies, calendar and fiscal, with month and day levels in both hierarchies. In this scenario, you might partition on the month, calendar year, and fiscal year levels.
The goal is to create partitions that fit in memory, which optimizes performance. The more memory your computer has, the larger the partitions can be and still achieve this goal.
Order Hierarchies: Displays the Order Hierarchies dialog box, which enables you to change the aggregation order of the hierarchies for the selected dimension.
Clear Selections: Deletes all hierarchy selections from the current display. Any selected hierarchies in other dimensions are unaffected.
Edit the precompute values to customize how the cube is solved: You can edit the percentage of values that are calculated and stored during data maintenance. The remaining members are calculated on demand in response to a query. In general, you should precompute the values that are queried most frequently.
A value of 0 does not create any aggregate values; they are calculated at run-time to provide the answer sets to queries. The result of 0% pre-aggregation is the fastest maintenance, the least storage space, but the slowest query response time. A value of 100 creates all of the aggregate values, which are simply fetched in response to queries. The result of 100% pre-aggregation is the longest maintenance, the most storage space, but the fastest query response time. Most DBAs choose values between these two extremes to balance the performance requirements for queries with the limitations of a data maintenance window.
A value of 1 only creates 1% of the aggregate values, but also creates the data structures for storing and tracking the aggregates. Thus, the amount of time to calculate this small percentage is correspondingly longer.
You may want to adjust the percentages over time to balance runtime performance with maintenance restrictions on time and disk space.
Partition Order: The order in which the partitions are aggregated.
Partition Name: Name assigned to the partition.
Partition Includes: Levels included in the partition.
Precompute: The percentage of precomputed values in this partition. You can edit this value unless Disable Editing of Cube Precompute Values is selected in the Configuration dialog box.
Automatically Manage Partition Order: Select this option to enable OLAP to determine the optimal aggregation order. Do not select this option when the aggregation order changes the results. Order is important for some aggregation operators, such as Average, and when a cube uses multiple aggregation methods, such as Hierarchical Last Member for Time and Sum for all other dimensions.
This option appears only when the Show Automatic Partitioning Order Check Box is selected in the OLAP Preferences.
Related Topics
This subpage shows how the members of the selected dimension are distributed among the partitions. Use this information to create a partitioning strategy with approximately an even number of dimension members in each partition.
The information is displayed in tabular and graphic formats.
Table
The table provides this information about the specified partitions:
Partition Name: Name of the partition, as shown in the Select Partitions subpage.
Number Partitions: Number of partitions created by partitioning on the selected level.
Total Members: Total number of dimension members being distributed across the partitions. This number includes the members at the level selected for partitioning and their children at levels included in the partition.
Minimum Members: Minimum number of dimension members assigned to a partition.
Maximum Members: Maximum number of dimension members assigned to a partition.
Average Members: Average number of dimension members assigned to a partition.
Standard Deviation: Amount of variation among the partitions from the average. A lower standard deviation is better than a high standard deviation.
Graph
The graph illustrates the partition selected in the table. It provides a visual representation of the number of members in each partition and their level in the dimension hierarchy.
A tool bar enables you to make temporary changes to the graph. The text tools are disabled. You can use these tools:
Fill Color: Changes the background color surrounding the graph.
Graph Type: Provides a variety of standard graph types, as described in Table: Partitioning Graph Types.
Show Legend: Controls whether the legend is displayed.
Grid Lines: Controls whether horizontal grid lines are displayed on graphs with an X/Y axis.
Gradient Effect: Controls whether colored areas appear solid or with a slight variation in color.
3-D Effect: Controls whether the graph appears flat or three-dimensional.
Partitioning Graph Types
Graph Type | Usage |
---|---|
Bar |
Comparisons (default) |
Horizontal Bar |
Comparisons |
Pie |
Percentage or comparisons of percentages; relationship between the parts and the whole |
Line |
Trends over time; rate of data change |
Area |
Trends over time; rate of data change |
Combination |
Trends over time; effect of one variable on another |
Scatter |
Correlations of two or three measures |
Stock |
Stock prices over time |
Circular |
Cyclical or directional patterns |
Pareto |
Highest and lowest contributors to a total; ranking |
3-D |
Three-dimensional comparison |
Funnel |
A filtering process that starts at 100% and decreases in stages to a much lower percentage |