Oracle OLAP Hierarchical Tools Oracle Database Tips by Donald Burleson

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.?

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.