An Introduction to Working with Analyses in the BI Catalog

You can create analyses in the BI catalog. You can also create analyses in the wizard, but the BI catalog enables you to use advanced features that support complex analyses or specific requirements.

For example, every analysis consists of "views," such as tables and graphs. The BI catalog provides a much greater variety of views than the wizard does. These include specialized items such as view selectors (which enable users to toggle among views in an analysis). For another example, the BI catalog enables you to use SQL statements to define criteria for filters.

To create or edit an analysis in the BI catalog:

Navigate to Tools > Reports and Analytics. Click the Browse Catalog button.
Click New > Analysis. A Select Subject Area list appears; in it, select the subject area from which your analysis is to draw data.

Or, select an existing analysis in the catalog and click Edit.
The analysis opens. Use features available on four tabs to define your analysis.
Click the Save Analysis icon to save an existing analysis at its current location. Or click the Save As icon and, in a Save As dialog, enter a name and select the folder in which to save the analysis.

Here are the basic things you can do in each of the four tabs:

Criteria

Expand folders in your subject area and choose columns to include in the analysis. Drag each column from its folder and drop it in the Selected Columns region. Or, double-click each column.

For any column you select, you may click a menu icon (it looks like a gear) to create filters, to sort the order in which records appear, or to configure column properties. (The topics that follow this one provide examples of filtering and column-property configuration.) You can view and edit filters you've created in a Filters region below the Selected Columns region.

Results

The initial view in any analysis is a table consisting of the columns you selected in the Criteria tab. In the Results tab, you can review it to confirm it returns data you expect. You can also add views, configure the layout of each view, and more.

To add a view to the analysis, click a New View icon and select among a wide variety of formats for tables, graphs, and other items. Each view appears in its own box in a Compound Layout region, with a descriptive title such as "Table" or "Graph."

You can add a graph only if the analysis includes at least one column from a fact folder. Only fact-folder columns contain numeric values, without which there's nothing to base a graph on. If you want to add a graph but are uncertain which type to select, click the Best Visualization option. It automatically selects the type of graph that presents your data most clearly.

You set layout options for each view individually:

Click the Edit View icon (it looks like a pencil) in the title line of a view.
A Layout region opens, with fields representing options suitable for the type of view you're working with. For example, table options are the same as they'd be if you were working with a table in the wizard, so there are Table, Table Prompts, Sections, and Excluded fields.
Within the Layout region, drag a column to a field representing an option, to apply that option to the column. Then click a Done button.

Prompts

A prompt is a drop-down list that enables a user to select among records the analysis is to display. An analysis might, for instance, prompt a user to select records of issues at one or more states. In the Results tab, you can configure similar prompts, but each applies only to an individual view. Each prompt you create in the Prompts tab, however, applies to all views in the analysis.

For a simple example, your analysis might include the State column from the Issue Details folder of the Compliance Real Time subject area. You may want to enable users to view issues at states they choose. In the Prompts tab:

Select New > Column Prompt > State.
In a New Prompt dialog, accept default values: is equal to/is in for Operator, and Choice List for User Input. To ensure that users can select only among actual state values, select Options and clear the Enable user to type values check box.
Then check your work: Click the Preview icon. A preview window presents a drop-down list; in it, you can select any combination of states and then click an OK button. The preview window then displays the analysis, filtered so that only records at prompted-for states appear.

Advanced

Review or modify the XML code and logical SQL statement that the analysis generates.

In particular, you can add a "prefix" that improves the performance of your analysis. Although the performance boost is more noticeable in some analyses than in others, this addition causes any analysis to generate results faster.

In the Advanced tab, scroll down to the Advanced SQL Clauses section.

Copy the following statement and paste it into the Prefix field:

SET VARIABLE OBIS_ORACLEDB_HINTS_FOR_TOP_SELECT='OPT_PARAM
      ("_complex_view_merging","false")
      OPT_PARAM("_optimizer_skip_scan_enabled","false")
      OPT_PARAM("_push_join_predicate","false")
      OPT_PARAM("_optimizer_push_pred_cost_based","false")';

Click the Apply SQL button.