Method for handling Rules for Actuals/Forecast periods

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 363
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Method for handling Rules for Actuals/Forecast periods

Post by PavoGa » Mon Jun 03, 2019 1:04 pm

Would like some opinions/ideas on handling calculations for closed vs open periods in a time dimension.

First, I've read several threads on time dimensions here, but as yet not one dealing with this particular topic. If I've missed one, more than happy to review it.

We track open/closed periods with a simple 1/0 flag where 1 = open. For the sake of ease in reconciling and to facilitate certain functionality within our model, we load actuals for various aspects of the forecasting applications into staging cubes. Here are some questions:
  1. In the rule, use an IF to STET the cell if the period is closed and load via TI?
  2. If retrieving actuals from the staging cubes instead of using a TI, use IF statements on all rules ( IF period is Forecast, forecast calc, actual calc )?
  3. As we only deal with 13 periods at a time, use aliases (Actuals1, Actuals2, ..., Forecast11, Forecast12) then the rules would have on the LHS (Actuals1...Actuals12) for Actual rules and (Forecast1...Forecast12) for forecast rules. The aliases change as periods are closed. Without going into detail, the problem here is it possible for two different live versions to have different open/closed periods. :?
  4. use the alias method to STET the Actuals periods and load via TI (has same problem as previous)?
  5. Use separate Actuals and Forecast measures. Actual calculations are multiplied by a time dim ActualsFlag value of 1 and Forecast are multiplied by a similar flag. Each are consolidated into a single element so only Actuals or Forecast are represented for each period appropriately?
  6. Suggestions?
Just curious as to how people may be or suggest handling these situations. I really prefer to avoid IF statements where possible and because of our versioning issue, am leaning towards #5. However, suggestions are very welcome.
Ty
Cleveland, TN

User avatar
ykud
Posts: 55
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Method for handling Rules for Actuals/Forecast periods

Post by ykud » Tue Jun 04, 2019 3:54 am

I usually do something simple like:

Code: Select all

