This chapter describes how to retrieve the results of a query with an Oracle OLAP API Cursor
and how to gain access to those results. This chapter also describes how to customize the behavior of a Cursor
to fit your method of displaying the results. For information on the class hierarchies of Cursor
and its related classes, and for information on the Cursor
concepts of position, fetch size, and extent, see Chapter 8, "Understanding Cursor Classes and Concepts".
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 query is an OLAP API Source
that specifies the data that you want to retrieve from Oracle OLAP and any calculations you want Oracle OLAP to perform on that data. A Cursor
is the object that retrieves, or fetches, the result set specified by a Source
. Creating a Cursor
for a Source
involves the following steps:
Get a primary Source
from an MdmObject
or create a derived Source
through operations on a DataProvider
or a Source
. For information on getting or creating Source
objects, see Chapter 5, "Understanding Source Objects".
If the Source
is a derived Source
, prepare and commit the Transaction
in which you created the Source
. To prepare and commit the Transaction
, call the prepareCurrentTransaction
and commitCurrentTransaction
methods of your TransactionProvider
. For more information on preparing and committing a Transaction
, see Chapter 7, "Using a TransactionProvider". If the Source
is a primary Source
, then you do not need to prepare and commit the Transaction
.
Create a CursorManagerSpecification
by calling the createCursorManagerSpecification
method of your DataProvider
and passing that method the Source
.
Create a SpecifiedCursorManager
by calling the createCursorManager
method of your DataProvider
and passing that method the CursorManagerSpecification
. If the Source
for the CursorManagerSpecification
has one or more inputs, then you must also pass an array of Source
objects that provides a Source
for each input.
Create a Cursor
by calling the createCursor
method of the CursorManager
. If you created the CursorManager
with an array of input Source
objects, then you must also pass an array of CursorInput
objects that provides a value for each input Source
.
Example 9-1 creates a Cursor
for the derived Source
named querySource
. The example uses a TransactionProvider
named tp
and a DataProvider
named dp
. The example creates a CursorManagerSpecification
named cursorMngrSpec
, a SpecifiedCursorManager
named cursorMngr
, and a Cursor
named queryCursor
.
Finally, the example closes the SpecifiedCursorManager
. When you have finished using the Cursor
, you should close the SpecifiedCursorManager
to free resources.
// Prepare and commit the current Transaction. try { tp.prepareCurrentTransaction(); } catch(NotCommittableException e) { println("Cannot commit the current Transaction. " + e); } tp.commitCurrentTransaction(); CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(querySource); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); Cursor queryCursor = cursorMngr.createCursor(); // Use the Cursor in some way, such as to display its values. cursorMngr.close();
The Cursor
interface encapsulates the notion of a current position and has methods for moving the current position. The ValueCursor
and CompoundCursor
interfaces extend the Cursor
interface. The Oracle OLAP API has implementations of the ValueCursor
and CompoundCursor
interfaces. Calling the createCursor
method of a CursorManager
returns either a ValueCursor
or a CompoundCursor
implementation, depending on the Source
for which you are creating the Cursor
.
A ValueCursor
is returned for a Source
that has a single set of values. A ValueCursor
has a value at its current position, and it has methods for getting the value at the current position.
A CompoundCursor
is created for a Source
that has more than one set of values, which is a Source
that has one or more outputs. Each set of values of the Source
is represented by a child ValueCursor
of the CompoundCursor
. A CompoundCursor
has methods for getting its child Cursor
objects.
The structure of the Source
determines the structure of the Cursor
. A Source
can have nested outputs, which occurs when one or more of the outputs of the Source
is itself a Source
with outputs. If a Source
has a nested output, then the CompoundCursor
for that Source
has a child CompoundCursor
for that nested output.
The CompoundCursor
coordinates the positions of its child Cursor
objects. The current position of the CompoundCursor
specifies one set of positions of its child Cursor
objects.
For an example of a Source
that has only one level of output values, see Example 9-4. For an example of a Source
that has nested output values, see Example 9-5.
An example of a Source
that represents a single set of values is one returned by the getSource
method of an MdmDimension
, such as an MdmPrimaryDimension
that represents product values. Creating a Cursor
for that Source
returns a ValueCursor
. Calling the getCurrentValue
method returns the product value at the current position of that ValueCursor
.
Example 9-2 gets the Source
from mdmProdHier
, which is an MdmPrimaryDimension
that represents product values, and creates a Cursor
for that Source
. The example sets the current position to the fifth element of the ValueCursor
and gets the product value from the Cursor
. The example then closes the CursorManager
. In the example, dp
is the DataProvider
.
Example 9-2 Getting a Single Value from a ValueCursor
Source prodSource = mdmProdHier.getSource(); // Because prodSource is a primary Source, you do not need to // prepare and commit the current Transaction. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(productSource); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); Cursor prodCursor = cursorMngr.createCursor(); // Cast the Cursor to a ValueCursor. ValueCursor prodValues = (ValueCursor) prodCursor; // Set the position to the fifth element of the ValueCursor. prodValues.setPosition(5); // Product values are Strings. Get the String value at the current position. String value = prodValues.getCurrentString(); // Do something with the value, such as display it. // Close the SpecifiedCursorManager. cursorMngr.close();
Example 9-3 uses the same Cursor
as Example 9-2. Example 9-3 uses a do...while
loop and the next
method of the ValueCursor
to move through the positions of the ValueCursor
. The next
method begins at a valid position and returns true
when an additional position exists in the Cursor
. It also advances the current position to that next position.
The example sets the position to the first position of the ValueCursor
. The example loops through the positions and uses the getCurrentValue
method to get the value at the current position.
Example 9-3 Getting All of the Values from a ValueCursor
// prodValues is the ValueCursor for prodSource. prodValues.setPosition(1); do { println(prodValues.getCurrentValue); } while(prodValues.next());
The values of the result set represented by a CompoundCursor
are in the child ValueCursor
objects of the CompoundCursor
. To get those values, you must get the child ValueCursor
objects from the CompoundCursor
.
An example of a CompoundCursor
is one that is returned by calling the createCursor
method of a CursorManager
for a Source
that represents the values of a measure as specified by selected values from the dimensions of the measure.
Example 9-4 uses a Source
, named units
, that results from calling the getSource
method of an MdmMeasure
that represents the number of units sold. The dimensions of the measure are MdmPrimaryDimension
objects representing products, customers, times, and channels. This example uses Source
objects that represent selected values from the default hierarchies of those dimensions. The names of those Source
objects are prodSel
, custSel
, timeSel
, and chanSel
. The creation of the Source
objects representing the measure and the dimension selections is not shown.
Example 9-4 joins the dimension selections to the measure, which results in a Source
named unitsForSelections
. It creates a CompoundCursor
, named unitsForSelCursor
, for unitsForSelections
, and gets the base ValueCursor
and the outputs from the CompoundCursor
. Each output is a ValueCursor
, in this case. The outputs are returned in a List
. The order of the outputs in the List
is the inverse of the order in which the outputs were added to the list of outputs by the successive join operations. In the example, dp
is the DataProvider
and tp
is the TransactionProvider
.
Example 9-4 Getting ValueCursor Objects from a CompoundCursor
Source unitsForSelections = units.join(prodSel) .join(custSel) .join(timeSel) .join(chanSel); // Prepare and commit the current Transaction (code not shown). // Create a Cursor for unitsForSelections. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitsForSelections); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); CompoundCursor unitsForSelCursor = (CompoundCursor) cursorMngr.createCursor(); // Get the base ValueCursor. ValueCursor specifiedUnitsVals = unitsForSelCursor.getValueCursor(); // Get the outputs. List outputs = unitsForSelCursor.getOutputs(); ValueCursor chanSelVals = (ValueCursor) outputs.get(0); ValueCursor timeSelVals = (ValueCursor) outputs.get(1); ValueCursor custSelVals = (ValueCursor) outputs.get(2); ValueCursor prodSelVals = (ValueCursor) outputs.get(3); // You can now get the values from the ValueCursor objects. // When you have finished using the Cursor objects, close the // SpecifiedCursorManager. cursorMngr.close();
Example 9-5 uses the same units measure as Example 9-4, but it joins the dimension selections to the measure differently. Example 9-5 joins two of the dimension selections together. It then joins the result to the Source
that results from joining the single dimension selections to the measure. The resulting Source
, unitsForSelections
, represents a query has nested outputs, which means it has more than one level of outputs.
The CompoundCursor
that this example creates for unitsForSelections
therefore also has nested outputs. The CompoundCursor
has a child base ValueCursor
and as its outputs has three child ValueCursor
objects and one child CompoundCursor
.
Example 9-5 joins the selection of channel dimension values, chanSel
, to the selection of customer dimension values, custSel
. The result is custByChanSel
, a Source
that has customer values as its base values and channel values as the values of its output. The example joins to units
the selections of product and time values, and then joins custByChanSel
. The resulting query is represented by unitsForSelections
.
The example prepares and commits the current Transaction
and creates a CompoundCursor
, named unitsForSelCursor
, for unitsForSelections
.
The example gets the base ValueCursor
and the outputs from the CompoundCursor
. In the example, dp
is the DataProvider
and tp
is the TransactionProvider
.
Example 9-5 Getting Values from a CompoundCursor with Nested Outputs
Source custByChanSel = custSel.join(chanSel); Source unitsForSelections = units.join(prodSel) .join(timeSel) .join(custByChanSel); // Prepare and commit the current Transaction (code not shown). // Create a Cursor for unitsForSelections. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitsForSelections); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); Cursor unitsForSelCursor = cursorMngr.createCursor(); // Send the Cursor to a method that does different operations // depending on whether the Cursor is a CompoundCursor or a // ValueCursor. printCursor(unitsForSelCursor); cursorMngr.close(); // The remaining code of someMethod is not shown. // The following code is in from the CursorPrintWriter class. // The printCursor method has a do...while loop that moves through the positions // of the Cursor passed to it. At each position, the method prints the number of // the iteration through the loop and then a colon and a space. The output // object is a PrintWriter. The method calls the private _printTuple method and // then prints a new line. A "tuple" is the set of output ValueCursor values // specified by one position of the parent CompoundCursor. The method prints one // line for each position of the parent CompoundCursor. private void printCursor(Cursor rootCursor) { int i = 1; do { print(i++ + ": "); _printTuple(rootCursor); println(); flush(); } while(rootCursor.next()); } // If the Cursor passed to the _printTuple method is a ValueCursor, // the method prints the value at the current position of the ValueCursor. // If the Cursor passed in is a CompoundCursor, the method gets the // outputs of the CompoundCursor and iterates through the outputs, // recursively calling itself for each output. The method then gets the // base ValueCursor of the CompoundCursor and calls itself again. private void _printTuple(Cursor cursor) { if(cursor instanceof CompoundCursor) { CompoundCursor compoundCursor = (CompoundCursor)cursor; // Put an open parenthesis before the value of each output. print("("); Iterator iterOutputs = compoundCursor.getOutputs().iterator(); Cursor output = (Cursor)iterOutputs.next(); _printTuple(output); while(iterOutputs.hasNext()) { // Put a comma after the value of each output. print(","); _printTuple((Cursor)iterOutputs.next()); } // Put a comma after the value of the last output. print(","); // Get the base ValueCursor. _printTuple(compoundCursor.getValueCursor()); // Put a close parenthesis after the base value to indicate // the end of the tuple. print(")"); } else if(cursor instanceof ValueCursor) { ValueCursor valueCursor = (ValueCursor) cursor; if (valueCursor.hasCurrentValue()) print(valueCursor.getCurrentValue()); else // If this position has a null value. print("NA"); } }
With the methods of a CompoundCursor
you can easily move through, or navigate, its structure and get the values from its ValueCursor
descendents. Data from a multidimensional OLAP query is often displayed in a crosstab format, or as a table or a graph.
To display the data for multiple rows and columns, you loop through the positions at different levels of the CompoundCursor
depending on the needs of your display. For some displays, such as a table, you loop through the positions of the parent CompoundCursor
. For other displays, such as a crosstab, you loop through the positions of the child Cursor
objects.
To display the results of a query in a table view, in which each row contains a value from each output ValueCursor
and from the base ValueCursor
, you determine the position of the top-level, or root, CompoundCursor
and then iterate through its positions. Example 9-6 displays only a portion of the result set at one time. It creates a Cursor
for a Source
that represents a query that is based on a measure that has unit cost values. The dimensions of the measure are the product and time dimensions. The creation of the primary Source
objects and the derived selections of the dimensions is not shown.
The example joins the Source
objects representing the dimension value selections to the Source
representing the measure. It prepares and commits the current Transaction
and then creates a Cursor
, casting it to a CompoundCursor
. The example sets the position of the CompoundCursor
, iterates through twelve positions of the CompoundCursor
, and prints out the values specified at those positions. The TransactionProvider
is tp
and the DataProvider
is dp
.
Example 9-6 Navigating for a Table View
Source unitPriceByMonth = unitPrice.join(productSel) .join(timeSel); // Prepare and commit the current Transaction (code not shown). // Create a Cursor for unitPriceByMonth. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitPriceByMonth); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor(); // Determine a starting position and the number of rows to display. int start = 7; int numRows =12; println("Month Product Unit Price"); println("----- ------- ----------"); // Iterate through the specified positions of the root CompoundCursor. // Assume that the Cursor contains at least (start + numRows) positions. for(int pos = start; pos < start + numRows; pos++) { // Set the position of the root CompoundCursor. rootCursor.setPosition(pos); // Print the local values of the output and base ValueCursors. // The getLocalValue method gets the local value from the unique // value of a dimension element. String timeValue = ((ValueCursor)rootCursor.getOutputs().get(0)) .getCurrentString(); String timeLocVal = getLocalValue(timeValue); String prodValue = ((ValueCursor)rootCursor.getOutputs().get(1)) .getCurrentString(); String prodLocVal = getLocalValue(prodValue); Object price = rootCursor.getValueCursor().getCurrentValue(); // sp9 is nine spaces. println(" " + timeLocVal + sp9 + prodLocVal + sp9 + price); }; cursorMngr.close();
If the time selection for the query has eight values, such as the first month of each calendar quarter for the years 2001 and 2002, and the product selection has three values, then the result set of the unitPriceByMonth
query has twenty-four positions. Example 9-6 displays the following table, which has the values specified by positions 7 through 18 of the CompoundCursor
.
Month Product Unit Price ------ ------- ---------- 61 13 2505.57 61 14 3155.91 61 15 2892.18 64 13 2337.30 64 14 3105.53 64 15 2856.86 69 13 2140.71 69 14 3008.95 69 15 2896.77 72 13 2130.88 72 14 2953.96 72 15 2880.39
Example 9-7 uses the same query as Example 9-6. In a crosstab view, the first row is column headings, which are the values from prodSel
in this example. The output for prodSel
is the faster varying output because the prodSel
dimension selection is the last output in the list of outputs that results from the operations that join the measure to the dimension selections. The remaining rows begin with a row heading. The row headings are values from the slower varying output, which is timeSel
. The remaining positions of the rows, under the column headings, contain the unitPrice
values specified by the set of the dimension values. To display the results of a query in a crosstab view, you iterate through the positions of the children of the top-level CompoundCursor
.
The TransactionProvider
is tp
and the DataProvider
is dp
.
Example 9-7 Navigating for a Crosstab View without Pages
Source unitPriceByMonth = unitPrice.join(productSel) .join(timeSel); // Prepare and commit the current Transaction (code not shown). // Create a Cursor for unitPriceByMonth. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitPriceByMonth); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor(); // Get the outputs and the ValueCursor. List outputs = rootCursor.getOutputs(); // The first output has the values of timeSel, the slower varying output. ValueCursor rowCursor = (ValueCursor) outputs.get(0); // The second output has the faster varying values of productSel. ValueCursor columnCursor = (ValueCursor) outputs.get(1); // The base ValueCursor has the values from unitPrice. ValueCursor unitPriceValues = rootCursor.getValueCursor(); // Display the values as a crosstab. println("\t Product"); println("\t-----------------------"); print("Month"); do { String value = ((ValueCursor) columnCursor).getCurrentString(); print("\t" + getLocalValue(value) + " "); } while (columnCursor.next()); println(); println("-----\t-------\t-------\t-------"); // Reset the column Cursor to its first element. columnCursor.setPosition(1); do { // Print the row dimension values. String value = ((ValueCursor) rowCursor).getCurrentString(); print(getLocalValue(value) + "\t"); // Loop over columns. do { // Print data value. print(unitPriceValues.getCurrentValue() + "\t"); } while (columnCursor.next()); println(); // Reset the column Cursor to its first element. columnCursor.setPosition(1); } while (rowCursor.next()); cursorMngr.close();
The following is a crosstab view of the values from the result set specified by the unitPriceByMonth
query. The first line labels the rightmost three columns as having product values. The third line labels the first column as having month values and then labels each of the rightmost three columns with the product value for that column. The remaining lines have the month value in the left column and then have the data values from the units measure for the specified month and product.
Product ------------------------- Month 13 14 15 ----- ------- ------- ------- 55 2426.07 3223.28 3042.22 58 2412.42 3107.65 3026.12 61 2505.57 3155.91 2892.18 64 2337.30 3105.53 2856.86 69 2140.71 3008.95 2896.77 72 2130.88 2953.96 2880.39 75 2074.56 3002.34 2865.14 78 1921.62 2943.96 2850.88
Example 9-8 creates a Source
that is based on a measure of units sold values. The dimensions of the measure are the customer, product, time, and channel dimensions. The Source
objects for the dimensions represent selections of the dimension values. The creation of those Source
objects is not shown.
The query that results from joining the dimension selections to the measure Source
represents unit sold values as specified by the values of its outputs.
The example creates a Cursor
for the query and then sends the Cursor
to the printAsCrosstab
method, which prints the values from the Cursor
in a crosstab. That method calls other methods that print page, column, and row values.
The fastest varying output of the Cursor
is the selection of products, which has three values (the product items 13, 14, and 15). The product values are the column headings of the crosstab. The next fastest varying output is the selection of customers, which has three values (the customers 58, 61, and 65). Those three values are the row headings. The page dimensions are selections of three time values (the months 43, 44, and 45), and one channel value (2, which is the direct sales channel).
The TransactionProvider
is tp
and the DataProvider
is dp
. The getLocalValue
method gets the local value from a unique dimension value.
Example 9-8 Navigating for a Crosstab View with Pages
// In someMethod. Source unitsForSelections = units.join(prodSel) .join(custSel) .join(timeSel) .join(chanSel); // Prepare and commit the current Transaction (code not shown). // Create a Cursor for unitsForSelections. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitsForSelections); SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); CompoundCursor unitsForSelCursor = (CompoundCursor) cursorMngr.createCursor(); // Send the Cursor to the printAsCrosstab method. printAsCrosstab(unitsForSelCursor); cursorMngr.close(); // The remainder of the code of someMethod is not shown. private void printAsCrosstab(CompoundCursor rootCursor) { List outputs = rootCursor.getOutputs(); int nOutputs = outputs.size(); // Set the initial positions of all outputs. Iterator outputIter = outputs.iterator(); while (outputIter.hasNext()) ((Cursor) outputIter.next()).setPosition(1); // The last output is fastest-varying; it represents columns. // The next to last output represents rows. // All other outputs are on the page. Cursor colCursor = (Cursor) outputs.get(nOutputs - 1); Cursor rowCursor = (Cursor) outputs.get(nOutputs - 2); ArrayList pageCursors = new ArrayList(); for (int i = 0 ; i < nOutputs - 2 ; i++) { pageCursors.add(outputs.get(i)); } // Get the base ValueCursor, which has the data values. ValueCursor dataCursor = rootCursor.getValueCursor(); // Print the pages of the crosstab. printPages(pageCursors, 0, rowCursor, colCursor, dataCursor); } // Prints the pages of a crosstab. private void printPages(List pageCursors, int pageIndex, Cursor rowCursor, Cursor colCursor, ValueCursor dataCursor) { // Get a Cursor for this page. Cursor pageCursor = (Cursor) pageCursors.get(pageIndex); // Loop over the values of this page dimension. do { // If this is the fastest-varying page dimension, print a page. if (pageIndex == pageCursors.size() - 1) { // Print the values of the page dimensions. printPageHeadings(pageCursors); // Print the column headings. printColumnHeadings(colCursor); // Print the rows. printRows(rowCursor, colCursor, dataCursor); // Print a couple of blank lines to delimit pages. println(); println(); } // If this is not the fastest-varying page, recurse to the // next fastest varying dimension. else { printPages(pageCursors, pageIndex + 1, rowCursor, colCursor, dataCursor); } } while (pageCursor.next()); // Reset this page dimension Cursor to its first element. pageCursor.setPosition(1); } // Prints the values of the page dimensions on each page. private void printPageHeadings(List pageCursors) { // Print the values of the page dimensions. Iterator pageIter = pageCursors.iterator(); while (pageIter.hasNext()) { String value = ((ValueCursor) pageIter.next()).getCurrentString(); println(getLocalValue(value)); } println(); } // Prints the column headings on each page. private void printColumnHeadings(Cursor colCursor) { do { print("\t"); String value = ((ValueCursor) colCursor).getCurrentString(); print(getLocalValue(value)); } while (colCursor.next()); println(); colCursor.setPosition(1); } // Prints the rows of each page. private void printRows(Cursor rowCursor, Cursor colCursor, ValueCursor dataCursor) { // Loop over rows. do { // Print row dimension value. String value = ((ValueCursor) rowCursor).getCurrentString(); print(getLocalValue(value)); print("\t"); // Loop over columns. do { // Print data value. print(dataCursor.getCurrentValue()); print("\t"); } while (colCursor.next()); println(); // Reset the column Cursor to its first element. colCursor.setPosition(1); } while (rowCursor.next()); // Reset the row Cursor to its first element. rowCursor.setPosition(1); }
Example 9-8 displays the following values, formatted as a crosstab. The display has added page, column, and row headings to identify the local values of the dimensions.
Channel 2 Customer 43 Product ---------------- Month 13 14 15 ----- -- -- -- 65 1 0 0 58 2 4 2 61 2 1 1 Channel 2 Customer 44 Product ---------------- Month 13 14 15 ----- -- -- -- 65 1 1 1 58 6 6 5 61 2 2 1 Channel 2 Customer 45 Product ---------------- Month 13 14 15 ----- -- -- -- 65 2 2 0 58 2 0 2 61 3 2 0
You can specify the following aspects of the behavior of a Cursor
.
The fetch size of a Cursor
, which is the number of elements of the result set that the Cursor
retrieves during one fetch operation.
Whether Oracle OLAP calculates the extent of the Cursor
. The extent is the total number of positions of the Cursor
. If the Cursor
is a child Cursor
of a CompoundCursor
, its extent is relative to any slower varying outputs.
Whether Oracle OLAP calculates the positions in the parent Cursor
at which the value of a child Cursor
starts or ends.
To specify the behavior of Cursor
, you use methods of the CursorSpecification
for that Cursor
. To get the CursorSpecification
for a Cursor
, you use methods of the CursorManagerSpecification
that you create for a Source
.
Note:
Specifying the calculation of the extent or the starting or ending position in a parentCursor
of the current value of a child Cursor
can be a very expensive operation. The calculation can require considerable time and computing resources. You should only specify these calculations when your application needs them.For more information on the relationships of Source
, Cursor
, CursorSpecification
, and CursorManagerSpecification
objects or the concepts of fetch size, extent, or Cursor
positions, see Chapter 8.
Example 9-9 creates a Source
, creates a CursorManagerSpecification
for the Source
, and then gets the CursorSpecification
objects from a CursorManagerSpecification
. The root CursorSpecification
is the CursorSpecification
for the top-level CompoundCursor
.
Example 9-9 Getting CursorSpecification Objects from a CursorManagerSpecification
Source unitsForSelections = units.join(prodSel) .join(custSel) .join(timeSel) .join(chanSel); // Prepare and commit the current Transaction (code not shown). // Create a Cursor for unitsForSelections. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitsForSelections); // Get the root CursorSpecification of the CursorManagerSpecification. CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification(); // Get the CursorSpecification for the base values. ValueCursorSpecification baseValueSpec = rootCursorSpec.getValueCursorSpecification(); // Get the CursorSpecification objects for the outputs. List outputSpecs = rootCursorSpec.getOutputs(); ValueCursorSpecification chanSelValCSpec = (ValueCursorSpecification) outputSpecs.get(0); ValueCursorSpecification timeSelValCSpec = (ValueCursorSpecification) outputSpecs.get(1); ValueCursorSpecification prodSelValCSpec = (ValueCursorSpecification) outputSpecs.get(2); ValueCursorSpecification custSelValCSpec = (ValueCursorSpecification) outputSpecs.get(3);
Once you have the CursorSpecification
objects, you can use their methods to specify the behavior of the Cursor
objects that correspond to them.
To manage the display of the result set retrieved by a CompoundCursor
, you sometimes need to know the extent of its child Cursor
components. You might also want to know the position at which the current value of a child Cursor
starts in its parent CompoundCursor
. You might want to know the span of the current value of a child Cursor
. The span is the number of positions of the parent Cursor
that the current value of the child Cursor
occupies. You can calculate the span by subtracting the starting position of the value from its ending position and subtracting 1.
Before you can get the extent of a Cursor
or get the starting or ending positions of a value in its parent Cursor
, you must specify that you want Oracle OLAP to calculate the extent or those positions. To specify the performance of those calculations, you use methods of the CursorSpecification
for the Cursor
.
Example 9-10 specifies calculating the extent of a Cursor
. The example uses the CursorManagerSpecification
from Example 9-9.
Example 9-10 Specifying the Calculation of the Extent of a Cursor
CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification(); rootCursorSpec.setExtentCalculationSpecified(true);
You can use methods of a CursorSpecification
to determine whether the CursorSpecification
specifies the calculation of the extent of a Cursor
as in the following example.
boolean isSet = rootCursorSpec.isExtentCalculationSpecified();
Example 9-11 specifies calculating the starting and ending positions of the current value of a child Cursor
in its parent Cursor
. The example uses the CursorManagerSpecification
from Example 9-9.
Example 9-11 Specifying the Calculation of Starting and Ending Positions in a Parent
CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification(); // Get the List of CursorSpecification objects for the outputs. // Iterate through the list, specifying the calculation of the extent // for each output CursorSpecification. Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator(); while(iterOutputSpecs.hasNext()) { ValueCursorSpecification valCursorSpec = (ValueCursorSpecification) iterOutputSpecs.next(); valCursorSpec.setParentStartCalculationSpecified(true); valCursorSpec.setParentEndCalculationSpecified(true); }
You can use methods of a CursorSpecification
to determine whether the CursorSpecification
specifies the calculation of the starting or ending positions of the current value of a child Cursor
in its parent Cursor
, as in the following example.
Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator(); ValueCursorSpecification valCursorSpec = (ValueCursorSpecification) iterOutputSpecs.next(); while(iterOutputSpecs.hasNext()) { if (valCursorSpec.isParentStartCalculationSpecified()) // Do something. if (valCursorSpec.isParentEndCalculationSpecified()) // Do something. valCursorSpec = (ValueCursorSpecification) iterOutputSpecs.next(); }
Example 9-12 determines the span of the positions in a parent CompoundCursor
of the current value of a child Cursor
for two of the outputs of the CompoundCursor
. The example uses the unitForSelections
Source
from Example 9-8.
The example gets the starting and ending positions of the current values of the time and product selections and then calculates the span of those values in the parent Cursor
. The parent is the root CompoundCursor
. The TransactionProvider
is tp
and the DataProvider
is dp
.
Example 9-12 Calculating the Span of the Positions in the Parent of a Value
Source unitsForSelections = units.join(prodSel) .join(custSel) .join(timeSel) .join(chanSel); // Prepare and commit the current Transaction (code not shown). // Create a CursorManagerSpecification for unitsForSelections. CursorManagerSpecification cursorMngrSpec = dp.createCursorManagerSpecification(unitsForSelections); // Get the root CursorSpecification from the CursorManagerSpecification. CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification(); // Get the CursorSpecification objects for the outputs. List outputSpecs = rootCursorSpec.getOutputs(); ValueCursorSpecification timeSelValCSpec = (ValueCursorSpecification) outputSpecs.get(1); // Output for time. ValueCursorSpecification prodSelValCSpec = (ValueCursorSpecification) outputSpecs.get(3); // Output for product. // Specify the calculation of the starting and ending positions. timeSelValCSpec.setParentStartCalculationSpecified(true); timeSelValCSpec.setParentEndCalculationSpecified(true); prodSelValCSpec.setParentStartCalculationSpecified(true); prodSelValCSpec.setParentEndCalculationSpecified(true); // Create the CursorManager and the Cursor. SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor(); // Get the child Cursor objects. ValueCursor baseValCursor = cursor.getValueCursor(); List outputs = rootCursor.getOutputs(); ValueCursor chanSelVals = (ValueCursor) outputs.get(0); ValueCursor timeSelVals = (ValueCursor) outputs.get(1); ValueCursor custSelVals = (ValueCursor) outputs.get(2); ValueCursor prodSelVals = (ValueCursor) outputs.get(3); // Set the position of the root CompoundCursor. rootCursor.setPosition(15); // Get the values at the current position and determine the span // of the values of the time and product outputs. print(promoSelVals.getCurrentValue() + ", "); print(chanSelVals.getCurrentValue() + ", "); print(timeSelVals.getCurrentValue() + ", "); print(custSelVals.getCurrentValue() + ", "); print(prodSelVals.getCurrentValue() + ", "); println(baseValCursor.getCurrentValue()); // Determine the span of the values of the two fastest varying outputs. int span; span = (prodSelVals.getParentEnd() - prodSelVals.getParentStart()) +1); println("The span of " + prodSelVals.getCurrentValue() + " at the current position is " + span + ".") span = (timeSelVals.getParentEnd() - timeSelVals.getParentStart()) +1); println("The span of " + timeSelVals.getCurrentValue() + " at the current position is " + span + ".") cursorMngr.close();
This example displays the following text.
CHANNEL_PRIMARY_AW::CHANNEL_AW::2, CALENDAR_YEAR_AW::MONTH_AW::44, SHIPMENTS_AW::SHIP_TO_AW::58, PRODUCT_PRIMARY_AW::ITEM_AW::15, 5.0 The span of PRODUCT_PRIMARY_AW::ITEM_AW::15 at the current position is 1. The span of CALENDAR_YEAR_AW::MONTH_AW::44 at the current position is 9.
The number of elements of a Cursor
that Oracle OLAP sends to the client application during one fetch operation depends on the fetch size specified for that Cursor
. You can set the fetch size on the root Cursor
for a Source
. Cursor
for that CursorSpecification
to change the fetch size of the Cursor
. The default fetch size is 100.
Example 9-13 uses the CursorManagerSpecification
from Example 9-9. It gets the default fetch size from the root CursorSpecification
, creates a Cursor
and sets a different fetch size on it, and then gets the fetch size for the Cursor
. The TransactionProvider
is tp
and the DataProvider
is dp
.
Example 9-13 Specifying a Fetch Size
CursorSpecification rootCursorSpec = cursorMngrSpec.getRootCursorSpecification(); println("The default fetch size is " + rootCursorSpec.getDefaultFetchSize() + "."); CursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec); Cursor rootCursor = cursorMngr.createCursor(); rootCursor.setFetchSize(10); println("The fetch size is now " + rootCursor.getFetchSize()) + ".";
This example displays the following text.
The default fetch size is 100. The fetch size is now 10.