Hyperion Financial Management: Business

Rule Writing Tips and Techniques

Chris Barbieri

Consolidation Practice Director

Oracle ACE

Ranzal & Associates

*With significant content from “JimTheRulesGuy” Heflin

& Geordan Drummond of Ranzal ☺

Topics

• Basics of HFM rules in classic Vbscript

• Advanced topics in classic mode

• Debugging

• Intro to Calc Manager• Intro to Calc Manager

• Questions ?

HFM Rules

The basicsThe basics

“A good rules file is an empty one.”

- Anonymous- Anonymous

What are we doing with rules in HFM?

•Rules primarily move data within the system

•Also provide NoInput, Input, ICT intersections

•It’s always a pullpull*

•Pull to all valid intersections on the left hand side of the

equation from the intersections on the rightequation from the intersections on the right

•Always think in 12 dimensions that comprise the point of view.

•* Except in consolidation rules, Sub Allocate, and ImpactStatus…

•Don’t focus on the vbscript

•Do focus on

“where can we read from and where can we

write to?”

HFM Rules 101:

There are two main functions

• The HS.ExpHS.Exp function is used to write data to the database

HFM destination = source data from HFMHFM destination = source data from HFM

• The HS.GetCellHS.GetCell function is used to retrieve data from the database

A variable = data from HFM

Tip 1: Know what dimensions are valid

on each side of the equation.

On the left hand side:

• The current point of view is set, these

dimensions cannot be specified:

– Scenario

– Year

– Period

On the right hand side:

• Any of the 12 dimensions

can be specified.

– Period

– Entity

– Value

• These can be specified

– Account

– ICP

– Custom 1-4 can be specified.

• View is a special case

– Based on the ZeroView settings and the

scenario default view

Example: Invalid HS.Exp

• Rollforward beginning retained earnings

HS.Exp “A#BegRetainedEarnings.P#January” & __

“= A#EndRetEarn.Y#Prior.P#December” & _

“+ A#NetIncome.Y#Prior.P#December.W#YTD”

Tip 2: Data is written to the base

level elements of a dimension

In the following dimensions you can only only write to a base level write to a base level elementselements:

– Account

– ICP– ICP

– Custom1

– Custom2

– Custom3

– Custom4

So only base members on the left hand side

Tip 3: Implicit vs. Explicit

Intersections

• Dimensions not explicitly on the right hand

side of an equation are implicitly lifted from

the left hand side and/or the POV.

• Very important to explicitly define what is on

the leftleft and rightright hand side of an equation.

Don’t leave rules to guesswork – be explicit!

The left hand drives the equation

HS.Exp “A#Account3. C1#Chips = A#Account4”HS.Exp “A#Account3. C1#Olap = A#Account4”

The same formula reversed

HS.Exp "A#Account3 = A#Account4. C1#Chips "HS.Exp "A#Account3 = A#Account4. C1#Olap"

Last

one in

wins!

Tip 4: A statement will run for all

valid intersections of data.

• This is often not the desired result

• For Example an account “Account1” has 8 valid base members in Custom1

HS.Exp “A#Account1 = 10”

• What will the total value of Account1 be for Custom1?

Results in “10” in every

valid intersection

Tip 6: If you can not specify a dimension

on left hand side of the equation, then

control when the statements are run.

If HS.Period.IsFirst = True Then

HS.Exp “A#BegRetainedEarnings” & _“= A#EndRetEarn.Y#Prior.P#Last” & _

“+ A#NetIncome.Y#Prior.P#Last.W#YTD ”“+ A#NetIncome.Y#Prior.P#Last.W#YTD ”

End If

Yes - The system knows what comes

prior to period 1

If HS.Period.IsFirst = True Then HS.Exp "A#Account1 = A#Account2. P#Prior" & _

"+ A#Account3.P#Prior.W#YTD"End If

• HFM ScenariosScenarios are not linked like the “Category” in • HFM ScenariosScenarios are not linked like the “Category” in Enterprise

• … but the system does “know” the sequence of the yearyeardimension

• Note: You can get in trouble if you run this rule on the first year in the system!

Tip 7: What will be the result?

HS.Exp "A#Account1 = A#Account2” & _

