Best Practices in Hyperion Financial Management Design & Implementation

(1)

Best Practices in

HFM Application Design

Chris Barbieri

Consolidation Practice Director Consolidation Practice Director

Oracle ACE Oracle ACE

Ranzal & Associates Ranzal & Associates

(2)

Personal Background

Chris Barbieri

•

• EsEstatablblisishehed HFd HFM peM perfrforormamancnce tue tunining ng tetechchniniququeses and statistics widely used today

and statistics widely used today •

• 4+ y4+ yeaears ars as Srs Sr. P. Proroduduct Ict Issssueues Mas Mananageger at r at HyHypeperirionon

–

– HFM, Smart HFM, Smart View, Shared Services, MDMView, Shared Services, MDM

,, HFM and Enterprise HFM and Enterprise • • MMBBAA, , BBaabbssoon n CCoolllleeggee •

• B.B.S. S. FiFinanancnce & e & AcAccocoununtitingng, B, Bosostoton Cn Colollelegege •

• CoCo-f-fououndnded ed ththe HFe HFM PM Pererfoformrmanance Tce Tununining Lg Lab aab att Ranzal with infrastructure expert Kurt Schletter Ranzal with infrastructure expert Kurt Schletter

(3)

Application Design: the Foundation of

Performance

• • H

Hyyp

per

eriio

on F

n Fiin

nan

ancciial

al

Management

• • Me

tada

data

ta de

desi

sign

gn as

as it

it im

impa

pact

ctss

–

– Volume of membersVolume of members –

– Impact of structuresImpact of structures

• • D

Da

atta

a

– – ContentContent – – DensityDensity

(4)

Metadata

(5)

Designing HFM’s 12

Dimensions

Application Profile

1 1.. YYeeaarr 2 2.. PPeerriioodd 3 3.. VViieeww

User controlled

5. 5. EntityEntity 6. 6. AccountAccount 7. 7. ICPICP 8. 8. ScenarioScenario 4

4.. VVaalluue e ddiimmeennssiioonn,, includes currencies includes currencies

User defined

9. 9. Custom 1Custom 1 10. 10. Custom 2Custom 2 11. 11. Custom 3Custom 3 12. 12. Custom 4Custom 4

(6)

Application Profile

Year

–

– No inherent impacNo inherent impact on performancet on performance –

– Cannot be chanCannot be changed after the application ged after the application is builtis built –

– Impacts the numImpacts the number of tables that can ber of tables that can be created in thebe created in the database

database

Period

–

– e e ase perase per oo s comprs compr se tse t e coe co umn structure oumn structure o every table, whether you use them or

every table, whether you use them or not.not. –

– For this reason, avoid wFor this reason, avoid weekly or yearly profiles unless iteekly or yearly profiles unless it is key to your

is key to your entire application’s designentire application’s design

View

–

– No impact, but No impact, but only YTD is stored and Periodic, only YTD is stored and Periodic, QTD areQTD are on-the-fly derivations

(7)

System Dimension

Value Dimension

–

– Can not Can not directly modify directly modify thisthis –

– “<Entity Curre“<Entity Currency>” is a sncy>” is a simple variable direcimple variable directing you to ting you to the currentthe current entity’s default currency

entity’s default currency –

– “<Parent Currency>” “<Parent Currency>” points back points back to the currency to the currency of the entity’sof the entity’s parent

parent

Currencies

–

– Don’t add Don’t add currencies you currencies you aren’t usingaren’t using

•

• Sets Sets of caof calc slc statutatus res recordcords fs for (evor (every ery entientityty**every currency)every currency) •

• ImpImpact of loact of loadiading mng metadetadata wiata with entith entity or cuty or currenrrency ccy changhangeses

–

– Normally Normally translate from the translate from the entity’s currency entity’s currency only into only into it’s parent’sit’s parent’s currency.

currency. –

– Beware of Beware of non-default trnon-default translationsanslations

•

• ImImpapactcted ed cacalc lc ststatatusus •

