Specifying the Criteria for Analyses

This procedure is a step in the process for constructing an analysis. For more information, see "What Is the Process for Constructing Analyses?"

You use the "Analysis editor: Criteria tab" to specify the criteria for an analysis, including columns, filters, and selection steps. You also can specify:

The default order in which columns are to be displayed in the analysis results
The default sorting for the values that are displayed in views
The default formatting (such as headings, number of decimal places, conditional formats, and systemwide defaults)
Column formulas (such as adding a Rank or Percentile function)
The aggregation rules for column totals
The sort order for subject area folders and folder objects within the Subject Areas pane

To specify the criteria for an analysis:

In the Criteria tab, select the columns to be included in the analysis by doing one of the following:
- Double-clicking the columns in the "Subject Areas pane."
- Dragging and dropping the columns from the Subject Areas pane to the "Selected Columns pane."
  
  To select multiple non-contiguous columns, press and hold the Ctrl key, then click each column to include.
- Dragging and dropping saved columns from the "Catalog pane."
The selected columns are displayed in a box in the Selected Columns pane. Each column box has two sections. The upper section shows the name of the folder that contains the column, for example, Customers. The lower section shows the name of the column, for example, Customer Name.

If you want to:
- Initiate a search within the subject areas that are displayed in the Subject Areas pane, click the Search toolbar button.
- Change the sort order of the subject area folders and objects within each folder, click the Sort Subject Area toolbar button. Clicking the button executes the sort and closes all subject area folders except the primary subject area. See "Changing the Sort Order of Subject Areas and Subject Area Folders" for additional information.
- Add or remove related subject areas from which to select columns, click the Add/Remove Subject Areas toolbar button in the Subject Areas pane to display the "Add/Remove Subject Areas dialog."
  
  If you add a related subject area but do not add any columns from that subject area to the analysis, then the subject area is not related to the analysis after you close and re-open the analysis.
- Refresh the content in the Subject Areas pane, click the Refresh toolbar button on the Subject Areas pane or click the arrow beside the button.
  
  Clicking the button executes the default behavior of Refresh Display. Clicking the arrow enables you to select Refresh Display or Reload Server Metadata to refresh the subject area's metadata.
  
  For more information about these options, see the "Subject Areas pane."
Modify the columns or levels of hierarchical columns as needed using the Selected Columns pane:
- Click the Options button to the right of a column name in the Selected Columns pane to display options to:
  - Specify the sort order for columns. (You cannot specify the sort order for hierarchy levels.)
    
    For more information, see "Sorting Data in Views."
  - Edit formulas for attribute columns and measure columns, including customizing headings, and specifying the aggregation rule. (You cannot customize headings, specify the aggregation rule, or edit the formulas for hierarchical columns or for hierarchy levels.)
    
    For more information, see "Editing the Formula for a Column."
  - Edit column properties to control the formatting and interaction of columns and hierarchy levels.
    
    For more information on applying formatting, see "Applying Formatting to a Column."
  - Add filters for attribute columns and measure columns. (You cannot add filters for hierarchical columns or hierarchy levels.) For more information, see "Creating Column Filters."
  - Delete the columns from the analysis. (You cannot delete hierarchy levels.)
- Click the Remove all columns from criteria toolbar button in the Selected Columns pane to remove all columns from the analysis.
- Click the Combine results based on union, intersection, and difference operations toolbar button in the Selected Columns pane to combine the results of two or more analyses into a single result. For more information, see "Combining Columns Using Set Operations."
- Use the Drag Column buttons in the Selected Columns pane to place the columns in the default order for display in the analysis results.
Add and edit inline filters as needed using the "Filters pane."

For more information, see "Creating Column Filters."
Create or edit selection steps as needed using the "Selection Steps pane."

For more information, see "Working with Selections of Data."
Add named filters, calculated items, and groups from the Oracle BI Presentation Catalog as needed using the "Catalog pane."
Use the buttons on the toolbar for the Criteria tab, as needed, to show or hide the Filters pane, to show or hide the Selections Steps pane, and to edit the properties of the analysis, such as the type of message (default or custom) to be displayed if no results are available.

You can now add views to the analysis. See "Displaying the Results of Analyses."

Notes

In general, an analysis should contain at least one measure column. Measure columns are the key additive measurements of business performance, such as dollar sales per store, or the number of service requests opened and closed each day. An analysis without any measure columns generally is not meaningful, and can cause poor query performance or unexpected results. If you want to build an analysis without any measure columns, then first consult the administrator.
Generally, you should avoid including both a hierarchical column and an attribute column that is also a lower level in the hierarchical column in the same analysis, unless you have selection steps that limit the data in the analysis to only the same level as the attribute column. If you do so, the data for all levels above the level that is the same as the attribute column will not be correct.

For example, suppose you have a hierarchical column named Merchandise Hierarchy that has these levels:
```
Total Product
   Brand
      LOB
         Product Type
            Product
```
And you have another attribute column named Product Name, which is the same level as the Product level. If you include both Merchandise Hierarchy and the Product Name attribute column in the same analysis, the data above the Product level will not be correct.
If you add a column from the Subject Areas pane to the Selected Columns pane after displaying the analysis results, then the column is either included (that is, displayed in the view) or excluded from existing views, depending on the setting of the Display of Columns Added in Criteria tab option in the "Analysis Properties dialog: Data tab":
- The column is included in existing views as well as in any new views that you add, if the Display in existing and new views option is selected for the Display of Columns Added in the Criteria tab option.
- The column is excluded from existing views (that is, it is placed in the Excluded drop target of the Layout pane) but included in any new views that you add, if the Exclude from existing views, but display in new views option is selected for the Display of Columns Added in the Criteria tab option.
  
  For more information on the Excluded drop target, see "Understanding Drop Targets."

Understanding Null Suppression

By default, null measure values are suppressed for all analyses. As a content designer sometimes you may want to include null values in an analysis to contribute to the overall understanding of the data. You can set null values to display for the data at both analysis and view levels by selecting the Include Null Values option.

The following applies to an analysis or a view when the Include Null Values option is selected:

Column members display.
When drilling a column on the edge (one with null suppression turned off), null suppression is turned off for all columns on the edge.
Member selection and conditions are applied to the edge and are filtered first. Note that nulls may not display due to the applied condition.
Calculated items, groups, totals, grand totals, showing data as a percentage, and server running aggregates, such as a running sum, are not affected by the Include Null Values setting.

Note:

Oracle BI Presentation Services sorts null values based on the NULL_VALUES_SORT_FIRST setting located in the NQSConfig.INI file.

Note:

Null values will be suppressed in a table view if you have a criteria filter set on a measure.

You cannot turn off null suppression for the following:

Direct Database Requests. See "Working with Direct Database Requests" for additional information.
Analyses that have been combined. See "Combining Columns Using Set Operations" for additional information.
Hand-coded logical SQL analyses or any analysis that has been converted by using the Advanced Logical SQL settings. See "Examining the Logical SQL Statements for Analyses" for additional information.

To turn off null suppression at the analysis level:

In the Criteria or Results tab, click the Analysis Properties button (see "Analysis Properties dialog" for additional information.)
Select the Include Null Values option to turn off null suppression for all views. Note that null suppression can be overridden at the view level for funnels, gauges, graphs, tables, pivot tables, and trellises.

To turn off null suppression at the view level for funnels, gauges, graphs, tables, pivot tables, and trellises:

Display the Results tab for the specific view. See "Editing Views" for additional information.
Click the Properties button.
Select the appropriate Include Null Values options for the view. For example, if you want to turn off null suppression for both rows and columns in a pivot table (see "Null Values" for additional information), select the Include rows with only Null values and Include columns with only Null values check boxes. This setting displays corresponding dimensions that have data, as well as null values. Note that if the view contains prompts or section edges, they also inherit the null suppression value from either the row or column edge.

Note:

Turning null suppression off may increase the volume of the data returned and impact performance. Contact your administrator for additional information.

If results for analyses that include null values are not as you expect, ensure that data in your sources is consistent. See "Ensuring That Data in Multiple Sources Is Consistent" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Editionfor more information.

Example 1: Adding a Filter to a Row Edge on a Measure When Null Values Are Included

Suppose that you have a pivot table with Year on the row edge and Include Null Values selected in the Pivot Table Properties dialog (that is, nulls are not suppressed). See "Null Values" for additional information.

Figure 2-3 shows an example of T05 Per Name Year on the row edge with null values not suppressed.

Figure 2-3 Pivot Table with "Include Null Values" Set on the Row Edge

This image is described in the surrounding text.

