How do I learn about Hyperion / Oracle EPM System?

This posting is for those folks new to Essbase/Hyperion / Oracle EPM System. You're just starting out... where to go?

Where to start in Oracle EPM System / Hyperion… Training is the best place to start when learning Oracle EPM System. For Planning or Essbase, start with Essbase. Planning data is stored in Essbase (so does the new Hyperion Profitability & Cost Management solution and other upcoming tools) so learning the fundamentals of Essbase is key. I definitely recommend attending interRel or Oracle training classes.

Attend the conferences – Collaborate 2009 (May) will host a number of presentations on Oracle EPM System with a focus for the business users. Kaleidoscope 2009 (June) will provide an extensive track on Oracle EPM System with a focus for the administrators and developers. Last year Kaleidoscope was essentially a 4 day advanced Essbase training course (limited marketing, main content focus) and I expect this year to be even better.

If training isn’t possible with your budget, find live / recorded webcasts and literature. Shameless but helpful plug: interRel offers FREE webcasts on Oracle EPM System / Hyperion every Thursday at 1pm CST. Oracle and other partners host webcasts as well. How do you find about them? Join a user group like ODTUG, OAUG, IOUG, or local user groups.

Related to available reading…We’ve also written two books on Essbase: Look Smarter Than You Are with Essbase System 9 that will teach you Essbase from start to finish, building an Essbase database, managing the database, and analyzing data in the database and for end users, we have written Look Smarter Than You Are with Essbase: An End User’s Guide. Coming early next year we will have the 11x Essbase books out along with a Hyperion Planning series. I would love to recommend some other books but as of now there aren’t any other Essbase/Hyperion specific books.

You can also get some good information in the technical documentation as well – http://www.oracle.com/technology/documentation/epm.html.

Definitely join the Users forum - http://forums.oracle.com/forums/category.jspa?categoryID=16

Blogs are a great way to learn and find information… My favorite blogs for Hyperion / Oracle EPM System:
http://www.looksmarter.blogspot.com/
http://www.looksmarterthanyouare2.blogspot.com/
http://glennschwartzbergs-essbase-blog.blogspot.com/
http://essbaselabs.blogspot.com/
http://john-goodwin.blogspot.com/
http://timtows-hyperion-blog.blogspot.com/

Download and install the applications and begin to play with them. Make sure to read the installation documentation because order is important as you install / configure the components. Most of the products have sample applications that you can utilize to learn about the various features.

And finally (last shameless plug), give your friendly consultants a call. I personally recommend interRel Consulting. :-)

Outline Order in BSO Databases

So what should the outline order be for your BSO databases? Many of you Essbase experts have heard this before but just in case…

Outline ordering (and in general Essbase tuning) is not an exact science. But I recommend you start here and then tune / test …

1. Largest Dense Dimensions
2. Smallest Dense Dimensions
3. Smallest Aggregating Sparse Dimensions
4. Largest Aggregating Sparse Dimensions
5. Non-aggregating Sparse Dimensions

Dense dimensions - define the data block and must reside at the top of the outline

Aggregating Sparse dimensions - dimensions that will be calculated to create new parent values

Non-Aggregating Sparse dimensions - dimensions that organizes the data into logical slices

• Placing these dimensions as the first Sparse dimensions positions them to be the first dimensions included in the calculator cache
• Data is often times more dispersed within the database
• Example - Scenario, Year or Version

Here’s an dimension ordering example based on member count:

Dimension	Type-Size
Accounts	D – 94
Time Periods	D – 21
Metrics (Hrs, AHR, $)	D – 14
Scenarios	AS – 9
Job Code	AS – 1,524
Organization	AS – 2,304
Versions	NAS – 7
Years	NAS – 7

Here’s an example based on dimension density (Ordering the dense dimensions from most dense to least dense maximizes the clustering of thedata):

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 do I find the density of each dimension?

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<> Statistics

Another consideration for outline ordering is your compression type. One compression type RLE (Run Length Encoding) is a good compression type when your data has many zeros or often repeats (found in our budgeting applications. RLE will evaluate and use RLE, Bitmap or IVP for compression. So how does outline ordering impact compression? The first dense dimension determines your “columns” in PAG file. Compression will take place from left to right, top to bottom.

The standard method is to place Accounts / Measures first in every outline. If we were to do that, the .pag file would look like the following (simplified version):

BUDGET	Sales	COGS	Margin	Exp.	Profit
January	100	50	50	30	20
February	100	50	50	30	20
March	100	50	50	30	20
April	120	50	70	30	40
May	120	50	70	30	40
June	120	50	70	30	40

But if we move Time to first dense dimension and we get the following (notice repeating values):

BUDGET	Jan	Feb	Mar	Apr	May	Jun
Sales	100	100	100	120	120	120
COGS	50	50	50	50	50	50
Margin	50	50	50	70	70	70
Exp.	30	30	30	30	30	30
Profit	20	20	20	40	40	40

So… Time should be dense and listed first in the outlinewhen using RLE compression.

ASO vs. BSO?

A common question that I get - when should I implement an ASO database vs. a BSO database? First, let's view some similarities between the two...

	BSO	ASO
Build dimensions	Y	Y
Load data	Y	Y
Write back to level 0	Y	Y
Retrieve data	Y	Y
Partitioning / Sharing Data	Y	Y (target or source in 11x)

Key differences ...

	BSO	ASO
Unlimited dimensions and members	N	Y
Write back to any level	Y	N
Advanced calculation engine	Y	N
Calc scripts vs. MDX formulas	Y	N
Partitioning / Sharing Data	Y	Limited – pre-11x

In short, use BSO when you need to write back to any level or perform complex calculations. Use ASO for the larger, aggregation focused databases with many dimensions and many members.

In the 11 world where I can now make an ASO database the source of a partition, I can take advantage of the BSO strengths (write back to any level, powerful calculation engine) and then source this information to a consolidated ASO database that maybe has the volumes of detail from other sources. (Note - the new Hyperion Profitability and Cost Management solution uses this model: BSO for allocation calcs and loads to an ASO cube for reporting).

Check out the DBAG for detailed comparison tables on ASO vs. BSO.