Why this blog?

Finding information from Oracle/Hyperion can be difficult as you search the thousands of pages of documentation. So I'm creating this blog with all of my tables and matrices for the Oracle Hyperion products. Questions like - How does Smart View compare to the Excel add-in? When should I use Financial Reporting or Web Analysis? I thought I would share this information with you in the simple format of tables and maybe a few bullet points. So no lengthy paragraphs on this blog... but hopefully some helpful Oracle Hyperion information.

Sunday, April 10, 2011

New Book Released on Essbase Studio

Wow... it has been a REALLY long time since I've posted a entry to this blog (where has the time gone?). Though I certainly have a great reason to post news today! Oracle ACE Director and fellow interRel employee, Glenn Schwartzberg has written the new edition the Look Smart Than You Are series, Look Smarter Than You Are with Essbase Studio. Glenn has done a great job and I recommend this book to all Essbase Administrators. You can find the book here on Lulu (http://www.lulu.com/product/paperback/look-smarter-than-you-are-with-essbase-studio/15261669 ).

Tuesday, December 2, 2008

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.

Sunday, October 12, 2008

SOX Report Card - Enterprise vs. Financial Management

I can't take credit for this table - I came across this in a Hyperion presentation and it is pretty helpful in making the case to switch to Financial Management.

Financial Management

Enterprise

Single version of the truth

A

A

Timeliness / Speed of close

A

A

Adjustment capabilities

A

A

Workflow

A

C

Audit functionality

A

C

Documentation

A

F

Internal controls / email alerting

A

F

Enterprise vs. Financial Management

Still on Enterprise and wondering if it is time to make the jump to Financial Management? Enterprise is still a solid product but the table below will help you understand some of the benefits of the FM solution:

Hyperion Financial Management

Hyperion Enterprise

Unify financial and operating results

Consolidation and reporting (regulatory filings)

12 Smart dimensions

4 Fixed dimensions

Scalable to thousands of users

Client / server architecture – limit to 150 users

Web enabled

Microsoft Windows architected – distributed deployment – some web

24 by 7 Availability

Offline maintenance and backup required

Easy to customize Web User interface

HTML programming required for customized user interface

Hyperion Financial Management

Hyperion Enterprise

Drag and drop, oriented reporting with Financial Reporting

Script based reporting

Adhoc analysis with drill down capabilities

Not available

Process Workflow

Not available

Robust controls and audit trails

Some controls and audit trails

Integration with Hyperion Planning

Not available

Shared security maintenance of users and groups across Hyperion products

Not available

UFC: Web Analysis vs. Interactive Reporting

Pretty exciting matchup, right? At a high level review, you'll note that Web Analysis and Interactive Reporting overlap on a number of key features. So which tool is the right tool? Historically, IR was good at relational reporting and analysis. WA was good at Essbase reporting and analysis. With version 9.3.1, Interactive Reporting introduced a new CubeQuery feature that now allows it to better report against Essbase (prior to 9.3.1, there were many frustrations when trying to use IR with Essbase). CubeQuery closely models the functionality that is provided with Web Analysis. What is really cool? You can now easily combine Essbase and relational data very easily in IR dashboards and reports.

Let's look in a bit more detail, comparing the advanced analytic capabilities between IR CubeQuery and WA:


WA Studio

WA Workspace

IR Studio

IR Web Client

IR HTML

Basic Drilling

Yes

Yes

Yes

Yes

Yes

Swap Rows and Columns

Yes

Yes

Yes

Yes

No

Client install

Required first the first time

No install necessary

Client application

Required the first time

No install necessary

Adhoc Query

Yes

Yes

Yes

Yes

No

Member Selection

High

High

Medium

Medium

None

Traffic Lighting

Yes

Yes

Yes – Called Spotlighter

Yes – Called Spotlighter

No

Formatting / Presentation

Yes

Yes

Yes

Yes

No

Save Personal Queries

Yes

Yes

Yes

Yes

No




Next let's compare the dashboarding capabilities between the two:


Web Analysis

Interactive Reporting

Complexity to Create

Medium

Medium - Complex

Requires Programming

None

Yes – Java scripting

Flexibility

High

Very high

Drillable within the Dashboard (Essbase sources)

Yes

No (but in future versions)

Present Essbase Data with Relational Data

Yes – but not well

Yes

Data Controls

Yes

Yes

Risk (when using Essbase as data source)

Low

Medium-High (new)

Time to Implement (when using Essbase as a data source)

Lower

Higher (new)




So in summary, what is the best tool?

It depends…

  • Technically you could use both but …
  • IR requires Java Scripting knowledge for creating and maintaining dashboards
  • Learning curve for both products (similar concepts but steps and design considerations are completely different)

  • Web Analysis if you are only reporting on Essbase
  • Interactive Reporting if you report against relational sources
  • Interactive Reporting if you report against Essbase and relational sources in the same report or dashboard with considerations*
    *Java scripting
    *New (available in 9.3.1); Some fixes and enhancements in 11.1.1
  • Web Analysis if you don’t have any technical resources available / programmers (Web Analysis is easier to learn for administrators and no Java Scripting or “coding” required)
  • From an basic end user perspective using Essbase, IR and WA have the same functionality for the most part (there may be a few more features in WA)
    - Keep only, remove only
    - Navigation is different between the 2 tools – training consideration
  • From a power end user perspective, Web Analysis provides more adhoc advanced analytic features against Essbase