| Oracle® OLAP Java API Developer's Guide 11g Release 1 (11.1) Part Number B28127-01 |
|
|
View PDF |
| Oracle® OLAP Java API Developer's Guide 11g Release 1 (11.1) Part Number B28127-01 |
|
|
View PDF |
You create a query by producing a Source that specifies the data that you want to retrieve from the data store and any operations that 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:
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 5, "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 6-1 lists the basic Source methods.
Table 6-1 The Basic Source Methods
| Method | Description |
|---|---|
|
Produces a |
|
|
Produces a |
|
|
Produces a |
|
|
Produces a |
|
|
Similar to the |
|
|
Produces a |
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 the associated aliased dimension, and then compare the results.
Example 6-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_YEAR::MONTH::2001.01, which identifies the month January, 2001, the custSel value is SHIPMENTS::SHIP_TO::BUSN WRLD SJ, which identifies the Business Word San Jose customer, and the prodSel value is PRODUCT_PRIMARY::ITEM::ENVY ABM, 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 final join operations match chanAlias1 to the input of unitsSel1 and match chanAlias1 to the input of unitsSel2.
Example 6-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 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 the 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 Total Channel is not greater than the units sold for the row Total Channel value but it is greater than the units sold for the Direct Sales, Catalog, and Internet row values.
----------------- chanAlias1 ---------------- chanAlias2 TotalChannel Catalog Direct Sales Internet ------------ ------------ ------- ------------ -------- TotalChannel false false false false Catalog true false false false Direct Sales true true false false Internet true true true false
You use the distinct method to produce a Source that does not have any duplicated values. Example 6-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 6-2, markets is a StringSource that represents the Markets hierarchy of the Customer dimension. The marketsAncestors object is the Source for the ancestors attribute of that hierarchy. To get a Source that represents the descendants of the ancestors, the example uses the join method to select, for each element of marketsAncestors, the elements of markets that have the marketsAncestors element as their ancestor. The join operation matches the base Source, markets, to the input of the ancestors attribute.
The resulting Source, marketsDescendants, however, still has markets as an input because the Source produced by the markets.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 markets selects the element of markets that has the value MARKETS::ACCOUNT::BUSN WRLD, which is the Business World account, and produces selVal. The join method of marketsDescendants uses selVal as the comparison Source. The method produces selValDescendants, which has the elements of marketsDescendants that are present in markets, and that are also in selVal. The input of marketsDescendants is matched by the joined Source markets. The markets 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 6-2 Using the distinct Method
Source marketsDescendants =
markets.join(marketsAncestors, markets.value());
Source selVal = markets.selectValue("MARKETS::ACCOUNT::BUSN WRLD");
Source selValDescendants = marketsDescendants.join(markets,
selVal,
false);
Source selValPlusDescendants = selVal.appendValues(selValDescendants)
.distinct();
A Cursor for the selValPlusDescendants Source has the following values:
MARKETS::ACCOUNT::BUSN WRLD MARKETS::SHIP_TO::BUSN WRLD HAM MARKETS::SHIP_TO::BUSN WRLD NAN MARKETS::SHIP_TO::BUSN WRLD NY MARKETS::SHIP_TO::BUSN WRLD SJ
If the example did not include the distinct method call, then a Cursor for selValPlusDescendants would have the following values:
MARKETS::ACCOUNT::BUSN WRLD MARKETS::ACCOUNT::BUSN WRLD MARKETS::SHIP_TO::BUSN WRLD HAM MARKETS::SHIP_TO::BUSN WRLD NAN MARKETS::SHIP_TO::BUSN WRLD NY MARKETS::SHIP_TO::BUSN WRLD SJ
You use the join method to produce a Source that has the elements of the 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 6-3 and Example 6-4 demonstrate the use of two of those constants, COMPARISON_RULE_REMOVE and COMPARISON_RULE_DESCENDING. Example 6-5 also uses COMPARISON_RULE_REMOVE.
Example 6-3 produces a result similar to Example 6-2. It uses markets, which is the Source for a hierarchy of the Customer dimension, and marketsAncestors, which is the Source for the ancestors attribute for the hierarchy. It also uses marketsDescendants, 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 marketsDescendants produces marketsDescendantsOnly, which specifies the descendants of markets, and which has markets as an input because the comparison parameter of the join operation is the Source that results from the markets.value() method.
Because COMPARISON_RULE_REMOVE is the comparison rule of the join operation that produced marketsDescendantsOnly, a join operation that matches a Source to the input of marketsDescendantsOnly produces a Source that has only those elements of marketsDescendantsOnly that are not in the comparison Source of the join operation.
The next join operation performs such a match. It matches the joined Source, markets, to the input of marketsDescendantsOnly to produce selValDescendantsOnly, which specifies the descendants of the selected hierarchy value but does not include the selected value because marketsDescendantsOnly 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 6-3 Using COMPARISON_RULE_REMOVE
Source selVal = markets.selectValue("MARKETS::ACCOUNT::BUSN WRLD");
Source marketsDescendantsOnly =
marketsDescendants.join(marketsDescendants.getDataType().value(),
markets.value(),
Source.COMPARISON_RULE_REMOVE);
// Select the descendants of the specified element.
Source selValDescendants = marketsDescendants.join(markets, selVal);
// Select only the descendants of the specified element.
Source selValDescendantsOnly = marketsDescendantsOnly.join(markets,
selVal);
A Cursor for selValDescendants has the following values.
MARKETS::ACCOUNT::BUSN WRLD MARKETS::SHIP_TO::BUSN WRLD HAM MARKETS::SHIP_TO::BUSN WRLD NAN MARKETS::SHIP_TO::BUSN WRLD NY MARKETS::SHIP_TO::BUSN WRLD SJ
A Cursor for selValDescendantsOnly has the following values.
MARKETS::SHIP_TO::BUSN WRLD HAM MARKETS::SHIP_TO::BUSN WRLD NAN MARKETS::SHIP_TO::BUSN WRLD NY MARKETS::SHIP_TO::BUSN WRLD SJ
Example 6-4 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 Primary 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 Year 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 6-4 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
----------------- --------------
2,845.59 Portable PCs
1,871.03 Desktop PCs
415.37 Memory
397.62 Monitors
196.05 CD/DVD
318.61 Modems/Fax
74.68 Documentation
65.92 Operating Systems
36.50 Accessories
You use the position method to produce a Source that has the positions of the elements of the base and has the base as an input. Example 6-5 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 MdmHierarchyLevel 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 element values.
The example then produces levelMembers, which is a Source that specifies the members 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 members of each level, by level, when the 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 members of the levels because it removes all of the other members 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 6-5 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.getHierarchyLevels();
for (int i = 0; i < levelSources.length; i++)
{
levelSources[i] = ((MdmHierarchyLevel) 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 member in the level, and the right column is the local value of the member.
Level Member Position in Level Member Value ---------- ------------------------ ------------ TOTAL_TIME 1 TOTAL YEAR 1 CY1998 YEAR 10 CY2007 QUARTER 1 CY1998.Q1 QUARTER 40 CY2007.Q4 MONTH 1 1998.01 MONTH 120 2007.12
You use the recursiveJoin method to produce a Source that has elements that are ordered hierarchically. You use the recursiveJoin method only with the Source for an MdmHierarchy or with 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 the 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 6-6 uses a recursiveJoin method that lists the parents first, restricts the parents to the base, and does not add the joined Source as 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 package attribute of each element.
The first recursiveJoin method orders the elements of the prodHier hierarchy in ascending hierarchical order. The prodParent object is the Source for the parent attribute of the hierarchy.
The prodPkgAttr object in the second recursiveJoin method is the Source for the package attribute of the dimension. Only the elements of the Item level have a related package value. Because the elements in the aggregate levels Total Product, Class, and Family, do not have a related package, the package 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 package, so their values are NA, also.
The second recursiveJoin method joins the package 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 package 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 package attribute to produce a Source that has the package values as 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 6-6 Sorting Products Hierarchically By Attribute
Source result1 = prodHier.recursiveJoin(prodDim.value(),
prodHier.getDataType(),
prodParent,
Source.COMPARISON_RULE_ASCENDING);
Source sortedHierNullsFirst =
prodHier.recursiveJoin(prodPkgAttr,
prodPkgAttr.getDataType(),
prodParent,
Source.COMPARISON_RULE_ASCENDING_NULLS_FIRST);
Source result2 = prodPkgAttr.join(sortedHierNullsFirst);
Source sortedHierNullsLast =
prodHier.recursiveJoin(prodPkgAttr,
prodPkgAttr.getDataType(),
prodParent,
Source.COMPARISON_RULE_DESCENDING_NULLS_LAST);
Source result3 = prodPkgAttr.join(sortedHierNullsLast);
A Cursor for the result1 Source has the following values, displayed with a heading added. The list contains only the first seventeen values of the Cursor.
Product Dimension Element Value -------------------------------- PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL PRODUCT_PRIMARY::CLASS::HRD PRODUCT_PRIMARY::FAMILY::DISK PRODUCT_PRIMARY::ITEM::EXT CD ROM PRODUCT_PRIMARY::ITEM::EXT DVD PRODUCT_PRIMARY::ITEM::INT 8X DVD PRODUCT_PRIMARY::ITEM::INT CD ROM PRODUCT_PRIMARY::ITEM::INT CD USB PRODUCT_PRIMARY::ITEM::INT RW DVD PRODUCT_PRIMARY::FAMILY::DTPC PRODUCT_PRIMARY::ITEM::SENT FIN PRODUCT_PRIMARY::ITEM::SENT MM PRODUCT_PRIMARY::ITEM::SENT STD PRODUCT_PRIMARY::FAMILY::LTPC PRODUCT_PRIMARY::ITEM::ENVY ABM PRODUCT_PRIMARY::ITEM::ENVY EXE PRODUCT_PRIMARY::ITEM::ENVY STD ...
A Cursor for the result2 Source has the following values, displayed as a table with headings added. The table contains only the first seventeen values of the Cursor. The left column has the member values of the hierarchy and the right column has the package attribute value for the member.
The Item level elements that have a null value appear first, and then the other level members appear in ascending order of package value. Since the data type of the package attribute is String, the package values are in ascending alphabetical order.
Product Dimension Element Value Package Attribute Value ------------------------------------- ----------------------- PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL NA PRODUCT_PRIMARY::CLASS::HRD NA PRODUCT_PRIMARY::FAMILY::DISK NA PRODUCT_PRIMARY::ITEM::EXT CD ROM NA PRODUCT_PRIMARY::ITEM::INT 8X DVD NA PRODUCT_PRIMARY::ITEM::INT CD USB NA PRODUCT_PRIMARY::ITEM::EXT DVD Executive PRODUCT_PRIMARY::ITEM::INT CD ROM Laptop Value Pack PRODUCT_PRIMARY::ITEM::INT RW DVD Multimedia PRODUCT_PRIMARY::FAMILY::DTPC NA PRODUCT_PRIMARY::ITEM::SENT FIN NA PRODUCT_PRIMARY::ITEM::SENT STD NA PRODUCT_PRIMARY::ITEM::SENT MM Multimedia PRODUCT_PRIMARY::FAMILY::LTPC NA RODUCT_PRIMARY::ITEM::ENVY ABM NA PRODUCT_PRIMARY::ITEM::ENVY EXE Executive PRODUCT_PRIMARY::ITEM::ENVY STD Laptop Value Pack ...
A Cursor for the result3 Source has the following values, displayed as a table with headings added. This time the members are in descending order, alphabetically by package attribute value.
Product Dimension Element Value Package Attribute Value ------------------------------------- ----------------------- PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL NA PRODUCT_PRIMARY::CLASS::HRD NA PRODUCT_PRIMARY::FAMILY::DISK NA PRODUCT_PRIMARY::ITEM::EXT CD ROM NA PRODUCT_PRIMARY::ITEM::INT 8X DVD NA PRODUCT_PRIMARY::ITEM::INT CD USB NA PRODUCT_PRIMARY::ITEM::INT RW DVD Multimedia PRODUCT_PRIMARY::ITEM::INT CD ROM Laptop Value Pack PRODUCT_PRIMARY::ITEM::EXT DVD Executive PRODUCT_PRIMARY::FAMILY::DTPC NA PRODUCT_PRIMARY::ITEM::SENT FIN NA PRODUCT_PRIMARY::ITEM::SENT STD NA PRODUCT_PRIMARY::ITEM::SENT MM Multimedia PRODUCT_PRIMARY::FAMILY::LTPC NA PRODUCT_PRIMARY::ITEM::ENVY ABM NA PRODUCT_PRIMARY::ITEM::ENVY STD Laptop Value Pack PRODUCT_PRIMARY::ITEM::ENVY EXE Executive ...
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 6-7 demonstrates the selection of such a subset. In the example, shipHier is a Source for the SHIPMENTS hierarchy of the Customer dimension. The selectValues method of shipHier produces custSel, which is a selection of some of the elements of shipHier. 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 6-7 Selecting a Subset of the Elements of a Source
StringSource custSel = (StringSource) shipHier.selectValues(new String[]
{"SHIPMENTS::SHIP_TO::COMP WHSE SIN",
"SHIPMENTS::SHIP_TO::COMP WHSE LON",
"SHIPMENTS::SHIP_TO::COMP WHSE SJ",
"SHIPMENTS::SHIP_TO::COMP WHSE ATL"});
Source custSel2 = custSel.selectValues(new String[]
{"SHIPMENTS::SHIP_TO::COMP WHSE SIN",
"SHIPMENTS::SHIP_TO::COMP WHSE SJ"});
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::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE ATL SHIPMENTS::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE LON SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE ATL SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE LON
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::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE SJ SHIPMENTS::SHIP_TO::COMP WHSE SIN SHIPMENTS::SHIP_TO::COMP WHSE SIN
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.
You use the extract method to extract the values of a Source that has Source objects as element values. If the elements of a Source have element values that are not Source objects, then the extract method operates like the value method.
Example 6-8 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 example selects values from hierarchies of the dimensions of the NumberSource for the Units and Sales measures. Two of those dimensions are the dimensions of the NumberSource for the Unit Price measure.
Next, the example creates a list Source, measDim, which has the three NumberSource objects as the 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 6-8 Using the extract Method
Source prodSel = prodHier.selectValues(new String[]
{"PRODUCT_PRIMARY::ITEM::ENVY STD",
"PRODUCT_PRIMARY::ITEM::ENVY EXE",
"PRODUCT_PRIMARY::ITEM::ENVY ABM"});
Source chanSel = chanHier.selectValue("CHANNEL_PRIMARY::CHANNEL::DIR");
Source timeSel = timeHier.selectValue("CALENDAR_YEAR::MONTH::2001.05");
Source custSel = custHier.selectValue("SHIPMENTS::TOTAL_CUSTOMER::TOTAL");
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::TOTAL_CUSTOMER::TOTAL CHANNEL_PRIMARY::CHANNEL::DIR CALENDAR_YEAR::MONTH::2001.05 ITEM UNIT PRICE UNITS SOLD SALES AMOUNT -------- ---------- ---------- ------------- ENVY ABM 2,993.29 26 77,825.54 ENVY EXE 3,147.85 37 116,470.45 ENVY STD 2,395.63 39 93,429.57
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 6-9 creates a cube that has the quantity of units sold as the data of the cube. 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 commits the current Transaction, and then creates a Cursor for the query and displays the 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 commits the Transaction, creates another Cursor for the query, and displays the 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 getContext method gets a Context11g object that has a method that displays the values of the Cursor in a crosstab format.
Example 6-9 Creating a Cube and Pivoting the Edges
// Create Parameter objects with values from the default hierarchies
// of the Customer and Product dimensions.
StringParameter custParam =
new StringParameter(dp, "SHIPMENTS::REGION::EMEA");
StringParameter prodParam =
new StringParameter(dp, "PRODUCT_PRIMARY::FAMILY::LTPC");
// Create parameterized Source objects using the Parameter objects.
Source custParamSrc = custParam.createSource();
Source prodParamSrc = prodParam.createSource();
// 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::YEAR::CY1999"
"CALENDAR_YEAR::YEAR::CY2000",
"CALENDAR_YEAR::YEAR::CY2001"});
Source chanSel = chanHier.selectValues(new String[]
{"CHANNEL_PRIMARY::CHANNEL::DIR",
"CHANNEL_PRIMARY::CHANNEL::CAT ",
"CHANNEL_PRIMARY::CHANNEL::INT"});
// 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);
// The following method commits the current Transaction.
getContext().commit();
// Create a Cursor for the query.
CursorManager cursorMngr = dp.createCursorManager(cube);
CompoundCursor cubeCursor = (CompoundCursor) cursorMngr.createCursor();
// Display the values of the Cursor as a crosstab.
getContext().displayCursorAsCrosstab(cubeCursor);
// Change the customer parameter value.
custParam.setValue("SHIPMENTS::REGION::AMER");
// Reset the Cursor position to 1 and display the values again.
cubeCursor.setPosition(1);
println();
getContext().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);
// Commit the current Transaction.
getContext().commit();
// Create another Cursor.
cursorMngr = dp.createCursorManager(cube);
cubeCursor = (CompoundCursor) cursorMngr.createCursor();
getContext().displayCursorAsCrosstab(cubeCursor);
// Change the product parameter value.
prodParam.setValue("PRODUCT_PRIMARY::FAMILY::DTPC");
// Reset the Cursor position to 1
cubeCursor.setPosition(1);
println();
getContext().displayCursorAsCrosstab(cubeCursor);
The following crosstab has the values of cubeCursor displayed by the first displayCursorAsCrosstab method.
Portable PCs
Europe
Catalog Direct Sales Internet
1999 1986 86 0
2000 1777 193 10
2001 1449 196 215
The following crosstab has the values of cubeCursor after the example changed the value of the custParam Parameter object.
Portable PCs
North America
Catalog Direct Sales Internet
1999 6841 385 0
2000 6457 622 35
2001 5472 696 846
The next crosstab has the values of cubeCursor after pivoting the column and row edges.
Portable PCs
North America
1999 2000 2001
Catalog 6841 6457 5472
Direct Sales 385 622 696
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
Catalog 14057 13210 11337
Direct Sales 793 1224 1319
Internet 0 69 1748
Drilling up or down in a dimension hierarchy is another typical OLAP operation. Example 6-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 Primary hierarchy of the Product dimension.
prodHier, which is the Source for the Product Primary hierarchy.
prodHierParentAttr, which is the Source for the parent attribute of the hierarchy.
prodHierAncsAttr, 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.
Example 6-10 Drilling in a Hierarchy
int pos = 5;
// 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 = prodHier.join(prodHier.value(), levelElement);
// Get ancestors of the level element.
Source levelElementAncs = prodHierAncsAttr.join(prodHier, levelElement);
// Get the parent of the level element.
Source levelElementParent = prodHierParentAttr.join(prodHier, levelElement);
// Get the children of a parent.
Source prodHierChildren = prodHier.join(prodHierParentAttr, prodHier.value());
// Select the children of the level element.
Source levelElementChildren = prodHierChildren.join(prodHier, 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(prodHier, levelElementParent, true);
// Get the short value descriptions for the ancestors.
Source levelElementAncsWithShortDescr =
prodShortLabel.join(prodHier, levelElementAncs, true);
// Commit the current Transaction.
getContext().commit();
// Create Cursor objects and display their values.
println("Level element values:");
getContext().displayResult(levelSrcWithShortDescr);
println("\nLevel element at position " + pos + ":");
getContext().displayResult(levelElement);
println("\nParent of the level element:");
getContext().displayResult(levelElementParent);
println("\nChildren of the level element:");
getContext().displayResult(levelElementChildrenWithShortDescr);
println("\nAncestors of the level element:");
getContext().displayResult(levelElementAncs);
The following list has the values of the Cursor objects created by the displayResults methods.
Level element values: 1: (PRODUCT_PRIMARY::FAMILY::ACC,Accessories) 2: (PRODUCT_PRIMARY::FAMILY::DISK,CD/DVD) 3: (PRODUCT_PRIMARY::FAMILY::DOC,Documentation) 4: (PRODUCT_PRIMARY::FAMILY::DTPC,Portable PCs) 5: (PRODUCT_PRIMARY::FAMILY::LTPC,Desktop PCs) 6: (PRODUCT_PRIMARY::FAMILY::MEM,Memory) 7: (PRODUCT_PRIMARY::FAMILY::MOD,Modems/Fax) 8: (PRODUCT_PRIMARY::FAMILY::MON,Monitors) 9: (PRODUCT_PRIMARY::FAMILY::OS,Operating Systems) Level element at position 5: 1: PRODUCT_PRIMARY::FAMILY:LTPC Parent of the level element: 1: (PRODUCT_PRIMARY::CLASS::HRD,Hardware) Children of the level element: 1: (PRODUCT_PRIMARY::ITEM::ENVY ABM,Envoy Ambassador) 2: (PRODUCT_PRIMARY::ITEM::ENVY EXE,Envoy Executive) 3: (PRODUCT_PRIMARY::ITEM::ENVY STD,Envoy Standard) Ancestors of the level element: 1: (PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL,Total Product) 2: (PRODUCT_PRIMARY::CLASS::HRD,Hardware) 3: (PRODUCT_PRIMARY::FAMILY::LTPC,Portable PCs)
Example 6-11 uses the recursiveJoin method to sort the elements of the Product Primary 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 element values and sortedProductsShortDescr as an 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.
prodHier, which is the Source for the Product Primary 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 Primary 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. The value of the element is SHIPMENTS::TOTAL_CUSTOMER::TOTAL, which is the total for all customers.
chanSel, which is a Source that specifies a single element of the default hierarchy of the Channel dimension. The value of the element value is CHANNEL_PRIMARY::CHANNEL::DIR, which is the direct sales channel.
timeSel, which is a Source that specifies a single element of the default hierarchy of the Time dimension. The value of the element value is CALENDAR_YEAR::YEAR::CY2001, which is the year 2001.
Example 6-11 Hierarchical Sorting by Measure Value
Source sortedProduct =
prodHier.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_PRIMARY 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 eleven 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 Portable PCs 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 TOTAL Total Product 43,785 CLASS HRD Hardware 16,543 FAMILY LTPC Portable PCs 1,192 ITEM ENVY ABM Envoy Ambassador 330 ITEM ENVY EXE Envoy Executive 385 ITEM ENVY STD Envoy Standard 477 FAMILY MON Monitors 1,193 ITEM 19 SVGA Monitor- 19" Super VGA 207 ITEM 17 SVGA Monitor- 17"Super VGA 986 ... CLASS SFT Software/Other) 27,242 ... FAMILY ACC Accessories 18,949 ITEM ENVY EXT KBD Envoy External Keyboard 146 ITEM EXT KBD External 101-key keyboard 678 ITEM MM SPKR 5 Multimedia speakers- 5" cones 717 ITEM STD MOUSE Standard Mouse 868 ITEM MM SPKR 3 Multimedia speakers- 3" cones 1,120 ITEM 144MB DISK 1.44MB External 3.5" Diskette 1,145 TEM KBRD REST Keyboard Wrist Rest 2,231 ITEM LT CASE Laptop carrying case 3,704 ITEM DLX MOUSE Deluxe Mouse 3,884 ITEM MOUSE PAD Mouse Pad 4,456
Example 6-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 prodHier, which is the Source for a hierarchy of the Product dimension, to produce totalProds, which specifies a single element with the value PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL, 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 totalProds 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 inputs.
The prodFamilies object is the Source for the Family level of the Product Primary 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 6-12 Getting the Share of Units Sold
Source totalProds =
prodHier.selectValue("PRODUCT_PRIMARY::TOTAL_PRODUCT::TOTAL");
NumberSource totalUnits = (NumberSource) units.joinHidden(totalProds);
Source productShare = units.div(totalUnits).times(100);
Source result =
productShare.join(prodFamilies)
.join(timeHier, "CALENDAR_YEAR::YEAR::CY2001")
.join(chanHier, "CHANNEL_PRIMARY::CHANNEL::DIR")
.join(custHier, "SHIPMENTS::TOTAL_CUSTOMER::TOTAL");
Source sortedResult = result.sortAscending();
A Cursor for the sortedResult 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_PRIMARY::FAMILY::LTPC 2.72% PRODUCT_PRIMARY::FAMILY::MON 2.73% PRODUCT_PRIMARY::FAMILY::MEM 3.57% PRODUCT_PRIMARY::FAMILY::DTPC 5.13% PRODUCT_PRIMARY::FAMILY::DOC 6.4% PRODUCT_PRIMARY::FAMILY::DISK 11.71% PRODUCT_PRIMARY::FAMILY::MOD 11.92% PRODUCT_PRIMARY::FAMILY::OS 12.54% PRODUCT_PRIMARY::FAMILY::ACC 43.28%
This section has two examples of using methods that operate on a series of time dimension elements. Example 6-13 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 hierarchy level.
In the example, dp is the DataProvider. The createListSource method creates measuresDim, which has the unitPrice and unitPriceLag4 Source objects as 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 Year 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_PRIMARY::FAMILY::DTPC, 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 6-13 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 right 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 Year hierarchy. The table includes only the first eight quarters.
Unit Price Quarter Unit Price Four Quarters Before --------------------------------- ---------- -------------------- CALENDAR_YEAR::QUARTER::CY1998.Q1 2687.54 NA CALENDAR_YEAR::QUARTER::CY1998.Q2 2704.48 NA CALENDAR_YEAR::QUARTER::CY1998.Q3 2673.27 NA CALENDAR_YEAR::QUARTER::CY1998.Q4 2587.76 NA CALENDAR_YEAR::QUARTER::CY1999.Q1 2394.79 2687.54 CALENDAR_YEAR::QUARTER::CY1999.Q2 2337.18 2704.48 CALENDAR_YEAR::QUARTER::CY1999.Q3 2348.39 2673.27 CALENDAR_YEAR::QUARTER::CY1999.Q4 2177.89 2587.76 ...
Example 6-14 uses the same unitPrice, quarterLevel, and prodSel objects as Example 6-13, 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 the 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 6-14 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_YEAR::QUARTER::CY1998.Q1 2687.54 10653.05 CALENDAR_YEAR::QUARTER::CY1998.Q2 2704.48 10360.30 CALENDAR_YEAR::QUARTER::CY1998.Q3 2673.27 9993.00 CALENDAR_YEAR::QUARTER::CY1998.Q4 2587.76 9668.12 CALENDAR_YEAR::QUARTER::CY1999.Q1 2394.79 9258.25 CALENDAR_YEAR::QUARTER::CY1999.Q2 2337.18 8911.87 CALENDAR_YEAR::QUARTER::CY1999.Q3 2348.39 8626.48 CALENDAR_YEAR::QUARTER::CY1999.Q4 2177.89 8291.37 ...
Example 6-15 uses NumberParameter objects to create parameterized Source objects. Those objects are the bottom and top parameters for the interval method of prodHier. That method produces paramProdSelInterval, which is a Source that specifies the set of elements of prodHier 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.
prodHier, which is the Source for the Product Primary 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.
chanHier, which is the Source for the Channel Primary hierarchy of the Channel dimension.
calendar, which is the Source for the Calendar Year hierarchy of the Time dimension.
shipHier, which is the Source for the Shipments hierarchy of the Customer dimension.
The Context11g object that is returned by the getContext method. The Context11g has methods that 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 the 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 6-15 Selecting a Range With NumberParameter Objects
NumberParameter startParam = new NumberParameter(dp, 1);
NumberParameter endParam = new NumberParameter(dp, 6);
NumberSource startParamSrc = (NumberSource) startParam.createSource();
NumberSource endParamSrc = (NumberSource) endParam.createSource();
Source paramProdSelInterval = prodHier.interval(startParamSrc,
endParamSrc);
Source paramProdSelIntervalShortDescr =
prodShortDescr.join(paramProdSelInterval);
NumberSource result = (NumberSource)
units.join(chanHier, "CHANNEL_PRIMARY::CHANNEL::INT")
.join(calendar, "CALENDAR_YEAR::YEAR::CY2001")
.join(shipHier, "SHIPMENTS::TOTAL_CUSTOMER::TOTAL")
.join(paramProdSelIntervalShortDescr);
// Commit the current transaction.
getContext().commit();
CursorManager cursorMngr = dp.createCursorManager(result);
Cursor resultCursor = cursorMngr.createCursor();
getContext().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.
getContext().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_PRIMARY::TOTAL_PRODUCT::TOTAL Total Product 55,872
PRODUCT_PRIMARY::CLASS::HRD Hardware 21,301
PRODUCT_PRIMARY::FAMILY::DISK Memory 6,634
PRODUCT_PRIMARY::ITEM::EXT CD ROM External 48X CD-ROM 136
PRODUCT_PRIMARY::ITEM::EXT DVD External - DVD-RW - 8X 1,526
PRODUCT_PRIMARY::ITEM::INT 8X DVD Internal - DVD-RW - 8X 1,543
Product Description Units Sold
------------------------------------- ---------------------- ----------
PRODUCT_PRIMARY::ITEM::INT CD ROM Internal 48X CD-ROM 380
PRODUCT_PRIMARY::ITEM::INT CD USB Internal 48X CD-ROM USB 162
PRODUCT_PRIMARY::ITEM::INT RW DVD Internal - DVD-RW - 6X 2,887
PRODUCT_PRIMARY::FAMILY::DTPC Desktop PCs 2,982
PRODUCT_PRIMARY::ITEM::SENT FIN Sentinel Financial 1,015
PRODUCT_PRIMARY::ITEM::SENT MM Sentinel Multimedia 875
