Oracle® Business Intelligence Server Administration Guide > Oracle BI Administration Tool Utilities and Expression Builder >

Expression Builder


You can use the Expression Builder dialog boxes in the Administration Tool to create constraints, aggregations, and other definitions within a repository. The expressions you create with the expression builder are similar to expressions created with SQL. Except where noted, you can use all expressions constructed with the expression builder in SQL queries against the Oracle BI Server.

For information about using SQL with the expression builder, refer to SQL Syntax and Semantics. For information about the SQL functions supported by the Oracle BI Server, refer to SQL Reference.

This section includes the following topics:

About the Expression Builder Dialog Boxes

You can access the expression builder from the following dialog boxes:

  • Logical Table Source—Content tab
  • Logical Table Source—Column Mapping tab
  • Logical Column—General tab
  • Logical Column—Aggregation tab
  • Logical Foreign Key
  • Physical Foreign Key
  • Session Variable—Variable tab
  • Static Repository Variable—Variable tab

When creating expressions in the Expression Builder dialog boxes, you can search the categories pane and building blocks pane. When you type a value into the search box, it filters out the non-matching strings and only the ones that match will appear. After typing search criteria in a search box, you can move up and down the list using arrows and tab between the first search box and the second search box. To return to the full list of results, you delete the search string from the Search field.

When you locate the item you want to insert into the expression, select it and click Insert in the dialog box or press Enter on your keyboard. The item you selected will appear in the expression in the expression box.

When you first open the Expression Builder dialog box, the items are not sorted. When checked the Sort Panes check box sorts all items in the panes. As soon as you select the check box, the panes are automatically redrawn without changing the contents of the panes or your filtering criteria.

Figure 15 shows an example of an expression builder and the dialog box contains the following sections:

  • The edit pane at the top of the dialog box allows you to edit the current expression.
  • The toolbar in the middle of the dialog box contains commonly used expression building blocks.
  • In the lower half of the dialog box, the left pane is the Selection pane. It displays the folders that are appropriate for the dialog box from which you accessed the expression builder.
  • The lower middle pane is the Categories pane. It displays the available categories for the folder you select in the Selection pane. The Search field below the middle pane allows you to search for a value in the middle pane.
  • The lower right pane is the Building Blocks pane. It displays the individual building blocks for the category you select in the Category pane. The Search field below the right pane allows you to search for a value in the right pane.
Figure 15. Example Expression Builder
Click for full size image

Expression Builder Toolbar

The toolbar is located in the middle portion of the expression builder. Table 22 describes each icon and its function in an expression.

Table 22. Expression Builder Toolbar
Operator
Description

+

Plus sign for addition.

-

Minus sign for subtraction.

*

Multiply sign for multiplication.

/

Divide by sign for division.

||

Character string concatenation.

(

Open parenthesis.

)

Close parenthesis.

>

Greater than sign, indicating values higher than the comparison.

<

Less than sign, indicating values lower than the comparison.

=

Equal sign, indicating the same value.

<=

Less than or equal to sign, indicating values the same or lower than the comparison.

>=

Greater than or equal to sign, indicating values the same or higher than the comparison.

<>

Not equal to, indicating values higher or lower, but not the same.

AND

AND connective, indicating intersection with one or more conditions to form a compound condition.

OR

OR connective, indicating the union with one or more conditions to form a compound condition.

NOT

NOT connective, indicating a condition is not met.

,

Comma, used to separate elements in a list.

Folders in the Selection Pane

The folders that appear in the Selection pane vary based on the dialog box from which you accessed the expression builder. This section describes the folders that may appear.

Aggregate Content

The Aggregate Content folder contains the available aggregate functions. Aggregate sources must use one of the functions listed here to specify the level of their content.

Dimensions

The Dimensions folder contains the dimension configured in the business model. If no dimension exists in a business model, or if the dimension folder is not pertinent to a particular expression builder, the Dimension folder is not displayed.

When you select the Dimensions folder, each configured dimension displays in the middle pane, and each level for the selected dimension displays in the right pane.

Logical Tables

The Logical Tables folder contains the logical tables configured in the business model. If logical tables are not pertinent to a particular expression builder, the Logical Tables folder is not displayed.

When you select the Logical Tables folder, each logical table in the business model displays in the middle pane, and each column for the selected logical table displays in the right pane.

Operators

The Operators folder contains the available SQL logical operators.

Expressions

The Expressions folder contains the available expressions.

Functions

The Functions folder contains the available functions. The functions that appear depend on the object you selected.

Constants

The Constants folder contains the available constants.

Types

The Types folder contains the available data types.

Repository Variables

This folder contains the available repository variables. If no repository variables are defined, this folder does not appear.

Session Variables

This folder contains the available system session and non system session variables. If no session variables are defined, this folder does not appear.

Example of Setting Up an Expression

