Resolving Loops Using Aliases

An alias table breaks a loop by making a copy of a table that is referenced multiple times in the same query for different purposes. In such a situation, the referenced table is functioning as a lookup table on the data foundation.

The alias is identical to the base table, but with a different name. The data in the alias is exactly the same as the original table, but the different name tricks SQL into using the same database table for two different purposes.

In the Motors data foundation, the COUNTRY table has already been identified as a shared lookup table. It was identified because it is serving two purposes in the data foundation: providing data for the CLIENT table and the SHOWROOM table.

Another way of spotting the problem table in a loop is that it only has the one end of multiple one-to-many joins going into it. Check the other tables in the loop. If you find no others with only one-end joins, the loop can probably be resolved using an alias.

Detect Aliases proposes candidate tables that you can edit and insert in the schema. You can also manually insert an alias table on the data foundation.

In the Master view of your data foundation, you can click on your potential alias table. After that, you can check the green plus icon in the Master view of your Data Foundation.

Select Insert Alias and the Insert Alias Tables window appears. Enter an alias name. Although not necessary, the name should reference the table name it is joined to, simply to help easily identify the original table.

Join the inserted alias table to the destination table and define cardinalities. Repeat for all tables that act as lookup tables for multiple purposes.

To detect aliases automatically, use the navigation pane of your data foundation and select Aliases and Contexts. Then select Detect Aliases from the Data Foundation menu. The Detect Aliases window appears. Select the check box of your original tables and enter a name for the alias table if necessary. Click OK and check the cardinality of the join.