ASO Planning – Do this, not that

Cameron Lackpour
Tim German
Dan Pressman
 Join the EPM Community at tonight’s

The EPM Carnival! 8 - 10 PM – Room 603

sponsored by:

Featuring:
The Quest – Are you up to the challenge?
Balloon Dart EPM Trivia
Ace Director Bean Bag Toss
Great Food and Flashy Prizes
Developing Essbase Applications
● Like the best, most advanced
Essbase conference there ever
could be
● Advanced content
● Good practices
● Written by some of the most well
known Essbase developers
● Source code at
www.developingessbasebook.com
● You should buy it
You’re Invited!
Join us November 4 & 5, 2014
Sheraton Imperial Hotel & Convention Center
Raleigh/Durham, North Carolina for the

East Coast Oracle Users Conference

CALL FOR PRESENTATIONS (Deadline July 14, 2014):
Share your knowledge by presenting at the 2014 conference. Presentations may be targeted
to novice, intermediate, advanced, or all attendees and are scheduled for 60 minutes.

VENDOR OPPORTUNITIES (contact Melissa@eastcoastoracle.org)

Plan now to attend ECO 2014 – one of the largest and most
economical Oracle training opportunities on the East Coast.

For details, to submit an abstract, or register visit www.eastcoastoracle.org.

4
The shoulders of giants or Dear God, didn’t
they do any of this themselves?
 Performance Architects
● Chuck Persky, Jonathan Etkin, Tyler Feddersen
 Ernst & Young
● Martin Slack
 interRel
● Glenn Schwartzberg
 James & Monroe
● Richard Phillipson
 Hackett Group
● Joe Watkins
 nTuple
● Dan Pressman
 Oracle
● Sree Menon
● Kim Reeve
What this presentation is about
 General features of ASO Plan Types
 Good ASO Planning practices
● How to implement ASO Plan Types within your
application
● Pros and cons of ASO Plan Types
● How to design Plan Types to take advantage of
ASO
 Performance findings versus BSO
What this presentation is not about
 ASO reporting databases from Planning
 The latest and greatest features of Planning
11.1.2.3.500
● Except where they intersect with ASO Planning
 General BSO Planning good practices
● Again except where they intersect with ASO Planning
 Why you should be using Hybrid BSO instead of
ASO
● Simply because Hybrid BSO is not yet ready to take on
that role
 Public Sector Planning and Budgeting (PSPB)
Can we do all of this in 55 minutes?
 Preparing for ASO Planning
 Working in ASO Planning
● Mechanics of working in the tool
● Limitations
● Administrative performance
 ASO Planning good practices
● Dimension design
● DTS
● Fx
● Allocations
 Sizing and performance
 Q&A
To optimize the whole you must suboptimize
the parts
 ASO Essbase optimization may not be the same as
ASO Planning optimization
 Balance ASO design versus:
● Maintenance within a Hyperion Planning context
● Complexity
● Likely small database size by ASO standards
 Planning imposes design and functionality
compromises on both BSO and ASO
 We will give you warnings, guidelines, and
techniques
Why use ASO Planning?
 Instant aggregations
 Planning applications that are “too big” for Planning
● Dimensions
● Data
● Speed
 It is the default engine for Essbase development
● Most new BSO databases today are the result of
Planning
● Everything else is (mostly) ASO
 Hybrid isn’t ready for Planning
● Come to our Wednesday session at 11:15 am for more
A quick note about Hybrid Essbase
 Hybrid Essbase is BSO with sort-of ASO
● “60% ASO, 40% something else”
 It seems like a really good fit for Planning
 However…
● It is not currently certified with Planning
● The 11.1.2.3.501 release is severely limited
● Real world experience with performance, capacity, and
defects is practically nil
● Come to the Wednesday “Evolution or Revolution: The
New Hybrid Essbase” session at 11:15 am
 ASO Planning is here, now, and it works
Requirements for ASO Planning
 Planning 11.1.2.3 or higher
 The correct license
● ASO Planning is not part of the standard Planning
license
 Enough RAM to keep the ASO Plan Types in
memory
● ASO is an in-memory database
● Memory requirements are different from BSO
Version and Licensing
 Planning 11.1.2.3.000 or higher
 Just because you can do it, doesn’t mean you
