2 Understanding OLAP Java API Metadata

Getting and Setting Names

Most metadata objects have a name that you can get by calling the getName method of the object. For some objects, you can assign a name when you create the object. For example, an oracle.olapi.metadata.deployment.AW object represents an analytic workspace. When you create an AW by calling the findOrCreateAW method of an MdmDatabaseSchema, you use the publicName parameter of the method to specify a name for the AW object that the method returns.

For some objects, you can use the setName method to change the name of an existing object. For example, you can change the name of an MdmStandardDimension by calling the setName method of the dimension object. The new name does not take effect until you commit the root transaction of the session. After you call setName, but before you commit the root transaction, the getNewName method returns the new name while the getName method returns the existing name.

You can get some objects by name from an MdmDatabaseSchema. For more information on getting objects by name, see "Representing Schemas".

For use in displaying names or descriptions in a user interface, or for any purpose you want, you can associate any number of names and descriptions with an MdmObject by using the MdmDescription class. For information on using that class, see "Using Descriptions".

Describing Unique Identifiers

Most metadata objects have a unique identifier (ID). The identifier has the form objectName or ownerName.objectName. For example, for the MdmDatabaseSchema that represents the schema for the user GLOBAL, the identifier returned by the getID method is GLOBAL. For an MdmPrimaryDimension named PRODUCT_AWJ, the getID method returns GLOBAL.PRODUCT_AWJ and for an MdmLevelHierarchy named PRODUCT_PRIMARY, the method returns GLOBAL.PRODUCT_AWJ.PRODUCT_PRIMARY. For some BaseMetadataObject objects, such as an MdmMetadataProvider, the getID method returns an empty string.

The ID of a metadata object is persistent. However, if the name or the owner of a metadata object changes, then the ID changes as well. For more information on getting objects by ID, see "Getting Metadata Objects by ID".

For a legacy 10g metadata object, the first part of the identifier is a namespace. The namespace is followed by the namespace delimiter, which is two periods. An example of the identifier of a 10g dimension is AWXML_DIMENSION..GLOBAL.PRODUCT_AW.

Supporting Legacy Metadata Objects

In Oracle Database, Release 11g, Oracle Database, Release 11gOracle OLAP supports legacy 10g OLAP Java API applications. Namespaces identify 10g metadata objects and enable them to exist in the same session as 11g objects.

MdmStandardDimension mdmProdAWDim =
  mdmDBSchema.findOrCreateStandardDimension("PRODUCT_AW",
    MdmMetadataProvider.AWXML_DIMENSION_NAMESPACE);

Getting Metadata Objects by ID

Usually, you get or create metadata objects by calling methods on the owning object. For example, you can get or create an MdmCube by calling the findOrCreateCube method of an MdmDatabaseSchema object. However, you can also get an existing metadata object from an MdmMetadataProvider by specifying the ID of the object. The MdmMetadataProvider.getMetadataObject method takes a String that is the ID of an object and returns the object. The getMetadataObjects method takes a List of IDs and returns a List of objects.

You can store the ID of a metadata object from one session and then get the object by that ID in another session. Of course, getting an object by a stored ID assumes that the object still exists and that the ID of the object has not changed. For some metadata objects, you can change the name or the owner. If the name or owner of the object changes, then the ID of the object changes.

Exporting and Importing Metadata as XML Templates

The MdmMetadataProvider class has many methods for exporting and importing metadata objects to and from XML definitions of those objects. The XML definition is a template from which Oracle OLAP can create the metadata objects defined.

You can use XML templates to transport metadata objects between Oracle Database instances. You can exchange XML templates between Analytic Workspace Manager and an OLAP Java API application; that is, in Analytic Workspace Manager you can import a template that you created with an MdmMetadataProvider export XML method, and you can use an importXML method to import an XML template created by Analytic Workspace Manager.

When exporting XML, you can rename objects or specify bind variables for the values of XML attributes. You can also supply an implementation of the XMLWriterCallback interface to manage some aspects of the export process. When importing XML, you can specify an MdmDatabaseSchema to own the imported objects, bind values to replace the bind variables in the exported XML, and an implementation of the XMLParserCallback interface to manage some aspects of the import process.

<!DOCTYPE Metadata [
<!ENTITY % BIND_VALUES PUBLIC "OLAP BIND VALUES" "OLAP METADATA">
%BIND_VALUES;
 ]>

Representing the Root Schema

The root schema is a container for database schema objects. This top-level schema is represented by the MdmRootSchema class. You get the MdmRootSchema with the getRootSchema method of the MdmMetadataProvider. From the MdmRootSchema you can get all of the MdmDatabaseSchema objects or you can get an individual MdmDatabaseSchema by name.

The MdmRootSchema class also contains all of the MdmCube, MdmMeasure, and MdmPrimaryDimension objects that are provided by the MdmMetadataProvider, and has methods for getting those objects. However, the List of objects returned by those methods contains only the cubes, measures, or dimensions that the user has permission to see.

Figure 2-4 shows the associations between an MdmMetadataProvider and the subclasses of MdmSchema.

Figure 2-4 Associations Between MdmMetadataProvider and the MdmSchema Subclasses

Description of "Figure 2-4 Associations Between MdmMetadataProvider and the MdmSchema Subclasses"

Representing Database Schemas

