| |
Materialized Views, Windows Aggregate Function
OADM also makes use of materialized views and the windows aggregate function.
Materialized Views
Materialized views are created by default in OADM to avoid data duplication. however, their refreshing is a user choice. It can be synchronous or asynchronous (offline); it is not forced. To enable the use of MVs, the DBA can:
(A) Create MVs in the STAR schema
- Snapshot relational tables based on SQL queries against warehouse detail tables may be created, with an inbuilt mechanism to refresh the snapshots when the underlying data has changed.
- When these materialized views meet a number of conditions, you can update the snapshots with incremental, changed data rather than completely rebuilding.
- This approach can dramatically reduce the time needed to refresh your summaries and makes them available to your end-users.
(B) Create Cube-orgainized MVs using the OLAP option
- Cube MVs are integrated with the Oracle Database SQL Optimizer to provide transparent summary management.
- Cube MVs are simply "turned on" as an option when creating an OLAP cube, and they provide enhancements to existing BI applications in the following ways
- Applications query detail relational tables. The 11g SQL Query Optimizer treats OLAP cubes as MVs and rewrites queries to access cubes transparently.
- A single cube provides the equivalent of tens, hundreds, or thousands of summary combinations.
- The cube MV is refreshed using standard MV procedures.
With either approach, the DBA also pepares the relational schema for query rewrite by assigning the appropriate constraints, defining SQL Dimension objects for lookup tables, granting the appropriate privileges, and turning on Query Rewrite.
Windows Aggregate Function
Relational Aggregate functions, those that act on a group of rows like max and min , are helpful to developers looking to simplify the logic required to perform a task.
Each OADM "aggregate" table is implemented as a materialized view, or its derivates, by default.
Close Window |