TimeXtender is a new tool that I’ve been using to rapidly increase our ETL Development. Check out the site: TimeXtender
There are two ways you can add a lookup and have relationships configured in TimeXtender:
Method 1 is to drag the column down from the lookup table to the main table and set the join by the keys that match between the 2 tables. For example we have ProductSubcategory and Product where ProductSubcategoryID is the key between the lookup table ProductSubcategory and the main table Product.This is considered a Left outer Join which will send rows that don’t match into the Warning table.
Method 2 is to drag the key from the lookup table to the foreign key of the main table. So for this example we drag the ProductCategoryID from ProductCategory table to the ProductCategoryID in the ProductSubCategory table. This will give an option to create a relation.
We can see the relationship created for the ProductCategoryID below:
Now we want to drag the Name from ProductCategory to the ProductSubCategory table and it will ask us if we want to use that relation that we just created to join the table for this field.
So we can see that join uses the same Keys that the relation has and this now gives us some leverage to change how that joins acts.
Lastly we can change the way that the join behaves by a right click on the relationship to change the options as follows:
(Click to enlarge image)
So we need to define the 3 relationship settings in terms of joins:
1. Error (Inner Join)
2. Warning (Left Outer Join)
3. Relation (Full Outer Join)