Loading Fact Table + Lookup / UnionAll for SK lookups.

Question

I got to populate FactTable with 12 lookups to dimension table to get SK's, of which 6 are to different Dim Tables and rest 6 are lookup to same DimTable (type II) doing lookup to same natural key.

Ex:

PrimeObjectID => lookup to DimObject.ObjectID => get ObjectSK

and got other columns which does same

OtherObjectID1 => lookup to DimObject.ObjectID => get ObjectSK

OtherObjectID2 => lookup to DimObject.ObjectID => get ObjectSK

OtherObjectID3 => lookup to DimObject.ObjectID => get ObjectSK

OtherObjectID4 => lookup to DimObject.ObjectID => get ObjectSK

OtherObjectID5 => lookup to DimObject.ObjectID => get ObjectSK

for such multiple lookup how should go in my SSIS package.

for now am using lookup / unionall foreach lookup. Is there a better way to this.

Answer 1

I don't understand why you are doing 2 lookups per dimension.

Typically we processed all the dimensions first (using the TableDifference component to infer/expire the dimensions).

Then the fact table was loaded, doing one lookup on each dimension (in series) using the business keys to find the surrogate keys.

Answer 2

I assume what you are doing is a lookup, with errors redirected to a derived column to set default values for failed lookups, followed by a union all for each of the lookup/derived column values. That pattern is fairly common and I use it in early stages to help debug. However, since a union all is a partially blocking component (ie the Union All creates a new buffer when it executes, but then passes data through as soon as it comes in) in SSIS this will decrease the overall efficiency of your package due to the overhead of creating new buffers in your data flow. Usually, I will code the series of lookups to ignore errors and then after the last one, I will include a derived column component that does a replace with the default for all of the columns that are included as targets of lookups. This allows for the most efficient flow of data through your dataflow. For more information on which data flow components are blocking or semi-blocking see this post: http://sqlblog.com/blogs/jorg_klein/archive/2008/02/12/ssis-lookup-transformation-is-case-sensitive.aspx