10 Creating Dynamic Queries

Example 10-1 Implementing a Template

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 
 * that 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 dynamicDef;

  /**
   * 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.
    dynamicDef = 
    createDynamicDefinition(new TopBottomTemplateGenerator(dataProvider));
  }

  /**
   * Gets the Source produced by the TopBottomTemplateGenerator
   * from the DynamicDefinition.
   */
  public final Source getSource() 
  {
    return dynamicDef.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 10-2 is an implementation of the TopBottomTemplateState inner class.

Example 10-2 Implementing a MetadataState

/**
 * Stores data that can be changed by a 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 10-3 is an implementation of the TopBottomTemplateGenerator inner class.

Example 10-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;

    // Depending on the topBottomType value, select from the base Source
    // the elements specified by the criterion Source and sort the
    // elements in ascending or descending order.
    // For descending order, specify that null values are last.
    // For ascending order, specify that null values are first.
       
    if (castState.topBottomType == TOP_BOTTOM_TYPE_TOP)
      sortedBase = castState.base.sortDescending(castState.criterion, false);
    else
      sortedBase = castState.base.sortAscending(castState.criterion, true);
    return sortedBase.interval(1, Math.round(castState.N));
  }
}

Example 10-4 Getting the Source Produced by the Template

import oracle.olapi.data.source.DataProvider;
import oracle.olapi.data.source.Source;
import oracle.olapi.examples.*;
import oracle.olapi.metadata.mdm.MdmAttribute;
import oracle.olapi.metadata.mdm.MdmBaseMeasure;
import oracle.olapi.metadata.mdm.MdmCube;
import oracle.olapi.metadata.mdm.MdmDatabaseSchema;
import oracle.olapi.metadata.mdm.MdmDimensionLevel;
import oracle.olapi.metadata.mdm.MdmDimensionMemberInfo;
import oracle.olapi.metadata.mdm.MdmHierarchyLevel;
import oracle.olapi.metadata.mdm.MdmLevelHierarchy;
import oracle.olapi.metadata.mdm.MdmMetadataProvider;
import oracle.olapi.metadata.mdm.MdmPrimaryDimension;
import oracle.olapi.metadata.mdm.MdmRootSchema;

/**
 * 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 extends BaseExample11g
{
  /**
   * Gets the MdmMetadataProvider, the DataProvider, the MdmRootSchema, and the
   * MdmDatabaseSchema for the current user.
   * Gets the UNITS_CUBE_AWJ MdmCube.
   * From the cube, gets the MdmBaseMeasure objects for the UNITS and SALES
   * measures and the MdmPrimaryDimension objects that dimension them.
   * Gets a hierarchy of the PRODUCT_AWJ dimension and the leaf level of the
   * dimension.
   * Gets the short description attribute of the dimension.
   * Creates a SingleSelectionTemplate and adds selections to it.
   * Creates a TopBottomTemplate and sets the properties of it.
   * 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() throws Exception
  {
    // Get the MdmMetadataProvider from the superclass.
    MdmMetadataProvider metadataProvider = getMdmMetadataProvider();
    // Get the DataProvider from the Context11g object of the superclass.
    DataProvider dp = getContext().getDataProvider();

    // Get the MdmRootSchema and the MdmDatabaseSchema for the user.
    MdmRootSchema mdmRootSchema =
      (MdmRootSchema)metadataProvider.getRootSchema();
    MdmDatabaseSchema mdmDBSchema = 
      mdmRootSchema.getDatabaseSchema(getContext().getUser());

    MdmCube unitsCube =
      (MdmCube)mdmDBSchema.getTopLevelObject("UNITS_CUBE_AWJ");
    MdmBaseMeasure mdmUnits = unitsCube.findOrCreateBaseMeasure("UNITS");
    MdmBaseMeasure mdmSales = unitsCube.findOrCreateBaseMeasure("SALES");

    // Get the Source objects for the measures.
    Source units = mdmUnits.getSource();
    Source sales = mdmSales.getSource();

    // Get the MdmPrimaryDimension objects for the dimensions of the cube. 
    List<MdmPrimaryDimension> cubeDims = unitsCube.getDimensions();
    MdmPrimaryDimension mdmTimeDim = null;
    MdmPrimaryDimension mdmProdDim = null;
    MdmPrimaryDimension mdmCustDim = null;
    MdmPrimaryDimension mdmChanDim = null;
    
    for(MdmPrimaryDimension mdmPrimDim : cubeDims)
    {
      if (mdmPrimDim.getName().startsWith("TIME"))
        mdmTimeDim = mdmPrimDim;
      else if (mdmPrimDim.getName().startsWith("PROD"))
        mdmProdDim = mdmPrimDim;
      else if (mdmPrimDim.getName().startsWith("CUST"))
        mdmCustDim = mdmPrimDim;
      else if (mdmPrimDim.getName().startsWith("CHAN"))
        mdmChanDim = mdmPrimDim;
    }

    // Get the hierarchy of the PRODUCT_AWJ dimension.
    MdmLevelHierarchy mdmProdHier = 
      mdmProdDim.findOrCreateLevelHierarchy("PRODUCT_PRIMARY");

    // Get the detail dimenson level of the PRODUCT_AWJ dimension.
    MdmDimensionLevel mdmItemDimLevel = 
      mdmProdDim.findOrCreateDimensionLevel("ITEM");
    // Get the hierarchy level of the dimension level.
    MdmHierarchyLevel mdmItemHierLevel = 
                       mdmProdHier.findOrCreateHierarchyLevel(mdmItemDimLevel);       
    // Get the Source for the hierarchy level.
    Source itemLevel = mdmItemHierLevel.getSource();

    // Get the short description attribute for the PRODUCT_AWJ dimension and
    // the Source for the attribute.
    MdmAttribute mdmProdShortDescrAttr = 
      mdmProdDim.getShortValueDescriptionAttribute();
    Source prodShortDescrAttr = mdmProdShortDescrAttr.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::YEAR::CY2001");
    MdmDimensionMemberInfo custMemInfo =
      new MdmDimensionMemberInfo(mdmCustDim, "SHIPMENTS::REGION::APAC");
    MdmDimensionMemberInfo chanMemInfo =
      new MdmDimensionMemberInfo(mdmChanDim, "CHANNEL_PRIMARY::CHANNEL::DIR");
 
    // 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 level of a hierarchy.
    TopBottomTemplate topNBottom = new TopBottomTemplate(itemLevel, 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());

    // End of code not shown in 
    //Example 7-3, "Using Child Transaction Objects".

    // Display a description of the result.
    String resultDescription = " products with the most units sold \nfor";
    displayResultDescr(singleSelections, topNBottom, resultDescription);

    // Get the Source produced by the TopBottomTemplate.
    Source result = topNBottom.getSource();

    // Join the Source produced by the TopBottomTemplate with the short
    // value descriptions. Use the joinHidden method so that the
    // dimension member values do not appear in the result.
    Source result = prodShortDescrAttr.joinHidden(topNBottomResult);

    // Commit the current transaction.
    getContext().commit();  // Method of Context11g.

    // Create a Cursor for the result and display the values of the Cursor.
    getContext().displayTopBottomResult(result);

    // Change a dimension member selection of the SingleSelectionTemplate.
    timeMemInfo.setUniqueValue("CALENDAR_YEAR::YEAR::CY2000");
    singleSelections.changeSelection(timeMemInfo);
 
    // Change the number of elements selected and the type of selection.
    topNBottom.setN(5);
    topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_BOTTOM);
 
    // Join the Source produced by the TopBottomTemplate to the short
    // description attribute.
    result = prodShortDescrAttr.joinHidden(topNBottomResult);

    // Commit the current transaction.
    getContext().commit();
 
    // Display a description of the result.
    resultDescription = " products with the fewest units sold \nfor";
    displayResultDescr(singleSelections, topNBottom, resultDescription);
 
    // Create a new Cursor for the Source produced by the TopBottomTemplate
    // and display the Cursor values.
    getContext().displayTopBottomResult(result);

    // Now change the measure to SALES, and get the top and bottom products by
    // SALES.    
    singleSelections.setMeasure(sales);
    // Change the number of elements selected.
    topNBottom.setN(7);
    // Change the type of selection back to the top.
    topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_TOP);

    resultDescription = " products with the highest sales amounts \nfor";
    displayResultDescr(singleSelections, topNBottom, resultDescription);

    topNBottomResult = topNBottom.getSource();
    result = prodShortDescrAttr.joinHidden(topNBottomResult);
    
    // Commit the current transaction.
    getContext().commit();
    getContext().displayTopBottomResult(result);

    // Change the type of selection back to the bottom.
    topNBottom.setTopBottomType(TopBottomTemplate.TOP_BOTTOM_TYPE_BOTTOM);

    resultDescription = " products with the lowest sales amounts \nfor";
    displayResultDescr(singleSelections, topNBottom, resultDescription);

    topNBottomResult = topNBottom.getSource();
    result = prodShortDescrAttr.joinHidden(topNBottomResult);
    
    // Commit the current transaction.
    getContext().commit();
    getContext().displayTopBottomResult(result);

  } 

  /**
   * Displays a description of the results of the query.
   * 
   * @param singleSelections The SingleSelectionsTemplate used by the query.
   * 
   * @param topNBottom The TopBottomTemplate used by the query.
   * 
   * @param resultDescr A String that contains a description of the query.
   */
  private void displayResultDescr(SingleSelectionTemplate singleSelections,
                                  TopBottomTemplate topNBottom,
                                  String resultDescr)
  {
    DataProvider dp = getContext().getDataProvider();
    
    // Get the short descriptions of the dimension members of the  
    // SingleSelectionTemplate.
    StringBuffer shortDescrsForMemberVals =
      singleSelections.getMemberShortDescrs(dp);
    
    // Display the number of dimension members selected, the result description,
    // and the short descriptions of the single selection dimension members.
    println("\nThe " + Math.round(topNBottom.getN()) + resultDescr
            + shortDescrsForMemberVals +" are:\n"); 
  }

  /**
   * 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 Context11g class.
   */
  public static void main(String[] args) 
  {
    new TopBottomTest().execute(args);
  }

}