FEEDER Guidance DB Rule with Limited Scope

[normanbobo]

I'm having a mental block and am looking for help to develop the appropriate feeders for the following rule:

Code: Select all

['Projected Revenue'] =  N: ROUNDP(
                             if(ATTRN('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWk Offset Nbr') <= 1, 0,
                                  if(ATTRN('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWk Offset Nbr') = 2, 
                                               DB('GRID Cash Model',
                                                   !GRID Projects,
                                                   !GRID Agreements,
                                                   !GRID Activity Center,
                                                   ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id'),
                                                   !GRID Project Types,
                                                   'Revenue' )
                                                /
                                                DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', 
                                                              ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id') )
                                                *
                                               DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', !GRID Fiscal Weeks ) 
                                          ,
                                               DB('GRID Cash Model',
                                                    !GRID Projects,
                                                    !GRID Agreements,
                                                    !GRID Activity Center,
                                                    ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id'),
                                                    !GRID Project Types,
                                                    'Projected Revenue' )
                                                /
                                                DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', 
                                                             ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id') )
                                                *
                                                DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', !GRID Fiscal Weeks )
                                        )
                                   ) 
                            , 2) ;  C: STET;

I'm fairly confident I don't need to take into account the factor cube feeding. But I do need to take into account the Revenue measure. Any help is appreciated. Thanks.

[tomok]

I'll help you if you'll provide an english description of what the rule does. This one looks pretty complicated, or at least pretty involved. I don't have time to figure out what this one does AND help you figure out what the feeder should be.

[jim wood]

As a punt have you tried 2 variable feeders, one for revenue and one for projected revenue when meet the same criteria as the rule???

[normanbobo]

Jim and Tomok,

Thanks for the responses.

Here is a breakdown of what the rule accomplishes:

The rule is calculating future revenue projections based on prior revenue actuals and a set of factors.

Code: Select all

['Projected Revenue'] =  N: ROUNDP(
                             if(ATTRN('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWk Offset Nbr') <= 1, 0,

This first "if" tests the "FWK Offset Nbr" in the fiscal weeks dimension. The offset number is the number of weeks between the current element and the "current week" (as of date) of the cube. Positive numbers are in the future. Negative numbers are in the past. This filter allows us to process only future dated fiscal weeks for this rule.

Code: Select all

                                    if(ATTRN('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWk Offset Nbr') = 2, 
                                               DB('GRID Cash Model',
                                                   !GRID Projects,
                                                   !GRID Agreements,
                                                   !GRID Activity Center,
                                                   ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id'),
                                                   !GRID Project Types,
                                                   'Revenue' )
                                                /
                                                DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', 
                                                              ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id') )
                                                *
                                               DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', !GRID Fiscal Weeks ) 

We begin calculating the projections for the fiscal week with the offset of 2. This first calculation is based on the last week of full actuals. The prior fiscal week attribute contains the fiscal week element id for the prior fiscal week. So the first DB statement does a lookback to get the actual revenue ("Revenue" measure) for the prior fiscal week. It then multiplies and divides the prior fiscal weeks' actual revenue by two factors stored in another cube. These two factors are stored by week and are based on historical averages. I won't go into the lengthy calculations used to derive them. They are calculated outside of TM1 and transferred into the factors cube. The prior actual Revenue is first divided by the prior week's factor and then multiplied by the current week's factor.

Code: Select all

                                          ,
                                               DB('GRID Cash Model',
                                                    !GRID Projects,
                                                    !GRID Agreements,
                                                    !GRID Activity Center,
                                                    ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id'),
                                                    !GRID Project Types,
                                                    'Projected Revenue' )
                                                /
                                                DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', 
                                                             ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Prior FWk Id') )
                                                *
                                                DB('GRID Cash Model Labor Factors','Cash Model Labor Factor', !GRID Fiscal Weeks )
                                        )
                                   ) 
                            , 2) ;  C: STET;

This "else" clause is executed for all future weeks. Rather than looking at the actual revenue, it performs the same calculation as the prior calculation only using the prior Projected Revenue rather than the prior actual Revenue (as there is no actual revenue into the future).

In Pseudocode:

Projected Revenue =
Week 1: Prior Fiscal Week's Revenue / Prior Week's Factor * Current Week's Factor
Subsequent weeks: Prior Fiscal Week's Projected Revenue / Prior Week's Factor * Current Week's Factor

I am calculating the Projected Revenue at the N level and hoping to using the consolidation engine for rollups. Just having a brain drain on the syntax for this complicated feeder where I believe I need to feed:

1) Revenue Week N-1 => Projected Revenue Week N (where N is offset 2)
2) Projected Revenue Prior Week N-1 => Projected revenue Week N (where N is offset > 2)

What's the syntax for this? THANKS!!

[tomok]

Based on what you are showing here, it looks like 'Revenue', the actual stored amount, is in the same dimension as 'Project Revenue'. This is good because it will help simplify the rule. If we step back and look at the facts in simple terms, it looks like Revenue is the determing factor as to whether or not we are ever going to have Projected Revenue, so that is the base to feed from. At first glance, you want to try and right a feeder that mimics the logic that determines in what future periods you are going to have Projected Revenue. However, I think this is a waste of time because 1) as long as you have Revenue, you are going to have Projected Revenue in ALL future periods, and 2) there is no need to use IF to determine the future periods because that target is always going to be changing and there is no way to "unfeed". What I mean by this is that if your population of future weeks includes Week # 45, and this is currently Week #44, then come next week, Week #45 is no longer a future week and doesn't need to be fed. However, you have already fed it and cannot unfeed it without restarting TM1.

Since you can't unfeed without doing something to trigger it, may I suggest you not worry about the IF logic of feeding the periods and create a rollup of all the future periods and then just feed this consolidation. Something like:

['Revenue'] => ['Project Revenue','FuturePeriods'];

You can then maintain the FuturePeriods rollup via a weekly TI to drop off the old week. If this results in a slight overfeeding, since it will look at ALL Revenue data to feed, you could modify it to only feed from the most recent previous week. Something like this:

['Revenue','MostRecentWeek'] => ['Project Revenue','FuturePeriods'];

Where MostRecentWeek is an alias assigned to the week element that was the most recent. You can also maintain this assignment via TI. Keep in mind this is conceptual, not necessarily the proper syntax for you case. However, given your set of facts this is the way I would tackle it.

[normanbobo]

<grammar corrections on 2011-08-23>

Tomok, thanks for the response and helping me think through this.

2) there is no need to use IF to determine the future periods because that target is always going to be changing and there is no way to "unfeed". What I mean by this is that if your population of future weeks includes Week # 45, and this is currently Week #44, then come next week, Week #45 is no longer a future week and doesn't need to be fed. However, you have already fed it and cannot unfeed it without restarting TM1.

This is an unusual application in that it is "read only". We are not performing any "planning" per se. It is effectively a "light data warehouse" solution used purely for financial performance reporting. The entire application (all cubes) is fully re-built on a weekly basis. We will be stopping and starting TM1 as part of the build process (mostly to clear out any potential memory leaks -- gotta love Windows ).

Since you can't unfeed without doing something to trigger it, may I suggest you not worry about the IF logic of feeding the periods and create a rollup of all the future periods and then just feed this consolidation. Something like:

['Revenue'] => ['Project Revenue','FuturePeriods'];

You can then maintain the FuturePeriods rollup via a weekly TI to drop off the old week. If this results in a slight overfeeding, since it will look at ALL Revenue data to feed, you could modify it to only feed from the most recent previous week. Something like this:

['Revenue','MostRecentWeek'] => ['Project Revenue','FuturePeriods'];

Where MostRecentWeek is an alias assigned to the week element that was the most recent. You can also maintain this assignment via TI. Keep in mind this is conceptual, not necessarily the proper syntax for you case. However, given your set of facts this is the way I would tackle it.

The leaf members of the proposed MostRecentWeek consolidation are already known using the "FWK Offset Nbr" attribute in our fiscal weeks dimension, which is the attribute used in the original rule. The leaf members of the proposed 'Future Periods' consolidation are also already know using the FWK Offset Nbr attribute in the same way.