should
 Must purchase either:
● Hyperion Enterprise Planning Suite
● Hyperion Planning Plus and Essbase Enterprise
Performance Management Foundation
 Cost?
● Talk to your friendly neighborhood Oracle salesman
How is ASO integrated into Planning?

 It’s just Planning

 Plan Types can be created as ASO
● Separate application is created given engine
difference
 Dimension, security, form, metadata and data
load, etc. functionality the same as BSO
 Dimensions can be shared across types of
Essbase engines
 Interface is still BSO-centric
BSO dimension settings do not apply

 Dimension order can be changed

 No facility for changing ASO dimension types
Change hierarchy types within member
properties
Required dimensions aren’t
 Still required in BSO, but not ASO
Full support for all data types

● Data types
● Text
● Date
● SmartList
● Supporting
Details
● Attachments
● Comments
An ASO Plan Type in Essbase

 Accounts – Dynamic, Compression

 Period, Scenario, Version – Multiple Hierarchies
 Year, Entity* – Stored
 These are unchangeable

*Entity becomes Multiple Hierarchies when it has members

 Limitations from the documentation
 Classic only, no EPMA
 11.1.2.3.000
● Calculation Manager not supported (not such a big deal TBD)
● No member formulas via Planning
● 11.1.2.3.500 supports Calculation Manager and member formulas
 No XREFs into ASO Plan Types (but can have them from
ASO Plan Types)
 “Required” dimensions not required, so approvals may or
may not work
 DTS not supported (this is an ASO Essbase limitation, not
Planning)
 You must be licensed for ASO Essbase
 Filters NOT supported in ASO Plan Type
Other known limitations
 ASO Plan Types are not Essbase data connections
● Cannot be accessed via Essbase/Smart View
● Can be accessed via Planning/Smart View
● No attributes are viewable
● ASO does attributes much better than BSO
● Why? Ask Shankar Viswanathan (Planning product manager)
at the Thursday Planning Deep Dive
 FDMEE cannot write to an ASO Planning Plan
Type
● Can write to ASO Essbase but then no drillback from
Planning forms
Papercuts
 Dimension density assignments make little sense
 Member formulas not available till 11.1.2.3.500
● Use HSP_UDF UDAs in EAS to assign
 Solve order depends on selecting correct Plan Type
 Easy to enter MDX into BSO formulas
● Ensure you have selected the proper Plan Type
 Validations often do not happen until the refresh
 No fx in multicurrency applications
● We have a fix for that
 Forms show BSO options in ASO forms
 Data copy/Copy Version do not work
Member Formulas
 11.1.2.3.000
● Not supported in Planning
● Use HSP_UDF (shades of Planning 2.1) and write it
in EAS
 11.1.2.3.500
● Accounts dimension or dynamic hierarchies only
● Supported in Classic dimension editor
Solve Order
This image cannot currently be display ed.

● Available in Member
Formula
● Only valid for ASO
Plan Types
Must pick the right Plan Type
Weak validations
No Planning fx

BSO Plan Type ASO Plan Type

• Must roll your own fx functionality

Not quite there, dimension metadata

Currency, Entity, and

Custom dimensions
Not quite there, forms
Not quite there, copy data

Where’s T3_ASO?
Did the data get copied?

BSO: Yes

ASO: No
Creating an ASO Plan Type
 Classic only
 BSO n + 1 number Plan Types
 Must have at least one BSO Plan Type
 ASO PT can be named the same as BSO, don’t
do this
 Outlineload.cmd/menu for metadata and data
 Native Essbase data loads faster
Directories
 Separate applications for ASO Plan Types
 Name with similar prefix to group in folders and
EAS
ASO Planning administrative considerations

 Planning administration performance is not

improved from BSO
 You will still need to use MaxL to perform ASO-
specific maintenance
● Merging slices
● Outline compacts
● Buffers
Administrative performance
 Loading large dimensions can be slow
 Deleting large dimensions is even slower
 More extant members = slower load/delete
performance
Dimension Records Load Time Delete Time
PostCode 47,863 13:45 45:00
Product 72,177 34:28 3:03:00

 Maintenance windows will change with the ability to

