Accelatis Optimizing HFM Presentation

(1)

B

Be

et

tt

te

er

r P

Pe

er

rf

fo

or

rma

manc

nce

e.. L

Le

es

ss

s G

G ue

ues

ss

sw

wo

or

rk.

k.

HFM Optimization

Jonathan Berry

Ed DeLise

jberry@accelatis

.com

203.331.2267

,,

678.296.3611

(2)

A

Ag

gen

end

da

a

..

2 2.. C

Co

on

nffii u

urra

attiio

on

n

3 3.. D

Da

atta

a

4 4.. R

Ru

ulle

es

s

2

(3)

A

Ag

gen

end

da

a

..

2 2.. C

Co

on

nffii u

urra

attiio

on

n

3 3.. D

Da

atta

a

4 4.. R

Ru

ulle

es

s

2

(4)

W

Wha

hat i

t is A

s Ac

cce

cella

ati

tis?

s?

3

(5)

Background

•• F

Fo

ou

un

nd

de

ed b

d by J

y Jo

on

na

atth

ha

an B

n Be

errrry

y

••

y

yp

pe

err o

on

n n

na

an

nc

c a

a a

an

na

ag

ge

em

me

en

n e

ev

ve

e o

op

pm

me

en

n e

ea

am

m

--•• Dir

Direct

ector E

or Engi

nginee

neerin

ring fo

g for HF

r HFM &

M & FD

FDM at

M at Ora

Oracle

cle

•• T

Te

ea

am

m

–

– Hyperion Technologists with combined 50 years

Hyperion Technologists with combined 50 years of experience.

of experience.

•• T

To

on

ny

y M

Miittrrii

o

Former Oracle Consulting Member Technical Staff

o

Co-author of HFM Consolidation Engine and Rules Engine

•• Rob

Robb

b Sal

Salzma

zmann:

nn: 13

13 Yea

Year v

r vete

eteran

ran of

of Ess

Essbas

base

e Imp

Implem

lement

entati

ations

ons and

and Tun

Tuning

ing

–

– Ed DeLise, Former Senior Executive at Upstream/FDM

Ed DeLise, Former Senior Executive at Upstream/FDM

4

(6)

Where we fit in

With Accelatis

Without Accelatis

Application Administrator Application Administrator

Quantifiable data and insight

Guesswork

IT Outsourced IT

Who owns

IT Outsourced IT

Collaborative

Product Support Users

this?

Product Support Users

ec s on

Making

Management Management

5

(7)

APM Proficiency Model

What Level do you want to operate?

Proficient

User simulations, load generation, benchmarking & predictive alerting.

5 Accelatis Clients

_{within a few}

weeks

Proactive

Repeatable Optimization. Insight into user activity. Monitoring, automation and continuous testing performed.

4 Ex lorator

Monitoring with alerts. Some automation. Resolution processes implemented. SLA compliance

3 Accelatis Clients on Day 1

Reactionary

-Basic Monitoring. Reactionary review of data.

1 Most EPM Clients Are Here

Most EPM Clients Are Here

6

(8)

Accelatis Ascension Suite

™

• Accelatis facilitates all

Manage

• HFM Rules Profiler is ust

Oracle EPM Users

n c pa e

one feature within the

Optimize focus area

Resolve

Optimize

• Other Optimization

Simulate o

Essbase Calc Profiler

o

Planning Forms Optimizer

o

HFM Subcube Analyzer

(9)

Benefits of HFM Optimization

.

2. More Predictable Close

3. Prevent Outages

(10)

Challenges

1. Data organization and usage not widely understood

2. HFM Rules are VBScript (Anything goes)

(11)

Configuration

Components to tune

1. Operating System

• TCP, Memor settin s

2. Web Server

• Compression, Caching

.

• Consolidation settings in registry

HKEY_LOCAL_MACHINE\SOFTWARE\Hyperion Solutions\Hyperion Financial Management\Server

• 32/64 Bit Considerations

.

• Purging log tables

• Oracle-specific tuning

Review of event logs

FreeLRUs

(12)

Data: Tables

DCE, DCN Tables

