Hyperionfinancialmanagement Applicationdesignforperformance 131205131558 Phpapp01 (2)

(1)

Hyperion Financial Management

Application Design for Performance

Chris Barbieri

(2)

Personal Background



Established HFM performance tuning techniques and

statistics widely used today



4+ years as Sr. Product Issues Manager at Hyperion



2001 HFM launch team 2001



Certified HFM, Hyperion Enterprise



B.S. Finance & Accounting, Boston College



MBA, Babson College



Established HFM Performance Tuning Lab at Ranzal

●

Vice President of world’s largest HFM practice



(3)

Personal Background



Established HFM performance tuning techniques and

statistics widely used today



4+ years as Sr. Product Issues Manager at Hyperion



2001 HFM launch team 2001



Certified HFM, Hyperion Enterprise



B.S. Finance & Accounting, Boston College



MBA, Babson College



Established HFM Performance Tuning Lab at Ranzal

●

Vice President of world’s largest HFM practice



(4)

Foundation of Performance



Focus first on single user



Metadata design as it

impacts performance

●

Volume of members

●

Impact of structures



Data

● ●

Content

● ●

Density



Rules



Environment

(5)

(6)

Designing HFM’s 12* Dimensions

Application Profile

1. Year

2. Period

3. View

System

4. Value dimension,

includes currencies

User controlled

5. Entity

6. Account

7. ICP

8. Scenario

User defined

9. Custom 1

10. Custom 2

11. Custom 3

12. Custom 4

(7)

Application Profile

Year

●

No inherent impact on performance

●

Can be increased after the application is built

●

Impacts database table volume

Period

●

Base periods comprise column structure of every table,

whether you use them or not

●

Avoid weekly profiles unless it is key to your entire

application’s design

●

Daily is inadvisable

View

●

No impact, but only YTD is stored

●

Other views are on-the-fly derivations

(8)

System Dimension

Value Dimension

●

Can not directly modify this

●

“<Entity Currency>” points to entity’s default currency

●

“<Parent Currency>” points to default currency of the entity’s parent

●

Anything above “<Entity Curr Total>” must be Parent.Child format

Currencies

●

Don’t add currencies you aren’t using

●

Sets of calc status records for (every entity

*

every currency)

●

Impact of loading metadata with entity or currency changes

●

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

currency

●

Beware of non-default translations

●

Impacted calc status

●

Data explosion

(9)

User Controlled Dimensions

Entity

●

Single biggest factor in consolidation time

●

Avoid Consolidate All or All With Data on each

hierarchy

●

Assign Adj flags sparingly

●

Don’t disable if you ever had journals on entity

ICP

●

“Hidden” dimension

Scenario

(10)

Impact of Account Depth



Effect is multiplied when you consider

the custom dimensions



Parent accounts don’t lock

4- Net Income

3- Optg Income

2- Gross Margin

1- Sales

4- Optg Income

3- Gross Profit

2- Gross Margin

1- Sales

5- EBIT

6- Net Income

(11)

User Defined Dimensions

Custom 1..4

●

Think dozens or hundreds; resist thousands

●

If Thousands are necessary, 64 bit makes this possible

●

Rules remain a major factor in performance

●

UI click

●

Avoid:

●

Employees

●

Detailed Products

●

Anything that is very dynamic, changing greatly from year to

year

●

One to one relationship with the entities

(12)

Metadata Efficiency Ratio

What does the average entity have in common with the

top entity?

●

Measure re-use of accounts and customs across entities

top entity

(13)

Metadata Volume Study: 82 apps

Median

+1 Std Deviation

High

Accounts

1,444

2,915

7,491

ICP Accounts With Plug

17

288 2,273

Accounts With Data Audit

32 1,358

7,490

Consolidation Rules

45%

OrgBy Period

16%

Currencies

25

57

150 Custom1

181 3,219

23,897

Custom2

72 2,374

20,484

Custom3

46

909 5,681

Custom4

19

182 1,199

Entity Hierarchies

4

12

44 Entities (unique)

672 4,352

21,199

ICP Members

208 1,160

7,770

Scenarios

12

29

81 Process Management

Scenarios

-

7

37 Scenarios Using Data Audit

-

11

78 Using Phased Submission?

17%

(14)

(15)

What’s a Subcube?



HFM data structure



Database tables stored by

●

Each record contains all periods for a [Year]

●

All records for a subcube are loaded into memory together

Parent subcube,

stored in DCN tables

Currency

subcubes, stored in

DCE tables

(16)

Take it to the Limit

Reports, Grids, or Forms that:

●

Pull lots of entities

●

Lots of years

●

Lots of scenarios

Not so problematic:

●

Lots of accounts

●

Lots of custom members

Smart View

●

Subcubes impact server performance

(17)

Data Design



Density



Content

●

Specifically: zeros

●

Tiny numbers

●

Invalid Records

“Metadata volume is interesting, but it’s

how you

it that matters most”

(18)

