Exceptions to the Rule: Essbase Design Principles That Don t Always Apply

Exceptions to the Rule: Essbase Design

Principles That Don’t Always Apply

Edward Roske, CEO

Oracle ACE Director

[email protected]

BLOG: LookSmarter.blogspot.com

WEBSITE: www.interrel.com

About interRel



Reigning Oracle Award winner

EPM & BI Solution of the year



Three Oracle ACE Directors for

Hyperion



Oracle Platinum Partner



One of the 100 fastest growing

tech companies in the USA

(CRN Magazine, 2007-2010)



Authors of the 8 Best Selling

books on Hyperion & Essbase



Only 4 books in the world on v11



All available on LuLu.com

3 Consulting Training Support Infra structure Press

Focused exclusively on

Oracle EPM & BI



8 Hyperion Books Available:



Essbase (7): Complete Guide



Essbase System 9: Complete Guide



Essbase System 9: End User Guide



Essbase 11: Admin Guide



Essbase Studio 11



Smart View 11: End User Guide



Planning: End Users Guide



Planning: Administrators



To order, check out

www.LuLu.com

Copyright © 2007, Hyperion. All rights reserved.

5

Disclaimer

These slides represent the work and opinions of

the presenter and do not constitute official

positions of Oracle or any other organization.

These material has not been peer reviewed and is

presented here with the permission of the

presenter.

These should not be copied, recorded or

reproduced without the expressed written

permission of interRel Consulting.

2011 LinkedIn.Com Analysis

of Years of Work Experience

We have always been told we have to follow

the rules!

Today I’m going to teach you it is sometimes

good to break the rules.



There isn’t one right answer

Tuning



Tuning guidelines can contradict other tuning

guidelines



Tuning for calculations vs. tuning for retrievals



Sometimes these tuning tips will have significant

impact, either positive or negative

Don't put more than one

database in an application

How Many Databases per Application?

 Usually one database per application

 If one database crashes, all databases could crash

 If one database is heavily used, this could negatively impact

performance of the other databases

C

o

p

y

ri

g

h

t

©

2

0

0

7

i

n

t

e

r

R

e

l

C

o

n

s

u

lt

i

n

g

p

r

e

s

e

n

t

a

ti

o

n

.

A

ll

ri

g

h

t

s

r

e

s

e

r

v

e

d

.

The Exceptions to the Rule

 You need to use XREF to share data across databases

 Essbase server process is shared across databases

 Performance will be faster

 Think

 Oracle Hyperion Planning

 Currency databases  Multi-currency applications

C

o

p

y

ri

g

h

t

©

2

0

0

7

i

n

t

e

r

R

e

l

C

o

n

s

u

lt

i

n

g

p

r

e

s

e

n

t

a

ti

o

n

.

A

ll

ri

g

h

t

s

r

e

s

e

r

v

e

d

.

The Exception to the Rule

 Unless almost every user has unique access where it is redundant

to have one to one users to groups

 Recent – Ask a Guru question

 I have a one-to-one relationship between the department and

the planner in all cases except 1 - Account, scenario, and version security will be set up for everyone the exact same (with only 1 exception).

 Recent – Ask a Guru questions (cont.)

 Typically, I set up security for the Planning dimensions, task lists, forms, business rules, etc. using groups but I'm not sure I should go through the trouble of creating all these groups if there is only one user in each of them.

 I am contemplating creating one planning group and one

admin group and granting security to everything except department based on this group. I would then set up security on the department based on the individual users. What do you think??

Dense Dimensions Make Up Cells within a Block

Actual Budget Var. Var%

Measures

Scenario

Calculate Block Size



Data block size is determined by the amount of data

in a particular combination of dense dimensions



Data block size is 8n bytes, where n is the number of

cells that exist for that combination of dense

dimensions



8 to 100 KB is the recommended block size

 If data blocks are smaller than 8K, the index is very large, forcing Essbase to write to and retrieve index from disk

 If data blocks are larger than 100KB, intelligent calc does not work efficiently and performance can be slower

