BISP Oracle EPM BSO Design & Statistics Advisor

Cube Outline Designer

Change a dimension between Dense and Sparse. All statistics update immediately.

Dimension	Stored Members	Type	Data Presence %	Upper-level Stored %	Action

Design objective: choose dense dimensions so that blocks are reasonably sized and well-filled, while sparse dimensions avoid creating an excessive number of actual blocks.

Population Assumptions

Sparse-combination occupancy %

Dense cell fill %

Repeating-value %

Zero-value %

Average clustering ratio

Index bytes per block (model)

Live Design Results

Dense cells per block

-

Expanded block size

-

Potential sparse blocks

-

Estimated actual blocks

-

Estimated Level-0 blocks

-

Estimated upper-level blocks

-

Estimated index pages

-

Potential-to-actual ratio

-

Recommendation Assumptions

Design objective Preferred expanded block size Maximum acceptable block increase

Penalize changes to the current outline

Design Recommendation Summary

Recommended dense dimensions

-

Recommended sparse dimensions

-

Projected block size

-

Projected potential blocks

-

Run the engine to score each dimension.

Dimension-by-Dimension Advisory

Dimension	Current	Dense score	Sparse score	Recommendation	What-if impact	Reason

Important: This is a transparent heuristic advisor. It cannot infer correlation between dimensions from member counts alone. Validate recommendations using representative data, calculations, forms, reports, and real database statistics.

Block Creation Scenario

Operation

Existing source blocks

Candidate target blocks

Non-missing result %

Targets already existing %

SET CREATEBLOCKONEQ ON

Create/copy missing targets

Block Lifecycle Visualization

Existing block Newly created Potential but absent Deleted

Blocks scanned

-

Existing targets reused

-

New blocks created

-

Blocks removed

-

Choose an operation and run the simulation.

Operation Rules Used by the Simulator

Data load / form input
A non-missing value at a new sparse combination creates a block. Missing-only records do not need a stored block.

DATACOPY
Target blocks can be created from populated source blocks. Suppressing missing-block copies reduces unnecessary targets.

AGG / CALC DIM
Stored sparse parent intersections may be created as child blocks aggregate upward.

Sparse formula
Block creation depends on calculation mode, existing blocks, equation result, and CREATEBLOCKONEQ behavior.

Dense formula
Normally calculates cells inside existing blocks; it does not independently materialize every absent sparse combination.

CLEARBLOCK
Removes qualifying blocks rather than merely writing #MISSING to individual cells.

Index Lookup Configuration

Requested sparse block number

Index page size

Data page target

Fragmentation / clustering ratio

Ratio: 0.86

Index-to-Page-File Visualizer

Index file (.ind)

→

Page file (.pag)

Sparse key

→

Index entry

→

Page-file location

→

Decompressed block

Run the lookup to visualize how a sparse key resolves to a stored data block.

Modeled index entries/page

-

Target index page

-

Target data page

-

Estimated physical reads

-

Calculation Workload

Calculation pattern

FIX selects % of blocks

Dirty blocks %

Calculation passes

Parallel threads

Block processing rate/thread/sec

I/O penalty multiplier

Use Intelligent Calculation

Calculation Performance Results

Blocks selected

-

Blocks processed

-

Estimated block passes

-

Estimated elapsed time

-

Modeled workload intensity

Configure the workload and run the estimate.

Optimization Breakdown

FIX selectivity

Pass reduction

Parallelism

Intelligent Calculation

Block creation risk

I/O exposure

Restructure Cost Inputs

Restructure type

Storage throughput (MB/sec)

CPU efficiency %

Available free disk (GB)

Safety margin %

Backup overhead %

Validation overhead %

Estimated Maintenance Impact

Data rewritten

-

Temporary disk required

-

Estimated duration

-

Disk readiness

-

Read source
-

→

Rewrite / rebuild
-

→

Validate
-

→

Backup / rollback
-

Run the estimator to evaluate disk and maintenance-window requirements.

Scenario Inputs

Custom dense multiplier Custom potential-block multiplier Custom occupancy multiplier Custom density %

Current vs Recommended vs Custom

Metric	Current	Recommended	Custom	Best Direction

Run the comparison to evaluate structural trade-offs.

Relative Workload Comparison

Database Statistics Inputs

Override actual blocks (0 = use estimate)

Reported block density %

Reported compression ratio

Use overrides to reproduce a real Essbase statistics screen. Leave zero to use the simulator model.

Statistic Analyzer

Block density

-

Compression ratio

-

Clustering ratio

-

Estimated database health

-

Storage Footprint Model

Component	Formula used by simulator	Interpretation
Expanded block space	Actual blocks × expanded block size	Logical uncompressed block footprint.
Compressed page data	Expanded space ÷ compression ratio	Directional .pag data estimate, not file-allocation overhead.
Index entries	One index entry per existing block	Logical pointers used to locate blocks.
Index model	Actual blocks × modeled bytes per entry	Teaching estimate; real index structure and overhead vary.

How Essbase Locates a Cell

User query
Entity=US, Product=P100, Jan, Sales

→

Sparse key
Entity + Product + Scenario...

→

Index entry
Pointer to a stored block

→

Data block
All dense intersections

→

Dense offset
Period × Account position

Visual Block Grid

A maximum of 240 sample cells is displayed. Filled cells approximate the selected block density.

Block Growth Experiment

Temporarily move a sparse dimension to dense

Select a sparse dimension to see the multiplication effect on block size and division effect on potential block count.

Choose Outline Change

Change type Fragmentation before restructure

24%

Restructure Impact

1. Validate outline

2. Build temporary files

3. Rewrite index

4. Rewrite blocks/page files

5. Replace & verify

Choose a change and run the simulation.

Expected impact

-

Index rewrite

-

Data block rewrite

-

After clustering

-

What happens during a high-impact dense restructure?

Old outline
Old dense member order

→

Old blocks read
Decompress and map cells

→

New block layout
New cell count/offsets

→

New page files
Recompressed blocks

→

New index
Pointers rebuilt

Optimization Profile

Primary workload Hybrid BSO available? Dense dimensions frequently changed?

Optimization Scorecard

Block-size score

-

Density score

-

Index score

-

Fragmentation score

-

Before You Change Dense/Sparse Settings

Test with representative data. A theoretical block-size improvement can still cause block proliferation, higher index cost, more locking, slower calculations, or expensive dense restructures. Export baseline statistics, test in a copy, reload data, calculate, and compare load/calc/retrieval timings.

Expert Formula Reference

Metric	Core relationship	Expert interpretation
Cells per block	Product of stored members in every dense dimension	Each stored BSO block carries the dense address space for one sparse key.
Expanded block size	Cells per block × 8 bytes	Each numeric cell is modeled as 8 bytes before block compression.
Potential blocks	Product of stored sparse members	Upper theoretical sparse address space; not the count Essbase necessarily stores.
Actual blocks	Existing non-empty sparse combinations	One block exists when at least one dense cell is stored for a sparse combination.
Block density	Stored non-missing cells ÷ total cells in sampled blocks	High density is useful only when block count and block size remain manageable.
Compression ratio	Expanded bytes ÷ compressed bytes	Usually improves with missing/repeating values; real behavior depends on compression method and data pattern.
Clustering ratio	Measure of .pag fragmentation; 1 is best	Lower values may indicate fragmented page files and extra I/O.
Upper-level blocks	Existing blocks containing stored sparse parent intersections	Can grow through aggregation and stored upper-level sparse members.

Oracle Documentation Used for the Learning Model

This simulator is educational and uses transparent assumptions for values that Oracle does not define as a universal fixed formula. Validate final settings using your cube's real application design and runtime statistics.

About BISP Trainings

BISP Trainings is focused on practical, implementation-oriented learning in Oracle Enterprise Performance Management, Essbase, financial consolidation, planning, profitability, reconciliation, metadata governance, tax reporting, Groovy automation, and emerging EPM + AI skills.

The learning approach emphasizes real business cases, hands-on configuration, calculations, architecture, troubleshooting, optimization, and consultant-ready project skills.

Core Learning Areas

Oracle Planning / EPBCS	Driver-based planning, forecasting, forms, business rules, integration and reporting
Oracle FCCS	Consolidation, eliminations, journals, ownership, currency translation and close
Oracle Essbase	BSO/ASO design, dense and sparse settings, calculations, tuning and optimization
Oracle PCMCS / EPCM	Allocations, activity-based costing, profitability and management reporting
Oracle ARCS / EDMCS / TRCS	Reconciliation, metadata governance and tax reporting implementation
Groovy, LangGraph and AI	Automation, intelligent workflows and EPM Copilot design

Assess Your EPM Skills

Use the BISP EPM Skills Assessment to evaluate your current knowledge and identify suitable Oracle EPM learning areas.

The assessment opens in a new browser tab and does not change the current simulator state.

How BISP Uses This Simulator

Understand
Concepts and formulas

→

Simulate
Dense, sparse and block behavior

→

Analyze
Statistics and performance risks

→

Optimize
Design and restructure decisions

→

Apply
Real project implementation