A quick note on the decades-old topic of dimension order. In a nutshell: don’t really trust System Order, it ignores changing the last dimension and it’s always the most impactful one. Ignore the rest of the post and move on, carry on if you’re interested in my ramblings on it.

There’s a metric ton of info on dimension ordering for TM1 out there (google yourself, can lend you a shovel to dig out of that), thought I’d write up some of my thoughts on the topic while I wait for another cube to deflate :)

Commonly discussed logic of the dimension ordering is an hourglass (largest sparsest to smallest sparsest then smallest dense to largest dense). Sounds exactly as it should for a complicated and mysterious topic, but thankfully you’ve got the System recommended order to automate dimension selection for you. Unfortunately it ignores moving the last dimension around as it expects it to be a measure dimension that can contain strings and therefore always have to be last.

Therefore the easiest way to check if there’s any optimisation gains at is to manually test placing a largest dense dimension as last (of course if there are no string elements in the cube).

And then you pick the next dimension proceed to do the same if you’re billed by hour, or you just call it a job done.

Any changes to dimension apart from the last one are actually almost negligible, you’d probably have 15% change on all dimensions except the last and 85% in selecting the last one.

Dense dimension doesn’t mean ‘input’ cells, but also applies to stored calculated cells, so if you have a rule driven cube that has a fairly dense dimensions due to your rules&feeders (for example you’re not brave enough for feederless currency conversion), placing such ‘densely calculated’ dimension last will decrease all your stargate views.

My guess (not confirmed, just the way I’d do it) as to why the last dimension is impactful and crucial is that it forms columns of the arrays that are used in TM1 storage:

• Picking a ‘wide & dense’ dimension allows a good compression run on the data stored, number of such blocks and decreases the length of ‘key’ containing all the other dimensions that identifies the row in this block.
• This will also explain why string elements have to be last as data type will be defined by columns of such array as it’s really hard to have multiple data types within the same column based on row keys.
• This also explains the decreasing impact of shuffling the dimension order around after the last dimension is placed: you’re just working with the length of reference key (or width of a tree), but the biggest impact will be on decreasing the number of rows&blocks themselves.

Well, it just finished reordering, catch you next time :)