11 Creating Dynamic Queries

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));
  }
}

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);
  }

}