I have multiple databases that I want to store in one data warehouse database. I am wondering how I design the import process to handle multiple lookup tables.

For example, say I have 5 databases all with the lookup table CustomerState. In one datatabse it could look like this:

enter image description here

In another database it could look like this:

enter image description here

How should I handle this in my enterprise layer of my DW database? Do I add a SourceSystemId to the lookup table, maybe something like this:

enter image description here

And then use the pkyCustomerStateId in my Customer table rather than the CustomerStateId?

1 Answer 1


This type of thing should be handled by the ETL process that brings the data into the data warehouse. In fact, this process is the T in ETL.

What you need to do first is define the logical key column(s) of the tables, so the business meaning of the rows can be equated between the databases. A multi-column key as you propose would complicate matters, and really doesn't solve the problem.

For this example, I would define CustomerState as the logical key column in the dimension, and when the separate tables are merged together, this column would be unique in the result, with new CustomerStateId values assigned. This ensures the dimension primary key is as narrow as possible, which will carry through to the fact tables and make them as narrow as possible as well.

The ETL process might do something like this (assuming the CustomerStateId column of the target table is an IDENTITY column):

MERGE INTO [dbo].[CustomerState] tgt
    USING [Staging].[CustomerState] src ON src.CustomerState = tgt.CustomerState
        INSERT (CustomerState) VALUES (src.CustomerState);

(The reason I used MERGE instead of INSERT is that in other dimensions you may need to handle doing updates as well; not in this case as there are no other columns.)

Then, the fact table loading process would use a lookup mechanism (Lookup Transformation in SSIS) to go from the CustomerState logical value to the newly-assigned CustomerStateid value generated by the above statement.

  • Thanks! That was my other idea but it seemed a bit like I was dropping bits of information I may need (The original Id's of the customer state in their respective systems) However, thinking about it I am not sure what I would use them for. Getting caught up in over thinking I suspect! Feb 15, 2013 at 14:55
  • @Barry: You're welcome. Sometimes the transformation involved is lossy; in other words, it's non-trivial or impossible to go from the unique business key in the dimension back to the source data (if this is a business requirement for the system). In these cases, it's more complicated because you may need to record the source identifier as well; this may not be a 1-to-1 relationship, or a single column, either. This is why it's important to first define the business keys in the source database, so you can identify these types of scenarios.
    – Jon Seigel
    Feb 15, 2013 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.