akirajt (7/7/2012)
Should the surrogate key, for instance customerKey for dimension customer, be created in the stage process and then the all data should be loaded into the dimCustomer or should the customerkey be created automatically in the dimCustomer when you are loading the data to the dimCustomer table?
Populate surrogate keys in staging. Otherwise you have to do joins back to the warehouse / mart tables while you are populating the referencing tables. Pre-populating the surrogate keys and the references to them minimises the number and size of joins and shortens the transaction during which the DW is being touched.
Avoid using IDENTITY columns in the data warehouse. If you are using SQL Server 2012 then use sequences instead.