Model the Associated Dimension Tables in a Single Dimension

In the logical layer, you can choose to model the two dimension tables associated with a bridge table in a single dimension, or in two separate dimensions.

Before you perform this task, you need to create the required physical joins. See Create Joins in the Physical Layer for Bridge and Associated Dimension Tables.

To model the associated dimension tables in a single dimension, create a second logical table source that maps to the bridge table and to the other dimension table, and then add columns from the other dimension table. Don't add the bridge table and the associated dimension table that isn't joined to the fact table to the logical layer. For the example described in About Bridge Tables, you add the Jobs table (dimension table joined to the fact table), but not the Assignment table (bridge table) and Employee table (dimension table not joined to the fact table).

Providing two separate logical table sources makes queries more efficient because it ensures that queries against a single dimension table don't involve the bridge table.

It's a good practice to use the bridge table name as the name of the source.

You can create dimensions based on your logical tables, including the logical table with the bridge table source.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer Logical layer icon.
  4. In the Logical Layer pane, click Create Create icon and then Create Logical Table to create the needed logical dimension table. Repeat this step to create the logical fact table.
  5. In the logical layer, double-click the dimension table that is joined to the fact table and in the table, click the Sources tab.
  6. Click Add Physical Table Add physical table icon and then Create Logical Table Source and in the table source's Name field, enter the name of the bridge table that you created in the physical layer.
  7. Click Add Physical Table and then Create Logical Table Source and in the table source's Name field, enter the name of the associated dimension table that isn't joined to the fact table.
  8. Click the Columns tab to navigate to the table's column list.
  9. Click Add Column Add column icon and then click Create New Column.
  10. In the new column's Name field, enter the name of a column from the dimension table that isn't joined to the fact table. Repeat this step to add the required columns.
  11. Click Save.