Dimension Order

[davekerby]

Hi Guys,

Im pretty new to TM1 and was wondering if i could get some advice with the dimension order within the TI Cube creation. I want to try and optimize performance as well as take minimum memory space

Please find attached my dimensions


Any help would be appreciated

Dave

[lotsaram]

Hi Dave,

I take it you have already read the "designing cubes" section of the developers guide? ... and you want more information on the mythical "cube optimization or dimension ordering"?

The "smallest densest to largest sparsest" rule of thumb was summed up pretty nicely in this post by Mike Cowie.

For performance optimization dimension order can be set and re-set "behind the scenes" using the re-order dimensions function. From a design perspective it is much more important to have a consistent ordering convention as this will make building reports and queries many times easier for both you and your power users. For a "finance" type cube a "typical" dimension order might be:
- scenario, year, month, company, cost center, account
and for a "sales" type cube a "typical" order might be:
- scenario, year, month, BU, region, product, measure

It is good practice to have a separate measure dimension for each cube (it looks like you don't at the moment!).

Getting back to the "smallest densest to largest sparsest" rule of thumb there's some contention as to whether dimension density or size is the more important factor. As Mike pointed out the two axes of small->big and dense->sparse can be contradictory. I tend to suspect that density is generally more important but it is much easier to order based on size as density is dependent on data in the cube and is likely also to vary over time. There is one product I know of that will automatically find the optimum dimension order using an algorithm but other than that it is manual trial and error and once you have more than a handful of dimensions there is no way to go through all the possible permutations so small to big is a good place to start.

[davekerby]

Hi, Thanks for your post it was very useful. I went down the route of ordering my dimensions small to big and that seemed to reduce the size of the cube in memory. However is still quite large at 4gb. I think the next excise is to work out the sparse dimensions and put that theory into practice..

If there are any other tips or tricks to optimising performance of a cube then let me know.

Cheers Dave

[robchr]

If you don't get it right the first time in terms of memory usage, there is always the Reorder Dimensions wizard, which you can use to test different dimension orders in the cube without making any changes.

Just right-click on the cube you made and select Re-order dimensions. Play around with the order and click test. Negative percentages mean that you saved that percentage of memory using the new order. Negative good. Positive bad.

[davekerby]

Thanks, Just to understand that a bit more. Am i right in saying the test dimension order rebuilds the cube with the new dimension order in the background on the server? So would I need the same amount of memory of the current cube available on the server for this test?

Regards
Dave

[Steve Rowe]

Correct, so if you are working in 32-bit world and you are working on the main cube, then there is a good chance to crash your server due to the memory limits.

Once you accept the changes though you only have the one cube again.

64-bit shouldn't be an issue though

[David Usherwood]

Oh yes.
Since you must be running 64 bit the server should not die though it could swap quite heavily.

[RajKerai]

I have a follow on question on re-ordering dimensions.

Has anyone experienced degradation in query times on a ruled cube after having optimized a cube (i.e. reduced the cube size) using the dimension ordering wizard.

[paulsimon]

Raj

Yes - Sometimes the optimal order in terms of smallest cube size is not necessarily the optimal order in terms of response time, however, most of the time it is. It is worth carrying out a few tests on a test box with the main reports that are used.

Regards


Paul Simon