Figure 16 shows the expression builder for a derived logical column.

Figure 16. Expression Builder for Derived Logical Columns
Click for full size image

Select the Functions folder in the left pane. Double-click the function in the right pane to paste the function in the edit box. In the expression builder's edit box, click once between the parentheses of the function to select the area as the insertion point for adding the argument of the function.

Double-click the logical column to paste the logical column at the insertion point as the argument of the function. Figure 17 shows where the expression appears in the window.

Figure 17. Example Logical Column Function in the Editing Pane
Click for full size image

Navigating Within the Expression Builder

Use the following procedure to navigate within an Expression Builder dialog box.

To navigate within an Expression Builder

  1. In the Selection pane, select the appropriate folder for the type of expression you want to build.

    The available categories for the folder appear in the Categories pane.

  2. Select the appropriate category for the expression you want to build.

    The available building blocks for that category appear in the Building Blocks pane.

  3. Double-click a building block to move it into the Editing pane.
  4. To insert an operator into the expression, double-click an operator on the Expression Builder toolbar.

Building an Expression

Use this procedure to build an expression in the Expression Builder dialog box.

To build an expression

  1. Navigate to the individual building blocks you want in the expression.

    The Syntax bar at the bottom of the Expression Builder dialog box shows the syntax for the expression.

  2. Add the building blocks to the Editing pane.
  3. Edit the building blocks to reflect the expression you want.
  4. Use the Expression Builder toolbar to insert operators into the expression.
  5. Repeat the preceding steps until the expression is complete, and then click OK.

    The Administration Tool displays a message for any syntax errors in the expression. When the expression is syntactically correct, the Administration Tool adds the expression to the dialog box from which you accessed the Expression Builder.

About Time Series Conversion Functions

Time series functions operate on time-oriented dimensions. To use these functions on a particular dimension, you have to designate the dimension as a Time Dimension and set one or more keys at one or more levels as Chronological keys. This identifies the dimension as having a monotonically increasing value in time (corresponds to chronological order).

NOTE:  It is required that you define a chronological key at a level that can be used to answer your time series query. It is recommended that you define additional chronological keys at other relevant levels for performance reasons.

Currently, AGO and TODATE are the types of time series conversion functions. Currently, you may only enter AGO and TODATE functions in the Expression Builder in the Administration Tool. You cannot use them in coded SQL.

The Ago and ToDate functions allow you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically. The time series functions calculate Period Ago and Period to Date functions based on user supplied calendar tables, not on standard SQL date manipulation functions.

The following list describes the important grains in navigating a time query, using the following query example:

Select quarter, YearAgoSales:

  • Query grain. The grain of the request. In the query example, the query grain is Quarter.
  • Time Series grain. The grain at which the aggregation is requested. In the query example, the Time Series grain is Year.

    NOTE:  Time series query is valid only if the time series grain is at the query grain or higher.

  • Storage grain. The query in the example can be computed from daily sales or from monthly sales, or from quarterly sales. The grain of the aggregate source is called aggregation grain.

    NOTE:  The chronological key has to be defined at this level.

    Figure 18 shows the time series functions in the Expression Builder dialog box.

    Figure 18. Example Time Series Functions in the Expression Builder
    Click for full size image
    Ago

    A time series aggregation function for relational data sources only. Calculates the aggregated value from the current time back to a specified time period. For example, Ago can produce sales for every month of the current quarter and the corresponding quarter-ago sales. Multiple Ago functions can be nested if all the Ago functions have the same level argument.

    NOTE:  You can nest exactly one ToDate and multiple Ago functions if they each have the same level argument.

    Syntax:

    AGO(<measure_expression>, <model_id>.<dimension_id>.<level_id>, <integer_literal>)

    In that example, <measure_expression> is an expression that contains at least one measure, <model_id> is a model identifier, <dimension_id> is a dimension identifier, <level_id> is a level identifier, and <integer_literal> is an integer literal. The following is an example of this syntax:

    AGO(model.sales.revenue + 5, model.time.month, 3)

    ToDate

    A time series aggregation function for relational data sources only. ToDate aggregates a measure attribute from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.

    If unsupported metrics are requested, NULL values will be returned and a warning entry will be written to the NQQuery.log file when the logging level equals three or above. A ToDate function may not be nested within another ToDate function.

    NOTE:  You can nest exactly one ToDate and multiple Ago functions if they each have the same level argument.

    Syntax:

    TODATE(<measure_expression>, <model_id>.<dimension_id>.<level_id>)

    About the IndexCol Conversion Function

    The IndexCol function allows you to build a derived logical column. Selecting IndexCol automatically generates the following function template:

    IndexCol( <<integer literal>>, <<expr1>> [, <<expr2>>, ?-] )

    For more information, refer to IndexCol.

  • Oracle® Business Intelligence Server Administration Guide Copyright © 2007, Oracle. All rights reserved.