TM1 Forum

What is more effective?


OPTION 1
MAR YTD
MAR
FEB YTD
FEB
JAN


OPTION 2
MAR YTD
MAR
FEB
JAN

I think option two is because you do not need to waste memory consolidating a consolidation and storing that in RAM. However, my predecessor used option one and I do not know why and I do not want to change it without understanding the reason. AKA without you guys to blame

Hi Eric,

I'm not sure which is more efficient given that I haven't compared both approaches, but I doubt the differences are very significant. I know in the past there were some TM1S.CFG options (now deprecated, I think, like ViewConsolidationOptimization) to make sure TM1 didn't go through duplicated effort when adding up data from rollups in other rollups. For example, if you used Q1 2008 in a Full Year 2008 rollup, there's no sense in recalculating all the months in Q1 2008 again for the Full Year total if someone has already requested that calculation and TM1 has performed it. Or that parameter may also have been used if the same rollup appeared in multiple places - I forget which.

Now, from a maintainability standpoint, we've usually recommended re-using other rollups in other rollups because it requires less of a burden of dimension maintenance. For example, if, instead of creating a Gross Margin with Sales and Cost of Sales rollups you listed all of the Sales and Cost of Sales N-level accounts under Gross Margin, that is going to be a maintenance headache since every new Sales/Cost of Sales account has to be added in multiple places in the account dimension. For a time dimension this isn't as big of a concern since Mar 2008 YTD is probably not going to change on you, but that might be why your predecessor followed that approach.

My guess is that you'd probably find more efficiencies in other places (rules, cube order, etc) if you're looking to tune up your models. Dimension calculations I've never really seen as a target for performance gains (beyond cleaning up any clutter).

Regards,

My only take is that from a display point of view the second option looks better for the user. As soon as they expand the consolidation you can see the children in a view. If you chose option 1 it takes a lot of expnading and it doesn't look good. (Especially once you reach period 11.)

Jim.

from a maintainability standpoint, we've usually recommended re-using other rollups in other rollups

I absolutely agree with Mike, with the one exception of YTD and other rollups in time dimensions. I think there is precious little difference from a calculation perspective as regardless of hierarchy TM1 in effect applies the relevant weightings from N level when consolidating. This preference is from a user perspective as with the other approach you end up with very deep hierarchies which is cumbersome (and ugly) for users to work with. As time dimensions are basically static apart from annual maintenance/rollover the small additional setup effort is worth it IMO.

ScottW wrote:...you end up with very deep hierarchies which is cumbersome (and ugly) for users to work with. As time dimensions are basically static apart from annual maintenance/rollover the small additional setup effort is worth it IMO.

Great point, Scott -- I have worked with both constructions of time dimensions and I really hate it when I can't get YTD with a Select by Level (and the fact that the levels list is so long) when you nest YTD rollups within one another.

Mike Cowie wrote:

ScottW wrote:...you end up with very deep hierarchies which is cumbersome (and ugly) for users to work with. As time dimensions are basically static apart from annual maintenance/rollover the small additional setup effort is worth it IMO.

Great point, Scott -- I have worked with both constructions of time dimensions and I really hate it when I can't get YTD with a Select by Level (and the fact that the levels list is so long) when you nest YTD rollups within one another.

Do no people read my posts? I said exactly the same thing earlier?

Just to repeat other people some more

It makes no difference to speed. When TM1 does consolidations at a higher level it doesn't do the calculation at the mid level, it goes straight down to the N level element and works it out from there, skipping all the in-between consolidations. The in betweens are only calculated if you actually look at them (or explicitly use that rule that says ConsolidateChildren).

Martin

Thank for confirming my thoughts. Also glad to hear about not calculating mid level consolidation. Another two random thoughts

1) Does everyone suffer from thinking their predecessor had no clue what they were doing?
2) Is my predecessor going to think this about me?

I'm posting after you so I can confirm you don't know what you're doing!!!!

jim wood wrote: Do no people read my posts? I said exactly the same thing earlier?

Sorry, Jim - I was going top-down in my inbox and in that rush didn't give credit.

Or maybe it was your contribution to all the posts concerning wives -- my natural instinct when I saw the word "wife" so much was to start tuning you out.

Regards,

I only mention the W word in the general channel. We have enough horror words on the TM1 board without adding that as well! But talking of her, she doesn't pay attnetion to anything I say either so I suppose I should be used to it by now.

1) Does everyone suffer from thinking their predecessor had no clue what they were doing?
2) Is my predecessor going to think this about me?

Interesting comment!
I think that TM1 suffers from this alot since there is so much "style" involved in the build of systems. I think it's also interesting that we are all self-taught to a greater or lesser extent. There's no professional qualifications like there is with relational DBs.

Of course I'm sure no one has cursed me when they have had to look after a system I built.

Cheers,

