| Oracle® OLAP Java API Developer's Guide 11g Release 1 (11.1) Part Number B28127-01 |
|
|
View PDF |
| Oracle® OLAP Java API Developer's Guide 11g Release 1 (11.1) Part Number B28127-01 |
|
|
View PDF |
This chapter describes how to create new metadata objects and map them to relational structures or expressions. It describes how to export and import the definitions of the metadata objects to XML templates. It also describes how to associate the objects with an analytic workspace, and how to build the analytic workspace.
This chapter includes the following topics:
The OLAP Java API provides the ability to create persistent metadata objects. The top-level metadata objects exist in the data dictionary of the Oracle Database instance. The API also provides the ability to create transient metadata objects that exist only for the duration of the session. An application can use both types of metadata objects to create queries that retrieve or otherwise use the data in the data store.
Before an OLAP Java API application can create metadata objects, a database administrator must have prepared the Oracle Database instance. The DBA must have set up permanent and temporary tablespaces in the database to support the creation of Oracle OLAP metadata objects and must have granted the privileges that allow the user of the session to create and manage objects. For information on preparing an Oracle Database instance, see Oracle OLAP User's Guide.
A dimensional metadata model typically includes the objects described in Chapter 2, "Understanding OLAP Java API Metadata". For detailed information on designing a dimensional metadata model, see Oracle OLAP User's Guide.
You implement the dimensional model by creating OLAP Java API metadata objects. You use classes in the oracle.olapi.metadata.mapping package to map the metadata objects to relational source objects and to build analytic workspaces. You use classes in the oracle.olapi.syntax package to specify Expression objects that you use in mapping the metadata. You use classes in the oracle.olapi.metadata.deployment package to deploy the metadata objects in an analytic workspace or in a relational database (ROLAP) organization.
The basic steps for implementing the dimensional model as OLAP Java API objects in an analytic workspace are the following:
Create an AW object and MdmPrimaryDimension and MdmCube objects.
Deploy the MdmPrimaryDimension and MdmCube objects to the AW.
Create MdmDimensionLevel, MdmHierarchy, and MdmAttribute objects for each MdmPrimaryDimension, create MdmHierarchyLevel objects to associate MdmDimensionLevel objects with an MdmHierarchy, and create the MdmMeasure and related objects for the MdmCube objects.
Map the metadata objects to the relational sources of the base data.
Commit the Transaction, which creates persistent objects in the database.
Load data into the objects from the relational sources by building the analytic workspace.
The following sections describe these steps. The examples in this chapter are modified excerpts from the BuildAW11g.java example program, which creates and builds an analytic workspace. The program also exports the analytic workspace to an XML template.
An analytic workspace is a container for related dimensional objects. It is represented by the AW class in the oracle.olapi.metadata.deployment package. An analytic workspace is owned by an MdmDatabaseSchema.
Example 4-1 demonstrates getting the MdmDatabaseSchema for the GLOBAL user and creating an AW. For an example that gets the MdmRootSchema, see Chapter 3.
A dimension is a list of unique values that identify and categorize data. Dimensions form the edges of a cube and identify the values in the measures of the cube. A dimension has one or more levels that categorize the dimension members. It can have one or more hierarchies that further categorize the members.
A dimension also has attributes that contain information about dimension members. For descriptions of creating attributes, see the "Creating Attributes" topic.
This section describes how to create objects that represent a dimension and the levels and hierarchies of a dimension.
An OLAP dimension is represented by the MdmPrimaryDimension class. A dimension is owned by an MdmDatabaseSchema. You create a dimension with the findOrCreateTimeDimension or the findOrCreateStandardDimension method of the MdmDatabaseSchema.
Example 4-2 creates a standard dimension that has the name CHANNEL_AWJ. The example creates an AWPrimaryDimensionOrganization object to deploy the dimension in an analytic workspace. The mdmDBSchema and aw objects are created by Example 4-1. The last three lines call the methods of Example 4-3, Example 4-4, and Example 4-8, respectively.
Example 4-2 Creating and Deploying an MdmStandardDimension
MdmStandardDimension mdmChanDim =
mdmDBSchema.findOrCreateStandardDimension("CHANNEL_AWJ");
AWPrimaryDimensionOrganization awChanDimOrg =
mdmChanDim.createAWOrganization(aw, true);
createAndMapDimensionLevels(mdmChanDim);
createAndMapHierarchies();
commit(mdmChanDim);
An MdmDimensionLevel represents the members of a dimension that are at the same level. Typically, the members of a level are in a column in a dimension table in the relational source. A MemberListMap associates the MdmDimensionLevel with the relational source.
Example 4-3 creates two MdmDimensionLevel objects for the CHANNEL_AWJ dimension and maps the dimension levels to the key columns of the GLOBAL.CHANNEL_DIM table. The example also maps the long description attributes for the dimension levels to columns of that table. The long description attribute, chanLongDescAttr, is created by Example 4-5.
Example 4-3 Creating and Mapping an MdmDimensionLevel
private ArrayList<MdmDimensionLevel> dimLevelList = new ArrayList();
private ArrayList<String> dimLevelNames = new ArrayList();
private ArrayList<String> keyColumns = new ArrayList();
private ArrayList<String> lDescColNames = new ArrayList();
private void createAndMapDimensionLevels(MdmPrimaryDimension mdmChanDim)
{
dimLevelNames.add("TOTAL_CHANNEL");
dimLevelNames.add("CHANNEL");
keyColumns.add("GLOBAL.CHANNEL_DIM.TOTAL_ID");
keyColumns.add("GLOBAL.CHANNEL_DIM.CHANNEL_ID");
lDescColNames.add("GLOBAL.CHANNEL_DIM.TOTAL_DSC");
lDescColNames.add("GLOBAL.CHANNEL_DIM.CHANNEL_DSC");
// Create the MdmDimensionLevel and MemberListMap objects.
int i = 0;
for(String dimLevelName : dimLevelNames)
{
MdmDimensionLevel mdmDimLevel =
mdmChanDim.findOrCreateDimensionLevel(dimLevelNames.get(i));
dimLevelList.add(mdmDimLevel);
// Create a MemberListMap for the dimension level.
MemberListMap mdmDimLevelMemListMap =
mdmDimLevel.findOrCreateMemberListMap();
ColumnExpression keyColExp = (ColumnExpression)
SyntaxObject.fromSyntax(keyColumns.get(i),
metadataProvider);
mdmDimLevelMemListMap.setKeyExpression(keyColExp);
mdmDimLevelMemListMap.setQuery(keyColExp.getQuery());
// Create an attribute map for the Long Description attribute.
AttributeMap attrMapLong =
mdmDimLevelMemListMap.findOrCreateAttributeMap(chanLongDescAttr);
// Create an expression for the attribute map.
Expression lDescColExp = (Expression)
SyntaxObject.fromSyntax(lDescColNames.get(i),
metadataProvider);
attrMapLong.setExpression(lDescColExp);
i++;
}
}
An MdmHierarchy represents a hierarchy in the dimensional object model. An MdmHierarchy can be an instance of the MdmLevelHierarchy or the MdmValueHierarchy class. An MdmLevelHierarchy has an ordered list of MdmHierarchyLevel objects that relate MdmDimensionLevel objects to the hierarchy.
Example 4-4 creates a hierarchy for the CHANNEL_AWJ dimension. It creates hierarchy levels for the hierarchy and associates attributes with the hierarchy levels. It also maps the hierarchy levels and the attributes to relational sources. The example uses the ArrayList objects from Example 4-3. It maps the MdmHierarchyLevel objects to the same relational source objects as the MdmDimensionLevel objects are mapped.
Example 4-4 Creating and Mapping MdmLevelHierarchy and MdmHierarchyLevel Objects
private void createAndMapHierarchies()
{
MdmLevelHierarchy mdmLevelHier =
mdmChanDim.findOrCreateLevelHierarchy("CHANNEL_PRIMARY");
// Create the MdmHierarchyLevel and HierarchyLevelMap objects.
int i = 0;
for(String hierLevelName : dimLevelNames)
{
MdmHierarchyLevel mdmHierLevel =
mdmLevelHier.findOrCreateHierarchyLevel(mdmLevelHier.getPrimaryDimension()
.findOrCreateDimensionLevel(dimLevelName));
HierarchyLevelMap hierLevelMap =
mdmHierLevel.findOrCreateHierarchyLevelMap();
ColumnExpression keyColExp = (ColumnExpression)
SyntaxObject.fromSyntax(keyColumns.get(i),
metadataProvider);
hierLevelMap.setKeyExpression(keyColExp);
hierLevelMap.setQuery(keyColExp.getQuery());
//Set the MdmDimensionLevel for the MdmHierarchyLevel.
mdmHierLevel.setDimensionLevel(dimLevelList.get(i));
i++;
}
}
Attributes contain information about dimension members. An MdmBaseAttribute represents values that are based on relational source tables. An MdmDerivedAttribute represents values that Oracle OLAP derives from characteristics or relationships of the dimension members. For example, the getParentAttribute() of an MdmPrimaryDimension returns an MdmDerivedAttribute that records the parent of each dimension member.
You create a base attribute for a dimension with the findOrCreateBaseAttribute method. You specify the data type of the attribute. For some attributes, you make the attribute visible with a method of the dimension like setValueDescriptionAttribute.
Example 4-5 creates a long description attribute for the CHANNEL_AWJ dimension and makes it visible on the dimension.
Example 4-5 Creating an MdmBaseAttribute
private MdmBaseAttribute chanLongDescAttr = null;
private void createLongDesciptionAttribute(MdmPrimaryDimension mdmChanDim)
{
// Create the long description attribute and set the data type for it.
chanLongDescAttr = mdmChanDim.findOrCreateBaseAttribute("LONG_DESCRIPTION");
SQLDataType sdtVC2 = new SQLDataType("VARCHAR2");
chanLongDescAttr.setSQLDataType(sdtVC2);
// Make the attribute visible on the dimension.
mdmChanDim.setValueDescriptionAttribute(chanLongDescAttr);
An attribute can have different values for the members of different levels of the dimension. In that case the attribute has an attribute mapping for each level. Example 4-3 creates an AttributeMap for the long description attribute for each dimension level by calling the findOrCreateAttributeMap method of the MemberListMap for each dimension level. It specifies a different column for each attribute map.
A cube in a dimensional object model is represented by the MdmCube class. An MdmCube owns one or more MdmMeasure objects. It has a list of the MdmPrimaryDimension objects that dimension the measures.
An MdmCube has the following objects associated with it.
MdmPrimaryDimension objects that specify the dimensionality of the cube.
MdmMeasure objects that contain data that is identified by the dimensions.
A CubeOrganization that specifies how the cube stores and manages the measure data.
CubeMap objects that associate the cube with relational sources.
A ConsistentSolveSpecification that specifies how to calculate, or solve, the aggregate level data.
This section has an example that creates a cube and some of the objects associated with it. Example 4-6 creates an MdmCube that has the name PRICE_CUBE_AWJ. The example creates an AWCubeOrganization object to deploy the cube in an analytic workspace. The mdmDBSchema and aw objects are created by Example 4-1 and the leafLevel ArrayList is created in Example 4-4. The mdmTimeDim and mdmProdDim objects are dimensions of time periods and product categories. The BuildAW11g program creates those dimensions. The last lines of the example call the methods in Example 4-7 and Example 4-8, respectively.
Example 4-6 Creating and Mapping an MdmCube
private MdmCube createAndMapCube(MdmPrimaryDimension mdmTimeDim,
MdmPrimaryDimension mdmProdDim)
{
MdmCube mdmPriceCube = mdmDBSchema.findOrCreateCube("PRICE_CUBE_AWJ");
// Add dimensions to the cube.
mdmPriceCube.addDimension(mdmTimeDim);
mdmPriceCube.addDimension(mdmProdDim);
AWCubeOrganization awCubeOrg = mdmPriceCube.createAWOrganization(aw, true);
awCubeOrg.setMVOption(AWCubeOrganization.NONE_MV_OPTION);
awCubeOrg.setMeasureStorage(AWCubeOrganization.SHARED_MEASURE_STORAGE);
awCubeOrg.setCubeStorageType("NUMBER");
AggregationCommand aggCommand = new AggregationCommand("AVG");
ArrayList<ConsistentSolveCommand> solveCommands = new ArrayList();
solveCommands.add(aggCommand);
ConsistentSolveSpecification conSolveSpec =
new ConsistentSolveSpecification(solveCommands);
mdmPriceCube.setConsistentSolveSpecification(conSolveSpec);
// Create and map the measures of the cube.
createAndMapMeasures(mdmPriceCube);
// Commit the Transaction.
commit(mdmPriceCube);
}
This section has an example that creates measures for a cube and maps the measures to fact tables in the relational database. The example uses the cube created by Example 4-6.
Example 4-7 Creating and Mapping Measures
private void createAndMapMeasures(MdmCube mdmPriceCube)
{
ArrayList<MdmBaseMeasure> measures = new ArrayList();
MdmBaseMeasure mdmCostMeasure =
mdmPriceCube.findOrCreateBaseMeasure("UNIT_COST");
MdmBaseMeasure mdmPriceMeasure =
mdmPriceCube.findOrCreateBaseMeasure("UNIT_PRICE");
SQLDataType sdt = new SQLDataType("NUMBER");
mdmCostMeasure.setSQLDataType(sdt);
mdmPriceMeasure.setSQLDataType(sdt);
measures.add(mdmCostMeasure);
measures.add(mdmPriceMeasure);
MdmTable priceCostTable = (MdmTable)
mdmDBSchema.getTopLevelObject("PRICE_FACT");
Query cubeQuery = priceCostTable.getQuery();
ArrayList<String> measureColumns = new ArrayList();
measureColumns.add("GLOBAL.PRICE_FACT.UNIT_COST");
measureColumns.add("GLOBAL.PRICE_FACT.UNIT_PRICE");
CubeMap cubeMap = mdmPriceCube.createCubeMap();
cubeMap.setQuery(cubeQuery);
// Create MeasureMap objects for the measures of the cube and
// set the expressions for the measures. The expressions specify the
// columns of the fact table for the measures.
int i = 0;
for(MdmBaseMeasure mdmBaseMeasure : measures)
{
MeasureMap measureMap = cubeMap.findOrCreateMeasureMap(mdmBaseMeasure);
Expression expr = (Expression)
SyntaxObject.fromSyntax(measureColumns.get(i),
metadataProvider);
measureMap.setExpression(expr);
i++;
}
// Create CubeDimensionalityMap objects for the dimensions of the cube and
// set the expressions for the dimensions. The expressions specify the
// columns of the fact table for the dimensions.
ArrayList<String> factColNames = new ArrayList();
factColNames.add("GLOBAL.PRICE_FACT.MONTH_ID");
factColNames.add("GLOBAL.PRICE_FACT.ITEM_ID");
List<MdmDimensionality> mdmDimltys = mdmPriceCube.getDimensionality();
for (MdmDimensionality mdmDimlty: mdmDimltys)
{
CubeDimensionalityMap cubeDimMap =
cubeMap.findOrCreateCubeDimensionalityMap(mdmDimlty);
MdmPrimaryDimension mdmPrimDim = (MdmPrimaryDimension)
mdmDimlty.getDimension();
String columnMap = null;
if (mdmPrimDim.getName().startsWith("TIME"))
{
columnMap = factColNames.get(0);
i = 0;
}
else// (mdmPrimDim.getName().startsWith("PRODUCT"))
{
columnMap = factColNames.get(1);
i = 1;
}
Expression expr = (Expression)
SyntaxObject.fromSyntax(columnMap,
metadataProvider);
cubeDimMap.setExpression(expr);
// Associate the leaf level of the hierarchy with the cube.
MdmHierarchy mdmDefHier = mdmPrimDim.getDefaultHierarchy();
MdmLevelHierarchy mdmLevHier = (MdmLevelHierarchy)mdmDefHier;
List<MdmHierarchyLevel> levHierList = mdmLevHier.getHierarchyLevels();
// The last element in the list must be the leaf level of the hierarchy.
MdmHierarchyLevel leafLevel = levHierList.get(levHierList.size() - 1);
cubeDimMap.setMappedDimension(leafLevel);
}
}
To save a metadata object as a persistent entity in the database, you must commit the Transaction in which you created the object. You can commit a Transaction at any time. Committing the Transaction after creating a top-level object and the objects that it owns is a good practice.
Example 4-8 gets the TransactionProvider from the DataProvider for the session and commits the current Transaction.
Example 4-8 Committing Transactions
private void commit(MdmSource mdmSource)
{
try
{
System.out.println("Committing the transaction for " +
mdmSource.getName() + ".");
(dp.getTransactionProvider()).commitCurrentTransaction();
}
catch (Exception ex)
{
System.out.println("Could not commit the Transaction. " + ex);
}
}
You can save the definition of a metadata object by exporting the object to an XML template. Exporting an object saves the definition of the object and the definitions of any objects that it owns. For example, if you export an AW object to XML, then the XML includes the definitions of any MdmPrimaryDimension and MdmCube objects that the AW owns, and the MdmAttribute, MdmMeasure and other objects owned by the dimensions and cubes.
Example 4-9 exports metadata objects to an XML template and saves it in a file. The following code calls the exportToXML method. The aw object is the analytic workspace created by Example 4-1.
List objectsToExport = new ArrayList(); objectsToExport.add(aw); exportToXML(objectsToExport, "globalawj.xml");
Example 4-9 Exporting to an XML Template
public void exportToXML(List objectsToExport, String fileName)
{
try
{
PrintWriter writer = new PrintWriter(new FileWriter(filename));
mp.exportFullXML(writer, // mp is the MdmMetadataProvider
objectsToExport,
null, // No Map for renaming objects
false); // Do not include the owner name
writer.close();
}
catch (IOException ie)
{
ie.printStackTrace();
}
}
You can import a metadata object definition as an XML template. After importing, you must build the object.
After creating and mapping metadata objects, or importing the XML definition of an object, you must perform the calculations that the objects specify and load the resulting data into physical storage structures.
Example 4-10 creates BuildItem objects for the dimensions and cubes of the analytic workspace. It creates a BuildProcess that specifies the BuildItem objects and passes the BuildProcess to the executeBuild method of the DataProvider for the session.
Example 4-10 Building an Analytic Workspace
BuildItem bldChanDim = new BuildItem(mdmChanDim);
BuildItem bldProdDim = new BuildItem(mdmProdDim);
BuildItem bldCustDim = new BuildItem(mdmCustDim);
BuildItem bldTimeDim = new BuildItem(mdmTimeDim);
BuildItem bldUnitsCube = new BuildItem(mdmUnitsCube);
BuildItem bldPriceCube = new BuildItem(mdmPriceCube);
ArrayList<BuildItem> items = new ArrayList();
items.add(bldChanDim);
items.add(bldProdDim);
items.add(bldCustDim);
items.add(bldTimeDim);
items.add(bldUnitsCube);
items.add(bldPriceCube);
BuildProcess bldProc = new BuildProcess(items);
try
{
dp.executeBuild(bldProc, 0);
}
catch (Exception ex)
{
System.out.println("Could not execute the BuildProcess." + ex);
}