Figure 2-4 shows an example of T05 Per Name Year on the row edge with null values not suppressed and a Criteria filter set to 2008 and 2009. When the Criteria filter is set for the attribute column, the filter is applied and the corresponding rows are removed from the analysis.

Figure 2-4 Pivot Table with "Include Null Values" Set on the Row Edge and Criteria Filter Set on an Attribute Column

Example 2: Adding a Filter to a Row Edge on a Measure When Null Values Are Included

Suppose that you have the same pivot table with Year on the row edge and null values selected in the Pivot Table Properties dialog (the same criteria as indicated in Figure 2-3). This time however, you set the filter on the measure, 1 - Revenue, where the value is "greater than 15,000,000." (See "Null Values" for additional information.)

Figure 2-5 shows an example of the results. Oracle BI Server returns all years (regardless of the measure filter) for unsuppressed edge queries. Because the measure value for T05 Per Name Year is equal to 15,000,000, the value for the row is filtered out, but the row still displays.

Figure 2-5 Pivot Table with "Include Null Values" Set on the Row Edge and Criteria Filter Set on a Measure

Example 3: Adding a "Not Null" Filter to a Row Edge on a Measure When Null Values Are Included

Suppose that you have the same pivot table with Year on the row edge and Include Null Values selected in the Pivot Table Properties dialog (the same criteria as indicated in Figure 2-3). This time however, you set the filter on the measure, 1 - Revenue, to not null. See "New Filter dialog" for additional information.

Figure 2-6 shows an example of the results. Oracle BI Server returns all values and honors the Include Null Values option for the member. Therefore, all rows with null values are displayed.

Figure 2-6 Pivot Table with "Include Null Values" Set on the Row Edge and a Measure Filter Set to "Not Null"

Editing the Formula for a Column

You can edit the formulas for attribute columns and measure columns when you specify the criteria for an analysis. This editing affects the column only in the context of the analysis and does not modify the formula of the original column in the subject area. You can also customize table and column headings and specify the aggregation rule for column totals. (This functionality is not available for hierarchical columns.)

A column formula specifies what the column values represent. In its most basic form, such as "Base Facts"."1 - Revenue", a column takes the data from the data source as is. You can edit the formula to add functions, conditional expressions, and so on. This editing enables you to present analysis results in a variety of ways. For example, suppose that you want to perform what-if analysis and show what the revenue might be by product if you increased revenue by 10%. You can see this increase by changing the formula for the 1 - Revenue column to show the revenue increased by 10%. Figure 2-7 shows an analysis in a pivot table that includes the 1 - Revenue column (which shows the revenue in the data source) and the 1 - Revenue Increased by 10% column, where the formula for the 1 - Revenue column was edited to calculate revenue increased by 10%.

Figure 2-7 Analysis Showing Revenue and Revenue Increased by 10%

To edit the formula of a column:

In the "Selected Columns pane," click the Options button beside the column whose formula you want to edit and select Edit Formula. The "Edit Column Formula dialog" is displayed.
Use the "Edit Column Formula dialog: Column Formula tab" to perform various tasks such as creating customized headers and creating or editing the formula for the column. You can build a simple mathematical formula using operator and character buttons, such as "Base Facts"."1 - Revenue"*1.10.
Optionally, use the "Edit Column Formula dialog: Bins tab" to combine values for the column into sets.
Click OK.

The column formula is saved with the analysis with which it is used.

Combining Columns Using Set Operations

After you have selected a subject area for an analysis, you can combine columns from one or more subject areas using Set operations such as Union or Intersect. By combining columns, you create a column for displaying the data in a different way.

Guidelines for Selecting Columns to Combine

When selecting columns to combine, keep the following guidelines in mind:

The number and data types of the columns to combine must be the same. The number of rows that are returned for each column can differ.
You can select columns from the same subject area or from a different subject area, but the columns must have some commonality.
You can specify one Set operation for one collection of criteria. For example, if you create criteria from the A-Sample Sales subject area, you can apply only one Set operation to those columns. You cannot apply different Set operations to different columns in the collection of criteria.
You cannot use hierarchical columns, selection steps, or groups when you combine criteria.

Difference Between Combining Columns Using Set Operations and Adding Columns from Related Subject Areas

Combining columns using Set operations produces different results than adding columns from related subject areas:

