After you have determined which dimensions your cube will contain you will need to consider the optimal order of your dimensions. Dimensional order can and will affect overall system performance and its memory consumption.
At this point in the design phase, you should have practical samples of all your dimensional data. These samples should be evaluated and each dimension should be categorized as either “sparse” or “dense” – remember, a “dense dimension” is a dimension that has a high percentage of values for its elements.
Best Practice suggests that you order your dimensions as follows:
“Smallest Sparse” to “Largest Sparse”, then
“Smallest Dense” to Largest Dense”
However, some flexibility is required. For example, it is probably better to put a very small, dense dimension that has only two or three elements before a very large but sparse dimension which might have thousands of elements.
Asking TM1 to Optimize For You
A good step in your design analysis should be to ask TM1 to validate your dimensional order choices by using the TM1 Cube Optimizer.
The Optimizer does not change the actual order of dimensions in the cube structure (it changes the way dimensions are ordered internally on the server and because the cube structure is not changed, any rules, functions, or applications referencing the cube remain valid) but it provides a report detailing the impact that the order of your dimensions will have on cube memory consumption and system performance.
After you have built your cube (ordering the dimensions based upon the data analysis) and optimally, loaded some realistic data samples you can following the following steps:
- In the Tree pane of the Server Explorer, select the cube you want to optimize.
- Click Cube, Re-order Dimensions and the Cube Optimizer dialog box will opens
- Select a dimension in the New Order of Dimensions list box.
- Click the arrows to change the order of the dimensions in your cube.
- Click Test.
Note the value next to the Percent Change label. If this value is negative, the new order of dimensions consumes less memory and is therefore more efficient.
- Repeat steps 3 through 5 until you achieve the most efficient ordering of dimensions.
- Click OK.
Results
Using the Optimizer, you’ll be able to validate the effectiveness of your dimensional order choices to insure cube efficiency as well as build your design expertise!
