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...
Ordering of Dimensions
-
- Posts: 44
- Joined: Thu May 01, 2014 12:46 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Ordering of Dimensions
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...
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...
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 44
- Joined: Thu May 01, 2014 12:46 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
Re: Ordering of Dimensions
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!!
Thanks in advance!!
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Ordering of Dimensions
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'.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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly