The demonstration requires a script-enabled browser.

# Oracle Business Intelligence Beans Sample: Ad-Hoc Analysis Tool

## Contents

Overview

Demonstration

Components of the sample application

- Desktop
- Sidebar
- Menu bar
- Toolbar
- Worksheet

Key concepts

- Connecting to the OLAP Catalog and to the BI Beans Catalog
- Opening and saving presentations
- Updating queries
- Extending the QueryBuilder
- Extending the graph toolbar
- Displaying views in a tree
- Displaying saved selections in a tree
- Displaying dimension members

Source Code Documentation

Setup requirements

- Getting Started
- Running the application using a batch file
- Installing the JDeveloper project

## Overview

This sample is a Java-client application that lets users analyze data using Oracle's powerful OLAP engine. It allows users to create new presentations, customize them, and save them. It incorporates code from several other BI Beans Sample applications, integrating them into a single, complete application. You can use this application as a starting point for the development of your own custom application or simply to learn how to integrate several BI Beans features.

Users can:

- Connect to any Oracle9i Release 2 Database (see BI Beans documentation for patch set details)
- Create new data presentations or open existing ones
- Display multiple views simultaneously
- Drill, pivot, and change the data page to analyze data in views
- Change the view type, from a crosstab to a graph, or from a graph to a crosstab
- Format presentations through a toolbar or in dialogs
- Use the QueryBuilder and the CalcBuilder to update data in views
- Save presentations
- Export and print presentations

This sample application demonstrates how to:

- Connect to the OLAP Catalog and to the BI Beans Catalog (including the Discoverer Catalog)
- Store and retrieve objects in the BI Beans Catalog
- Extend the QueryBuilder to create a New Presentation wizard
- Extend the Graph toolbar to create an application toolbar
- Create dialogs that incorporate BI Beans user-interface components
- Define and apply new calculations, using the CalcBuilder
- Apply saved selections to a query programmatically
- Use an `OLAPDimensionListPanel` (a dimension list) to update selections
- Implement drag-and-drop and multiple-document-interface (MDI) support

Note: This sample is not a supported, packaged product. You will notice, for example, that the sample uses hard-coded strings for messages and labels. A product would call out to resource bundles to get localizable strings. Also, this sample does not include Help topics, and it has not been subjected to the rigorous testing that a product requires.

## Demonstration

This demonstration shows how to analyze data using the application. It steps through the key capabilities of the sample, including: creating and formatting data presentations, defining advanced multidimensional queries, navigating through the data, and much more.

Run the demonstration

## Components of the sample application

The following image shows the sample application displaying two worksheets: a crosstab reporting at a high-level sales growth across the world, and a bar graph that reflects how the top 5 products are selling. Click on a component in the picture to read more about the component. The remainder of this section has a heading for each component.

### Desktop

The desktop is where open worksheets appear, and where users can interact with a presentation. The desktop and the sidebar are contained in a `JSplitPane`, so users can drag a splitter to change amount of space for each component.

### Sidebar

The Sidebar is panel that contains three Explorers, as tabs:

- `CatalogExplorer` (Worksheets Tab) -- Displays crosstabs and graphs that are in the BI Beans Catalog. The `CatalogExplorer` uses several other classes to display a hierarchical tree of objects. Users can drag objects from the `CatalogExplorer` to the desktop to open a worksheet. They can also double-click an object to open it.

  The code in the `CatalogExplorer` shows how to search the BI Beans Catalog and filter the results, in order to list only the crosstabs, graphs, and folders that are in the Catalog. You can also see how to set up a tree in this class, and how to respond to drag gestures.

- `FavoritesExplorer` (Saved Selections tab) -- Displays saved selections that are in the BI Beans Catalog. There are three ways a user can change a dimension selection in a worksheet using the Saved Selections tab:

  1. Drag a selection to any opened worksheet
  2. Double-click a selection to update the active worksheet
  3. Click the Go button to update the active worksheet
  The code in the `FavoritesExplorer` shows how to search the BI Beans Catalog to list only saved selections. It also shows you how to apply a saved selection to the existing selection for a dimension.
