I was trying to design a CheckSum for our Update and Insert implentation today and I was researching the CheckSum function in SQL Server and I found this good article. I implemented the HASHBYTES() into my design. It’s a bit more work but a little more accurate then the CheckSum.
Check out this article by TomLannen: HASHBYTES()
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)
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
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
Here is the C# code for the Zoned Decimal Converter Component.
https://docs.google.com/open?id=0B2HwBMqP-TJlenlxTzhGNnJWTU0
Off to Milliken on Monday to start a new contract in Spartanburg, SC.
Check out my companies blog to see what were all doing.http://is.gd/ZfY2UW
