accelatis optimizing hfm presentation

45
Better Performance. Less Guesswork. HFM Optimization Jonathan Berry President & CEO Ed DeLise VP Sales & Marketing Principal President & CEO [email protected] 203.331.2267 VP Sales & Marketing, Principal [email protected] 678.296.3611

Upload: matt-janecek

Post on 28-Dec-2015

62 views

Category:

Documents


0 download

DESCRIPTION

Accelatis Optimizing HFM Document

TRANSCRIPT

Page 1: Accelatis Optimizing HFM Presentation

Better Performance. Less Guesswork.

HFM Optimizationp

Jonathan BerryPresident & CEO

Ed DeLiseVP Sales & Marketing PrincipalPresident & CEO

[email protected]

VP Sales & Marketing, [email protected]

Page 2: Accelatis Optimizing HFM Presentation

Agenda

1 Background1. Background

2. Configurationg

3. Data

4. Rules

2

Page 3: Accelatis Optimizing HFM Presentation

What is Accelatis?

3

Page 4: Accelatis Optimizing HFM Presentation

Background

• Founded by Jonathan BerryH i Fi i l M t D l t T 1998 2008• Hyperion Financial Management Development Team 1998-2008

• Director Engineering for HFM & FDM at Oracle

• Team– Hyperion Technologists with combined 50 years of experience.

• Tony Mitrio Former Oracle Consulting Member Technical Staffo Co-author of HFM Consolidation Engine and Rules Engine

• Robb Salzmann: 13 Year veteran of Essbase Implementations and Tuning

– Ed DeLise, Former Senior Executive at Upstream/FDM

4

Page 5: Accelatis Optimizing HFM Presentation

Where we fit inWith AccelatisWithout Accelatis

Application Administrator

Application Administrator

Quantifiable data and insightGuesswork

ITOutsourced IT

Who owns

ITOutsourced IT

CollaborativeD i i

Product SupportUsers

this?

Product SupportUsers

Decision Making

pp

Management

pp

Management

5

Page 6: Accelatis Optimizing HFM Presentation

APM Proficiency ModelWhat Level do you want to operate?

ProficientUser simulations, load generation, benchmarking &

predictive alerting.5

Accelatis Clients within a few

weeks

Procedural

ProactiveRepeatable Optimization. Insight into user activity.

Monitoring, automation and continuous testing performed.4

Exploratory

ProceduralMonitoring with alerts. Some automation. Resolution

processes implemented. SLA compliance

2

3 Accelatis Clients on Day 1

ReactionaryNo monitoring Ad-hoc resources gathered for each issue

ExploratoryBasic Monitoring. Reactionary review of data.

1

2

Most EPM Clients Are HereMost EPM Clients Are Here

6

No monitoring. Ad-hoc resources gathered for each issue.

Page 7: Accelatis Optimizing HFM Presentation

Accelatis Ascension Suite™

• Accelatis facilitates all EPM focus areas

Manage

MonitorA ti i t

EPM focus areas

• HFM Rules Profiler is just

Oracle EPM Users and Components

MonitorAnticipate HFM Rules Profiler is just one feature within the Optimize focus area

and Components

ResolveOptimize • Other Optimization capabilities include:

Simulate

capabilities include:o Essbase Calc Profilero Planning Forms Optimizero HFM Subcube Analyzer

7

Page 8: Accelatis Optimizing HFM Presentation

Benefits of HFM Optimization

1 Faster Consolidations1. Faster Consolidations

2. More Predictable Close

3. Prevent Outages

8

Page 9: Accelatis Optimizing HFM Presentation

Challenges

1. Data organization and usage not widely understood

2. HFM Rules are VBScript (Anything goes)

9

Page 10: Accelatis Optimizing HFM Presentation

ConfigurationComponents to tune

1. Operating System• TCP, Memory settings, y g

2. Web Server• Compression, Caching

3 App Server3. App Server• Consolidation settings in registry HKEY_LOCAL_MACHINE\SOFTWARE\Hyperion

Solutions\Hyperion Financial Management\Server• 32/64 Bit Considerations

4 Database4. Database• Purging log tables• Oracle-specific tuning

Review of event logsFreeLRUs

10

Page 11: Accelatis Optimizing HFM Presentation

Data: TablesDCE, DCN TablesDCN vary by parent: upper half of value dim

