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!
Feed from C level
-
- 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
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.
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
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
-
- Posts: 27
- Joined: Mon Aug 21, 2017 2:14 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Feed from C level
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
Target Cube = Rule populating leaf element Apr 2014
If I feed from leaf elements the result will be unfed since there is a 0 value in Apr 2014 in my source cube.
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
Target Cube = Rule populating leaf element Apr 2014
If I feed from leaf elements the result will be unfed since there is a 0 value in Apr 2014 in my source cube.
-
- 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
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.
-
- 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
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).
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
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
-
- Posts: 27
- Joined: Mon Aug 21, 2017 2:14 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Feed from C level
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.
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.
-
- 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
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'];
-
- 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
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.
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
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
-
- Posts: 27
- Joined: Mon Aug 21, 2017 2:14 pm
- OLAP Product: TM1
- Version: 10.3
- Excel Version: 2016
Re: Feed from C level
These are great suggestions guys appreciate the feedback!
Cheers
Cheers