Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating Surrogate-Keys for Type 1 and Type 2 dimensions using SSIS Expand / Collapse
Author
Message
Posted Wednesday, December 27, 2006 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 8:24 PM
Points: 1, Visits: 40
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/hbhaiya/2783.asp
Post #333038
Posted Wednesday, January 10, 2007 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 6:11 AM
Points: 5, Visits: 36
Why don't you use GUIDs for the surrogate-keys?
Post #335763
Posted Wednesday, January 10, 2007 8:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 8:16 AM
Points: 945, Visits: 1,018

I think this was a good and thorough article on SCD's.

One thing of note if you get the highest key value and manually set surrogates versus using identity columns then you cannot load a dimension in parallel.

Also be wary of using the checksum to determine record changes, it is possible, however unlikely, that two different records could produce the same checksum.  Here is a quote from BOL:

"CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also usually changes. However, there is a small chance that the checksum will not change."

GUID's may be great in that you don't need to worry about uniqueness, but there are a couple of reasons not to use GUID's as surrogates.  One is that they are 4 times larger then an integer.  May not sound like a big deal but when you are dealing with potentially hundreds of millions of records with an expectation of sub-second response time on reports, every little bit helps.  Also you may want to create clustered indexes on the surrogate in the fact tables.  Since GUID's are not sequential, at least in not in SQL2K, then you will quickly fragment your fact tables.

David




Post #335788
Posted Thursday, January 11, 2007 6:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:56 AM
Points: 81, Visits: 190

Thank you for writing this article, Harsh. It's a good article. I learned about the version number from your article; I've used effective and expiry date and current version column but I have come across the version number, so thanks for the valuable input.

I tend to agree with David that using identity is better than GUID or max+1. As David correctly say if we use max+1 we could have problem when loading in parallel. I've seen an application (not DW though, it was an OLTP) used max+1 to generate ID and we experienced contention and locking problems. Last year when building a DW in a project somebody suggested to use GUID and we decided not too because of the same reason as David mentioned, i.e. performance. Not on the dimension tables, but performance of the fact table as fact tables have many SKs columns. I used big int sometimes for customer and product dimensions but all other dimensions normal int usually sufficient.

Looking forward to your next article.

Kind regards,
Vincent

Post #336137
Posted Thursday, August 23, 2007 1:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:16 AM
Points: 8, Visits: 147

can I download the code in your article .

both the database and the ssis package.

 

 

thanks in advance

Ran

 




Post #393092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse