Skip Headers
Oracle® OLAP Developer's Guide to the OLAP API
10g Release 2 (10.2)

Part Number B14347-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

11 Creating Dynamic Queries

This chapter describes the Oracle OLAP API Template class and its related classes, which you use to create dynamic queries. This chapter also provides examples of implementations of those classes.

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.

About Template Objects

The Template class is the basis of a very powerful feature of the Oracle OLAP API. You use Template objects to create modifiable Source objects. With those Source objects, you can create dynamic queries that can change in response to end-user selections. Template objects also offer a convenient way for you to translate user-interface elements into OLAP API operations and objects.

These features are briefly described in the following section. The rest of this chapter describes the Template class and the other classes you use to create dynamic Source objects. For information on the Transaction objects that you use to make changes to the dynamic Source and to either save or discard those changes, see Chapter 8, "Using a TransactionProvider".

About Creating a Dynamic Source

The main feature of a Template is its ability to produce a dynamic Source. That ability is based on two of the other objects that a Template uses: instances of the DynamicDefinition and MetadataState classes.

When a Source is created, a SourceDefinition is automatically created. The SourceDefinition has information about how the Source was created. Once created, the Source and its SourceDefinition are paired immutably. The getSource method of a SourceDefinition returns its paired Source.

DynamicDefinition is a subclass of SourceDefinition. A Template creates a DynamicDefinition, which acts as a proxy for the SourceDefinition of the Source produced by the Template. This means that instead of always getting the same immutably paired Source, the getSource method of the DynamicDefinition gets whatever Source is currently produced by the Template. The instance of the DynamicDefinition does not change even though the Source that it gets is different.

The Source that a Template produces can change because the values, including other Source objects, that the Template uses to create the Source can change. A Template stores those values in a MetadataState. A Template provides methods to get the current state of the MetadataState, to get or set a value, and to set the state. You use those methods to change the data values the MetadataState stores.

You use a DynamicDefinition to get the Source produced by a Template. If your application changes the state of the values that the Template uses to create the Source, for example, in response to end-user selections, then the application uses the same DynamicDefinition to get the Source again, even though the new Source defines a result set different than the previous Source.

The Source produced by a Template can be the result of a series of Source operations that create other Source objects, such as a series of selections, sorts, calculations, and joins. You put the code for those operations in the generateSource method of a SourceGenerator for the Template. That method returns the Source produced by the Template. The operations use the data stored in the MetadataState.

You might build an extremely complex query that involves the interactions of dynamic Source objects produced by many different Template objects. The end result of the query building is a Source that defines the entire complex query. If you change the state of any one of the Template objects that you used to create the final Source, then the final Source represents a result set different than that of the previous Source. You can thereby modify the final query without having to reproduce all of the operations involved in defining the query.

About Translating User Interface Elements into OLAP API Objects

You design Template objects to represent elements of the user interface of an application. Your Template objects turn the selections that the end user makes into OLAP API query-building operations that produce a Source. You then create a Cursor to fetch the result set defined by the Source from Oracle OLAP. You get the values from the Cursor and display them to the end user. When an end user makes changes to the selections, you change the state of the Template. You then get the Source produced by the Template, create a new Cursor, get the new values, and display them.

Overview of Template and Related Classes

In the OLAP API, several classes work together to produce a dynamic Source. In designing a Template, you must implement or extend the following:

Instances of those three classes, plus instances of other classes that Oracle OLAP creates, work together to produce the Source that the Template defines. The classes that Oracle OLAP provides, which you create by calling factory methods, are the following:

What Is the Relationship Between the Classes That Produce a Dynamic Source?