DCN vary by parent: upper half of value dim

Table Population (MSSQL specific)

If #records > 15000 consider chan in

rimar ke to clustered rimar ke

SQL Example:

SELECT count(*)

_

_ _

(13)

Data: Subcubes

SubCube Population

If #records > 100,000 consider changing org structure

SQL Example:

SELECT lEntity, lValue, count(*) as numRecs

_

_ _

GROUP BY lEntity, lValue

(14)

Data: Zeros

No reason for soft zeros

Determine how many zeros are in each period

select * from COMMA_DCN_1_2005 where dp1_Input=0 and dp2_Input=0

an p _ nput= an

Then

foreach Period if d <PERIOD> In utTransT

_

e != 2 then SoftZero not

HardZero

(15)

HFM Rules Profiling

Why?

1. Faster Consolidations

2. More Predictable Close

3. Performance is affected not only by Rules changes but also by data changes,

period changes, POV, etc…

Challenges

1. Not easy to determine bottlenecks without expertise

2. Data organization and usage not widely understood

3. HFM Rules are VBScript (Anything goes)

What you will learn today

1. We will review the manual process of what our software does automatically

2. Huge value in profiling rules manually or using our tool

14

(16)

Goals of Profiling Rules

1. Determine where the time is going?

• re e ne unc ons

• Custom functions

• Blocks of code

• Time b Entit

2. Counters

• Invocations by function

• Invocations by specific line

3. Determine Rules effect on data?

• opu a ng zeros

• Subcube density

(17)

Steps (aka our Agenda)

1. Instrument Rules file

o

Add timin s to all functions

o

Track subroutines with complete stack trace

o

Track time by Entity

o

Add custom timing blocks and function line iterators

2. Capture Subcube Populations Prior to Calculation

3. Execute Consolidation

.

5. Analyze Results

o

Functions

o

Timing Blocks

o

Iterators

o

Timings by Entity

o

Changes to SubCube population due to Rules

.

16

(18)

Storing Data Techniques

1. Write to file

2. Write to database

3. Write to object

.

17

(19)

WriteToFile

Example Calls

1. Call WriteToFile("Finished CalcRoutine (Entity=" & UCase(HS.Entity.Member) & ").") 2. Call WriteToFile “Hit Line of Code: XXXYYY”

Sample WriteToFile function

Sub WriteToFile(data)

Const ForReading = 1, ForWriting = 2, ForAppending = 8 Dim fso, file

Set fso = CreateObject("Scripting.FileSystemObject")

Set file = fso.OpenTextFile("c:\Temp\RuleRunLog.txt", ForAppending, True) If (1) Then

Set file = fso.OpenTextFile("\\machine\data\timings.txt", ForAppending, True) f.WriteLine data & “ – “ & Now() & " - " & Timer()

f.Close

On Error GoTo 0' End If

*Performance issues result from multiple script engines writing to the

18

(20)

WriteToDatabase

Example Calls

1. Call WriteToDatabase("Finished CalcRoutine (Entity=" & UCase(HS.Entity.Member) & ").") 2. Call WriteToDatabase “Hit Line of Code: XXXYYY”

Sample WriteToDatabase function

WriteToDatabase(data) , ,

Const adOpenDynamic = 2, adLockPessimistic = 2, adCmdText = 1 Set conn = Server.CreateObject("ADODB.Connection")

conn.open connectionString

strSQLQuery = "INSERT INTO tablename (FieldName1, FieldName2) VALUES (data, Value2)" Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open strSQLQuery, conn, adOpenDynamic, adLockPessimistic, adCmdText conn.close

End Sub

*Be aware that the time to write the data to the database will affect overall

19

(21)

WriteToObject

Example Calls

1. Call WriteToObject("Finished CalcRoutine (Entity=" & UCase(HS.Entity.Member) & ").") 2. Call WriteToOb ect “Hit Line of Code: XXXYYY”

Sample WriteToObject function

WriteToObject(data)

Set objHelper = CreateObject(“PROGID”) Call objHelper.AddSample(data)

Set objHelper = Nothing End Sub

*Recommend performing the actual write activities in a thread so as

