Skip to content

Personal tools
You are here: Home » 10g » Oracle10g Articles » OLAP Enhancements
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548

OLAP Enhancements

by Mike Ault
From the bestselling Oracle10g book, Oracle Database 10g New Features by Mike Ault, Madhu Tumma, and Daniel Liu, published by Rampant TechPress and can be purchased directly from Rampant TechPress for 10% off by clicking here.

Oracle 10g introduces new OLAP capabilities, using the built-in analytical workspaces of the Oracle database. There are many new PL/SQL and XML-based interfaces that aid in the creation of workspaces, based on the cubes, dimensions, measures, and calculations defined in the OLAP database catalog. These new interfaces can be used by the new packages provided or by Oracle Enterprise Manager to define and build analytical workspaces. This removes the need for the user to learn OLAP DML commands. Now you are in the familiar territory of PL/SQL Packages and OEM!

Oracle 10g also provides significant help with performance enhancements. New parallel capabilities are provided for aggregate and SQL import operations, making it much faster to load and materialize analytical workspaces from relational database information. And 10g also provides many hierarchy-related improvements.

We will examine some of these improvements next.

Hierarchy Handling Improvements

Enhancements include additional support for two primary clients, BI Beans and JOLAP, in terms of:

Hierarchical Sorting — The improved API allows the client to specify explicitly that a source be sorted hierarchically. This enhancement allows the client to specify a specific sort order within each level and to specify whether parents come first or last.

Multi-hierarchy Selections — The client can create dimension selections that contain members from multiple hierarchies. This is a requirement of BI Beans.

Hierarchically Aware Dimension Values — The ID of the hierarchy and level encoded in the values of a dimension member is a requirement for multi-hierarchical selections to work. It allows the application developer to write more efficient SQL queries.

Metadata Changes — The MDM (multi-dimensional model) client metadata model has been enhanced to provide objects that represent the notion of a shared level. This is a JOLAP requirement.

With Oracle Database 10g, Client has better control over the usage and selection of a hierarchy. Given a selection from a hierarchy and a self-relation representing the parent of each member of the hierarchy, the client is able to sort the selection hierarchically with either the parent or the children first. Also, the client can now specify a sort order for siblings by either attribute or measure values.

In 10g, it becomes possible to replace the hierarchy class with explicit subclasses. MdmLevelHierarchy represents hierarchies that are based on levels. MdmLevelHierarchy contains a list of levels, a ‘level attribute,’ and a ‘level depth attribute.’

In order to support multi-hierarchy selections in Oracle Database 10g, some changes are introduced in the metadata. Now, the dimension itself dimensions parent attributes. Each hierarchy returns the same instance of MdmAttribute to the call getParentAttribute, and similarly for getLevelAttribute and getLevelDepthAttribute.

In 10g, all dimensions obtained from the getDimensions methods on MdmSchema and MdmMeasure are instances of MdmPrimaryDimension. An MdmPrimaryDimension is a new class used to represent top level, logical dimensions like “Products,” “Geographies,” and “Times.”

XML Support for Analytic Workspace (AW)

In general, there are three basic OLAP development interfaces. They are OLAP java interface, SQL and PL-SQL interface, and OLAP DML interface. The 10g database release focuses more on improving the SQL and PL-SQL interface by providing XML access and expanding the power of SQL user.

Oracle Database 10g includes a new set of XML metadata and associated PL/SQL packages for SQL users to define simple multi-dimensional models and load data into data models. These procedures also help to perform advanced OLAP calculations on the loaded data.

The 10g release introduces the new package dbms_aw_xml, which comes with a comprehensive set of functions. This package allows you to create the Analytic Workspace (AW) and execute the calculations specified in the XML metadata.

New Package dbms_aw_xml

The dbms_aw_xml contains the following functions:

      • validate — This function verifies that the XML provided by the user is well-formed and is ready for execution. It checks that all the implicit AW constraints are met.
      • interpret — This function interprets the input and makes changes to the AW named ‘awname.’ If there is no AW named awname, one is created by this function. INTERPRET accepts the users XML and calls the aw_create olap dml package to create the AW and perform the specific calculations.
      • describe - This function allows the users to examine the contents of the AW. It takes the awname as the input, and outputs the information as XMLType. If the input is provided, it outputs the information about all AW objects.

    Prior to 10g, the cwm_to_ecm olap dml package used to read the metadata from the cwm lite catalog and used it to create an Analytic Workspace. This package can now read XML metadata and produce corresponding Analytic Workspace/s. It indicates that the Analytic Workspace created by CwM metadata and produced from the XML metadata have the same structure and follow the same conventions.

    AW Objects Created by XML Metadata

    The Analytic Workspace created from the XML metadata leads to the creation of many AW objects. They are as follows:

    AWObject — This is a super class that describes objects that have attributes. This object is loadable from an external data source, such as relational data. Examples of AWObject include dimensions and measures.

    Attribute — This is an object that describes the characteristic features of another object.

    Dimension — This is an ordered list of dimension members. Dimension is the core of the multi-dimensional model.

    Cube — This is a logical object that describes the dimensionality of objects. Cubes are composed of one or more dimensions.

    Hierarchy — A hierarchy is a way of structuring dimension members. At the bottom of a hierarchy lies the detail level. If a dimension member is at the top of the hierarchy, then the parent value is null.

    MemberSelection — This is an ordered list of dimension members. A MemberSelection is useful for identifying a specific portion of a cube.

    Level — This is a MemberSelection that describes a portion of a hierarchy. In a level, no parent can be a child of another member of the same level.

    HierarchyLevelAssociation — This is another abstract object that provides a link between a hierarchy and its levels. The end-user does not commonly work with this object.

    Measure — A Measure is the object that holds the user's data. The dimensions, hierarchies etc. all provide the logical framework that holds the Measure.

    SolveDefinition — This represents a set of computations to be applied to one or more measures. SolveDefinition represents the real value of the Oracle OLAP system.

    Useful Views

    The following new views allow the user to examine Analytic Workspace for more detailed information, including data volume, sparsity of data, and access:


    Mike Ault is one of the leading names in Oracle technology. The author of more than 20 Oracle books and hundreds of articles in national publications, Mike Ault has five Oracle Masters Certificates and was the first popular Oracle author with his book Oracle7 Administration and Management. Mike also wrote several of the “Exam Cram” books, and enjoys a reputation as a leading author and Oracle consultant.

    Mike has released his complete collection of Oracle scripts, covering every possible area of Oracle administration and management.The collection is available at:

    Contributors : Mike Ault
    Last modified 2005-06-21 11:58 PM
    Transaction Management
    Reduce downtime and increase repeat sales by improving end-user experience.
    Free White Paper
    Database Recovery
    Feeling the increased demands on data protection and storage requirements?
    Download Free Report!

    Powered by Plone