STET'ing out all elements of a consolidation

[terry720]

Hi all,

I've searched and not found a topic on this. I also can't find anything on it in my "Official Guide"

Basically, we have two sets of time periods....Actualized time periods and Forcasted time periods. Right now, we change the rule file each month to add the newly "Actualized" month to the list of time periods that are STET'd so the Forecast rules don't apply.

One alternative is to replace the current STET rule (with all the actualized time periods on the left side of the rule) with an IF(ELISANC(),STET,Contiunue) rule...but we are concerned that means it will evaluate EVERY SINGLE CELL in the cube to determine if it needs to be STET'd or not. That would significantly impact performance.

What I'd like to do is replace all the individual N-level time periods on the left side of the rule with a C-level element and have it STET all the N-Levels (but not the C-levels) under it.

We have a similar situation with our "Scenario" dimension. Each month we snapshot off a copy of our financials into a frozen scenario. In order to do that, we have to add the new scenario to the scenario dimension....then add the scenario to the scenario STET rule so that no rules apply and we can snapshot the data from the live (ruled) scenario to the frozen (un-ruled) scenario. Again, all the frozen scenarios are listed individually on the left side of the STET rule.

All of this means every month we have to make rule changes and migrate them through. The rule files (5 of them) take upwards of 2 hours apiece to save...so we do it as part of a restart (which takes 5 hours) instead. It's a hassle.

Any ideas?

Terry

[tomok]

I know what your are trying to accomplish but unfortunately, the only way to apply a rule to a dynamic area (the left-hand side of the rule) is to do it with an IF condition in the definition (the right-hand side of the rule.

[ioscat]

when rule is applied to a cell it calculates (as i know) in several cases - 1) the cell is shown in the viewer or 3) asked by enother rule or 2) the cell is feeded and asked by consolidation engine. So if you put somewhere rule "STET", you won't get extremely terrible performance, but of course it will degrade. If you open not large views - you maybe even won't see any difference.

one another way is to write a script that will update rux file automatically

[terry720]

So right now a rule that tells the system to use actuals instead of calculating a forecast number looks like this:

[{'2006 BB TTD','2006 00','2006 01','2006 02','2006 03','2006 04','2006 05','2006 06','2006 07','2006 08','2006 09','2006 10','2006 11','2006 12',
'2007 BB TTD','2007 00','2007 01','2007 02','2007 03','2007 04','2007 05','2007 06','2007 07','2007 08','2007 09','2007 10','2007 11','2007 12',
'2008 BB TTD','2008 00','2008 01','2008 02','2008 03','2008 04','2008 05','2008 06','2008 07','2008 08','2008 09','2008 10','2008 11','2008 12',
'2009 BB TTD','2009 00','2009 01','2009 02','2009 03','2009 04','2009 05','2009 06','2009 07','2009 08','2009 09','2009 10','2009 11','2009 12',
'2010 BB TTD','2010 00','2010 01','2010 02','2010 03','2010 04','2010 05','2010 06','2010 07','2010 08','2010 09','2010 10','2010 11','2010 12',
'2011 BB TTD','2011 00','2011 01','2011 02','2011 03','2011 04','2011 05','2011 06','2011 07','2011 08','2011 09','2011 10','2011 11','2011 12',
'2012 00','2012 01','2012 02','2012 03','2012 04','2012 05','2012 06','2012 07','2012 08','2012 09','2012 10','2012 11','2012 12', '2013 00','2013 01'},
'Local', 'Value(Thousands)', 'Direct Input', 'IDS Scenario':{'Rolling Forecast', 'Restricted Forecast'}] = N: ['TOTAL ACTUAL SOURCES', 'Total Value (Thousands)'];

What I was hoping I could do was replace all those time periods with the 'Actualized Time Periods' consolidation element...but it sounds like that won't work.

The other option is as follows but I am worried about a performance impact:

['Local', 'Value(Thousands)', 'Direct Input', 'IDS Scenario':{'Rolling Forecast', 'Restricted Forecast'}] = N:
IF(ELINANC('CMN Time Period','Actualized Time Periods',!CMN Time Period),
['TOTAL ACTUAL SOURCES', 'Total Value (Thousands)'],
CONTINUE);


My understanding is that it will then apply the rule to every single time period...first testing it...then deciding if it should apply a result or continue.

Terry

[tomok]

Those are basically your two options but for #2 I wouldn't use the ELISANC function to determine the periods. I would add an attribute to the dimension and then do IF(ATTRS('CMN Time Period',!CMN Time Period,'Actualized Time Period')@='Y',....................). The ELISANC function is more resource intensive than ATTRS. The other thing to note is that rules are only applied to cells asked for in a view or report. If you don't ask for all the periods in that list they don't have to be evaluated. FWIW, I've used this logic on multiple occasions and never experienced any undue performance hit.

[Harvey]

That's a strange way to structure your period dimension, but assuming this can't be changed, the best option is to add an attribute or lookup cube that will contain flags to indicate if the forecast rule should apply. That way, each month, you just update the flags and you end up with a simplified rule.

I usually use a cube for lookups and apply a naming convention, as I think it makes it harder for users to understand their model when things are hidden away in attributes. The attribute editor is a pain to use, too, I much rather entering data in the cube viewer.

[tomok]

The attribute editor is a pain to use, too, I much rather entering data in the cube viewer.

You don't have to use the attribute editor. You can use a cube view on the }ElementAttributes cube. As long as you publish the view in the Applications folder users can see it even if they have Display Control Objects turned off.

[terry720]

All good suggestions...one additional complication is that the "Actuals Thru Date" attribute is scenario specific. We only actually have rules applying to 3 main scenarios (Rolling Forecast, Restricted Forecast, and Long Range Business Plan). The two forecast scenarios always have the same ATD but the LRBP is different. The Forecast scenarios change each month but the LRBP is frozen for several months at a time.

So we'd actually need 2 attributes...or the a single attribute could be set to indicate Both (Y), Forecast Scenarios Only (F), or Blank (). Right now we have LRBP and Forecast data in separate cubes so the rules in each cube could specify which result to look for. When we combine both into one cube (planned for next year) we'll need a rule that's a little more complex.