1. Analytical Application Infrastructure User Guide
  2. Unified Analytical Metadata
  3. References
  4. Creating Expression using Expression Builder

Creating Expression using Expression Builder

You can define an expression in the Expression Builder window to join two selected tables. Click to display the Expression Builder window.

Figure 8-51 Expression Builder window


This illustration shows the Expression Builder window. The window displays the Entities, Functions, Operators, and Expression. You can select the required values and click OK to build the expression.

The Expression window consists of the following sections:

  • Entities: Consists of the Entities folder with the list of tables that you selected from the Entity Groups folder. Double-click the Entities folder to view the selected dimension tables (Product and Segment tables).
  • Functions: Consists of functions that are specific to databases like Oracle and MS SQL Server. You can use these functions along with Operators to specify the join condition.

    The Functions categories are displayed based on the database types as tabulated.

    Table 8-23 Database and its Functions

    Database Functions
    Transact SQL Specific to MS SQL server which consists of Date & Time, Math, and System functions.
    SQL OLAP Specific to Microsoft OLAP which consists of Array, Dimension, Hierarchy, Logical, Member, Number, Set, and String functions.
    SQL Specific to Oracle which consists of Character, Conversion, Date and Numeric functions.

    Note:

    It is not mandatory to specify a Function for a join condition.
  • Operators: Consists of the function operators categorized into folders as tabulated.

    The following tables shows the Operator and its types used.

    Table 8-24 Operator and its Types

    Operator Types
    Arithmetic +, -, %, * and /
    Comparison '=', '!=', '< >', ‘>', '<', 'IN', 'NOT IN, 'ANY', 'SOME', 'LIKE' and 'ALL'.
    Logical 'NOT', 'AND' and 'OR'
    Set UNION, UNION ALL, INTERSECT and MINUS
    Others The Other operators are 'PRIOR', '(+)', '(' and ')'.

To specify the join condition:

  1. Select the Entity of the fact table to which you want join the dimension entities.
  2. Select a Function depending on the database type.
  3. Select the Operator which you want to use for the join condition.
  4. Select the second Entity from the Entities pane that you want to join with the first entity. You can also select more than one dimension table and link to the fact table.

    The defined expression is displayed in the Expression pane. You can click Reset to clear the Expression pane.

  5. 5. Click OK and save the join condition details.