to limit the interru tion to the consolidation rocess.

20

(22)

Tracking Time

1. Adding timers to beginning and end of functions

2. Adding timers to blocks of code

3. Adding ‘Iterators’ capturing lines of code called

21

(23)

Timers

1. How to capture time spent

• mers o eg nn ng an en o unc ons

Sub Calculate()

'Accelatis Instrumentation

Dim AccRules, accStartTime, accEndTime, accFunctionTrace " "

Set AccRules=CreateObject("AccHFMRulesProfili ng.RulesProfiling_Class" ) accStartTime = AccRules.GetCurrentTime()

Call StandardCalculations (accFunctionTrace)

Call CalculateNetIncomeVarianceToPlan (accFunctionTrace) Call ValidateVarianceCommentary (accFunctionTrace) Call ValidateMiscExpenseDetail (accFunctionTrace) Call ValidateBalancedBalanceSheet (accFunctionTrace) Call ValidateFlashInput (accFunctionTrace)

Call CalculateCashFlow (accFunctionTrace) 'Accelatis Instrumentation

accEndTime = AccRules.GetCurrentTime()

Call AccRules.AddSample("Calculate",accFunctionTrace,, HS.Scenario.ID, HS.Scenario.Member, HS.Year.ID, HS.Year.Member, HS.Period.ID, . . , . . , . . , . . , . . , . . , . . , , End Sub

(24)

Capturing Function Stack

Custom functions called by multiple higher level

functions

BEFORE Sub Calculate()

Call CustomFunction Parameters

AFTER Sub Calculate()

Call CustomFunction Parameters “Calculate” End Sub Sub Translate() Call CustomFunction(Parameters) End Sub End Sub Sub Translate()

Call CustomFunction(Parameters, “Translate”) End Sub

Sub CustomFunction(parameters) ‘Do Stuff

Call WriteToFile(“CustomFunction”, parameters) End Sub

Sub CustomFunction(parameters, functionStack) ‘Do Stuff

Call WriteToFile(“CustomFunction”, parameters, functionStack) End Sub

23

(25)

Capturing POV

HS.<DIMENSION>.ID

and

HS.<DIMENSION>.Member

Example Usage

Call WriteToObject("Finished CalcRoutine (Entity=" & UCase(HS.Entity.Member) & ").“)

24

(26)

Putting it Together

Sub HelperFunction(accFunctionTrace) '

Dim AccRules, accStartTime, accEndTime, accFunctionTrace accFunctionTrace = accFunctionTrace & “HelperFunction”

Set AccRules=CreateObject("AccHFMRulesProfiling.RulesProfiling_Class") accStartTime = AccRules.GetCurrentTime() Call OtherFunction1(accFunctionTrace) Call OtherFunction2(accFunctionTrace) If (test = TRUE) 'Accelatis Instrumentation

Call AccRules.AddLineCounter(lineCountIDDefinedSomeplace, “HelperFunction”, lineNumber) Call DoOneThing(accFunctionTrace) Else Call DoAnotherThing(accFunctionTrace) End If Call OtherFunction3(accFunctionTrace) 'Accelatis Instrumentation accEndTime = AccRules.GetCurrentTime()

Call AccRules.AddSample(“HelperFunction“, accFunctionTrace, HS.Scenario.ID, HS.Scenario.Member, HS.Year.ID, HS.Year.Member, HS.Period.ID, HS.Period.Member, HS.Entity.ID, HS.Entity.Member, HS.Parent.ID,

HS.Parent.Member HS.Value.ID HS.Value.Member accStartTime accEndTime

25

(27)

Capturing Subcube Populations

select lEntity, lValue, COUNT (*) FROM <CURRENCY_TABLE>

group by lEntity, lValue

Entity Value NumRecords 5 10 72

5 11 34

select lEntity, lParent, lValue, COUNT (*) FROM <NODE_TABLE>

group by lEntity, lParent, lValue

Entity

Parent

Value

NumRecords

5

8

10

72

5

8

11

34 •Need to convert IDs to Metadata Labels

•Need to compare Before and After Consolidation

26

(28)

Gotchas

1. Multiple VBScript engines

• Total time calculated may be more than actual elapsed time

due to multiple threads

2. Performance Impact of Instrumentation

• If tracking function time, write data outside of timings

• If using Objects, make calls asynchronous so writing of data

happens in queue on background thread. Do not create thread

for every call!

3. Eliminate unnecessary calls to routines from global scope. This

can greatly increase load time and application startup time

27

(29)

Gotchas (cont)

4. Writing to file from multiple VBScript engines is dangerous.

deadlocks as well as major performance issues.

• Doing so effectively forces HFM Consolidation into single

.

5. Resolution of timing using the Timer function in VBScript is >=

-calculation errors over many iterations.

28

(30)

Analyzing Results

Manual Options

1. Import files into Excel and plot

2. Read data from database and plot in Excel or other SQL based

report writer

High Level Formulas (dependant on how data is written

and reporting to ol)

1. For Function Count, Filter by FunctionName and COUNT total rows

• Optionally include StackTrace in filter to narrow down code path

2. For Function Timing, Filter by FunctionName and SUM Duration by

Function/Entity/StackTrace

3. For Line Counts, Filter by LineCounterID and COUNT total rows

4. For Timing Blocks, Filter by TimingBlockID and SUM Duration on all rows

29

(31)

Accelatis Rules Profiler

Analysis: Graphical review of results

breakdown

• Overall Inclusive Function Time • Most Time Consuming Inclusive Functions • Overall Exclusive Function Time • Most Time Consuming Exclusive Functions • Function Call Frequency • Most Frequently Called Functions

30

(32)

Accelatis Rules Profiler

Analysis: Graphical review of results

• Timing Block times (Inclusive, Longest, Shortest, Frequency)

(33)

Accelatis Rules Profiler

Analysis: Graphical review of results

• Iterator frequency

(34)

Accelatis Rules Profiler

Analysis: Graphical review of results

• View time by Entity, Value and Rules Function (to pinpoint bottlenecks)

(35)

Upcoming Webinars

Top Notch Oracle EPM Performance: Load Testing and User Simulation

On Tuesday Apr il 10, we will cover the primary aspects of meaningful load testing and

user simulation, including:

•Essbase, Planning, HFM, and all associated products

•4 phases – Design, Validation, Execution and Analysis

•Single and Multi-User Performance, Concurrency and Longevity

• –

,

-•Analysis – identifying bottlenecks and remediation

Making Oracle EPM Run More Smoothly: The APM Proficiency Model

On Tuesday May 8th, we will discuss mastering the black art of Oracle EPM

Application Performance Management. We will cover:

•Better system performance

•Less guesswork when troubleshooting issues

• rov

ng rst c ass user exper ences

•Reducing risk during critical, high use periods

•Collaborate between departments effectively

34 ,

,

(36)

Questions?

Jonathan Berry

President & CEO

[email protected]

Ed DeLise

VP Sales & Marketing, Principal

[email protected]

35

(37)

How Accelatis Works

Transition Slide

(38)

HFM Rules Profiling

Profile Rules with little effort

• Create profile ‘packages’ that include instrumentation rules and consolidation profiles

• Profiler will automaticall ca ture all function timin s and distin uish to -level and

custom calls (inclusive and exclusive function timings)

(39)

HFM Rules Profiling

Timing Bl ocks and Iterators

• Timing blocks can be set to any block of code to collect timings

• Function Line Iterators will count how many times a line of code is hit

(40)

HFM Rules Profiling

Compare to Original Rules Code

(Extract instrumented Rules to RLE file an option)

(41)

HFM Rules Profiling

Automated Execution Steps:

1. Take snapshot of subcube populations

2. Extract original rules

3. Load instrumented rules

4. Run consolidation

5. Take post-snapshot of subcube population

6. Reload original rules

(42)

HFM Rules Profiling

Analysis: Graphical review of results

breakdown

• Overall Inclusive Function Time • Most Time Consuming Inclusive Functions • Overall Exclusive Function Time • Most Time Consuming Exclusive Functions • Function Call Frequency • Most Frequently Called Functions