(8)

User Controlled Dimensions

Entity

–

– Sum of the data of the childrenSum of the data of the children –

– Avoid Consolidate All or All WAvoid Consolidate All or All With Data on each hierarchyith Data on each hierarchy –

– Assign AdjAssign Adj flags sparinglyflags sparingly

–

– “Hidden” dimension“Hidden” dimension

Scenario

–

(9)

Impact of Account Depth

4-4-Net INet Incncomeome

3-3-OpOptgtgInIncocomeme 5-5-EBEBITIT

6-6-Net INet Inconcomeme



 Effect is multiplied when you consider theEffect is multiplied when you consider the

custom dimensions custom dimensions



 Parent accounts don’t lockParent accounts don’t lock

2-2-GrosGross Ms Margiarginn

1-1- SaSaleless

4-4-OpOptgtgInInccomomee

3-3-GrGross Prooss Profitfit

2-2-GrosGross Ms Margiarginn

1-1- SaSaleless

(10)

User Defined Dimensions

Custom 1..4

–

– Think dozens or hundreds, but not thThink dozens or hundreds, but not thousandsousands –

– Avoid:Avoid:

•

• EEmmppllooyyeeeess •

• PPrroodduuccttss •

• AnyAnythithing ng thathat is t is vevery dry dynaynamimicc •

(11)

Metadata Efficiency Ratio

What does the average entity have in common with

the top

entity?

–

– Density measurement of re-use of the accDensity measurement of re-use of the accounts and customounts and customss across all entities

across all entities

top entity top entity children children unique custom 1 unique custom 1

(12)

Metadata Volumes (Americas)

D Diimmeennssiioonn AAvveerraaggee Volume Volume Recorded Recorded High High Comments Comments A Accccoouunnttss 22,,113322 1144,,440099 E Ennttiittiieess 11,,116655 2222,,888822 C

Cuurrrreenncciieess 1166 223333 _{use only}_{use only}₁₁_currency_currency_30%_30% C

Cuussttoomm11 338888 1199,,441100 use Custom 1use Custom 196%96%

C

S

Scceennaarriiooss 1111 7788 E

Ennttiittyyhhiieerraarrcchhiieess 33 2244 the equivalent of Organizations in Hyperion Enterprisethe equivalent of Organizations in Hyperion Enterprise

IICCP P AAccccoouunntts s wwiitth h PPlluugg 4411 11,,222233 use automated intercompany matchinguse automated intercompany matching56%56%

A

Accccoouunntts s wwiitth h LLiinne e IItteem m DDeettaaiill 3366 11,,666677 _16%_16%use this, but onlyuse this, but only10%10%have more thanhave more than11account flaggedaccount flagged

C

CoonnssoolliiddaattiioonnRuRulleess -- -- use consolidation rulesuse consolidation rules28%28%

C

Coonnssoolliiddaattiioon n mmeetthhooddss 55 1100 use methodsuse methods14%14%

OrgByPeriod

OrgByPeriod use organization by perioduse organization by period9%9%

IICCPPMMeemmbbeerrss 8686 11,,440077 track intercompany activitytrack intercompany activity81%81%

E

Ennttiittiiees s ffllaaggggeed d ffoor r PPaarreennt t AAddjjss 114433 77,,669988 Allow Allow [Parent Adj] or [Parent Adj] or [Contribution Adj] journals[Contribution Adj] journals30%30%

S

(13)

Data

(14)

What’s a Subcube?

• • H

HF

FM

M d

dat

ata

a ssttrru

uct

ctu

urre

e

• • Da

Data

taba

basse t

e tab

ablles

es st

stor

ored

ed by

by

–

– Each record containsEach record contains allall periods for the [Year]periods for the [Year] –

– All records for a subcube are loaded into memoryAll records for a subcube are loaded into memory togethertogether

Parent subcube, stored Parent subcube, stored in DCN tables

in DCN tables

Currency subcubes, Currency subcubes, stored in DCE tables stored in DCE tables

