What's New in Oracle OLAP?

This preface identifies the major enhancements to the OLAP option of Oracle Database.

Oracle Database 11g Release 2 (11.2) Oracle OLAP

In Release 2, the OLAP Option to Oracle Database 11g provides numerous ease-of-use features.

Upgrade Support

You can easily upgrade an analytic workspace from CWM or standard form (AWXML) metadata in Oracle OLAP 10g. You can then incorporate all the features of Oracle OLAP 11g as described in this Guide.

Improved Manageability in Analytic Workspace Manager

Several new features in Analytic Workspace Manager simplify the creation and modification of dimensional objects. You can now rename all objects, save partial mappings, drop or modify dimensions, levels, hierarchies and attributes, and use WHERE conditions in mappings. You can preview the contents of XML templates, fix duplicate names, and modify existing objects by merging them with the template definitions.

SQL Aggregation Management

SQL Aggregation Management is a group of PL/SQL subprograms in DBMS_CUBE that supports the rapid deployment of cube materialized views from existing relational materialized views. A single cube materialized view can replace many of the relational materialized views of summaries on a fact table, providing uniform response time to all summary data through query rewrite.

In the process of creating the cube materialized views, DBMS_CUBE also creates a fully functional analytic workspace including a cube and the cube dimensions. Thus, SQL Aggregation Management can also be used to create the initial metadata for a multidimensional data store enhanced with calculated measures and queried directly by analytic applications.

Build Logs

OLAP now provides several logs: a build log, an operations log, a rejected records log, and a dimension compilation log. You can view the logs either in Analytic Workspace Manager or using the PL/SQL DBMS_CUBE_LOG package.

These logs enable you to track the progress of long running processes, then use the results to profile performance characteristics. They provide information to help you diagnose and remedy problems that may occur during development and maintenance of a cube: Hierarchies that are improperly structured in the relational source tables, records that fail to load, or data refreshes that take too long to complete. They also help diagnose performance problems in querying cubes.

Oracle Database 11g Release 1 (11.1) Oracle OLAP

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.

OLAP Metadata Integration

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.

Automatic Maintenance of Cube and Dimension Views

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 CUBE_TABLE function, which enables the SQL Optimizer enhancements.

Cube Scripts

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 eliminate the use of procedural programs for processing cubes.

Cost-Based Aggregation

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.

Calculation Expression Syntax

OLAP calculation expressions extend the syntax of the SQL analytic functions. This syntax is 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

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.

Object and Data Security

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 GRANT and REVOKE syntax.

Data security controls access to the data in a cube or a dimension. You can grant SELECT, INSERT, UPDATE, and DELETE privileges to dimension members (keys) either globally or for a particular cube to control access to the data in a cube.