Each Oracle Database user owns a relational schema. The schema for a database user is represented by the MdmDatabaseSchema class. The MdmRootSchema has one MdmDatabaseSchema object for each database user. An MdmDatabaseSchema has the same name as the database user. For example, the name of the MdmDatabaseSchema for the user GLOBAL is GLOBAL.

You can get one or all of the MdmDatabaseSchema objects with methods of the MdmRootSchema. However, access to the objects that are owned by an MdmDatabaseSchema is determined by the security privileges granted to the user of the session. For information on object and data security management and privileges, see Oracle OLAP User's Guide.

An MdmDatabaseSchema is the owner of top-level OLAP metadata objects and the objects created by them. You use an MdmDatabaseSchema to get existing metadata objects or to create new ones. The top-level objects are the following.

Except for an MdmTable, you can create new top-level objects, or get existing ones, with the findOrCreate methods such as findOrCreateAW and findOrCreateStandardDimension. Creating objects is described in Chapter 3.

When you commit the Transaction in which you have created top-level OLAP metadata objects, those objects then exist in the Oracle data dictionary. They are available for use by ordinary SQL queries as well as for use by applications that use the Oracle OLAP Java API.

Because the metadata objects exist in the Oracle data dictionary, an Oracle Database DBA can restrict access to certain types of the metadata objects. In a client application, you can set such restrictions by using the JDBC API to send standard SQL GRANT and REVOKE commands through the JDBC connection for the user session.

You can get an MdmTable, or other top-level object, with the getTopLevelObject method. You can get all of instances of a particular type of top-level object with methods such as getAWs, getDimensions, or getOrganizationalSchemas, or you can use the getSchemaObjects to get all of the objects owned by the MdmDatabaseSchema. You can add or remove top-level objects with methods like addAW and removeSchemaObject.

Representing Organizational Schemas

An OLAP measure folder organizes measures, cubes, and dimensions. A measure folder is represented by the MdmOrganizationalSchema class. Measure folders provide a way to differentiate among the similarly named measures. For example, a user may have access to several schemas with measures named Sales or Costs. You could separate measures that have the same name into different MdmOrganizationalSchema objects. An MdmOrganizationalSchema has methods for adding or removing cubes, dimensions, and measures. You can nest organizational schemas, so the class also has methods for adding and removing other MdmOrganizationalSchema objects.

Representing Cubes

An OLAP cube is represented by the MdmCube class. An MdmCube is a container for MdmMeasure objects that are dimensioned by the same set of MdmPrimaryDimension objects. An application creates MdmBaseMeasure or MdmDerivedMeasure objects with the findOrCreateBaseMeasure and findOrCreateDerivedMeasure methods of an MdmCube. It associates each of the dimensions of the measures with the cube by using the addDimension method.

An MdmCube usually corresponds to a single fact table or view. To associate the table or view with the cube, you use Query and CubeMap objects. You get the Query for the table or view and then associate the Query with the CubeMap by using the setQuery method of the CubeMap.

The CubeMap contains MeasureMap and CubeDimensionalityMap objects that map the measures and dimensions of the cube to data sources. With the MeasureMap, you specify an MdmBaseMeasure and an Expression that identifies the column in the fact table or view that contains the base data for the measure.

To map the dimensions of the cube you get the MdmDimensionality objects of the cube. You create a CubeDimensionalityMap for each MdmDimensionality. You then specify an Expression for the CubeDimensionalityMap that identifies the foreign key column for the dimension in the fact table or view. If you want to specify a dimension column other than the column for the leaf level dimension members, then you must specify a join Condition with the setJoinCondition method of the CubeDimensionalityMap.

An MdmCube has an associated CubeOrganization. The CubeOrganization deploys the cube in an analytic workspace or as a relational database object. To deploy a cube to an analytic workspace, you call the findOrCreateAWCubeOrganization method of the MdmCube. You use the AWCubeOrganization returned by that method to specify characteristics of the cube, such as how Oracle OLAP builds the cube, how the cube stores measure data, and whether the database creates materialized views for the cube. For information on the AWCubeOrganization class, see Oracle OLAP Java API Reference.

If the AWCubeOrganization has a materialized view option of REWRITE_MV_OPTION, then Oracle OLAP creates a materialized view for the cube that can be used by the database query rewrite system. If the materialized view option is REWRITE_WITH_ATTRIBUTES_MV_OPTION, then Oracle OLAP includes in the rewrite materialized view the dimension attributes for which the isPopulateLineage method returns true. You set the materialized view options with the setMVOption method of the AWCubeOrganization.

An MdmCube also has a ConsistentSolveSpecification object, which contains one or more ConsistentSolveCommand objects that specify how Oracle OLAP calculates (or solves) the values of the measures of the cube. For example, as the ConsistentSolveCommand, you could specify an AggregationCommand that represents the SUM or the MAX function. You specify the ConsistentSolveSpecification with the setConsistentSolveSpecification method of the cube.

A cube is consistent when the values of the measures match the specification, for example, when the values of the parents are equal to the SUM of the values of their children. A cube becomes consistent when the BuildProcess executes the ConsistentSolveCommand.

For examples of creating MdmCube and MdmMeasure objects and mapping them, and of the other operations described in this section, see Example 4-7, "Creating and Mapping an MdmCube" and Example 4-8, "Creating and Mapping Measures".

Figure 2-6 shows the associations between an MdmCube and the some of the classes mentioned in this section. The figure shows an MdmCube as deployed in an analytic workspace.

