Optimizing dimension order in a Cube

Cube optimization/dimension re-ordering is only suitable for numeric-only cubes. If the cubes contain string data then the last dimension can’t be changed and therefore there will be a minimal benefit as the last dimension is the most important by far for cube optimisation.

Optimising dimensions in a cube structure does not change the actual order of dimensions in the cube structure; it is an internal server change so rules, reports and anything referencing the cube is not affected.

The method described below is relevant if cube sizes are so big that time and system resources required to find the optimum order via an iterative algorithm is out of the question.

Generally speaking optimising dimension orders in a cube can prove to be a time consuming exercise based on the recommended optimal ordering of smallest sparse to largest sparse, followed by smallest dense to largest dense dimensions.  From our experience, Cubewise has found that 90% of the gain comes from placing the correct dimension as the last dimension in a cube.  In all documentation to date however there is rarely any mention of methods of determining what the last dimension should be!

In terms of what constitutes a “large” dimension and a “dense” dimension in terms of the optimal order of “small sparse to small dense followed by large sparse to large dense,” this is somewhat of a grey area, a “large dense” dimension and therefore the candidate as the optimal LAST dimension may only have a few hundred elements, hence it may not be what we would usually think of as being a “large” dimension.

For the purposes of a useable rule of thumb a “large dense dimension” is where there are more than 75% of populated N level elements with more than 200 N elements in the dimension.  The “largest densest” dimension for the purposes of optimising a given cube will depend on the relative size and density of other dimensions in the cube.  Provided the “large” dimensions are over a certain size generally density is the more important factor in determining the optimal last dimension.

During the re-ordering process generally allow for a temporary RAM increase on the TM1 server by a factor of two for the cube that you are re-ordering.  Where the dimension order is better than the current order, gains of up to 80-90% can be made on the current cube size (but likewise if the order is worse the result could be a 10 fold increase versus current cube size.)  Therefore cube optimisation should only be attempted with adequate memory resources on the server, otherwise you risk causing an out of memory crash of TM1.

Time to spill the beans – here is a pretty simple method of calculating optimal dimension order using Excel and TM1.

 1         Calculate the number of N level elements within a dimension

Create a 0 level subset within the dimension

In Excel use the SUBSIZ formula SUBSIZ(Dimension,SubsetName)

This will give you the number of N level elements in the dimension

2          Calculate the number of populated cells within each dimension in the cube excluding the measures dimension

Construct a view with the dimension to test as the row subset using the level 0 subset

Have no column subset and ensure the other dimensions are “Total …..”

Select a measure element that you know has data e.g. Total Sales or Net Profit

Zero supress the view

Snapshot to Excel and perform a count on the number of rows

3          Calculate the % used

Divide the number of populated cells by the number of n level elements in the dimension and this will give you the % of populated cells.
Do this for each dimension

4          Analyse results

Rank/Sort the dimensions by density then by size.  Exclude small dimensions.  The remaining dimensions at the top of the list will give you an idea of what the best order for the dimensions should be.

The large dimension with the highest density should be the last dimension in the cube.  You can repeat to determine the 2^nd last dimension, 3^rd last dimension etc. but be aware that there are diminishing returns by an approximate factor of 10. Thus if 90% of the gain is from correctly positioning the last dimension there is only a 9% gain from the optimal 2^nd last dimension and there is rarely any point continuing beyond this point.