Resetting Dimension Surrogate Keys

View previous topic View next topic Go down

Resetting Dimension Surrogate Keys

Post  ian.coetzer on Mon Mar 05, 2012 7:57 am

Hi

I have recently come across a situation where some dimensions and fact tables are truncate and re-populated.
Since the surrogate keys were defined as identity integers some of the dimension members now have different surrogate keys than what they had originally.

This presents a problem when using reporting software that stores the surrogate key when a filter is set.

I know of two strategies to overcome this:

1. rather use MERGE statements in Sql Server 2008 to update / expire records (instead of repopulating the dimension completely)
2. generating surrogate keys internally comprising some business key in conjunction with a source key

Any thoughts on this?
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Resetting Dimension Surrogate Keys

Post  ngalemmo on Mon Mar 05, 2012 4:07 pm

I would never truncate and repopulate a dimension table as an ongoing process. Change the code and perform proper updates on the dimension.

Facts are less of an issue.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Resetting Dimension Surrogate Keys

Post  ian.coetzer on Tue Mar 06, 2012 8:07 am

I agree.
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Resetting Dimension Surrogate Keys

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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