- `DimensionExplorer` (Members tab) -- Displays members of a dimension. There are three ways a user can change a dimension selection in a worksheet using the Members tab:
  1. Drag a dimension member to any opened worksheet
  2. Double-click a dimension member to update the active worksheet
  3. Highlight dimension members and click the Go button to update the active worksheet

  The code in the `DimensionExplorer` shows how to use an `OLAPDimensionPanel` to allow users to select dimension members. It also shows you how to apply the member selection to an existing selection.

### Menu bar

The menu bar contains menus that allow users to open and save worksheets, change the view type, insert calculations, format graphs, and so on. The menu bar is set up in the main Analyzer class (`Analyzer.java`).

### Toolbar

The toolbar provides quick access to common tasks, such as formatting graphs and crosstabs, editing queries, saving worksheets, and so on. The toolbar is a `GraphToolbar` that has extra tools added to it.

### Worksheet

A worksheet contains either a crosstab or a graph. The desktop can have more than one open worksheet at a time. One worksheet is the active worksheet.

## Key Concepts

This sample application demonstrates how to accomplish a number of programming tasks. The subsections in this section point you to code that accomplishes these tasks.

### Connecting to the OLAP Catalog and to the BI Beans Catalog

The OLAP Catalog contains metadata that describes the data that can be analyzed through the use of Oracle OLAP. The metadata is necessary for accessing data, from an analytic workspace or from a data warehouse, that appears in a BI Beans presentation. The BI Beans Catalog stores XML definitions of BI Beans objects such as crosstabs, graphs, calculations, and saved selections. The Discoverer Catalog is an instance of the BI Beans Catalog and is used for OracleAS Discoverer. The sample application also can connect to the Discoverer Catalog. In this sample application, the `AnalyzerConnection` class encapsulates the connection to the OLAP Catalog and to the BI Beans Catalog. The class makes the following assumptions:

- The BI Beans Catalog is installed in the same database as the OLAP Catalog.

- The user is an RDBMS user.

The `AnalyzerConnection` presents a dialog, in which the user enters connection information. (`AnalyzerConnection` saves this information in a configuration file and uses the information in the file for subsequent sessions.) The `AnalyzerConnection` uses this information to establish an OLAP connection and a BI Beans Catalog connection (a persistence connection).

If the user is a valid RDBMS user but is not already a valid BI Beans Catalog user, then the class automatically adds the user to the BI Beans Catalog. There is one exception. If the user is connecting to a Discoverer Catalog, then the user must be a authorized Discoverer user. See the Discoverer documentation on how to authorize RDBMS users with EM.

The `AnalyzerConnection` also creates the `MetadataManager` and the `QueryManager` that the application needs. The `MetadataManager` is responsible for creating an integrated view of your business data. It merges the definitions from the OLAP Catalog and the BI Beans Catalog.

The following table describes important connection tasks and identifies the method that accomplishes each task.

Task | Method
--- | ---
Present a dialog for collecting user connection information | `AnalyzerConnection.showConnectDialog`
Add a user to the BI Beans Catalog (uses a JDBC connection) | `AnalyzerConnection.recoverUser`
Connect to the OLAP Catalog | `AnalyzerConnection.getOLAPConnection` (called from the `connect` method)
Connect to the BI Beans Catalog | `AnalyzerConnection.getCatalogConnection` (called from the `connect` method for database catalogs) `AnalyzerConnection.getLocalCatalogConnection` (called from the `connect` method for file-based catalogs)
Create a `BISession` | `AnalyzerConnection.connect`
Create the `MetadataManager` and the `QueryManager` | `AnalyzerConnection.connect`

### Opening and saving presentations

The sample application allows users to save presentations in the BI Beans Catalog and to open them from the Catalog. The `AnalyzerUtilities` class has code for displaying the `PersistenceObjectChooser` as an Open dialog and as a Save As dialog. It uses a filter to make the `PersistenceObjectChooser` display only crosstabs and graphs.

Because a view aggregates a `Query` object that defines the data that the view displays, opening a view requires a `MetadataManager` and a `QueryManager`. An inner class, `AnalyzerUtilities.OpenViewWorker`, retrieves the view from the Catalog. Because opening a view might take a bit of time, this operation runs on a separate thread.

The following table describes the primary tasks involved in opening and saving views, along with the methods that accomplish each task.

Task | Method
--- | ---
Present an Open dialog that displays only crosstab and graph object types | `AnalyzerUtilities.showOpenDialog`
Open the selected view | `AnalyzerUtilities.OpenViewWorker.construct` (called from `Analyzer.openView`, which instantiates an `OpenViewWorker` to open the view in a separate thread; if you extend the sample and want to open a view, then call `Analyzer.openView` rather than the `construct` method.)
Put the newly opened view in a worksheet and make it the active worksheet | `Analyzer.addWorksheet` (called from `AnalyzerUtilities.OpenViewWorker.finished`, which is called after the `OpenViewWorker` opens the view; `finished` closes the separate thread.)
Update the `DimensionExplorer` to include all the dimensions in the query of the newly opened view | `DimensionExplorer.addDimensions` (called from `Analyzer.addWorksheet`)
Update menus and toolbars to reflect the fact that the worksheet is active | `Analyzer.enableMenus`
Present a Save As dialog that displays only crosstab and graph object types | `AnalyzerUtilities.showSaveAsDialog`
Save the view under the specified name and location | `AnalyzerUtilities.save`

### Updating Queries

A BI Beans `Query` specifies the data that a view displays. Each `Query` has one or more measures (such as Sales or Quota), and each measure has dimensions (such as Product, Geography, and Time). For each dimension, a `Selection` object specifies the members (such as New York, Paris, and Tokyo) and/or the conditions used to retrieve the members (such as "Top 5 Cities based on Sales") for a given `Query`.

The BI Beans QueryBuilder allows users to edit all aspects of a query. The CalcBuilder allows users to create new measures that are calculations of existing measures. The sample application incorporates these builders.

The sample application also allows users to edit queries by dragging saved selections or dimension members to a view, from Explorers in the Sidebar. The `FavoritesExplorer` object displays the saved selections, getting a list of them from the BI Beans Catalog. The `DimensionExplorer` displays the members of a dimension and allows users to select members and drag them to a view to update the selection. Both Explorers actually update the query in response to the drag operation.

The following table describes the primary tasks involved in updating queries, along with the methods that accomplish each task.

Task | Method
--- | ---
Display and run the QueryBuilder | `AnalyzerUtilities.runQueryBuilder`
Display and run the CalcBuilder | `AnalyzerUtilities.runCalcBuilder`
Add the new measure from the CalcBuilder to an existing query | `AnalyzerUtilities.updateMeasures` (called from `AnalyzerUtilities.runCalcBuilder`)
Update a selection by applying a saved selection to it | `FavoritesExplorer.applySelection`
Update a selection by applying selected members to it | `DimensionExplorer.applyDimListSelection`

### Extending the QueryBuilder

The sample application includes a New Presentation wizard, which allows users to create new crosstabs and graphs. To create this wizard, the application extends the QueryBuilder. The application adds a page at the beginning, where users select the type of view to create. If the user chooses to create a graph, then the application adds a page for specifying a graph type.

The following table describes the primary tasks involved in displaying a new presentation wizard, along with the methods that accomplish each task.

Task | Method
--- | ---
Add a view type page to the QueryBuilder | `NewPresentationWizard.run`
Conditionally add a Graph Type page to the QueryBuilder | `NewPresentationWizard.wizardSelectionChanged`
Instantiate a graph and set the query on it | `NewPresentationWizard.wizardSelectionChanged`
Instantiate a crosstab and set the query on it | `NewPresentationWizard.wizardSelectionChanged`
Put the new view in a new worksheet | `Analyzer.newPresentation` (called from `Analyzer.addWorksheet`; `newPresentation` also runs the `NewPresentationWizard`)

### Extending the graph toolbar

To provide a toolbar, the sample application extends the `GraphToolbar` object, adding other tools to it. The `Analyzer.jbInit` method accomplishes this task. To find the code where it creates tools and the toolbar, search for the comment "Update the toolbar with a few features" in the `Analyzer` class.

### Displaying views in a tree

The Worksheet tab in the Sidebar displays a tree of folders, graphs, and crosstabs from the BI Beans Catalog. The `CatalogExplorer` class is the class for this tab. Several other classes support the use of a `JTree` to display Catalog items: `CatalogTreeModel`, `CatalogTreeNode`, `CatalogTreeCellRenderer`, and `BIPresentationFilter`.

The `CatalogExplorer.populateTree` method shows how to set up a `JTree` to display folders, crosstabs, and graphs from the BI Beans Catalog. Note that `m_filter` in that method is a `BIPresentationFilter`.

### Displaying saved selections in a tree

The FavoritesExplorer class is the class for the Saved Selections tab in the Sidebar. Like the `CatalogExplorer`, the `FavoritesExplorer` searches for objects in the BI Beans Catalog. Unlike the `CatalogExplorer`, however, the `FavoritesExplorer` searches only for saved selections. The `FavoritesExplorer` extends the `CatalogExplorer`.

Note that the two Explorers use different mechanisms to filter the results from the Catalog search. The `FavoritesExplorer` specifies the object type in a `BasicAttributes` object, which it passes to the search method. The `CatalogExplorer` uses a `BIPresentationFilter` to filter the search results after the fact. The `BasicAttributes` technique is more efficient, but it can only be used when you want to search for only a single object type. When you need to search for two or more object types to display in a pane, you need to use a `BIFilter`.

The following table describes the primary tasks involved in displaying saved selections in a tabbed pane, along with the methods that accomplish each task.

Task | Method
--- | ---
Search the Catalog for saved selections | `FavoritesExplorer` constructor
Apply a saved selection to the active view | `FavoritesExplorer.applySelection`

### Displaying dimension members

The `DimensionExplorer` is the class for the Members tab in the Sidebar. The `DimensionExplorer` uses an `OLAPDimensionListPanel` to display the members of a single dimension. The user chooses the dimension from a drop-down that lists all the dimensions that are in any open view. The `DimensionExplorer` maintains a vector of objects that contain information about each dimension.

Users can select members in the `DimensionExplorer` and drag them to the active view. A drop-down in the `DimensionExplorer` specifies whether the selected members are added to the existing selection, removed from it, kept in it, or replaces the current selection.

Dimension list tasks

The following table describes the primary tasks involved in using a dimension list to display dimension members, along with the methods that accomplish each task.

Task | Method
--- | ---
Add dimensions from a new or newly opened view | `DimensionExplorer.addDimensions`
Instantiate and populate a new `OLAPDimensionListPanel` | `DimensionExplorer.getDimList`
Apply the selected members to a selection | `DimensionExplorer.applyDimListSelection`

Tracking the list of dimensions in use

The following table describes the primary tasks involved in maintaining a vector of dimensions that are displayed in open worksheets, along with the methods that accomplish each task. In general, you can look for the `m_dimensions` member in the `DimensionExplorer`.

Task | Method
--- | ---
Add dimensions from a new or newly opened view, if necessary; increment the usage count if the dimension is already in the vector | `DimensionExplorer.addDimensions`
Remove dimensions from the list, or decrement the usage counts, when a view is closed | `DimensionExplorer.removeDimensions`
Get the index for a dimension | `DimensionExplorer.getDimensionIndex`
Find out if a dimension is in the vector | `DimensionExplorer.inDimensionList`

## Source Code Documentation

Source code documentation (javadoc) has been provided to help you understand the components of the application.

## Setup Requirements

The sample is packaged for both running and modifying the application.

- Getting started
- Deploying and running the application using Java Web Start
- Running the application using a batch file
- Installing the JDeveloper project

### Getting Started

The source code, compiled jar file (`adhoc_tool.jar`), and source code documentation are available in the `<BI_ORACLE_HOME>/bi/samples/adhoc_tool` directory.

The following table describes the directories that are now available:

Directory | Contents
--- | ---
`\` | This overview file and the project file that is used in JDeveloper
`\src` | The Java source code for the application
`\demo` | Self-running demonstration of the application
`\deploy` | The compiled jar file and batch file
`\doc` | API documentation for the application
`\classes` | The compiled classes for the application. These are packaged in the `adhoc_tool.jar` file found in the \deploy directory

You are now ready to continue.

### Running the application using a batch file:

You can also run the application on a stand-alone client using a batch file. To run the sample application using a batch file, perform the following tasks:

- If you haven't installed a Java runtime environment (J2SE 1.4), you can download it from the Sun web site.
- Using a text editor, update the adhoc_tool.bat file. Set the `JAVA_HOME` environment variable to the location of your J2SE 1.4 home.
- Run the adhoc_tool.bat file.

### Installing the JDeveloper project:

- Create a new Workspace in JDeveloper.
- Open the `adhoc_tool.jpr` project in the Workspace.

Copyright © 2004 Oracle Corporation. All Rights Reserved.