Ordering Dimensions in a Cube

Dimensions in a cube have an order that you select when you create a cube. The order you select can affect system performance, so you should give some consideration to the order of dimensions before creating a cube.

As a first step toward ordering dimensions, divide the dimensions into two groups: sparse and dense dimensions. A dense dimension has a high percentage of values for its elements. You can estimate the density by answering this question: If one element in the dimension has a value, keeping the elements of the other dimensions constant, what is the probability that the other elements in the dimension have values?

For example, if you have a budget in January for a given account and region, you probably also have a value for the remaining months. Therefore, the Month dimension is probably dense. Similarly, if you have a budget value for a given month, account, and region, you probably also have an actual value, making ActVsBud a dense dimension.

However, in a worldwide sales cube, you probably do not sell every product in every region. Therefore, you would treat Product and Region as sparse dimensions.

We generally recommend that you order the dimensions as follows: smallest sparse to largest sparse, followed by smallest dense to largest dense. However, some flexibility is required. For example, it is probably better to put a very small, dense dimension such as ActVsBud that has only two or three elements before a very large but sparse dimension, such as Product, which might have thousands of elements.