| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-01 |
|
|
View PDF |
| Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-01 |
|
|
View PDF |
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 9, "Understanding Cursor Classes and Concepts".
This chapter includes the following topics:
For the complete code of the examples in this chapter, see the example programs available from the Overview of the Oracle OLAP Java API Reference.
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 6, "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 8, "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 10-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.
Example 10-1 Creating a Cursor
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
System.out.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 10-4. For an example of a Source that has nested output values, see Example 10-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 10-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 10-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 10-3 uses the same Cursor as Example 10-2. Example 10-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 10-3 Getting All of the Values from a ValueCursor
// prodValues is the ValueCursor for prodSource
prodValues.setPosition(1);
do
{
System.out.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 10-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 10-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 10-4 Getting ValueCursor Objects from a CompoundCursor
Source unitsForSelections = units.join(prodSel)
.join(custSel)
.join(timeSel)
.join(chanSel);
// Prepare and commit the current Transaction
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
System.out.println("Cannot commit the current Transaction. " + e );
}
tp.commitCurrentTransaction();
// 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 10-5 uses the same units measure as Example 10-4, but it joins the dimension selections to the measure differently. Example 10-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 10-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 10-5 Getting Values from a CompoundCursor with Nested Outputs
// ...in someMethod... Source custByChanSel = custSel.join(chanSel Source unitsForSelections = units.join(prodSel) .join(timeSel) .join(custByChanSel); // Prepare and commit the current Transaction try { tp.prepareCurrentTransaction(); } catch(NotCommittableException e) { System.out.println("Caught exception " + e + "."); } tp.commitCurrentTransaction(); // 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... // 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 { cpw.print(i++ + ": "); _printTuple(rootCursor); cpw.print("\n"); cpw.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 cpw.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 cpw.print(","); _printTuple((Cursor)iterOutputs.next()); } // Put a comma after the value of the last output cpw.print(","); // Get the base ValueCursor _printTuple(compoundCursor.getValueCursor()); // Put a close parenthesis after the base value to indicate // the end of the tuple. cpw.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 10-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. The cpw object is a PrintWriter.
Example 10-6 Navigating for a Table View
Source unitPriceByMonth = unitPrice.join(productSel)
.join(timeSel);
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
cpw.println("Cannot prepare the current Transaction. " + e);
}
tp.commitCurrentTransaction();
// 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;
cpw.println("Month Product Unit Price");
cpw.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();
cpw.println(" " + timeLocVal + " " + prodLocVal
+ " " + 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 10-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 4281.42 69 14 6017.90 69 15 5793.54 72 13 4261.76 72 14 5907.92 72 15 5760.78
Example 10-7 uses the same query as Example 10-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. The cpw object is a PrintWriter.
Example 10-7 Navigating for a Crosstab View without Pages
Source unitPriceByMonth = unitPrice.join(productSel) .join(timeSel); try { tp.prepareCurrentTransaction(); } catch(NotCommittableException e) { cpw.println("Cannot prepare the current Transaction. " + e); } tp.commitCurrentTransaction(); // 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. cpw.println("\t Product"); cpw.println("\t-----------------------"); cpw.print("Month"); do { String value = ((ValueCursor) columnCursor).getCurrentString(); cpw.print("\t" + getLocalValue(value) + " "); } while (columnCursor.next()); cpw.println(); cpw.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(); cpw.print(getLocalValue(value) + "\t"); // Loop over columns do { // Print data value cpw.print(unitPriceValues.getCurrentValue() + "\t"); } while (columnCursor.next()); cpw.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 4281.42 6017.90 5793.54
72 4261.76 5907.92 5760.78
75 4149.12 6004.68 5730.28
78 3843.24 5887.92 5701.76
Example 10-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 cpw object is a PrintWriter. The getLocalValue method gets the local value from a unique dimension value.
Example 10-8 Navigating for a Crosstab View with Pages
// ...in someMethod...
Source unitsForSelections = units.join(prodSel)
.join(custSel)
.join(timeSel)
.join(chanSel);
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
cpw.println("Cannot prepare the current Transaction. " + e);
}
tp.commitCurrentTransaction();
// 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...
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
cpw.println();
cpw.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();
cpw.println(getLocalValue(value));
}
cpw.println();
}
// Prints the column headings on each page
private void printColumnHeadings(Cursor colCursor)
{
do
{
cpw.print("\t");
String value = ((ValueCursor) colCursor).getCurrentString();
cpw.print(getLocalValue(value));
} while (colCursor.next());
cpw.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();
cpw.print(getLocalValue(value));
cpw.print("\t");
// Loop over columns
do
{
// Print data value
cpw.print(dataCursor.getCurrentValue());
cpw.print("\t");
} while (colCursor.next());
cpw.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 10-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
----- -- -- --
58 2 4 2
61 2 1 1
65 1 0 0
Channel 2
Customer 44
Product
----------------
Month 13 14 15
----- -- -- --
58 6 6 5
61 2 2 1
65 1 1 1
Channel 2
Customer 45
Product
----------------
Month 13 14 15
----- -- -- --
58 2 0 2
61 3 2 0
65 2 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 9.
Example 10-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 10-9 Getting CursorSpecification Objects from a CursorManagerSpecification
Source unitsForSelections = units.join(prodSel)
.join(custSel)
.join(timeSel)
.join(chanSel);
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
System.out.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();
// 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 promoSelValCSpec =
(ValueCursorSpecification) outputSpecs.get(0);
ValueCursorSpecification chanSelValCSpec =
(ValueCursorSpecification) outputSpecs.get(1);
ValueCursorSpecification timeSelValCSpec =
(ValueCursorSpecification) outputSpecs.get(2);
ValueCursorSpecification prodSelValCSpec =
(ValueCursorSpecification) outputSpecs.get(3);
ValueCursorSpecification custSelValCSpec =
(ValueCursorSpecification) outputSpecs.get(4);
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 10-10 specifies calculating the extent of a Cursor. The example uses the CursorManagerSpecification from Example 10-9.
Example 10-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 10-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 10-9.
Example 10-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.
boolean isSet;
Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator();
ValueCursorSpecification valCursorSpec = (ValueCursorSpecification)
iterOutputSpecs.next();
while(iterOutputSpecs.hasNext())
{
isSet = valCursorSpec.isParentStartCalculationSpecified();
isSet = valCursorSpec.isParentEndCalculationSpecified();
valCursorSpec = (ValueCursorSpecification) iterOutputSpecs.next();
}
Example 10-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 10-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, the DataProvider is dp, and cpw is a PrintWriter.
Example 10-12 Calculating the Span of the Positions in the Parent of a Value
Source unitsForSelections = units.join(prodSel)
.join(custSel)
.join(timeSel)
.join(chanSel);
try
{
tp.prepareCurrentTransaction();
}
catch(NotCommittableException e)
{
cpw.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();
// 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(3); // output for time
ValueCursorSpecification prodSelValCSpec =
(ValueCursorSpecification) outputSpecs.get(1); // 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.
cpw.print(promoSelVals.getCurrentValue() + ", ");
cpw.print(chanSelVals.getCurrentValue() + ", ");
cpw.print(timeSelVals.getCurrentValue() + ", ");
cpw.print(custSelVals.getCurrentValue() + ", ");
cpw.print(prodSelVals.getCurrentValue() + ", ");
cpw.println(baseValCursor.getCurrentValue());
// Determine the span of the values of the two fastest varying outputs
int span;
span = (prodSelVals.getParentEnd() - prodSelVals.getParentStart()) +1);
cpw.println("The span of " + prodSelVals.getCurrentValue() +
" at the current position is " + span + ".")
span = (timeSelVals.getParentEnd() - timeSelVals.getParentStart()) +1);
cpw.println("The span of " + timeSelVals.getCurrentValue() +
" at the current position is " + span + ".")
cursorMngr.close();
This example displays the following text.
CHANNEL_ROLLUP::CHANNEL::2, CALENDAR::MONTH::44, SHIPMENTS_ROLLUP::SHIP_TO::61, PRODUCT_ROLLUP::ITEM::15, 1.0 The span of PRODUCT_ROLLUP::ITEM::15 at the current position is 1. The span of CALENDAR::MONTH::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 10-13 uses the CursorManagerSpecification from Example 10-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, the DataProvider is dp, and cpw is a PrintWriter.
Example 10-13 Specifying a Fetch Size
CursorSpecification rootCursorSpec =
cursorMngrSpec.getRootCursorSpecification();
context.println("The default fetch size is "
+ rootCursorSpec.getDefaultFetchSize() + ".");
CursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec);
Cursor rootCursor = cursorMngr.createCursor();
rootCursor.setFetchSize(10);
context.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.
