All Forums UDA
ragugct 7 posts Joined 08/04
19 Jul 2007
Change in Primary Key on EDW table

All,There is a change in Primary key of a warehouse table holding data up to date.Basically source system added an existing column in the table as a primary key and sequelly the same change need to be amended in warehouse(EDW) as well.Any structured way of handling this to use the data with old primary keys as well new primary keysMany thanks in advance for all of your inputs.

sachinp17 53 posts Joined 11/06
16 Aug 2007

Hi, Make the old primary key columns as primary index and new primary key columns as primary key..Regds,sachin

30 Aug 2007

I think a surrogate key or an identity column can help you.Add a column to your table making it the unique primary index.Then reload your table with existing data.Before reloading make sure u have a surrogate key logic being handled by the ETL or declare the primary index as identity column.Thanks

Paddu 14 posts Joined 09/07
06 Oct 2007

There are 2 ways to perform this.Present condition:1)you have a table with rows.2) Created a new column which will be the primary key.3) This new column have NULL in the existing records.If above condition is right then go for Insert or update.Insert: Reload entire data as suggested by bhattachary.Update:create a new column(dont make it primary key)update this col using some sort of coding.Then make this column as primary key.This solve your issue i belive.

marcmc 112 posts Joined 12/05
10 Oct 2007

...also remember that if you re-define the Primary Index of your table, the skew of your data across amps will most likely change for the better or the worse. You can analyse skew with the following:select hashamp(hashbucket(hashrow(col1, col2, col3))) ,count(*)from tableNamegroup by 1;Just insert the column/group of columns that you currently use and/or you intend to use for your primary index to get a before and after comparison.Hope that helps.

You must sign in to leave a comment.