Figure 2-6 MdmCube and Associated Objects

Description of "Figure 2-6 MdmCube and Associated Objects"

Representing Measures

An MdmMeasure is an abstract class that represents a set of data that is organized by one or more MdmPrimaryDimension objects. The structure of the data is similar to that of a multidimensional array. Like the dimensions of an array, which provide the indexes for identifying a specific cell in the array, the MdmPrimaryDimension objects that organize an MdmMeasure provide the indexes for identifying a specific value of an element of the MdmMeasure.

For example, suppose you have an MdmMeasure that has data that records the number of product units sold to a customer during a time period and through a sales channel. The data of the measure is organized by dimensions for products, times, customers, and channels (with a channel representing the sales avenue, such as catalog or internet.). You can think of the data as occupying a four-dimensional array with the product, time, customer, and channel dimensions providing the organizational structure. The values of these four dimensions are indexes for identifying each particular cell in the array. Each cell contains a single data value for the number of units sold. You must specify a value for each dimension in order to identify a value in the array.

The values of an MdmMeasure are usually numeric, but a measure can have values of other data types. The concrete subclasses of MdmMeasure are MdmBaseMeasure and MdmDerivedMeasure.

An MdmBaseMeasure in an analytic workspace has associated physical storage structures. Typically an MdmCube gets the base data for an MdmBaseMeasure from a column in a fact table. Oracle OLAP then calculates the aggregate values of the measure and stores those values in an OLAP view for the cube.

An MdmDerivedMeasure has no associated physical storage. Oracle OLAP dynamically calculates the values for an MdmDerivedMeasure as needed.

The set of elements that are in an MdmMeasure is determined by the structure of the MdmPrimaryDimension objects of the MdmMeasure. That is, each element of an MdmMeasure is identified by a tuple, which is a unique combination of members from the MdmPrimaryDimension objects.

The MdmPrimaryDimension objects of an MdmMeasure are MdmStandardDimension or MdmTimeDimension objects. They usually have at least one hierarchical structure. Those MdmPrimaryDimension objects include all of the members of their component MdmHierarchy objects. Because of this structure, the values of the elements of an MdmMeasure are of one or more of the following:

• Values from the fact table column, view, or calculation on which the MdmMeasure is based. These values belong to MdmMeasure elements that are identified by a combination of values from the members at the leaf level of an MdmHierarchy.

• Aggregated values that Oracle OLAP has provided. These values belong to MdmMeasure elements that are identified by the value of at least one member from an aggregate level of an MdmHierarchy.

• Values specified by an Expression for a MdmDerivedMeasure or a custom dimension member.

As an example, imagine an MdmBaseMeasure called mdmUnitCost that is dimensioned by an MdmTimeDimension called mdmTimeDim and an MdmStandardDimension of products called mdmProdDim. Each of the mdmTimeDim and the mdmProdDim objects has all of the leaf members and aggregate members of the dimension it represents. A leaf member is one that has no children. An aggregate member has one or more children.

A unique combination of two members, one from mdmTimeDim and one from mdmProdDim, identifies each mdmUnitCost element, and every possible combination of dimension elements is used to specify the entire mdmUnitCost element set.

Some mdmUnitCost elements are identified by a combination of leaf members (for example, a particular product item and a particular month). Other mdmUnitCost elements are identified by a combination of aggregate members (for example, a particular product family and a particular quarter). Still other mdmUnitCost elements are identified by a mixture of leaf and aggregate members. The values of the mdmUnitCost elements that are identified only by leaf members come directly from the column in the database fact table (or fact table calculation). They represent the lowest level of data. However, for the elements that are identified by at least one aggregate member, Oracle OLAP provides the values. These higher-level values represent aggregated, or rolled-up, data.

Thus, the data represented by an MdmBaseMeasure is a mixture of fact table data from the data store and aggregated data that Oracle OLAP makes available for analytical manipulation. The data can include values that Oracle OLAP assigns as specified by an MdmModel.

Representing Dimensions

Dimensions are represented by instances of the MdmPrimaryDimension class, which is an abstract subclass of MdmDimension. The concrete subclasses of the MdmPrimaryDimension class represent different types of data. The concrete subclasses of MdmPrimaryDimension are the following:

• MdmMeasureDimension, which has all of the MdmMeasure objects in the data store as the values of the dimension members. A data store has only one MdmMeasureDimension. You can obtain the MdmMeasureDimension by calling the getMeasureDimension method of the MdmRootSchema. You can get the measures of the data store by calling the getMeasures method of the MdmMeasureDimension.

• MdmStandardDimension, which has no special characteristics, and which typically represent dimensions of products, customers, distribution channels, and so on.

• MdmTimeDimension, which has time periods as the values of the members. Each time period has an end date and a time span. An MdmTimeDimension has methods for getting the attributes that record that information.

An MdmPrimaryDimension implements the following interfaces.

• Buildable, which is a marker interface for objects that you can specify in constructing a BuildItem.

• MdmMemberListMapOwner, which defines methods for finding or creating, or getting, a MemberListMap object.

• MdmViewColumnOwner, which is marker interface for objects that can have an associated MdmViewColumn.

• MetadataObject, which defines a method for getting a unique identifier.

• MdmQuery, which defines methods for getting the Query object associated with the implementing class and for getting information about the Query.