We are already building / calculating alternate consolidations in the fiscal weeks dimension similar to the proposed MostRecentWeek and FuturePeriods consolidations, but they are not "permanent". They are dropped at the start of the weekly loads and re-created at the end of the weekly loads (which take about 5 hours). The "current week" is determined based on the maximum dates in the fact data we are loading, which is why the alternate consolidations are not built until after the data is loaded. I believe that if a rule referenced one of these consolidations, it would be invalidated at the time we dropped the alternate rollup. I'm not sure how TM1 would react to the consolidation beind dropped, even though it would be re-created a few hours later.

Thus, I think I have no choice but to build a feeder which "reverses" the syntax of the rule using If statements. But I struggle with that syntax. Any further assistance would be greatly appreciated.
----------------

As I sidelight, one issue I am monitoring is overflowing the rule/feeder "stack". It is my understanding that "recursive" rules/feeders (e.g., referrring back to a prior element which refers to a prior element which refers to a prior element, etc.) are handled with a stacking mechanism which can only accomodate 256 items in the stack. It is my recollection that both the rules and the feeders are limited to 256 recursions. In other words, if the rule or feeder were to operate over 5 years of weekly elements, I would get 260 items and overflow the stack. By calculating and feeding only future periods (our fiscal weeks dimension is limited to a maximum of 2 future years -- current year and one more year), I am limiting the recursion to a maximum of 104 steps. In fact, since we are storing 5+ years of data, we did not even attempt to calculate Inception to Date values and instead calculated them outside of TM1 and are loading them in a TI process.

[normanbobo]

the following feeder statements failed to work:

Code: Select all

# Projected Revenues

['Revenue', {'FY-2011','FY-2012'}]            => ['Projected Revenue'];
['Projected Revenue', {'FY-2011','FY-2012'}]  => ['Projected Revenue'];

Consideration: 'Revenue' is actually a consolidation of other measures. Should I be feeding from the leaf measures that make up Revenue?

[lotsaram]

This is an unusual application in that it is "read only". We are not performing any "planning" per se. It is effectively a "light data warehouse" solution used purely for financial performance reporting. The entire application (all cubes) is fully re-built on a weekly basis. We will be stopping and starting TM1 as part of the build process (mostly to clear out any potential memory leaks -- gotta love Windows ).

Here's a thought. If you are rebuilding all the cubes from a data warehouse on a weekly basis and there is no data entry or "planning" taking place then why do you need rules at all? You know the logic for calculating projected revenue so why not translate the rule into TI and just load projected revenue as data, then your feeder problem simply ceases to exist.

[normanbobo]

lotsaram,

Thanks for the feedback (as always).

So, the plot thickens. You are correct that we could do all of this work outside of the application and just load the data. However, when we compared the effort to develop these calculations outside of TM1 or even in TI processes as compared to developing rules, we "drew the line" at loading the core inputs and have gone with rules for the rest of the work. As we continue to make our way through some fairly complex requirements, we are also discovering "factors" (such as the ones used in this calculation) which the users would like to "tinker with" between weekly loads to do some limited "what-if" calculations to change some basic assumptions. This is not "typical" planning in that it will not be done often, but I guess I should correct myself as this could be classified as "planning." We are thus "stuck" with using rules.

More to follow in a subsequent post.

[normanbobo]

Breaking the problem down into its component parts, the following "hard-coded" feeders work:

Code: Select all

# Projected Revenues

['Revenue', 'FWk 2011-04-03']            => ['Projected Revenue', 'FWk 2011-04-10' ];
['Projected Revenue', 'FWk 2011-04-10']    => ['Projected Revenue', 'FWk 2011-04-17'];
['Projected Revenue', 'FWk 2011-04-17']    => ['Projected Revenue', 'FWk 2011-04-24'];
#....

Now I need to translate this into formulas...

[jim wood]

Either on this site or else where look up variable feeders. I'll give you a starter for 10 you'll need a lookup cube and an if statement. Saying that, I would I would take the advice of Lotsaram. Why don't you build a lookup cube and load the data in to the correct projected week in TI?

Jim.

[rmackenzie]

