Tuesday, December 2, 2014

HFM - Value Dimension and Rules

Maybe the complex part of HFM is the value dimension and rules, after you understand these two parts of HFM elements I am sure you will get a better understand of HFM's consolidation logic. So let me introduce the value dimension first.

Value dimension is a system-defined dimension, it represents the types of value stored in your application. You can find the following image for your quick understanding if this dimension.


Actually, you will find the dimension shows in HFM system as below, depending on how many currencies you have set up for the application. (5 currencies in this example.)


Technically, <EC>, <ECA>, <ECT>, <PC>, <PCA> and <PCT> are the pointers to the currency members. For example, if the entity and its parent's local currencies are HKD, the data will be stored in "HKD" member when input data in <EA> or translate data to <PA>.

For the sub group rollup logic in Entity dimension, you can find the picture below. Sub Group's <EC> = Sum of the children Subsidiaries/Associates/Joint Ventures' [Contribution Total]


After the understanding the value dimension, we can move to the HFM rule's calculation logic. Actually, the HFM admin guide describes very clearly about the rules so I just copy some of the contents here.

You use Financial Management rules to automate the calculations of data within an application. You can use rules for these purposes:

  • Calculate data entry level amounts for a specific entity, scenario, and period.
  • Prevent data entry for a specific cell in a specific entity, scenario, and period.
  • Allow input at the Parent entity level.
  • Calculate data that cannot be calculated through a hierarchical aggregation, such as ratios or variance analysis.
  • Perform allocations from a parent entity to a list of base entities.
  • Perform complex currency conversions, calculate exchange rate differences, or perform other calculations necessary for your consolidation.
  • Define formulas to dynamically calculate accounts.
  • Specify the accounts in the application that support intercompany transactions.
HFM provides the following rule types
  • Calculation
  • Translation
  • Consolidation
  • Allocation
  • Input
  • NoInput
  • Dynamic Calculation
  • Transactions
  • Equity Pickup
  • OnDemand (From version 11.1.2.3)


During the consolidation process, rules are executed in a pre-defined sequence. For each base child of a specific parent, the calculation sequence for the various elements in the Value dimension takes place in this order:
  1. Accounts defined as IsCalculated in the metadata are cleared in EntityCurrency.
  2. Accounts defined as IsCalculated in the metadata are cleared in EntityCurrAdjs.
  3. The Sub Calculate() routine is executed on EntityCurrency.
  4. The Sub Calculate() routine is executed on EntityCurrAdjs.
  5. The ParentCurrency data is cleared.
  6. Default translation is applied to all accounts defined as Revenue, Expense, Asset, Liability for the total amount of EntityCurrency and EntityCurrAdjs. For accounts with the Flow or Balance attribute, translation is not applied by default, the total amount of EntityCurrency and EntityCurrAdjs is rolled up into Parent Currency.
  7. The Sub Translate() routine is executed.
  8. The Sub Calculate() routine is executed on ParentCurrency.
  9. Accounts defined as “IsCalculated” in the metadata are cleared in ParentCurrAdjs.
  10. The Sub Calculate() routine is executed on ParentCurrAdjs.
  11. Accounts defined as “IsCalculated” in the metadata are cleared in ParentAdjs
  12. The Sub Calculate() routine is executed on ParentAdjs.
  13. Proportion and Elimination data are cleared.
  14. Default consolidation and eliminations are performed for the total amount of Parent and ParentAdjs.
  15. The Sub Calculate() routine is executed on Proportion and Elimination.
  16. Accounts defined as “IsCalculated” in the metadata are cleared in ContributionAdjs.
  17. The Sub Calculate() routine is executed on ContributionAdjs.
After the previous steps have been repeated for each base child, this sequence takes place for the parent entity:
  1. The EntityCurrency data is cleared.
  2. The sum of the total of Proportion, Elimination, and ContributionAdjs for every child is written into EntityCurrency of the parent entity.
  3. The Sub Calculate() routine is executed on EntityCurrency.
  4. Accounts defined as “IsCalculated” in the metadata are cleared in EntityCurrAdjs.
  5. The Sub Calculate() routine is executed on EntityCurrAdjs.
Note: If a parent is further consolidated into another parent, this sequence continues with step 5 from the child consolidation sequence.

Monday, December 1, 2014

Hyperion Planning - Why Form Cells are Read-only?

Sometimes we need to diagnose for our clients why some cells in forms are read-only and cannot input data. I would like to list all the possible reasons as much as I can. If you know there are other possible reasons please reply me in the blog, thanks.
  1. The login user is a viewer role - Provison setting in Shared Services
  2. The login user is not the current owner of the cell's planning unit - Check the current owner of the planning unit in Planning Manage Approvals
  3. One member of the cell is Dynamic Calc/Label Only - Member setting in Planning Dimension
  4. One member of the cell is not a leaf member - Member hierarchy in Planning Dimension
  5. The periods are out of the Scenario's valid input range (From Start Year/Period to End Year/Period) - Scenario member setting in Planning Dimension
  6. One of the members is Read-only - Member security setting in Planning Dimension
  7. Forgot to refresh the new added member to Essbase - Check the member synchronization of Planning and Essbase/Refresh Database
  8. Segment (One of the rows or columns) of the form is set to Read-only - Segment setting in Planning Form Design
  9. The form is set to Read-only - Form setting in Planning Form Design
  10. The segment is a formula row/column - Form setting in Planning Form Design
  11. The cell contains Supporting Detail - Cell input in Planning Form

Thursday, November 20, 2014

FDM - Mapping Tables

Mapping Tables map source members to target members in FDM applications. There are 5 types of mappings.
  • Explicit Mappings
  • Between Mappings
  • In Mappings
  • Like Mapping
  • Conditional Mapping 
1. Explicit Mappings: one to one mapping. For example, source account 1100 will map to target account 10010101

Tips: You can define the Target Account as "IGNORE", then FDM will ignore all the data records with the source account defined in this rule in the source file. (e.g. records with 1300-101 source account will be ignore when load data to the target system.) You can do that in other types of mappings as well.

2. Between Mappings: many to one mapping. For example, source account in the range 1503~1591 will map to target account 15010101

 3. In Mappings: many to one mapping. For example, source account 1503, 1510 and 1515 will map to target account 15010101


 4. Like Mapping: many to one/many mapping. For example, all the source account start with 1190 will map to account 10010201


You can also define the same wildcard characters (* and ?) in both source and target members so that it will be considered to be automap. For example,


 5. Conditional Mapping: many to many mapping. You can define this type of mapping in Between, In or Like mappings.

When add a new line of the mapping, input "#Script" in the Target field. And then click "Zoom" in the Script field.


You can use a VB script to define a conditional mapping.


varValues(14) is one of the preset variables that are available for use in conditional script expressions, you can find the meaning of the variables in the FDM admin guide.


This ICP mapping means, if the target account equals "InterCoAP" then return ICP as "[Michigan]". In other cases, return ICP as "[ICP None]". The conditional mapping will be as below,


If a source value is used with multiple map types, FDM processes the maps in the following order:
  1. Explicit
  2. Between
  3. In
  4. Like
Explicit maps override all other map types. Between maps override In and Like maps. In maps override Like maps.

By default, FDM maps are sorted alphabetically, from within the Rule Name column. To enable FDM to sort and process maps by numeric value, you enable map sequencing. If a source account is valid within multiple maps of one mapping type, the source account is assigned to


For the dimensions order, FDM processes in the following order by default,
  1. Account
  2. Entity
  3. ICP
  4. Custom1...Custom20
/*------------------------------------------------------------------------------------------------------------------*/

Mapping Tables can be export and import. I don't like to use the MapLoader, instead, I use Import XLS to import mapping tables.

First, we can export the mapping table to Excel by clicking a button.


After the exporting, you can open the Excel file.


You can find there two rows are hidden in this Excel, unhide these rows.


You can find the hidden information as below. Actually, it's the table name and the field names of each column. Don't try to edit these two rows.


In the Excel upper left corner, it's the name box. You can select a name called upsTDATAMAP in this file.


It's the range definition which will be used for import in the later.


You can try to add a row in the end of the existing records to define a new mapping.


Then you need to navigate to Name Manager under the menu Formulas.


You can find the original range of upsTDATAMAP here.


Update the range to cover the new added row.


Then you can find the upsTDATAMAP's range has been updated as below.


Return to FDM application, click the menu Tools and then Import XLS.


Select the Excel file we updated before.


Click Import


Shows the following information.


You can find the record can be added successfully.


Actually, you can use this method to import any other objects that can be exported from the FDM application. (e.g. Control Tables, Import Formats...)

FDM - Lock Options

