Lookup views allow you to validate the process of looking up a primary key value in a lookup or reference table based on a text value from a source, and then storing the lookup table primary key in the target fact table. For example, a product name in the source system might be in a dimension that serves as the lookup table. The data transformation process involves looking up the product name and placing the primary key from the lookup table in the target fact table as a foreign key. You must validate the product name in the source table against the foreign key in the target table.
The following table lists the keys used in the example:
The source table product name field is found in the lookup table. After the product name is found, the primary key from the lookup table is stored in the target table as a foreign key.
To test the validity of the lookup table foreign key in the target table, complete the following tasks:
Create the lookup view. Add the source table and the lookup table to the lookup view. Then create a relationship between the product name in the source and lookup tables.
Create a table pair with the lookup view and the table that is the target of the data transformation process. Join the tables on the source table primary key, which is stored in the target table as a foreign key.
Create an OUTER_VALUE test that compares the primary key of the lookup table to the lookup ID that is stored as a foreign key in the target table.
The OUTER_VALUE test checks the validity of the lookup table primary key stored in the target table against the contents of the source table. The test also finds any orphans, which are records in the target table that do not match any records in the lookup table.