The Exception to the Rule

 Testing proves that a bigger block size performs better

 64 bit Essbase

 With the 64-bit engine, larger blocks in the 1 MB range have

shown significant reductions in calculation times

 Performance gains are attributable to wider bandwidth disk

The Exception to the Rule

 If you have a large database and you need to frequently add

new accounts or change existing accounts, consider making Accounts sparse

 Especially in Planning where you need more frequent

Don't use a cross dimensional

operator on the left side of an

Try to Limit Cross-Dims

 Using cross-dimensional operator:

 Faster, easier to maintain:

22

FIX (Sales)

Budget = Actual * 1.1;

ENDFIX

FIX (Budget)

Sales = Sales->Actual * 1.1;

ENDFIX

Left Hand Cross-Dims

 Don’t ever do this:

 Write it like this:

FIX (Budget)

Sales = COGS->Actual * 1.1;

ENDFIX

The Exception to the Rule

 You have a special case where you need to force creation of

blocks.

 Using both a dense and sparse member in left hand side of

an equation will create blocks without the nasty Createblocksonequation command.

SALES(

Sales->Budget = COGS->Actual * 1.1;

)

The Exception to the Rule

 Sometimes performance is much better if you hardcode

something in a formula versus using costly functions like @prior

 Can be MUCH faster than @PRIOR

IF(@ISmbr("Jan") and @ISMBR("FY10"))

BegBal = "Sales"->"Dec"->"FY09";

ELSEIF(@ISmbr("Jan") and @ISMBR("FY09"))

BegBal = "Sales"->"Dec"->"FY08";

Focusing Calculations

 Two most common methods

 Fix  If  Other methods:  CrossDim operator  MemberSet functions  Relationship functions  Variables

If/Else/ElseIf/EndIf



Conditional processing is often needed to process business

rules



Can include complex conditions



And, Or, Not, etc.



Syntax



IF (condition) ElseIf/Else EndIf;



Summary



Condition must be if parenthesis



In a calc script, associate with a member name



Does really matter which member name

Fix/EndFix



FIX Used to focus or limit a calculation



Used extensively



Most effective when used on members of sparse

dimensions



Only blocks that meet the Fix criteria are processed



Remaining blocks are simply skipped



Typical uses



Only calculate one scenario



Limit calc to current year



Syntax

To IF or FIX



Generally, IF works best on dense dimension

members



Particularly when ElseIf or Else are included

 Block is only brought into memory once and all related

conditions are processed

 Fix would bring block in multiple times



Conversely, FIX works best on sparse

dimension members



Old saying “Fix on Sparse, If on dense”

The Exception to the Rule



FIX works properly on dense members



May result in all blocks being processed



All statements between the FIX and ENDFIX are

executed as a block



FIX statements can be nested



Multiple members from the same dimension are

treated as “or”

 In other words, all members will be processed



If a dense member is in a fix with sparse members,

only those dense members are looked at within the

block, so fixing on the combo of both can be good

Always follow the Hourglass

(or Hourglass on a stick) method

Dimension Ordering based on Member Counts–

Example

Dimension Ordering based on Dimension Density -

Example

Dimension Type-Size Density After Calc Density After Load Data Points Created Time Periods D – 21 85% 85% - Metrics (Hrs, AHR, $) D – 14 22% 22% - Accounts D – 94 3 % 2% - Scenarios AS – 9 22% 11% 199 Job Code AS – 1,524 .56% .23% 853 Organization AS – 2,304 .34% .09% 783 Versions NAS – 7 19% 19% - Years NAS – 7 14% 14% -

How to Determine Individual Dimension Density

1.

Make the dimension the lone Dense dimension

2.

Load and calculate just that dimension

3.

Check the block density value in Administration

Services >> Database >> Properties >> Statistics



Ordering the dense dimensions from most dense to

least dense maximizes the clustering of the data



A more condensed database will perform better than

one where the data has a highly dispersed

Hourglass on a Stick

Original Optimized

Accounts (D) Time Periods (D) Time Periods (D) Metrics (D)

Metrics (D) Accounts (D)

