| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-01 |
|
|
View PDF |
| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-01 |
|
|
View PDF |
The objects in the MDM model, which is described in Chapter 2, are based on relational tables and views in the data store. Metadata mapping (MTM) objects provide the information that maps the MDM objects to the relational tables and views on which the MDM objects are based. MTM objects are instances of the classes in the oracle.olapi.metadata.mtm package.
Application developers who have extensive experience with the OLAP API and with SQL can investigate, and in some cases create, objects from the MTM classes. For example, they might want to investigate MTM objects in order to discover the tables and columns to which particular MDM objects are mapped. Or they might want to create new objects in order to implement custom MDM objects, such as an MdmMeasure.
This chapter briefly describes the MTM objects, explains key concepts required for understanding them, and provides simple examples of how they can be used. The chapter has the following sections:
For detailed information about the MTM classes, see the Oracle OLAP Java API Reference. For the complete code for the examples in this chapter, see the example programs available from the Overview of the Oracle OLAP Java API Reference.
When an application developer uses Source objects to specify a query and Cursor objects to execute it, Oracle OLAP first identifies the MdmSource objects that correspond to the Source objects, and then identifies the MtmSourceMap objects that correspond to those MDMSource objects. An MtmSourceMap object maps the relationship between an MdmSource and the underlying SQL tables and expressions on which the MdmSource is based.
Oracle OLAP must identify the underlying SQL tables and expressions, because it must generate a SQL SELECT statement for every MdmSource that is referenced in an OLAP API query. The SELECT statements are constructed by the SQL generator component of Oracle OLAP.
The SQL generator tailors a SQL statement to the subclass of MdmSource for which it is generating the SQL code.
For an MdmSubDimension, the SQL statement is based on an MtmDimensionMap. The code includes the following three parts, one on each line.
SELECT select-list-expression FROM source-table ORDER BY expression
For an MdmMeasure, the SQL statement is based on an MtmMeasureMap. The code includes the following two parts, one on each line.
SELECT select-list-expression FROM source-table
An MdmAttribute does not have its own SQL statement. An MdmAttribute is associated with an MdmDimension, and it is based on the table or tables to which the MdmDimension is mapped. The columns for the MdmAttribute are part of the select-list-expression for the SELECT statement on which the MdmDimension is based. An MtmAttributeMap stores information about those columns.
An MdmDimensionMap, MdmMeasureMap, or MdmAttributeMap references the following MTM objects, which hold information about the parts of the generated SQL statement:
An MtmExpression, which identifies an expression that the SQL generator can use as the select-list-expression.
An MtmTabularSource, which identifies a logical table that the SQL generator can use as the source-table in the FROM clause. A source-table can be a table or view, a SELECT statement, or the join of a pair of tables.
An MtmDimensionOrderSpecification, which holds information that the SQL generator can use to construct the expression for the ORDER BY clause of the SELECT statement for an MdmDimension.
For MdmMeasure objects, the SQL generator also uses the following MTM objects:
MtmBaseCube objects, which record the dimensionality of the MdmMeasure objects for one set of the dimension hierarchies of an MtmPartionedCube.
MtmCubeDimensionality objects, which store information about the fact table and dimension tables that must be joined to specify the data of an MdmMeasure. An MtmBaseCube has a set of MtmCubeDimensionality objects, one for each dimension of the measures of the cube.
Instances of the classes in the MTM package provide the information that the SQL generator needs to construct SELECT statements that implement OLAP API queries. The information is recorded in the form of MTM objects, such as MtmExpression, MtmTabularSource, and MtmCube.
As an application developer, you can interrogate MTM objects to discover the underlying relational tables and expressions. In some cases, you can use the information that you have discovered to create new MdmSource objects.
For mapping purposes, every MdmMeasure belongs to a cube, and all of the MdmMeasure objects in a cube have the same dimensionality. That is, the values of the measures are specified by elements of the same set of MdmDimension objects. Thus, when you know the cube to which a measure belongs, you also know its dimensionality. From the point of view of mapping, the MTM model only has to record the dimensionality once for all the measures in a cube.
Dimensions can have multiple hierarchies, and the underlying data can be different for each hierarchy. Therefore, the MTM model emphasizes hierarchy mappings, which are more specific, rather than dimension mappings.
The MTM model also considers the fact that if a cube is made up of dimensions with multiple hierarchies, then the data can be different for each combination of hierarchies. Therefore, such a cube is partitioned into base cubes, each of which represents one hierarchy combination.
For example, the OLAP Catalog has a cube for the UNITS measure and its four dimensions, which are TIME, PRODUCTS, CHANNEL, and CUSTOMER. CUSTOMER has two hierarchies, MARKET_ROLLUP and SHIPMENTS_ROLLUP. The other three dimensions each have only one hierarchy, which are CALENDAR, PRODUCT_ROLLUP, and CHANNEL_ROLLUP. The following are the possible hierarchy combinations for the measures.
CALENDAR, PRODUCT_ROLLUP, CHANNEL_ROLLUP, MARKET_ROLLUP
CALENDAR, PRODUCT_ROLLUP, CHANNEL_ROLLUP, SHIPMENTS_ROLLUP
The MtmPartitionedCube for that cube therefore has two MtmBaseCube objects.
For all of these reasons, the mappings that are recorded by MTM objects are organized by cube and hierarchy, rather than by measure and dimension.
Ordinarily, neither an end-user nor an application developer needs to know the names of the relational columns to which an MdmSource is mapped. However, sometimes this information can be useful. For example, an application developer might want to ask a database administrator (DBA) to change a particular value in a dimension or might want to identify an existing column so that the developer can map a new custom metadata object to it. To discover the columns to which an MdmSource is mapped, you use instances of the concrete subclasses of the MtmObject class.
To identify the columns to which an MdmSource is mapped, you first get the MtmSourceMap for the MdmSource, and then from it you get the MtmTabularSource. From the MtmTabularSource, you get the MtmColumnExpression objects that represent the columns.
Of course, not all MdmSource objects have a specific column that can be mapped. An MdmSource that is mapped to a specific column has an MtmExpression that is implemented as an MtmColumnExpression. The MtmExpression cannot be an MtmCustomExpression or MtmLiteralExpression, because these objects are not based on a specific column. You should be familiar with the data and metadata with which you are working, so that you are not attempting to find specific columns for MdmSource objects that are derived or otherwise specified.
In Example 5-1, mdmProdHier is an MdmLevelHierarchy that represents the default hierarchy of the PRODUCT dimension. The example gets the MtmLevelHierarchyMap for the MdmLevelHierarchy, gets the MtmRdbmsTableOrView that represents the relational table to which the dimension is mapped, and then gets the MtmColumnExpression objects that represent the columns of the table.
Example 5-1 Getting the Columns for an MtmLevelHierarchyMap
MtmLevelHierarchyMap mtmProdHierMap =
(MtmLevelHierarchyMap) mdmProdHier.getLevelHierarchyMap();
MtmRdbmsTableOrView mtmRdbmsTableOrView =
(MtmRdbmsTableOrView) mtmProdHierMap.getTable();
System.out.println("The name of the table is "
+ mtmRdbmsTableOrView.getName());
List mdmProdColumns = mtmRdbmsTableOrView.getColumns();
Iterator mdmProdColItr = mdmProdColumns.iterator();
System.out.println("Its columns are:");
while (mdmProdColItr.hasNext())
{
MtmColumnExpression mtmColExp = (MtmColumnExpression) mdmProdColItr.next();
System.out.println(mtmColExp.getColumnName());
}
The output of the example is the following:
The name of the table is GLOBAL.PRODUCT_DIM Its columns are: TOTAL_PRODUCT_ID CLASS_ID FAMILY_ID ITEM_ID CLASS_DSC FAMILY_DSC ITEM_DSC TOTAL_PRODUCT_DSC ITEM_PACKAGE_ID
In Example 5-2, the mdmShipToLevel object is the MdmLevel that represents the SHIP_TO level of the default MdmLevelHierarchy of the CUSTOMER dimension. The example gets the MtmColumnExpression object that represents the column to which the MdmLevel is mapped and then gets the table that the column is in.
Example 5-2 Getting the Column Mapped To an MdmLevel
MtmLevelMap mtmShipToLevelMap = mdmShipToLevel.getLevelMap();
MtmColumnExpression mtmShipToColumnExp = (MtmColumnExpression)
mtmShipToLevelMap.getLevelExpression();
String shipToLevelColumnName = mtmShipToColumnExp.getColumnName();
System.out.println(shipToLevelColumnName);
MtmRdbmsTableOrView mtmTableWithShipTo = (MtmRdbmsTableOrView)
mtmShipToColumnExp.getTable();
System.out.println(mtmTableWithShipTo.getName());
The example displays the following:
SHIP_TO_ID GLOBAL.CUSTOMER_DIM
In Example 5-3, mdmUnits is an MdmMeasure that represents the UNITS measure. The example gets the MtmMeasureMap for the MdmMeasure, gets the MtmPartitionedCube that represents the cube to which the measure belongs, and gets the base cubes of the MtmPartitionedCube. The base cubes are all instances of MtmUnsolvedCube.
For the first base cube, the example gets the MtmRdbmsTableOrView that represents the relational table to which the dimension is mapped, which is the GLOBAL.UNITS_HISTORY_FACT table, and then gets the MtmColumnExpression objects that represent the columns of the table.
The other base cube of the partitioned cube represents the other combination of dimension hierarchies for the cube. All of the base cubes are mapped to the same table.
Example 5-3 Getting the Columns For an MdmMeasure
MtmMeasureMap mtmMeasureMap = mdmUnits.getMeasureMap();
MtmPartitionedCube mtmPCube = (MtmPartitionedCube) mtmMeasureMap.getCube();
List baseCubes = mtmPCube.getBaseCubes();
MtmUnsolvedCube mtmFirstBaseCube = (MtmUnsolvedCube) baseCubes.get(0);
MtmRdbmsTableOrView mtmRdbmsTableorView =
(MtmRdbmsTableOrView) mtmFirstBaseCube.getTable();
System.out.println("The name of the table is " +
mtmRdbmsTableorView.getName());
List columns = mtmRdbmsTableorView.getColumns();
Iterator colItr = columns.iterator();
System.out.println("Its columns are:"
while (colItr.hasNext())
{
MtmColumnExpression mtmColExpr = (MtmColumnExpression) colItr.next();
System.out.println(mtmColExpr.getColumnName());
}
The example displays the following:
The name of the table is GLOBAL.UNITS_DETAIL_FACT Its columns are: CHANNEL_ID SHIP_TO_ID ITEM_ID MONTH_ID UNITS
Using the MTM mapping objects, you can create a custom metadata objects, such as an MdmMeasure, that exists only for the life of your MdmMetadataProvider. A custom MdmMeasure must be assigned to an existing MtmCube.
To create a custom measure, you start with an existing MdmMeasure that has the dimensionality that you want your custom MdmMeasure to have. Oracle OLAP assigns the new MdmMeasure to the MtmCube to which the existing MdmMeasure belongs, and creates it within the scope of your current MdmMetadataProvider.
Complete the following steps to create the custom measure:
Call the getMeasureMap method of the existing MdmMeasure, which returns the MtmMeasureMap for the MdmMeasure.
Call the getCube method of the MtmMeasureMap, which returns the MtmPartitionedCube for the MtmMeasureMap.
Call the getMdmCustomObjectFactory method of your MdmMetadataProvider, which returns an MdmCustomObjectFactory.
Call a method of the MdmCustomObjectFactory that creates a new MtmExpression.
Call a method of the MdmCustomObjectFactory that accepts the MtmCube and MtmExpression as parameters and returns a new custom MdmMeasure.
Example 5-4 demonstrates these steps. It creates a custom MdmMeasure that is based on the RDBMS column to which an existing MdmMeasure is mapped. In the example, the existing MdmMeasure is mdmUnitPrice and mp is the MdmMetadataProvider. The MdmMeasure is based on the UNIT_PRICE column of the PRICE_AND_COST_HISTORY_FACT table of the relational Global schema.
Example 5-4 Creating a Custom Measure
MtmMeasureMap mtmUnitPriceMap = mdmUnitPrice.getMeasureMap();
MtmPartitionedCube mtmUnitPricePartCube = (MtmPartitionedCube)
mtmUnitPriceMap.getCube();
MdmCustomObjectFactory mdmCustObjFactory = mp.getMdmCustomObjectFactory();
FundamentalMetadataProvider fdp = dp.getFundamentalMetadataProvider();
FundamentalMetadataObject numberFMO = fdp.getNumberDataType();
MtmCustomExpression mtmCustExp =
mdmCustObjFactory.createCustomExpression("UNIT_PRICE - UNIT_COST",
numberFMO);
MdmMeasure mdmCustMeasure =
mdmCustObjFactory.createNumericMeasure("MARKUP",
mtmUnitPricePartCube,
mtmCustExp);
The way in which an MdmSource is mapped by MTM objects depends on the way its underlying data is specified (the data might be solved or unsolved) as well as the form in which the data is aggregated (grouping set, rollup, or embedded totals form). An understanding of these storage and aggregation concepts can be useful when you peruse the MTM classes. Classes such as MtmSolvedETCubeDimensionality and MtmUnsolvedLevelHierarchyMap encapsulate the storage and aggregation types.
Typically, the data that is analyzed using the OLAP API is structured hierarchically. Detailed (leaf-level) data is at the lowest level of the hierarchy, and aggregate data is at higher levels of the hierarchy. A hierarchy is one of two types:
A level hierarchy, in which each element belongs to a level and the parent-child relationships are organized by level. A level-based hierarchy can have up to 31 levels.
A value hierarchy, in which each element participates in parent-child relationships but there are no levels in the logical organization. (However, in the MTM model, a logical value hierarchy is stored in a level-based form.)
The detail data is ordinarily specified by a DBA in relational tables, materialized views, or an analytic workspace. However, the aggregate data might, or might not, be specified by the DBA. Aggregate data that is not specified by the DBA must be calculated by Oracle OLAP.
If all the data for a cube is specified by the DBA, then the cube is considered to be solved. If some or all of the aggregate data must be calculated by Oracle OLAP, then the cube is unsolved.
Note that the data for a solved cube is not necessarily stored in the database. It might be specified by the DBA as a materialized view, which is calculated when necessary. The distinction between solved and unsolved cubes rests on who specifies the data: the DBA, or Oracle OLAP.
It is not only cubes that can be either solved or unsolved. Hierarchies can be solved or unsolved as well. If all of the elements of a hierarchy, both aggregate and detailed, exist in a single table or view, then the hierarchy is solved. If some or all of the aggregate elements must be collected by Oracle OLAP from separate tables, then the hierarchy is unsolved.
There are three possible forms in which data for a cube can be aggregated. For a solved cube, the DBA specified the method of aggregation, so the SQL statement that is constructed by the SQL generator does not have to reflect the aggregation form. However, for an unsolved cube, the Oracle OLAP SQL generator constructs a SQL statement that is appropriate to the aggregation form.
The following forms are supported. Each is described in terms of a SQL statement, though it might be specified by a DBA for a solved cube or by the SQL generator for an unsolved cube.
Grouping set aggregation form. The SQL statement uses the GROUP BY GROUPING SETS syntax to aggregate the data for each level combination. The select list includes all of the level expressions as well as a GROUPING_ID expression for each hierarchy.
Rollup aggregation form. The SQL statement uses the GROUP BY ROLLUP syntax to aggregate the data for each level combination. The select list includes all of the level expressions as well as a GROUPING_ID expression for each hierarchy.
ET aggregation form. The SQL statement uses the GROUP BY ROLLUP syntax to aggregate the data for each level combination. However, only the ET and GROUPING_ID expression for each hierarchy are placed in the SELECT list.
The SQL expressions for the three aggregation forms are described in more detail in the rest of this section. For information about GROUPING_ID expressions, ET expressions, and types of aggregation, see the Oracle OLAP Application Developer's Guide.
Using the aggregation form for a given cube, the SQL generator constructs an appropriate SQL statement. (Note that the getAggregationForm method of an MtmUnsolvedCube returns its aggregation form.)
In all cases, the SQL statement aggregates the higher-level values from the detailed level (leaf-level) data. The statement has the following structure.
SELECT SUM(measure1), SUM(measure2), ..., SUM(measureN), dimension-keys FROM fact-table, dimension-tables WHERE join-condition GROUP BY group-by-clause
For example, assume a single hierarchy with three levels: Y as the top level, Q as the middle level, and M as the bottom level. The GROUP BY clause is one of the following, depending on the aggregation form:
GROUP BY clause for grouping set aggregation:
GROUP BY GROUPING SETS((Y), (Q), (M))
GROUP BY clause for rollup aggregation:
GROUP BY Y, ROLLUP(Q, M)
GROUP BY clause for ET aggregation:
GROUP BY Y, ROLLUP(Q, M)
Using the same example, the dimension-keys component of the select list is one of the following, depending on the aggregation form:
dimension-keys for grouping set aggregation:
SELECT Y, Q, M, GROUPING_ID(Y, Q, M)
dimension-keys for rollup aggregation:
SELECT Y, Q, M, GROUPING_ID(Y, Q, M)
dimension-keys for ET aggregation:
SELECT
(CASE GROUPING_ID(Y, Q, M)
WHEN 3 THEN Y
WHEN 1 THEN Q
ELSE M
END) et_value,
GROUPING_ID(Y, Q, M)
In general, for grouping set or rollup form, the dimension-keys component is made up of one expression for each level and one GROUPING ID expression. For ET form, the dimension-keys component is made up of an ET expression and a GROUPING ID expression.
Note that, in all cases, the join-condition in the generated SQL statement is determined by the MtmUnsolvedCubeDimensionality object that is associated with the MtmUnsolvedCube.
All of the values, both detail and aggregate, for a solved cube are explicitly specified by a DBA. Therefore, the SELECT statement that is generated by the Oracle OLAP SQL generator is relatively simple, and it has the same structure for all aggregation forms. Using the same example, the SELECT statement would be the following.
SELECT Y, Q, M, dimension-keys FROM source-table
The dimension-keys component has the same make up as it does for unsolved cubes, varying by the aggregation form that the DBA used.
The DBA specifies the aggregation form for a solved cube when he or she is setting up metadata using Oracle Enterprise Manager or the SQL procedures provided by Oracle for working with the OLAP Catalog.
The DBA specifies one of the following two storage types:
ET (Embedded Totals). The fact table for the cube includes all of the aggregated values for the associated measures. Therefore, materialized views are not required. DBAs can create cubes with the ET storage type when they use the SQL procedures for working with the OLAP Catalog.
Lowest Level. The fact table for the cube includes only the detailed (leaf-level) data. Aggregated values must be supplied using materialized views. When DBAs create cubes using the OLAP Management tool in Oracle Enterprise Manager, the cubes are created with lowest level storage type. Using a different tool, the DBA can specify one of the following forms of materialized view for aggregating data:
Grouping Set form, in which all the hierarchy combinations are in a single materialized view. This form is created when the DBA uses the DBMS_ODM package procedures.
Rolled Up form, in which there is a separate materialized view for each hierarchy combination. This form is created when the DBA uses the OLAP Summary Adviser in Oracle Enterprise Manager.
Thus, there are three aggregation forms, which correspond to the following three concrete subclasses of the MtmSolvedCubeDimensionality class.
A term that recurs in the methods of the MTM classes is GID, which stands for Grouping ID. It refers to the GID column of the fact table for a cube. The GID column, which is derived from the level columns in the fact table, identifies the level associated with each value in a hierarchy. The values of a GID column are calculated by assigning a zero to each non-null value and a one to each null value in the level columns. The resulting binary number is the value of the GID. Hierarchy values that have the same GID are in the same level.
For more information about storage types, aggregation forms, and GID columns, see the Oracle OLAP Application Developer's Guide.
In addition to the aggregation form, an MtmUnsolvedCube has an MtmSolveSpecification, which records the SQL operation that Oracle performs when it aggregates the measure data specified by a dimension, and the order in which Oracle aggregates the dimensions of the measure. An MtmAggregationSpecification, which is a subclass of MtmSolveSpecification, has one or more MtmAggregationStep objects.
An MtmAggregationStep specifies the SQL function and other aspects of operations to perform when Oracle aggregates the values of the measures of an MtmUnsolvedCube for the dimension hierarchies that of the MtmDimensionMap objects of the MtmAggregationStep. The MtmDimensionMap objects of an MtmUnsolvedCube are always instances of MtmUnsolvedLevelHierarchyMap.
Each MtmUnsolvedLevelHierarchyMap of the MtmUnsolvedCube is associated with one and only one MtmAggregationStep. An MtmAggregationStep can specify the aggregation operations for one or more of the MtmUnsolvedLevelHierarchyMap objects.
The default aggregation function is SUM. For an MtmSimpleAggregationStep, you can specify other SQL group functions or your own function. You can create a simple aggregation step or other types of aggregation steps with methods of an MtmObjectFactory, which you get from your MdmMetadataProvider.
The other types of aggregation steps are MtmFirstLastAggregationStep, MtmWeightedAverageStep, and MtmNoAggregationStep. An MtmFirstLastAggregationStep represents an aggregation that uses the SUM function and uses the measure data specified by the first or last child element of the current parent element as the aggregate measure value for the parent element. An MtmWeightedAverageStep specifies the AVG function with a weighting factor applied to the aggregation. An MtmNoAggregationStep specifies that no aggregation occur for the dimension hierarchy or hierarchies.