“ * (A#Account3 / A#Account4 )"

Nothing – but be careful

• The HS.Exp function itself seems to take division by zero into consideration and will just yield nothing.

• It did not write a zero if there was data in the destination cell… it did not write anythingdestination cell… it did not write anything

• If you use a GetCell or regular VB code…

SomeVariableSomeVariable = HS. GetCell "A#Account2” & _“ * (Variable1/ AVariableWithValueZeroAVariableWithValueZero)“

• This will fail with a division by 0 error

Tip 8: The [None] entity

• The [None] entity does not have a currency

• Rules will assume you want to read data from the

same value dimension member you are in

• [None] is the only valid value

If HS.Entity.Member = "Child2" Then

HS.Exp "A#Account2 = E#[None].E#[None]. V#[None].V#[None].A#Account4“

End If

Tip 9: Don’t fill the database with 0’s

• Be careful when writing to the database that you are not

pushing 0’s into the database

• A zero is data – which is not the same as nothing. What do • A zero is data – which is not the same as nothing. What do

you think will happen when you run a “Consolidate all with

Data”?

• This has a negative effect on performance

• This can very quickly bloat the database size

HS.Exp – Pushing Zeros

HS.Exp "A#Account3 .C1#Chips = A#Account4.C1#Chips * 1.1"

HS.Exp “A#Account3 .C1#Computers = A#Account4 “ & _ “.C1#Computers * 1.1”

BeforeBefore

After

HS.Exp - Pushing Zeros part 2

SomeVariable = HS.GetCell("A#Account4. C1#Computer s") * 1.1

HS.Exp "A#Account3.C1#Computers = " & SomeVariable

Before

After

HS.Exp – Pushing Zeros Part 3

SomeVariable = HS.GetCell("A#Account4.C1#Computers") * 1.1

If SomeVariableSomeVariable <> 0 <> 0 Then

HS.Exp "A#Account3.C1#Computers = " & SomeVariable

End IfEnd If

HS.Exp – Pushing Zeros Part 4

You can use the GetCellNoData function. This function is like the GetCell function but as an added bonus it populates a Boolean variable to let you know if function is returning data

SomeVariable = HS. GetCellNoData (“A#Account4.C1#Chips”, IsEmptyIsEmpty) * 1.1

If IsEmptyIsEmpty = False Then

HS.Exp "A#Account3.C1#Chips = " & SomeVariable

End If

HFM Rules

Advanced Topics and DebuggingAdvanced Topics and Debugging

The thrills and excitement of working in

the Value dimension !

AKA: If you’re not afraid….. you will be

The Value Dimension is the key

• The Value dimension is the key to Rules

• The Value dimension is the key to HFM

• It enables:– Currency Translation– Currency Translation

– Intercompany Transactions

– Percentage Consolidation

– Journal Entries at multiple levels

A simple representation of the

elements in the value dimension

[Contribution Adjs]

[Elimination][Proportion]

[Contribution Total]

[Contribution]

[Parent Total]

<Entity Curr Adjs>

[Parent Adjs]

<Parent Curr Adjs>

<Entity Currency>

<Parent Currency>

[Parent]

[Parent Total]

<Parent Currency Total>

<Entity Currency Total>

Tip 1: The calculate sub procedure may

be run several times per entity.

• Unless restricted, Sub Calculate can run up to eight times per eight times per entityentity

• May accidentally end

[Contribution Adjs]

[Elimination][Proportion]

[Contribution Total]

[Contribution]

[Parent Total]

• May accidentally end up with double or triple the desired value

• Even if it’s correct, it simply takes unnecessary time

<Entity Curr Adjs>

[Parent Adjs]

<Parent Curr Adjs>

<Entity Currency>

<Parent Currency>

[Parent]

<Parent Currency Total>

<Entity Currency Total>

Specify the Value Dimension

If HS.Value.Member = "<Entity Currency>" Then

Some code statements…

Else

Some code statements…Some code statements…

End If

Tip 2: Reading Across the Value

Dimension

<Entity Curr Adjs><Entity Currency>

3

1 2

<Entity Currency Total>

• Rules are not run and you can’t write to <Entity Currency Total>

• (1) and (2) are entirely independent of each other

• Avoid reading from outside the data unit

•• Definitely avoid Definitely avoid reading Calculated values from outside the data unit.

HFM Rules 201:

Default Sub routines

Most CommonMost Common

• Calculate

• Translate

• Consolidate

Less CommonLess Common

• Allocate

• Input

InfrequentInfrequent

• ICT

• EPU

• Consolidate

• NoInput

• Dynamic

Tip 1: Debugging

The Editor will help you….

• Save time

• Format code

• Syntax check code• Syntax check code

• Use VB functions – The editor knows what

parameters are required etc.

• Has color coding

Tip:2 Put the POV into variables

• At the beginning of code place the Point of View into variables.

• Pass this into custom subroutines

Tip 3: Comparisons are case

sensitive.

'If you write this statement:

pov_scenario = HS.Scenario.Member

'And get this data out of the system:

pov_scenario is equal to "ACTUAL"

'This comparison is not true

If pov_scenario = "actual" thenIf pov_scenario = "actual" then

‘However this is true

If LCase(pov_scenario) = "actual" then

'The LCase function is the Lower Case function (Yes there is a UCasefunction for Upper Case)

What does this code do?

If HS.Value.Member = "<Entity Currency>" And HS.Scenario.Member = "ACTUAL" _ And HS.Year.Member >= "2002" Thenvariable1 = HS.Entity.List("","CC_Alloc_Ent")Dim iFor i = LBound(variable1) To UBound(variable1)If HS.Entity.Member = variable1(i) ThenVariable2 = HS.Getcell("A#account123.I#[ICPNone].C1#MfgFixGen.C2#[None].C3#[None].C4#[None]")+HS.Getcell("A#account123.I#[ICPNone].C1#MfgVarGen.C2#[None].C3#[None].C4#[N]")+HS.Getcell("A#account123.I#[ICPNone].C1#MfgVarGen.C2#[None].C3#[None].C4#[None]")+HS.Getcell("A#account123.I#[ICPNone].C1#ShippgWHGen.C2#[None].C3#[None].C4#[None]")+HS.Getcell("A#account123.I#[ICPNone].C1#SellingGen.C2#[None].C3#[None].C4#[None]")+HS.Getcell("A#account123.I#[ICPNone].C1#EnginGen.C2#[None].C3#[None].C4#[None]")+HS.Getcell("A#account123.I#[ICPNone].C1#AdminGen.C2#[None].C3#[None].C4#[None]")If Variable2 > 0 ThenCall Subprocedure1Exit ForEnd IfEnd IfNextEnd If

Tip 4: Formatting Counts

Formatting 1: Document Code

• Use comments to say what the code is doing– Add any concerns you may have about the code and possible ways for it

to break or need for maintenance

– Add your initials and dates to everything you change

• If you don’t document code, it is very difficult for anyone to help youyou

• Undocumented code is difficult to maintain (modify later)

• Your future audience may be you!

Formatting 2: Use meaningful variable

names.

• Don’t make your variables a secret code

• If the variable is holding average daily sales call it

something like:something like:

– AvgDaySales

– average_daily_sales

• Be careful if you mix case

Formatting 3: Use indenting

• Indent code in loopsFor month = 1 to 12

Line of codeLine of codeLine of code

Next ‘Month

• Indent code in conditional statements• Indent code in conditional statementsIf month = 6 then

Line of codeElse

Line of codeEnd If ‘month

• Comment the end of each loop or condition• 900 lines later, you’ll clearly understand which conditions were

in place

Formatting 4: Line Continuations

A formula may be complex but at least try to be reasonable as to where the line breaks occur.

Resources

• HFM Rules course from Oracle Education

• VBA for Dummies – Nice overview of programming and VBA.

• VBScript in a Nutshell– by Childs, Lomax, & Petrusha, published by O’Reilly, is a – by Childs, Lomax, & Petrusha, published by O’Reilly, is a

really handy syntax reference when coding.

• Microsoft – 6.0 Programmer’s Guide

• HFM_Admin.pdf & HFM_User.pdf– Functions guide

• Oracle Technology Network forum– http://forums.oracle.com/forums/forum.jspa?forumID=407&start=0

Tip 4: Type less

• Variables can hold stringsstrings which represent blocks of codeNones =".I#[ICP None].C1#[None].C2#[None].C3#[None].C4#[N one]"

• This works

HS.Exp " A#AvgWorkCap" & Nones & "=" & ( sum_wc / 13)HS.Exp " A#AvgWorkCap" & Nones & "=" & ( sum_wc / 13)

Tip 5: Impact Status

• HS.ImpactStatus(“target POV”)

• Function that simply changes the calc status of the target from anything to “CN”

– Only need to use on base entities

– Anytime Sub Calculate runs, you know that data has been updated

– Same or future Period, can be a different scenario, year, entity, etc.

• Always used for Roll-forwards• Always used for Roll-forwards

Impact Status for Synchronizing

Actual Data with Forecast

• Used to notify the Forecast that Actual has been updated– First, when you’re in the Actual scenario, change the calc status in the Forecast

Scenario

– Second, when you’re in the Forecast scenario, set all accounts equal to their respective values from Actual

• Also used for currency restatement scenarios

• Performance-wise, this is “expensive” so use wisely and sparingly• Performance-wise, this is “expensive” so use wisely and sparingly

Tip 8: Break the Rule file into

multiple sub-procedures

Advantages:• Each sub procedure will deal with one “set” or rules.

– Cash Flow– Allocations– Statistics

• You can turn types of rules on and off• Cuts down on repetitive code• You can turn types of rules on and off• Cuts down on repetitive code• Helps with debugging• Overall code is more readable• Calc Manager is designed for this approach

Calling all Sub Routines

Tip 9: Debugging Code

• The real mystery is: what are the values of variables

while the system is running?

• What is the POV when the rule is running?

• Did I meet an If..Then condition?• Did I meet an If..Then condition?

• Does my variable contain what I think it should?

• You can’t use a VB message box function

• Write information to a text file that you can look at

after the rules have run.

Writing to a text file

• Not natively part of HFM, but used so often most people think it is

• Added functionality directly into Calc Manager

Using the WriteToFile Routine

• Call WriteToFile(“Text to write out”)

• Call WriteToFile(“VariableName = ” & variable content)

Output of the File

• File output is from the HFM app server’s perspective and owned by the DCOM user

– Create a file share for this

• This is a snapshot of the text output

• I was just trying to see what data was in each of the value dimensions at a particular time

• The Time / Date Stamp is coded into the output, and is automatic

Intro to Calc Manager

The future of EPM rules developmentThe future of EPM rules development

Overview

• Web-based, object-oriented module that creates:

– HFM rules

– Planning Business Rules

• GUI creates flow chart-like representation of rules

• Relationship to EPMA• Relationship to EPMA

– 11.1.1.2 must use EPMA to use Calc Manager and vice-

versa

– 11.1.1.3 allows for Calc Manager to be used with Classic

applications and Classic rules with EPMA

Overview

How is this different from today’s rules?

• In the end the logic is still the same for HFM

– Calc Mgr creates and stores the visual representation

– During deployment an .rle file is created from the objects

and loaded to HFMand loaded to HFM

– Deployment is equivalent to today’s step of loading rules

– VBScript also be generated on demand to validate what

will be created during deployment

Object Hierarchy

• RuleSets

– Rules *– Rules *

•Components

* Rules can also contain other

nested Rules

Rule Sets

• RuleSets– Are equivalent to major subroutines (e.g. Calculate, Consolidate,

Translate, NoInput, etc.)

– Only one RuleSet per Calculation Type can be deployed at once but multiple versions can be created and stored

– RuleSets perform “Calls” for all of the individual rules

Rules

• Rules

– Are equivalent to custom subroutines that are called from main subroutines

– Where all of the actual code is actually housed

Elements – Components (Demo)

• Formula– Equivalent to any statement/formula such as HS.Exp or setting a variable equal

to something

– Also contains conditional processing for individual statements

• Script– Allows VBScript to be written for part of a rule rather than using the GUI

interface

• Condition• Condition– Used to wrap a conditional statement around other components (e.g. “If

pov_entity = x then….”)

• Member Range– A list of items that can be looped through, such as a member list

• Data Range– Equivalent to Open Data Unit loops

• Fixed Loop– A numbered series that can be looped through (e.g. “For i = 1 to 10”)

Formula Details (Demo)

• Almost all of the typically used functions are available, whether HFM specific, or general VBScript ones

• Changes in Syntax

– “@” symbol used at the beginning of formulas– “@” symbol used at the beginning of formulas

– HS.Exp is implicit if no variables are used but just POVs are referenced

– No need to use ampersands for concatenation

– No need to use double quotes unless space is part of member name

– Variables require “{}” brackets

Features (Demo)

• On-demand conversion to VBScript

– Done at the Rule or RuleSet level – allows you to see the code that will be generated upon deployment

– Can not be modified and converted back to Component objects but assists in troubleshooting

• Commentary available in numerous places• Commentary available in numerous places

• Logging (aka “Write to File”)

• Timer

• Disable – equivalent to “commenting out” a line

• Member, function and variable selection throughout

Replacement Variables

• Typically used for constants like static strings

• Need to be “declared” through the Variable

dialogue box before use

Execution Variables

• Typically used for situations in which variable is populated or reset as part of a rule (e.g. Open Data Unit members)

• Need to be “declared” through the Variable dialogue box before use

• Scope can be at RuleSet or Rule level• Scope can be at RuleSet or Rule level

Execution Variables: Boolean

• True or False

• Prefix with “b”

Execution Variables: Numeric

• Numeric value, usually data or a counter

• Prefix with “n”

Type Explicit Exp Statements

Execution Variables: String

• Strings, usually the label of a metadata member, or a user defined field contents

• Populated by functions

• Prefix with “s”

Other Features

• Sharing

• Expand/Collapse

• Zoom Levels

• Printing

Rule Development / Conversion

Approaches

• Create from scratch using new graphical objects

• Place the rules into script objects• Place the rules into script objects

• Use a conversion utility to migrate from VB Script to

Calculation Manager objects

Benefits

What are the benefits of using Calculation Manager?

• Easier to maintain for administrators

• Doesn’t require quite as much coding knowledge (but still requires app knowledge)

• More transparent way to explain calculations to • More transparent way to explain calculations to auditors or for documentation purposes

• Shared objects can be used in multiple situations and multiple applications

• Templates allow for the faster creation of the most commonly used rules and the benefit of leveraging best practices

Questions?

Chris BarbieriChris Barbiericbarbieri@ranzal.comcbarbieri@ranzal.com

Needham, Needham, MAMANeedham, Needham, MAMA

USAUSA

+1.617.480.6173+1.617.480.6173

www.ranzal.comwww.ranzal.com