| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 1 (10.1) Part Number B10335-02 |
|
|
View PDF |
| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 1 (10.1) Part Number B10335-02 |
|
|
View PDF |
You create a query by producing a Source that specifies the data that you want to retrieve from the data store and any operations you want to perform on that data. To produce the query, you begin with the primary Source objects that represent the metadata of the measures and the dimensions and their attributes that you want to query. Typically, you use the methods of the primary Source objects to derive a number of other Source objects, each of which specifies a part of the query, such as a selection of dimension elements or an operation to perform on the data. You then join the primary and derived Source objects that specify the data and the operations that you want. The result is one Source that represents the query.
This chapter briefly describes the various kinds of Source methods, and discusses some of them in greater detail. It also discusses how to make some typical OLAP queries using these methods and provides examples of some of them.
This chapter includes the following topics:
For the complete code of the examples in this chapter, see the example programs available from the Overview of the Oracle OLAP Java API Reference.
The Source class has many methods that return a derived Source. The elements of the derived Source result from operations on the base Source, which is the Source whose method is called that produces the derived Source. Only a few methods perform the most basic operations of the Source class.
The Source class has many other methods that use one or more of the basic methods to perform operations such as selecting elements of the base Source by value or by position, or sorting elements. Many of the examples in this chapter and in Chapter 6, " Understanding Source Objects" use some of these methods. Other Source methods get objects that have information about the Source, such as the getDefinition, getInputs, and getType methods, or convert the values of the Source from one data type to another, such as the toDoubleSource method.
This section describes the basic Source methods and provides some examples of their use. Table 7-1 lists the basic Source methods.
Table 7-1 The Basic Source Methods
This section provides examples of using some of the basic methods.
You use the alias method to control the matching of a Source to an input. For example, if you want to find out if the measure values specified by an element of a dimension of the measure are greater than the measure values specified by the other elements of the same dimension, then you need to match the inputs of the measure twice in the same join operation. To do so, you can produce two Source objects that are aliases for the same dimension, make them inputs of two instances of the measure, join each measure instance to its aliased dimension, and then compare the results.
Example 7-1 performs such an operation. It produces a Source that specifies whether the number of units sold for each value of the channel dimension is greater than the number of units sold for the other values of the channel dimension.
The example joins to units, which is the Source for a measure, Source objects that are selections of single values of three of the dimensions of the measure to produce unitsSel. The unitsSel Source specifies the units elements for the dimension values that are specified by the timeSel, custSel, and prodSel objects, which are outputs of unitsSel.
The timeSel, custSel, and prodSel Source objects specify single values from the default hierarchies of the TIME, CUSTOMER, and PRODUCT dimensions, respectively. The timeSel value is CALENDAR::MONTH::55, which identifies the month January, 2001, the custSel value is SHIPMENTS_ROLLUP::SHIP_TO::52, which identifies the Business Word San Jose customer, and the prodSel value is PRODUCT_ROLLUP::ITEM::15, which identifies the Envoy Ambassador portable PC.
The example next creates two aliases, chanAlias1 and chanAlias2, for chanHier, which is the default hierarchy of the CHANNEL dimension. It then produces unitsSel1 by joining unitsSel to the Source that results from calling the value method of chanAlias1. The unitsSel1 Source has the elements and outputs of unitsSel and it has chanAlias1 as an input. Similarly, the example produces unitsSel2, which has chanAlias2 as an input.
The example uses the gt method of unitsSel1, which determines whether the values of unitsSel1 are greater than the values of unitsSel2. The following join operations matches chanAlias1 to the input of unitsSel1 and matches chanAlias1 to the input of unitsSel2.
Example 7-1 Controlling Input-to-Source Matching With the alias Method
Source unitsSel = units.join(timeSel).join(custSel).join(prodSel);
Source chanAlias1 = chanHier.alias();
Source chanAlias2 = chanHier.alias();
NumberSource unitsSel1 = (NumberSource)
unitsSel.join(chanAlias1.value());
NumberSource unitsSel2 = (NumberSource)
unitsSel.join(chanAlias2.value());
Source result = unitsSel1.gt(unitsSel2)
.join(chanAlias1) // Output 2, column
.join(chanAlias2); // Output 1, row;
The result Source specifies the query, "Are the units sold values of unitsSel1 for the channel values of chanAlias1 greater than the units sold values of unitsSel2 for the channel values of chanAlias2?" Because result is produced by the joining of chanAlias2 to the Source produced by unitsSel1.gt(unitsSel2).join(chanAlias1), chanAlias2 is the first output of result, and chanAlias1 is the second output of result.
A Cursor for the result Source has as its values the boolean values that answer the query. The values of the first output of the Cursor are the channel values specified by chanAlias2 and the values of its second output are the channel values specified by chanAlias1.
The following is a display of the values of the Cursor formatted as a crosstab with headings added. The column edge values are the values from chanAlias1, and the row edge values are the values from chanAlias2. The values of the crosstab cells are the boolean values that indicate whether the units sold value for the column channel value is greater than the units sold value for the row channel value. For example, the crosstab values in the first column indicate that the units sold for the column channel value All Channels is not greater than the units sold for the row All Channels value but it is greater than the units sold for the Direct Sales, Catalog, and Internet row values.
---------- chanAlias1 ---------- chanAlias2 All Channels Direct Sales Catalog Internet ------------ ------------ ------------ ------- -------- All Channels false false false false Direct Sales true false true false Catalog true false false false Internet true true true false
You use the distinct method to produce a Source that does not have any duplicated values. Example 7-2 selects an element from a hierarchy of the CUSTOMER dimension and gets the descendants of that element. It then appends the descendants to the hierarchy element selection. Because the Source for the descendants includes the ancestor value, the example uses the distinct method to remove the duplicated ancestor value, which would otherwise appear twice in the result.
In Example 7-2, mktRollup is a StringSource that represents the MARKET_ROLLUP hierarchy of the CUSTOMER dimension. The mktRollupAncestors object is the Source for the ancestors attribute of that hierarchy. To get a Source that represents the descendents of the ancestors, the example uses the join method to select, for each element of mktRollupAncestors, the elements of mktRollup that have the mktRollupAncestors element as their ancestor. The join operation matches the base Source, mktRollup, to the input of the ancestors attribute.
The resulting Source, mktRollupDescendants, however, still has mktRollup as an input because the Source produced by the mktRollup.value() method is the comparison Source of the join operation. The comparison parameter Source of a join operation does not participate in the matching of an input to a Source.
The selectValue method of mktRollup selects the element of mktRollup that has the value MARKET_ROLLUP::ACCOUNT::23, which is the Business World account, and produces selVal. The join method of mktRollupDescendants uses selVal as the comparison parameter. The method produces selValDescendants, which is has the elements of mktRollupDescendants that are present in mktRollup, and that are also in selVal. The input of mktRollupDescendants is matched by the joined Source mktRollup. The mktRollup Source is not an output of selValDescendants because the value of the visible parameter of the join operation is false.
The appendValues method of selVal produces selValPlusDescendants, which is the result of appending the elements of selValDescendants to the element of selVal and then removing any duplicate elements with the distinct method.
Example 7-2 Using the distinct Method
Source mktRollupDescendants =
mktRollup.join(mktRollupAncestors, mktRollup.value());
Source selVal = mktRollup.selectValue("MARKET_ROLLUP::ACCOUNT::23");
Source selValDescendants = mktRollupDescendants.join(mktRollup,
selVal,
false);
Source selValPlusDescendants = selVal.appendValues(selValDescendants)
.distinct();
A Cursor for the selValPlusDescendants Source has the following values:
MARKET_ROLLUP::ACCOUNT::23 MARKET_ROLLUP::SHIP_TO::51 MARKET_ROLLUP::SHIP_TO::52 MARKET_ROLLUP::SHIP_TO::53 MARKET_ROLLUP::SHIP_TO::54
If the example did not include the distinct method call, then a Cursor for selValPlusDescendants would have the following values:
MARKET_ROLLUP::ACCOUNT::23 MARKET_ROLLUP::ACCOUNT::23 MARKET_ROLLUP::SHIP_TO::51 MARKET_ROLLUP::SHIP_TO::52 MARKET_ROLLUP::SHIP_TO::53 MARKET_ROLLUP::SHIP_TO::54
You use the extract method to extract the values of a Source that has Source objects as its element values. If the elements of a Source have element values that are not Source objects, the extract method operates like the value method.
Example 7-3 uses the extract method to get the values of the NumberSource objects that are themselves the values of the elements of measDim. Each of the NumberSource objects represents a measure. The first two are the primary NumberSource objects for the UNITS and the UNIT_PRICE measures, and the third is a NumberSource derived from a mathematical operation on the primary NumberSource objects.
The example selects values from hierarchies of the dimensions of the NumberSource for the UNITS measure. Two of the dimensions are the dimensions of the NumberSource for the UNIT_PRICE measure. The example produces sales, which is the result of the times method of units with unitPrice as the rhs parameter of the method.
Next, the example creates a list Source, measDim, which has the three NumberSource objects as its element values. It then uses the extract method to get the values of the NumberSource objects. The resulting unnamed Source has measDim as an extraction input. The input is matched by first join operation, which has measDim as the joined parameter. The example then matches the other inputs of the measures by joining the dimension selections to produce the result Source.
Example 7-3 Using the extract Method
Source prodSel = prodHier.selectValues(new String[]
{"PRODUCT_ROLLUP::ITEM::13",
"PRODUCT_ROLLUP::ITEM::14",
"PRODUCT_ROLLUP::ITEM::15"});
Source chanSel = chanHier.selectValue("CHANNEL_ROLLUP::CHANNEL::2");
Source timeSel = timeHier.selectValue("CALENDAR::MONTH::59");
Source custSel = custHier.selectValue("SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1");
Source sales = units.times(unitPrice);
Source measDim = dp.createListSource(new Source[]
{units, unitPrice, sales});
Source result = measDim.extract().join(measDim) // column
.join(prodSel) // row
.join(timeSel) // page
.join(chanSel) // page
.join(custSel); // page
The following crosstab displays the values of a Cursor for the result Source, with headings and formatting added.
SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1
CHANNEL_ROLLUP::CHANNEL::2
CALENDAR::MONTH::59
UNITS SOLD TOTAL OF UNIT PRICES SALES AMOUNT
ITEM ---------- -------------------- -------------
----
13 39 2,395.63 93,429.57
14 37 3,147.85 116,470.45
15 26 2,993.29 77,825.54
You use the join method to produce a Source that has the elements of its base Source that are determined by the joined, comparison, and comparisonRule parameters of the method. The visible parameter determines whether the joined Source is an output of the Source produced by the join operation. You also use the join method to match a Source to an input of the base or joined parameter Source.
The join method has many signatures that are convenient shortcuts for the full join(Source joined, Source comparison, int comparisonRule, boolean visible) method. The examples in this chapter use various join method signatures.
The Source class has several constants that you can provide as the value of the comparisonRule parameter. Example 7-4 and Example 7-5 demonstrate the use of two of those constants, COMPARISON_RULE_REMOVE and COMPARISON_RULE_DESCENDING. Example 7-6 also uses COMPARISON_RULE_REMOVE.
Example 7-4 produces a result similar to Example 7-2. It uses mktRollup, which is the Source for a hierarchy of the CUSTOMER dimension, and mktRollupAncestors, which is the Source for the ancestors attribute for the hierarchy. It also uses mktRollupDescendants, which is a Source for the descendants of elements of the hierarchy.
The example first selects an element of the hierarchy. Next, the join method of mktRollupDescendants produces mktRollupDescendantsOnly, which specifies the descendants of mktRollup, and which has mktRollup as an input because the comparison parameter of the join operation is the Source that results from the mktRollup.value() method.
Because COMPARISON_RULE_REMOVE is the comparison rule of the join operation that produced mktRollupDescendantsOnly, a join operation that matches a Source to the input of mktRollupDescendantsOnly produces a Source that has only those elements of mktRollupDescendantsOnly that are not in the comparison Source of the join operation.
The next join operation performs such a match. It matches the joined Source, mktRollup, to the input of mktRollupDescendantsOnly, to produce selValDescendantsOnly, which specifies the descendants of the selected hierarchy value but does not include the selected value because mktRollupDescendantsOnly specifies the removal of any values that match the value of the comparison Source, which is selVal.
As a contrast, the last join operation produces selValDescendants, which specifies the descendants of the selected hierarchy value and which does include the selected value.
Example 7-4 Using COMPARISON_RULE_REMOVE
Source selVal = mktRollup.selectValue("MARKET_ROLLUP::ACCOUNT::23");
Source mktRollupDescendantsOnly =
mktRollupDescendants.join(mktRollupDescendants.getDataType().value(),
mktRollup.value(),
Source.COMPARISON_RULE_REMOVE);
// Select the descendants of the specified element.
Source selValDescendants = mktRollupDescendants.join(mktRollup, selVal);
// Select only the descendants of the specified element.
Source selValDescendantsOnly = mktRollupDescendantsOnly.join(mktRollup,
selVal);
A Cursor for selValDescendants has the following values.
MARKET_ROLLUP::ACCOUNT::23 MARKET_ROLLUP::SHIP_TO::51 MARKET_ROLLUP::SHIP_TO::52 MARKET_ROLLUP::SHIP_TO::53 MARKET_ROLLUP::SHIP_TO::54
A Cursor for selValDescendantsOnly has the following values.
MARKET_ROLLUP::SHIP_TO::51 MARKET_ROLLUP::SHIP_TO::52 MARKET_ROLLUP::SHIP_TO::53 MARKET_ROLLUP::SHIP_TO::54
Example 7-5 demonstrates another join operation, which uses the comparison rule COMPARISON_RULE_DESCENDING. It uses the following Source objects.
prodSelWithShortDescr, which is the Source produced by joining the Source for the short value description attribute of the PRODUCT dimension to the Source for the FAMILY level of the PRODUCT_ROLLUP hierarchy of that dimension.
unitPrice, which is the Source for the UNIT_PRICE measure.
timeSelWithShortDescr, which is the Source produced by joining the Source for the short value description attribute of the TIME dimension to the Source for a selected element of the CALENDAR hierarchy of that dimension.
The resulting Source specifies the product family level elements in descending order of total unit prices for the month of May, 2001.
Example 7-5 Using COMPARISON_RULE_DESCENDING
Source result =
prodSelWithShortDescr.join(unitPrice,
unitPrice.getDataType(),
Source.COMPARISON_RULE_DESCENDING,
true)
.join(timeSelWithShortDescr);
A Cursor for the result Source has the following values, displayed as a table. The table includes only the short value descriptions of the dimension elements and the unit price values, and has formatting added.
May, 2001
Total Unit Prices Product Family
----------------- --------------
8,536.77 Portable PCs
5,613.08 Desktop PCs
1,273.00 CD-ROM
830.74 Memory
795.24 Monitors
448.06 Documentation
364.93 Accessories
318.61 Modems/Fax
131.84 Operating Systems
You use the position method to produce a Source that has the positions of the elements of its base and has the base as an input. Example 7-6 uses the position method in producing a Source that specifies the selection of the first and last elements of the levels of a hierarchy of the TIME dimension.
In the example, mdmTimeDim is the MdmPrimaryDimension for the TIME dimension. The example gets the level attribute and the default hierarchy of the dimension. It then gets Source objects for the attribute and the hierarchy.
Next, the example creates an array of Source objects and gets a List of the MdmLevel components of the hierarchy. It gets the Source object for each level and adds it to the array, and then creates a list Source that has the Source objects for the levels as its element values.
The example then produces levelMembers, which is a Source that specifies the elements of the levels of the hierarchy. Because the comparison parameter of the join operation is the Source produced by levelList.value(), levelMembers has levelList as an input. Therefore, levelMembers is a Source that returns the elements of each level, by level, when its input is matched in a join operation.
The range Source specifies a range of elements from the second element to the next to last element of a Source.
The next join operation produces the firstAndLast Source. The base of the operation is levelMembers. The joined parameter is the Source that results from the levelMembers.position() method. The comparison parameter is the range Source and the comparison rule is COMPARISON_RULE_REMOVE. The value of the visible parameter is true. The firstAndLast Source therefore specifies only the first and last elements of the levels because it removes all of the other elements of the levels from the selection. The firstAndLast Source still has levelList as an input.
The final join operation matches the input of firstAndLast to levelList.
Example 7-6 Selecting the First and Last Time Elements
MdmAttribute mdmTimeLevelAttr = mdmTimeDim.getLevelAttribute();
MdmLevelHierarchy mdmTimeHier = (MdmLevelHierarchy)
mdmTimeDim.getDefaultHierarchy();
Source levelRel = mdmTimeLevelAttr.getSource();
StringSource calendar = (StringSource) mdmTimeHier.getSource();
Source[] levelSources = new Source[3];
List levels = mdmTimeHier.getLevels();
for (int i = 0; i < levelSources.length; i++)
{
levelSources[i] = ((MdmLevel) levels.get(i)).getSource();
}
Source levelList = dp.createListSource(levelSources);
Source levelMembers = calendar.join(levelRel, levelList.value());
Source range = dp.createRangeSource(2, levelMembers.count().minus(1));
Source firstAndLast = levelMembers.join(levelMembers.position(),
range
Source.COMPARISON_RULE_REMOVE,
true);
Source result = firstAndLast.join(levelList);
A Cursor for the result Source has the following values, displayed as a table with column headings and formatting added. The left column names the level, the middle column is the position of the element in the level, and the right column is the local value of the element.
Level Level Position Level Value ----- -------------- ----------- YEAR 1 1 YEAR 7 119 QUARTER 1 5 QUARTER 26 116 MONTH 1 19 MONTH 77 107
You use the recursiveJoin method to produce a Source that has its elements ordered hierarchically. You use the recursiveJoin method only with the Source for an MdmHierarchy or on a subtype of such a Source. The method produces a Source whose elements are ordered hierarchically by the parents and their children in the hierarchy.
Like the join method, you use the recursiveJoin method to produce a Source that has the elements of its base Source that are determined by the joined, comparison, and comparisonRule parameters of the method. The visible parameter determines whether the joined Source is an output of the Source produced by the recursive join operation.
The recursiveJoin method has several signatures. The full recursiveJoin method has parameters that specify the parent attribute of the hierarchy, whether the result should have the parents before or after their children, how to order the elements of the result if the result includes children but not the parent, and whether the joined Source is an output of the resulting Source.
Example 7-7 uses a recursiveJoin method that lists the parents first, restricts the parents to the base, and does not add the joined Source is an output. The example first sorts the elements of a hierarchy of the PRODUCT dimension by hierarchical levels and then by the value of the color attribute of each element.
The first recursiveJoin method orders the elements of the prodRollup hierarchy in ascending hierarchical order. The prodParent object is the Source for the parent attribute of the hierarchy.
The prodColorAttr object in the second recursiveJoin method is the Source for a color attribute of the hierarchy. Only the elements of the ITEM level of the hierarchy have a related color value. Because the elements in the aggregate levels TOTAL_PRODUCT, CLASS, and FAMILY, do not have related colors, the color attribute value for elements in those levels is null, which appears as NA in the results. Some of the ITEM level elements do not have a related color, so their values are NA, also.
The second recursiveJoin method joins the color attribute values to their related hierarchy elements and sorts the elements hierarchically by level, and then sorts them in ascending order in the level by the color value. The COMPARISON_RULE_ASCENDING_NULLS_FIRST parameter specifies that elements that have a null value appear before the other elements in the same level. The example then joins the result of the method, sortedHierNullsFirst, to the color attribute to produce a Source that has the color values as its element values and sortedHierNullsFirst as an output.
The third recursiveJoin method is the same as the second, except that the COMPARISON_RULE_ASCENDING_NULLS_LAST parameter specifies that elements that have a null value appear after the other elements in the same level.
Example 7-7 Sorting Products Hierarchically By Color
Source result1 =
prodRollup.recursiveJoin(prodDim.value(),
prodRollup.getDataType(),
prodParent,
Source.COMPARISON_RULE_ASCENDING);
Source sortedHierNullsFirst =
prodRollup.recursiveJoin(prodColorAttr,
prodColorAttr.getDataType(),
prodParent,
Source.COMPARISON_RULE_ASCENDING_NULLS_FIRST);
Source result2 = prodColorAttr.join(sortedHierNullsFirst);
Source sortedHierNullsLast =
prodRollup.recursiveJoin(prodColorAttr,
prodColorAttr.getDataType(),
prodParent,
Source.COMPARISON_RULE_DESCENDING_NULLS_LAST);
Source result3 = prodColorAttr.join(sortedHierNullsLast);
A Cursor for the result1 Source has the following values, displayed with a heading added. The list contains only the first ten values of the Cursor.
Product Dimension Element Value -------------------------------- PRODUCT_ROLLUP::TOTAL_PRODUCT::1 PRODUCT_ROLLUP::CLASS::2 PRODUCT_ROLLUP::FAMILY::4 PRODUCT_ROLLUP::ITEM::13 PRODUCT_ROLLUP::ITEM::14 PRODUCT_ROLLUP::ITEM::15 PRODUCT_ROLLUP::FAMILY::5 PRODUCT_ROLLUP::ITEM::16 PRODUCT_ROLLUP::ITEM::17 PRODUCT_ROLLUP::ITEM::18 ...
A Cursor for the result2 Source has the following values, displayed as a table with headings added. The table contains only the first ten values of the Cursor. The left column has the element values of the hierarchy, and the right column has the color attribute value for the element.
The ITEM level elements that have a null value appear first, and then the other level elements appear in ascending order of color value. Since the data type of the color attribute is String, the color values are in ascending alphabetical order.
Product Dimension Element Value Color Value ------------------------------- ----------- PRODUCT_ROLLUP::TOTAL_PRODUCT::1 NA PRODUCT_ROLLUP::CLASS::2 NA PRODUCT_ROLLUP::FAMILY::4 NA PRODUCT_ROLLUP::ITEM::14 NA PRODUCT_ROLLUP::ITEM::15 Black PRODUCT_ROLLUP::ITEM::13 Silver PRODUCT_ROLLUP::FAMILY::5 NA PRODUCT_ROLLUP::ITEM::18 NA PRODUCT_ROLLUP::ITEM::17 Beige PRODUCT_ROLLUP::ITEM::16 Silver ...
A Cursor for the result3 Source has the following values, displayed as a table with headings added. This time the elements are in descending order, alphabetically by color attribute value.
Product Dimension Element Value Color Value ------------------------------- ----------- PRODUCT_ROLLUP::TOTAL_PRODUCT::1 NA PRODUCT_ROLLUP::CLASS::2 NA PRODUCT_ROLLUP::FAMILY::4 NA PRODUCT_ROLLUP::ITEM::14 NA PRODUCT_ROLLUP::ITEM::13 Silver PRODUCT_ROLLUP::ITEM::15 Black PRODUCT_ROLLUP::FAMILY::5 NA PRODUCT_ROLLUP::ITEM::18 NA PRODUCT_ROLLUP::ITEM::16 Silver PRODUCT_ROLLUP::ITEM::17 Beige ...
You use the value method to create a Source that has itself as an input. That relationship enables you to select a subset of elements of the Source.
Example 7-8 demonstrates the selection of such a subset. In the example, shipRollup is a Source for the SHIPMENTS_ROLLUP hierarchy of the CUSTOMER dimension. The selectValues method of shipRollup produces custSel, which is a selection of some of the elements of shipRollup. The selectValues method of custSel produces custSel2, which is a subset of that selection.
The first join method has custSel as the base and as the joined Source. It has custSel2 as the comparison Source. The elements of the resulting Source, result1, are one set of the elements of custSel for each element of custSel that is in the comparison Source. The true value of the visible parameter causes the joined Source to be an output of result1.
The second join method also has custSel as the base and custSel2 as the comparison Source, but it has the result of the custSel.value() method as the joined Source. Because custSel is an input of the joined Source, the base Source matches that input. That input relationship causes the resulting Source, result2, to have only those elements of custSel that are also in the comparison Source.
Example 7-8 Selecting a Subset of the Elements of a Source
StringSource custSel = (StringSource) shipRollup.selectValues(new String[]
{"SHIPMENTS_ROLLUP::SHIP_TO::60",
"SHIPMENTS_ROLLUP::SHIP_TO::61",
"SHIPMENTS_ROLLUP::SHIP_TO::62",
"SHIPMENTS_ROLLUP::SHIP_TO::63"});
Source custSel2 = custSel.selectValues(new String[]
{"SHIPMENTS_ROLLUP::SHIP_TO::60",
"SHIPMENTS_ROLLUP::SHIP_TO::62"});
Source result1 = custSel.join(custSel, custSel2, true);
Source result2 = custSel.join(custSel.value(), custSel2, true);
A Cursor for result1 has the following values, displayed as a table with headings added. The left column has the values of the elements of the output of the Cursor. The right column has the values of the Cursor.
Output Value result1 Value ----------------------------- ----------------------------- SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::61 SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::63 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::61 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::63
A Cursor for result2 has the following values, displayed as a table with headings added. The left column has the values of the elements of the output of the Cursor. The right column has the values of the Cursor.
Output Value result2 Value ----------------------------- ----------------------------- SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::60 SHIPMENTS_ROLLUP::SHIP_TO::62 SHIPMENTS_ROLLUP::SHIP_TO::62
Along with the methods that are various signatures of the basic methods, the Source class has many other methods that use combinations of the basic methods. Some methods perform selections based on a single position, such as the at and offset methods. Others operate on a range of positions, such as the interval method. Some perform comparisons, such as eq and gt, select one or more elements, such as selectValue or removeValue, or sort elements, such as sortAscending or sortDescendingHierarchically.
The subclasses of Source each have other specialized methods, also. For example, the NumberSource class has many methods that perform mathematical functions such as abs, div, and cos, and methods that perform aggregations, such as average and total.
This section has examples that demonstrate the use of some of the Source methods. Some of the examples are tasks that an OLAP application typically performs.
One typical OLAP operation is the creation of a cube, which is a multi-dimensional array of data. The data of the cube is specified by the elements of the column, row, and page edges of the cube. The data of the cube can be data from a measure that is specified by the elements of the dimensions of the measure. The cube data can also be dimension elements that are specified by some calculation of the measure data, such as products that have unit sales quantities greater than a specified amount.
Most of the examples in this section create cubes. Example 7-9 creates a cube that has the quantity of units sold as its data. The column edge values are initially from a channel dimension hierarchy, the row edge values are from a time dimension hierarchy, and the page edge values of the cube are from elements of hierarchies for product and customer dimensions. The product and customer elements on the page edge are represented by parameterized Source objects.
The example joins the selections of the dimension elements to the short value description attributes for the dimensions so that the results have more information than just the numerical identifications of the dimension values. It then joins the Source objects derived from the dimensions to the Source for the measure to produce the cube query. It prepares and commits the current Transaction, and then creates a Cursor for the query and displays its values.
After displaying the values of the Cursor, the example changes the value of the Parameter for the parameterized Source for the customer selection, thereby retrieving a different result set using the same Cursor in the same Transaction. The example resets the position of the Cursor, and displays the values of the Cursor again.
The example then pivots the column and row edges so that the column values are time elements and the row values are channel elements. It prepares and commits the Transaction, creates another Cursor for the query, and displays its values. It then changes the value of each Parameter object and displays the values of the Cursor again.
The dp object is the DataProvider. The context object has a method that displays the values of the Cursor in a crosstab format.
Example 7-9 Creating a Cube and Pivoting Its Edges
// Create Parameter objects with values from the default hierarchies
// of the CUSTOMER and PRODUCT dimensions.
StringParameter custParam =
new StringParameter(dp, "SHIPMENTS_ROLLUP::REGION::9");
StringParameter prodParam =
new StringParameter(dp, "PRODUCT_ROLLUP::FAMILY::4");
// Create parameterized Source objects using the Parameter objects.
StringSource custParamSrc = dp.createParameterizedSource(custParam);
StringSource prodParamSrc = dp.createParameterizedSource(prodParam);
// Select single values from the hierarchies, using the Parameter
// objects as the comparisons in the join operations.
Source paramCustSel = custHier.join(custHier.value(), custParamSrc);
Source paramProdSel = prodHier.join(prodHier.value(), prodParamSrc);
// Select elements from the other dimensions of the measure
Source timeSel = timeHier.selectValues(new String[]
{"CALENDAR::YEAR::2"
"CALENDAR::YEAR::3",
"CALENDAR::YEAR::4"});
Source chanSel = chanHier.selectValues(new String[]
{"CHANNEL_ROLLUP::CHANNEL::2",
"CHANNEL_ROLLUP::CHANNEL::3",
"CHANNEL_ROLLUP::CHANNEL::4"});
// Join the dimension selections to the short description attributes
// for the dimensions.
Source columnEdge = chanSel.join(chanShortDescr);
Source rowEdge = timeSel.join(timeShortDescr);
Source page1 = paramProdSel.join(prodShortDescr);
Source page2 = paramCustSel.join(custShortDescr);
// Join the dimension selections to the measure.
Source cube = units.join(columnEdge)
.join(rowEdge)
.join(page2)
.join(page1);
// Get the TransactionProvider.
TransactionProvider tp = context.getTransactionProvider();
// Prepare and commit the currentTransaction.
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
context.println("Cannot prepare the current Transaction. " + e
}
tp.commitCurrentTransaction();
// Create a Cursor for the query.
CursorManagerSpecification cMngrSpec =
dp.createCursorManagerSpecification(cube);
SpecifiedCursorManager spCMngr = dp.createCursorManager(cMngrSpec);
Cursor cubeCursor = spCMngr.createCursor();
// Display the values of the Cursor as a crosstab.
context.displayCursorAsCrosstab(cubeCursor);
// Change the customer parameter value.
custParam.setValue("SHIPMENTS_ROLLUP::REGION::10");
// Reset the Cursor position to 1 and display its values again.
cubeCursor.setPosition(1);
context.println(" ");
context.displayCursorAsCrosstab(cubeCursor);
// Pivot the column and row edges.
columnEdge = timeSel.join(timeShortDescr);
rowEdge = chanSel.join(chanShortDescr);
// Join the dimension selections to the measure.
cube = units.join(columnEdge)
.join(rowEdge))
.join(page2)
.join(page1);
// Prepare and commit the current Transaction.
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
context.println("Cannot prepare the current Transaction. " + e);
}
tp.commitCurrentTransaction();
// Create another Cursor.
cMngrSpec = dp.createCursorManagerSpecification(cube);
spCMngr = dp.createCursorManager(cMngrSpec);
cubeCursor = spCMngr.createCursor();
context.displayCursorAsCrosstab(cubeCursor);
// Change the product parameter value.
prodParam.setValue("PRODUCT_ROLLUP::FAMILY::5");
// Reset the Cursor position to 1
cubeCursor.setPosition(1);
context.println(" ");
context.displayCursorAsCrosstab(cubeCursor);
The following crosstab has the values of cubeCursor displayed by the first displayCursorAsCrosstab method.
Portable PCs
Europe
Direct Sales Catalog Internet
1999 86 1986 0
2000 193 1777 10
2001 196 1449 215
The following crosstab has the values of cubeCursor after the example changed the value of the custParam Parameter object.
Portable PCs
North America
Direct Sales Catalog Internet
1999 385 6841 0
2000 622 6457 35
2001 696 5472 846
The next crosstab has the values of cubeCursor after pivoting the column and row edges.
Portable PCs
North America
1999 2000 2001
Direct Sales 385 622 696
Catalog 6841 6457 5472
Internet 0 35 846
The last crosstab has the values of cubeCursor after changing the value of the prodParam Parameter object.
Desktop PCs
North America
1999 2000 2001
Direct Sales 793 1224 1319
Catalog 14057 1321 11337
Internet 0 69 1748
Drilling up or down in a dimension hierarchy is another typical OLAP operation. Example 7-10 demonstrates getting the elements of one level of a dimension hierarchy, selecting an element, and then getting the parent, children, and ancestors of the element.
The example uses the following objects.
levelSrc, which is the Source for the FAMILY level of the PRODUCT_ROLLUP hierarchy of the PRODUCT dimension.
prodRollup, which is the Source for the PRODUCT_ROLLUP hierarchy.
prodRollupParentAttr, which is the Source for the parent attribute of the hierarchy.
prodRollupAncsAttr, which is the Source for the ancestors attribute of the hierarchy.
prodShortLabel, which is the Source for the short value description attribute of the PRODUCT dimension.
context, which has methods that prepare and commit the current Transaction, that create a Cursor for a Source, that display text, and that display the values of the Cursor.
Example 7-10 Drilling in a Hierarchy
int pos = 2;
// Get the element at the specified position of the level Source.
Source levelElement = levelSrc.at(pos);
// Select the element of the hierarchy with the specified value.
Source levelSel = prodRollup.join(prodRollup.value(), levelElement);
// Get ancestors of the level element.
Source levelElementAncs = prodRollupAncsAttr.join(prodRollup,
levelElement);
// Get the parent of the level element.
Source levelElementParent = prodRollupParentAttr.join(prodRollup,
levelElement);
// Get the children of a parent.
Source prodRollupChildren = prodRollup.join(prodRollupParentAttr,
prodRollup.value());
// Select the children of the level element.
Source levelElementChildren = prodRollupChildren.join(prodRollup,
levelElement);
// Get the short value descriptions for the elements of the level.
Source levelSrcWithShortDescr = prodShortLabel.join(levelSrc);
// Get the short value descriptions for the children.
Source levelElementChildrenWithShortDescr =
prodShortLabel.join(levelElementChildren);
// Get the short value descriptions for the parents.
Source levelElementParentWithShortDescr =
prodShortLabel.join(prodRollup, levelElementParent, true);
// Get the short value descriptions the ancestors.
Source levelElementAncsWithShortDescr =
prodShortLabel.join(prodRollup, levelElementAncs, true);
// Prepare and commit the current Transaction.
context.commit();
// Create Cursor objects and display their values.
context.println("Level element values:");
context.displayResult(levelSrcWithShortDescr);
context.println("\nLevel element at position " + pos + ":");
context.displayResult(levelElement);
context.println("\nParent of the level element:");
context.displayResult(levelElementParent);
context.println("\nChildren of the level element:");
context.displayResult(levelElementChildrenWithShortDescr);
context.println("\nAncestors of the level element:");
context.displayResult(levelElementAncs);
The following list has the values of the Cursor objects created by the displayResults methods.
Level element values: 1: (PRODUCT_ROLLUP::FAMILY::4,Portable PCs) 2: (PRODUCT_ROLLUP::FAMILY::5,Desktop PCs) 3: (PRODUCT_ROLLUP::FAMILY::6,Operating Systems) 4: (PRODUCT_ROLLUP::FAMILY::7,Accessories) 5: (PRODUCT_ROLLUP::FAMILY::8,Monitors) 6: (PRODUCT_ROLLUP::FAMILY::9,Modems/Fax) 7: (PRODUCT_ROLLUP::FAMILY::10,Memory) 8: (PRODUCT_ROLLUP::FAMILY::11,CD-ROM) 9: (PRODUCT_ROLLUP::FAMILY::12,Documentation) Level element at position 2: 1: PRODUCT_ROLLUP::FAMILY::5 Parent of the level element: 1: (PRODUCT_ROLLUP::CLASS::2,Hardware) Children of the level element: 1: (PRODUCT_ROLLUP::ITEM::16,Sentinel Standard) 2: (PRODUCT_ROLLUP::ITEM::17,Sentinel Financial) 3: (PRODUCT_ROLLUP::ITEM::18,Sentinel Multimedia) Ancestors of the level element: 1: (PRODUCT_ROLLUP::TOTAL_PRODUCT::1,Total Product) 2: (PRODUCT_ROLLUP::CLASS::2,Hardware) 3: (PRODUCT_ROLLUP::FAMILY::5,Desktop PCs)
Example 7-11 uses the recursiveJoin method to sort the elements of the PRODUCT_ROLLUP hierarchy of the PRODUCT dimension hierarchically in ascending order of the values of the UNITS measure. The example joins the sorted products to the short value description attribute of the dimension, and then joins the result of that operation, sortedProductsShortDescr, to units.
The successive joinHidden methods join the selections of the other dimensions of units to produce the result Source, which has the measure data as its element values and sortedProductsShortDescr as its output. The example uses the joinHidden methods so that the other dimension selections are not outputs of the result.
The example uses the following objects.
prodRollup, which is the Source for the PRODUCT_ROLLUP hierarchy.
units, which is the Source for the UNITS measure of product units sold.
prodParent, which is the Source for the parent attribute of the PRODUCT_ROLLUP hierarchy.
prodRollupAncsAttr, which is the Source for the ancestors attribute of the hierarchy.
prodShortDescr, which is the Source for the short value description attribute of the PRODUCT dimension.
custSel, which is a Source that specifies a single element of the default hierarchy of the CUSTOMER dimension. Its value is SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1, which is all customers.
chanSel, which is a Source that specifies a single element of the default hierarchy of the CHANNEL dimension. Its value is CHANNEL_ROLLUP::CHANNEL::2, which is the direct sales channel.
timeSel, which is a Source that specifies a single element of the default hierarchy of the TIME dimension. Its value is CALENDAR::YEAR::4, which is the year 2001.
Example 7-11 Hierarchical Sorting by Measure Value
Source sortedProduct =
prodRollup.recursiveJoin(units,
units.getDataType(),
prodParent,
Source.COMPARISON_RULE_ASCENDING,
true, // Parents first
true); // Restrict parents to base
Source sortedProductShortDescr = prodShortDescr.join(sortedProduct);
Source result = units.join(sortedProductShortDescr)
.joinHidden(custSel)
.joinHidden(chanSel)
.joinHidden(timeSel);
A Cursor for the result Source has the following values, displayed in a table with column headings and formatting added. The left column has the name of the level in the PRODUCT_ROLLUP hierarchy. The next column to the right has the product identification value, and the next column has the short value description of the product. The rightmost column has the number of units of the product sold to all customers in the year 2001 through the direct sales channel.
The table contains only the first nine and the last ten values of the Cursor, plus the Software/Other class value. The product values are listed in hierarchical order by units sold. The Hardware class appears before the Software/Other class because the Software/Other class has a greater number of units sold. In the Hardware class, the Monitors family sold the fewest units, so it appears first. In the Software/Other class, the Accessories family has the greatest number of units sold, so it appears last.
Product Level ID Description Units Sold
------------- -- ---------------------------- ----------
TOTAL_PRODUCT 1 Total Product 43,783
CLASS 2 Hardware 16,541
FAMILY 4 Portable PCs 1,192
ITEM 15 Envoy Ambassador 330
ITEM 14 Envoy Executive 385
ITEM 13 Envoy Standard 477
FAMILY 8 Monitors 1,193
ITEM 21 Monitor- 19 Super VGA 207
ITEM 20 Monitor- 15 Super VGA 986
...
CLASS 3 Software/Other 27,242
...
FAMILY 7 Accessories 18,949
ITEM 22 Envoy External Keyboard 146
ITEM 23 External 101-key keyboard 678
ITEM 32 Multimedia speakers- 5 cones 717
ITEM 46 Standard Mouse 868
ITEM 27 Multimedia speakers- 3 cones 1,120
ITEM 31 1.44MB External 3.5 Diskette 1,145
ITEM 48 Keyboard Wrist Rest 2,231
ITEM 19 Laptop carrying case 3,704
ITEM 47 Deluxe Mouse 3,884
ITEM 30 Mouse Pad 4,456
Example 7-12 uses the NumberSource methods div and times to produce a Source that specifies the share that the Desktop PC and Portable PC families have of the total quantity of product units sold for the selected time, customer, and channel values. The example first uses the selectValue method of prodRollup, which is the Source for a hierarchy of the PRODUCT dimension, to produce allProds, which specifies a single element with the value PRODUCT_ROLLUP::TOTAL_PRODUCT::1, which is the highest aggregate level of the hierarchy.
The joinHidden method of the NumberSource units produces totalUnits, which specifies the UNITS measure values at the total product level, without having allProds appear as an output of totalUnits. The div method of units then produces a Source that represents each units sold value divided by total quantity of units sold. The times method then multiplies the result of that div operation by 100 to produce productShare, which represents the percentage, or share, that a product element has of the total quantity of units sold. The productShare Source has the inputs of the units measure as its inputs.
The prodFamilies object is the Source for the FAMILY level of the PRODUCT_ROLLUP hierarchy. The join method of productShare, with prodFamilies as the joined Source, produces a Source that specifies the share that each product family has of the total quantity of products sold.
The custSel, chanSel, and timeSel Source objects are selections of single elements of hierarchies of the CUSTOMER, CHANNEL, and TIME dimensions. The remaining join methods match those Source objects to the other inputs of productShare, to produce result. The join(Source joined, String comparison) signature of the join method produces a Source that does not have the joined Source as an output.
The result Source specifies the share for each product family of the total quantity of products sold to all customers through the direct sales channel in the year 2001.
Example 7-12 Getting the Share of Units Sold
Source allProds = prodRollup.selectValue("PRODUCT_ROLLUP::TOTAL_PRODUCT::1");
NumberSource totalUnits = (NumberSource) units.joinHidden(allProds);
Source productShare = units.div(totalUnits).times(100);
Source result =
productShare.join(prodFamilies)
.join(timeHier, "CALENDAR::YEAR::4")
.join(chanHier, "CHANNEL_ROLLUP::CHANNEL::2")
.join(custHier, "SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1");
A Cursor for the result Source has the following values, displayed in a table with column headings and formatting added. The left column has the product family value and the right column has the share of the total number of units sold for the product family to all customers through the direct sales channel in the year 2001.
Product Family Element Share of Total Units Sold ------------------------- ------------------------- PRODUCT_ROLLUP::FAMILY::4 2.72% PRODUCT_ROLLUP::FAMILY::5 5.13% PRODUCT_ROLLUP::FAMILY::6 12.54% PRODUCT_ROLLUP::FAMILY::7 43.28% PRODUCT_ROLLUP::FAMILY::8 2.73% PRODUCT_ROLLUP::FAMILY::9 11.92% PRODUCT_ROLLUP::FAMILY::10 3.57% PRODUCT_ROLLUP::FAMILY::11 11.71% PRODUCT_ROLLUP::FAMILY::12 6.4%
Example 7-13 produces two results. The first is result1, which is a Source that specifies the rank of two families of products and their members in the order of the sales of all product units. The second is result2, which ranks those families and their members by quantity of units sold compared to each other.
The units object is the Source for the UNITS measure, and prodRollup is the Source for the PRODUCT_ROLLUP hierarchy of the PRODUCT dimension. The join method of units produces a Source that specifies units sold values for each element of the hierarchy.
The select method has as its filter parameter the BooleanSource produced by the gt method of the Source that results from the units.value() method. The Source that results from the select method has units as an input. When a join operation matches a Source to that input, it produces a Source that, for each element of the units measure, has the Boolean value true for every units sold value that is greater than the current element value.
The count method then produces a Source that has, for each element of the measure, the total number of all the products that have greater sales quantities. The product element with the greatest quantity of units sold therefore has a count of zero. The plus method then adds 1 to each count amount so that the rank values begin with the number 1.
The join method of the Source produced by the plus method selects the elements of pcParentsAndChildren from all of the elements of the product hierarchy. The joinHidden methods then match Source objects that specify selections of the dimensions that are the remaining inputs of the units measure to produce result1, which specifies the calculation of the rank of the selected product elements relative to all of the product elements for the customer, time, and channel values.
The methods that product result2 are the same except that the first join produces a Source that specifies the units elements only for the elements of pcParentsAndChildren. The select, gt, count, and plus methods operate on only those selected elements of the hierarchy. The result2 Source therefore specifies the calculation of the rank of the selected product elements relative to each other rather than relative to all product elements.
Example 7-13 Ranking Products by Units Sold
// First result: PC products unit sales ranked relative to all products.
Source result1 = units.join(prodRollup,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
false)
.select(units.value().gt(units)).count().plus(1)
.join(pcParentsAndChildren)
.joinHidden(custSel)
.joinHidden(timeSel)
.joinHidden(chanSel);
// Second result: PC products unit sales ranked relative to each other.
Source result2 = units.join(pcParentsAndChildren,
dp.getEmptySource(),
Source.COMPARISON_RULE_REMOVE,
false)
.select(units.value().gt(units)).count().plus(1)
.join(prodRollup)
.joinHidden(custSel)
.joinHidden(timeSel)
.joinHidden(chanSel);
A Cursor for the result1 Source has the following values, displayed in a table with column headings and formatting added. The left column has the product element value and the right column has the rank of that product compared to all product units sold.
Rank Compared To Product Element Total Products Sold ------------------------- ------------------- PRODUCT_ROLLUP::FAMILY::5 16 PRODUCT_ROLLUP::FAMILY::4 21 PRODUCT_ROLLUP::ITEM::16 29 PRODUCT_ROLLUP::ITEM::17 31 PRODUCT_ROLLUP::ITEM::18 34 PRODUCT_ROLLUP::ITEM::13 35 PRODUCT_ROLLUP::ITEM::14 37 PRODUCT_ROLLUP::ITEM::15 39
A Cursor for the result2 Source has the following values, displayed in a table with column headings and formatting added. The left column has the product element value and the right column has the rank of that product compared to the other product family members.
Rank Compared To Product Element Each Other ------------------------- ---------------- PRODUCT_ROLLUP::FAMILY::5 1 PRODUCT_ROLLUP::FAMILY::4 2 PRODUCT_ROLLUP::ITEM::16 3 PRODUCT_ROLLUP::ITEM::17 4 PRODUCT_ROLLUP::ITEM::18 5 PRODUCT_ROLLUP::ITEM::13 6 PRODUCT_ROLLUP::ITEM::14 7 PRODUCT_ROLLUP::ITEM::15 8
This section has two examples of using methods that operate on a series of time dimension elements. Example 7-14 uses the lag method of unitPrice, which is the Source for the UNIT_PRICE measure, to produce unitPriceLag4, which specifies, for each element of unitPrice, the element of unitPrice that is four time periods before it at the same time dimension level.
In the example, dp is the DataProvider. Its createListSource method creates measuresDim, which has the unitPrice and unitPriceLag4 Source objects as its element values. The extract method of measuresDim gets the values of the elements of measuresDim. The Source produced by the extract method has measuresDim as an extraction input. The first join method matches a Source, measuresDim, to the input of the Source produced by the extract method.
The unitPrice and unitPriceLag4 measures both have the PRODUCT and TIME dimensions as inputs. The second join method matches quarterLevel, which is a Source for the QUARTER level of the CALENDAR hierarchy of the TIME dimension, to the measure input for the TIME dimension, and makes it an output of the resulting Source.
The joinHidden method matches prodSel to the measure input for the PRODUCT dimension, and does not make prodSel an output of the resulting Source. The prodSel Source specifies the single hierarchy element PRODUCT_ROLLUP::FAMILY::5, which is Desktop PCs.
The lagResult Source specifies the aggregate unit prices for each quarter and the aggregate unit prices for the quarter four quarters earlier for the Desktop PC product family.
Example 7-14 Using the Lag Method
NumberSource unitPriceLag4 = unitPrice.lag(mdmTimeHier, 4);
Source measuresDim = dp.createListSource(new Source[] {unitPrice,
unitPriceLag4});
Source lagResult = measuresDim.extract()
.join(measuresDim)
.join(quarterLevel)
.joinHidden(prodSel);
A Cursor for the lagResult Source has the following values, displayed in a table with column headings and formatting added. The left column has the quarter, the middle column has the total of the unit prices for the members of the Desktop PC family for that quarter, and the left column has the total of the unit prices for the quarter four quarters earlier. The first four values in the right column are NA because quarter 5, Q1-98, is the first quarter in the CALENDAR hierarchy. The table includes only the first eight quarters.
Unit Price Quarter Unit Price Four Quarters Before --------------------- ---------- -------------------- CALENDAR::QUARTER::5 16125.24 NA CALENDAR::QUARTER::6 16226.89 NA CALENDAR::QUARTER::7 16039.61 NA CALENDAR::QUARTER::8 15526.53 NA CALENDAR::QUARTER::9 21553.14 16,125.24 CALENDAR::QUARTER::10 21034.61 162,26.89 CALENDAR::QUARTER::11 21135.51 16,039.61 CALENDAR::QUARTER::12 19600.98 15,526.53 ...
Example 7-15 uses the same unitPrice, quarterLevel, and prodSel objects as Example 7-14, but it uses the unitPriceMovingTotal measure as the second element of measuresDim. The unitPriceMovingTotal Source is produced by the movingTotal method of unitPrice. That method provides mdmTimeHier, which is an MdmLevelHierarchy component of the TIME dimension, as its dimension parameter and the integers 0 and 3 as the starting and ending offset values.
The movingTotalResult Source specifies, for each quarter, the aggregate of the unit prices for the members of the Desktop PC family for that quarter and the total of that unit price plus the unit prices for the next three quarters.
Example 7-15 Using the movingTotal Method
NumberSource unitPriceMovingTotal =
unitPrice.movingTotal(mdmTimeHier, 0, 3);
Source measuresDim = dp.createListSource(new Source[]
{unitPrice,
unitPriceMovingTotal});
Source movingTotalResult = measuresDim.extract()
.join(measuresDim)
.join(quarterLevel)
.joinHidden(prodSel);
A Cursor for the movingTotalResult Source has the following values, displayed in a table with column headings and formatting added. The left column has the quarter, the middle column has the total of the unit prices for the members of the Desktop PC family for that quarter, and the left column has the total of the unit prices for that quarter and the next three quarters. The table includes only the first eight quarters.
Unit Price Moving Total Quarter Unit Price Current Plus Next Three Periods -------------------- ---------- ------------------------------- CALENDAR::QUARTER::5 16,125.24 63,918.27 CALENDAR::QUARTER::6 16,226.89 69,346.17 CALENDAR::QUARTER::7 16,039.61 74,153.89 CALENDAR::QUARTER::8 15,526.53 79,249.79 CALENDAR::QUARTER::9 21,553.14 80,206.84 CALENDAR::QUARTER::10 21,034.61 80,206.84 CALENDAR::QUARTER::11 21,135.51 77,638.28 ...
Example 7-16 uses NumberParameter objects to create parameterized Source objects. Those objects are the bottom and top parameters for the interval method of prodRollup. That method produces paramProdSelInterval, which is a Source that specifies the set of elements of prodRollup from the bottom to the top positions of the hierarchy.
The product elements specify the elements of the units measure that appear in the result Source. By changing the values of the Parameter objects, you can select a different set of units sold values using the same Cursor and without having to produce new Source and Cursor objects.
The example uses the following objects.
dp, which is the DataProvider for the session.
prodRollup, which is the Source for the PRODUCT_ROLLUP hierarchy of the PRODUCT dimension.
prodShortDescr, which is the Source for the short value description attribute of the PRODUCT dimension.
units, which is the Source for the UNITS measure of product units sold.
chanRollup, which is the Source for the CHANNEL_ROLLUP hierarchy of the CHANNEL dimension.
calendar, which is the Source for the CALENDAR hierarchy of the TIME dimension.
shipRollup, which is the Source for the SHIPMENTS_ROLLUP hierarchy of the CUSTOMER dimension.
context, which has methods that prepare and commit the current Transaction, that create a Cursor for a Source, that display text, and that display the values of the Cursor.
The join method of prodShortDescr gets the short value descriptions for the elements of paramProdSelInterval. The next four join methods match Source objects to the inputs of the units measure. The example creates a Cursor and displays the result set of the query. Next, the setPosition method of resultCursor sets the position of the Cursor back to its first element.
The setValue methods of the NumberParameter objects change the values of those objects, which changes the selection of product elements specified by the query. The example then displays the values of the Cursor again.
Example 7-16 Selecting a Range With NumberParameter Objects
NumberParameter startParam = new NumberParameter(dp, 1);
NumberParameter endParam = new NumberParameter(dp, 6);
NumberSource startParamSrc = dp.createParameterizedSource(startParam);
NumberSource endParamSrc = dp.createParameterizedSource(endParam);
Source paramProdSelInterval = prodRollup.interval(startParamSrc,
endParamSrc);
Source paramProdSelIntervalShortDescr =
prodShortDescr.join(paramProdSelInterval);
NumberSource result = (NumberSource)
units.join(chanRollup, "CHANNEL_ROLLUP::CHANNEL::4")
.join(calendar, "CALENDAR::YEAR::4")
.join(shipRollup,
"SHIPMENTS_ROLLUP::ALL_CUSTOMERS::1")
.join(paramProdSelIntervalShortDescr);
// Get the TransactionProvider and prepare and commit the
// current transaction. These operations are not shown.
CursorManagerSpecification cMngrSpec =
dp.createCursorManagerSpecification(results);
SpecifiedCursorManager spCMngr = dp.createCursorManager(cMngrSpec);
Cursor resultCursor = spCMngr.createCursor();
context.displayCursor(resultCursor);
//Reset the Cursor position to 1;
resultCursor.setPosition(1);
// Change the value of the parameterized Source
startParam.setValue(7);
endParam.setValue(12);
// Display the results again.
context.displayCursor(resultsCursor);
The following table displays the values of resultCursor, with column headings and formatting added. The left column has the product hierarchy elements, the middle column has the short value description, and the right column has the quantity of units sold.
Product Description Units Sold -------------------------------- ---------------------- ---------- PRODUCT_ROLLUP::TOTAL_PRODUCT::1 Total Product 55,872 PRODUCT_ROLLUP::CLASS::2 Hardware 21,301 PRODUCT_ROLLUP::FAMILY::4 Portable PCs 1,420 PRODUCT_ROLLUP::ITEM::13 Envoy Standard 550 PRODUCT_ROLLUP::ITEM::14 Envoy Executive 482 PRODUCT_ROLLUP::ITEM::15 Envoy Ambassador 388 PRODUCT_ROLLUP::FAMILY::5 Desktop PCs 2,982 PRODUCT_ROLLUP::ITEM::16 Sentinel Standard 1,092 PRODUCT_ROLLUP::ITEM::17 Sentinel Financial 1,015 PRODUCT_ROLLUP::ITEM::18 Sentinel Multimedia 875 PRODUCT_ROLLUP::FAMILY::8 Monitors 1,505 PRODUCT_ROLLUP::ITEM::20 Monitor- 15 Super VGA 1,238
|
 Copyright © 2000, 2003 Oracle Corporation All Rights Reserved. |
|