Cubes Property Sheet: Storage tab

The Storage tab determines how the data is stored physically. These choices impact the performance of the cube.

Cube Storage Advisor

Runs the Cube Storage Advisor. You should always run the Cube Storage Advisor after creating or redesigning a cube. Only override its recommendations if you fully understand the impact of your changes.

To design the cube storage yourself, set the following options while creating the cube. Afterward, you can run the Cube Storage Advisor, or you can delete and re-create the cube.

Use Compression

Select this option if the data in this cube is extremely sparse. Note that extreme sparsity is quite common.

Extreme sparsity often results from one or more of these factors:

A cube has a large number of dimensions (seven or more).

One dimension has more than 300,000 members.

Two dimensions have more than 100,000 members each.

Dimension hierarchies have numerous levels, with little change to the number of dimension members from one level to the next, so that many parents have only one descendant for several contiguous levels.

Compressed storage uses less space and results in faster aggregation than normal sparse storage for extremely sparse cubes.

Some aggregation operators are not compressible because their values can change for every level: all weighted operators, all scaled operators, and the Hierarchical Weighted Average operator. When these operators are used in a cube, the aggregation engine compresses the dimensions that it can, but it cannot compress the entire cube.

Data Type

For compressed storage, all measures in the cube must have the same data type. For regular storage, a data type is associated with each measure. Depending on your selection of a data type, you may set the following additional parameters:

Precision: The maximum number of significant decimal digits.

Scale: The number of digits from the decimal point to the least significant digit.

Size: The maximum length in bytes for text data types.

Use Global Composites

An unpartitioned cube always has one composite for the cube, whether it is compressed or uncompressed. A partitioned compressed cube always has a composite for each partition. A choice between single (global) and multiple composites is available only for uncompressed, partitioned cubes.

This option defines a single composite for the cube instead of a composite for each partition. By using a global composite, you may be able to:

Eliminate redundancy among multiple composites and thereby lower the storage requirements.

Load the entire composite in memory for improved all-around performance.

A global composite may be a better choice under these circumstances:

The partitioned dimension is dense, and the sparsity patterns for the other dimensions are consistent across partitions.

The aggregated composite will not exceed the available memory (50 million or more values).

Regular composites are being used. Compressed cubes cannot use global composites.

The analytic workspace does not support multiwriter applications.

Note: A global composite does not allow parallel aggregation across partitions. If the analytic workspace has fewer cubes than available processes, then aggregating the partitions in parallel may improve build performance more than a global composite.

When in doubt, do not choose this option. The cube will have one composite for each partition.

Dimension Order and Sparsity

Select the sparse dimensions.

The dimension order is important for regular storage, but it has no effect on compressed storage. To order the dimensions, use the arrow keys to move the sparse dimensions down the list, after the dense dimensions. All dimensions that you have identified as sparse (by selecting the Sparse box) must be grouped together.

These are basic guidelines for ordering the dimensions for regular storage:

List Time first to expedite data loading and time-based analysis. Time is often a dense dimension, although it may be sparse if the base level is Day or the cube has many dimensions.

List the sparse dimensions in order from the one with the most members to the one with the least.