Retrieving the Results of a Query

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:

1. 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.
2. 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 on your TransactionProvider. For more information on preparing and committing a Transaction, see Chapter 7. If the Source is a primary Source, then you do not need to prepare and commit the Transaction.
3. Create a CursorManagerSpecification by calling the createCursorManagerSpecification method on your DataProvider and passing that method the Source.
4. Create a SpecifiedCursorManager by calling the createCursorManager method on 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.
5. Create a Cursor by calling the createCursor method on 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.

Example 9-1 Creating a Cursor

try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  System.out.println("Caught exception " + 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();

Getting Values from a Cursor

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 on 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 on an MdmDimension, such as an MdmDimension that represents a hierarchical list of 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 mdmProductHier, which is an MdmDimension 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 productSource = mdmProductHier.getSource();
// Because productSource 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 productCursor = cursorMngr.createCursor();
// Cast the Cursor to a ValueCursor.
ValueCursor productValues = (ValueCursor) productCursor;
// Set the position to the fifth element of the ValueCursor.
productValues.setPosition(5);

// Product values are strings. Get the String value at the current
// position.
String value = productValues.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

// productValues is the ValueCursor for productSource
productValues.setPosition(1);
do {
   System.out.println(productValues.getCurrentValue);
}
while(productValues.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 on 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 salesAmount, that results from calling the getSource method on an MdmMeasure that represents monetary amounts for sales. The dimensions of the measure are MdmDimension objects representing products, customers, times, channels, and promotions. This example uses Source objects that represent selected values from those dimensions. The names of those Source objects are prodSel, custSel, timeSel, chanSel, and promoSel. 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 salesForSelections. It creates a Cursor, named salesForSelCursor, for salesForSelections, casts the Cursor to a CompoundCursor, named salesCompndCrsr, 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 dimensions were joined to the measure. In the example, dp is the DataProvider and tp is the TransactionProvider.

Example 9-4 Getting ValueCursor Objects from a CompoundCursor

Source salesForSelections = salesAmount.join(prodSel)
                                       .join(custSel)
                                       .join(timeSel)
                                       .join(chanSel)
                                       .join(promoSel);
// Prepare and commit the current Transaction
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

// Create a Cursor for salesForSelections
CursorManagerSpecification cursorMngrSpec =
            dp.createCursorManagerSpecification(salesForSelections);
SpecifiedCursorManager cursorMngr = 
                             dp.createCursorManager(cursorMngrSpec);
Cursor salesForSelCursor = cursorMngr.createCursor();

// Cast salesForSelCursor to a CompoundCursor
CompoundCursor salesCompndCrsr = (CompoundCursor) salesValues;

// Get the base ValueCursor
ValueCursor specifiedSalesVals = salesCompndCrsr.getValueCursor();

// Get the outputs
List outputs = salesCompndCrsr.getOutputs();
ValueCursor promoSelVals = (ValueCursor) outputs.get(0);
ValueCursor chanSelVals = (ValueCursor) outputs.get(1);
ValueCursor timeSelVals = (ValueCursor) outputs.get(2);
ValueCursor custSelVals = (ValueCursor) outputs.get(3);
ValueCursor prodSelVals = (ValueCursor) outputs.get(4);

// 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 sales amount 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, salesForSelections, represents a query has nested outputs, which means it has more than one level of outputs.

The CompoundCursor that this example creates for salesForSelections 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 promotion dimension values, promoSel, to the selection of channel dimension values, chanSel. The result is chanByPromoSel, a Source that has channel values as its base values and promotion values as the values of its output. The example joins to salesAmount the selections of product, customer, and time values, and then joins chanByPromoSel. The resulting query is represented by salesForSelections.

The example prepares and commits the current Transaction and creates a Cursor, named salesForSelCursor, for salesForSelections.

The example casts the Cursor to a CompoundCursor, named salesCompndCrsr, and gets the base ValueCursor and the outputs from it. In the example, dp is the DataProvider and tp is the TransactionProvider.

Example 9-5 Getting Values from a CompoundCursor with Nested Outputs

// ...in someMethod...
Source chanByPromoSel = chanSel.join(promoSel);
Source salesForSelections = salesAmount.join(prodSel)
                                       .join(custSel)
                                       .join(timeSel)
                                       .join(chanByPromoSel);

// Prepare and commit the current Transaction
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

// Create a Cursor for salesForSelections
CursorManagerSpecification cursorMngrSpec =
            dp.createCursorManagerSpecification(salesForSelections);
SpecifiedCursorManager cursorMngr = 
                             dp.createCursorManager(cursorMngrSpec);
Cursor salesForSelCursor = cursorMngr.createCursor();

// Send the Cursor to a method that does different operations 
// depending on whether the Cursor is a CompoundCursor or a 
// ValueCursor.
printCursor(salesForSelCursor);
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.
public void printCursor(Cursor rootCursor) {
  int i = 1;
  do {
     output.print(i++ + ": ");
     _printTuple(rootCursor);
     output.print("\n");
     output.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
    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
      output.print(",");
      _printTuple((Cursor)iterOutputs.next());
    }
     // Put a comma after the value of the last output
    output.print(",");
    // Get the base ValueCursor
    _printTuple(compoundCursor.getValueCursor());
    
   // Put a close parenthesis after the base value to indicate  
   // the end of the tuple.
       output.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");
  }
}