Ordering Dimensio in a Cube

[Acc_Moonwalk]

Hi Team,

I have one question in ordering a dimension when creating a cube ?

Dimensions are : Subsidiries , Versions, and ,Year ,and Measure dimensions.

I am ordering it in : 1) Subsidiries 2) Year 3) Versions 4) Measures

it it OK if i order it in this way, if not can you give me the correct order and explination why we need to give in that order.

Regards
Ashok

[tomok]

There is no "correct" order for how dimensions should be listed in a cube. There used to be before TM1 added the dimension re-ordering function but now that is moot. Put them in the order you want and then let TM1 decide the best order for performance after the fact.

[Gregor Koch]

Hi

There is a correct/mandatory order if you are using String elements in one of the dimensions - said dimension needs to be the last in the cube.
Also, and I haven't tested this in 9.5 and think the last time I have seen it happen was in a 9.4(.3?) version, you should not have more than one dimension with string elements in the same cube. Whenever I tried to browse one of those 'double string dim' cubes the server spit the dummy. Please check the documentation on this and it is really beside the question of correct order.

As for 'let TM1 decide the best order'... I personally don't trust the suggested order and have found many examples where I was able to still gain more than 30% improvement by manually setting the order after TM1 'said' this is the best order.

Cheers

[rmackenzie]

There is no "correct" order for how dimensions should be listed in a cube. There used to be before TM1 added the dimension re-ordering function but now that is moot. Put them in the order you want and then let TM1 decide the best order for performance after the fact.

Over time, there has been a combination of vendor advice and user/ developer experience (anecdotal or otherwise) to show that there are various different optimisation strategies that can be employed - predominantly, staging the dimensions from sparse to dense can realise a saving in memory footprint and you may experience a performance improvement. I'm not saying don't try it, but TM1's internal re-ordering function is not always consistent with this, and other strategies, and it is certainly not the end of the matter.

[tomok]

The internal re-ordering routine only works if your cube is populated to the same extent it would be in production. Just building it, and running the re-ordering process in an empty cube will not produce consistent results. The routine has never really produced significant savings for me because I always build my cubes going small-sparse to large-dense out of habit. However, there have been instances where it does produce savings. I always put my dimensions in the same order over all the cubes in my models, for user consistency sake, even when that violates the small-sparse to large-dense mantra. The re-ordering helps in those instances. Of course, YMMV.

[lotsaram]

The internal re-ordering routine only works if your cube is populated to the same extent it would be in production. Just building it, and running the re-ordering process in an empty cube will not produce consistent results. The routine has never really produced significant savings for me because I always build my cubes going small-sparse to large-dense out of habit. However, there have been instances where it does produce savings. I always put my dimensions in the same order over all the cubes in my models, for user consistency sake, even when that violates the small-sparse to large-dense mantra. The re-ordering helps in those instances. Of course, YMMV.

I think the point that Gregor and Robin were making is that the dimension order suggested by TM1 is in fact only very rarely the real bona fide optimal order and it is possible to gain much better memory footprint saving and performance gain by setting the order manually or using other tools which actually do always arrive at the optimal order.