Aggregate Persistence Improvements

Oracle Analytics Server automatically creates more usable aggregates and creates aggregates without the need to fix data set errors or modeling problems.

Surrogate Keys

Aggregate persistence can create surrogate keys for joining dimensions to fact aggregate tables.

In most cases the source and the target databases aren't the same instance.

The Oracle BI Server uses the hash join method to improve surrogate key creation. Where possible, a new request variable is automatically added to the fact aggregate population query and when this request variable is set, the query engine builds hash joins for the dimension tables in parallel before joining to the fact table.

The Oracle BI Summary wizard displays the Use surrogate keys option to suggests when you should use surrogate keys. When this option is selected, the using_surrogate_key clause is added to all levels in the aggregate specification.

Auto Correction (Hardening) of Level Keys

Aggregate persistence auto-corrects or hardens level keys that aren't unique.

The Oracle BI Summary Advisor recommends aggregates with level keys that are unique as defined, or with level keys that are auto-corrected (hardened) to make unique keys. Modifications to underlying data might impact such aggregates.

To improve performance, Oracle suggests creating aggregates using surrogate key rather than natural keys. Auto-correction, or hardening, isn't as effective when natural keys are used, especially in the prepare-create mode of operation.

Unbalanced (Ragged) and Skip-Level Hierarchies

Aggregate persistence creates aggregates for logical dimensions with unbalanced or skip-level hierarchies. You can create aggregates with or without using surrogate keys. The Oracle BI Summary Advisor recommends aggregates that contain logical dimensions with unbalanced and skip-level hierarchies.

Chronological Keys

The Oracle BI Server requires chronological keys to support time series functions such as AGO, TODATE, and PERIODROLLING.

Time series functions operate correctly when only the lowest key in the logical dimension is chronological.

Aggregate persistence generates chronological keys with the CK_ prefix for time levels without chronological keys. A new column is added to the physical dimension aggregate table to store the chronological key value, and a new logical column is added to the logical table of the time dimension. The column is mapped to the new column added to the physical dimension aggregate table.

The delete aggregates statement automatically removes all metadata created to support generated chronological keys.

Count Distinct Measures

The Oracle BI Server uses aggregates with count distinct measures to serve queries for these measures at higher grains.

The Aggregate Persistence wizard includes the Persist Count Distinct Measures as raw values option, when selected appends as_raw_values to all the valid count distinct measures specified. When the Persist Count Distinct Measures as raw values option is selected, aggregate persistence sets an aggregation expression override on the corresponding logical column for the system-generated aggregate logical table source. The Oracle BI Summary Advisor recommends both methods of persistence for count distinct measures.