After adding fact tables and (optionally) dimension tables to the Business Intelligence Data View, you can define relationships between fact tables and dimension tables. All dimension tables must have a column mapped to a fact table column, or it is an invalid orphan table.
The figure below shows mappings between the inventory_fact fact table and relationships with the product, store, and time_by_day dimension tables.
Defining Relationships Between Fact Tables and Dimension Tables
- In the Schema Editor Tree View, click Business Intelligence Data View. The Business Intelligence Data View displays the Data View tab.
-
To map a fact table ID to a dimension table ID, select the fact table from the table list, and click Add Relationship.
The Add Relationship Dialog appears.
The selected fact table is listed under the Foreign Key Database and Foreign Key Table. The dimension table will be listed under the Primary Key Database and Primary Key Table.
- Select the appropriate database containing the dimension table (the default is the same database as the fact table) and then select the actual table.
- After you have selected the dimension table, click the Add Columns button to define the join relationship between the fact and dimension tables. The Add Column dialog box appears.
- Select the Foreign Key Column from the fact table.
- Select the Primary Key Column from the dimension table.
-
By default, the join condition is set to equals (=). To change the join condition – for example, if you want to use non-equijoins to join data based on a date range – click the drop-down arrow next to the Join Condition field, and then select the appropriate condition.
- Click OK.
- If the relationship is defined by a composite key (multiple columns from each table are required to uniquely identify the relationship), repeat steps 4–8 for the additional columns that are required to define the relationship.
-
If more than one fact table column needs to refer to the same dimension table (such dimensions are often called role-playing dimensions), you can define an alias for the Primary Key Table.
For example, the fact table contains an order date and a ship date, each of which needs to join to the same time dimension table. You could create one alias called Order Date for the join between the Order Date column in the fact table and the Date column in the time dimension table, and a second alias called Ship Date for the join between the Ship Date column in the fact table and the same Date column in the time dimension table.
The following figure shows a completed example of an Alias.
-
After you have defined all the fact table column relationships with dimension table columns, you can add fact tables and dimension tables to the tree view.
You can only use an individual fact table column once in a relationship with a dimension column, unless it is part of a composite key (two or more columns that uniquely identify an entry in the fact table). In the case of composite keys, the same combination of columns that define a composite key can only be used once in defining a relationship to a dimension table. Otherwise, it would not be a proper star or snowflake schema.