When you combine columns using Set operations, the analysis results show a single newly combined column governed by a Set operation. For example, see "Example: Combining Columns from One Subject Area."
When you add columns from related subject areas to an analysis, the results show each added column individually. For example, if you have the appropriate permissions, then you can create an analysis by selecting one column from a primary subject area and selecting another column from a related subject area.

Figure 2-8 shows a table with columns from related subject areas. The 1 - Revenue measure is stored in the A-Sample Sales subject area and the 8 - Quota Amount measure is stored in the B-Sample Quotas Subject Area.

Figure 2-8 Measure Columns from Related Subject Areas

For information, see "What Are Subject Areas and Columns?"

Example: Combining Columns from One Subject Area

The A-Sample Sales subject area contains the Offices folder, which contains the D1 Office and D2 Department columns. You can combine these two columns and create a column called Offices & Departments. You can include the Union All Set operation to specify that this new column shows all the values from both columns in a single column in a table.

Combining Columns from One or More Subject Areas

The following procedure describes how to combine columns from one or more subject areas. Some steps include references to the example of creating the Offices & Departments column with the A-Sample Sales subject area.

Note:

Data formatting that has been saved as the systemwide default for a column is not reflected in a combined column. If you want the data formatting in the combined column, then you must reapply it to the combined column. For more information on data formatting, see "Column Properties dialog: Data Format tab."

To combine columns from one or more subject areas:

In the Analysis editor, create an empty analysis that uses a subject area such as A-Sample Sales.
In the Criteria tab, select the columns to include in the analysis. For example, select D1 Office from the Offices folder and 1 - Revenue from the Base Facts folder.
In the "Selected Columns pane," click the Combine results based on union, intersection, and difference operations toolbar button to display the Select Subject Area menu.
Select a subject area that contains the columns to combine with the columns that you have previously included. For example, click A-Sample Sales.

The Set Operations area is displayed in the Selected Columns pane. Note the boxes with dotted line borders that are displayed below the criteria. These boxes indicate the kind of column that you must combine with those that you have previously included. For example, the boxes might include "Add Column (D1 Office)" and "Add Column (1 - Revenue)". This text indicates that the columns that you include in each of those boxes is combined with the previously selected D1 Office and 1 - Revenue columns using a Set operation to form a new column. When you combine measure columns, no arithmetic operations are performed.
In the Subject Areas pane, select the columns to combine with the originally selected columns. For example, from the Offices folder, select D2 Department and from the Base Facts folder, select 1 - Revenue.

Note that the boxes that previously had dotted line borders now hold the columns that you have just selected. You have now specified the columns to combine.
Click the Union button under the Result Columns link. Select the operation type to use for combining the columns. For example, select the Union All type.

The result columns are those that are displayed in views after applying the set operation of the derived columns.
Click the Result Columns link. Note that the Selected Columns pane is updated to show the newly combined columns that you have just created. You can work with these columns as you do other columns in this pane. For example, you can rename the first column (that is the single newly combined column) by following these steps:
1. Click the Options button for the D1 Office column.
2. Select Column Properties.
3. Select Column Format.
4. Ensure that Custom Headings is selected.
5. In the Column Heading box, enter Offices & Departments.
6. Click OK.
Click the Results tab to view the columns in a table in the "Compound Layout."

Figure 2-9 shows how the combined Offices & Departments column is displayed in a table.

Figure 2-9 Combined Column Displayed in a Table

Viewing Metadata Information from the Subject Areas Pane

You can view metadata information for subject areas, folders, columns, and hierarchy levels. This is helpful, for example, if you need a full description of one of these objects when creating an analysis.

Metadata information is contained in a metadata dictionary. A metadata dictionary is a static set of XML documents. Each XML document describes a metadata object, such as a column, including its properties and relationships with other metadata objects.

Note:

In order for you to view metadata information, the administrator must have performed various configuration tasks, as described in "Providing Access to Metadata Dictionary Information" in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To view metadata information:

In the Subject Areas pane in the Criteria tab or the Results tab, select the subject area, folder, column, or hierarchy level for which you want to view metadata information.
Click View Metadata Dictionary on the toolbar.

The metadata dictionary information for the object is displayed either in a new browser window or in a tab, depending on the browser setting.

Note:

If the metadata dictionary information does not display in the browser, then ensure that the browser setting to access data sources across domains is enabled.