Feed from C level

Post Reply
CRP0021
Posts: 27
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Feed from C level

Post by CRP0021 »

I would like to feed from a consolidated element to leaf level element and am looking for an optimal way to do this.

Here's the scenario....

My rule in the target cube is populating leaf elements with values from a consolidated element in my source cube. Rule and Feeder currently working fine as is, looking to see if there is an optimal way to feed from a consolidated to a leaf element. As the model grows this feeder will become more inefficient I believe and am looking for alternate ideas.

RULE:
['Amount']=N:
DB('rp_SourceReporting',!Account,!Line Item,!Currency,!Entity,!Scenario,!Time | ' CTD,'Amount')

FEEDER:
['Amount']=>
DB('rp_TargetReporting',!Account,!Line Item,!Currency,!Entity,!Scenario,'(All Time)','Amount');

Currently I'm feeding the (All Time) roll up as my leaf elements that are being populated with the consolidated values will be fed.

Time Dimension hierarchies:

(All CTD)
2016-1 CTD
2016-1
2015-12 CTD
2015-12
2015-11 CTD
2015-11
2015-10 CTD

(All Time)
2016
2016-Q1
2016-1
2016-2
2016-3


Thank you!
Wim Gielis
MVP
Posts: 3105
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: Feed from C level

Post by Wim Gielis »

Hello,

Welcome on-board.

Question. Elements like "2016-xxx CTD", are they consolidated ?
If not, can't you feed from an n-level element to the element that you get when you chop off the ' CTD' ?
The inverse of adding ' CTD' in the rule, could be a SUBST that takes of the ' CTD' at the end.
If an element does not contain the ' CTD' at the end, the feeder might be targeting an element in the other cube that does not exist,
but that's not an easy. The feeder will not be put.
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
CRP0021
Posts: 27
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: Feed from C level

Post by CRP0021 »

Hi Wim thanks for the response!

Yes the elements you see with the CTD are all consolidated.

Basically it means "cumulative to date" and each Parent has the preceding Parent + the month as children.

So as an example if you wanted to look at a cumulative balance at December 2016 my Time dimension aggregates in a hierarchy all the values from the beginning of time into this Parent.

Source Cube = Rule pulling from element CTD Apr 2014
Source.png
Source.png (6.72 KiB) Viewed 5791 times
Target Cube = Rule populating leaf element Apr 2014
target.png
target.png (5.59 KiB) Viewed 5791 times
If I feed from leaf elements the result will be unfed since there is a 0 value in Apr 2014 in my source cube.

Feed.png
Feed.png (18.13 KiB) Viewed 5791 times
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feed from C level

Post by tomok »

You're still calculating cumulative values from 2014???? Why? I can see the benefit of daisy-chaining periods together for rule-based forecast data but not for historical data. There is nothing to be gained from this and as your cube grows with each passing year, the performance will get worse and worse. What you should really do is change the TargetReporting cube so that historical data is brought in via a TI process and not a rule. Why does it need to be dynamic? Leave the rule and daisy-chaining of the cumulative balances for future periods.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3105
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: Feed from C level

Post by Wim Gielis »

This is a calculation for History-to-date values.

Just like you have consolidations in the source cube to calculate the History-to-date values,
I would use TI to create consolidations in the target cube; for each month there should be a (wisely named) consolidation containing that month AND all the future months. Then you can feed from an n-level element to the consolidated element (containing all future n-level months). You do not need to feed to '(All Time)' because then a month also feeds a month in the past. What's the benefit ?

Feeding towards a consolidated element feeds all n-level descendants of the consolidation, so watch out, this can lead to a lot of feeders if the time dimension spans quite some period. Obviously, as time progresses, feeding a consolidated cell will feed more and more cells, and this effect increases each time you add the 'next year'. As part of the yearly exercise of adding the next year, I would also remove one year from the history. Or at least, reduce some dimensionality by copying monthly values to only 1 month (december of the year for example), or take other actions - IF the cube is heavy because of the feeders. Backup the results of the past with TI.

Obviously, you might also want to explore TI to copy values from 1 cube to the other, eliminating the use for feeders.

First tell us why this needs to be so dynamic (rules and feeders).
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
CRP0021
Posts: 27
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: Feed from C level

Post by CRP0021 »

Hi Tomok,
The source database has transaction level detail from beginning of time.

When looking at cumulative balances at a certain point in time for Balance Sheet items the source data does not contain that information, which is why I have aggregated all periods in the hierarchy with the CTD concept.

Using a TI process is my 2nd choice but I have already written the code to do this.

Was really looking for any suggestions on how to feed from a C level element to a leaf level element so that I could weigh out which option (Rule or TI) works better and makes most sense going forward.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Feed from C level

Post by tomok »

CRP0021 wrote: Mon Aug 21, 2017 8:43 pm Was really looking for any suggestions on how to feed from a C level element to a leaf level element so that I could weigh out which option (Rule or TI) works better and makes most sense going forward.
I don't know what to tell you here, there really aren't any "suggestions" as to how to feed from a C level element to a leaf. You just do it. There aren't any options to choose from. In your case you are aggregating balances from the beginning of time (as you have defined it) and you are passing that aggregated value over to the reporting cube as a total. Now you need to feed it and you did it by feeding from the aggregate. This is the technically correct way to do it. The only options I can think of are to:

1) Alter your approach to don't use rules at all and just TI the data from Source to Target,
2) use a hybrid where you TI over the old stuff and just rule over the current year or,
3) Create a new measure in your reporting cube called "Feeder". Write a TI process that cycles through the target cube and writes a value of 1 to this new measure for each month in the target cube where the cumulative balance would cause a balance to appear. What I mean by this is you look at every intersection in the source cube and then for every month in the target cube from that date forward (you'll have to write some loops to do this) write a value of 1 in this new Feeder measure. This new measure will now contain a value of 1 in every month in the target cube where a balance is being ruled in from the source. You can now use this to feed the target. Something like:

Code: Select all

['Feeder'] => ['Amount'];
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Wim Gielis
MVP
Posts: 3105
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: Feed from C level

Post by Wim Gielis »

And what about my solution as a 4th option in your list ?

At least the feeder to (All Time) should be replaced with a feeder to 'a month and its months in the future' consolidation.
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
CRP0021
Posts: 27
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: Feed from C level

Post by CRP0021 »

These are great suggestions guys appreciate the feedback!

Cheers
Post Reply