Navigating a CompoundCursor for Different Displays of Data

With methods on 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. It casts the Cursor 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 output object is a PrintWriter.

Example 9-6 Navigating for a Table View

Source unitPriceByDay = unitPrice.join(productSel)
                                 .join(timeSel);
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

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

// Cast the Cursor to a CompoundCursor
CompoundCursor rootCursor = (CompoundCursor) unitPriceByDayCursor;

// Determine a starting position and the number of rows to display
int start = 7;
int numRows = 12;

// 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 values of the output ValueCursors
   output.print(rootCursor.getOutputs().get(0).getCurrentValue() + "\t");
   output.print(rootCursor.getOutputs().get(1).getCurrentValue() + "\t");
   // Print the value of the base ValueCursor and a new line
   output.print(rootCursor.getValueCursor().getCurrentValue() + "\n");
   output.flush();
};
cursorMngr.close();

If the time selection for the query has eight values, such as the first day of each calendar quarter for the years 1999 and 2000, and the product selection has three values, then the result set of the unitPriceByDay query has twenty-four positions. Example 9-6 displays something like the following table, which has the values specified by positions 7 through 18 of the CompoundCursor.

01-JUL-99    815     57
01-JUL-99    1050    23
01-JUL-99    2055    22
01-OCT-99    815     56
01-OCT-99    1050    24
01-OCT-99    2055    21
01-JAN-00    815     58
01-JAN-00    1050    24
01-JAN-00    2055    24
01-APR-00    815     59
01-APR-00    1050    24
01-APR-00    2055    25

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 timeSel in this example. The output for timeSel is the faster varying output because the timeSel dimension selection was joined to the measure first. The remaining rows begin with a row heading. The row headings are values from the slower varying output, which is productSel. 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 specify the positions of the children of the top-level CompoundCursor and then iterate through their positions. Example 9-7 gets the values but does not include code for putting the values in the appropriate cells of the crosstab display.

Example 9-7 Navigating for a Crosstab View without Pages

Source unitPriceByDay = unitPrice.join(productSel)
                                 .join(timeSel);
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

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

// Cast the Cursor to a CompoundCursor
CompoundCursor rootCursor = (CompoundCursor) unitPriceByDayCursor;

// Determine a starting position and the number of rows to display.
// colStart is the position in columnCursor at which the current
// display starts and rowStart is the position in rowCursor at
// which the current display starts.
int colStart = 1;
int rowStart = 1;
String productValue;
String timeValue;
double price;
int numProducts = 3;
int numDays = 12;

// Get the outputs and the ValueCursor
CompoundCursor rootCursor = (CompoundCursor) unitPriceByDayCursor;
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);
ValueCursor unitPriceValues = rootCursor.getValueCursor();// Prices

// Loop through positions of the faster varying output Cursor
for(int pPos = colStart; pPos < colStart + numProducts; pPos++) {
  columnCursor.setPosition(pPos);
  // Loop through positions of the slower varying output Cursor
  for(int tPos = rowStart; tPos < rowStart + numDays; tPos++) {
    rowCursor.setPosition(tPos);
    // Get the values. Sending the values to the appropriate
    // display mechanism is not shown.
    productValue = columnCursor.getCurrentString();
    timeValue = rowCursor.getCurrentString();
    price = unitPriceValues.getCurrentDouble();
  }
}
cursorMngr.close();

Figure 9-1 is crosstab view of the values from the result set specified by the unitPriceByDay query.

Figure 9-1 Crosstab View of the Result Set Specified by unitPriceByDay

Text description of the illustration xtabview.gif

Example 9-8 creates a Source that is based on a sales amount measure. The dimensions of the measure are the customer, product, time, channel, and promotion 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 total sales amount 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 customers, which has three values that specify all of the customers from France, the UK, and the USA. The customer values are the column headings of the crosstab. The next fastest varying output is the selection of products, which has four values that specify types of products. The page dimensions are selections of two time values, which are the first and second calendar quarters of the year 2000, one channel value, which is the direct channel, and one promotion value, which is all promotions.

The TransactionProvider is tp and the DataProvider is dp. The output object is a PrintWriter.

Example 9-8 Navigating for a Crosstab View with Pages

// ...in someMethod...
Source salesAmountsForSelections = salesAmount.join(customerSel)
                                              .join(productSel);
                                              .join(timeSel);
                                              .join(channelSel);
                                              .join(promotionSel);
try{
  tp.prepareCurrentTransaction();
}
catch(NotCommittableException e){
  output.println("Caught exception " + e + ".");
}
tp.commitCurrentTransaction();

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

// Send the Cursor to the printAsCrosstab method
printAsCrosstab(salesForSelCursor);

cursorMngr.close();
// ...the remainder of the code of someMethod...

// This method expects a CompoundCursor.
private void printAsCrosstab(Cursor cursor) {
  // Cast the Cursor to a CompoundCursor
  CompoundCursor rootCursor = (CompoundCursor) cursor;
  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
      output.println();
      output.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())
    output.println(((ValueCursor) pageIter.next()).getCurrentValue());
  output.println();
}

// Prints the column headings on each page
private void printColumnHeadings(Cursor colCursor) {
  do {
     output.print("\t");
     output.print(((ValueCursor) colCursor).getCurrentValue());
  } while (colCursor.next());
  output.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
    output.print(((ValueCursor) rowCursor).getCurrentValue());
    output.print("\t");
    // Loop over columns
    do {
          // Print data value
        output.print(dataCursor.getCurrentValue());
        output.print("\t");
      } while (colCursor.next());
    output.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);
}

The crosstab output of Example 9-8 looks like the following.

Promotion total
Direct
2000-Q1

                       FR           UK            US
Outerwear - Men        750563.50    938014.00     12773925.50
Outerwear - Women      984461.00    1388755.50    15421979.00
Outerwear - Boys       693382.00    799452.00     9183052.00
Outerwear - Girls      926520.50    977291.50     11854203.00

Promotion total
Direct
2000-Q2

                       FR            UK            US
Outerwear - Men        683521.00     711945.00     9947221.50
Outerwear - Women      840024.50     893587.50     12484221.00
Outerwear - Boys       600382.50     755031.00     8791240.00
Outerwear - Girls      901558.00     909421.50     9975927.00