load large dimensions
● Performance that was tolerable for smaller BSO
dimensions, isn’t
Refresh considerations
 Application refreshes touch all Plan Types
● All dimensions are rewritten
● Outline (not data) restructures can be slow with large
dimensions
● Data restructures are even slower
 Refreshes are serial
● Plan Type add order
Outline restructure considerations
 Dimensions are shared across Plan Types
 What is minor in BSO is major in ASO and vice
versa
 You must consider both engines when
restructuring
 Rules for restructure are completely different
 Your processes may impact both engines
 Common restructures
Type Function BSO Cost ASO Cost

Outline Add, remove, rename, Small, unless dense High, clears aggregate
reorder members restructure triggered views, full outline
restructure; 3x .dat file
requirements, aggregations
must be rerun
Add member Add member as last child Same as previous if Light outline restructure
without crossing a power of applicable
2 boundary; change some
properties in dynamic
hierarchies

Attribute dimension add, Add, remove, reorder, Small, rewrite of outline file Medium, rebuilds aggregate
delete, modify assign to base of attribute views, 3x .dat file
dimension members requirements
 Unique restructures
Type Function BSO Cost ASO Cost

Add member Add member and cross N/A High, clears aggregate
power of 2 boundary views, full outline
restructure

Dense/Sparse Add, remove, reorder dense High, rewrite of every block N/A
dimension members/Add, in database, increased
remove, reorder sparse fragmentation, index
dimension members restructure/Low, outline
restructure, index
restructure
Hierarchy type change Add, delete, move, modify N/A High, clears aggregate
hierarchy type; change # of views, full outline
stored levels; change top restructure; 3x .dat file
member of hierarchy requirements, aggregations
storage; dynamic <-> must be rerun
stored; break match with
primary/alternate hiearchy
YTD in Planning
● BSO supports D-T-S,
ASO does not
● Common BSO practice of
stacked or shared YTD
works
● Dynamic, dense, fast,
supports Time Balance
● Works in ASO as well
● Dynamic or stored
hierarchies in Multiple
Hierarchies dimension
● Fast
● There are several catches
 Time Balance/Period catch #1
● Stored hierarchies with
shared members is the
norm in ASO

● Planning won’t permit it

Time Balance/Period, catch 22

● So try it as dynamic
● Unpossible!
● What to do?
YTD cannot be in BSO yet not in ASO

 Many dimensions allow selecting/deselecting

Plan Types
 Period is not one of those dimensions
Several choices
 Abandon Time Balance altogether
● Keep stacked or shared YTD hierarchies
● Use an Analytic dimension in ASO and BSO to do Time Balance
based on Equity, Asset, or other UDAs
● But what about BSO?
 Create mirror Accounts and Period in ASO Plan Types
● BSO uses either D-T-S or stacked YTD/QTD hierarchies
● ASO rolls its own
● Additional maintenance requirement
● Possibly better performance
 Abandon stacked or shared YTD
● BSO uses D-T-S
● ASO uses Analytic dimension with Aggregated QTD/YTD
● Simplest approach
You must create an Analytic dimension

 MDX Aggregate approach

● Preserves Accounts dimension functionality
● Lowest impact on BSO Plan Types
● PeriodsToDate function will trigger MDX optimization
in 11.1.2.3.50x
 Optimal for Planning, suboptimal (perhaps) for
ASO
MDX Aggregate Analytic dimension

● Aggregate will sum

and respect Time
Gen 2
Balance
● Performance Gen 3
boosted in 11.1.2.3
● Analytic
● MTD
● QTD
● YTD
Essbase optimization, Planning
suboptimization
 Stored hierarchies in ASO are faster than dynamic
hierarchies
● But
● All values are “+”
● Signflip on retrieve makes it all work

 Optimal for Essbase, suboptimal for Planning

● Planners are inputting data
● Are they going to flip signs when they type?
A note about the Account dimension

 For Planning purposes, a dimension tagged as

Accounts makes sense, usually
● Dynamic
● Compression
● Supports Time Balance, member formulas, all unary
operators
 Optimal for Planning, suboptimal for Essbase