For the classic FDM applications, you can find many "Lock" options in the menus. Let me introduce these kinds of options one by one.

1. Lock the system so that all the basic users cannot login.

In the POV bar, click "Open"


Then a window will pop up, check the box System Lock on and then click OK


Then you can find "Open" became "Locked" in the POV bar


When basic users try to login the system, the following error will occur.



2. Lock the POV so that all the basic users cannot change

Click the menu "Administration" and then "Point-of-View Mode Lock"


Check the On/Off box and then OK


Shows the following message.


When basic users try to change the period,


Shows the following message, and the period cannot be changed.


But the locations can still be changed.


3. Lock all the locations' current POV, so that all the users cannot import data.

Click the menu "Administration" and then "Lock All Locations (Current Category/Period)"


Confirm the POV and click Yes


You can find a lock icon for all the locations in the current POV as below.


When users try to import data (Including the admin users), the following error will show


4. Lock the current location's current POV, so that all the users cannot import data.

Click the menu "Tools" and then "Lock Current Point-of-View"


You can find a lock icon for the current location and the current POV as below.


And then you can't import data as below.


Wednesday, November 19, 2014

Hyperion Planning - Date Function

Hyperion Planning has the ability to input data in "Date" format, but actually the data store in Essbase is still a number with the format "YYYYMMDD", not a real date type of data. In a real case, a client wants to calculate the interval days between two given days input in Hyperion Planning. I searched some blogs to introduce using CDF to calculate the difference, but I am not good at CDF and I tried to calculate it with the following basic functions.
  • @DATEDIFF
  • @DATEPART
  • @DATEROLL
  • @FORMATDATE
  • @TODATE
  • @TODATEEX
  • @TODAY
Please find the code as below,

The calculation logic is as below,

1. "Start Date", "End Date" are the dates input in Hyperion Planning, and they are stored in Essbase with "YYYYMMDD" format as numbers. To explain more easily, we assume
  • sDate = "Start Date" = 20130101
  • eDate = "End Date" = 20141120

2. Then we need to breakdown the date into its elements (Year, Month and Day), with the calculation above, we can calculate
  • sYear = 2013
  • sMonth = 1
  • sDay = 1
  • eYear = 2014
  • eMonth = 11
  • eDay = 20

3. Next step, we will translate the "Start Date" and "End Date" from number format to a date format with @DATAROLL function. The logic is, we set a start date 2000-01-01, and then roll the number of years (sYear - 2000), number of months (sMonth - 1) and number of days (sDay - 1) as the intervals. So that we can get the following result,
  • StartDate = 2013-01-01 (Date format)
  • EndDate = 2014-11-20 (Date format)

4. Last step, we just need to use @DATADIFF function to calculate the difference of two dates (StartDate and EndDate)
  • nDay = 689
/*-----------------------------------------------------------------------------------------------------------------*/


Another use case is, if we want to get the number of year, month and days of the month for the current point of view. For example, if the POV is FY14, Nov, it need to return three numbers 2014, 11 and 30.

Let's create three dynamic members: nYear, nMonth and nDay. The member formulas are as below, (Assume the Planning application start year is FY05)

  • nYear
2004+@COUNT(SKIPNONE,@ILSIBLINGS(@CURRMBR("Year")));

  • nMonth
IF (@ISMBR("Jan"))
 1;
ELSEIF (@ISMBR("Feb"))
 2;
ELSEIF (@ISMBR("Mar"))
 3;
ELSEIF (@ISMBR("Apr"))
 4;
ELSEIF (@ISMBR("May"))
 5;
ELSEIF (@ISMBR("Jun"))
 6;
ELSEIF (@ISMBR("Jul"))
 7;
ELSEIF (@ISMBR("Aug"))
 8;
ELSEIF (@ISMBR("Sep"))
 9;
ELSEIF (@ISMBR("Oct"))
 10;
ELSEIF (@ISMBR("Nov"))
 11;
ELSEIF (@ISMBR("Dec"))
 12;
ENDIF

  • nDay
IF (@ISMBR("Jan","Mar","May","Jul","Aug","Oct","Dec"))
 31;
ELSEIF (@ISMBR("Feb"))
 IF ("nYear"/4 == @INT("nYear"/4))
  29;
 ELSE
  28;
 ENDIF
ELSEIF (@ISMBR("Apr","Jun","Sep","Nov"))
 30;
ENDIF

The result is as below,