(15)

Take it to the Limit

Reports, Grids, or Forms that:

–

– Pull lots of entitiesPull lots of entities –

– Lots of yearsLots of years –

– Lots of scenariosLots of scenarios

Not so problematic:

–

– Lots of accountsLots of accounts –

– Or Custom dimension membersOr Custom dimension members

Smart View

–

– Cell volume impacts bandwidthCell volume impacts bandwidth –

(16)

HFM Urban Legends

• • 10

100,

0,00

000 r

0 rec

ecor

ords

ds pe

per

r su

subc

bcub

ube

e

• • In

Incr

creas

ease M

e MaxN

axNum

umDat

DataRe

aReco

cord

rdsI

sInRA

nRAM = be

M = bett

tter

er

performance

• • 50

500 0 ch

chiilldr

dren

en to

to a

a ar

aren

entt

• • Sy

Syst

stem 9

em 9 al

allo

lows a

ws an un

n unli

limi

mite

ted su

d sub cu

b cube s

be siz

ize

e

• • Cus

Custom

toms s

s shou

hould

ld be

be ord

ordered

ered lar

larges

gest

t to

to smal

smalles

lestt

• • Li

Limi

mit t

t to t

o the

he Ac

Acco

coun

unt d

t dim

imen

ensi

sion d

on dept

epth

h

•

(17)

Data Design

“Metadata volume is interesting, but it’s

ho

how

w y

you

ou

it that

it

that matter

matters

s most”

most”

• • D

De

en

nssiittyy

• • C

Co

on

ntte

en

ntt

–

– Specifically:Specifically: zeroszeros –

– Tiny numbersTiny numbers –

(18)

Data Volume Measurement

• • N

No p

o pe

errffe

ecct m

t me

etth

ho

od

d

M

Meetthhoodd HHooww--TToo PPrrooss CCoonnss

Data Extract

Data Extract Extract all data,Extract all data, count per entity count per entity

Simple, easy to see input Simple, easy to see input from calculated

from calculated

Can only extract Can only extract <Entity Currency> <Entity Currency>

FreeLRU

FreeLRU Parse HFM eventParse HFM event logs

logs

Good sense of average Good sense of average cube, easy to monitor cube, easy to monitor monthly growth monthly growth Can’t identify Can’t identify individual cubes, individual cubes, harder to understand harder to understand Database Database Analysis Analysis Query DCE, DCN Query DCE, DCN tables and count tables and count

Easy for a DBA, see all Easy for a DBA, see all subcubes

subcubes

Doesn’t count dynamic Doesn’t count dynamic members, includes members, includes invalid records invalid records

(19)

Data Density Using FreeLRU

• • Su

Surv

rvey o

ey of d

f data d

ata den

ensi

sity u

ty usi

sing F

ng Free

reeLRU

LRU me

meth

thod

od

Number of applications reviewed: 32

Number of applications reviewed: 32 AAvveerraaggee MMiinn MaMaxx MMeeddiiaann AABBCC Customer Customer NumCubesInRAM NumCubesInRAM 22,,667722 7722 1100,,220066 11,,334455 557777 NumDataRecordsInRAM NumDataRecordsInRAM 11,,550022,,778888 224477,,990000 5,627,7485,627,748 11,,117700,,990088 11,,110077,,661144 NumRecordsInLargestCube NumRecordsInLargestCube 8866,,441155 22,,550088 559933,,992244 5533,,008899 3311,,444466

Average records per cube

Average records per cube 66,,330099 2244 9911,,441188 11,,335522 22,,228888

Average metadata efficiency: Average metadata efficiency:

averag

average cube/densest e cube/densest cubecube

7

(20)

Loaded Data

• • Wh

What p

at perc

ercen

ent of t

t of the l

he loa

oade

ded da

d data i

ta is a ze

s a zero v

ro val

alue

ue?

?

–

– No hard rule, but <5% may be rNo hard rule, but <5% may be reasonableeasonable –

