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)

Tabular AMO 2012

This is a great article on codeplex about creating a tablular model using AMO 2012. I am in the process of designing methods to extract information from the BISM tabular model using AMO and XML. I just wanted to share this with you as I will begin to analyze this method further. I will be posting some code here and there to show the differences from Multidimensional and tabular AMO.

Link: Tabular AMO 2012

SQL Saturday #174 Charlotte BI Edition

What a great success SQL Saturday #174 was in Charlotte. My presentation on “Programmatically managing the BI Stack” went well and I was asked to post some links to my code and my PowerPoint presentation. My links can be found in my menu above under Code and Presentations.

Here are the follwing links:
Mod Package Code
AMO Cube Documenter
SQL Saturday #174 Programmatically managing the BI Stack Presentation