The classes that produce a dynamic Source work together as follows:

  • A Template has methods that create a DynamicDefinition and that get and set the current state of a MetadataState. An extension to the Template abstract class adds methods that get and set the values of fields on the MetadataState.

  • The MetadataState implementation has fields for storing the data to use in generating the Source for the Template. When you create a new Template, you pass the MetadataState to the constructor of the Template. When you call the getSource method of the DynamicDefinition, the MetadataState is passed to the generateSource method of the SourceGenerator.

  • The DataProvider is used in creating a Template and by the SourceGenerator in creating new Source objects.

  • The SourceGenerator implementation has a generateSource method that uses the current state of the data in the MetadataState to produce a Source for the Template. You pass in the SourceGenerator to the createDynamicDefinition method of the Template to create a DynamicDefinition.

  • The DynamicDefinition has a getSource method that gets the Source produced by the SourceGenerator. The DynamicDefinition serves as a proxy for the immutably paired SourceDefinition of that Source.

Template Class

You use a Template to produce a modifiable Source. A Template has methods for creating a DynamicDefinition and for getting and setting the current state of the Template. In extending the Template class, you add methods that provide access to the fields on the MetadataState for the Template. The Template creates a DynamicDefinition that you use to get the Source produced by the SourceGenerator for the Template.

For an example of a Template implementation, see Example 11-1.

MetadataState Interface

An implementation of the MetadataState interface stores the current state of the values for a Template. A MetadataState must include a clone method that creates a copy of the current state.

When instantiating a new Template, you pass a MetadataState to the Template constructor. The Template has methods for getting and setting the values stored by the MetadataState. The generateSource method of the SourceGenerator for the Template uses the MetadataState when the method produces a Source for the Template.

For an example of a MetadataState implementation, see Example 11-2.

SourceGenerator Interface

An implementation of SourceGenerator must include a generateSource method, which produces a Source for a Template. A SourceGenerator must produce only one type of Source, such as a BooleanSource, a NumberSource, or a StringSource. In producing the Source, the generateSource method uses the current state of the data represented by the MetadataState for the Template.

To get the Source produced by the generateSource method, you create a DynamicDefinition by passing the SourceGenerator to the createDynamicDefinition method of the Template. You then get the Source by calling the getSource method of the DynamicDefinition.

A Template can create more than one DynamicDefinition, each with a differently implemented SourceGenerator. The generateSource methods of the different SourceGenerator objects use the same data, as defined by the current state of the MetadataState for the Template, to produce Source objects that define different queries.

For an example of a SourceGenerator implementation, see Example 11-3.

DynamicDefinition Class

DynamicDefinition is a subclass of SourceDefinition. You create a DynamicDefinition by calling the createDynamicDefinition method of a Template and passing it a SourceGenerator. You get the Source produced by the SourceGenerator by calling the getSource method of the DynamicDefinition.

A DynamicDefinition created by a Template is a proxy for the SourceDefinition of the Source produced by the SourceGenerator. The SourceDefinition is immutably paired to its Source. If the state of the Template changes, then the Source produced by the SourceGenerator is different. Because the DynamicDefinition is a proxy, you use the same DynamicDefinition to get the new Source even though that Source has a different SourceDefinition.

The getCurrent method of a DynamicDefinition returns the SourceDefinition immutably paired to the Source that the generateSource method currently returns. For an example of the use of a DynamicDefinition, see Example 11-4.

Designing and Implementing a Template

The design of a Template reflects the query-building elements of the user interface of an application. For example, suppose you want to develop an application that allows the end user to create a query that requests a number of values from the top or bottom of a list of values. The values are from one dimension of a measure. The other dimensions of the measure are limited to single values.

The user interface of your application has a dialog box that allows the end user to do the following:

To generate a Source that represents the query that the end user creates in the first dialog box, you design a Template called TopBottomTemplate. You also design a second Template, called SingleSelectionTemplate, to create a Source that represents the end user's selections of single values for the dimensions other than the base dimension. The designs of your Template objects reflect the user interface elements of the dialog boxes.

In designing the TopBottomTemplate and its MetadataState and SourceGenerator, you do the following:

Using your application, an end user selects units sold as the measure and products as the base dimension in the first dialog box. From the Single Selections dialog box, the end user selects the Asia Pacific region, the first quarter of 2001, and the direct sales channel as the single values for each of the remaining dimensions.

