TimeXtender – Lookups and Relationships

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.

lookup

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.

relation

We can see the relationship created for the ProductCategoryID below:

created_relation

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.

catnamerelation

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.

finalcatnamejoin

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)

RelationView

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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s