Table Population (MSSQL specific)If #records > 15000 consider changing primary key to clustered primary key# eco ds 5000 co s de c a g g p a y ey o c us e ed p a y ey

SQL Example:SELECT count(*) FROM COMMA DCE 1 2011FROM COMMA_DCE_1_2011

11

Page 12: Accelatis Optimizing HFM Presentation

Data: SubcubesSubCube PopulationIf #records > 100,000 consider changing org structure

SQL Example:SELECT lEntity, lValue, count(*) as numRecsFROM COMMA DCE 1 2011FROM COMMA_DCE_1_2011GROUP BY lEntity, lValue

12

Page 13: Accelatis Optimizing HFM Presentation

Data: ZerosNo reason for soft zerosPossible erroneous hard zerosPossible erroneous hard zeros

Determine how many zeros are in each periodselect * from COMMA_DCN_1_2005 where dp1_Input=0 and dp2_Input=0

d d 3 I t 0 d XXXand dp3_Input=0 and XXX

Thenforeach Period if dp<PERIOD> InputTransType != 2 then SoftZero, not p _ p yp ,

HardZero

13

Page 14: Accelatis Optimizing HFM Presentation

HFM Rules ProfilingWhy?1. Faster Consolidations2. More Predictable Close3. Performance is affected not only by Rules changes but also by data changes,

period changes, POV, etc…

Challenges1. Not easy to determine bottlenecks without expertise2. Data organization and usage not widely understood3. HFM Rules are VBScript (Anything goes)

What you will learn today1. We will review the manual process of what our software does automatically2. Huge value in profiling rules manually or using our tool

14© 2012 Accelatis, LLC

Page 15: Accelatis Optimizing HFM Presentation

Goals of Profiling Rules

1. Determine where the time is going?P d fi d f ti• Predefined functions

• Custom functions• Blocks of code• Time by Entityy y

2. Counters• Invocations by function• Invocations by specific line

3. Determine Rules effect on data?P l ti• Populating zeros

• Subcube density

© 2012 Accelatis, LLC 15

Page 16: Accelatis Optimizing HFM Presentation

Steps (aka our Agenda)1. Instrument Rules file

o Add timings to all functionso Add timings to all functionso Track subroutines with complete stack traceo Track time by Entityo Add custom timing blocks and function line iteratorso Persist timings datao Persist timings data

2. Capture Subcube Populations Prior to Calculation3. Execute Consolidation4 Capture Subcube Populations After Calculation4. Capture Subcube Populations After Calculation5. Analyze Results

o Functionso Timing Blockso Iteratorso Timings by Entityo Changes to SubCube population due to Rules

6 Remediation6. Remediation

16© 2012 Accelatis, LLC

Page 17: Accelatis Optimizing HFM Presentation

Storing Data Techniques

Options for persisting resultsOptions for persisting results

1. Write to file

2. Write to database

3. Write to object

4 Hybrid Approach4. Hybrid Approach

17© 2012 Accelatis, LLC

Page 18: Accelatis Optimizing HFM Presentation

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

Sample WriteToFile functionSub WriteToFile(data)

On Error Resume NextOn Error Resume NextConst ForReading = 1, ForWriting = 2, ForAppending = 8Dim fso, fileSet 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.CloseOn Error GoTo 0'

End IfEnd SubEnd Sub

*Performance issues result from multiple script engines writing to the same file

18© 2012 Accelatis, LLC

same file.

Page 19: Accelatis Optimizing HFM Presentation

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

Sample WriteToDatabase functionWriteToDatabase(data)

Dim conn, strSQLQuery, rsDim conn, strSQLQuery, rsConst 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.closeset conn = Nothingset conn = Nothing

End Sub

*Be aware that the time to write the data to the database will affect overall lid ti ti d lti t l f ti i ti

19© 2012 Accelatis, LLC

consolidation time and ultimately accuracy of timing proportions.

Page 20: Accelatis Optimizing HFM Presentation

WriteToObjectExample Calls1. Call WriteToObject("Finished CalcRoutine (Entity=" & UCase(HS.Entity.Member) & ").")2. Call WriteToObject(“Hit Line of Code: XXXYYY”)j ( )

Sample WriteToObject functionWriteToObject(data)

Dim objHelperDim objHelperSet 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 interruption to the consolidation process.to limit the interruption to the consolidation process.

20© 2012 Accelatis, LLC

Page 21: Accelatis Optimizing HFM Presentation

Tracking Time

1. Adding timers to beginning and end of functions2. Adding timers to blocks of code3. Adding ‘Iterators’ capturing lines of code called

21© 2012 Accelatis, LLC

Page 22: Accelatis Optimizing HFM Presentation

Timers

1. How to capture time spentAdd ti t b i i d d f f ti• Add timers to beginning and end of functions

Sub Calculate()

'Accelatis InstrumentationDim AccRules, accStartTime, accEndTime, accFunctionTraceaccFunctionTrace = "Calculate"accFunctionTrace = CalculateSet AccRules=CreateObject("AccHFMRulesProfiling.RulesProfiling_Class")accStartTime = AccRules.GetCurrentTime()

Call StandardCalculations (accFunctionTrace)Call CalculateNetIncomeVarianceToPlan (accFunctionTrace)Call ValidateVarianceCommentary (accFunctionTrace)Call ValidateMiscExpenseDetail (accFunctionTrace)Call ValidateFixedAssetDetail (accFunctionTrace)Call ValidateFixedAssetDetail (accFunctionTrace)Call ValidateBalancedBalanceSheet (accFunctionTrace)Call ValidateFlashInput (accFunctionTrace)Call CalculateCashFlow (accFunctionTrace)

'Accelatis InstrumentationaccEndTime = AccRules.GetCurrentTime()Call AccRules.AddSample("Calculate",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)HS.Period.Member, HS.Entity.ID, HS.Entity.Member, HS.Parent.ID, HS.Parent.Member, HS.Value.ID, HS.Value.Member, accStartTime, accEndTime)

End Sub

© 2012 Accelatis, LLC 22

Page 23: Accelatis Optimizing HFM Presentation

Capturing Function Stack

Custom functions called by multiple higher level functions

BEFORESub Calculate()

Call CustomFunction(Parameters)

AFTERSub 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 StuffCall WriteToFile(“CustomFunction”, parameters)

End Sub

Sub CustomFunction(parameters, functionStack)‘Do StuffCall WriteToFile(“CustomFunction”, parameters, functionStack)

End Sub

23© 2012 Accelatis, LLC

Page 24: Accelatis Optimizing HFM Presentation

Capturing POVHS.<DIMENSION>.ID and HS.<DIMENSION>.Member

Example UsageCall WriteToObject("Finished CalcRoutine (Entity=" & UCase(HS.Entity.Member) & ").“)j ( ( y ( y ) ) )

24© 2012 Accelatis, LLC

Page 25: Accelatis Optimizing HFM Presentation

Putting it TogetherSub HelperFunction(accFunctionTrace)

'Accelatis Instrumentation'Accelatis InstrumentationDim AccRules, accStartTime, accEndTime, accFunctionTraceaccFunctionTrace = accFunctionTrace & “HelperFunction”Set AccRules=CreateObject("AccHFMRulesProfiling.RulesProfiling_Class")accStartTime = AccRules.GetCurrentTime()

Call OtherFunction1(accFunctionTrace)Call OtherFunction2(accFunctionTrace)If (test = TRUE)

'Accelatis InstrumentationCall AccRules.AddLineCounter(lineCountIDDefinedSomeplace, “HelperFunction”, lineNumber)Call DoOneThing(accFunctionTrace)

ElseCall DoAnotherThing(accFunctionTrace)

End IfCall OtherFunction3(accFunctionTrace)

'Accelatis InstrumentationaccEndTime = 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© 2012 Accelatis, LLC

, , , , )

End Sub

Page 26: Accelatis Optimizing HFM Presentation

Capturing Subcube Populations

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

Entity Value NumRecords5 10 725 11 34

select lEntity, lParent, lValue, COUNT (*) FROM <NODE_TABLE>group by lEntity, lParent, lValueg p y y

Entity Parent Value NumRecords5 8 10 725 8 11 34

•Need to convert IDs to Metadata Labels•Need to compare Before and After Consolidation

26© 2012 Accelatis, LLC

Page 27: Accelatis Optimizing HFM Presentation

Gotchas

1. Multiple VBScript engines• Do not use global variables as counters• Do not use global variables as counters• 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 g j y g

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© 2012 Accelatis, LLC

Page 28: Accelatis Optimizing HFM Presentation

Gotchas (cont)

