Saturday, 30 November 2013

..designing a scenario


Hi all,

In this post I am trying to clarify my thoughts regarding the Scenario dimension.

The scenario dimension in HFM holds the different data sets reported by the group. Despite the fact that scenario dimension has usually up to ten members, it is one of the most important dimensions of the application. The reason is that the scenario dimension defines the input frequency (month-to-date, quarter-to-date or year-to-date), the calculation sequence between Periodic and YTD for the flow accounts, the process management, the data audit and the execution of the consolidation rules.

From a functional perspective, the most common scenarios that I have seen up to today are:
  • Flash
  • Actual
  • Budget
  • Forecast
  • Translation scenarios of the previous data sets
Following, I have a quick description of the scenario dimension setting. The description is based on my experience, on the HFM admin manual and on discussions that I had with other consultants.

1. Member or Label or Name:

The name or the label of the scenario. After version 11.1.1.3, the name must be unique and can contain up to 80 characters,
including spaces, but cannot start with a space.

In the Oracle manual, it is specified that the scenario name should not include the following characters:
  • Asterisk ( * )
  • At sign ( @ )
  • Comma ( , )
  • Curly brackets ( { } )
  • Double quotation marks ( “ )
  • Minus sign ( - )
  • Number sign ( # )
  • Period ( . )
  • Plus sign ( + )
  • Semicolon ( ; )
  • Slash mark ( / )

Due to the fact that scenario can be used at the rule file, I usually prefer to have short names which does not include spaces.

2. Description/Alias

The description or alias of the scenario is an setting that it is used to describe the purpose of the specific scenario. The length of the description is up to 80 characters and there are no limitations in characters.

3. Default Frequency:

Default Frequency is used to define the input period of a scenario. If the view dimension of the application has months (MTD), quarters (QTD) and years (YTD), the user must select one of these members as an input period of the scenario.

Consequently, this setting defines which members of the period dimension are loaded with data into a given year for the specified scenario. The most common members are:
  • MTD: monthly, data can be input on the 12 periods of the year
  • QTD: quarterly, data can be input on Q1 to Q4 members only
  • YTD: yearly, data can be input on the FY (Financial Year) member only
For example in an application, the users can input Flash and Budget: monthly, Budget and Forecast: quarterly and Budget of future year: yearly.
  • Flash/Actual: MTD
  • Budget/Forecast: QTD
  • BudgetPlus: YTD

4. Default View:

The default view setting is used to define the data view (Year-to-Date or Periodic) to use when Scenario View is selected in the point-of-view bar. There are only two options: YTD or Periodic.

Additionally, Default view is used to define the following:
  • Sets the in the view dimension to that default view.
  • Default view also determines the view in calculation rules written in HFM.
  • Default view also determines the view for journal entry

Based on my experience, the most common selection is the YTD and I believe that the reason is that the original business requirement is to work with the YTD values and not the periodic. Consequently, in the above example:
  • Flash/Actual: YTD
  • Budget/Forecast: YTD
  • BudgetPlus: YTD

5. ZeroViewForAdj / ZeroViewForNonadj:

These are the two most confusing settings in HFM I believe (I guess due to lack of proper documentation until version 11.1.x.x).

The ZeroViewForNonadjs is used to define how the scenario will handle the missing data of a flow account and the ZeroViewForAdj is used to define the scenario will handle the missing values from flow accounts that are included in journals. In the following paragraph I call both settings as "ZeroView" settings. Flow account is any account with account type: Revenue, Expense or Flow. These types are usually used for the income statement accounts and the main characteristic is that the periodic view is different from the YTD view.

The ZeroView settings define the way that HFM will interpret the missing values in a scenario. In order to understand the concept, please remember that the view dimension has the periodic and the YTD value. So a value of the P&L can be either periodic or YTD. The ZeroView settings are used to define which of the two view dimension members will be zero if the data are missing from the system when an account will be Revenue, Expense or Flow.

As an example, let’s assume that entity A has sales of £100 in P01 and sales of £200 in P02. This means that in P02, the periodic value of the sales is £100 and the YTD value of sales is £200. Now, let’s think what will happen on P03 before loading any data. In case that the ZeroViewForNonadj is YTD, HFM will consider that in P03 the periodic value of the sales is £-200 and the YTD value of sales is £0. On the other hand, if the ZeroViewForNonadj is periodic, HFM will consider that in P03 the periodic value of the sales is £0 and the YTD value of sales is £200.

To summarise:
  • the settings define how HFM will interpret missing values
  • the settings apply only to flow accounts (revenue, expense, and flow)
  • the majority of HFM applications, Actual data is loaded YTD and for this reason the ZeroView settings are also YTD. Based on that the users can ignore any zeros when they load the data file. Additionally, HFM users do not have to post reversing journals in the next period since HFM will calculate the reverse entry automatically in the next period.

Following the previous example:
  • Flash/Actual: YTD
  • Budget/Forecast: YTD
  • BudgetPlus: YTD

6. Consolidate YTD:

Similar to the ZeroView settings, the ConsolidateYTD setting is used to define the sequence of the consolidation execution between the periodic and the YTD values. This setting is also applied only in the accounts that are flagged as Revenue, Expense or Flow.

When the ConsolidateYTD setting of the scenario is Yes, HFM will first consolidate the YTD values of accounts at the parent entity and then it will calculate the periodic values. On the other hand, when ConsolidateYTD flat is No, HFM will first consolidate periodic values of accounts at the parent entity and then it will calculate the YTD values. 

My understanding ( I am not sure if this is correct) is that the main reason of having such a setting is for the case when the application has automatic consolidation rules and a new entity starts after the beginning of the new year. If the application uses manual journals in order to create the group values then this setting has no effect. Additionally, in order to enable this setting, when ConsolidateYTD setting of the scenario dimension is N, the ZeroView settings should be set to Periodic. As a result, the ConsolidateYTD values is usually set to Y, in order to support the ZeroView setting and the periodic consolidation values are created via rules or journals..

Following the previous example, the settings will be:
  • Flash/Actual: Y
  • Budget/Forecast: Y
  • BudgetPlus: Y

7. SupportsProcessManagement:

This setting is used to define the whether process management will be enabled for the specific scenario.

The options are:
  • Y to enable the Process Management without email alerts
  • N to disable the Process Management option
  • A to enable Process Management and email alerts

So, following the previous example, the setting will be:
  • Flash/Actual: Y
  • Budget/Forecast: Y
  • BudgetPlus: Y

8. SecurityClass:

The setting is used to define the security class of the scenario. The security class will be used in shared services to allow the users to have Read, Promote, Metadata or All access.

So, following the previous example, the setting will be:
  • Flash/Actual: S_Actual
  • Budget/Forecast: S_Budget
  • BudgetPlus: S_Budget

9. MaximumReviewLevel:

This setting had been design to define the maximum review level of an application but it was never actually added in the process management mechanism (at least I am not aware of). Consequently, I usually ignore it.

10. Use Line Items

This setting is used to define whether the scenario will use line-item detail. The options are Y if the scenario can accept line items or N if the scenario cannot accept line items.

Due to the requirement to keep details on the different miscellaneous accounts, the majority of the scenarios are enabled to accept line-items.

So, in the previous example, the setting will be:
  • Flash/Actual: Y
  • Budget/Forecast: Y
  • BudgetPlus: Y

11.  EnableDataAudit

This setting is used to define whether the scenario will keep track of the data changes for audit purposes.

The options are:
  • Y to automatically audit all accounts 
  • O to audit only those accounts that have EnableDataAudit set to True.
  • N to disable auditing for all accounts.
The data audit functionality is really useful for the scenarios that will be audited in the end of the quarter or year; however enabling the data audit may reduce the performance of the application. For this reason only the most important scenarios are enabled.

So, in the previous example, the setting will be:
  • Flash/Actual: Y
  • Budget/Forecast: N
  • BudgetPlus: N

12. DefFreqForICTrans:

This setting is used to define the default frequency for intercompany transaction data. This is a frequency from the view dimension.

So, in the previous example, the setting will be:
  • Flash/Actual: YTD
  • Budget/Forecast: YTD
  • BudgetPlus: YTD

13. PhasedSubmissionStartYear:

This setting is used to define the starting year of enabling the phased submission. However, I have not actually use it in the past.

That was a full description of what I have in my mind regarding the technical part of scenario dimension. Maybe in the future, I will add more details regarding the functional aspect of the scenario dimension.

Feel free to write any comments… (I know that some of you read my posts, so please say Hi…)

Enjoy life,

Regards,

Thanos

...Records & Subcubes in HFM

A few years ago, I decided to spend time to understand how HFM calculation engine works with aim to improve the performance of the r...