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

Resetting Dimension Surrogate Keys

2 posters

Go down

Resetting Dimension Surrogate Keys Empty Resetting Dimension Surrogate Keys

Post  ian.coetzer 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?
ian.coetzer
ian.coetzer

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

Back to top Go down

Resetting Dimension Surrogate Keys Empty Re: Resetting Dimension Surrogate Keys

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Resetting Dimension Surrogate Keys Empty Re: Resetting Dimension Surrogate Keys

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

I agree.
ian.coetzer
ian.coetzer

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

Back to top Go down

Resetting Dimension Surrogate Keys Empty Re: Resetting Dimension Surrogate Keys

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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