– No zeros are best, watch ZeroViewNo zeros are best, watch ZeroView settings on the scenariossettings on the scenarios

• • Wat

Watch

ch out

out for

for tin

tiny va

y value

lues,

s, resu

resultin

lting

g fro

from a

m allo

llocati

cations

ons

• • How

How muc

much d

h does

oes the

the dat

data e

a expa

xpand

nd fro

from S

m Sub

ub Calc

Calcula

ulate?

te?

–

– Am I generating zeros, or tiny numbers?Am I generating zeros, or tiny numbers?

I

Innppuut t BBaasse e RReeccoorrddss IInnppuut t PPlluus s CCaallccuullaatteed d BBaasse e RReeccoorrddss % % IInnccrreeaassee

Fr

FromomRuRulesles

T

Toottaall 22,,003311,,997766 TToottaall 44,,338877,,552200 _{116 %}_{116 %} IInnppuut t zzeerrooss 1188,,002244 CCaallccuullaatteed d zzeerrooss 441133,,883377 _{2,196 %}_{2,196 %} %

% zzeerro o llooaaddeedd 00..99%% % % zzeerroos s ccaallccuullaatteed d aat t bbaassee 99..44%% V

Vaalluuees s > > --1 1 aannd d < < 11 337733,,222266 VVaalluuees s > > --1 1 aannd d < < 1 1 ccaallccuullaatteedd 559933,,998811 _{59 %}_{59 %} %

(21)

Effect of Sparsity on Record Volume

• • Mo

Most

st den

dense

se dat

data i

a is a

s at t

t the

he to

top e

p ent

ntit

ityy

–

– Greatest number of populated intersectionsGreatest number of populated intersections (account _ custom 1..4

(22)

Consolidated Data

• • To

Tota

tal

l vo

vollum

ume o

e of d

f dat

ata i

a in a

n any

ny

subcube

• • Ho

How m

w man

any z

y zer

eros

os ar

are

e ge

gene

nera

rate

ted

d

by the consolidation process?

Consolidated Base

Consolidated Base RecordsRecords T

Toottaall 999911,,558877 C

Coonnssoolliiddaatteed d zzeerrooss 119944,,220044 %

% zzeerrooss 1199..66%%

–

– Intercompany eliminationsIntercompany eliminations –

– AllocationsAllocations –

– Empty variablesEmpty variables

V Vaalluuees s > > --1 1 aannd d < < 11 8844,,225511 % % vvaalluuees s > > --1 1 aannd d < < 11 88..55%% Loaded 0.9% Loaded 0.9% Calculated Calculated 9.4% 9.4% Consolidated Consolidated 19.6% 19.6%

(23)

Data Density <> Calc Time

1.000 1.000 1.500 1.500 2.000 2.000 2.500 2.500 400 400 500 500 600 600 700 700 800 800 900 900 S S e e c c o o n n d d s s R R e e c c o o r r d d s s Aver

Average Rule Execution Time iage Rule Execution Time in Contrast with Data n Contrast with Data VolumeVolume

correlation between density and calc times

• • Mo

Most

st app

appli

licat

catio

ions

ns are

are ru

rule

les b

s bou

ound

nd

--0.500 0.500 --100 100 200 200 300 300

(24)

Invalid Records

•

• • T

Tyyp

Type 1:

pe

e 1

1::

Orphaned records from metadata that has

been deleted

–

– Member is removed from dimension_ItemMember is removed from dimension_Item table, but nottable, but not from the data tables

from the data tables –

– These can be removed b These can be removed b Database > Delete Invalid RecordsDatabase > Delete Invalid Records

•

• • T

Tyyp

Type 2:

pe

e 2

2::

the member still exists, but is no longer in a

valid intersection

–

– Most often from changing CustomXMost often from changing CustomX Top Member on anTop Member on an account

account –

– These cannot be removed by HFM, but are filtered out inThese cannot be removed by HFM, but are filtered out in memory

(25)

(26)