● Acceptable performance until it gets too big
● Will a Planning Plan Type ever get there?
Custom Period and Account
considerations
 Accounts
● Will likely end up with a Dynamic hierarchy anyway
● An alternate dimension loses all of the Account-y things that
make Planning Accounts so powerful
● Time Balance
● Could be worked round with custom calculations
 Period
● Open/closed periods by Scenario
● Member security, but very limited because of a lack of ANDs
 Both
● Shared metadata
● Data movement across dissimilar dimensions
● Optimal for Essbase, suboptimal for Planning
Business Rules
 Just like BSO, ASO has Business Rules as well
 None in 11.2.3.000
● Two choices
● Do it all in the outline
● Figure out another way to perform procedural ASO
calculations/allocations
 Available in 11.1.2.3.500
● Looks just like Essbase ASO business rules
● Graphical only, if you color within the lines
Graphical Business Rules
 Three objects
● Point of View
● Allocation
● Formula
 ASO procedural calculations are for level zero
only; can read upper level results
 Do other logic in the outline
Formula calculation with a twist

● POV must always

be at level 0
● Calc Mgr variables
can drive selections
from Planning
● Nest POV objects
● Formula at the
center
● Very BSO-ish
looking
The problem with formula calculation
 Unlike BSO, ASO considers all possible level 0
member combinations
● All of them
● And then only values where there is data
● This is slow, very slow
● There is no way to avoid this in a procedural calculation
 How slow?
● Test T3 database with rates and 1 data value took 6,000
seconds to calculate
● Same database and data in BSO took less than 0.025
seconds
 What to do?
Joe Watkins’ genius
 Use a member formula to drive NONEMPTY
 Use a procedural allocation to copy the
member formula value to a stored member
● Yes, you can do that through a 1-to-1 spread
allocation
 You (or Joe) have just solved the NONEMPTY
cell issue
 This is fast
 Applies to all level zero calculations
Member formula is straightforward
NONEMPTYTUPLE
to only address
existing data

CASE
statement to
calculate USD

Member with
formula in
Analytic
dimension
How does it work in Essbase?
MaxL

“FIX”

Source with “cross dim” aka tuple

Target tuple 100% allocated to USD

Execute
Note the MaxL
parameter variables
What not do this via a Calculation
Manager allocation?

A level zero
descendant of
a level zero
member is
impossible
If only we could execute MaxL from
Planning
 ASO objects do not support this, but BSO does
● Calc Mgr MaxL functions in Essbase
● @CalcMgrExecuteEncryptMaxLFile (privateKey, maxlFileName,
arguments, asynchronous)
● @CalcMgrExecuteMaxLEnScript (privateKey, maxlScripts,
arguments, asynchronous)
● @CalcMgrExecuteMaxLFile (user, password, maxlFileName,
arguments, asynchronous)
● @CalcMgrExecuteMaxLFile (user, password, maxlFileName,
arguments)
● @CalcMgrExecuteMaxLScript (user, password, maxlScripts,
arguments, asynchronous)
● @CalcMgrExecuteMaxLScript (user, password, maxlScript,
arguments)
● RUNJAVA
Interrogate essfunc.xml for more CDF
info
C:\Oracle\Middleware\user_projects\epmsystem1\Essba
seServer\essbaseserver1\java\essfunc.xml
Run MaxL from BSO Business Rules
 Run a BSO Business Rule from an ASO form on save
● Variables get passed
● Run on Save works
 BSO Calc Mgr functions then execute the MaxL
● @CalcMgrExecute or RUNJAVA
● Can run from any BSO database
● MaxL can address any BSO or ASO database
@CalcMgrExecuteEncryptMaxLFile syntax

 Formula, place within single member FIX

 Pass public key only
 Use forward slashes for MaxL script path
 Must enclose member names in @NAME
 All MaxL parameters must be enclosed in @LIST
RUNJAVA syntax

 No FIX or member formula required

 -D to decrypt
 Pass public key and redundant encrypted username and
password
 Server but not application or data name
 Variables from ASO forms can be passed to BSO Calc Mgr
rules
● This is the key to working with Planning
Allocation and fx performance
 Like for like database, data (one year), and processes
 Simple BSO and ASO allocation using Sales as basis
across Product
 In-built BSO fx, custom ASO as per previous slides
 All time in seconds and yes it really is that fast
Process BSO ASO X fast
Allocate 106 3 35
Fx 400 1.2 333
Aggregate 1,772 N/A N/A
Total 2278 4.2 542

 And that is why ASO Planning is worth a look

Form performance slower than BSO?
 Forms with upper level members potentially
more expensive
 Have you ever wondered what Suppress
missing blocks really does?
● It’s BSO magic
● Uses the undocumented MDX
NONEMPTYBLOCK command
● Filters data at the block level before it ever hits
Planning
Form performance numbers
Test case Time Behavior

BSO with suppress 0.034 seconds Returns non missing data

blocks
ASO with suppress rows Never comes back Eats 16 GB of RAM,
crashes Planning
3 billion
potential,
BSO with suppress rows Never comes back Eats 16 GB of RAM,
21 actual
crashes Planning
rows
 How big is it in Essbase?
Level 0 storage (GB) # level 0 blocks # level 0 cells
14 474,300
15 500,000 1,000,000,000
400,000 800,000,000
10 8.5
300,000 600,000,000
200,000 400,000,000
5
100,000 200,000,000
0 0 0
BSO ASO BSO ASO

Upper level data size Load time (secs) Agg time (secs)
(GB) 10,000 8,061 20,000 17,962
8,000 15,000
300
200 6,000
200 10,000
4,000
100 2,000 1,222 5,000
0 0 0
BSO BSO ASO BSO
Single threaded allocate, fx, and
aggregate performance
Single Year Six years
2500 12,000

2000 10,000
8,000
1500
6,000
1000
4,000
500 2,000
0 -
BSO ASO BSO ASO
Aggregation 1,772 Aggregation 9,313
Fx 400 1.2 Fx 1,046 5.6
Allocation 106 3 Allocation 404 14

 Single year
● 35 times faster allocation, 333 times faster fx, 542 times faster overall
 Multiple year
● 29 times faster allocation, 187 times faster fx, 550 times faster overall
ASO fx performance using execute
calculation
2000

● ASO scaled well 1800

1600
● 1,300 seconds to 1400
1200
input, allocate, and 1000
fx a very large 800
600
database 400

● 5x simultaneous 200
0
threads only 16% 1-way x 50 Load / FX
ASO w/ Regular Attribute
1299
ASO w/ Stored Attribute
1474

slower 5-way x 10 Load / FX 1513 1738

Query performance, or choose when to
suffer
Top of house, no attribute Both sparse in POV
30 80
25 70
Time in seconds

Time in seconds
60
20 50
15 40
10 30
20
5 10
0 0
ASO BSO ASO BSO
Second Pass 12.683 0 Second Pass 33.025 0.016
First Pass 14.992 0.187 First Pass 35.274 0.281

Product in grid Postcode in grid

2 2
Time in seconds

Time in seconds
1.5 1.5

1 1

0.5 0.5

0 0
ASO BSO ASO BSO
Second Pass 0.421 0.031 Second Pass 0.874 0.031
First Pass 1.327 0.032 First Pass 0.889 0.048
Considerations
 Interface is still tied to BSO in many ways
 Calculation complexity
● Member formulas, procedural calculations, mix of BSO and
ASO Calc Mgr rules
 Lack of Essbase data connection
 No EPMA
 Easy integration out of ASO via @XREF but no
dynamic way in
 Planning administration performance does not scale
well
● This is ASO Planning, not ASO Essbase
 Refresh complexity
 Hybrid is coming, but unknown
Is ASO Planning right for you?
 ASO Planning is an exciting new option for
budgeting and forecast
● “Too big” databases become easy
● Instant aggregations
● Fast performance
 Limitations as noted
 Conversions
● Performance should drive the decision
● Complex calculations may require extensive redesign
 New applications
● Calculation design factors
● Consider Planning vs. Essbase requirements
 As always, start simple
 Q&A

Cameron Lackpour Tim German

cameron@clsolve.com tim.german@qubix.com
http://camerons-blog-for-essbase- http://www.cubecoder.com/
hackers.blogspot.com/
@CameronLackpour