Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SK generation in SQL Server 2005/2008

Go down

SK generation in SQL Server 2005/2008 Empty SK generation in SQL Server 2005/2008

Post  VHF Wed May 18, 2011 10:09 am

Informal survey:

For those running on Microsoft SQL Server 2005/2008/R2, how do you generate surrogate key (SK) values for new dimension records? IDENTITY field or another approach?

Please specify if you are using SSIS, hand-coded T-SQL, or other for ETL.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

SK generation in SQL Server 2005/2008 Empty Re: SK generation in SQL Server 2005/2008

Post  Mike Honey Wed May 18, 2011 8:29 pm

IDENTITY, using SSIS
Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

SK generation in SQL Server 2005/2008 Empty Re: SK generation in SQL Server 2005/2008

Post  BoxesAndLines Wed May 18, 2011 9:15 pm

I manage my own.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

SK generation in SQL Server 2005/2008 Empty Re: SK generation in SQL Server 2005/2008

Post  apermag Wed Jun 29, 2011 4:45 pm

BoxesAndLines wrote:I manage my own.

Hi BoxesAndLines,

Can I ask how you manage the SK's? Selecting a max?

Thanks

apermag

Posts : 17
Join date : 2011-06-28

Back to top Go down

SK generation in SQL Server 2005/2008 Empty Re: SK generation in SQL Server 2005/2008

Post  BoxesAndLines Wed Jun 29, 2011 6:15 pm

No, I use Informatica Sequence transformation.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

SK generation in SQL Server 2005/2008 Empty Re: SK generation in SQL Server 2005/2008

Post  VHF Thu Jun 30, 2011 12:05 pm

The reason I originally started is this thread is up to now I have been using an IDENTITY column in the dimension table as the SK. I typically use the SQL 2008 MERGE command to update dimension tables from a staging table, which works very well.

However, on the rare occasion I need to completely rebuild a dimension table, I need to be careful not to loose my SK assignments. Yes, I can copy the records to another table, make the structural changes, then bring them back with IDENTIY INSERT turned ON. However, I was thinking it would be nice if the SK assignments were stored separately.

I did come across a mention of of having a key assignment table in the staging database in one of the Kimball books. If I do this, have the SK (as an IDENTITY field) and the business key. When loading the dimension I would merge into key assignment table first to assign keys for new records, then merge into the dimension table (which would no longer have an identity column.) Having the key assignments safely stored in the staging database would allow completely rebuilding the dimension tables as needed without fear of losing the SKs.

My question now is if I adopt this approach should I have a separate key assignment table for each dimension or should I lump all the dimensions (or at least all the dimensions that use a 32-bit key) together into a single key assignment table (and thus in effect a single key generator shared by all dimensions)?



Last edited by VHF on Thu Jun 30, 2011 12:08 pm; edited 1 time in total (Reason for editing : typo)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

SK generation in SQL Server 2005/2008 Empty Re: SK generation in SQL Server 2005/2008

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum