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.

Tuesday, December 2, 2008

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.

1 comment:

King said...

Hello Tracy,
You mentioned after the compression topic that,
Accounts/Measures dimension should be first in the outline.
But, after the sample table, you wrote Time has to be the first dimension.
Did I interpret in incorrectly. Please explain..
Thank You