I agree with tomok’s solution. It is much easier to control the feeding if you use consolidated elements that you can manipulate in TI. You are correct about needing to be concerned about the stack if you are writing a recursive feeder and that is another reason to use tomok’s suggestion (or do it in TI as others are suggesting).

Regarding your concern about how TM1 will react to dropping and re-building the consolidation - IMO it doesn’t really matter as long as you consider your ETL strategy. The consolidation needs to be in place when you load new data into the Revenue measure so the feeders are set for the calculated cells for Projected Revenue. If you then remove the consolidation, the feeders won’t disappear. Moreover, if you rebuild the consolidation, the feeders that were set won’t change. The link between the feeders and the consolidation structure exists only when the feeder statement is being (re-)evaluated.

If you really need to use feeders that are the inverse of the calculations, then you could try the feeders below which are a bit of guess on my part! You will need a ‘Next FWk Id’ that works with ‘Prior FWk Id’ and also a ‘Feed Next Weeks Projected Revenue’ attribute that should help control the stack activity. At some point in a chain of weeks (perhaps the last in the financial year) you set this attribute to 0.

Obviously this is air-coding so be kind if it doesn’t actually work! Also, beware that using conditional feeders can raise more issues than they solve... YMMV as always.

Code: Select all

FEEDERS;

# this weeks revenue feeds next weeks projected revenue
[‘Revenue’] => DB(‘GRID Cash Model’, 
			!GRID Projects, 
			!GRID Agreements, 
			!GRID Activity Center,
			ATTRS(‘GRID Fiscal Weeks’, !GRID Fiscal Weeks, ‘Next FWk Id’),
			!GRID Project Types,
			‘Projected Revenue’ );

# any weeks projected revenue feeds the following weeks projected revenue
# feeder is conditional on ‘feed next weeks projected revenue flag’
[‘Projected Revenue’] => DB(IF(ATTRN(‘GRID Fiscal Weeks’, !GRID Fiscal Weeks, ‘Feed Next Weeks Projected Revenue’)=1, ‘GRID Cash Model’, ‘’),
			!GRID Projects, 
			!GRID Agreements, 
			!GRID Activity Center,
			ATTRS(‘GRID Fiscal Weeks’, !GRID Fiscal Weeks, ‘Next FWk Id’),
			!GRID Project Types,
			‘Projected Revenue’ );

[normanbobo]

All,

Thanks again for the contributions. The following feeders did the trick:

Code: Select all

['Revenue']                      => DB( IF( ATTRN( 'GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWK Offset Nbr') = 1,
                                                          'GRID Cash Model',''),
                                                          !GRID Projects,		
                                                          !GRID Agreements,
                                                          !GRID Activity Center,
                                                          ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Next FWk Id'),
                                                          !GRID Project Types,
                                                          'Projected Revenue' 
                                                    );

['Projected Revenue']      => DB( IF( ATTRN( 'GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWK Offset Nbr') > 1,
                                                          'GRID Cash Model',''),
                                                          !GRID Projects,		
                                                          !GRID Agreements,
                                                          !GRID Activity Center,
                                                          ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Next FWk Id'),
                                                          !GRID Project Types,
                                                          'Projected Revenue' 
                                                    );

The mental block I had was that I needed a "Next FWk Id" attribute. I arrived at this at about the same time that rmckenzie posted his response, which served to clarify and validate my thinking. I added this attribute to the Fiscal Week dimension and voila!

OK...so why stick with these feeders? Discussing the other options:

1) Using a consolidation containing all future weeks: We did not have a consolidation like this in our Fiscal Weeks dimension and I was not sure I wanted to add it as it would be visible to users and thus would "clutter" the dimension for them (this is already a very busy dimension). Over time, this option would also have the risk of "building up" feeders from prior weeks. Thus it would have *required* that we refresh the feeders (by starting / stopping? the TM1 service or running a process to refresh the feeders. But in the end, this was a good solution. I just did not want to do it right now when I now have a solution that seems to work.

2) Loading the data in TI: this could also work. Since we had already committed to using rules for our calculations (we already had some 30 or more rules), I wanted to stick with rules all the way through and not switch to a TI process for just this measure. One other factor in this is that the requirements we are getting from our users seem to be a bit "squishy". Rules are more flexible and more easily changed than TI processes. I have that developer's intuition that we will be revisiting these calculations once we start showing the solution to the users (and once they see how easy it is to change the design as opposed to their current spreadsheet world).

