# Oracle Business Intelligence Beans Sample

## Using QueryBuilder

### Overview

The Using QueryBuilder Capabilities sample application demonstrates how to define and modify queries. It shows how to programmatically make changes to an existing query as well as how to use QueryBuilder to change a query in an interactive manner. BIQuery connects to Oracle9i OLAP (the source of the business data for the application) and to the BI Catalog (where object definitions, like crosstabs and graphs, are saved).

The BIQuery class extends BIFrame. BIQuery provides a menu with access to a simple connection dialog which prompts a user for security credentials. BIQuery then makes the two connections described above. After the connection is established, a new crosstab can be created using the "New Crosstab" file menu option.

After a Crosstab is created, the user can perform one of the following functions:

- Modify the query by invoking QueryBuilder. The user can invoke QueryBuilder by using the "Modify query" menu item. Within the QueryBuilder, the user can select the measures to include in the query, alter the layout of the crosstab, and change the selections for the Channel and Time dimensions.

- Insert a Favorite. To explore this feature, right-click the Crosstab and choose the first option of the popup menu. A dialog appears to allow the user to replace the Geography selection with a predefined selection in the BI Beans Catalog.

- Insert members. To explore this feature, right-click the Crosstab and choose the second option of the popup menu. A dialog appears to allow the user to replace the Product selection with any combination of Product members.

###

### Code Highlights

There are a few areas of interest in the code which may be particularly useful for developers. The code below is taken from the `createCrosstab` method and demonstrates how to limit the universe for a particular dimension. Specifically, this code limits the universe on the Time dimension to the descendants of 2000:

`Selection selection = m_query.findSelection(m_strTimeDimension);
Vector base = new Vector();
base.addElement("1804"); // the id for 2000
FamilyStep fs = new FamilyStep(m_strTimeDimension, m_strTimeHierarchy, FamilyStep.OP_DESCENDANTS, base, true);
selection.removeAllSteps();
selection.addStep(fs);
try {
m_query.setUniverseSelection(m_strTimeDimension, selection);
}
catch (Exception e) {
showExceptionDialog(this, e);
}
`

The `initQueryBuilder` method shows how to create and customize a QueryBuilder. This method demonstrates how to choose what panels are displayed, choose what tabs on the panels are active, and control access to specific dimensions. Many of the customizations actually correspond to the default behavior, but are included for illustration purposes. The code below demonstrates how to limit the dimensions that the user has access to in the QueryBuilder. Since the Geography and Product dimensions could be modified using the popup menus in this sample, the QueryBuilder is limited to modifying only the Channel and Time dimensions as follows:

`String[] strDimensions = new String[2];
strDimensions[0] = m_strChannelDimension;
strDimensions[1] = m_strTimeDimension;
m_queryBuilder.setVisibleDimensions(strDimensions);
`

The private class `FavoritesDialog` and the method insertGeographyFavorite show how a QueryBuilder panel can be used outside of QueryBuilder and how to incorporate a Favorite selection into the query. The method `insertProductMembers` demonstrates how to use a DimensionListDialog to select members that can be directly applied to the query.

The following code is taken from `insertGeographyFavorite` and shows how to replace the selection on a dimension with an arbitrary selection step. This code is also used in `insertProductMembers` with Product replacing Geography as the dimension:

`try {
step.setAction(Step.SELECT);
}
catch (InvalidStepArgException e) {
showExceptionDialog(this, e);
}
Selection selection = m_query.findSelection(m_strGeographyDimension);
selection.removeAllSteps();
selection.addStep(step);
try {
m_query.applySelection(selection);
}
catch (Exception e) {
showExceptionDialog(this, e);
}`

Copyright © 2004 Oracle Corporation. All Rights Reserved.