Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have a simple OLTP database with orders, products, and customers:

And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date dimension:

When loading the orders table into fact_orders (let's say I was using an SSIS Lookup Transformation to assign the surrogate keys), does that mean that the source of data for the orders would also need to have the natural "foreign key" values that were associated with the order in the OLTP system?

In other words, would the data that's being loaded come from a query like this?

1 Answer
1

I'm not sure that you need "natural" keys, but you probably do need to maintain a key mapping of sorts. So you need to understand what relationships map between your source and target systems, identify the keys for those relationships and build your key mappings from there.

CustomersToDim_Customers (customer_id, dim_customer_id)
ProductsToDim_Products (product_id, dim_product_id)
OrderDatesToDim_Date (order_date, date_id) or (map_id,order_date,date_id) if you want to use a key to map.

And lastly, I see the order_id as your key to the fact table. So I
would go

In my case I renamed the fields for the mart with dim_field_id because
I didn't want name collision within my tables or confusion as to which
Id they pointed to. Your ETL would have to know that
CustomersToDim_Customers.dim_customer_id really maps to
Dim_Customers.customer_id and that
CustomersToDim_Customers.customer_id really maps to
Customers.customer_id.

I would also be half-inclined to include the order_number in the
OrdersToFactOrders mapping table, but that is because I like to have
tracking data for audit purposes. Makes my life easier. But, based on
what you told me, order_number and order_id are one-to-one so the
inclusion of order_number would be redundant then and only necessary
if you have a perfectionist paranoia to make sure your data is correct
on both sides (I really like to make sure that A on side A and B on
side B are really correct after the ETL is done.).

I think so. Can you elaborate on what that mapping table might look like based on the example I have?
–
8kbAug 29 '12 at 18:20

I am guessing that order number is really what ties everything together? That order number can occur multiple times in your order table to tie multiple products for the same or more than one customer? Or would order number only ever show once in the order table?
–
Chris AldrichAug 29 '12 at 19:09

In this example, I think the rules are one order per one customer and one product (not realistic, I know).
–
8kbAug 29 '12 at 19:14