You can define an expression in the Expression Builder Window to join two selected tables.
The Expression Builder 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 – This is divided as Database Functions and User Defined Functions. Database 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.
Database and its Functions
Database |
Functions |
|---|---|
Transact SQL |
Specific to MS SQL server which consists of Date and 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 String, Aggregate, Date and Time, and Mathematical 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.
Operator and its Types
Operator |
Types |
|---|---|
Arithmetic |
+, -, %, * and / |
Comparison |
'=', '!=', '< >', '>', '<', >=, <=,'IN', 'NOT IN', 'ANY', 'BETWEEN', 'LIKE', 'IS NULL', and 'IS NOT NULL'. |
Logical |
'NOT', 'AND' and 'OR' |
Set |
UNION, UNION ALL, INTERSECT and MINUS |
Other |
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 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 reset the values.
5. Click OK.
The defined expression is validated as per the selected table and entity
definition and on successful validation, it is displayed in the main window.