Skip Headers
Previous
Previous
 
Next
Next

Create/Edit Cube: Partition

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

"Cube Partitioning"

Select Partitions

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.

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

"When Does Aggregation Order Matter?"

Partition Member Analysis

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:

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:

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