The query that the end user has created requests the ten products that have the highest total amount of units sold through the direct sales channel to customers in the Asia Pacific region during the calendar year 2001.

For examples of implementations of the TopBottomTemplate, TopBottomTemplateState, and TopBottomTemplateGenerator classes, and an example of an application that uses them, see Example 11-1, Example 11-2, Example 11-3, and Example 11-4. The TopBottomTemplateState and TopBottomTemplateGenerator classes are implemented as inner classes of the TopBottomTemplate outer class.

Implementing the Classes for a Template

Example 11-1 is an implementation of the TopBottomTemplate class.

Example 11-1 Implementing a Template

package globalExamples;

import oracle.olapi.data.source.DataProvider;
import oracle.olapi.data.source.DynamicDefinition;
import oracle.olapi.data.source.Source;
import oracle.olapi.data.source.SourceGenerator;
import oracle.olapi.data.source.Template;
import oracle.olapi.transaction.metadataStateManager.MetadataState;

/**
 * Creates a TopBottomTemplateState, a TopBottomTemplateGenerator,
 * and a DynamicDefinition. Gets the current state of the
 * TopBottomTemplateState and the values it stores. Sets the data
 * values stored by the TopBottomTemplateState and sets the changed state as
 * the current state.
 */
public class TopBottomTemplate extends Template 
{
// Constants for specifying the selection of elements from the 
// beginning or the end of the result set.
  public static final int TOP_BOTTOM_TYPE_TOP = 0;
  public static final int TOP_BOTTOM_TYPE_BOTTOM = 1;

  // Variable to store the DynamicDefinition.
  private DynamicDefinition _definition;

  /**
   * Creates a TopBottomTemplate with a default type and number values
   * and the specified base dimension.
   */
  public TopBottomTemplate(Source base, DataProvider dataProvider)
  {
    super(new TopBottomTemplateState(base, TOP_BOTTOM_TYPE_TOP, 0),
                                     dataProvider);
    // Create the DynamicDefinition for this Template. Create the 
    // TopBottomTemplateGenerator that the DynamicDefinition uses.
    _definition = 
    createDynamicDefinition(new TopBottomTemplateGenerator(dataProvider));
  }

  /**
   * Gets the Source produced by the TopBottomTemplateGenerator
   * from the DynamicDefinition.
   */
  public final Source getSource() 
  {
    return _definition.getSource();
  }

  /**
   * Gets the Source that is the base of the elements in the result set.
   * Returns null if the state has no base.
   */
  public Source getBase()
  {
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    return state.base;
  }

  /**
   * Sets a Source as the base.
   */
  public void setBase(Source base) 
  {
     TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    state.base = base;
    setCurrentState(state);
  }

  /**
   * Gets the Source that specifies the measure and the single
   * selections from the dimensions other than the base.
   */
  public Source getCriterion()
  {
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    return state.criterion;
  }

  /**
   * Specifies a Source that defines the measure and the single values 
   * selected from the dimensions other than the base.
   * The SingleSelectionTemplate produces such a Source.
   */
  public void setCriterion(Source criterion)
  {
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    state.criterion = criterion;
    setCurrentState(state);
  }

  /**
   * Gets the type, which is either TOP_BOTTOM_TYPE_TOP or 
   * TOP_BOTTOM_TYPE_BOTTOM.
   */
  public int getTopBottomType()
  {
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    return state.topBottomType;
  }

  /**
   * Sets the type.
   */
  public void setTopBottomType(int topBottomType)
  {
    if ((topBottomType < TOP_BOTTOM_TYPE_TOP) ||
        (topBottomType > TOP_BOTTOM_TYPE_BOTTOM ))
      throw new IllegalArgumentException("InvalidTopBottomType");
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    state.topBottomType = topBottomType;
    setCurrentState(state);
  }

  /**
   * Gets the number of values selected.
   */
  public float getN()
  {
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    return state.N;
  }

