Hi there,
I'm trying to follow the classic Kimball method for a "order header/detail" combined fact table. In this particular fact table, I wanted to do a gut-check with any dimensional modelers/designers for this sort of a star schema. The crux of the issue is that the individual order lines don't all necessarily have the same set of dimensions. For example, one line might be labor, the next two might be parts and the next one might be for commercial charges. This is for vehicle maintenance, so imagine your quote from the mechanic for a car (or in this case bus) maintenance order.
A very quick abstraction of the fact table where we're deriving the data from 3rd part OLTP database might be something like this:
declare @fact_header_detail table
(dim_order_date_key int,
dim_vehicle_key int,
dim_employee_key int,
dim_part_key int,
dim_commercial_vendor_key int,
record_type varchar(30),
unit_of_measure varchar(10),
quantity float,
unit_cost float,
total_cost float
)
After inserting 1 work order with 4 lines of detail, you can see you'll never have a dim_employee_key and dim_part_key on the same line. There's just no good way I can think of to collapse parts and labor onto the same line, let alone commercial charges unless you take out the atomic grain (e.g., part numbers, employees etc) Any thoughts? Here's some sample output:
Any advice would be much appreciated!
If I take a look at the record type then I would make a fact table for each type, like factWorkOrder_Hours, factWorkOrder_Parts etc.. Based on your limited data I would create a boolean commercial and if the boolean = true your commercial_vendor_ket should not be 0. In that case you can easily select all hours and make a split on commercial and non-commercial.
AdvantureWorks database is an example database for a datawarehouse and they have also salesorders and weborders. Maybe you can take a look at that.
I've looked at AdventureworksDW but don't think I ever formed the correllary. That's a great suggestion. I was hoping we could derive a complete set of metrics at the same grain (line item) but it's a good point. I'll take a look at that method. I just know that almost every analysis is going to eventually have to drill down to a complete work order, so that would be a lot of drill-across queries; which is what I was trying to avoid. Thanks for the suggestion.
Actually, Rogier, to be clear, if I were to separate between labor and parts, I'd have to drill across both facts in order to summarize a complete work order. In that case, you'd never be able to get a complete work order cost without including both facts. If every query will require a drill across routine, would it then not make sense to pull both facts into one? One key distinction I think between this data set and the facts in Adventureworks is that Adventureworks shows discrete sales orders. These line items are all part of the same "work" order...so one work order can have parts, labor, and commercial charges.
I would add the totals in the fact_header. I would have a totalcost, totalcost_labor and totalcost_parts in the fact_header, 1 row for each order_no.
Ok - Kimball pushes against having a separate header fact, but I suppose that makes sense. The header data already has the aggregates too so it's pretty easy to do. Thanks for your insights!
