June 25, 2009 at 1:45 pm
I'm starting a new topic from another thread since it veered off the original subject.
I've tried all 3 of the above methods to identify and process only new and changed data in my source files. The 1st 2 are native to VS/BI 2005, the 3rd you can DL from codeplex: http://kimballscd.codeplex.com/. For all you A.D.D. folks - the winner was CheckSum.
CheckSum
Pros: by far the fastest for processing largish source files. I have potentially hundreds of files totaling over 100k records to process every day so this was a biggie for me. It relies on a stored hash column that you add to your your existing dimension to compare source data against so these keys do not have to generated on the fly.
Cons: You have to configure it manually, but I found in my case this was fine as I ended up having to tweak the other methods so much that I didn't save any time with the wizards. You can find some easy to follow intrctions here:
http://www.sqlis.com/post/Checksum-Transformation.aspx
Another common complaint is that the Checksum algorithm does not guarantee uniqueness, but don't all the methods use some kind of hashing algorithm to detect changes? Or perhaps they don't and that's why they are slower. If this is the case then the hashing algorithm would be a primary drawback. I had to select the "Original" algorithm; the latest and greatest, CRC32, appeared to be hosed.
SDC Transformation
Pros: The configuration wizard and built-ins for things that are rather tedious to write yourself, like expiring records and creating current ones when certain conditions are met, automatic change and create dates for new and changed records.
Cons: It's a lot slower than Checksum. This was a deal-breaker for me, ladies.
Kimball Method SCD
Pros: It is supposed to be a lot faster then SCD transformation but I could not verify this because I couldn't get the dang thing working. The built-in auditing features looked real nice.
Cons: Never got it to run - it just hung. Others have had the same problem. It has more bells and whistles than SCD so there's a bit of a leaning curve to set it up, which turned out to be a waste of time in my case.
June 26, 2009 at 1:51 pm
oops sorry - gave some misinformation below. CheckSum transformation is NOT native to VS/BI 2005 (SCD transformation is). You can DL CheckSum from http://www.sqlis.com/post/Checksum-Transformation.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply