Oracle Business Intelligence now automatically creates more usable aggregates and creates aggregates without having to fix data set errors or modeling problems.
Aggregate persistence can create surrogate keys for joining dimensions to fact aggregate tables.
In most cases the source and the target databases are not 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.
Aggregate persistence auto-corrects, or hardens, level keys that are not unique.
The Oracle BI Summary Advisor recommends aggregates with level keys that are unique as defined, or with level keys that can be 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, is not as effective when natural keys are used, especially in the prepare-create mode of operation.
Aggregate persistence creates aggregates for logical dimensions with unbalanced or skip-level hierarchies.
These aggregates can be created with or without using surrogate keys. The Oracle BI Summary Advisor has also been improved to recommend aggregates that contain logical dimensions with unbalanced and skip-level hierarchies.
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. However, in releases prior to Oracle BI EE 11g Release 1 (11.1.1.9), this lack of chronological keys at higher levels caused aggregates at those levels to be unusable by the Oracle BI Server when it processed queries that use time series functions.
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.
The Oracle BI Server now utilizes 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 which when selected appends as_raw_values to all the valid count distinct measures specified. When this option is selected, aggregate persistence also sets an aggregation expression override on the corresponding logical column for the system-generated aggregate logical table source. The Oracle BI Summary Advisor now recommends both methods of persistence for count distinct measures.