SSIS source fact table column lookup

Question

I currently have a source fact table, that references all of its source dimensions. I have already used SSIS to take the source dimensions and load them into our destination dimensions. While doing this, I had a PK created in each dimension, and moved the original source PK into another column within the table.

The trouble I am encountering now, is how to perform the look from when I load the source fact table into the destination fact table, and have each source dimension primary key (now in a new column in the destination dimensions) reference the correct destination dimension primary key. Of which, the destination primary key will be in the destination fact table.

Would I need to use SK lookups, or just a transformation lookup? Furthermore, for a novice user, what would be the easiest / quickest to learn?

Hopefully some of this makes sense!

Thanks in advance for any help or advice!

Answer 1

I didn't completely understand your scenario, its quite confusing. Maybe if you give more specific examples it would be easier to help.

Nevertheless, the logical behavior in these type of scenarios is always load the dimensions first and when loading the fact, you use lookup transformation components to get the correct value of the foreign key from the dimension tables

here is an official video from youtube teaching how to use this component

Answer 2

When loading the data from the source "fact" you would be looking up the Source PKs, which in your case would be the Business Keys, or what you would call it. If you are using SCD type 2 dimensions, you would perhaps also want the Start/End dates in your lookup as well.

For Non-type2 dimensions, the easiest (and fastest) would be to just do a regular lookup. Your Source Fact table has a DimA_id (which is the Business Key). Use SELECT PK, BK FROM DimA in your lookup task, join the dima_id to the dimensions BK, and put the PK into the downstream. Use the PK when inserting to the destination Fact table.