Moving measure dimension in Cube optimization

[Jodha]

we are running out of system RAM so i focused on dimension re-ordering to reduce cube sizes . There is one large summary cube which is of 12 GB big and it is taking large chunk of Sytem RAM.
When i checked measure dimension there is one string element "Comment" which is used to load comments and this is only string element present in entire cube. As we are not loading any comments i deleted comment string element from measure dimension, making entire dimension as numeric dimension and moved measure dimesnion from bottom to top in dimension re order, which yielded surprising results . I was able to reduce cube size from 12 GB to 3 GB just by deleting one string element and just re ordering one dimension .Is it advisable to move measure diemnsion from last to top in dimension re ordering . I read some where having measure dimension as last element in dimesnion re ordering is best pratice and always have Time and measure diemsnions as last dimensions.


My summary cube is of 14 dimensions with 3 dimensions used for Time. I am on windows server 2008R2 with TM1 10.2.2 .

If there is any negative effects on performance by moving measure dimension please advise .

Waiting for your valuable inputs .

[BariAbdul]

As Gurus mentioned numerous times ,it is best practice to have time dimension as last dimension and string element must be part of the last dimension.I don't know what performance gains you got by moving measure dimension as first dimension.But that's about it.Thanks

[Alan Kirk]

we are running out of system RAM so i focused on dimension re-ordering to reduce cube sizes . There is one large summary cube which is of 12 GB big and it is taking large chunk of Sytem RAM.
When i checked measure dimension there is one string element "Comment" which is used to load comments and this is only string element present in entire cube. As we are not loading any comments i deleted comment string element from measure dimension, making entire dimension as numeric dimension and moved measure dimesnion from bottom to top in dimension re order, which yielded surprising results . I was able to reduce cube size from 12 GB to 3 GB just by deleting one string element and just re ordering one dimension .Is it advisable to move measure diemnsion from last to top in dimension re ordering . I read some where having measure dimension as last element in dimesnion re ordering is best pratice and always have Time and measure diemsnions as last dimensions.

My summary cube is of 14 dimensions with 3 dimensions used for Time. I am on windows server 2008R2 with TM1 10.2.2 .

If there is any negative effects on performance by moving measure dimension please advise .

As you appear to be well aware, you do need to have strings in the last dimension only. This restriction also applies to reordering dimensions as this documentation page discusses:

You should be aware of the following limitations when reordering dimensions in an IBM© Cognos© TM1© cube that includes a string dimension.

If the last dimension in the original order of dimensions is a string dimension, that string dimension must remain as the last dimension in the new order of dimensions. You cannot change the order of a string dimension that exists as the final dimension in a cube definition.
If the last dimension in the original order of dimensions is a numeric dimension, the last dimension in the new order of dimensions must also be a numeric dimension. You cannot replace a numeric dimension in the original order of dimensions with a string dimension in a new order of dimensions.

If you do not observe these limitations, you will receive a 'Dimension re-ordering failed' error when attempting to reorder the dimensions in a string cube.

Taking the Comments element out unshackles you from that, as you found.

With regard to the other point that you raise, it's important to remember how vital to TM1 the definition of the Time and Measures dimensions is.

Specifically, not at all. Not one iota. Not the vaguest bit.

With the exception of the strings restriction discussed above, TM1 is dimension-agnostic. It doesn't care which dimension goes where, though of course your choice of that will determine the memory efficiency of the cubes. And unfortunately that issue itself is not so much science as an art as discussed in this thread (which is referred to in the Cube Design section of the FAQ)

This is what the documentation has to say about Measures and Time dimensions:

Editing Cube Properties

TM1® allows you to set cube properties that specify measures and time dimensions used by OLE DB for OLAP applications, and that determine whether a cube loads automatically or on demand. Usually, you set these cube properties when you create a cube, but you can edit the properties any time.

Editing Measures and Time Dimension

OLE DB for OLAP client applications include provisions for measures and time dimensions. Even though TM1® clients do not include such provisions, you can use TM1 to set measures and time dimensions for cubes that you access by OLE DB for OLAP clients.

(Emphasis added.)

However given that you are referring to having "3 dimensions used for Time" it would seem that you are referring to "dimensions that represent time" rather than "the time dimension" in the cube property sense. (Since in the cube properties you can set only a single dimension as "the" time dimension.)

If that is the case then consider the implications of "always have Time and measure diemsnions as last dimensions". If you have, say, a Year, a Period, and a DayOfWeek dimension (which I'd guess is the sortof thing that you're describing)... how would TM1 even know that they are time dimensions? You know that those three represent a period of time but to TM1 they're just a collection of elements; places to store values. Consequently it can't possibly matter to TM1 which position the dimension appears in. Similarly what you consider to be the "measures" dimension is "just another dimension" to TM1 unless it has string elements. Even if it's flagged as the measures dimension in the cube properties, TM1 doesn't think of it as a measures dimension simply because that concept doesn't exist for it. It's there purely for the OLE DB OLAP clients that need it.

