By default, the data builder considers the first table that you drop in the
workspace as the fact table. As you add tables to the
data query, the data builder attempts to join the tables with the first table, the fact table.
This strategy works well for straightforward fact table and
dimension table arrangements. However, in many cases, you need to join additional tables to
a junction table. Junction tables are also known as bridge tables or link tables.
To retrieve the transaction amount and transaction type from the Entity_Dim table,
it must have a join that uses the Bridge table instead of the first table (fact table)
that was added to the workspace.
To create a join to
a junction (or bridge) table:
-
Add the fact table to the data query first.
-
Add the dimension tables, such as the Accounts_Dim table and junction tables. The
data builder attempts
to join the tables automatically.
Review the join as
follows:
-
Check that the correct
columns are used.
-
Change the join type,
such as from inner to left, as needed.
-
Add the tables that
use the junction table, such as the Entity_Dim table. Initially, the
data builder attempts to join the tables to the fact table.
Correct the join as
follows:
-
Select the automatic
join to the fact table, right-click, and select
Remove
Join Condition.
-
Use your pointer to connect the keys from the junction table to the dimension table.
-
Change the join type
as needed.