Page 1 of 1

Ordering of Dimensions

Posted: Sat May 19, 2018 7:52 am
by krithika331
Hi,

I am trying to derive the logical ordering of dimensions for a sample cube. For this I have calculated the N level count of each dimension using the SUBSIZ in excel and also the populated cells for dimension by creating a view and checking the no of rows populated with the zero suppression ON. Then calculated the density. Can anybody please tell if this approach is correct? For a complex models in Real time also and to deal with the Performance, do we need to do the same approach?

For E.g.
Division = 20/60 = 33.33% (No of populated cells/ No of N level cells)
channels = 20/80 = 25
Product = 40/210 = 19%
Version = 20/20 = 100
Month = 50/120 = 41%

So Ordering of the dimension taken is as
1.Product
2.Channels
3.Divisions
4.Month
5.Version
6.Measure

Thanks in advance...

Re: Ordering of Dimensions

Posted: Sun May 20, 2018 5:35 pm
by Steve Rowe
So the rule of thumb is shortest sparsest to longest and most dense.

I don't think that density is as simple as (No of populated cells/ No of N level cells), it is more "if Element 1 is populated, if I keep all the other cell references the same, what is the likelihood of Element 2 being populated and so forth". This is not quite the same as populated ratios.

Its a good approximation though as you have got the order about right, though I would have put product at position 3, though and with the dimension sizes you are looking at it probably won't matter. The ones at the end are far more important.

I would expect to need to swap month and version around at some point as well.

The measure dimension though may be an issue, what is in it and why is it at the end? In general terms don't mix string and numeric data as it restricts your ability to reorder dimensions which can cripple a cube...

Re: Ordering of Dimensions

Posted: Fri May 25, 2018 6:26 am
by krithika331
Thanks a lot Steve. Just trying to understand like are there any other kind of testing/calculation in deciding the ordering of the dimensions in designing cube in order not to face the performance issues. I used to go to the Start Performance Monitor and check the memory size of the cube previously and change the design of the dimensions and ordering. But to gauge correctly, taking into the performance of the Cube and as best practice, could you please give some inputs.

Thanks in advance!!

Re: Ordering of Dimensions

Posted: Fri May 25, 2018 7:43 am
by Wim Gielis
krithika331 wrote: Fri May 25, 2018 6:26 amJust trying to understand like are there any other kind of testing/calculation in deciding the ordering of the dimensions in designing cube in order not to face the performance issues.
The usual reflex when investigating and looking for something you don't know, is to right-click an object, in this case a cube. There you will find 'Reorder dimensions'.