Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-02 |
|
|
View PDF |
This chapter describes the Oracle OLAP API Cursor
class and its related classes, which you use to retrieve the results of a query. This chapter also describes the Cursor
concepts of position, fetch size, and extent. For examples of creating and using a Cursor
and its related objects, see Chapter 9, "Retrieving Query Results".
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.
A Cursor
retrieves the result set defined by a Source
. You can also get the SQL generated for a Source
by the Oracle OLAP SQL generator without having to create a Cursor
.
To get the SQL for the Source
, you create an ExpressSQLCursorManager
by using the createSQLCursorManager
method of a DataProvider
. You can then use classes outside of the OLAP API to retrieve data using the generated SQL.
The Oracle OLAP API has two paths to the creation of a Cursor
for a Source
. The older method requires creating a CursorManagerSpecification
, then creating a CursorManager
, and then creating a Cursor
. The newer method eliminates the CursorManagerSpecification
. Instead, you simply create a CursorManager
for the Source
and then create a Cursor
.
In the older method, after creating a Source
that defines the data that you want to retrieve from the data store, you create a Cursor
for that Source
by doing the following:
Creating a CursorManagerSpecification
by passing the Source
to the createCursorManagerSpecification
method of the DataProvider
that you are using. The CursorManagerSpecification
has CursorSpecification
objects in a structure that mirrors the structure of the Source
.
Creating a CursorManager
by calling the createCursorManager
method of the DataProvider
and passing it the CursorManagerSpecification
. The CursorManager
creates Cursor
objects. It also manages the local data cache for the Cursor
objects and is aware of changes to the Source
for a dynamic query or a parameterized Source
. If the Source
for the CursorManagerSpecification
has inputs, then you must also pass to the createCursorManager
method an array of Source
objects for those inputs.
Creating a Cursor
by calling the createCursor
method of the CursorManager
. The structure of the Cursor
mirrors the structures of the CursorManagerSpecification
and the Source
. The CursorSpecification
objects of a CursorManagerSpecification
specify the behavior of their corresponding Cursor
objects. If the Source
for the CursorManagerSpecification
has inputs, then you must also pass to the createCursor
method an array of CursorInput
objects that specify values for the input Source
objects.
For an example of creating a Cursor
using this method, see Chapter 9.
This architecture provides great flexibility in fetching data from a result set and in selecting data to display. You can do the following:
Create more than one CursorManagerSpecification
object for the same Source
. You can specify different behavior on the CursorSpecification
components of the various CursorManagerSpecification
objects in order to retrieve and display different sets of values from the same result set. You might want to do this when displaying the data from a Source
in different formats, such as in a table and a crosstab.
Receive notification that the Source
produced by the Template
has changed. If you add a CursorManagerUpdateListener
to the CursorManager
for a Source
, then the CursorManager
notifies the CursorManagerUpdateListener
when the Source
for a dynamic query has changed and you that therefore need to update the CursorManagerSpecification
for the CursorManager
.
Update the CursorManagerSpecification
for a CursorManager
. If you are using Template
objects to produce a dynamic query and the state of a Template
changes, then the Source
produced by the Template
changes. If you have created a Cursor
for the Source
produced by the Template
, then you need to replace the CursorManagerSpecification
for the CursorManager
with an updated CursorManagerSpecification
for the changed Source
. You can then create a new Cursor
from the CursorManager
.
Create different Cursor
objects from the same CursorManager
and set different fetch sizes on those Cursor
objects. You might do this when you want to display the same data as a table and as a graph.
This older method of creating a CursorManager
returns an ExpressSpecifiedCursorManager
.
In the newer method, you create a Cursor
for a Source
by doing the following:
Creating a CursorManager
by calling one of the createCursorManager
methods of the DataProvider
and passing it the Source
. If you want to alter the behavior of the Cursor
, then you can create a CursorInfoSpecification
and use the methods of it to specify the behavior. You then create a CursorManager
with a method that takes the Source
and the CursorInfoSpecification
.
Creating a Cursor
by calling the createCursor
method of the CursorManager
.
This newer method of creating a CursorManager
returns an ExpressDataCursorManager
.
Some Source
objects do not specify data that a Cursor
can retrieve from the data store. The following are Source
objects for which you cannot create a Cursor
that contains values.
A Source
that specifies an operation that is not computationally possible. An example is a Source
that specifies an infinite recursion.
A Source
that defines an infinite result set. An example is the fundamental Source
that represents the set of all String
objects.
A Source
that has no elements or includes another Source
that has no elements. Examples are a Source
returned by the getEmptySource
method of DataProvider
and another Source
derived from the empty Source
. Another example is a derived Source
that results from selecting a value from a primary Source
that you got from an MdmDimension
and the selected value does not exist in the dimension.
If you create a Cursor
for such a Source
and try to get the values of the Cursor
, then an Exception
occurs.
When you create a derived Source
or change the state of a Template
, you create the Source
in the context of the current Transaction
. The Source
is active in the Transaction
in which you create it or in a child Transaction
of that Transaction
. A Source
must be active in the current Transaction
for you to be able to create a Cursor
for it.
Creating a derived Source
occurs in a write Transaction
. Creating a Cursor
occurs in a read Transaction
. After creating a derived Source
, and before you can create a Cursor
for that Source
, you must change the write Transaction
into a read Transaction
by calling the prepareCurrentTransaction
and commitCurrentTransaction
methods of the TransactionProvider
your application is using. For information on Transaction
and TransactionProvider
objects, see Chapter 7, "Using a TransactionProvider".
For a Cursor
that you create for a query that includes a parameterized Source
, you can change the value of the Parameter
object and then get the new values of the Cursor
without having to prepare and commit the Transaction
again. For information on parameterized Source
objects, see Chapter 5, "Understanding Source Objects".
In the oracle.olapi.data.cursor
package, the Oracle OLAP API defines the interfaces described in the following table.
Interface | Description |
---|---|
Cursor |
An abstract superclass that encapsulates the notion of a current position. |
ValueCursor |
A Cursor that has a value at the current position. A ValueCursor has no child Cursor objects. |
CompoundCursor |
A Cursor that has child Cursor objects, which are a child ValueCursor for the values of the Source associated with it and an output child Cursor for each output of the Source . |
The structure of a Cursor
mirrors the structure of the Source
associated with it. If the Source
does not have any outputs, then the Cursor
for that Source
is a ValueCursor
. If the Source
has one or more outputs, then the Cursor
for that Source
is a CompoundCursor
. A CompoundCursor
has as children a base ValueCursor
, which has the values of the base of the Source
of the CompoundCursor
, and one or more output Cursor
objects.
The output of a Source
is another Source
. An output Source
can itself have outputs. The child Cursor
for an output of a Source
is a ValueCursor
if the output Source
does not have any outputs and a CompoundCursor
if it does.
Example 8-1 creates a query that specifies the prices of selected product items for selected months. In the example, timeHier
is a Source
for a hierarchy of a dimension of time values, and prodHier
is a Source
for a hierarchy of a dimension of product values.
If you create a Cursor
for prodSel
or for timeSel
, then either Cursor
is a ValueCursor
because both prodSel
and timeSel
have no outputs.
The unitPrice
object is a Source
for an MdmMeasure
that represents values for the price of product units. The MdmMeasure
has as inputs the MdmPrimaryDimension
objects representing products and times, and the unitPrice
Source
has as inputs the Source
objects for those dimensions.
The example selects elements of the dimension hierarchies and then joins the Source
objects for the selections to that of the measure to produce querySource
, which has prodSel
and timeSel
as outputs.
Example 8-1 Creating the querySource Query
Source timeSel = timeHier.selectValues(new String[] {"CALENDAR_YEAR_AW::MONTH_AW::55", "CALENDAR_YEAR_AW::MONTH_AW::58", "CALENDAR_YEAR_AW::MONTH_AW::61", "CALENDAR_YEAR_AW::MONTH_AW::64"}); 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 querySource = unitPrice.join(timeSel).join(prodSel);
The result set defined by querySource
is the unit price values for the selected products for the selected months. The results are organized by the outputs. Since timeSel
is joined to the Source
produced by the unitPrice.join(prodSel)
operation, timeSel
is the slower varying output, which means that the result set specifies the set of selected products for each selected time value. For each time value the result set has three product values so the product values vary faster than the time values. The values of the base ValueCursor
of querySource
are the fastest varying of all, because there is one price value for each product for each day.
Example 9-1 in Chapter 9, creates a Cursor
, queryCursor
, for querySource
. Since querySource
has outputs, queryCursor
is a CompoundCursor
. The base ValueCursor
of queryCursor
has values from unitPrice
, which is the base Source
of the operation that created querySource
. The values from unitPrice
are those specified by the outputs.The outputs for queryCursor
are a ValueCursor
that has values from prodSel
and a ValueCursor
that has values from timeSel
.
Figure 8-1 illustrates the structure of queryCursor
. The base ValueCursor
and the two output ValueCursor
objects are the children of queryCursor
, which is the parent CompoundCursor
.
Figure 8-1 Structure of the queryCursor CompoundCursor
The following table displays the values from queryCursor
in a table. The left column has time values, the middle column has product values, and the right column has the unit price of the product for the month.
Month | Product | Price of Unit |
---|---|---|
55 | 13 | 2426.07 |
55 | 14 | 3223.28 |
55 | 15 | 3042.22 |
58 | 13 | 2412.42 |
58 | 14 | 3107.65 |
58 | 15 | 3026.12 |
61 | 13 | 2505.57 |
61 | 14 | 3155.91 |
61 | 15 | 2892.18 |
64 | 13 | 2337.30 |
64 | 14 | 3105.53 |
64 | 15 | 2856.86 |
For examples of getting the values from a ValueCursor
, see Chapter 9.
CursorSpecification
objects specify some aspects of the behavior of their corresponding Cursor
objects. You must specify the behavior on a CursorSpecification
before creating the corresponding Cursor
. To specify the behavior, use the following CursorSpecification
methods:
setDefaultFetchSize
setExtentCalculationSpecified
setParentEndCalculationSpecified
setParentStartCalculationSpecified
specifyDefaultFetchSizeOnChildren
(for a CompoundCursorSpecification
only)
A CursorSpecification
also has methods that you can use to discover if the behavior is specified. Those methods are the following:
isExtentCalculationSpecified
isParentEndCalculationSpecified
isParentStartCalculationSpecified
If you have used the CursorSpecification
methods to set the default fetch size, or to calculate the extent or the starting or ending positions of a value in the parent of the value, then you can successfully use the following Cursor
methods:
getExtent
getFetchSize
getParentEnd
getParentStart
setFetchSize
For examples of specifying Cursor
behavior, see Chapter 9. For information on fetch sizes, see "About Fetch Sizes". For information on the extent of a Cursor
, see "What is the Extent of a Cursor?". For information on the starting and ending positions in a parent Cursor
of the current value of a Cursor
, see "About the Parent Starting and Ending Positions in a Cursor".
A CursorManagerSpecification
for a Source
has one or more CursorSpecification
objects. The structure of those objects reflects the structure of the Source
. For example, a Source
that has outputs has a top-level, or root, CursorSpecification
for the Source
, a child CursorSpecification
for the values of the Source
, and a child CursorSpecification
for each output of the Source
.
A Source
that does not have any outputs has only one set of values. A CursorManagerSpecification
for that Source
therefore has only one CursorSpecification
. That CursorSpecification
is the root CursorSpecification
of the CursorManagerSpecification
.
You can create a CursorManagerSpecification
for a multidimensional Source
that has one or more inputs. If you do so, then you need to supply a Source
for each input when you create a CursorManager
for the CursorManagerSpecification
. You must also supply a CursorInput
for each input Source
when you create a Cursor
from the CursorManager
. You might create a CursorManagerSpecification
for a Source
with inputs if you want to use a CursorManager
to create a series of Cursor
objects with each Cursor
retrieving data specified by a different set of single values for the input Source
objects.
The structure of a Cursor
reflects the structure of the CursorManagerSpecification
. A Cursor
can be a single ValueCursor
, for a Source
with no outputs, or a CompoundCursor
with child Cursor
objects, for a Source
with outputs. Each Cursor
corresponds to a CursorSpecification
in the CursorManagerSpecification
. You use CursorSpecification
methods to specify aspects of the behavior of the corresponding Cursor
.
If your application uses Template
objects, and a change occurs in the state of a Template
so that the structure of the Source
produced by the Template
changes, then any CursorManagerSpecification
objects that the application created for the Source
expire. If a CursorManagerSpecification
expires, then you must create a new CursorManagerSpecification
. You can then either use the new CursorManagerSpecification
to replace the old CursorManagerSpecification
of a CursorManager
or use it to create a new CursorManager
. You can discover whether a CursorManagerSpecification
has expired by calling the isExpired
method of the CursorManagerSpecification
.
The CursorInfoSpecification
interface and the subinterfaces CompoundCursorInfoSpecification
and ValueCursorInfoSpecification
, specify methods for the abstract CursorSpecification
class and the concrete CompoundCursorSpecification
and ValueCursorSpecification
classes. A CursorSpecification
specifies certain aspects of the behavior of the Cursor
that corresponds to it. You can create instances of classes that implement the CursorInfoSpecification
interface either directly or indirectly.
You can create a CursorInfoSpecification
for a Source
directly by calling the createCursorInfoSpecification
method of a DataProvider
. You can use the methods of the CursorInfoSpecification
to specify aspects of the behavior of a Cursor
. You can then use the CursorInfoSpecification
in creating a CursorManager
by passing it as the cursorInfoSpec
argument to the createCursorManager
method of a DataProvider
.
You can create a CursorInfoSpecification
for a Source
indirectly by creating a CursorManagerSpecification
. You pass a Source
to the createCursorManagerSpecification
method of a DataProvider
and the CursorManagerSpecification
returned has a root CursorSpecification
for that Source
. If the Source
has outputs, then the CursorManagerSpecification
also has a child CursorSpecification
for the values of the Source
and one for each output of the Source
.
With CursorSpecification
methods, you can do the following:
Get the Source
that corresponds to the CursorSpecification
.
Get or set the default fetch size for the corresponding Cursor
.
On a CompoundCursorSpecification
, specify that the default fetch size is set on the children of the corresponding Cursor
.
Specify that Oracle OLAP should calculate the extent of a Cursor
.
Determine whether calculating the extent is specified.
Specify that Oracle OLAP should calculate the starting or ending position of the current value of the corresponding Cursor
in the parent Cursor
. If you know the starting and ending positions of a value in the parent, then you can determine how many faster varying elements the parent Cursor
has for that value.
Determine whether calculating the starting or ending position of the current value of the corresponding Cursor
in the parent is specified.
Accept a CursorSpecificationVisitor
.
For more information, see "About Cursor Positions and Extent" and "About Fetch Sizes".
In the oracle.olapi.data.source
package, the Oracle OLAP API defines the classes described in the following table.
Interface | Description |
---|---|
CursorInfoSpecification |
An interface that specifies methods for CursorSpecification objects. |
CursorSpecification |
An abstract class that implements some methods of the CursorInfoSpecification interface. |
CompoundCursorSpecification |
A CursorSpecification for a Source that has one or more outputs. A CompoundCursorSpecification has component child CursorSpecification objects. |
CompoundInfoCursorSpecification |
An interface that specifies methods for CompoundCursorSpecification objects. |
ValueCursorSpecification |
A CursorSpecification for a Source that has values and no outputs. |
ValueCursorInfoSpecification |
An interface for ValueCursorSpecification objects. |
A Cursor
has the same structure as the CursorManagerSpecification
. For every ValueCursorSpecification
or CompoundCursorSpecification
of a CursorManagerSpecification
, a Cursor
has a corresponding ValueCursor
or CompoundCursor
. To be able to get certain information or behavior from a Cursor
, your application must specify that it wants that information or behavior by calling methods of the corresponding CursorSpecification
before it creates the Cursor
.
The OLAP API has the following concrete classes for creating a Cursor
for a Source
or for getting the SQL generated by a Source
.
ExpressDataCursorManager
ExpressSpecifiedCursorManager
ExpressSQLCursorManager
An ExpressSQLCursorManager
has methods that return the SQL generated by the Oracle OLAP SQL generator for the Source
. You create one or more ExpressSQLCursorManager
objects by calling the createSQLCursorManager
or createSQLCursorManagers
methods of a DataProvider
. You do not use an ExpressSQLCursorManager
to create a Cursor
to retrieve the result set of the query specified by the Source
. Instead, you use the SQL returned by the ExpressSQLCursorManager
with classes outside of the OLAP API to retrieve the data specified by the query.
An ExpressDataCursorManager
or ExpressSpecifiedCursorManager
returned by one of the createCursorManager
methods of a DataProvider
manages the buffering of data for the Cursor
objects it creates.
You can create more than one Cursor
from the same cursor manager, which is useful for displaying data from a result set in different formats such as a table or a graph. All of the Cursor
objects created by a cursor manager have the same specifications, such as the default fetch sizes. Because the Cursor
objects have the same specifications, they can share the data managed by the cursor manager.
An ExpressSpecifiedCursorManager
implements the SpecifiedCursorManager
interface, which extends the CursorManager
interface. A CursorManager
has methods for creating a Cursor
, for discovering whether the CursorManagerSpecification
for the CursorManager
needs updating, and for adding or removing a CursorManagerUpdateListener
. The SpecifiedCursorManager
interface adds methods for updating the CursorManagerSpecification
, for discovering whether the SpecifiedCursorManager
is open, and for closing it. Some of the createCursorManager
methods of DataProvider
return an ExpressSpecifiedCursorManager
, which is an implementation of the SpecifiedCursorManager
interface.
When your application no longer needs a SpecifiedCursorManager
, it should close it to free resources in the application and in Oracle OLAP. To close the SpecifiedCursorManager
, call the close
method of it.
If your application is using OLAP API Template
objects and the state of a Template
changes in a way that alters the structure of the Source
produced by the Template
, then any CursorManagerSpecification
objects for the Source
are no longer valid. You need to create new CursorManagerSpecification
objects for the changed Source
.
After creating a new CursorManagerSpecification
, you can create a new CursorManager
for the Source
. You do not, however, need to create a new CursorManager
. You can call the updateSpecification
method of the existing CursorManager
to replace the previous CursorManagerSpecification
with the new CursorManagerSpecification
. You can then create a new Cursor
from the CursorManager
.
To determine whether the CursorManagerSpecification
for a CursorManager
needs updating, call the isSpecificationUpdateNeeded
method of the CursorManager
. You can also use a CursorManagerUpdateListener
to listen for events generated by changes in a Source
. For more information, see "CursorManagerUpdateListener Class" .
This topic describes CursorInput
, CursorManagerUpdateListener
, and CursorManagerUpdateEvent
classes in the oracle.olapi.data.cursor
package.
For Oracle OLAP in Oracle Database 10g, the OLAP API includes Parameter
classes, which are more convenient than CursorInput
objects. With a Parameter
, you can create a parameterized Source
. You can create a CursorManagerSpecification
for a query that includes a parameterized Source
, and then create a CursorManager
and a Cursor
.
You can then change the value of the Parameter
, which changes the selection of dimension or measure elements specified by the parameterized Source
. The Cursor
for the query then has the new set of values for the changed query. You do not need to prepare and commit the Transaction
again before getting the values of the Cursor
. For information on parameterized Source
objects, see Chapter 5, "Understanding Source Objects".
A CursorInput
provides a value for a Source
that you include in the array of Source
objects that is the inputSources
argument to the createCursorManager
method of a DataProvider
. If you create a CursorManagerSpecification
for a Source
that has one or more inputs, then you must provide an inputSources
argument when you create a CursorManager
for that CursorManagerSpecification
. You include a Source
in the inputSources
array for each input of the Source
that you pass to the createCursorManagerSpecification
method.
When you create a CursorInput
object, you can specify either a single value or a ValueCursor
. If you specify a ValueCursor
, then you can call the synchronize
method of the CursorInput
to make the value of the CursorInput
be the current value of the ValueCursor
.
CursorManagerUpdateListener
is an interface that has methods that receive CursorManagerUpdateEvent
object. Oracle OLAP generates a CursorManagerUpdateEvent
object in response to a change that occurs in a Source
that is produced by a Template
or when a CursorManager
updates a CursorManagerSpecification
. Your application can use a CursorManagerUpdateListener
to listen for events that indicate it might need to create new Cursor
objects from the CursorManager
or to update the display of data from a Cursor
.
To use a CursorManagerUpdateListener
, implement the interface, create an instance of the class, and then add the CursorManagerUpdateListener
to the CursorManager
for a Source
. When a change to the Source
occurs, the CursorManager
calls the appropriate method of the CursorManagerUpdateListener
and passes it a CursorManagerUpdateEvent
. Your application can then perform the tasks needed to generate new Cursor
objects and update the display of values from the result set that the Source
defines.
You can implement more than one version of the CursorManagerUpdateListener
interface. You can add instances of them to the same CursorManager
.
Oracle OLAP generates a CursorManagerUpdateEvent
object in response to a change that occurs in a Source
that is produced by a Template
or when a CursorManager
updates a CursorManagerSpecification
.
You do not directly create instances of this class. Oracle OLAP generates CursorManagerUpdateEvent
objects and passes them to the appropriate methods of any CursorManagerUpdateListener
objects you have added to a CursorManager
. The CursorManagerUpdateEvent
has a field that indicates the type of event that occurred. A CursorManagerUpdateEvent
has methods you can use to get information about it.
A Cursor
has one or more positions. The current position of a Cursor
is the position that is currently active in the Cursor
. To move the current position of a Cursor
call the setPosition
or next
methods of the Cursor
.
Oracle OLAP does not validate the position that you set on the Cursor
until you attempt an operation on the Cursor
, such as calling the getCurrentValue
method. If you set the current position to a negative value or to a value that is greater than the number of positions in the Cursor
and then attempt a Cursor
operation, then the Cursor
throws a PositionOutOfBoundsException
.
The extent of a Cursor
is described in "What is the Extent of a Cursor?" .
The current position of a ValueCursor
specifies a value, which you can retrieve. For example, prodSel
, a derived Source
described in "Structure of a Cursor" , is a selection of three products from a primary Source
that specifies a dimension of products and their hierarchical groupings. The ValueCursor
for prodSel
has three elements. The following example gets the position of each element of the ValueCursor
, and displays the value at that position. The context
object has a method that displays text.
// prodSelValCursor is the ValueCursor for prodSel println("ValueCursor Position Value "); println("-------------------- ------------------------ "); do { println(" " + prodSelValCursor.getPosition() + " " + prodSelValCursor.getCurrentValue()); } while(prodSelValCursor.next());
The preceding example displays the following:
ValueCursor Position Value -------------------- ------------------------ 1 PRODUCT_PRIMARY_AW::ITEM_AW::13 2 PRODUCT_PRIMARY_AW::ITEM_AW::14 3 PRODUCT_PRIMARY_AW::ITEM_AW::15
The following example sets the current position of prodSelValCursor
to 2 and retrieves the value at that position.
prodSelValCursor.setPosition(2); println(prodSelValCursor.getCurrentString());
The preceding example displays the following:
PRODUCT_PRIMARY_AW::ITEM_AW::14
For more examples of getting the current value of a ValueCursor
, see Chapter 9.
A CompoundCursor
has one position for each set of the elements of the descendent ValueCursor
objects. The current position of the CompoundCursor
specifies one of those sets.
For example, querySource
, the Source
created in Example 8-1, has values from a measure, unitPrice
. The values are the prices of product units at different times. The outputs of querySource
are Source
objects that represent selections of four month values from a time dimension and three product values from a product dimension.
The result set for querySource
has one measure value for each tuple (each set of output values), so the total number of values is twelve (one value for each of the three products for each of the four months). Therefore, the queryCursor
CompoundCursor
created for querySource
has twelve positions.
Each position of queryCursor
specifies one set of positions of the outputs and the base ValueCursor
. For example, position 1 of queryCursor
defines the following set of positions for the outputs and the base ValueCursor
:
Position 1 of output 1 (the ValueCursor
for timeSel
)
Position 1 of output 2 (the ValueCursor
for prodSel
)
Position 1 of the base ValueCursor
for queryCursor
(This position has the value from the unitPrice
measure that is specified by the values of the outputs.)
Figure 8-2 illustrates the positions of queryCursor
CompoundCursor
, the base ValueCursor
, and the outputs.
Figure 8-2 Cursor Positions in queryCursor
The ValueCursor
for queryCursor
has only one position because only one value of unitPrice
is specified by any one set of values of the outputs. For a query such as querySource
, the ValueCursor
of the Cursor
has only one value, and therefore only one position, at a time for any one position of the root CompoundCursor
.
Figure 8-3 illustrates one possible display of the data from queryCursor
. It is a crosstab view with four columns and five rows. In the left column are the month values. In the top row are the product values. In each of the intersecting cells of the crosstab is the price of the product for the month.
Figure 8-3 Crosstab Display of queryCursor
A CompoundCursor
coordinates the positions of the ValueCursor
objects relative to each other. The current position of the CompoundCursor
specifies the current positions of the descendent ValueCursor
objects. Example 8-2 sets the position of queryCursor
and then gets the current values and the positions of the child Cursor
objects.
Example 8-2 Setting the CompoundCursor Position and Getting the Current Values
CompoundCursor rootCursor = (CompoundCursor) queryCursor; ValueCursor baseValueCursor = rootCursor.getValueCursor(); List outputs = rootCursor.getOutputs(); ValueCursor output1 = (ValueCursor) outputs.get(0); ValueCursor output2 = (ValueCursor) outputs.get(1); int pos = 5; rootCursor.setPosition(pos); println("CompoundCursor position set to " + pos + "."); println("The current position of the CompoundCursor is = " + rootCursor.getPosition() + "."); println("Output 1 position = " + output1.getPosition() + ", value = " + output1.getCurrentValue()); println("Output 2 position = " + output2.getPosition() + ", value = " + output2.getCurrentValue()); println("VC position = " + baseValueCursor.getPosition() + ", value = " + baseValueCursor.getCurrentValue());
Example 8-2 displays the following:
CompoundCursor position set to 5. The current position of the CompoundCursor is 5. Output 1 position = 2, value = CALENDAR_YEAR_AW::MONTH_AW::58 Output 2 position = 2, value = PRODUCT_PRIMARY_AW::ITEM_AW::14 VC position = 1, value = 3107.65
The positions of queryCursor
are symmetric in that the result set for querySource
always has three product values for each time value. The ValueCursor
for prodSel
, therefore, always has three positions for each value of the timeSel
ValueCursor
. The timeSel
output ValueCursor
is slower varying than the prodSel
ValueCursor
.
In an asymmetric case, however, the number of positions in a ValueCursor
is not always the same relative to the slower varying output. For example, if the price of units for product 15 for month 64 were null because that product was no longer being sold by that date, and if null values were suppressed in the query, then queryCursor
would only have eleven positions. The ValueCursor
for prodSel
would only have two positions when the position of the ValueCursor
for timeSel
was 4.
Example 8-3 demonstrates an asymmetric result set that is produced by selecting elements of one dimension based on a comparison of measure values. The example uses the same product and time selections as in Example 8-1. It uses a Source
for a measure of product units sold, units
, that is dimensioned by product, time, sales channels, and customer dimensions. The chanSel
and custSel
objects are selections of single values of the dimensions. The example produces a Source
, querySource2
, that specifies which of the selected products sold more than one unit for the selected time, channel, and customer values.
Because querySource2
is a derived Source
, this example prepares and commits the current Transaction
. The TransactionProvider
in the example is tp
. For information on Transaction
objects, see Chapter 7.
The example creates a Cursor
for querySource2
, loops through the positions of the CompoundCursor
, gets the position and current value of the first output ValueCursor
and the ValueCursor
of the CompoundCursor
, and displays the positions and values of the ValueCursor
objects. The getLocalValue
method is a method in the program that extracts the local value from a unique value.
Example 8-3 Positions in an Asymmetric Query
// Create the query querySource2 = prodSel.join(unitPrice).join(timeSel); // Prepare and commit the current Transaction. try { tp.prepareCurrentTransaction(); } catch(NotCommittableException e) { output.println("Cannot commit current Transaction " + e); } tp.commitCurrentTransaction(); // Create the Cursor. The DataProvider is dp. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(querySource2); CursorManager cursorManager = dp.createCursorManager(cursorMngrSpec); Cursor queryCursor2 = cursorManager.createCursor(); CompoundCursor rootCursor = (CompoundCursor) queryCursor2; ValueCursor baseValueCursor = rootCursor.getValueCursor(); List outputs = rootCursor.getOutputs(); ValueCursor output1 = (ValueCursor) outputs.get(0); // Get the positions and values and display them. println("CompoundCursor Output ValueCursor" + " ValueCursor"); println(" position position | value " + "position | value"); do { println(sp6 + rootCursor.getPosition() + // sp6 is 6 spaces sp13 + output1.getPosition() + // sp12 is 13 spaces sp7 + getLocalValue(output1.getCurrentString()) + //sp7 is 7 spaces sp7 + baseValueCursor.getPosition() + sp7 + getLocalValue(baseValueCursor.getCurrentString())); } while(queryCursor2.next());
Example 8-3 displays the following:
CompoundCursor Output ValueCursor ValueCursor position position | value position | value 1 1 55 1 13 2 1 55 2 14 3 1 55 3 15 4 2 58 1 15 5 3 61 1 14 6 3 61 2 15 7 4 64 1 13 8 4 64 2 14
Because not every combination of product and time selections has unit sales greater than 1 for the specified channel and customer selections, the number of elements of the ValueCursor
for the values derived from prodSel
is not the same for each value of the output ValueCursor
. For time value 55, all three products have sales greater than one, but for time value 58, only one of the products does. The other two time values, 61 and 64, have two products that meet the criteria. Therefore, the ValueCursor
for the CompoundCursor
has three positions for time 55, only one position for time 58, and two positions for times 61 and 64.
To effectively manage the display of the data that you get from a CompoundCursor
, you sometimes need to know how many faster varying values exist for the current slower varying value. For example, suppose that you are displaying in a crosstab one row of values from an edge of a cube, then you might want to know how many columns to draw in the display for the row.
To determine how many faster varying values exist for the current value of a child Cursor
, you find the starting and ending positions of that current value in the parent Cursor
. Subtract the starting position from the ending position and then add 1, as in the following.
long span = (cursor.getParentEnd() - cursor.getParentStart()) + 1;
The result is the span of the current value of the child Cursor
in the parent Cursor
, which tells you how many values of the fastest varying child Cursor
exist for the current value. Calculating the starting and ending positions is costly in time and computing resources, so you should only specify that you want those calculations performed when your application needs the information.
An Oracle OLAP API Cursor
enables your application to have only the data that it is currently displaying actually present on the client computer. For information on specifying the amount of data for a Cursor
, see "About Fetch Sizes".
From the data on the client computer, however, you cannot determine at what position of the parent Cursor
the current value of a child Cursor
begins or ends. To get that information, you use the getParentStart
and getParentEnd
methods of a Cursor
.
For example, suppose your application has a Source
named cube
that represents a cube that has an asymmetric edge. The cube has four outputs. The cube
Source
defines products with unit sales greater than one purchased by a certain customers during three months of the year 2001. The products were sold through the direct sales channel.
You create a Cursor
for that Source
and call it cubeCursor
. The CompoundCursor
cubeCursor
has the following child Cursor
objects:
output 1, a ValueCursor
for the channel values
output 2, a ValueCursor
for the time values
output 4, a ValueCursor
for the customer values
The base ValueCursor
, which has values that are the products with unit sales greater than one.
Figure 8-4 illustrates the parent, cubeCursor
, with the values of the child Cursor
objects layered horizontally. The slowest varying output, with the channel value, is at the top and the fastest varying child, with the product values, is at the bottom. The only portion of the edge that you are currently displaying in the user interface is the block between positions 9 and 12 of cubeCursor
, which is shown within the bold border. The positions, 1 through 15, of cubeCursor
appear over the top row.
Figure 8-4 Values of the ValueCursor Children of cubeCursor
The current value of the output ValueCursor
for the time Source
is 44. You cannot determine from the data within the block that the starting and ending positions of the current value, 44, in the parent, cubeCursor
, are 5 and 9, respectively.
The cubeCursor
from the previous figure is shown again in Figure 8-5, this time with the range of the positions of the parent, cubeCursor
, for each of the values of the child Cursor
objects. By subtracting the smaller value from the larger value and adding one, you can compute the span of each value. For example, the span of the time value 44 is (9 - 5 + 1) = 5.
Figure 8-5 The Range of Positions of the Child Cursor Objects of cubeCursor
To specify that you want Oracle OLAP to calculate the starting and ending positions of a value of a child Cursor
in the parent Cursor
, call the setParentStartCalculationSpecified
and setParentEndCalculationSpecified
methods of the CursorSpecification
corresponding to the Cursor
. You can determine whether calculating the starting or ending positions is specified by calling the isParentStartCalculationSpecified
or isParentEndCalculationSpecified
methods of the CursorSpecification
. For an example of specifying these calculations, see Chapter 9.
The extent of a Cursor
is the total number of elements it contains relative to any slower varying outputs. Figure 8-6 illustrates the number of positions of each child Cursor
of cubeCursor
relative to the value of the slower varying output. The child Cursor
objects are layered horizontally with the slowest varying output at the top.
The total number of elements in cubeCursor
is fifteen so the extent of cubeCursor
is therefore fifteen. That number is over the top row of the figure. The top row is the ValueCursor
for the channel value. The extent of the ValueCursor
for channel values is one because it has only one value.
The second row down is the ValueCursor
for the time values. The extent is 3, since there are 3 months values. The next row down is the ValueCursor
for the customer values. The extent of the elements depends on the value of the slower varying output, which is time. The extent of the customers ValueCursor
for the first month is two, for the second month it is two, and for the third month it is three.
The bottom row is the base ValueCursor
for the cubeCursor
CompoundCursor
. The values base ValueCursor
are products. The extent of the elements of the products ValueCursor
depends on the values of the customers ValueCursor
and the time ValueCursor
. For example, since three products values are specified by the first set of month and customer values (products 13, 14, and 15 for customer 58 for time 43), the extent of the products ValueCursor
for that set is 3. For the second set of values for customers and times (customer 61 for time 43), the extent of the products ValueCursor
is 1, and so on.
Figure 8-6 The Number of Elements of the Child Cursor Objects of cubeCursor
The extent is information that you can use, for example, to display the correct number of columns or correctly-sized scroll bars. The extent, however, can be expensive to calculate. For example, a Source
that represents a cube might have four outputs. Each output might have hundreds of values. If all null values and zero values of the measure for the sets of outputs are eliminated from the result set, then to calculate the extent of the CompoundCursor
for the Source
, Oracle OLAP must traverse the entire result space before it creates the CompoundCursor
. If you do not specify that you wants the extent calculated, then Oracle OLAP only needs to traverse the sets of elements defined by the outputs of the cube as specified by the fetch size of the Cursor
and as needed by your application.
To specify that you want Oracle OLAP to calculate the extent for a Cursor
, call the setExtentCalculationSpecified
method of the CursorSpecification
corresponding to the Cursor
. You can determine whether calculating the extent is specified by calling the isExtentCalculationSpecified
method of the CursorSpecification
. For an example of specifying the calculation of the extent of a Cursor
, see Chapter 9.
An OLAP API Cursor
represents the entire result set for a Source
. The Cursor
is a virtual Cursor
, however, because it retrieves only a portion of the result set at a time from Oracle OLAP. A CursorManager
manages a virtual Cursor
and retrieves results from Oracle OLAP as your application needs it. By managing the virtual Cursor
, the CursorManager
relieves your application of a substantial burden.
The amount of data that a Cursor
retrieves in a single fetch operation is determined by the fetch size specified for the Cursor
. You specify a fetch size to limit the amount of data your application needs to cache on the local computer and to maximize the efficiency of the fetch by customizing it to meet the needs of your method of displaying the data.
You can also regulate the number of elements that Oracle OLAP returns by using Parameter
and parameterized Source
objects in constructing your query. For more information on Parameter
objects, see Chapter 5, "Understanding Source Objects". For examples of using parameterized Source
objects, see Chapter 6, "Making Queries Using Source Methods".
When you create a CursorManagerSpecification
for a Source
, as the first step in creating a Cursor
, Oracle OLAP specifies a default fetch size on the root CursorSpecification
of the CursorManagerSpecification
. You can change the default fetch size with the setDefaultFetchSize
method of the root CursorSpecification
. You can also change the fetch size with the setFetchSize
method of the CursorManager
that you create using the CursorManagerSpecification
, or with the setFetchSize
method of a Cursor
that you create with the CursorManager
.
You can create two or more Cursor
objects from the same CursorManager
and use both Cursor
objects simultaneously. Rather than having separate data caches, the Cursor
objects can share the data managed by the CursorManager
.
An example is an application that displays the results of a query to the user as both a table and a graph. The application creates a CursorManagerSpecification
for a Source
and then creates a CursorManager
for the CursorManagerSpecification
. The application creates two separate Cursor
objects from the same CursorManager
, one for a table view and one for a graph view. The two views share the same query and display the same data, just in different formats. Figure 8-7 illustrates the relationship between the Source
, the Cursor
objects, and the views.
Figure 8-7 A Source and Two Cursors for Different Views of the Values