Data Volume Measurement



No perfect method

Method

How-To

Pros

Cons

Data

Extract

Extract all data,

count per entity

Simple, easy to see

input from calculated

Can only extract

<Entity Currency>

FreeLRU

Parse HFM event

logs

Good sense of

average cube, easy to

monitor monthly

growth

Can’t identify

individual cubes,

harder to understand

Database

Analysis

Query DCE, DCN

tables and count

Easy for a DBA, see

all subcubes

Doesn’t count

dynamic members,

includes invalid

records

(19)

Data Density Using FreeLRU

Number of applications reviewed

46 Median +1 Std Dev

Min

Max

NumCubesInRAM

1,369

9,426

72 51,840

NumDataRecordsInRAM

1,179,049 4,679,031 247,900 23,019,754

NumRecordsInLargestCube

53,089

167,085

2,508

5939!"

Records #er cube

1,352

15,537

24 91,418

(20)

Loaded vs. Consolidated Zeros



What percent of the loaded data is a

zero value?

●

<5% is reasonable

●

Ideally, no zeros

●

Watch ZeroView settings on scenarios



How many zeros are generated by

the consolidation process?

●

Intercompany eliminations

●

Allocations

●

Empty variables

Loaded 0.9%

Calculated 9.4%

(21)

Data Growth Up the Entity Hierarchy

Number of Entities

Top of hierarchy

1 Total in hierarchy

5,571

Base of hierarchy

2,980

Base Entity

input 91

records

_{Base Entity}

calculated 153

records

Average

Entity 178

records

Top Entity

16,829 records

(22)

Loaded, Calculated, and Consolidated Data



Rough stats: median from 12 applications

●

+1 std deviation

Monthly

Zeros

%

Monthly

Growth

Rules

Growth

Loaded Records

153,826

534,239

4.3%

3.3%

Loaded + Calculated

Records

349,360

717,570

22.5%

2.7%

2.0 Consolidated

Records

62,090

142,432

8.7%

3.2%

(23)

Invalid Records



Type 1:

Orphaned records from metadata that has

been deleted

●

Member is removed from dimension_Item table, but not

from the data tables

●

These can be removed by Database > Delete Invalid

Records



Type 2:

the member still exists, but is no longer in a

valid intersection

●

Most often from changing CustomX Top Member on an

account

●

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

(24)

So… How Much Memory Do I Really Need?

Calculation

Company

Number of entities

11,321

* 2 cubes: entity currency + contribution

11,321

Non-USD entities

2,939

add another cube for parent currency

2,939

Entity_value cubes

14,260

Actual 2013, 2012

2 Forecast1 2013, Plan 2013, TestForecast 2013

3 Tests, etc.

10 Total Year_scenarios

15 Total cubes

213,900

Average records per cube

175 Optimal NumDataRecordsinRAM setting

37,432,500

bytes per record

120

(25)

(26)

Data Density <> Calc Time

correlation between density and calc times



Most applications are rules bound

- 0.500 1.000 1.500 2.000 2.500 - 100 200 300 400 500 600 700 800 900

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

S e c o n d s R e c o r d s

(27)

But Some Applications are I/O Bound

Time vs. Volume

0

50

100

150

200

250

300

350

400

450 _

D

B

M

.

U

S

.

U

S

.

D

E

.

C

N

B

M

7

0

4

5

1

9

8

9

9 G

O

8

0

8

5

4

5

8

0

1

8

2

8

5

1 .

B

E

.

B

R

8

2

0

8

5

6

S e c o n d s

0 10,000

20,000

30,000

40,000

50,000

60,000

elapsed

totalrecords

(28)

How Long Should Rules Take?



Total consolidation time in seconds,12 periods

●

Consolidate All With Data for consistency, reliability

●

Fastest of three consecutive runs



Divide by 12 periods and total number of entities

●

Descendants inclusive of POV parent

0 0.25

2.0

4.0

10.0

(29)

Rules Impact Ratio



Total consolidation time with

rules



Divided by time with Blank

Rules



Typically 2- 5 times



More than that is an

(30)



Ideal for simple applications that do not need

consolidation rules



Skips writing [Proportion] to the database

●

Smaller DCN tables

●

If no [Elimination], [Parent Adjs] or [Contribution Adjs]

the DCN tables won’t even exist



Studies show about 30% faster consolidation times



Must be enabled at app creation

(31)

Reference

Application

(32)

Small but Constant Application



Applied across multiple environments

0:00:00 0:00:43 0:01:26 0:02:10 0:02:53 0:03:36 0:04:19 0:05:02

physical physical virtual virtual physical physical virtual physical virtual virtual virtual virtual virtual virtual virtual virtual virtual HFM lab A: Dev B: Dev C: Dev K: FIT D: QA Ranzal:

dev L: Stage K: dev T-61 laptop E: non-prod F: prod T-410 laptop

G: Dev H: Dev H: prod I: prod

Full Rules

Blank Rules