You can define an expression in the Specify Expression window to join two selected tables. Click button. The Specify Expression window is displayed.
The Specify 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 – 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 |
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 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 |
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 |
Others |
The Other operators are 'PRIOR', '(+)', '(' and ')'. |
Concatenation |
|| |
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 section. You can click button to reset the values or click .button to erase the specific value.
5. Click OK. The defined expression is validated as per the selected table and entity definition and on successful validation, is displayed in the main window.