| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-02 |
|
|
View PDF |
| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-02 |
|
|
View PDF |
This chapter introduces Source objects, which you use to specify a query. With a Source, you specify the data that you want to retrieve from the data store and the analytical or other operations that you want to perform on the data. Chapter 6, "Making Queries Using Source Methods", provides examples of using Source objects. Using Template objects to make modifiable queries is discussed in Chapter 10, "Creating Dynamic Queries".
This chapter includes the following topics:
For information on how to get the complete code for most of the examples in this chapter, see the topic "Sample Schema for OLAP API Examples" in Chapter 1.
After you have used the classes in the oracle.olapi.metadata.mdm package to get MdmSource objects that represent OLAP metadata measures and dimensions, you can get Source objects from them. You can also create other Source objects with methods of a DataProvider. You can then use the Source objects to create a query that specifies the data that you want to retrieve from the database. To retrieve the data, you create a Cursor for the Source.
With the methods of a Source, you can specify selections of dimension or measure values and specify operations on the elements of the Source, such as mathematical calculations, comparisons, and ordering, adding or removing elements of a query. The Source class has a few basic methods and many shortcut methods that use one or more of the basic methods. The most complex basic methods are the join(Source joined, Source comparison, int comparisonRule, boolean visible) method and the recursiveJoin(Source joined, Source comparison, Source parent, int comparisonRule, boolean parentsFirst, boolean parentsRestrictedToBase, int maxIterations, boolean visible) method. The many other signatures of the join and recursiveJoin methods are shortcuts for certain operations of the basic methods.
In this chapter, the information about the join method applies equally to the recursiveJoin method, except where otherwise noted. With the join method, you can select elements of a Source and, most importantly, you can relate the elements of one Source to those of another Source. For example, to specify the dimension members that retrieving the data of a measure requires, you use a join method to relate the dimension to the measure.
A Source has certain characteristics, such as a type and a data type, and it sometimes has one or more inputs or outputs. This chapter describes these concepts. It also describes the different kinds of Source objects and how you get them, the join method and other Source methods, and how you use those methods to specify a query.
The kinds of Source objects that you use to specify data and to perform analysis, and the ways that you get them, are the following:
Primary Source objects, which are returned by the getSource method of an MdmSource object such as an MdmDimension or an MdmMeasure. A primary Source provides access to the data that the MdmSource represents. Getting primary Source objects is usually the first step in creating a query. You then typically select elements from the primary Source objects, thereby producing derived Source objects.
Derived Source objects, which you get by calling some of the methods of a Source object. Methods such as join return a new Source that is based on the Source on which you call the method. All queries on the data store, other than a simple list of values specified by the primary Source for an MdmSubdimension, such as an MdmLevelHierarchy or an MdmLevel, are derived Source objects.
Fundamental Source objects, which are returned by the getSource method of a FundamentalMetadataObject. These Source objects represent the OLAP API data types.
List or range Source objects, which are returned by the createConstantSource, createListSource or createRangeSource methods of a DataProvider. Typically, you use this kind of Source as the joined or comparison parameter to a join method.
Empty, null, or void Source objects. Empty and void Source objects are returned by the getEmptySource or getVoidSource method of a DataProvider, and null Source objects are returned by the nullSource method of a Source. An empty Source has no elements. A void or null Source has one element that has the value of null. The difference between them is that the type of a void Source is the FundamentalMetadataObject for the Value data type, and the type of a null Source is the Source whose nullSource method returned it. Typically, you use these kinds of Source objects as the joined or comparison parameter to a join method.
Dynamic Source objects, which are returned by the getSource method of a DynamicDefinition. A dynamic Source is usually a derived Source. It is generated by a Template, which you use to create a dynamic query that you can revise after interacting with an end user.
Parameterized Source objects, which are returned by the createParameterizedSource methods of a DataProvider. Like a list or range Source, you use a parameterized Source as a parameter to the join method. Unlike a list or range Source, however, you can change the value that the Parameter represents after the join operation and thereby change the selection that the derived Source represents. You can create a Cursor for that derived Source and retrieve the results of the query. You can then change the value of the Parameter, and, without having to create a new Cursor for the derived Source, use that same Cursor to retrieve the results of the modified query.
Placeholder Source objects, which are returned by the getSource method of the FundamentalMetadataObject that represents a placeholder for a specific data type. You get the FundamentalMetadataObject for a placeholder with methods of a FundamentalMetadataProvider such as the getNumberPlaceholder or getStringPlaceholder methods. Oracle OLAP uses placeholder Source objects in Assignment objects in an MdmModel or CustomModel. In an Assignment, a placeholder Source represents the Source for the current dimensioned Source to which the value is being assigned. You can use a placeholder Source in creating a custom dimension member and Oracle OLAP automatically adds an Assignment to the appropriate Model.
The Source class has the following subclasses:
BooleanSource
DateSource
NumberSource
StringSource
These subclasses have different data types and implement Source methods that require those data types. Each subclass also implements methods unique to it, such as the implies method of a BooleanSource or the indexOf method of a StringSource.
A Source has a data type and a type, a Source identification (ID), and a SourceDefinition. This topic describes these concepts. Some Source objects have one or more inputs or outputs. Those complex concepts are discussed in the "Inputs and Outputs of a Source" topic. Some Source objects have an associated Model object, which is discussed in the "Model Objects and Source Objects" topic.
As described in Chapter 2, "Understanding OLAP API Metadata", the OLAP API has a class, FundamentalMetadataObject, that represents the data type of the elements of an MdmSource. The data type of a Source is represented by a fundamental Source. For example, a BooleanSource has elements that have Java boolean values. The data type of a BooleanSource is the fundamental Source that represents OLAP API Boolean values.
To get the fundamental Source that represents the data type of a Source, call the getDataType method of the Source. You can also get a fundamental Source by calling the getSource method of a FundamentalMetadataObject.
Example 5-1 demonstrates getting the fundamental Source for the OLAP API String data type, the Source for the data type of an MdmPrimaryDimension, and the Source for the data type of the Source for the MdmPrimaryDimension, and comparing them to verify that they are all the same object. In the example, dp is the DataProvider and mdmProdDim is the MdmPrimaryDimension for the Product dimension.
Example 5-1 Getting the Data Type of a Source
FundamentalMetadataProvider fmp = dp.getFundamentalMetadataProvider();
FundamentalMetadataObject fmoStringDataType = fmp.getStringDataType();
Source stringDataTypeSource = fmoStringDataType.getSource();
FundamentalMetadataObject fmoMdmProdDimDataType =
mdmProdDim.getDataType();
Source mdmProdDimDataTypeSource = fmoMdmProdDimDataType.getSource();
Source prodDim = mdmProdDim.getSource();
Source prodDimDataTypeSource = prodDim.getDataType();
if(stringDataTypeSource == prodDimDataTypeSource &&
mdmProdDimDataTypeSource == prodDimDataTypeSource)
println("The Source objects for the data types are the same.");
else
println("The Source objects for the data types are not the same.");
The example displays the following:
The Source objects for the data types are the same.
Along with a data type, a Source has a type, which is the Source from which the elements of the Source are drawn. The type of a Source determines whether the join method can match the Source to an input of another Source. The only Source that does not have a type is the fundamental Source for the OLAP API Value data type, which represents the set of all values, and from which all other Source objects ultimately descend.
The type of a fundamental Source is the data type of the Source. The type of a list or range Source is the data type of the values of the elements of the list or range Source.
The type of a primary Source is one of the following:
The fundamental Source that represents the data type of the values of the elements of the primary Source. For example, the Source returned by getSource method of a typical MdmMeasure is the fundamental Source that represents the set of all OLAP API number values.
The Source for the MdmSource of which the MdmSource of the primary Source is a component. For example, the type of the Source returned by the getSource method of an MdmLevelHierarchy is the Source for the MdmPrimaryDimension of which the hierarchy is a component.
The type of a derived Source is one of the following:
The base Source, which is the Source whose method returned the derived Source. A Source returned by the alias, extract, join, recursiveJoin, or value methods, or one of their shortcuts, has the base Source as the type. An exception is the derived Source returned by the distinct method, whose type is the type of the base Source rather than the base Source itself.
A fundamental Source. Methods such as position and count return a Source the type of which is the fundamental Source for the OLAP API Integer data type. Methods that make comparisons, such as eq, le, and so on, return a Source the type of which is the fundamental Source for the Boolean data type. Methods that perform aggregate functions, such as the NumberSource methods total and average, return as the type of the Source a fundamental Source that represents the function.
You can find the type by calling the getType method of a Source.
A Source derived from another Source is a subtype of the Source from which it is derived. You can use the isSubtypeOf method to determine if a Source is a subtype of another Source.
For example, in Example 5-2 the myList object is a list Source. The example uses myList to select values from prodHier, a Source for the default MdmLevelHierarchy of the MdmPrimaryDimension for the Product dimension. In the example, dp is the DataProvider.
Example 5-2 Using the isSubtypeOf Method
Source myList = dp.createListSource(new String[] {
"PRODUCT_PRIMARY_AW::FAMILY_AW::4",
"PRODUCT_PRIMARY_AW::FAMILY_AW::5",
"PRODUCT_PRIMARY_AW::FAMILY_AW::7",
"PRODUCT_PRIMARY_AW::FAMILY_AW::8"});
Source prodSel = prodHier.selectValues(myList);
if (prodSel.isSubtypeOf(prodHier))
println("prodSel is a subtype of prodHier.");
else
println("prodSel is not a subtype of prodHier.");
Because prodSel is a subtype of prodHier, the condition in the if statement is true and the example displays the following:
prodSel is a subtype of prodHier.
The type of both myList and prodHier is the fundamental String Source. The type of prodSel is prodHier because the elements of prodSel are derived from the elements of prodHier.
The supertype of a Source is the type of the type of a Source, and so on, up through the types to the Source for the fundamental Value data type. For example, the fundamental Value Source is the type of the fundamental String Source, which is the type of prodHier, which is the type of prodSel. The fundamental Value Source and the fundamental String Source are both supertypes of prodSel. The prodSel Source is a subtype of prodHier, and of the fundamental String Source, and of the fundamental Value Source.
A Source has an identification, an ID, which is a String that uniquely identifies it during the current connection to the database. You can get the identification by calling the getID method of a Source. For example, the following code gets the identification of the Source for the MdmPrimaryDimension for the Product dimension and displays the value.
println("The Source ID of prodDim is " + prodDim.getID());
The preceding code displays the following:
The Source ID of prodDim is Hidden..D_GLOBAL_AW.PRODUCT_AW
The text displayed by Example 5-9 has several examples of Source identifications.
Each Source has a SourceDefinition object, which records information about the Source. The different kinds of Source objects have different kinds of SourceDefinition objects. For example, the fundamental Source for an MdmPrimaryDimension has an MdmSourceDefinition, which is a subclass of HiddenDefinition, which is a subclass of SourceDefinition.
The SourceDefinition of a Source that is produced by a call to the join method is an instance of the JoinDefinition class. From a JoinDefinition you can get information about the parameters of the join operation that produced the Source, such as the base Source, the joined Source, the comparison Source, the comparison rule, and the value of the visible parameter.
The inputs and the outputs of a Source are complex and powerful aspects of the class. This section describes the concepts of inputs and outputs and provides examples of how they are related.
A Source that has inputs is a dimensioned Source. An input of a Source is also a Source. An input indicates that the values of the dimensioned Source depend upon an unspecified set of values of the input. A Source that matches to the input provides the values that the input requires. You match an input to a dimensioned Source by using the join method. For information on how to match a Source to an input, see "Matching a Source To an Input".
Certain Source objects always have one or more inputs. They are the Source objects for the MdmDimensionedObject subclasses MdmMeasure and MdmAttribute. They have inputs because the values of a measure or attribute are specified by the values of their dimensions. The inputs of the Source for the measure or attribute are the Source objects for the dimensions of the measure or the attribute. Before you can retrieve the data for a measure or an attribute, you must match each input to a Source that provides the required values.
Some Source methods produce a Source that has an input. You can produce a Source that has an input by using the extract, position, or value methods. These methods provide a means of producing a Source whose elements are a subset of the elements of another Source. A Source produced by one of these methods has the base Source as an input.
For example, in the following code, the base Source is prodHier. The value method produces prodHierValues, which has prodHier as an input.
Source prodHierValues = prodHier.value();
The input provides the means to select values from prodHier, as demonstrated by Example 5-2. The selectValues method in Example 5-2 is a shortcut for the following join method.
Source prodSel = prodHier.join(prodHier.value(),
myList,
Source.COMPARISON_RULE_SELECT,
false);
The parameters of the join method specify the elements of the base Source that appear in the resulting Source. In the example, the joined parameter is the Source produced by the prodHier.value() method. The resulting unnamed Source has prodHier as an input. The input is matched by the base of the join method, which is also prodHier. The result of the join operation, prodSel, has the values of prodHier that match the values of prodHier that are in the comparison Source, myList.
If the joined Source were prodHier and not the Source produced by prodHier.value(), then the comparison would be between the Source object itself and the values of the comparison Source and not between the values of the Source and the values of the comparison Source. Because the joined Source object does not match any of the values of the comparison Source, the result of the join method would have all of the elements of prodHier instead of having only the values of prodHier that are specified by the values of the joined Source that match the values of the comparison Source as specified by the comparison rule.
The input of a Source produced by the position or value method, and an input intrinsic to an MdmDimensionedObject, are regular inputs. A regular input causes the join method, when it matches a Source to the input, to compare the values of the comparison Source to the values of the Source that has the input rather than to the input Source itself.
The input of a Source produced by the extract method is an extraction input. An extraction input differs from a regular input in that, when a value of the Source that has the extraction input is a Source, the join method extracts the values of the Source that is a value of the Source that has the input. The join method then compares the values of the comparison Source to the extracted values rather than to the Source itself.
A Source can have from zero to many inputs. You can get all of the inputs of a Source by calling the getInputs method, the regular inputs by calling the getRegularInputs method, and the extraction inputs by calling the getExtractionInputs method. Each of those methods returns a Set of Source objects.
The join method returns a Source that has the elements of the base Source that are specified by the parameters of the method. If the value of the visible parameter is true, then the joined Source becomes an output of the returned Source. An output of a Source returned by the join method has the elements of the joined Source that specify the elements of the returned Source. An output is a means of identifying the elements of the joined Source that specify the elements of the Source that has the output.
A Source can have from zero to many outputs. You can get the outputs of a Source by calling the getOutputs method, which returns a List of Source objects.
A Source with more than one output has one or more elements for each set of the elements of the outputs. For example, a Source that represents a measure that has had all of the inputs matched, and has had the Source objects that match the inputs turned into outputs, has a single type element for each set of the elements of the outputs because each data value of the measure is identified by a unique set of the values of the dimensions. A Source that represents dimension values that are selected by some operation performed on the data of a measure, however, might have more than one element for each set of the elements of the outputs. An example is a Source that represents product values that have unit costs greater than a certain amount. Such a Source might have several products for each time period that have a unit cost greater than the specified amount.
Example 5-3 produces a selection of the elements of shipHier, which is a Source for a hierarchy of a dimension of customer values. The customers are grouped by a shipment origination and destination hierarchy.
Example 5-3 Using the join Method To Produce a Source Without an Output
Source custValuesToSelect = dp.createListSource(new String[]
{"SHIPMENTS_AW::REGION_AW::9",
"SHIPMENTS_AW::REGION_AW::10"});
Source shipHierValues = shipHier.value();
Source custSel = shipHier.join(shipHierValues,
custValuesToSelect,
Source.COMPARISON_RULE_SELECT,
false);
The shipHierValues Source has an input of shipHier. In the join method in the example, the base Source, shipHier, matches the input of the joined Source, shipHierValues because the base and the input are the same object. The join method selects the elements of the base shipHier whose values match the values of the joined shipHier that are specified by the comparison Source, custValuesToSelect. The method produces a Source, custSel, that has only the selected elements of shipHier. Because the visible parameter is false, the joined Source is not an output of custSel. The custSel Source therefore has only two elements, the values of which are SHIPMENTS_AW::REGION_AW::9 and SHIPMENTS_AW::REGION_AW::10.
You produce a Source that has an output by specifying true as the visible parameter to the join method. Example 5-4 joins the Source objects for the dimension selections from Example 5-2 and Example 5-3 to produce a Source, custSelByProdSel, that has one output. The custSelByProdSel Source has the elements from custSel that are specified by the elements of prodSel.
The comparison Source is an empty Source, which has no elements and which is the result of the getEmptySource method of the DataProvider, dp. The comparison rule value, COMPARISON_RULE_REMOVE, selects only the elements of prodSel that are not in the comparison Source. Because the comparison Source has no elements, all of the elements of the joined Source are selected. Each of the elements of the joined Source specify all of the elements of the base Source. The resulting Source, custSelByProdSel, therefore has all of the elements of custSel.
Because the visible parameter is true in Example 5-4, prodSel is an output of custSelByProdSel. Therefore, for each element of the output, custSelByProdSel has the elements of custSel that are specified by that element of the output. Because the custSel and prodSel are both simple lists of dimension values, the result is the cross product of the elements of both Source objects.
Example 5-4 Using the join Method To Produce a Source With an Output
Source custSelByProdSel = custSel.join(prodSel,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true);
To actually retrieve the data specified by custSelByProdSel, you must create a Cursor for it. Such a Cursor contains the values shown in the following table, which has headings added that indicate that the values from the output, prodSel, are in the left column and the values from the elements of the custSelByProdSel Source, which are derived from the type, custSel, are in the right column.
Output Values Type Values ------------------------- ---------------------------- PRODUCT_PRIMARY_AW::FAMILY::4 SHIPMENTS_AW::REGION_AW::10 PRODUCT_PRIMARY_AW::FAMILY::4 SHIPMENTS_AW::REGION_AW::9 PRODUCT_PRIMARY_AW::FAMILY::5 SHIPMENTS_AW::REGION_AW::10 PRODUCT_PRIMARY_AW::FAMILY::5 SHIPMENTS_AW::REGION_AW::9 PRODUCT_PRIMARY_AW::FAMILY::8 SHIPMENTS_AW::REGION_AW::10 PRODUCT_PRIMARY_AW::FAMILY::8 SHIPMENTS_AW::REGION_AW::9 PRODUCT_PRIMARY_AW::FAMILY::7 SHIPMENTS_AW::REGION_AW::10 PRODUCT_PRIMARY_AW::FAMILY::7 SHIPMENTS_AW::REGION_AW::9
The custSelByProdSel Source has two type elements, and the output of the custSelByProdSel has four elements. The number of elements of custSelByProdSel is eight because for this Source, each output element specifies the same set of two type elements.
Each join operation that specifies a visible parameter of true adds an output to the list of outputs of the resulting Source. For example, if a Source has two outputs and you call one of the join methods that produces an output, then the Source that results from the join operation has three outputs. You can get the outputs of a Source by calling the getOutputs method, which returns a List of Source objects.
Example 5-5 demonstrates joining a measure to selections from the dimensions of the measure, thus matching to the inputs of the measure Source objects that provide the required elements. Because the last two join methods match the dimension selections to the inputs of the measure, the resulting Source does not have any inputs. Because the visible parameter in those joins is true, the last join method produces a Source that has two outputs.
Example 5-5 gets the Source for the measure of unit costs. That Source, unitCost, has two inputs, which are the primary Source objects for the Time and Product dimensions, which are the dimensions of unit cost. The example gets the Source objects for level hierarchies of the dimensions, which are subtypes of the Source objects for the dimensions. It produces selections of the level hierarchies and then joins those selections to the measure. The result, unitCostSel, specifies the unit costs of the selected products at the selected times.
Example 5-5 Using the join Method To Match Source Objects To Inputs
Source unitCost = mdmUnitCost.getSource();
Source calendar = mdmCalendar.getSource();
Source prodHier = mdmProdHier.getSource();
Source timeSel = calendar.join(calendar.value(),
dp.createListSource(new String[]
{"CALENDAR_YEAR_AW::MONTH_AW::47",
"CALENDAR_YEAR_AW::MONTH_AW::59"}),
Source.COMPARISON_RULE_SELECT,
false);
Source prodSel = prodHier.join(prodHier.value(),
dp.createListSource(new String[]
{"PRODUCT_PRIMARY_AW::ITEM_AW::13",
"PRODUCT_PRIMARY_AW::ITEM_AW::14",
"PRODUCT_PRIMARY_AW::ITEM_AW::15"}),
Source.COMPARISON_RULE_SELECT,
false);
Source unitCostSel = unitCost.join(timeSel,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true)
.join(prodSel,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true);
The unnamed Source that results from joining timeSel to unitCost has one output, which is timeSel. Joining prodSel to that unnamed Source produces unitCostSel, which has two outputs, timeSel and prodSel. The unitCostSel Source has the elements from the type, unitCost, that are specified by the outputs.
A Cursor for unitCostSel contains the following, displayed as a table with headings added that indicate the structure of the Cursor. A Cursor has the same structure as the associated Source. The unit cost values are formatted as dollar values.
Output 1 Output 2 Type
Values Values Values
------------------------------- ------------------------- --------
PRODUCT_PRIMARY_AW::ITEM_AW::13 CALENDAR_YEAR_AW::MONTH_AW::47 2897.40
PRODUCT_PRIMARY_AW::ITEM_AW::13 CALENDAR_YEAR_AW::MONTH_AW::59 2376.73
PRODUCT_PRIMARY_AW::ITEM_AW::14 CALENDAR_YEAR_AW::MONTH_AW::47 3238.36
PRODUCT_PRIMARY_AW::ITEM_AW::14 CALENDAR_YEAR_AW::MONTH_AW::59 3015.90
PRODUCT_PRIMARY_AW::ITEM_AW::15 CALENDAR_YEAR_AW::MONTH_AW::47 2847.47
PRODUCT_PRIMARY_AW::ITEM_AW::15 CALENDAR_YEAR_AW::MONTH_AW::59 2819.85
Output 1 has the values from prodSel, output 2 has the values from timeSel, and the type values are the values from unitCost that are specified by the output values.
Because these join operations are performed by most OLAP API applications, the API provides shortcuts for these and many other join operations. Example 5-6 uses shortcuts for the join operations in Example 5-5 to produce the same result.
Example 5-6 Using Shortcuts
Source unitCost = mdmUnitCost.getSource();
StringSource calendar = (StringSource) mdmCalendar.getSource();
StringSource prodHier =(StringSource) mdmProdHier.getSource();
Source timeSel = calendar.selectValues(new String[]
{"CALENDAR_YEAR_AW::MONTH_AW::47",
"CALENDAR_YEAR_AW::MONTH_AW::59"}),
Source prodSel = prodHier.selectValues(new String[]
{"PRODUCT_PRIMARY_AW::ITEM_AW::13",
"PRODUCT_PRIMARY_AW::ITEM_AW::14",
"PRODUCT_PRIMARY_AW::ITEM_AW::15"}),
Source unitCostSel = unitCost.join(timeSel).join(prodSel);
In a join operation, a Source-to-input match occurs only between the base Source and the joined Source. A Source matches an input if one of the following conditions is true.
The Source is the same object as the input or it is a subtype of the input.
The Source has an output that is the same object as the input or the output is a subtype of the input.
The output has an output that is the same object as the input or is a subtype of the input.
The join operation looks for the conditions in the order in the preceding list. It searches the list of outputs of the Source recursively, looking for a match to the input. The search ends with the first matching Source. An input can match with only one Source, and two inputs cannot match with the same Source.
When a Source matches an input, the result of the join method has the elements of the base that match the elements specified by the parameters of the method. You can determine if a Source matches another Source, or an output of the other Source, by passing the Source to the findMatchFor method of the other Source.
When a Source matches an input, the resulting Source does not have that input. Matching a Source to an input does not affect the outputs of the base Source or the joined Source. If a base Source has an output that matches the input of the joined Source, the resulting Source does not have the input but it does have the output.
If the base Source or the joined Source in a join operation has an input that is not matched in the operation, then the unmatched input is an input of the resulting Source.
The comparison Source of a join method does not participate in the input matching. If the comparison Source has an input, then that input is not matched and the Source returned by the join method has that same input.
Example 5-7 demonstrates a base Source matching the input of the joined Source in a join operation. The example uses the position method to produce a Source that has an input, and then uses the join method to match the base of the join operation to the input of the joined Source.
Example 5-7 Matching the Base Source to an Input of the Joined Source
Source myList = dp.createListSource(new String[]
"PRODUCT_PRIMARY_AW::FAMILY_AW::4",
"PRODUCT_PRIMARY_AW::FAMILY_AW::5",
"PRODUCT_PRIMARY_AW::FAMILY_AW::7",
"PRODUCT_PRIMARY_AW::FAMILY_AW::8"});
Source pos = dp.createListSource(new int[] {2, 4});
Source myListPos = myList.position();
Source myListSel = myList.join(myListPos,
pos,
Source.COMPARISON_RULE_SELECT,
false);
In Example 5-7, the position method returns myListPos, which has the elements of myList and which has myList as an input. The join method matches the base myList to the input of the joined Source, myListPos.
The comparison Source, pos, specifies the positions of the elements of myListPos to match to the positions of the elements of myList. The elements of the resulting Source, myListSel, are the elements of myList whose positions match those specified by the parameters of the join method.
A Cursor for myListSel has the following values.
PRODUCT_PRIMARY_AW::FAMILY_AW::5 PRODUCT_PRIMARY_AW::FAMILY_AW::8
If the visible parameter in Example 5-7 were true instead of false, then the result would have elements from myList and an output of myListPos. A Cursor for myListSel in that case would have the following values, displayed as a table with headings added that indicate the output and type values.
Output Type Values Values ------ ------------------------- 2 PRODUCT_PRIMARY_AW::FAMILY_AW::5 4 PRODUCT_PRIMARY_AW::FAMILY_AW::8
Example 5-8 demonstrates matching outputs of the joined Source to two inputs of the base Source. In the example, units is a Source for an MdmMeasure. It has as inputs the primary Source objects for the Time, Product, Customer, and Channel dimensions.
The DataProvider is dp, and prodHier, shipHier, calendar, and chanHier are the Source objects for the default hierarchies of the Product, Customer, Time, and Channel dimensions, respectively. Those Source objects are subtypes of the Source objects for the dimensions that are the inputs of units.
The join method of prodHier in the first line of Example 5-8 results in prodSel, which specifies selected product values. In that method, the joined Source is the result of the value method of prodHier. The joined Source has the same elements as prodHier, and it has prodHier as an input. The comparison Source is the list Source that is the result of the createListSource method of the DataProvider.
The base Source of the join method, prodHier, matches the input of the joined Source. Because prodHier is the input of the joined Source, the Source returned by the join method has only the elements of the base, prodHier, that match the elements of the joined Source that appear in the comparison Source. Because the visible parameter value is false, the resulting Source does not have the joined Source as an output. The next three similar join operations in Example 5-8 result in selections for the other three dimensions.
The join method of timeSel has custSel as the joined Source. The comparison Source is the result of the getEmptySource method, so it has no elements. The comparison rule specifies that the elements of the joined Source that are present in the comparison Source do not appear in the resulting Source. Because the comparison Source has no elements, all of the elements of the joined Source are selected. The true value for the visible parameter causes the joined Source to be an output of the Source returned by the join method. The returned Source, custSelByTime, has the selected elements of the Customer dimension and has timeSel as an output.
The join method of prodSel has custSelByTime as the joined Source. It produces prodByCustByTime, which has the selected elements from the Product dimension and has custSelByTime as an output. Example 5-8 then joins the dimension selections to the units Source.
The dimension selections are subtypes of the Source objects that are the inputs of units, and therefore the selections match the inputs of units. The input for the Product dimension is matched by prodByCustByTime because prodByCustByTime is a subtype of prodSel, which is a subtype of prodHier. The input for the Customer dimension is matched by the custSelByTime, which is the output of prodByCustByTime.
The custSelByTime Source is a subtype of custSel, which is a subtype of shipHier. The input for the times dimension is matched by timeSel, which is the output of custSelByTime. The timeSel Source is a subtype of calendar.
Example 5-8 Matching an Input of the Base Source to an Output of the Joined Source
Source prodSel = prodHier.join(prodHier.value(),
dp.createListSource(new String[]
{"PRODUCT_PRIMARY_AW::FAMILY_AW::4",
"PRODUCT_PRIMARY_AW::FAMILY_AW::5"}),
Source.COMPARISON_RULE_SELECT,
false);
Source custSel = shipHier.join(shipHier.value(),
dp.createListSource(new String[]
{"SHIPMENTS_AW::REGION_AW::9",
"SHIPMENTS_AW::REGION_AW::10"}),
Source.COMPARISON_RULE_SELECT,
false);
Source timeSel = calendar.join(calendar.value(),
dp.createConstantSource(
"CALENDAR_YEAR_AW::YEAR::4"),
Source.COMPARISON_RULE_SELECT,
false);
Source chanSel = chanHier.join(chanHier.value(),
dp.createConstantSource(
"CHANNEL_PRIMARY_AW::CHANNEL_AW::4"),
Source.COMPARISON_RULE_SELECT,
false);
Source custSelByTime = custSel.join(timeSel,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true);
Source prodByCustByTime = prodSel.join(custSelByTime,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true);
Source selectedUnits = units.join(prodByCustByTime,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true)
.join(promoSel,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true ),
.join(chanSel,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
true);
A Cursor for selectedUnits contains the following values, displayed in a crosstab format with column headings and formatting added. The table has only the local values of the dimension elements. The first two lines are the page edge values of the crosstab, which are the values of the chanSel output of selectedUnits, and the value of timeSel, which is an output of the prodByCustByTime output of selectedUnits. The row edge values of the crosstab are the customer values in the left column, and the column edge values are the products values that head the middle and right columns.
The crosstab has only the local value portion of the unique values of the dimension elements. The measure values are the units sold values specified by the selected dimension values.
4
4
Products
----------
Customers 4 5
--------- --- ----
9 215 439
10 846 1748
The following table has the same results except that the dimension element values are replaced by the short descriptions of those values.
Internet
2001
Products
-------------------------
Customers Portable PCs Desktop PCs
------------- ------------ -----------
Europe 215 439
North America 846 1748
To demonstrate turning inputs into outputs, Example 5-9 uses units, which is the Source for the Units measure, and defaultHiers, which is an ArrayList of the Source objects for the default hierarchies of the dimensions of the measure. The example gets the inputs and outputs of the Source for the measure. It displays the Source identifications of the Source for the measure and for the inputs of the Source. The inputs of the Source for the measure are the Source objects for the MdmPrimaryDimension objects that are the dimensions of the measure.
Example 5-9 next displays the number of inputs and outputs of the Source for the measure. Using the join(Source joined) method, which produces a Source that has the elements of the base of the join operation as the elements of it and the joined parameter Source as an output, it joins one of the hierarchy Source objects to the Source for the measure, and displays the number of inputs and outputs of the resulting Source. It then joins each remaining hierarchy Source to the result of the previous join operation and displays the number of inputs and outputs of the resulting Source.
Finally the example gets the outputs of the Source produced by the last join operation, and displays the Source identifications of the outputs. The outputs of the last Source are the Source objects for the default hierarchies, which the example joined to the Source for the measure. Because the Source objects for the hierarchies are subtypes of the Source objects for the MdmPrimaryDimension objects that are the inputs of the measure, they match those inputs.
Example 5-9 Matching the Inputs of a Measure and Producing Outputs
Set inputs = units.getInputs();
Iterator inputsItr = inputs.iterator();
List outputs = units.getOutputs();
Source input = null;
int i = 1;
println("The inputs of " + units.getID() + " are:");
while(inputsItr.hasNext())
{
input = (Source) inputsItr.next();
println(i + ": " + input.getID());
i++;
}
println(" ");
int setSize = inputs.size();
for(i = 0; i < (setSize + 1); i++)
{
println(units.getID() + " has " + inputs.size() +
" inputs and " + outputs.size() + " outputs.");
if (i < setSize)
{
input = defaultHiers.get(i);
println("Joining " + input.getID() + " to " + units.getID());
units = units.join(input);
inputs = units.getInputs();
outputs = units.getOutputs();
}
}
println("The outputs of " + units.getID() + " are:");
Iterator outputsItr = outputs.iterator();
i = 1;
while(outputsItr.hasNext())
{
Source output = (Source) outputsItr.next();
println(i + ": " + output.getID());
i++;
}
The text displayed by the example is the following:
The inputs of Hidden..M_GLOBAL_AW.UNITS_CUBE_AW.UNITS_AW are: 1: Hidden..D_GLOBAL_AW.PRODUCT_AW 2: Hidden..D_GLOBAL_AW.CHANNEL_AW 3: Hidden..D_GLOBAL_AW.CUSTOMER_AW 4: Hidden..D_GLOBAL_AW.TIME_AW Hidden..M_GLOBAL_AW.UNITS_CUBE_AW.UNITS_AW has 4 inputs and 0 outputs. Joining Hidden..D_GLOBAL_AW.PRODUCT_AW.PRODUCT_PRIMARY_AW to Hidden..M_GLOBAL_AW.UNITS_CUBE_AW.UNITS_AW Join.30 has 3 inputs and 1 outputs. Joining Hidden..D_GLOBAL_AW.CUSTOMER_AW.SHIPMENTS_AW to Join.30 Join.31 has 2 inputs and 2 outputs. Joining Hidden..D_GLOBAL_AW.TIME_AW.CALENDAR_YEAR_AW to Join.31 Join.32 has 1 inputs and 3 outputs. Joining Hidden..D_GLOBAL_AW.CHANNEL_AW.CHANNEL_PRIMARY_AW to Join.32 Join.33 has 0 inputs and 4 outputs. The outputs of Join.33 are: 1: Hidden..D_GLOBAL_AW.CHANNEL_AW.CHANNEL_PRIMARY_AW 2: Hidden..D_GLOBAL_AW.TIME_AW.CALENDAR_YEAR_AW 3: Hidden..D_GLOBAL_AW.CUSTOMER_AW.SHIPMENTS_AW 4: Hidden..D_GLOBAL_AW.PRODUCT_AW.PRODUCT_PRIMARY_AW
Note that as each successive Source for a hierarchy is joined to the result of the previous join operation, it becomes the first output in the List of outputs of the resulting Source. Therefore, the first output of Join.33 is Hidden..D_GLOBAL_AW.CHANNEL_AW.CHANNEL_PRIMARY_AW, and the last output is Hidden..D_GLOBAL_AW.PRODUCT_AW.PRODUCT_PRIMARY_AW.
Parameterized Source objects provide a way of specifying a query and retrieving different result sets for the query by changing the set of elements specified by the parameterized Source. You create a parameterized Source with a createParameterizedSource method of the DataProvider that you are using. In creating the parameterized Source, you supply a Parameter object. The Parameter supplies the value that the parameterized Source specifies.
Parameter objects are similar to CursorInput objects in that you use them to specify an initial value for a Source that is part of a query. A typical use of both Parameter and CursorInput objects is to specify the page edges of a cube. Example 6-9 demonstrates using Parameter objects to specify page edges.
An advantage of Parameter objects over CursorInput objects is that with Parameter objects you can easily fetch from the server only the set of elements that you currently need. Example 6-15 demonstrates using Parameter objects to fetch different sets of elements.
When you create a Parameter object, you supply an initial value for the Parameter. You then create the parameterized Source using the Parameter. You include the parameterized Source in specifying a query. You create a Cursor for the query. You can change the value of the Parameter with the setValue method, which changes the set of elements that the query specifies. Using the same Cursor, you can then display the new set of values.
Example 5-10 demonstrates the use of a Parameter and a parameterized Source to specify an element in a measure dimension. It creates a list Source that has as element values the Source objects for Unit Cost and Unit Price measures. The example creates a StringParameter object that has as an initial value the unique identifying String for the Source for the Unit Cost measure. That StringParameter is then used to create a parameterized Source.
The example extracts the values from the measures, and then selects the data values that are specified by joining the dimension selections to the measure specified by the parameterized Source. It creates a Cursor for the resulting query and displays the results. After resetting the Cursor position and changing the value of the measParam StringParameter, the example displays the values of the Cursor again.
The dp object is the DataProvider. The getContext method gets a Context10g object that has a method that displays the values of the Cursor with only the local value of the dimension elements.
Example 5-10 Using a Parameterized Source With a Measure Dimension
Source measDim = dp.createListSource(new Source[] {unitCost,
unitPrice});
// Get the unique identifiers of the Source objects for the measures.
String unitCostID = unitCost.getID();
String unitPriceID = unitPrice.getID();
// Create a StringParameter using one of the IDs as the initial value.
StringParameter measParam = new StringParameter(dp, unitCostID);
// Create a parameterized Source.
StringSource measParamSrc = dp.createParameterizedSource(measParam);
// Extract the values from the measure dimension elements, and join
// them to the specified measure and the dimension selections.
Source result = measDim.extract().join(measDim, measParamSrc)
.join(prodSelShortDescr)
.join(timeSelShortDescr);
// Get the TransactionProvider and prepare and commit the
// current transaction. These operations are not shown.
// Create a Cursor.
CursorManagerSpecification cMngrSpec =
dp.createCursorManagerSpecification(results);
SpecifiedCursorManager spCMngr = dp.createCursorManager(cMngrSpec);
Cursor resultsCursor = spCMngr.createCursor();
// Display the results.
getContext().displayCursor(resultsCursor, true);
//Reset the Cursor position to 1.
resultsCursor.setPosition(1);
// Change the value of the parameterized Source.
measParam.setValue(unitPriceID);
// Display the results again.
getContext().displayCursor(resultsCursor, true);
The following table displays the first set of values of resultsCursor, with column headings and formatting added. The left column of the table has the local value of the Time dimension hierarchy. The second column from the left has the short value description of the time value. The third column has the local value of the Product dimension hierarchy. The fourth column has the short value description of the product value. The fifth column has the Unit Cost measure value for the time and product.
Time Description Product Description Unit Cost ---- ----------- ------- --------------- --------- 58 Apr-01 13 Envoy Standard 2360.78 58 Apr-01 14 Envoy Executive 2952.85 59 May-01 13 Envoy Standard 2376.73 59 May-01 14 Envoy Executive 3015.90
The following table displays the second set of values of resultsCursor in the same format. This time the fifth column has values from the Unit Price measure.
Time Description Product Description Unit Price ---- ----------- ------- --------------- ---------- 58 Apr-01 13 Envoy Standard 2412.42 58 Apr-01 14 Envoy Executive 3107.65 59 May-01 13 Envoy Standard 2395.63 59 May-01 14 Envoy Executive 3147.85
This topic describes the Model interface and the implementations of it, and the relationship of Model and Source objects. It also presents examples of creating custom Model objects and performing other tasks that involve Source and Model objects.
A Model is analogous to the Oracle SQL MODEL clause. With a Model you can assign a value to the Source for a dimensioned object for one or more sets of members of the dimensions of the object. The value that the Model assigns can be anything from a simple constant to the result of a complex calculation involving several other Source objects with nested Model objects.
The value that a Model assigns for a set of dimension members is represented by an Assignment object. A Model can have one or more Assignment objects. Each dimension member in the set is represented by a Qualification object. An Assignment has one or more Qualification objects.
The value that the Assignment assigns is specified by a Source. An Assignment also has an integer that specifies a precedence that affects the order in which Oracle OLAP calculates a value and assigns it. If you create more that one Assignment for a Model without specifying a precedence, then the order in which Oracle OLAP calculates and assigns the values is not guaranteed.
A Model assigns values for existing dimension members. You can use a Model to assign a different value for a dimension member, or to assign a value for a set of members of more than one dimension, or to assign a different value for a specific measure for the set of dimension members, or to assign a value for the dimension member for an attribute.
When you create a custom dimension member, you specify an assignment value for it. Oracle OLAP automatically adds an Assignment object that specifies the value for the custom member to the appropriate Model for the dimension. Oracle OLAP assigns that value as the measure value for any measure dimensioned by the dimension.
Figure 5-1 illustrates the class hierarchy of the Model interface and the classes that implement it. The oracle.olapi.metadata.mdm.MdmModel class implements the Model interface for MdmObject objects. Another implementation of the Model interface is the CustomModel class in the oracle.olapi.data.source package.
Figure 5-1 The Model Interface and Implementations
A Model has one or more inputs, which are the Source objects for which the model assigns values. The inputs are equivalent to the list of dimensions of an OLAP DML or SQL Model. For example, the MdmDimensionCalculationModel returned by the getNumberCalcModel method of an MdmStandardDimension has as an input the Source for that same MdmStandardDimension. The MdmDimensionedObjectModel returned by the getModel method of an MdmAttribute has as an input the Source for the MdmPrimaryDimension that dimensions the attribute. The MdmDimensionedObjectModel returned by getModel method of an MdmMeasure has as inputs the Source objects for the MdmPrimaryDimension objects that dimension the measure.
A Model can have one or more parents, which are other Model objects from which the Model inherits Assignment objects. An MdmMeasureModel has as parents the MdmDimensionCalculationModel objects of the dimensions associated with it. MdmAttributeModel and MdmDimensionCalculationModel objects do not have parent Model objects.
A CustomModel can have inputs and it can have parent Model objects. When you create a CustomModel object, you can specify inputs and parent Model objects for it. A CustomModel can have also have outputs, which MdmModel objects do not have.
You can create a series of CustomModel objects and have them inherit Assignment objects from each other. The following restrictions apply to the inheritance of an Assignment by one CustomModel from another:
The inheritance cannot be circular. For example, if customModelB inherits from customModelA, then customModelA cannot inherit from customModelB.
The type and the outputs of the CustomModel objects must be the same.
If a parent CustomModel has an input, then the child CustomModel must also specify that input. The child CustomModel can have additional inputs, but it must specify the inputs of the parent CustomModel objects.
After creating a CustomModel and adding any assignments to it, you can create a Source for it by calling the createSolvedSource method of the CustomModel. With the defaultValues parameter of the createSolvedSource method, you can specify a Source that supplies default values for the Source returned by the method. If you do not specify a Source for the default values, then the default values of the resulting Source are null.
The Source.extract method is implemented as a CustomModel. An advantage of using your own CustomModel over the extract method is that you can assign the measure value to a String other than a Source ID. Example 5-11 demonstrates using the extract method and then using a CustomModel to achieve the same result. It also demonstrates using another CustomModel to achieve a result that assigns the measure values to a different set of String values.
In the example, unitPrice and unitCost are NumberSource objects for the Unit Price and Unit Cost measures, and dp is the DataProvider. The prodSel object is a Source that represents the selection of three members of the Product dimension.
Example 5-11 Implementing the extract Method As a CustomModel
// Create a Source that represents a calculation involving two measures.
Source calculation = unitPrice.minus(unitCost);
// Create a list Source that has Source objects as element values.
Source sourceListSrc = dp.createListSource(new Source[]
{unitPrice, unitCost, calculation});
// Use the extract method to get the values of the Source components of the
// list and join Source objects that match the inputs.
Source resultUsingExtract =
sourceListSrc.extract()
.join(sourceListSrc)
.join(prodSel)
.join(calendar, "CALENDAR_YEAR_AW::MONTH_AW::47");
// Produce the same result using a CustomModel directly.
CustomModel customModel = dp.createModel(sourceListSrc);
customModel.assign(unitPrice.getID(), unitPrice);
customModel.assign(unitCost.getID(), unitCost);
customModel.assign(calculation.getID(), calculation);
Source measValForSrc = customModel.createSolvedSource();
Source resultUsingCustomModel =
measValForSrc.join(sourceListSrc)
.join(prodSel)
.join(calendar, "CALENDAR_YEAR_AW::MONTH_AW::47");
// Create a list Source that has String objects as element values.
Source stringListSrc = dp.createListSource(new String[]
{"price", "cost", "markup"});
// Create a CustomModel for the list Source.
CustomModel customModel2 = dp.createModel(stringListSrc);
customModel2.assign("price", unitPrice);
customModel2.assign("cost", unitCost);
customModel2.assign("markup", calculation);
Source measValForSrc2 = customModel2.createSolvedSource();
Source resultUsingCustomModel2 =
measValForSrc2.join(stringListSrc)
.join(prodSel)
.join(calendar, "CALENDAR_YEAR_AW::MONTH_AW::47");
Cursor objects for resultUsingExtract and resultUsingCustomModel have the same values, which are the following, shown with formatting added:
PRODUCT_PRIMARY_AW::ITEM_AW::13 Hidden..M_GLOBAL.PRICE_CUBE.UNIT_PRICE 3118.61 PRODUCT_PRIMARY_AW::ITEM_AW::13 Hidden..M_GLOBAL.PRICE_CUBE.UNIT_COST 2897.40 PRODUCT_PRIMARY_AW::ITEM_AW::13 Join.2 221.21 PRODUCT_PRIMARY_AW::ITEM_AW::14 Hidden..M_GLOBAL.PRICE_CUBE.UNIT_PRICE 3442.86 PRODUCT_PRIMARY_AW::ITEM_AW::14 Hidden..M_GLOBAL.PRICE_CUBE.UNIT_COST 3238.36 PRODUCT_PRIMARY_AW::ITEM_AW::14 Join.2 204.50 PRODUCT_PRIMARY_AW::ITEM_AW::15 Hidden..M_GLOBAL.PRICE_CUBE.UNIT_PRICE 2962.14 PRODUCT_PRIMARY_AW::ITEM_AW::15 Hidden..M_GLOBAL.PRICE_CUBE.UNIT_COST 2847.47 PRODUCT_PRIMARY_AW::ITEM_AW::15 Join.2 114.67
A Cursor for resultUsingCustomModel2 has the following values, shown with formatting added:
PRODUCT_PRIMARY_AW::ITEM_AW::13 price 3118.61 PRODUCT_PRIMARY_AW::ITEM_AW::13 cost 2897.40 PRODUCT_PRIMARY_AW::ITEM_AW::13 markup 221.21 PRODUCT_PRIMARY_AW::ITEM_AW::14 price 3442.86 PRODUCT_PRIMARY_AW::ITEM_AW::14 cost 3238.36 PRODUCT_PRIMARY_AW::ITEM_AW::14 markup 204.50 PRODUCT_PRIMARY_AW::ITEM_AW::15 price 2962.14 PRODUCT_PRIMARY_AW::ITEM_AW::15 cost 2847.47 PRODUCT_PRIMARY_AW::ITEM_AW::15 markup 114.67
The value that is specified by the assigned Source of an Assignment object can be the result of a calculation that involves another Assignment object. Each custom member adds an Assignment to the appropriate MdmDimensionCalculationModel object of the dimension.
Example 5-12 creates the same custom member of the Product dimension that Example 2-1 creates. It then creates a second custom member for the dimension. In Example 5-12, the value assigned by the second custom member depends on the value assigned by the first custom member.
As in Example 2-1, Example 5-12 uses the DataProvider object, dp, to get the placeholder Source, ph, for the Number data type from the DataProvider. the example uses the placeholder in defining the objects, calc and dependentCalc, that define the values that Oracle OLAP assigns for the custom members.
The calc object defines the value assigned for the first custom member as the value specified by product item 14 plus the value specified by item 15. The dependentCalc object defines the value assigned for the second custom member as the value specified by the first custom member, product item 60, plus the value specified by item 13.
The prodSel object specifies the dimension members for items 13, 14, and 15 and the custom members, items 60 and 61. The unitCost and unitPrice objects are Source objects for the Unit Cost and Unit Price measures, and the calendar object is the Source for the Calendar Year hierarchy of the Time dimension.
The result object is the query produced by joining the Source objects for the Unit Cost and Unit Price measures to the Source objects for the selected members of the dimensions of the measures. The join method used to join the Time dimension value, CALENDAR_YEAR_AW::MONTH_AW::47, to the result of the previous join operations causes the Time value to not appear in the result object.
Example 5-12 Creating an Assignment That Depends on Another Assignment
Source ph = dp.getFundamentalMetadataProvider()
.getNumberPlaceholder()
.getSource();
Source calc = ((NumberSource)
(ph.join(prodHier, "PRODUCT_PRIMARY_AW::ITEM_AW::14")))
.plus(
(NumberSource)
(ph.join(prodHier, "PRODUCT_PRIMARY_AW::ITEM_AW::15")));
MdmStandardMember mdmItem60 = mdmProdStdDim.createCustomMember("60",
mdmItemLevel,
"4",
calc,
10);
Source dependentCalc = ((NumberSource)
(ph.join(prodHier, "PRODUCT_PRIMARY_AW::ITEM_AW::60")))
.plus(
(NumberSource)
(ph.join(prodHier, "PRODUCT_PRIMARY_AW::ITEM_AW::13")));
MdmStandardMember mdmItem61 = mdmProdStdDim.createCustomMember("61",
mdmItemLevel,
"4",
dependentCalc,
10);
StringSource prodSel = prodHier.selectValues(
new String[]{"PRODUCT_PRIMARY_AW::ITEM_AW::13",
"PRODUCT_PRIMARY_AW::ITEM_AW::14",
"PRODUCT_PRIMARY_AW::ITEM_AW::15",
"PRODUCT_PRIMARY_AW::ITEM_AW::60"
"PRODUCT_PRIMARY_AW::ITEM_AW::61"});
Source result = unitPrice.join(unitCost)
.join(prodSel)
.join(calendar, "CALENDAR_YEAR_AW::MONTH_AW::47");
A Cursor for result has the following values, with column headings and formatting added:
Product Item Cost Price ------------------------------- ------- ------- PRODUCT_PRIMARY_AW::ITEM_AW::13 2897.40 3118.61 PRODUCT_PRIMARY_AW::ITEM_AW::14 3238.36 3442.86 PRODUCT_PRIMARY_AW::ITEM_AW::15 2847.47 2962.14 PRODUCT_PRIMARY_AW::ITEM_AW::60 6085.83 6405.00 PRODUCT_PRIMARY_AW::ITEM_AW::61 8983.23 9523.61
Example 5-13 creates a custom member of the Product dimension that has an assigned value that is the result of an aggregation operation. The example uses the Source for an MdmAttribute that relates marketing managers to dimension members in the Item level of a dimension hierarchy.
Like Example 5-12, this example uses a placeholder Source, ph, in creating the calc object, which defines the value that Oracle OLAP assigns for the custom member. The calc object represents the total of the measure values specified by a set of dimension members.
The example creates the custom member and then specifies a short value description for it. Next, the example appends the custom member to the selection of products. Finally, it produces the result query by joining the Source for the Sales measure to the Source objects for the short value description of the Product dimension and the selected members of the dimensions of the measure. The particular join method that is used to join the Customer dimension value, SHIPMENTS_AW::SHIP_TO_AW::106, the Channel dimension value, CHANNEL_PRIMARY_AW::TOTAL_CHANNEL_AW::1, and the Time dimension value, CALENDAR_YEAR_AW::YEAR_AW::3, to the result of the previous join operations causes the Customer, Channel, and Time values to not appear in the result object.
Example 5-13 Creating a Custom Member That Assigns an Aggregated Value
// Select the members of the level that are managed by a marketing manager.
Source prodForManager = itemLevel.join(mktMngrAttr, "Jackson");
Source calc = ((NumberSource)
(ph.join(prodHier,
new String[] {"PRODUCT_PRIMARY_AW::ITEM_AW::24",
"PRODUCT_PRIMARY_AW::ITEM_AW::25",
"PRODUCT_PRIMARY_AW::ITEM_AW::26",
"PRODUCT_PRIMARY_AW::ITEM_AW::33",
"PRODUCT_PRIMARY_AW::ITEM_AW::34",
"PRODUCT_PRIMARY_AW::ITEM_AW::35",
"PRODUCT_PRIMARY_AW::ITEM_AW::36",
"PRODUCT_PRIMARY_AW::ITEM_AW::37",
"PRODUCT_PRIMARY_AW::ITEM_AW::38",
"PRODUCT_PRIMARY_AW::ITEM_AW::39"}))).total();
MdmStandardMember mdmMktMngrTotal =
mdmProdStdDim.createCustomMember("65", // member local value
mdmItemLevel, // member level
"4", // parent local value
calc, // calculation Source
10); // precedence value
mdmMktMngrTotal.setShortDescription("Marketing Manager Total");
Source mktMngrWithTotal = prodForManager.appendValue(
prodHier.selectValue(
"PRODUCT_PRIMARY_AW::ITEM_AW::65"));
Source result = sales.join(prodShortDescr.join(mktMngrWithTotal))
.join(shipHier, "SHIPMENTS_AW::SHIP_TO_AW::106")
.join(chanHier, "CHANNEL_PRIMARY_AW::TOTAL_CHANNEL_AW::1")
.join(calendar, ""CALENDAR_YEAR_AW::YEAR_AW::3");
The following is a crosstab display of the values of a Cursor for result. The display includes only the local value of the Product dimension members and has column headings and formatting added.
Product Description Sales Amount ------- ---------------------------- ------------ 24 56Kbps V.90 Type II Modem 39,178.31 25 512MB USB Drive 16,665.00 26 1GB USB Drive 11,762.41 33 56Kbps V.92 Type II Fax/Modem 24,136.29 34 Internal 48X CD-ROM 3,290.74 35 Internal - DVD-RW - 8X 16,654.33 36 External 48X CD-ROM 2,589.97 37 External - DVD-RW - 8X 18,340.97 38 Internal 48X CD-ROM 926.50 39 Internal - DVD-RW - 6X 25,605.98 65 Marketing Manager Total 159,150.50
