How do I correctly populate a fact table with the surrogate key from the dimension table?

Assuming you are using SSIS to build an ETL process you simply use a lookup inside a data flow task to grab the key.  You do this for all of the needed keys to create your fact table recordset then write them to the fact table (which may also need a lookup to determine if it is an insert of a new record or an update to an existing record)

• Marked as answer by dbSlave65 15 hours 12 minutes ago

I am not exactly sure what scenario that you are referring to.  The dimension keys for the fact row is usually populated in the ETL as seen in the link below.

https://dwbi1.wordpress.com/2012/05/26/how-to-populate-a-fact-table-using-ssis-part1/

Alternatively, you can update the dimension keys once all the fact table has been loaded.  This will require that the dimensions natural keys be part of the fact row.  There are probably other ways of doing this, but this is what I can think of off the top of my head.

• Marked as answer by dbSlave65 15 hours 11 minutes ago

Keep in mind that you will also need to consider late-arriving dimensions as part of your ETL logic.  If your dimensions are loaded prior to your fact load, there is a chance that some of your fact rows may have new dimensions that were not loaded into your dimension table.

In these cases,  you need to figure out a strategy to update the missing dimension keys in your fact row.  There are a few strategies in this area.  Generally speaking, the ETL will use a dummy key in the initial load and update it with the correct dimension key in the next load.