The Internal Workings of Essbase: BSO and ASO Secrets Revealed

Edward Roske eroske@interrel.com BLOG: LookSmarter.blogspot.com WEBSITE: www.interrel.com TWITTER: ERoske

Disclaimer
These slides represent the work and opinions of the presenter and do not constitute official positions of Oracle or any other organization. This material has not been peer reviewed and is presented here with the permission of the presenter. This material should not be reproduced without the written permission of interRel Consulting.

We will send you a copy of the slides shortly after the presentation.

About interRel
2008 Oracle Titan Award winner - EPM Solution of the year 18 presentations at Collaborate 2009, 14 presentations at Kaleidoscope, 6 at OpenWorld 2008 2008 Oracle Excellence Award winner with Pearson Education One of the fastest growing companies in the world (Inc. Mag., 08) We have two of the three Hyperion Oracle ACE Directors in the world Founding Hyperion Platinum Partner; now Oracle Certified Partner Focused exclusively on Oracle Hyperion EPM software Consulting Training Infrastructure and Installation Support Software sales
3

 5 Hyperion Books Available:

Essbase (7): Complete Guide Essbase System 9: Complete Guide Essbase System 9: End User Guide Smart View 11: End User Guide Essbase 11: Admin Guide

eBooks available on Amazon Kindle

Coming Soon
Hyperion Planning for End Users (September) Hyperion Planning for Admins To order, check out www.lulu.com

Copyright 2007, Hyperion. All rights reserved.

Who is this Edward Roske guy, anyway?

Began Essbase consulting in 1995 One of the first Essbase certified consultants in the world Founded interRel Consulting in 1997 to focus on Hyperion Speaker at annual Hyperion user conferences since 1996 President of ODTUG Hyperion SIG Essbase Domain Lead for OAUG Hyperion SIG Author of Look Smarter Than You Are with Hyperion Essbase and most importantly Playwright and Lyricist of Eddie and the Consultants: A System 9 Musical which performed to sold out audiences in Las Vegas and Walt Disney World in Orlando
5

Agenda
Introduction Internal Workings of Essbase: BSO Internal Workings of Essbase: ASO Question and Answer

Internal Workings of Essbase: Block Storage

Bob Earle Invented Sparse and Dense

How is data distributed?

SPARSE
Products X Locations X Accounts

DENSE
Time Periods X X X X X X X X

X
X X

X
X

X X

X X X

X X

X X X

Block Structure

Var% Var Bud Act UnitsSold Headcnt Profit Expense OtherExp Marketing Salary Rev COGs Sales Jan Feb Mar Q1 Q2 Q3 Yr

Account (dense)

Period (dense)
9

Data Cells Within the Block

Actual -> Profit -> Jan

Cross-dimensional operator (means Actual BY Profit BY Jan)

Blocks
An individual block is created for each combination of sparse stored members
Cola->East

Cola->New York

Cola->Florida

Cola->Massachusetts

11

Where do we define database?

The outline!

12

Storage and Compression

13

Storage
PAG File
Contains the data (the blocks with a header for each) Contains up to 2 Gb of data in each PAG file (32-bit) Can be 1,024 different files Can be compressed and fragmented Can be stored on multiple drive locations

IND File
Contains the list or pointers to the blocks (intersection of sparse dimensions) Contains up to 2 Gb of index in each IND file (32-bit) Can be 1,024 different files Can be fragmented Can be stored on multiple drive locations
14

Storage

15

Compression

16

Compression
Simple compression settings
None zLib Index Value Pair Cant assign directly Good for really large blocks with really sparse data

Following types use multiple compressions (one per block)

Bitmap

Good for non-repeating data Will use Bitmap or IVP

RLE = Run Length Encoding

Good for data with zeros and data that repeats (such as budgeting) Will use RLE, Bitmap, or IVP
17

Dimension Order & Compression

Dimension order affects compression First dense dimension determines your columns in PAG file Compression is from left to right, top to bottom Move Time to first dense dimension and we get:

BUDGET Sales COGS

Jan 100 50

Feb 100 50

Mar 100 50

Apr 120 50

May 120 50

Jun 120 50

Margin
Exp. Profit

50
30 20

50
30 20

50
30 20

70
30 40

70
30 40

70
30 40

