This preface identifies the major enhancements to the OLAP option of Oracle Database.
The OLAP Option to Oracle Database 11g continues the development trends of Oracle9i and Oracle Database 10g, especially in deepening integration with the database and enhancing SQL access to cubes, security, and metadata. The power of OLAP is easily accessible to SQL applications. Oracle Database 11g also introduces the cube as a summary management solution for relational OLAP (ROLAP) implementations.
All metadata for cubes and dimensions is stored in the Oracle database and revealed in the data dictionary views, so that you can query the entire business model in SQL. Use of the data dictionary to store the metadata officially codifies the dimensional model in the database, provides significant improvements for metadata queries, and supports other new features such as SQL object security for cubes and dimensions.
Oracle Database 11g automatically creates and maintains relational views for every cube, dimension, and hierarchy in the database. If you modify a dimensional object, such as adding a calculated measure to a cube, the view is immediately re-created to reflect the change. Oracle Database defines these views using the new
CUBE_TABLE function, which enables the SQL Optimizer enhancements.
A cube script is an ordered list of commands that prepare a cube for querying, such as Clear Data, Load Data, Aggregate, Execute PL/SQL, and Execute OLAP DML. For many applications, cube scripts will eliminate the need to use procedural programs for processing cubes.
Fast updates and uniform querying performance are two hallmarks of the OLAP option. Cost-based aggregation enhances performance in both areas by executing a fine-grained pre-aggregation strategy and storing sparse data sets very efficiently.
OLAP calculation expressions extend the syntax of the SQL analytic functions. This syntax is already familiar to SQL developers and DBAs, so that it is easier for them to adopt than proprietary OLAP languages and APIs.
This syntax is used to define calculations that are embedded in the cube, such as dynamically calculated facts or measures.
Cube materialized views are cubes that have been enhanced to use the automatic refresh and query rewrite features of Oracle Database.
Cube materialized views bring the fast update and fast query capabilities of the OLAP option to applications that query detail relational tables. Summary data is generated and stored in a cube, and query rewrite automatically redirects queries to the cube materialized views. Applications experience excellent query performance.
Oracle Database 11g introduces both object security and data security to OLAP cubes and dimensions. Both types of security are granted to database users and roles.
Object security controls access to analytic workspaces, cubes, and dimensions using standard SQL
Data security controls access to the data in a cube or a dimension. You can grant
DELETE privileges to dimension members (keys) either globally or in the context of a particular cube to control access to the data in a cube.
See Also:Chapter 8, "Security"