Time dimension consolidation - help with rules needed

[Sighurd]

Many of you with Cognos Enterprise Planning experience remember 'First Period' and 'Last Period' settings. They were very useful for working with all sorts of opening and closing balances.

I tried to model this behavior using attributes. My real life cube has several big dimensions. I made a very simple demo cube with following dimensions: time, managers, turnover (calculation dimension). I made several rules and defined several attributes. I have an issue with consolidation, it is highlighted. You can see it all on attached screenshot.

I tried to experiment with filling up 'first' and 'last' attributes of months, but it resulted in wrong results or circular references. I think I finally found a way to solve the problem. I uncommented the line with 'ConsolidateChildren' instruction. The result can be seen on this screenshot. It seems to work.

I tried to apply this solution to my real life task and discovered that my cube recalculate time changed from split second to 4-5 seconds. I am convinced that the solution I found is not optimal. Can you share some best practice advice? I am sure this situation if far from unique, and there must be a better solution.

[declanr]

Out of interest why haven't you created "closing balance" as a consolidation?
TM1 Consolidations will (almost) always calculate quicker than a rule.

Other than that you are correct and this has been discussed a number of times and searching the forum will give you a few pointers.

[Sighurd]

In my task the calculation dimension is quite big with a lot of elements and calculations, so I tried to keep it plain and simple at first. Now I decided to check if I could move some rules to consolidations, and it appeared that I could.

Back to my demo cube. I applied consolidation logic to closing balance. It made one rule obsolete, but took my ability to create different rules for N and C levels, as 'closing balance' is now always C level. So I had to become creative. This is the best I could manage so far. I moved this logic to my main task and it works fast, so I am mostly happy.

How can it be improved further?

[declanr]

In my task the calculation dimension is quite big with a lot of elements and calculations, so I tried to keep it plain and simple at first. Now I decided to check if I could move some rules to consolidations, and it appeared that I could.

Back to my demo cube. I applied consolidation logic to closing balance. It made one rule obsolete, but took my ability to create different rules for N and C levels, as 'closing balance' is now always C level. So I had to become creative. This is the best I could manage so far. I moved this logic to my main task and it works fast, so I am mostly happy.

How can it be improved further?

ConsolidateChildren() is a very useful function but if it's not completely necessary I would tend to avoid it and I think in your case putting an IF statement into you opening balance calculation at C-level to just use standard TM1 consolidation where the Managers dimension is a consolidation would save you having to use it.

[Sighurd]

ConsolidateChildren() is a very useful function but if it's not completely necessary I would tend to avoid it and I think in your case putting an IF statement into you opening balance calculation at C-level to just use standard TM1 consolidation where the Managers dimension is a consolidation would save you having to use it.

During my previous experiments I noticed productivity drop when ConsolidateChildren() was used, so I would be happy to get rid of it. As of now I need to put ConsolidateChildren() for all my non-time dimensions, and I don't like it, I would prefer standard consolidation if possible. What kind of IF statement you think I should insert into C-level of opening balance calculation?

[lotsaram]

During my previous experiments I noticed productivity drop when ConsolidateChildren() was used, so I would be happy to get rid of it. As of now I need to put ConsolidateChildren() for all my non-time dimensions, and I don't like it, I would prefer standard consolidation if possible. What kind of IF statement you think I should insert into C-level of opening balance calculation?

You are taking the wrong approach, you shouldn't need consolidate children at all. Typically CB is a consolidation of OB and period movements. Depending on the model and source system OB can either be data entry for each period or rule calculated (and fed, which seemed to be your 1st problem) as the CB of the previous period. This reduces everything to a N level rule to calculate the OB. Where you might need rules is to override the natural consolidation so that balances display the balance value and not an accumulation. Normally you do this with a lookup of the last N period in the consolidation. However this is something that you need to do for overriding consolidation (C rule only) for the time dimension, usually you wouldn't need to do this for the business dimensions as these should consolidate.

[Sighurd]

You are taking the wrong approach, you shouldn't need consolidate children at all.

I'd like to avoid it too. I was overly optimistic yesterday, performance problems returned. If I turn off 'consolidate children' rules, they are gone.
But then I have this problem.

Typically CB is a consolidation of OB and period movements. Depending on the model and source system OB can either be data entry for each period or rule calculated (and fed, which seemed to be your 1st problem) as the CB of the previous period.

In my last screenshot CB is a consolidated item, I have a feeder, which I am not sure is right.

Code: Select all

['closing balance']=>DB('turnover',ATTRS('months',!months,'next'),!managers,'opening balance');

Any help appreciated.

This reduces everything to a N level rule to calculate the OB. Where you might need rules is to override the natural consolidation so that balances display the balance value and not an accumulation. Normally you do this with a lookup of the last N period in the consolidation.

Currently I do it using 'first' and 'last' attributes in time dimension. Do you mean that I have to look up for first or last period of consolidated item with a rule?

However this is something that you need to do for overriding consolidation (C rule only) for the time dimension, usually you wouldn't need to do this for the business dimensions as these should consolidate.

My problem seems to be on leaf levels of time dimension (months) AND aggregated levels of other dimensions (managers). OB tries to use C:level calculation, but month on leaf level does not have 'first' attribute. If it had, it would create circular reference. So I need to use a different approach, obviously.

[tomok]

Why are you making this so difficult? This is a very common scenario, I've used it on multiple occasions without issue. It should work fine like this:

Code: Select all

skipcheck;
['jan','opening balance'] = STET:
['opening balance'] = N:db('turnover',ATTRS('months',!months,'prev'),!managers,'closing balance');

feeders;
['closing balance'] => db('turnover',ATTRS('months',!months,'next'),!managers,'opening balance');

[Sighurd]

Why are you making this so difficult? This is a very common scenario, I've used it on multiple occasions without issue.

I agree it's very common. I make it so difficult because I am only learning, and don't know all efficient approaches and best practices. Additionally, Enterprise Planning experience takes it's toll sometimes.

This happens if I leave only what you wrote.

This is my next iteration. I got rid of 'ConsolidateChildren', added one more check for opening balance (OB) in the earliest periods, and made OB rule universal for N: and C: levels. Seems to work just fine. Going to test it on my real task to see if it works fast enough.

[tomok]

Didn't know that you were expecting to look at quarters and the year separately. You just need one off rules for each quarter and then the year:

Code: Select all

['Q1','opening balance'] = C:['jan','opening balance'];
['Q2','opening balance'] = C:['apr','opening balance'];
['Q3','opening balance'] = C:['jul','opening balance'];
['Q4','opening balance'] = C:['oct','opening balance'];
['Year','opening balance'] = C:['jan','opening balance'];

You need to make sure this line is in your TM1S.cfg file:

AllowSeparateNandCRules=T