Years Job Code (AS)

Versions Organization (AS)

Scenarios Years (NAS)

Job Code Versions (NAS)

Organization Scenarios (NAS)

Employee Status Employee Status (Attr Dim) Fund Group Fund Group (Attr Dim)

D=Dense, AS=Aggregating Sparse, NAS=Non-Aggregating Sparse

Typical Hourglass

Modified Hourglass

The Exception to the Rule



Outline order impacts retrievals



Sparse retrievals



Sparse dimensions down the rows or

across the columns



Accesses multiple indexes and multiple

blocks



Dense retrievals



Dense dimensions down the rows and across the

columns



Accesses one index entry and one block



Faster retrieval times



So how your reports are designed may

impact what you choose as “sparse” and

“dense”



If retrieval performance is slow, you may also

want to consider moving up heavily queried

dimensions in the outline

The Exception to the Rule



You want to use parallel calculation



Because HGoaS usually puts dimensions at the

bottom of the outline that are getting fixed on, it could

prevent parallel calculation



You may be able to overcome this by increasing the

taskdims.



Serial Calculation



Default



Current Essbase behavior



Each calc is executed serially



Parallel Calculation



Set at system, application, database or calc script



Generates tasks



Schedules tasks to run on up to 4 threads



Operating system can schedule each thread on a

separate CPU

Parallel Calculation



Apply multiple processors to a calc



Only straight forward calcs can use this



If order dependent portions, calculated in serial



Maximum of 4 processors

Parallel Calculations



If parallel processing is feasible, Essbase splits the

request into independent tasks that can be run

concurrently



Set at the server, application, database or individual

calc script level



Can run on up to 4 threads



Essbase will analyze the outline and calculation

request to determine if parallel calc is possible



Check the application log to see how parallel calc is

being used

Parallel Calculations



Essbase will review the request



Set number of worker threads



In essbase.cfg – CALCPARALLEL appname dbname n



In calc script – SET CALCPARALLEL n



N = 1 through 4; default is 1



Set number of dimensions in task suffix



In essbase.cfg – NUMTASKDIMS appname dbname n



In calc script – SET NUMTASKDIMS n



N = 1 to number of sparse dimensions

Exception



Sometimes parallel calculation is slower



Small set of data being processed



Fix limits the task dimensions



Complex formula interdependencies will force a

serial calc

Always give enough memory to

load the whole index into memory

Make the data cache as large as

possible if you have plenty of

Cache Definitions

 Index cache - buffer in memory that holds index pages (.ind).

How many index pages are in memory at one time depends upon the amount of memory allocated to the cache.

 Data file cache - buffer in memory that holds compressed data

files (.pag files). Essbase allocates memory to the data file

cache during data load, calculation, and retrieval operations, as needed. Only used with direct I/O.

 Data cache - buffer in memory that holds uncompressed data

blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations, as needed.

Index Cache

Data Cache

Paging

Physical disk

Memory

Index pages

ESS.IND

Data blocks

ESS.PAG

Factors Affecting Cache Sizing



Database size



Block size



Index size



Available memory



Data distribution



Sparse / dense configuration



Needs of database (e.g. complexity of calculations)

Priority for Memory Allocation

1.

Index Cache

2.

Data File Cache

3.

Data Cache

Guideline for Index Cache



Default



Buffered I/O: 1024 KB (1048576 bytes)



Direct I/O: 10240 KB (10485760 bytes)



Guideline:



Combined size of all essn.ind files, if possible;

otherwise, as large as possible



Do not set this cache size higher than the total

index size, as no performance improvement

results

Guideline for Data Cache

 Default



3072 KB (3145728 bytes)

 Guideline



0.125 * Combined size of all essn.pag

files, if possible; otherwise as large as

possible

Guideline for Data Cache



Increase value if any of these conditions

exist:

 Many concurrent users are accessing different data

blocks

 Calculation scripts contain functions on sparse ranges,

and the functions require all members of a range to be in memory (for example, when using @RANK and

@RANGE)

 For data load, the number of threads specified by the

