SSIS 2008: Replacing Natural Keys With Surrogate Keys

View previous topic View next topic Go down

SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  djphatic on Sat Jun 23, 2012 4:24 pm

I am building a package in SSIS 2008 and I need to replace the natural dimension keys in my fact table with the surrogate keys in the dimension tables. Each fact has 7 date dimensions, 6 time dimensions and other dimensions.

Is there a method of updating common dimension fields, i.e. all date or time fields, using one lookup transformation or do I need a lookup transformation for each field?

djphatic

Posts : 20
Join date : 2012-04-21

View user profile

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  John Simon on Tue Jun 26, 2012 1:42 am

You need to use a Lookup transformation for each.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  TheNJDevil on Tue Jun 26, 2012 10:00 am

Yes you need a lookup for each one, but I think his question was more tuned toward performance. Do those 7 date dimension lookup transformations actually load the date dimension 7 times? If so, I believe he is looking for a way to not have it load 7 times.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  John Simon on Thu Jun 28, 2012 9:19 pm

Use the Cache Transform as a source in a Data Flow. Then point your lookup to the Cache.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  blynch on Sun Jul 01, 2012 7:28 am

I think you will have to have 7 cache files as I think you can only use a cache file once per package.

blynch

Posts : 18
Join date : 2011-10-16

View user profile

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  John Simon on Sun Jul 01, 2012 9:48 pm

Nope.
I just tested it and loaded two tables with the same cache.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With Surrogate Keys

Post  blynch on Wed Jul 04, 2012 12:37 pm

Within the same package? I want that to work for me. Will have to look into that, thanks.

blynch

Posts : 18
Join date : 2011-10-16

View user profile

Back to top Go down

Re: SSIS 2008: Replacing Natural Keys With 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