Ordering of Dimensions

Post Reply
krithika331
Posts: 33
Joined: Thu May 01, 2014 12:46 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2010

Ordering of Dimensions

Post by krithika331 » Sat May 19, 2018 7:52 am

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...

User avatar
Steve Rowe
Site Admin
Posts: 1765
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Ordering of Dimensions

Post by Steve Rowe » Sun May 20, 2018 5:35 pm

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...

krithika331
Posts: 33
Joined: Thu May 01, 2014 12:46 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2010

Re: Ordering of Dimensions

Post by krithika331 » Fri May 25, 2018 6:26 am

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!!

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Ordering of Dimensions

Post by Wim Gielis » Fri May 25, 2018 7:43 am

krithika331 wrote:
Fri May 25, 2018 6:26 am
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.
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'.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply