Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-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 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:
For information on how to get the complete code for the examples in this chapter, see the topic "Sample Schema for OLAP API Examples" in Chapter 1.
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_AW::MONTH_AW::55
, which identifies the month January, 2001, the custSel
value is SHIPMENTS_AW::SHIP_TO_AW::52
, which identifies the Business Word San Jose customer, and the prodSel
value is PRODUCT_PRIMARY_AW::ITEM_AW::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 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 Direct Sales Catalog Internet ------------ ------------ ------------ ------- -------- TotalChannel 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 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, mktSegment
is a StringSource
that represents the Market Segment hierarchy of the Customer dimension. The mktSegmentAncestors
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 mktSegmentAncestors
, the elements of mktSegment
that have the mktSegmentAncestors
element as their ancestor. The join operation matches the base Source
, mktSegment
, to the input of the ancestors attribute.
The resulting Source
, mktSegmentDescendants
, however, still has mktSegment
as an input because the Source
produced by the mktSegment.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 mktSegment
selects the element of mktSegment
that has the value MARKET_SEGMENT_AW::ACCOUNT_AW::23
, which is the Business World account, and produces selVal
. The join
method of mktSegmentDescendants
uses selVal
as the comparison Source
. The method produces selValDescendants
, which has the elements of mktSegmentDescendants
that are present in mktSegment
, and that are also in selVal
. The input of mktSegmentDescendants
is matched by the joined Source
mktSegment
. The mktSegment
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 mktSegmentDescendants = mktSegment.join(mktSegmentAncestors, mktSegment.value()); Source selVal = mktSegment.selectValue("MARKET_SEGMENT_AW::ACCOUNT_AW::23"); Source selValDescendants = mktSegmentDescendants.join(mktSegment, selVal, false); Source selValPlusDescendants = selVal.appendValues(selValDescendants) .distinct();
A Cursor
for the selValPlusDescendants
Source
has the following values:
MARKET_SEGMENT_AW::ACCOUNT_AW::23 MARKET_SEGMENT_AW::SHIP_TO_AW::51 MARKET_SEGMENT_AW::SHIP_TO_AW::52 MARKET_SEGMENT_AW::SHIP_TO_AW::53 MARKET_SEGMENT_AW::SHIP_TO_AW::54
If the example did not include the distinct
method call, then a Cursor
for selValPlusDescendants
would have the following values:
MARKET_SEGMENT_AW::ACCOUNT_AW::23 MARKET_SEGMENT_AW::ACCOUNT_AW::23 MARKET_SEGMENT_AW::SHIP_TO_AW::51 MARKET_SEGMENT_AW::SHIP_TO_AW::52 MARKET_SEGMENT_AW::SHIP_TO_AW::53 MARKET_SEGMENT_AW::SHIP_TO_AW::54
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 mktSegment
, which is the Source
for a hierarchy of the Customer dimension, and mktSegmentAncestors
, which is the Source
for the ancestors attribute for the hierarchy. It also uses mktSegmentDescendants
, 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 mktSegmentDescendants
produces mktSegmentDescendantsOnly
, which specifies the descendants of mktSegment
, and which has mktSegment
as an input because the comparison
parameter of the join operation is the Source
that results from the mktSegment.value()
method.
Because COMPARISON_RULE_REMOVE
is the comparison rule of the join operation that produced mktSegmentDescendantsOnly
, a join operation that matches a Source
to the input of mktSegmentDescendantsOnly
produces a Source
that has only those elements of mktSegmentDescendantsOnly
that are not in the comparison Source
of the join operation.
The next join operation performs such a match. It matches the joined Source
, mktSegment
, to the input of mktSegmentDescendantsOnly
to produce selValDescendantsOnly
, which specifies the descendants of the selected hierarchy value but does not include the selected value because mktSegmentDescendantsOnly
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 = mktSegment.selectValue("MARKET_SEGMENT_AW::ACCOUNT_AW::23"); Source mktSegmentDescendantsOnly = mktSegmentDescendants.join(mktSegmentDescendants.getDataType().value(), mktSegment.value(), Source.COMPARISON_RULE_REMOVE); // Select the descendants of the specified element. Source selValDescendants = mktSegmentDescendants.join(mktSegment, selVal); // Select only the descendants of the specified element. Source selValDescendantsOnly = mktSegmentDescendantsOnly.join(mktSegment, selVal);
A Cursor
for selValDescendants
has the following values.
MARKET_SEGMENT_AW::ACCOUNT_AW::23 MARKET_SEGMENT_AW::SHIP_TO_AW::51 MARKET_SEGMENT_AW::SHIP_TO_AW::52 MARKET_SEGMENT_AW::SHIP_TO_AW::53 MARKET_SEGMENT_AW::SHIP_TO_AW::54
A Cursor
for selValDescendantsOnly
has the following values.
MARKET_SEGMENT_AW::SHIP_TO_AW::51 MARKET_SEGMENT_AW::SHIP_TO_AW::52 MARKET_SEGMENT_AW::SHIP_TO_AW::53 MARKET_SEGMENT_AW::SHIP_TO_AW::54
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 ----------------- -------------- 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 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 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 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.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 member in the level, and the right column is the local value of the member.
Level Member Position in Level Member Value ----- ------------------------- ----------- YEAR 1 1 YEAR 8 145 QUARTER 1 5 QUARTER 32 144 MONTH 1 19 MONTH 96 138
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 sixteen values of the Cursor
.
Product Dimension Element Value -------------------------------- PRODUCT_PRIMARY_AW::TOTAL_PRODUCT_AW::1 PRODUCT_PRIMARY_AW::CLASS_AW::2 PRODUCT_PRIMARY_AW::FAMILY_AW::10 PRODUCT_PRIMARY_AW::ITEM_AW::25 PRODUCT_PRIMARY_AW::ITEM_AW::26 PRODUCT_PRIMARY_AW::FAMILY_AW::11 PRODUCT_PRIMARY_AW::ITEM_AW::34 PRODUCT_PRIMARY_AW::ITEM_AW::35 PRODUCT_PRIMARY_AW::ITEM_AW::36 PRODUCT_PRIMARY_AW::ITEM_AW::37 PRODUCT_PRIMARY_AW::ITEM_AW::38 PRODUCT_PRIMARY_AW::ITEM_AW::39 PRODUCT_PRIMARY_AW::FAMILY_AW::4 PRODUCT_PRIMARY_AW::ITEM_AW::13 PRODUCT_PRIMARY_AW::ITEM_AW::14 PRODUCT_PRIMARY_AW::ITEM_AW::15 ...
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 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_AW::TOTAL_PRODUCT_AW::1 NA PRODUCT_PRIMARY_AW::CLASS_AW::2 NA PRODUCT_PRIMARY_AW::FAMILY_AW::10 NA PRODUCT_PRIMARY_AW::ITEM_AW::25 NA PRODUCT_PRIMARY_AW::ITEM_AW::26 NA PRODUCT_PRIMARY_AW::FAMILY_AW::11 NA PRODUCT_PRIMARY_AW::ITEM_AW::35 NA PRODUCT_PRIMARY_AW::ITEM_AW::36 NA PRODUCT_PRIMARY_AW::ITEM_AW::38 NA PRODUCT_PRIMARY_AW::ITEM_AW::37 Executive PRODUCT_PRIMARY_AW::ITEM_AW::34 Laptop Value Pack PRODUCT_PRIMARY_AW::ITEM_AW::39 Multimedia PRODUCT_PRIMARY_AW::FAMILY_AW::4 NA PRODUCT_PRIMARY_AW::ITEM_AW::15 NA PRODUCT_PRIMARY_AW::ITEM_AW::14 Executive PRODUCT_PRIMARY_AW::ITEM_AW::13 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_AW::TOTAL_PRODUCT_AW::1 NA PRODUCT_PRIMARY_AW::CLASS_AW::2 NA PRODUCT_PRIMARY_AW::FAMILY_AW::10 NA PRODUCT_PRIMARY_AW::ITEM_AW::25 NA PRODUCT_PRIMARY_AW::ITEM_AW::26 NA PRODUCT_PRIMARY_AW::FAMILY_AW::11 NA PRODUCT_PRIMARY_AW::ITEM_AW::35 NA PRODUCT_PRIMARY_AW::ITEM_AW::36 NA PRODUCT_PRIMARY_AW::ITEM_AW::38 NA PRODUCT_PRIMARY_AW::ITEM_AW::39 Multimedia PRODUCT_PRIMARY_AW::ITEM_AW::34 Laptop Value Pack PRODUCT_PRIMARY_AW::ITEM_AW::37 Executive PRODUCT_PRIMARY_AW::FAMILY_AW::4 NA PRODUCT_PRIMARY_AW::ITEM_AW::15 NA PRODUCT_PRIMARY_AW::ITEM_AW::13 Laptop Value Pack PRODUCT_PRIMARY_AW::ITEM_AW::14 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_AW
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_AW::SHIP_TO_AW::60", "SHIPMENTS_AW::SHIP_TO_AW::61", "SHIPMENTS_AW::SHIP_TO_AW::62", "SHIPMENTS_AW::SHIP_TO_AW::63"}); Source custSel2 = custSel.selectValues(new String[] {"SHIPMENTS_AW::SHIP_TO_AW::60", "SHIPMENTS_AW::SHIP_TO_AW::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_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::63 SHIPMENTS_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::61 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::63 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::61
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_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::62 SHIPMENTS_AW::SHIP_TO_AW::60 SHIPMENTS_AW::SHIP_TO_AW::60
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
.
Some of the Source
methods are implemented as CustomModel
objects. For example, the extract
method, which used to be a basic Source
method, now uses a CustomModel
, as shown in Example 5-11 in Chapter 5, "Understanding Source Objects". The current implementation of the extract
method produces the same result as the previous implementation.
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_AW::ITEM_AW::13", "PRODUCT_PRIMARY_AW::ITEM_AW::14", "PRODUCT_PRIMARY_AW::ITEM_AW::15"}); Source chanSel = chanHier.selectValue("CHANNEL_PRIMARY_AW::CHANNEL_AW::2"); Source timeSel = timeHier.selectValue("CALENDAR_YEAR_AW::MONTH_AW::59"); Source custSel = custHier.selectValue("SHIPMENTS_AW::TOTAL_CUSTOMER_AW::1"); 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_AW::TOTAL_CUSTOMER_AW::1 CHANNEL_PRIMARY_AW::CHANNEL_AW::2 CALENDAR_YEAR_AW::MONTH_AW::59 UNIT PRICE UNITS SOLD SALES AMOUNT ITEM ---------- ---------- ------------- ---- 13 2,395.63 39 93,429.57 14 3,147.85 37 116,470.45 15 2,993.29 26 77,825.54
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 prepares and 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 prepares and 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 Context10g
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_AW::REGION_AW::9"); StringParameter prodParam = new StringParameter(dp, "PRODUCT_PRIMARY_AW::FAMILY_AW::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_AW::YEAR_AW::2" "CALENDAR_YEAR_AW::YEAR_AW::3", "CALENDAR_YEAR_AW::YEAR_AW::4"}); Source chanSel = chanHier.selectValues(new String[] {"CHANNEL_PRIMARY_AW::CHANNEL_AW::2", "CHANNEL_PRIMARY_AW::CHANNEL_AW::3", "CHANNEL_PRIMARY_AW::CHANNEL_AW::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); // The following method prepares and commits the current Transaction. prepareAndCommit(); // 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. getContext().displayCursorAsCrosstab(cubeCursor); // Change the customer parameter value. custParam.setValue("SHIPMENTS_AW::REGION_AW::10"); // 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); // Prepare and commit the current Transaction. prepareAndCommit(); // Create another Cursor. cMngrSpec = dp.createCursorManagerSpecification(cube); spCMngr = dp.createCursorManager(cMngrSpec); cubeCursor = spCMngr.createCursor(); getContext().displayCursorAsCrosstab(cubeCursor); // Change the product parameter value. prodParam.setValue("PRODUCT_PRIMARY_AW::FAMILY_AW::5"); // 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 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 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); // Prepare and commit the current Transaction. prepareAndCommit(); // 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_AW::FAMILY_AW::10,Memory) 2: (PRODUCT_PRIMARY_AW::FAMILY_AW::11,CD/DVD) 3: (PRODUCT_PRIMARY_AW::FAMILY_AW::12,Documentation) 4: (PRODUCT_PRIMARY_AW::FAMILY_AW::4,Portable PCs) 5: (PRODUCT_PRIMARY_AW::FAMILY_AW::5,Desktop PCs) 6: (PRODUCT_PRIMARY_AW::FAMILY_AW::6,Operating Systems) 7: (PRODUCT_PRIMARY_AW::FAMILY_AW::7,Accessories) 8: (PRODUCT_PRIMARY_AW::FAMILY_AW::8,Monitors) 9: (PRODUCT_PRIMARY_AW::FAMILY_AW::9,Modems/Fax) Level element at position 5: 1: PRODUCT_PRIMARY_AW::FAMILY_AW:5 Parent of the level element: 1: (PRODUCT_PRIMARY_AW::CLASS_AW::2,Hardware) Children of the level element: 1: (PRODUCT_PRIMARY_AW::ITEM_AW::16,Sentinel Standard) 2: (PRODUCT_PRIMARY_AW::ITEM_AW::17,Sentinel Financial) 3: (PRODUCT_PRIMARY_AW::ITEM_AW::18,Sentinel Multimedia) Ancestors of the level element: 1: (PRODUCT_PRIMARY_AW::TOTAL_PRODUCT_AW::1,Total Product) 2: (PRODUCT_PRIMARY_AW::CLASS_AW::2,Hardware) 3: (PRODUCT_PRIMARY_AW::FAMILY_AW::5,Desktop 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_AW::TOTAL_CUSTOMER_AW::1
, 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_AW::CHANNEL_AW::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. The value of the element value is CALENDAR_YEAR_AW::YEAR_AW::4
, 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_AW
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_AW 1 Total Product 43,783 CLASS_AW 2 Hardware 16,541 FAMILY_AW 4 Portable PCs 1,192 ITEM_AW 15 Envoy Ambassador 330 ITEM_AW 14 Envoy Executive 385 ITEM_AW 13 Envoy Standard 477 FAMILY_AW 8 Monitors 1,193 ITEM_AW 21 Monitor- 19" Super VGA 207 ITEM_AW 20 Monitor- 15" Super VGA 986 ... CLASS_AW 3 Software/Other 27,242 ... FAMILY_AW 7 Accessories 18,949 ITEM_AW 22 Envoy External Keyboard 146 ITEM_AW 23 External 101-key keyboard 678 ITEM_AW 32 Multimedia speakers- 5" cones 717 ITEM_AW 46 Standard Mouse 868 ITEM_AW 27 Multimedia speakers- 3" cones 1,120 ITEM_AW 31 1.44MB External 3.5" Diskette 1,145 ITEM_AW 48 Keyboard Wrist Rest 2,231 ITEM_AW 19 Laptop carrying case 3,704 ITEM_AW 47 Deluxe Mouse 3,884 ITEM_AW 30 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_AW::TOTAL_PRODUCT_AW::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 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_AW::TOTAL_PRODUCT_AW::1"); NumberSource totalUnits = (NumberSource) units.joinHidden(totalProds); Source productShare = units.div(totalUnits).times(100); Source result = productShare.join(prodFamilies) .join(timeHier, "CALENDAR_YEAR_AW::YEAR_AW::4") .join(chanHier, "CHANNEL_PRIMARY_AW::CHANNEL_AW::2") .join(custHier, "SHIPMENTS_AW::TOTAL_CUSTOMER_AW::1"); 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_AW::FAMILY_AW::4 2.72% PRODUCT_PRIMARY_AW::FAMILY_AW::8 2.73% PRODUCT_PRIMARY_AW::FAMILY_AW::10 3.57% PRODUCT_PRIMARY_AW::FAMILY_AW::5 5.13% PRODUCT_PRIMARY_AW::FAMILY_AW::12 6.4% PRODUCT_PRIMARY_AW::FAMILY_AW::11 11.71% PRODUCT_PRIMARY_AW::FAMILY_AW::9 11.92% PRODUCT_PRIMARY_AW::FAMILY_AW::6 12.54% PRODUCT_PRIMARY_AW::FAMILY_AW::7 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_AW::FAMILY_AW::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 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_AW::QUARTER_AW::5 16125.24 NA CALENDAR_YEAR_AW::QUARTER_AW::6 16226.89 NA CALENDAR_YEAR_AW::QUARTER_AW::7 16039.61 NA CALENDAR_YEAR_AW::QUARTER_AW::8 15526.53 NA CALENDAR_YEAR_AW::QUARTER_AW::9 21553.14 16,125.24 CALENDAR_YEAR_AW::QUARTER_AW::10 21034.61 162,26.89 CALENDAR_YEAR_AW::QUARTER_AW::11 21135.51 16,039.61 CALENDAR_YEAR_AW::QUARTER_AW::12 19600.98 15,526.53 ...
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_AW::QUARTER_AW::5 16,125.24 63,918.27 CALENDAR_YEAR_AW::QUARTER_AW::6 16,226.89 69,346.17 CALENDAR_YEAR_AW::QUARTER_AW::7 16,039.61 74,153.89 CALENDAR_YEAR_AW::QUARTER_AW::8 15,526.53 79,249.79 CALENDAR_YEAR_AW::QUARTER_AW::9 21,553.14 83,324.24 CALENDAR_YEAR_AW::QUARTER_AW::10 21,034.61 80,206.84 CALENDAR_YEAR_AW::QUARTER_AW::11 21,135.51 77,638.28 ...
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 Context10g
object that is returned by the getContext
method. The Context10g
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 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 = dp.createParameterizedSource(startParam); NumberSource endParamSrc = dp.createParameterizedSource(endParam); Source paramProdSelInterval = prodHier.interval(startParamSrc, endParamSrc); Source paramProdSelIntervalShortDescr = prodShortDescr.join(paramProdSelInterval); NumberSource result = (NumberSource) units.join(chanHier, "CHANNEL_PRIMARY_AW::CHANNEL_AW::4") .join(calendar, "CALENDAR_YEAR_AW::YEAR_AW::4") .join(shipHier, "SHIPMENTS_AW::TOTAL_CUSTOMER_AW::1") .join(paramProdSelIntervalShortDescr); // Get the TransactionProvider and prepare and commit the current transaction. prepareAndCommit(); CursorManagerSpecification cMngrSpec = dp.createCursorManagerSpecification(results); SpecifiedCursorManager spCMngr = dp.createCursorManager(cMngrSpec); Cursor resultCursor = spCMngr.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_AW::TOTAL_PRODUCT_AW::1 Total Product 55,872 PRODUCT_PRIMARY_AW::CLASS_AW::2 Hardware 21,301 PRODUCT_PRIMARY_AW::FAMILY_AW::10 Memory 1,948 PRODUCT_PRIMARY_AW::ITEM_AW::25 512MB USB Drive 1,382 PRODUCT_PRIMARY_AW::ITEM_AW::26 1GB USB Drive 566 PRODUCT_PRIMARY_AW::ITEM_AW::11 CD/DVD 6,634 PRODUCT_PRIMARY_AW::FAMILY_AW::34 Internal 48X CD-ROM 380 PRODUCT_PRIMARY_AW::ITEM_AW::35 Internal - DVD-RW - 8X 1,543 PRODUCT_PRIMARY_AW::ITEM_AW::36 External 48X CD-ROM 136 PRODUCT_PRIMARY_AW::ITEM_AW::37 External - DVD-RW - 8X 1,526 PRODUCT_PRIMARY_AW::ITEM_AW::38 Internal 48X CD-ROM 162 PRODUCT_PRIMARY_AW::ITEM_AW::39 Internal - DVD-RW - 6X 2,887