[{'Forecast Version1','Forecast Version 2', etc for live versions}] = IF (DB(LookupCube(!Version, !Month, 'is Actual') = 1, ['Actual], CONTINUE);

FEEDERS;
['Actual'] => ['Live Versions'];

Wim Gielis
MVP
Posts: 2116
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Method for handling Rules for Actuals/Forecast periods

Post by Wim Gielis » Tue Jun 04, 2019 7:49 am

Like ykud, we have the same in a not so small TM1 FPA model for a retail customer. It works fine in general.

[As a sidenote, and I don't want to hijack the thread.
On a regular basis, in this model I replace rules- calculated actuals in planning periods with actuals copied in by TI. It's an exercise every year.
I do turn off the feeders for these past years (hardcoded in the feeders, or a consolidation of PAST YEARS driving the feeders). Just wondering if TM1 uses memory for these feeders IF the target cells are cells containing values copied in with TI, rather than rules-calculated cells. Are these feeders ignored / have no impact / or do they consume memory ?]
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
Steve Rowe
Site Admin
Posts: 1933
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Method for handling Rules for Actuals/Forecast periods

Post by Steve Rowe » Tue Jun 04, 2019 7:57 am

I'm not sure I totally understand what you are asking but.....

Are you using the "wrong" functionality.

You get the data to the cube by the most efficient method for your design, that will vary depending on a large number of factors.

Open / closed is a security issue and should be dealt with using security not a fundamental change in the nature of the way the data is stored?

lotsaram
MVP
Posts: 3276
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Method for handling Rules for Actuals/Forecast periods

Post by lotsaram » Tue Jun 04, 2019 8:02 am

If the forecast periods will actually be data input (either direct or loaded from external system) versus rule calculated then also consider not having a rule at all and just using cell security to ensure actuals are read only.

Although I'm a big fan of rules, any time you can avoid rules and use input data instead it is going to be faster to consolidate and calculate views.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
Community Contributor
Posts: 363
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Method for handling Rules for Actuals/Forecast periods

Post by PavoGa » Tue Jun 04, 2019 12:28 pm

Thank you all for the responses. After weighing "a large number of factors", decided to load actuals through a TI . The comments here confirm that decision. Always have done it that way, but wondered if I might be missing something. Was leaning towards rules because of some factors, but the overall weight was on the side of continuing to use TIs.

A few follow up questions:
  1. What is your opinion of using data reservations? (we have the distinct possibility of different open/closed periods for multiple active versions within a cube)
  2. Our model is phased. I.e., some calculations vary whether we are pre-FY budgeting or re-forecasting during the year as periods close. Have been using a consolidation with measures, where applicable, for each phase (actuals/plan/forecast). Displaying the consolidation for closed periods, prevents having to apply security in the cube for closed periods. Better a consolidation or cell security, if either is an option, in one's opinion? (we do not, in this model, allow user entry to consolidations)
  3. I've always been told to avoid cell security if at all possible. Is there a performance hit because of that or is it just a matter of the added complexity, don't fool with it? (Data reservations?)
Ty
Cleveland, TN

User avatar
gtonkin
MVP
Posts: 687
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Method for handling Rules for Actuals/Forecast periods

Post by gtonkin » Tue Jun 04, 2019 12:55 pm

In terms of data reservations vs cell security, I would go with cell security as it would be more flexible to code via rules/TI.
We have models where we have Stages and set groups to various stages in the process. Based on the stage, cell security blocks/allows input.

For a model I am currently busy with, the client specifically wants data reservations. Unless I am doing it wrong, this requires processes to check that there are no reservations in place before trying to place one - 3 possible outcomes - none, someone else, you already have.
Once you have the reservation, you need to release it but can only release if you have a reservation - more checks.
You also need to consider that a reservation is against an element - N or C, you cannot specify multiple elements.
Data reservations just feel like a lot more work with more risk e.g. user does not release and another user cannot reserve.

No doubt it depends on your model, size of cube, number of dimensions etc. etc. but hope the above helps.

User avatar
Steve Rowe
Site Admin
Posts: 1933
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Method for handling Rules for Actuals/Forecast periods

Post by Steve Rowe » Tue Jun 04, 2019 2:15 pm

I've always been told to avoid cell security if at all possible. Is there a performance hit because of that or is it just a matter of the added complexity, don't fool with it?
I think this has long been a piece of received wisdom but its possible that times have changed but I'll say up front, as like you I've usually avoided implementing if at all possible, I don't have a lot of years of direct experience to draw on. A couple of comments though.

I recently noticed looking at a system a colleague had built that the cell level security cube does not have to contain all the dimensions of the target cube. If you build it via perspectives you get the full size cube, if you build it in PM you can cherry pick the relevant dimensions. Not sure when this arrived but it must have been with a muted fanfare as its a step change in how the complexity of cell level security works. David Usherwood tells me that you can build the cut down security cube via TI as well.

I would still avoid rules that derive the security in the cube since the string values are not cached, though I have a vague reconciliation that security cubes to cache their string values and that's why SecurityRefresh; is required. So I'm happy to be wrong on this one....

Wim Gielis
MVP
Posts: 2116
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Method for handling Rules for Actuals/Forecast periods

Post by Wim Gielis » Tue Jun 04, 2019 2:22 pm

CellSecurityCubeCreate (Destroy also exists)

https://www.ibm.com/support/knowledgece ... reate.html

It must have been 10.0 somewhere (from memory).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
mattgoff
MVP
Posts: 515
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016
Location: Florida, USA
Contact:

Re: Method for handling Rules for Actuals/Forecast periods

Post by mattgoff » Tue Jun 04, 2019 4:06 pm

I'll throw my hat in the ring with our solution. Our model is middle-sized, maybe up to 30 GB in RAM depending on how long it's been since the last restart and how recently we've scrubbed out old saved snapshot versions.

We handle actuals in the forecast version with rules. We have a control cube with two entries for this purpose, "forecast_date_start" and "forecast_date_end" (in format mmm-yyyy). In the GL cube, there's an "actuals" version pulled from Oracle (zeros for open periods). In the "forecast" scenario, a rule copies in any periods prior to the forecast period from the "actuals" version and they are obviously then unwriteable. Other rules (esp important for rollforward) are only computed through the end of the forecast period.

Pros:
  • Doesn't require cell security (since we have separate period and year dimensions, would only require element security if we had a merged time dim).
  • Makes forecast start and end dates easily available to rules, TI, and Excel.
  • Easy to grant access to power users to allow them to change forecast start/end. If this was done by TI and security, there are more steps and they need more access (or a more complex script w/ security access I guess).
Cons:
  • Requires careful typing (or validation) to ensure the syntax is correct.
I don't know if performance is a pro or a con. It was never an issue so I've never A/B tested it.

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.

Post Reply