Dimension order for a cube.

[deepakjain2020]

Hi All,

We are having a cube with 7 dimensions.

Currently the order of dimesions

1. DIM1 having 249 elements
2. DIM2 having 419 elements
3. DIM3 having 898 elements
4. DIM4 having 2 elements
5. DIM5 having 23 elements
6. DIM6 having 66 elements
7. DIM7 is a measure dimension having 4 elements.

After going through best practices @ http://www.bedrocktm1.org/resources/fil ... Design.pdf
I thought of having the order as mentioned below.

1. DIM1 having 2 elements
2. DIM2 having 23 elements
3. DIM3 having 66 elements
4. DIM4 having 249 elements
5. DIM5 having 419 elements
6. DIM6 having 898 elements
7. DIM7 is a measure dimension having 4 elements.

I will like to know what would be the best way to order dimensions for better performance.

[Alan Kirk]

I will like to know what would be the best way to order dimensions for better performance.

There's no one right answer for that; in fact in the FAQ there's a link to a thread which discusses this.

The issues are:
(a) Just because you have n elements now, doesn't mean that you will in the future and;
(b) Sparsity is likely to change over time as more data is added. What is dense when you start may become sparse over time. This would be especially true of something like a Product dimension, as new ones come in and old ones are phased out. Suggest that you take a look at the thread that's linked to in the FAQ to see the discussion involved. Ultimately all you can do is make your best estimate. You can of course reorder the cube later if it turns out to be too much of an issue but these days it's more important to build for speed over just size.

[jim wood]

To your point Alan, if you're using the cube optimizer (darn US spell checker) the original cube order has less of baring on things.

[deepakjain2020]

Yep, your suggestions in FAQ are helpful.

When we reorder dimensions, we will have to make changes to rules already written, is that right?

Is there anyway to make changes automatically?

Regards,
Deepak Jain

[Alan Kirk]

Yep, your suggestions in FAQ are helpful.

When we reorder dimensions, we will have to make changes to rules already written, is that right?

Is there anyway to make changes automatically?

No, the rules syntax works off the original order, as do worksheet formulas and the like. The rearrangement for all intents and purposes only happens internally. However the biggest gotcha is that in some versions dimension reordering can screw with dynamically created view data sources in TIs. Steve Vincent provides good coverage of that in this thread.

[deepakjain2020]

Thanks Alan

[Duncan P]

Also for some reason the BI studios show the reordered dimensions.