DLTHREADSWRITE setting is very high and the expanded block size is large

The Exception to the Rule

 Index Cache



Last index page into RAM, next out of RAM

as cache is filled



Default is 1024



Generally, set to hold index in RAM

The Exception to the Rule

 Data Cache



Last block into RAM, next out of RAM as

caches are filled



Default is 3072



Uncompresses block in RAM (using more

data cache)

The Exception to the Rule



On cache (especially index) In older versions, there

was a point of diminishing returns after about 50-55

percent of the index was in memory; while it could

help, the ROI was minimal compared with up to 55%

The Exception to the Rule



If you have limited resources for many applications

and not enough memory to go around, try one of the

following guideline for the data cache:



Uncompressed Block Size X 200



# of Data Block writes X Uncompressed Block

Size



# of Commit Blocks X Uncompressed Block Size



# of Lock Blocks X Uncompressed Block Size

C

o

p

y

ri

g

h

t

©

2

0

0

7

i

n

t

e

r

R

e

l

C

o

n

s

u

lt

i

n

g

p

r

e

s

e

n

t

a

ti

o

n

.

A

ll

ri

g

h

t

s

r

e

s

e

r

v

e

d

.

The Exception to the Rule



If you have limited resources for many applications and

not enough memory to go around, try the following

guideline for the index cache:

1.

Set the initial cache setting to 20% of the size of the

index file

2.

Run a consolidation / calculation and check the value of

the hit ratio

3.

If the hit ratio exceeds 90%, then the cache is fully

tuned

4.

If the hit ratio is lower than 90%, increase the cache

setting to 30% of the index file size and calculate again

5.

Repeat this process until the hit ratio is at 90% or higher

C

o

p

y

ri

g

h

t

©

2

0

0

7

i

n

t

e

r

R

e

l

C

o

n

s

u

lt

i

n

g

p

r

e

s

e

n

t

a

ti

o

n

.

A

ll

ri

g

h

t

s

r

e

s

e

r

v

e

d

.

Always build calculations into the

Essbase database

Push Calculations to Essbase



Reporting tools have an expansive lists of formula and

calculation capabilities



But… this means processing on the Reports server



If you can, push calculations back to the Essbase server



Benefits

 Faster

 Standard formula across all reports accessible to users

 (Data integrity)

 Central place for business rule / calculation logic

The Exception to the Rule



Simple ratio or variance calculations that do not

calculate correctly in Essbase due to:



Attribute dimensions



Other calculation dependencies



Infrequent calculations

Avoid repeating members in a

dimension

Avoid repeating members in a dimension

 Repeating indicates a need to split dimensions

 Splitting dimensions reduces outline redundancy

The Exception to the Rule



Be careful! The design principle will create a bigger

database (exponentially)



E.g. If you split Accounts into Accounts and Metrics and

both dimensions are dense, your block size increase

significantly

Account Time

Account

The Exception to the Exception

 Creating alternate time rollups like YTD Jan, YTD Feb are good

examples of when you may want to split repeating members into a separate dimension

 If you are doing calculations where you are trying to specify ranges of periods/year (rolling averages for example) the extra time periods mess it up

 If you have alternate time rollups like YTD Jan, YTD Feb, this

may be better designed as an analytic dimension – especially in ASO

 Add a new “view” dimension (dynamic)

Another Exception to the Exception



In dense dimensions unary operations are faster

than any other type of calculation



So for ratios, use shared members with the proper

sign to perform the calculation



Not only does it make calculation faster, it also

allows the user to see what made up the formula

Keep in Mind: Tuning

 It is ok to break the rules

 There isn’t one right answer

 Tuning

 Tuning guidelines can contradict other tuning guidelines

 Tuning for calculations vs. tuning for retrievals

 In some databases, these tuning tips will have significant

impact. In other databases, the tuning tips won’t

The Exception to the Rule: Essbase

Design Principles That Don’t Always

Apply

Edward Roske, CEO

Oracle ACE Director

[email protected]

BLOG: LookSmarter.blogspot.com

WEBSITE: www.interrel.com