Cube optimisation can be done a number of ways:

• Using the inbuilt re-order dimensions in TM1 (9.5.1 and above only) or
• Using Vizier (http://cubewise.com/vizier-91.php) which will always find the optimal order. For more information on Vizier and its many other features please contact Cubewise (http://cubewise.com/contact-us-7.php)
• Manually using the "re-order dimensions" right-click menu option from the cubes object tree in server explorer

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 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 in the following instances:

• You do not have Vizier; or
• 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
• Note: The measure used to calculate density for the other dimensions should be a data input measure not a rule calculated measure

2       Calculate the number of populated elements 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 elements 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.