  /**
   * Sets the number of values to select.
   */
  public void setN(float N)
  {
    TopBottomTemplateState state = (TopBottomTemplateState) getCurrentState();
    state.N = N;
    setCurrentState(state);
  }
}

Example 11-2 is an implementation of the TopBottomTemplateState inner class.

Example 11-2 Implementing a MetadataState

/**
 * Stores data that can be changed by its TopBottomTemplate.
 * The data is used by a TopBottomTemplateGenerator in producing
 * a Source for the TopBottomTemplate.
 */
private static final class TopBottomTemplateState
     implements Cloneable, MetadataState
{
  public int topBottomType;
  public float N;
  public Source criterion;
  public Source base;

  /**
   * Creates a TopBottomTemplateState.
   */
  public TopBottomTemplateState(Source base, int topBottomType, float N)
  {
    this.base = base;
    this.topBottomType = topBottomType;
    this.N = N;
  }

  /**
   * Creates a copy of this TopBottomTemplateState.
   */
  public final Object clone()
  {
    try 
    {
      return super.clone();
    }
    catch(CloneNotSupportedException e) 
    {
      return null;
    }
  }
}

Example 11-3 is an implementation of the TopBottomTemplateGenerator inner class.

Example 11-3 Implementing a SourceGenerator

/**
 * Produces a Source for a TopBottomTemplate based on the data
 * values of a TopBottomTemplateState.
 */
private final class TopBottomTemplateGenerator
      implements SourceGenerator
{
  // Store the DataProvider.
  private DataProvider _dataProvider;

  /**
   * Creates a TopBottomTemplateGenerator.
   */
  public TopBottomTemplateGenerator(DataProvider dataProvider)
  {
    _dataProvider = dataProvider;
  }

  /**
   * Generates a Source for a TopBottomTemplate using the current 
   * state of the data values stored by the TopBottomTemplateState.
   */
  public Source generateSource(MetadataState state)
  {
    TopBottomTemplateState castState = (TopBottomTemplateState) state;
    if (castState.criterion == null)
      throw new NullPointerException("CriterionParameterMissing");
    Source sortedBase = null;
    if (castState.topBottomType == TOP_BOTTOM_TYPE_TOP)
      sortedBase = castState.base.sortDescending(castState.criterion);
    else
      sortedBase = castState.base.sortAscending(castState.criterion);
    return sortedBase.interval(1, Math.round(castState.N));
  }
}

Implementing an Application That Uses Templates

After you have stored the selections made by the end user in the MetadataState for the Template, use the getSource method of the DynamicDefinition to get the dynamic Source created by the Template. This section provides an example of an application that uses the TopBottomTemplate described in Example 11-1. For brevity, the code does not contain much exception handling.

The Context10g class used in the example has methods that do the following:

  • Connects to an Oracle Database instance as the specified user.

  • Provides the OLAP Catalog metadata objects for the measure and the dimensions selected by the end user.

  • Creates Cursor objects and displays their values.

Example 11-4 does the following:

  • Creates a Context10g object and from it gets the DataProvider and the TransactionProvider.

  • From the Context10g object, gets the MdmMeasure and the MdmPrimaryDimension objects that it uses.

  • Creates a SingleSelectionTemplate for selecting single values from some of the dimensions of the measure. For the code of the SingleSelectionTemplate class that this example uses, see Appendix B.

  • Creates a TopBottomTemplate and stores selections made by the end user.

  • Gets the Source produced by the TopBottomTemplate.

  • Uses the Context10g object to create a Cursor for that Source and to display its values.

To use Example 8-2 from Chapter 8, replace the lines in the run method from the following comment to the end of the method

// Replace from here on for the Using Child Transaction example.

You can also get the complete code for the examples from the Oracle OLAP Java API Reference..

Example 11-4 Getting the Source Produced by the Template