Don't worry Steve, we know exactly what you were up to - anyway at least to the extent that you and the clients did! But don't let the paranoia level drop too low...

jim wood wrote:I'm posting after you so I can confirm you don't know what you're doing!!!!

Don't give away my secrets. We all know I don't even know how to log in to TM1. I just post a question of what I need to do and wait for Captain Kirk to post an answer. Thanks you for the paycheck Allen!

Martin Ryan wrote:Just to repeat other people some more
It makes no difference to speed. When TM1 does consolidations at a higher level it doesn't do the calculation at the mid level, it goes straight down to the N level element and works it out from there, skipping all the in-between consolidations. The in betweens are only calculated if you actually look at them (or explicitly use that rule that says ConsolidateChildren).

That's what I thought as well; but I've always wondered how that gels with a consolidation structure like the one shown in the attachment.

The consolidation "Profit Or Loss" is made up of two further consolidations; Revenue, weighted as 1, and Expenses, weighted as -1. Within expenses you also have a consolidation (Net Interest) which has an N level component weighted as -1.

The only thing I can figure is that EITHER:
- it DOES have to calculate the intermediate values to take into account weightings OR...
- Each consolidation stores with it in memory the effective weightings of all of its N level descendants.

My bet is on option 2, but short of cornering Manny P and threatening to make him post on Cognos Communities until he tells us we'll probably never know for sure.

Eric wrote:Thank for confirming my thoughts. Also glad to hear about not calculating mid level consolidation. Another two random thoughts

1) Does everyone suffer from thinking their predecessor had no clue what they were doing?
2) Is my predecessor going to think this about me?

If your predecessor is going to think that about you in the future, it will probably be in relation to your grasp or otherwise of 4 dimensional space-time.

In particular, the 4th dimensional part.

Although some of my cubes are getting large enough to have their own gravity fields, so maybe my own predecessor can come after me if I get some Hawking-style monkey-business going around their event horizons.

(Who said this Forum was getting too nerdish??? WHO???)

Hi Eric

I generally use the chaining method for YTD, eg

Mar YTD
Mar
Feb YTD

However, this is because I generally use one time dimension (Year and Month combined) so it is really

CY_2005-03_YTD
CY_2005-03
CY_2005-02_YTD

To select YTD a user can select *_YTD using selection by regular expression

However, to make it easier I generally create a hierarchy eg

CY_2005_YTD_Grp
CY_2005-01_YTD
...
CY_2005-12_YTD

CY_YTD_Grp
CY_2000_YTD_Grp
...
CY_2009_YTD_Grp

The weight on the YTD Grps and the YTDs rolling up to the YTD Grps is 0 so users don't see any odd numbers.

Using the same approach I construct a top level hierarchy like this (using Dimension Worksheets)

CY_All_Month_Templates
CY_All_Month_Templates excl Grps
CY_All_Time_Periods_H
CY_YTD_Grp
CY_CTD_Grp
CY_OTD_Grp
CY_RollYr_Grp
CY_RollYrAve_Grp
CY_YoYGrowth_Grp
CY_YoYGrowth_Ratio_Grp
CY_PerGrowth_Grp
CY_PerGrowth_Ratio_Grp
CY_Season_Grp

This allows the user to select from Years going down to Months, Years going down to Half Years, Quarters then Months, YTD, Cumulative to Date, Opening balance to Date, Rolling Year, Rolling Year Average, Year on Year Growth, Year on Year Growth Ratios (calc via rules), Period Growth and Ratios, Seasons, etc.

I have even extended this to give an alternate Financial Year hierarchy, which is a very difficult problem to solve with the two time dimension approach.

By using hierarchies for grouping rather than just for consolidation, and by using a weight of zero to stop duplicate consolidations, you can give a lot of time series functionality, but still make it quite manageable for the user.

Of course, if you go for the single time dimension approach, then the YTD method of consolidating from all base items becomes a little unwieldy, and I certainly wouldn't want to do it with CTD.

Consolidation is much faster than rules, generally about a 100 times faster, so if you can do a calculation via consolidation then, from the performance point of view you should do it.

Some people prefer the two dimension approach ie Year and Month from a useability approach, but I find the single time dimension approach more useable in many ways.

Going back to the original point for YTD either consolidation approach is likely to be just as efficient.

Interestingly I recall some earlier work where they found that dimensions that had a very large fan out, eg 1000 elements going in to one top level element actually benefited from having some random sub-consolidations eg all elements starting with '1' as a sub consolidation. I think that this was because it allowed TM1 to make use of the intermediate consolidations to avoid having to check so many cells before it got to the top level number. So I think that in some cases TM1 may take advantage of intermediate consolidations, but I believe that it won't do this unless it is to its advantage.

Regards


Paul Simon

Paul,

Thanks! That was very informative especially since you go into the 2 dimension vs 1 dimension for time explanation. Personally I am a fan of the 2 dimension approach, but have seen the advantages of both.