Need to merge fact tables

[mr_neal]

(Summary)
I have a Contact fact table containing contact history and a Orders fact table containing the history of the order. Contacts can be by phone or in-person (call_type). So my reporting requirement is to show contacts made by call_type and the related order record. Currently we have to perform a function on the orders fact that returns greatest update date where the order ids of each fact match.

One caveat is that when a contact is made, the order may not reflect the latest contact, like if someone is just checking on the status. My thought there was to add contact_id to the order fact, but then I would have to insert a order fact record every time a contact is made.

Well, I'm new to this DW world, so could someone tell me what I'm missing. It seems like I'm joining two fact tables. Please help if possible. :0

[hang]

There are two different fact tables in your case. The first one contains order fact, and the second one order-calls. Contact should be a SCD dimension that contains all the relevant contact details. Order number would be a degenerate dimension that connects the two fact tables.

[mr_neal]

Order number would be a degenerate dimension that connects the two fact tables.

Hang, I am already using order number key in both fact tables. However, this does not solve my problem since an order can have multiple call types. So my report requirement is to show what happened during the last contact by call type. Below is sample data. If you look at the Contact row 3, there is no match to Order using order_no, call_type and date. This was caveat in original mail. (Sometimes a call is made about an order, but the status does not change and therefore, an Order record is not inserted.) In order to get results, you have to select Max(date) from Order where order_no's match and call_types match.

(Order)
order action call type date current
----- -------- ------------- ---------- -------
001 open in-person 02-FEB-12 N
001 on-hold in-person 03-FEB-12 N
001 closed phone 05-FEB-12 Y

(Contact)
order call type date
----- ------------- ----------
001 in-person 02-FEB-12
001 in-person 03-FEB-12
001 in-person 04-FEB-12 (This contact did not result in order record insert)
001 phone 05-FEB-12

[hang]

If your order fact table is to track the order status, then I would use effective date pairs so that you may use date range to match the two facts. For current status, you may use current='Y' instead of Max(date).

[mr_neal]

If your order fact table is to track the order status, then I would use effective date pairs so that you may use date range to match the two facts. For current status, you may use current='Y' instead of Max(date).

Thanks again for your reply. Yes, my order fact is to track order status. We already have effective date and current flag on the order fact table. Using current flag is fine when we only care about the latest call type. However, we also need to provide analysis on what happened during the prior calls or visits and the reason an order was not completed. In my sample data, when current flag is not Y, I still need to get the combination of call type, order number and max(effective date). Since there could be multiple records on this order for the in-person call type, I need to get max(effective date) to return the correct row. Currently the only key that relates the Order to the Contact is the Order Primary Key and I don't think it would be acceptable to add that key to the Contact Fact. Maybe I should add a flag on the Order to indicate the latest record for that call type? Is there something fundamental I am missing here? It seems like these situations would occur frequently in datawarehouse modeling.

[hang]

If you think of your order fact as a transaction dimension, you could have a SK in the table and timestamp (effective start/end date) any change on order status, Now you can put the SK in the contact fact table.

The concept of order transaction dimension seems a bit strange as order itself is somewhat a fact when the focus is on the order transaction. But since your focus is on the order contact event fact, order with corresponding status would become contextual information for that fact. I guess in this case, it would be valid to treat order with changing status as a dimension.

[Sponsored content]