4. Writing to file from multiple VBScript engines is dangerous. • Each instance may block access to the file and cause• Each instance may block access to the file and cause

deadlocks as well as major performance issues. • Doing so effectively forces HFM Consolidation into single

threaded operationthreaded operation.

5. Resolution of timing using the Timer function in VBScript is >= 15ms. Using this built-in timing function can amplify timing15ms. Using this built in timing function can amplify timing calculation errors over many iterations.

28© 2012 Accelatis, LLC

Page 29: Accelatis Optimizing HFM Presentation

Analyzing ResultsManual Options

1. Import files into Excel and plot2. 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 tool)

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/StackTracey

3. For Line Counts, Filter by LineCounterID and COUNT total rows4. For Timing Blocks, Filter by TimingBlockID and SUM Duration on all rows

29© 2012 Accelatis, LLC

Page 30: Accelatis Optimizing HFM Presentation

Accelatis Rules ProfilerAnalysis: Graphical review of results

FunctionFunction 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

Page 31: Accelatis Optimizing HFM Presentation

Accelatis Rules Profiler

Analysis: Graphical review of results• Timing Block times (Inclusive, Longest, Shortest, Frequency)

31

Page 32: Accelatis Optimizing HFM Presentation

Accelatis Rules Profiler

Analysis: Graphical review of results• Iterator frequency

32

Page 33: Accelatis Optimizing HFM Presentation

Accelatis Rules Profiler

Analysis: Graphical review of results• View time by Entity, Value and Rules Function (to pinpoint bottlenecks)

33

Page 34: Accelatis Optimizing HFM Presentation

Upcoming WebinarsTop Notch Oracle EPM Performance: Load Testing and User Simulation

On Tuesday April 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•Test Execution – monitoring test-refinementTest Execution monitoring, test refinement•Analysis – identifying bottlenecks and remediation

Making Oracle EPM Run More Smoothly: The APM Proficiency Modelg y yOn 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 issuesP idi fi t l i•Providing first class user experiences

•Reducing risk during critical, high use periods•Collaborate between departments effectively

To register or to view past sessions go to:

© 2012 Accelatis, LLC 34

To register, or to view past sessions, go to: http://www.accelatis.com/webinars-hyperion

Page 35: Accelatis Optimizing HFM Presentation

Questions?

Jonathan BerryPresident & [email protected] 331 2267

Ed DeLiseVP Sales & Marketing, [email protected] 296 3611

35© 2012 Accelatis, LLC

203.331.2267 678.296.3611

Page 36: Accelatis Optimizing HFM Presentation

How Accelatis Works

Transition Slide

36

Page 37: Accelatis Optimizing HFM Presentation

HFM Rules ProfilingProfile Rules with little effort• Create profile ‘packages’ that include instrumentation rules and consolidation profiles• Profiler will automatically capture all function timings and distinguish top-level andProfiler will automatically capture all function timings and distinguish top level and

custom calls (inclusive and exclusive function timings)

37

Page 38: Accelatis Optimizing HFM Presentation

HFM Rules ProfilingTiming Blocks 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

38

Page 39: Accelatis Optimizing HFM Presentation

HFM Rules Profiling

Compare to Original Rules Code (Extract instrumented Rules to RLE file an option)

39

Page 40: Accelatis Optimizing HFM Presentation

HFM Rules Profiling

Automated Execution Steps:1. Take snapshot of subcube populations2. Extract original rules3. Load instrumented rules4. Run consolidation5. Take post-snapshot of subcube population6. Reload original rules

40

Page 41: Accelatis Optimizing HFM Presentation

HFM Rules ProfilingAnalysis: Graphical review of results

FunctionFunction 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

41

Page 42: Accelatis Optimizing HFM Presentation

HFM Rules Profiling

Analysis: Graphical review of results• Timing Block times (Inclusive, Longest, Shortest, Frequency)

42

Page 43: Accelatis Optimizing HFM Presentation

HFM Rules Profiling

Analysis: Graphical review of results• Iterator frequency

43

Page 44: Accelatis Optimizing HFM Presentation

HFM Rules Profiling

Analysis: Graphical review of results• View time by Entity, Value and Rules Function (to pinpoint bottlenecks)

44

Page 45: Accelatis Optimizing HFM Presentation

HFM Rules Profiling

Analysis: Graphical review of results• View SubCubes with largest data Increases due to Rules• *In this example there is no data change…

45