An MdmPrimaryDimension can have component MdmDimensionLevel objects that organize the dimension members into levels. It also can have MdmHierarchy objects, which organize the levels into the hierarchies. An MdmPrimaryDimension has all of the members of the component MdmHierarchy objects, while each of the MdmHierarchy objects has only the members in that hierarchy.

You can get all of the MdmPrimaryDimension objects that are contained by an MdmDatabaseSchema or an MdmOrganizationalSchema by calling the getDimensions method of the object. An MdmDatabaseSchema has methods for finding an MdmTimeDimension or an MdmStandardDimension by name or creating the object if it does not already exist.

MdmStandardDimension and MdmTimeDimension objects contain MdmAttribute objects. Some of the attributes are derived by Oracle OLAP, such as the parent attribute, and others you map to data in relational tables or to data that you specify by an Expression. For information on attributes, see "Representing Dimension Attributes".

An MdmPrimaryDimension can organize the dimension members into one or more levels. Each level is represented by an MdmDimensionLevel object. An MdmStandardDimension or an MdmTimeDimension can contain MdmHierarchy objects that organize the levels into hierarchical relationships. In an MdmLevelHierarchy the dimension levels are represented by MdmHierarchyLevel objects. The concrete MdmDimensionLevel and MdmHierarchyLevel classes, and the abstract MdmHierarchy class, are the direct subclasses of the abstract MdmSubDimension class.

Representing Dimension Levels

An MdmDimensionLevel represents a set of dimension members that are at the same level. A dimension member can be in at most one dimension level. You get or create an MdmDimensionLevel with the findOrCreateDimensionLevel of an MdmPrimaryDimension. You can map an MdmDimensionLevel to a data source by using a MemberListMap.

An MdmPrimaryDimension has a method for getting a list of all of the MdmDimensionLevel objects that it contains. It also has a method for finding an MdmDimensionLevel by name or creating the object if it does not already exist.

Representing Hierarchies

MdmHierarchy is an abstract subclass of MdmSubDimension. The concrete subclasses of MdmHierarchy are MdmLevelHierarchy and MdmValueHierarchy.

An MdmHierarchy organizes the members of a dimension into a hierarchical structure. The parent-child hierarchical relationships of an MdmLevelHierarchy are based on the levels of the dimension. In an MdmValueHierarchy, the hierarchical relationships are based on dimension member values and not on levels. An MdmPrimaryDimension can have more than one of either or both kinds of hierarchies.

The parent of a hierarchy member is recorded in a parent MdmAttribute, which you can get by calling the getParentAttribute method of the MdmHierarchy. The ancestors of a hierarchy member are recorded in an ancestors MdmAttribute, which you can get by calling the getAncestorsAttribute method.

An MdmPrimaryDimension has a method for getting a list of all of the MdmHierarchy objects that it contains. It also has methods for finding an MdmLevelHierarchy or MdmValueHierarchy by name or creating the object if it does not already exist.

Representing a Level-based Hierarchy

MdmLevelHierarchy is a subclass of MdmHierarchy. An MdmLevelHierarchy has a tree-like structure with a top, or highest, level, and a leaf, or lowest, level. Each member may have zero or one parent in the hierarchy. Cycles are not allowed, for example where member A is the parent of member B, member B is the parent of member C, and member C is the parent of member A.

Members that are not the child of any other member are the top members. Members with children are aggregates or aggregate members of the hierarchy. Members with no children are the leaves or leaf members of the hierarchy.

Each member is in a level. The levels are ordered, from top level to leaf level. The order is determined by the order in which you create the MdmDimensionLevel objects of the MdmPrimaryDimension. The first MdmDimensionLevel that you create is the top level and the last one you create is the leaf level. For example, for the CALENDAR_YEAR hierarchy of the TIME_AWJ dimension, the BuildAW11g example program creates four MdmDimensionLevel objects in the following order: TOTAL_TIME, YEAR, QUARTER, and MONTH. The top level is TOTAL_TIME and the leaf level is MONTH.

If a member of the hierarchy has a parent, then that parent must be in a higher level. Oracle OLAP expects that all leaf members in the hierarchy are in the leaf level. You can specify that Oracle OLAP allow the hierarchy to be ragged. In a ragged hierarchy, one or more leaf members are not in the leaf level. You can specify allowing the hierarchy to be ragged by calling the setIsRagged(true) method of the MdmLevelHierarchy.

Oracle OLAP also expects that if a member is in a level below the top level, then that member has a parent, and that the parent is in the level just above the level of the member. If a member is not at the top level and that member either does not have a parent or the parent is not in the next higher level, then the hierarchy is a skip-level hierarchy. You can specify allowing a skip-level hierarchy by calling the setIsSkipLevel(true) method of the MdmLevelHierarchy.

Figure 2-7 illustrates the relationships of members in a regular hierarchy, a ragged hierarchy, and two types of skip-level hierarchies.

Figure 2-7 Regular, Ragged, and Skip-level Hierarchies

Description of "Figure 2-7 Regular, Ragged, and Skip-level Hierarchies"

The different levels of an MdmLevelHierarchy are represented by MdmHierarchyLevel objects. For an example of creating a level-based hierarchy, see "Creating and Mapping an MdmLevelHierarchy".

The MdmLevelHierarchy has all of the members of the hierarchy, and each of the component MdmHierarchyLevel objects has only the members at the level that it represents. An MdmLevelHierarchy can also represent a nonhierarchical list of members, in which case the MdmLevelHierarchy has one MdmHierarchyLevel, and both objects have the same members. You get the levels of an MdmLevelHierarchy by calling the getHierarchyLevels method.

An MdmLevelHierarchy has a method for getting a list of all of the MdmHierarchyLevel objects that it contains. It also has a method for finding an MdmHierarchyLevel by name or creating the object if it does not already exist.

An MdmPrimaryDimension can contain more than one MdmLevelHierarchy. For example, an MdmTimeDimension dimension might have two MdmLevelHierarchy objects, one organized by calendar year time periods and the other organized by fiscal year time periods. The MdmHierarchyLevel objects of one hierarchy associate MdmDimensionLevel objects of calendar year time periods with the hierarchy. The MdmHierarchyLevel objects of the other hierarchy associate MdmDimensionLevel objects of fiscal year time periods with that hierarchy. Generally, level-based hierarchies share the lowest level, so the MdmHierarchyLevel for the lowest level of each of the hierarchies associates the same MdmDimensionLevel with each hierarchy. For example, the calendar year hierarchy and the fiscal year hierarchy share the same MdmHierarchyLevel of month time periods.

Representing Hierarchy Levels

MdmHierarchyLevel is a subclass of MdmSubDimension. An MdmHierarchyLevel associates an MdmDimensionLevel with an MdmLevelHierarchy.

The order of the levels in the hierarchy is specified by the order in which you create the MdmHierarchyLevel objects for the MdmLevelHierarchy. The first MdmHierarchyLevel that you create is the highest level and the last one that you create is the lowest level. For an example of creating a hierarchy, see "Creating and Mapping an MdmLevelHierarchy".

Describing the MdmAttribute Class

The abstract MdmAttribute class has a subclass, which is the abstract class MdmSingleValuedAttribute. That class has two concrete subclasses: MdmBaseAttribute and MdmDerivedAttribute.

Describing the MdmBaseAttribute Class

An MdmBaseAttribute has values that are stored in the OLAP views for the dimension that contains it and the hierarchy to which it applies. For information on OLAP views, see "Using OLAP Views".

You create an MdmBaseAttribute with the findOrCreateBaseAttribute method of an MdmPrimaryDimension. You map the MdmBaseAttribute to a column in a relational table or view. When you build the MdmPrimaryDimension that created the attribute, Oracle OLAP stores the values of the MdmBaseAttribute in an OLAP view. You can get the column for the MdmBaseAttribute in the OLAP view by using the getETAttributeColumn method. That method returns an MdmViewColumn object.

Examples of MdmBaseAttribute objects are the name attribute created and mapped in Example 4-5 and the long description attribute created in Example 4-6. The mapping for that long description attribute is in Example 4-3.

For regular OLAP queries, using Source objects, you only need to map an MdmBaseAttribute to MdmDimensionLevel objects by using MemberListMap objects. For SQL queries against OLAP views, you should map the attributes to MdmHierarchyLevel objects by using HierarchyLevelMap objects.

mdmAttr.setPopulateLineage(true);

SELECT TOTAL_PRODUCT_SHORT_DESC || '*' || CLASS_SHORT_DESC || '*' ||
        FAMILY_SHORT_DESC || '*' || ITEM_SHORT_DESC
  FROM PRODUCT_AWJ_PRODUCT_PRIMA_VIEW
  ORDER BY TOTAL_PRODUCT nulls first, CLASS nulls first,
           FAMILY nulls first, ITEM nulls first;

TOTAL_PRODUCT_SHORT_DESC||'*'||CLASS_SHORT_DESC||'*'||FAMILY_SHORT_DESC||'*'||IT
--------------------------------------------------------------------------------
Total Product***
*Hardware**
**CD/DVD*
***External 48X CD-ROM
***External - DVD-RW - 8X
***Internal - DVD-RW - 8X
...
**Desktop PCs*
***Sentinel Financial
***Sentinel Multimedia
***Sentinel Standard
**Portable PCs*
***Envoy Ambassador
***Envoy Executive
***Envoy Standard
...

TOTAL_PRODUCT_SHORT_DESC||'*'||CLASS_SHORT_DESC||'*'||FAMILY_SHORT_DESC||'*'||IT
--------------------------------------------------------------------------------
Total Product***
Total Product*Hardware**
Total Product*Hardware*CD/DVD*
Total Product*Hardware*CD/DVD*External 48X CD-ROM
Total Product*Hardware*CD/DVD*External - DVD-RW - 8X
Total Product*Hardware*CD/DVD*Internal - DVD-RW - 8X
...
Total Product*Hardware*Desktop PCs*
Total Product*Hardware*Desktop PCs*Sentinel Financial
Total Product*Hardware*Desktop PCs*Sentinel Multimedia
Total Product*Hardware*Desktop PCs*Sentinel Standard
Total Product*Hardware*Portable PCs*
Total Product*Hardware*Portable PCs*Envoy Ambassador
Total Product*Hardware*Portable PCs*Envoy Executive
Total Product*Hardware*Portable PCs*Envoy Standard
...

Describing the MdmDerivedAttribute Class

An MdmDerivedAttribute has values that Oracle OLAP calculates on the fly as you need them. You create an MdmDerivedAttribute with the findOrCreateDerivedAttribute method of an MdmPrimaryDimension. The values of an MdmDerivedAttribute come from a relation between dimension members or from an Expression that you specify. Oracle OLAP generates several MdmDerivedAttribute objects, such as the attributes returned by the getParentAttribute and the getAncestorsAttribute methods of an MdmPrimaryDimension.

Getting Cube View and View Column Names

To get the name of a cube view, call the MdmCube.getViewName() method. For example, the following code gets the name of the view for the MdmCube that is named UNITS_CUBE_AWJ. In the code, the mdmDBSchema object is the MdmDatabaseSchema for the GLOBAL user.

MdmCube mdmUnitsCube =
 (MdmCube)mdmDBSchema.getTopLevelObject("UNITS_CUBE_AWJ");
String cubeViewName = mdmUnitsCube.getViewName();
println("The name of the view for the " +
         mdmUnitsCube.getName() + " cube is " + cubeViewName + ".");

The output of the code is the following.

The name of the view for the UNITS_CUBE_AWJ cube is UNITS_CUBE_AWJ_VIEW.

You can change the name of the OLAP view by using the MdmCube.setViewName method. To make the name change permanent, you must commit the Transaction.

The OLAP view for an MdmCube has a column for each measure of the cube, including each derived measure. In Oracle OLAP User's Guide, a derived measure is known as a calculated measure. A cube view also has a column for each dimension of the cube. For example, for the MdmCube named UNITS_CUBE_AWJ, the view is named UNITS_CUBE_AWJ_VIEW. The following code gets the names of the view columns.

MdmCube mdmUnitsCube = mdmDBSchema.findOrCreateCube("UNITS_CUBE_AWJ");
List<MdmQueryColumn> mdmQCols = mdmUnitsCube.getQueryColumns();
for (MdmQueryColumn mdmQCol : mdmQCols )
{
  MdmViewColumn mdmViewCol = (MdmViewColumn) mdmQCol;
  println(mdmViewCol.getViewColumnName());
}

The code displays the following output.

TIME_AWJ
PRODUCT_AWJ
CUSTOMER_AWJ
CHANNEL_AWJ
UNITS
SALES
COST

The UNITS, SALES, and COST columns are for the measures of the cube, and the other four columns are for the dimensions of the cube.

Getting Dimension and Hierarchy View and View Column Names

To get the name of the OLAP view for a dimension or a hierarchy, call the getETViewName() method of the MdmPrimaryDimension or MdmHierarchy. You can get the name of a column in the view by calling the appropriate method of the metadata object. For example, the following code gets the name of the key column for the CHANNEL_AWJ dimension and the parent column for the CHANNEL_PRIMARY hierarchy.

println(mdmChanDim.getETKeyColumn().getViewColumnName());
MdmViewColumn mdmParentCol =(MdmViewColumn) mdmChanHier.getETParentColumn();
println(mdmParentCol.getViewColumnName());

The code displays the following output.

DIM_KEY
PARENT

You can change the name of the OLAP view by using the setETViewName method of the MdmPrimaryDimension or MdmHierarchy.

The OLAP view for an MdmPrimaryDimension has a column for the dimension keys, a column for each dimension level, and a column for each attribute associated with the dimension. For example, for the MdmStandardDimension named CHANNEL_AWJ, the view is named CHANNEL_AWJ_VIEW. The SQL command DESCRIBE CHANNEL_AWJ_VIEW displays the names of the following columns.

DIM_KEY
LEVEL_NAME
MEMBER_TYPE
DIM_ORDER
LONG_DESCRIPTION
SHORT_DESCRIPTION
TOTAL_CHANNEL_LONG_DESC
TOTAL_CHANNEL_SHORT_DESC
CHANNEL_LONG_DESC
CHANNEL_SHORT_DESC

The OLAP view for an MdmHierarchy has a column for the dimension keys and a column for the parent of a hierarchy member. If it is an MdmLevelHierarchy, then it also has a column for each hierarchy level and a column for the depth of a level. If the hierarchy has one or more added attributes, then the view has a column for each attribute. For example, for the MdmLevelHierarchy named CHANNEL_PRIMARY, the view is named CHANNEL_AWJ_CHANNEL_PRIMA_VIEW. The SQL command DESCRIBE CHANNEL_AWJ_CHANNEL_PRIMA_VIEW displays the names of the following columns.

DIM_KEY
LEVEL_NAME
MEMBER_TYPE
DIM_ORDER
HIER_ORDER
LONG_DESCRIPTION
SHORT_DESCRIPTION
TOTAL_CHANNEL_LONG_DESC
TOTAL_CHANNEL_SHORT_DESC
CHANNEL_LONG_DESC
CHANNEL_SHORT_DESC
PARENT
DEPTH
TOTAL_CHANNEL
CHANNEL

Using OLAP View Columns

The "Creating Basic Queries" topic of Oracle OLAP User's Guide provides several examples of how to create SQL queries using the OLAP views. An OLAP Java API query that uses Source objects automatically uses these views.

You can also provide direct access to the OLAP views to the users of your OLAP Java API application. You could allow users to specify a SQL SELECT statement that uses the views and then send that SQL query to the database.

Example 2-4 reproduces Example 4-2 of Oracle OLAP User's Guide except that it uses the cubes and dimensions of the GLOBAL_AWJ analytic workspace. The example selects the Sales measure from UNITS_CUBE_AWJ_VIEW, and joins the keys from the cube view to the hierarchy views to select the data.

In the example, mdmDBSchema is the MdmDatabaseSchema for the GLOBAL user. The example is an excerpt from the BasicCubeViewQuery.java example program.

// In a method...
  // Get the cube.
  MdmCube mdmUnitsCube =
     MdmCube)mdmDBSchema.getTopLevelObject("UNITS_CUBE_AWJ");
  // Get the OLAP view for the cube.
  String cubeViewName = mdmUnitsCube.getViewName();
  // Display the name of the OLAP view for the cube.
  println("The name of the OLAP view for the " + mdmUnitsCube.getName()
          + " cube is:\n  " + cubeViewName);

  // Get the dimensions and the hierarchies of the dimensions.
  MdmPrimaryDimension mdmTimeDim =
   (MdmPrimaryDimension)mdmDBSchema.getTopLevelObject("TIME_AWJ");
  MdmLevelHierarchy mdmCalHier =
    mdmTimeDim.findOrCreateLevelHierarchy("CALENDAR_YEAR");

  // Display the name of the OLAP view name for the hierarchy and 
  // display the names of the hierarchy levels.
  displayViewAndLevelNames(mdmCalHier);

  MdmPrimaryDimension mdmProdDim =
   (MdmPrimaryDimension)mdmDBSchema.getTopLevelObject("PRODUCT_AWJ");
  MdmLevelHierarchy mdmProdHier =     mdmProdDim.findOrCreateLevelHierarchy("PRODUCT_PRIMARY");
  displayViewAndLevelNames(mdmProdHier);

  MdmPrimaryDimension mdmCustDim =
   (MdmPrimaryDimension)mdmDBSchema.getTopLevelObject("CUSTOMER_AWJ");
  MdmLevelHierarchy mdmShipHier =
    mdmCustDim.findOrCreateLevelHierarchy("SHIPMENTS");
  displayViewAndLevelNames(mdmShipHier);

  MdmPrimaryDimension mdmChanDim =
   (MdmPrimaryDimension)mdmDBSchema.getTopLevelObject("CHANNEL_AWJ");
  MdmLevelHierarchy mdmChanHier =
    mdmChanDim.findOrCreateLevelHierarchy("CHANNEL_PRIMARY");
  displayViewAndLevelNames(mdmChanHier);

  // Create a SQL SELECT statement using the names of the views and the
  // levels.
  // UNITS_CUBE_AWJ_VIEW has a column named SALES for the sales measure.
  // TIME_AWJ_CALENDAR_YEAR_VIEW has a column named LONG_DESCRIPTION
  // for the long description attribute.
  // The hierarchy views have columns that have the same names as the levels.
  String sql = "SELECT t.long_description time,\n" +
                    "     ROUND(f.sales) sales\n" +
                    "  FROM TIME_AWJ_CALENDAR_YEAR_VIEW t,\n" +
                    "     PRODUCT_AWJ_PRODUCT_PRIMA_VIEW p,\n" +
                    "     CUSTOMER_AWJ_SHIPMENTS_VIEW cu,\n" +
                    "     CHANNEL_AWJ_CHANNEL_PRIMA_VIEW ch,\n" +
                    "     UNITS_CUBE_AWJ_VIEW f\n" +
                    "  WHERE t.level_name = 'YEAR'\n" +
                    "     AND p.level_name = 'TOTAL_PRODUCT'\n" +
                    "     AND cu.level_name = 'TOTAL_CUSTOMER'\n" +
                    "     AND ch.level_name = 'TOTAL_CHANNEL'\n" +
                    "     AND t.dim_key = f.time_awj\n" +
                    "     AND p.dim_key = f.product_awj\n" +
                    "     AND cu.dim_key = f.customer_awj\n" +
                    "     AND ch.dim_key = f.channel_awj\n" +
                    "  ORDER BY t.end_date";

  // Display the SQL SELECT statement.
  println("\nThe SQL SELECT statement is:\n" + sql);

  // Display the results of the SQL query.
  String title = "\nThe results of the SQL query are:\n";
  executeSQL(sql, title);
// ...
} // End of method.

private void displayViewAndLevelNames(MdmLevelHierarchy mdmLevelHier)
{
  // Get the OLAP view name for the hierarchy.
  String levelHierViewName = mdmLevelHier.getETViewName();
  // Display the name of the OLAP view for the hierarchy.
  println("\nThe OLAP view for the " + mdmLevelHier.getName() + 
          " hierarchy is:\n  " + levelHierViewName);
  
  // Display the names of the levels of the hierarchy.
  displayLevelNames(mdmLevelHier);
}
 
private void displayLevelNames(MdmLevelHierarchy mdmLevelHier)
{
  List<MdmHierarchyLevel> mdmHierLevelList = 
    mdmLevelHier.getHierarchyLevels();
 println("The names of the levels of the "
         + mdmLevelHier.getName() + " hierarchy are:");
 for (MdmHierarchyLevel mdmHierLevel :  mdmHierLevelList)
 {
   println("  " + mdmHierLevel.getName());
 }
}

protected void executeSQL(String sql, String heading)
{
  try
  {
    Statement statement = dp.getConnection().createStatement();
    println(heading);
    ResultSet rs = statement.executeQuery(sql);
    SQLResultSetPrinter.printResultSet(getCursorPrintWriter(), rs);
    rs.close();
    statement.close();
  }
  catch (SQLException e)
  {
    println("Could not execute SQL statement. " + e);
  }
}

The output of Example 2-4 is the following.

The name of the OLAP view for the UNITS_CUBE_AWJ cube is:
  UNITS_CUBE_AWJ_VIEW

The OLAP view for the CALENDAR_YEAR hierarchy is:
  TIME_AWJ_CALENDAR_YEAR_VIEW
The names of the levels of the CALENDAR_YEAR hierarchy are:
  TOTAL_TIME
  YEAR
  QUARTER
  MONTH

The OLAP view for the PRODUCT_PRIMARY hierarchy is:
  PRODUCT_AWJ_PRODUCT_PRIMA_VIEW
The names of the levels of the PRODUCT_PRIMARY hierarchy are:
  TOTAL_PRODUCT
  CLASS
  FAMILY
  ITEM

The OLAP view for the SHIPMENTS hierarchy is:
  CUSTOMER_AWJ_SHIPMENTS_VIEW
The names of the levels of the SHIPMENTS hierarchy are:
  TOTAL_CUSTOMER
  REGION
  WAREHOUSE
  SHIP_TO

The OLAP view for the CHANNEL_PRIMARY hierarchy is:
  CHANNEL_AWJ_CHANNEL_PRIMA_VIEW
The names of the levels of the CHANNEL_PRIMARY hierarchy are:
  TOTAL_CHANNEL
  CHANNEL

The SQL SELECT statement is:
SELECT t.long_description time,
     ROUND(f.sales) sales
  FROM TIME_AWJ_CALENDAR_YEAR_VIEW t,
     PRODUCT_AWJ_PRODUCT_PRIMA_VIEW p,
     CUSTOMER_AWJ_SHIPMENTS_VIEW cu,
     CHANNEL_AWJ_CHANNEL_PRIMA_VIEW ch,
     UNITS_CUBE_AWJ_VIEW f
  WHERE t.level_name = 'YEAR'
     AND p.level_name = 'TOTAL_PRODUCT'
     AND cu.level_name = 'TOTAL_CUSTOMER'
     AND ch.level_name = 'TOTAL_CHANNEL'
     AND t.dim_key = f.time_awj
     AND p.dim_key = f.product_awj
     AND cu.dim_key = f.customer_awj
     AND ch.dim_key = f.channel_awj
  ORDER BY t.end_date

The results of the SQL query are:

TIME       SALES                 
---------- ----------------------
1998       100870877             
1999       134109248             
2000       124173522             
2001       116931722             
2002       92515295              
2003       130276514             
2004       144290686             
2005       136986572             
2006       140138317             
2007       <null>      

Using Source Objects

Example 2-4 demonstrates how to create a SQL statement using the OLAP views. You can produce the same results by using OLAP Java API Source objects, as shown in Example 2-5. The code in Example 2-5 uses the MdmLevelHierarchy objects from Example 2-4.

// Get the Sales measure and the Source for it.
MdmBaseMeasure mdmSales = mdmUnitsCube.findOrCreateBaseMeasure("SALES");
NumberSource sales = (NumberSource)mdmSales.getSource();

// Get the Source objects for the PRODUCT_PRIMARY, CHANNEL_PRIMARY
// and the SHIPMENTS hierarchies.
StringSource prodHier = (StringSource)mdmProdHier.getSource();
    StringSource shipHier = (StringSource)mdmShipHier.getSource();  
StringSource chanHier = (StringSource)mdmChanHier.getSource();
 
// Get the YEAR hierarchy level.
List<MdmHierarchyLevel> hierLevels = mdmCalHier.getLevels();
MdmHierarchyLevel mdmYearHierLevel = null;
for(MdmHierarchyLevel mdmHierLevel : hierLevels)
{
  mdmYearHierLevel = mdmHierLevel;
  if(mdmYearHierLevel.getName().equals("YEAR"))
  {
    break;
  }
}
// Get the Source for the Year level of the CALENDAR_YEAR hierarchy.
Source yearLevel = mdmYearHierLevel.getSource();

// Select single values for the hierarchies except for the time hierarchy.
Source prodSel = prodHier.selectValue("PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL");
Source custSel = shipHier.selectValue("SHIPMENTS::TOTAL_CUSTOMER::TOTAL");
Source chanSel = chanHier.selectValue("CHANNEL_PRIMARY::TOTAL_CHANNEL::TOTAL");

// Get the Long Description attribute for the Time dimension.
MdmBaseAttribute mdmTimeLDAttr = (MdmBaseAttribute)
  mdmTimeDim.getValueDescriptionAttribute();
Source timeLDAttr = mdmTimeLDAttr.getSource();

Source yearsWithLDValue = timeLDAttr.join(yearLevel);

Source result = sales.joinHidden(prodSel)
                     .joinHidden(custSel)
                     .joinHidden(chanSel)
                     .join(yearsWithLDValue);

getContext().commit();
getContext().displayResult(result);

The values of the Cursor for the result Source are the following. The code for formatting the values is not shown. For the complete code for Example 2-4 and Example 2-5, see the BasicCubeViewQuery.java example program.

Year  Sales
----  ------------
1998  100870876.58
1999  134109248.15
2000  124173521.55
2001  116931722.03
2002   92515295.02
2003  130276513.86
2004  144290685.55
2005  136986571.96
2006  140138317.39
2007  NA