import oracle.olapi.data.source.DataProvider;
import oracle.olapi.data.source.Source;
import oracle.olapi.data.source.StringSource;
import oracle.olapi.transaction.Transaction;
import oracle.olapi.transaction.TransactionProvider;
import oracle.olapi.transaction.NotCommittableException;
import oracle.olapi.metadata.mdm.MdmDimensionMemberInfo;
import oracle.olapi.metadata.mdm.MdmHierarchy;
import oracle.olapi.metadata.mdm.MdmMeasure;
import oracle.olapi.metadata.mdm.MdmPrimaryDimension;
 
import java.util.Iterator;
import java.util.ArrayList;
import java.util.List;

/**
 * Creates a query that specifies a number of elements from the top
 * or bottom of a selection of dimension members, creates a Cursor
 * for the query, and displays the values of the Cursor.
 * The selected dimension members are those that have measure values
 * that are specified by selected members of the other dimensions of
 * the measure.
 */
public class TopBottomTest
{
  /**
   * Creates a Context10g object that connects to an Oracle Database instance.
   * Gets the MdmMeasure for the Global schema UNITS measure
   * and the MdmPrimaryDimension objects for that measure.
   * Gets the default hierarchies for the dimensions and then gets the Source
   * object for the base of the query.
   * Creates a SingleSelectionTemplate and adds selections to it.
   * Creates a TopBottomTemplate and sets its properties.
   * Gets the Source produced by the TopBottomTemplate, creates a Cursor 
   * for it, and displays the values of the Cursor.
   * Changes the state of the SingleSelectionTemplate and the
   * TopBottomTemplate, creates a new Cursor for the Source produced by the
   * TopBottomTemplate, and displays the values of that Cursor.
   */
  public void run(String[] args) 
  {
    // Create a Context10g object and get the DataProvider and
    // TransactionProvider from it.
    Context10g context = new Context10g(args, false);
    DataProvider dp = context.getDataProvider();
    TransactionProvider tp = context.getTransactionProvider();

    // Get the MdmMeasure for the measure.
    MdmMeasure mdmUnits = context.getMdmMeasureByName("UNITS");
    // Get the Source for the measure.
    Source units = mdmUnits.getSource();

    // Get the MdmPrimaryDimension objects for the dimensions of the measure.
    MdmPrimaryDimension[] mdmPrimDims = 
      context.getMdmPrimaryDimensionsByName(new String[] 
                                                {"CUSTOMER",
                                                 "PRODUCT",
                                                 "CHANNEL",
                                                 "TIME"});
    MdmPrimaryDimension mdmCustDim = mdmPrimDims[0];
    MdmPrimaryDimension mdmProdDim = mdmPrimDims[1];
    MdmPrimaryDimension mdmChanDim = mdmPrimDims[2];
    MdmPrimaryDimension mdmTimeDim = mdmPrimDims[3];

    // Get the default hierarchy of the Product dimension.
    MdmHierarchy mdmProdRollup = mdmProdDim.getDefaultHierarchy();

    // Get the StringSource for the hierarchy.
    StringSource prodRollup = (StringSource) mdmProdRollup.getSource();

    // Create a SingleSelectionTemplate to produce a Source that
    // represents the measure values specified by single members of each of
    // the dimensions of the measure other than the base dimension.
    SingleSelectionTemplate singleSelections =
                        new SingleSelectionTemplate(units, dp);

    // Create MdmDimensionMemberInfo objects for single members of the
    // other dimensions of the measure.
    MdmDimensionMemberInfo timeMemInfo =
         new MdmDimensionMemberInfo(mdmTimeDim, "CALENDAR::YEAR::4");
    MdmDimensionMemberInfo custMemInfo =
         new MdmDimensionMemberInfo(mdmCustDim, "SHIPMENTS_ROLLUP::REGION::8");
    MdmDimensionMemberInfo chanMemInfo =
         new MdmDimensionMemberInfo(mdmChanDim, "CHANNEL_ROLLUP::CHANNEL::2");
 
    // Add the dimension member information objects to the
    // SingleSelectionTemplate.
    singleSelections.addDimMemberInfo(custMemInfo);
    singleSelections.addDimMemberInfo(chanMemInfo);
    singleSelections.addDimMemberInfo(timeMemInfo);

    // Create a TopBottomTemplate specifying, as the base, the Source for a
    // a hierarchy of a dimension.
    TopBottomTemplate topNBottom = new TopBottomTemplate(prodRollup, dp);

    // Specify whether to retrieve the elements from the beginning (top) or the
    // end (bottom) of the selected elements of the base dimension.
    topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_TOP);

