How best to model Timesheet facts against Sales Order facts

[mosesofj]

Apologies for what must seem like a simple question...

In our company we track sales orders and time booked to sales orders. As I undestand it, both of these should be business processes in their own right and there would therefore be 2 fact tables:

1) SalesOrders (containing quantities, costs, customer sales order ref, sales order description? etc.)
2) Timesheet (containing hours spent / pre-sales hours spent, sales order ref, customer? etc.)

Each of these has multiple confirmed dimensions (e.g. Staff Dimension, possibly Customer?).

In the OLTP system, SalesOrders actually consist of header data (customer, date etc.) and multiple SalesOrder records (item, quantity, price etc.) so perhaps my thinking is all wrong, and SalesOrders is actually a dimension and SalesOrderLine is the fact?

My question relates to how to design the model in such a way that I can answer questions like:

* Which sales orders have had the most time booked to them?
* In a give period of time, how much pre-sales time has been spent per sales order (with the sales order ref, description and company)?
* Are there any sales orders with only pre-sales time booked (no chargeable time booked)

I understand that I shouldn't be joining 2 fact tables together, but my (flawed) design wants me to do that.

Any help gratefully received!

Dave Hughes

[ngalemmo]

Nothing wrong with the design. As for 'joining' facts, you don't join facts in the technical sense of a relational join. The reason is that you must assume any such join is a many-to-many relationship between the two tables. Instead you 'combine' fact tables. You either aggregate the two tables along common dimensions and join on those dimensions, or you perform a union of the two tables.