Multiple Fact tables, Aggregate tables or a different approach

Hello,

We have the following sales related transaction tables. We are building a dimensional model around this sales module. My questions are on the design of the fact table.

1) Should there be one fact table with the lowest grain data (SalesOrderLineShipmentInventoryDetails) and then build aggregate fact tables for other levels?
or
2) Should there be multiple fact tables. One fact table combining SalesOrderHdr, SalesOrderLine and SalesOrderLineRep. The other fact table having details on the remaining 3 tables? There will be a parent child relationship between these fact tables.
or
3) A different approach.

Details


Transaction tables
--------------------
SalesOrderHdr
SalesOrderLine
SalesOrderLineRep
SalesOrderLineDeliveryDetails
SalesOrderLineShipmentDetails
SalesOrderLineShipmentInventoryDetails

The cardinality between the tables is 1 to n. 1 SalesOrderHdr has n SalesOrderLines, 1 SalesOrderLine has n SalesOrderLineReps and so on.

SalesOrderLineRep: This table has information on the sales representatives associated with each SalesOrderLine, and the percentage of profit the sales person gets.
SalesOrderLineDeliveryDetails: This table has information on the number of shipments that will be made to deliver the quantity in SalesOrderLine
SalesOrderLineShipmentDetails: This table has the details of the shipping carrier - UPS, FedEx, Packaging details etc
SalesOrderLineShipmentInventoryDetails: This table has the details on where the items where picked up from inventory. Details like warehouse #, Bin # and quantity picked up from each.

Example
1 Sales order header(SalesOrderHdr) could have 3 sales order lines (SalesOrderLine). If we are looking at sales order line #1. This line could have a quantity of 100 items that have been requested. There could be 2 sales representatives for this line, and salesRep#1 is getting 70% of the profit and salesRep #2 is getting 30% of the profit. The quantity 100 will be delivered in 4 shipments, each shipment will have a quantity of 25. 25 items for shipment 1 could have been got from warehouse #1, bin #5.

3 Sales order lines
Sales order line 1 has the following information
SalesOrderHeader: 1
SalesOrderLine: 11
Qty: 100
2 SalesReps
SalesRep #1 - 70% profit
SalesRep #2 - 30 % profit


Questions

1) Should there be one fact table with the lowest grain data (SalesOrderLineShipmentInventoryDetails) and then build aggregate fact tables for other levels?
or
2) Should there be multiple fact tables. One fact table combining SalesOrderHdr, SalesOrderLine and SalesOrderLineRep. The other fact table having details on the remaining 3 tables? There will be a parent child relationship between these fact tables.
or
3) Different approach.


Thank you

Quickly, as much as possible, I would recommend to shoot for a transaction fact table offering a full vision of all historical changes. You fact will be something like that:

For the sales rep ratio, only good if yours business can only support 2 sales rep. It the number of sales rep is variable (1,3,12 etc) then the notion of commission/ratio would have to be handle differently, either in the dimension or in a second fact). The rests stays valid.

Dim_order_date
Dim_delivery_date
Dim_dw_loaded_date
Dim_product
Dim _warehouse
Dim_sales_rep_#1
Dim_sales_rep_#2
Qty ordered
Qty shipped
Price


Where:
amount sold would be a derived object in your BI tool that calculate the (Qty ordered +Qty shipped * price)
Qty sold would be split in 1 column, the qty order and the qty delivered. When you deliver a batch, you substract it from the qty_order.

Ex of the transactions:

Dim_order_date;Dim_delivery_date;Dw_loaded_dt;Qty ordered;Qty shipped;Price
2009-08-27;2999-12-31;2009-08-27;100;0;100$
2009-08-27;2009-09-03;2009-09-03;-25;25;100$
2009-08-27;2009-09-15;2009-09-15;-75;75;100$


Then you can handle reversal record if some order are return or price change. Ex :

Dim_order_date ;Dim_delivery_date;Dw_loaded_dt ;Qty ordered ;Qty shipped ;Price
2009-08-27;2009-09-30;2009-09-30;0;-100;100$
2009-08-27;2009-09-30;2009-09-30;0;100;90$

Still far from perfect but you got the idea.

Good luck

Fact tables should reflect the business events that take place. The data should be collected at the lowest level of detail possible. So, sales (as reflected by the sales order) is one such event. Shipping another, and invoicing yet another. Each would have at least one fact table.

As you mentioned, the data for a particular event (such as sales) is, for most any modern system, available at various levels of detail for different purposes. There is always header and line (although you may be able to incorporate header level facts, if any, into a line level fact table) but there are further breakdowns from line, covering accounting, pricing, commissions, etc... These should also be implemented as different fact tables as their dimensionality is different. What you implement would depend on business requirements.

Aggregates that combine different events (such as an order-to-cash view) can be constructed later, after all the necessary facts have been built.

SalesOrderHdr
SalesOrderLine
SalesOrderLineRep...........!!!!!!!!!!!!!