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.0.2) Oracle OLAP

Several changes in this release make the powerful analytics of Oracle OLAP more accessible.

OLAP DML Support

Analytic Workspace Manager has improved support of the OLAP DML, which makes the powerful analytics of Oracle OLAP, such as forecasts, allocations, and models, more accessible. You can define custom calculated measures using the OLAP DML. You can also create programs directly in Analytic Workspace Manager instead of OLAP Worksheet. You can invoke these programs in a calculated measure, a cube script, or a maintenance script. The OLAP DML object definitions and programs are saved in the XML when creating a template of the analytic workspace.

Several new OLAP DML statements are dimensionally aware, generating all of the physical objects used to support cubes, cube dimensions, and other dimensional database objects.

Features in Analytic Workspace Manager

Several changes in Analytic Workspace Manager support ease-of-use and improved user control.

• Calculated measure generator enables you to quickly specify all of the standard, predefined calculated measures for a cube. See "Creating Calculated Measures".

• Automatic data type detection ensures the correct setting while simplifying the process of defining attributes and measures.

• You can set hierarchy data constraints to the desired level of consistency.

• Maintenance scripts provide you with greater control over the maintenance process for an analytic workspace. See "Creating and Executing Maintenance Scripts".

• Detailed maintenance reports enable you to track the progress of a maintenance job, detect problems quickly, and correct any issues. See "Loading Data Into Dimensions" and "Loading Data Into Cubes".

• You can delete scheduled maintenance jobs, and stop or delete running jobs.

• Enhanced mapping support enables you to map to constants, expressions, and aggregate data.

• You can identify a different base level for cube aggregation, above the level at which the data is loaded.

• Object security enhancements automatically extend the permissions set on an object to its relational views and the analytic workspace.

• Materialized views can contain a count of the number of children contributing to an aggregate value in a measure, as needed by some applications.

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.