It is conventional to have the measures dimension as the last dimension for two reasons:
(a) It allows you to put a string measure in if you need to; and
(b) It's (arguably) more intuitive for the users to have the last dimension of any cube as "what the value is" (sales, purchases, wages, etc) and the other ones as "where the value lives" (profit centre, period, company, etcetera). However this is achieved if you originally build the cube that way anyway since the users still see the dimensions in their original order, not in the way that you might later rebuild them. (Including moving the dimension to the top as you have done.)

As for the time dimensions... I'm not sure where you read that. At a guess it may have been advice from someone suggesting that you be consistent in the ordering of your dimensions to keep them familiar to the users. Or it may have been someone who is aware of the Short To Long preferred dimension order, discussed in the thread that I pointed to earlier. Time dimensions (at least if you use only one dimension for time) tend to be rather long and therefore often tend to go lower down in the list. This would be less true if you're using multiple time dimensions as you are. And in either case, it's not, as I said, mandatory because the fact that you as a human see a particular dimension as representing time, doesn't automatically mean that TM1 as a non-sentient[1] bunch of algorithms does.

----------------------
[1] TM1 is non-sentient as of version 10.2 anyway. Given the increasing reliance of IBM on Java, I hold no particular fears that my server will start to emulate Skynet any time soon. It'll be too busy trying to load a splash screen and render chunky, nondescript icons to have time to worry about destroying mankind, much as having Performance Muddler as an interface would give it a very great reason to wish to do so. Consequently I expect that its failure to care whether a particular dimension represents measures, time or other value characteristics will continue indefinitely.

[lotsaram]

Ignore what BariAbdul told you as it is wrong.

Read what Alan told you for understanding there are some good points there.

If there is any negative effects on performance by moving measure dimension please advise.

As to your specific question, the answer is no. A smaller cube is a faster cube. If you can change the dimension order and achieve a massive reduction in memory size (with all the same data points) then chances are you will have also massively boosted the query performance of the cube. While the relationship between memory saving from dimension reordering and query time isn't strictly inverse linear it is in that direction. I also observerd a fairly massive gain from dimension reordering very recently (also coincedentally from moving the measure dimension to first in a 14 dim cube which I also did not expect) the saving was 95% and a 23 GB cube shrunk back down to 1.2 GB, a much more maneagble number. The cube was 20x smaller but it wasn't 20x faster, it was faster but only about 2 - 3 x which is still something for users to be happy about.

Don't assume however that you should now always reorder the measure dimension to be first not last. The optimal dimension order is a unique case by case determination that depends on both dimension size and the relative density of populated vs. non-populated N elements in each dimension, that is it depends on the pattern of data that the cube holds. Over time as more versions are used, more time periods hold data, whatever, the optimal dimension order could change, it could be that in 1 years time the cube has a different optimal dimension order

[BariAbdul]

Sorry.Lotsaram,I meant measure dimension actually.Thanks for correction.

[Jodha]

Thanks All for your valuable suggestions.

Alan your detail explanation is very helpful.

Lotsaram - Thanks for sharing your experience. It gave me boost to implement the same in Production.

[rmackenzie]

I've looked at this before and the standard advice (i.e. hasn't change since the Applix days) is:

An "optimum" ordering of the dimensions of a cube would simply order the dimensions in increasing order of density. A more complex algorithm could be devised where the order is calculated by first picking the densest of all the dimensions and making it the last. The next-to-last dimension would then be picked looking at the remaining cube - treating each group of the last dimension as a single cell, and computing dimension densities on that basis. The process is then repeated until all dimensions are selected.

Lotsaram makes the comment that:

Don't assume however that you should now always reorder the measure dimension to be first not last. The optimal dimension order is a unique case by case determination that depends on both dimension size and the relative density of populated vs. non-populated N elements in each dimension, that is it depends on the pattern of data that the cube holds.

This is true, but the reason for the 'measures go last' heuristic is that it is more likely that e.g. you measure product performance across time generally, and to a lesser extent across centres, regions, reps, companies etc. The point being that typically it is the 'analytic' dimensions, not the measures, that define the sparsity AKA non-density.

So, if you know that some measures are not populated e.g for every month, or not for every scenario; then you can look at putting measures somewhere other than in last place in the dimension order. However, I'd comment that from a design point-of-view, that if you had time-specific measures, or scenario-specific measures then you may have left something to be desired in the measure dimension design.

Hope that helps - it is not the easiest one to get ones head around.