Notice repeating values Time should be dense then Measures for RLE compression
18

Calculations

19

Calculation Process

Vermont -> Cola -> Actual Accounts Sales COGS Margin

Jan
42.37 82.34

Feb
38.77

Mar

Qtr1

124.71 119.43 161.93 406.07 47.28 128.42 80.66 114.65 277.65

Dense Calculation

Year Qtr1
Data load from table

After calc of Time dimension

XXXX XXXXXXX Jan ### XX Feb ### XX ### Mar XX

Sales

After calc of Accounts dimension

COGS Margin Profit

Sparse Calculation
Calculated blocks Upper-level blocks Input blocks Level zero blocks
Vermont -> Cola New York -> Cola

East -> Cola

Calculation Order: All Dimensions

First, Accounts Second, Time Third, remaining dense dimensions Fourth, remaining sparse dimensions Two Pass Calculation

Calculations
Default Calc - simplest method Calc scripts Dynamic calculations Intelligent calculation

Commit Blocks
Using Uncommitted Access
When Commit Level is reached, blocks write to hard drive

Default is 3000 blocks Setting Commit Blocks to Zero

Writes at completion of the entire transaction Will dramatically improve calculation time Will fragment your PAG file during a calculation

25

Retrievals

26

Index Cache
New requests

Index pages in index cache

Index pages on physical disk

Old requests

Memory

Disk

Data Cache
New requests

Disk blocks in data cache

Disk blocks on physical disk

Old requests

Memory

Disk

Retrievals
Sparse retrievals
Sparse dimensions down the rows or across the columns Accesses multiple indexes and multiple blocks Slower retrieval times

Dense retrievals
Dense dimensions down the rows and across the columns Accesses one index entry and one block Faster retrieval times

29

Internal Workings of Essbase: Aggregate Storage

30

BSO Limitations
Financial applications are more densely populated so BSO works great in those instances BSO engine can handle sparse data but on a limited scale Outline size limited Batch times required for loads and calcs

Aggregate Storage Option

Remember all the concepts we just learned:

Dense / Sparse Index / page files Cache settings Block storage

32

Aggregate Storage Databases

Similar to a BSO database outline, dimensions, hierarchies BUT Different method for storing/calculating databases New storage kernel built to handle ASO databases Calculates 10-100x faster Stores up to 252 dimension combinations

33

Aggregate Storage Databases

ASO addresses the following types of databases:
Read-only*
Write back to level zero available in 9.3.1

Rack and stack Large dimensions

New Types of databases are possible

Customer analysisdata is analyzed from any dimension and there are potentially millions of customers Procurement analysismany products are tracked across many vendors Logistics analysisnear real-time updates of product shipments Market Basket analysisproducts purchased along with other products

34

When to use ASO

Database is sparse and has many dimensions, and/or the dimensions have many levels of members Database is used primarily for read-only purposes, with few or no data updates Calculation of the database is frequent, is based mainly on summarizing of the data, and does not rely on calculation scripts

Starting in Essbase 11x, ASO should be your default/starting idea

35

BSO vs. ASO

BSO Outline ASO Outline

36

End User Perspective

End users wont care whether their database is ASO or BSO The way users access ASO is the same as BSO
Excel Add-in Financial Reporting, Web Analysis, etc. Smart View Add-in

Repeat no differences (just more data/dimensions)

ASO Benefits from IT Perspective

Faster load and calc times provide
Lower hardware costs Lower maintenance costs Higher availability

Small disk footprints Efficient tuning for storage and query response

How does ASO work?

Simple question with not so simple answer Asked greatest minds in the business how ASO works and got the same resounding answer: Its a black box or it's top secret and hard to understand There must be a better answer!

ASO is Designed For

More dimensions and members Less time required for batches
Fast aggregation of sparse data sets Faster loads Incremental loads

Reduction in database footprint

Key Concepts
Storage Sparse data Indexing Aggregation Nodes

ASO Storage (ROLAP in disguise)

ASO can also be said to be ROLAP with a super fancy index scheme that rules. Big difference between ASO and BSO and ROLAP is the ASO storage mechanism ROLAP stores data in a table and indexes a combination key between the rows Essbase stores the concept of a cube of data in multiple dimensions or rather multiple keys

ASO Storage (ROLAP in disguise)