    // Set the number of elements of the base dimension to retrieve.
    topNBottom.setN(10);
    // Get the Source produced by the SingleSelectionTemplate and specify it as
    // the criterion object.
    topNBottom.setCriterion(singleSelections.getSource());

    // Prepare and commit the current transaction.
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      context.println("Cannot commit the current Transaction. " + e);
    }
    tp.commitCurrentTransaction();

    // Replace from here on for the Using Child Transaction Objects example.

    // Get the short value descriptions of the dimension members from the
    // SingleSelectionTemplate.
    StringBuffer shortDescrsForMemberVals =
                               singleSelections.getMemberShortDescrs(dp, tp);
    context.println("\nThe " + Math.round(topNBottom.getN()) +
                    " products with the most units sold \nfor" +
                    shortDescrsForMemberVals +" are:\n");

    // Get the Source produced by the TopBottomTemplate, create a Cursor
    // for it, and display the values of the Cursor.
    Source result = topNBottom.getSource();
    context.displayTopBottomResult(result);

    // Change a dimension member selection of the SingleSelectionTemplate.
    timeMemInfo.setUniqueValue("CALENDAR::YEAR::3");
    singleSelections.changeSelection(timeMemInfo);
 
    // After changing the selection of a dimension member, get the short value
    // descriptions of the dimension members again.
    StringBuffer shortDescrsForMemberValsAfter =
                                singleSelections.getMemberShortDescrs(dp, tp);

    // Change the number of elements selected and the type of selection.
    topNBottom.setN(5);
    topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_BOTTOM);
 
    // Prepare and commit the current transaction.
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      context.println("Cannot commit the current Transaction. " + e);
    }

    tp.commitCurrentTransaction();
 
    context.println("\nThe " + Math.round(topNBottom.getN()) + " products " +
                  "with the fewest units sold \nfor" +
                    shortDescrsForMemberValsAfter + " are:\n");
 
    // Create a new Cursor for the Source produced by the TopBottomTemplate
    // and display the Cursor values.
    context.displayTopBottomResult(result);
  } 

  /**
   * Runs the TopBottomTest application.
   *
   * @param args An array of String objects that provides the arguments
   *             required to connect to an Oracle Database instance, as
   *             specified in the Context10g class.
   */
  public static void main(String[] args) 
  {
    new TopBottomTest().run(args);
  }

}

The TopBottomTest program produces the following output.

The 10 products with the most units sold
for Asia Pacific, Direct Sales, 2001 are:

 1. PRODUCT_ROLLUP::TOTAL_PRODUCT::1
 2. PRODUCT_ROLLUP::CLASS::3
 3. PRODUCT_ROLLUP::FAMILY::7
 4. PRODUCT_ROLLUP::CLASS::2
 5. PRODUCT_ROLLUP::FAMILY::9
 6. PRODUCT_ROLLUP::FAMILY::6
 7. PRODUCT_ROLLUP::FAMILY::11
 8. PRODUCT_ROLLUP::ITEM::30
 9. PRODUCT_ROLLUP::ITEM::28
10. PRODUCT_ROLLUP::ITEM::47
 
The 5 products with the fewest units sold
for Asia Pacific, Direct Sales, 2000 are:
 
1. PRODUCT_ROLLUP::ITEM::44
2. PRODUCT_ROLLUP::ITEM::36
3. PRODUCT_ROLLUP::ITEM::43
4. PRODUCT_ROLLUP::ITEM::38
5. PRODUCT_ROLLUP::ITEM::21