If we continue with using these feeders, for what potential "gotchas" should I now be on the watch?

[lotsaram]

The mental block I had was that I needed a "Next FWk Id" attribute. I arrived at this at about the same time that rmckenzie posted his response, which served to clarify and validate my thinking. I added this attribute to the Fiscal Week dimension and voila!

I always make sure that in all my models there are time lookup cubes and that all time offset type attributes have matching forwards and backwards offsets. It is also good to just develop a standard set of lookup cubes to handle time offsets and calculations. Much better than realizing you need something extra when you are most of the way through an implementation. It's also better to do as much of the lifting in time lookup calcs in such a smaller lookup cube, that way the logic is done just the once rather than repetitively in calculation rules which can get expensive.

Code: Select all

['Projected Revenue']      => DB( IF( ATTRN( 'GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWK Offset Nbr') > 1,
                                                          'GRID Cash Model',''),
                                                          !GRID Projects,      
                                                          !GRID Agreements,
                                                          !GRID Activity Center,
                                                          ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Next FWk Id'),
                                                          !GRID Project Types,
                                                          'Projected Revenue' 
                                                    );

A thought on the feeder, if you had a 3rd attribute 'Next FWk Feeder' which could be set by the logic in the }ElementAttributes cube

Code: Select all

['Next FWk Feeder'] = S: 
IF( ATTRN( 'GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWK Offset Nbr') > 1,
  ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Next FWk Id'),
  ''
);

... then you don't need a conditional feeder as you can just handle the aspect of not feeding if the offset number is <=1 by missing the fiscal week part of the cell address rather than the cube itself.

[normanbobo]

I always make sure that in all my models there are time lookup cubes and that all time offset type attributes have matching forwards and backwards offsets. It is also good to just develop a standard set of lookup cubes to handle time offsets and calculations. Much better than realizing you need something extra when you are most of the way through an implementation.

It's also better to do as much of the lifting in time lookup calcs in such a smaller lookup cube, that way the logic is done just the once rather than repetitively in calculation rules which can get expensive.

We have built a Fiscal Weeks Dimension which has over 50 attributes containing time-based calculations such as you describe. I did fail to add the "prior" and "next" style attributes when I first built the dimension - hind sight is 20-20. In any case, please describe the lookup cubes you mention. What are the dimensions? What is the advantage of putting the calculations in a cube versus attributes in a dimension?

Code: Select all

['Projected Revenue']      => DB( IF( ATTRN( 'GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWK Offset Nbr') > 1,
                                                          'GRID Cash Model',''),
                                                          !GRID Projects,      
                                                          !GRID Agreements,
                                                          !GRID Activity Center,
                                                          ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Next FWk Id'),
                                                          !GRID Project Types,
                                                          'Projected Revenue' 
                                                    );

A thought on the feeder, if you had a 3rd attribute 'Next FWk Feeder' which could be set by the logic in the }ElementAttributes cube

Code: Select all

['Next FWk Feeder'] = S: 
IF( ATTRN( 'GRID Fiscal Weeks', !GRID Fiscal Weeks, 'FWK Offset Nbr') > 1,
  ATTRS('GRID Fiscal Weeks', !GRID Fiscal Weeks, 'Next FWk Id'),
  ''
);

... then you don't need a conditional feeder as you can just handle the aspect of not feeding if the offset number is <=1 by missing the fiscal week part of the cell address rather than the cube itself.

Interesting solution.

[tomok]

What is the advantage of putting the calculations in a cube versions attributes in a dimension?

The biggest disadvantage to attributes, as they are defined in TM1, is they cannot vary. TIme is probably not a good example, but let's say you had an attribute on your Product dimension to store which region it belongs to. Now the client decides that they want the region assignment to vary by version, that in Forecast 1 they want it in one region then in Forecast 2 they want in another. Can'rt do this with a TM1 attribute. You have to create a custom "attribute" cube and include the Product and Version dimensions.