Assigning surrogate keys to early arriving facts using Integration Services

How to get the attachment

tkejser — Wed, 13 May 2009 17:35:48 GMT

To get the attached ZIP file click on the title of this post to bring it up in full screen. At the bottom of the page, there is a link to SK_Example.zip.

Thanks

Thomas Kejser

re: Assigning surrogate keys to early arriving facts using Integration Services

marcelfranke — Tue, 19 May 2009 16:39:09 GMT

You can even save the first lookup and just use the second one, when define your partial cache big enough to hold all your dimension elements. Then you also don't need the union all element and it looks very smart.

re: Assigning surrogate keys to early arriving facts using Integration Services

tkejser — Tue, 19 May 2009 17:07:19 GMT

Marcel: If you try to profile your solution, you will see that even the KNOWN keys result in roundtrip to the database. If you have a 10.000 row dimension (like this example), then you will get 10.000 roundtrips, even though only 1000 keys are early arriving.

By having the first lookup catch most rows with a FULL cache, you save 9000 roundtrips. A performance gain worth taking I think :-)

re: Assigning surrogate keys to early arriving facts using Integration Services

marcelfranke — Tue, 19 May 2009 19:56:27 GMT

You are right, because the partial cache starts empty and then you have all those round trips over the StoredProcedure. But anyway a nice solution. :)

How to handle Inferred Members in SSIS 2008

The truth is on the wire — Wed, 20 May 2009 11:36:41 GMT

Everybody who is familiar with Kimballs data warehouse design (Kimball Group) also knows about the early arriving facts problem. In SSIS 2008 the solution for achieving inferred members is quite a little bit easier. Thomas Kejser wrote a nice blog entry