Its a multidimensional index ASO takes it a step further and indexes the indexes in a way for more rapid aggregation of data Storage is no longer in "blocks" but in highly optimized aggregation nodes Visualize it as an asymmetric fractal Christmas tree flattened out and then indexed again

How does ASO work

Load Data only at level 0

Create aggregate views Algorithm selects and stores most taxing queries Dynamic queries at runtime and increased speed by leveraging nearest stored view

ASO Concepts
Concept of stored and dynamic hierarchies
Stored hierarchies can only aggregate Dynamic hierarchies can utilize formulas and advanced unary operators

Formulas on dimension members use MDX syntax Pre-aggregated views can be defined to help query performance Aggregated design wizard helps you create aggregation scripts Outline paging helps you page portions of the outline in and out of memory to assist in performance You can convert BSO outlines to ASO outlines using a wizard

Storage and Compression

46

Directory Structure
Directory structure differs in both content and purpose from BSO Tablespaces are utilized to store data and metadata
Default stores numeric data (.dat file) Log records database activity Metadata stores metadata information about the objects in the database Temp temporary working space for the Essbase kernel

Tablespace Overview
Defines the database storage in the form of file locations Each ASO application has 4 tablespaces
Default database values Temp temporary work space Log transaction log files Metadata database data structure

File location specifies a physical disk space for storing database files Each tablespace may contain one or more file locations
Can span multiple physical drives and/or logical volumes
48

Manage Tablespaces

Sizing Tablespaces
During the data load and aggregation process, data is stored in both the Temp and Default directories ASO will always build the full .dat file in the temp tablespace while the default tablespace still has the production .dat Hence, for your maximum drive size you have to plan on AT LEAST 3x your max bloated .dat size if you want to be "safe" (buffer to temp to default)

50

Compression Dimension ASO

In old releases, the Accounts dimension enabled database compression Beginning in 9.3, the Accounts dimension is the default compression dimension BUT you can choose a different compression dimension Essbase helps you choose a compression dimension by estimating what the database size would be depending on which dimension is tagged as compression Time is an excellent candidate for compression dimension, especially if you have fiscal year as a separate dimension

Calculations

52

Aggregating ASO Data

For ASO databases, after data values are loaded into the level 0 cells of an outline, the database requires no separate calculation step From any point in the database, users can retrieve and view values that are aggregated for only the current retrieval ASO databases are smaller than block storage databases, enabling quick retrieval of data values For even faster retrieval, you can precalculate data values and store the precalculated results in aggregations

Aggregations The Down Side

Lengthy process Requires extra disk space
Sometimes yes, but it is rare that default aggs are more than 40 percent the size of the input data.

You want to balance query time and storage space

Intelligent Aggregations for ASO

You can define hard restrictions for a dimension
Default (no restriction for primary hierarchy, no aggregation for alternate hierarchies) Consider all levels Do not aggregate Consider top level only (you only query top level) Never aggregate to intermediate levels (you only query level zero or top dimension)

Level based weighting provide levels to consider Process ASO considers hints when creating aggregations Attempts to create the most useful aggregations based on hints

55

Query Hints
You can define soft restrictions as a query hint Just select a representative member (any member) Essbase will take this into consideration when creating aggregation views

56

Query Hints

Design Considerations
BSO
Complex calculations and allocations Write back at upper levels from end users are required

ASO
Large analysis applications with many dimensions and members Rolling up and analyzing large volumes of data

Both ASO and BSO

Take advantage of the strengths of both database types

Consider Using Both ASO and BSO

BSO

Budget

Product SKU Analysis

Partition

ASO

Consider Using Both ASO and BSO version 11

Product SKU Analysis

ASO

BSO

Partition

Budget

ASO vs. BSO Recap

What are the similarities between ASO and BSO?
Building dimensions Loading data (level zero only for ASO) Retrieving data

What are the differences?

Many dimensions, many members No calc scripts Use MDX member formulas Aggregations for improved query performance

Lots of improvements in System 9 and version 11

Understand the limitations in early versions of Essbase ASO Dont miss the new features in 9.3.1 and 11x

61

Thank You!!
Edward Roske eroske@interrel.com BLOG: LookSmarter.blogspot.com WEBSITE: www.interrel.com TWITTER: ERoske