How best to model Timesheet facts against Sales Order facts

View previous topic View next topic Go down

How best to model Timesheet facts against Sales Order facts

Post  mosesofj on Tue Oct 18, 2011 5:17 am

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

mosesofj

Posts : 1
Join date : 2011-10-18

View user profile

Back to top Go down

Re: How best to model Timesheet facts against Sales Order facts

Post  ngalemmo on Tue Oct 18, 2011 10:12 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum