Modelling WorkOrders

Hi

I am looking for some advice on how best to model Work Orders and their associated costs.

A work order can have the following costs: Labour, Material, Service and Tools both actual and planned. The source system stores each cost type in a seperate transaction table resutling in the costs residing in 8 transaction tables. A work order may have zero or many cost transactions. Each cost type has common dimensions and some that are specific to the type of cost e.g. LabourCost has a person, Service cost has a vendor

The source WorkOrder table has many attributes e.g. Work Order number, Work Order Class, Work Type, Work Order Status, A number of dates - start, end, planned start end, a Location and an Asset. It is required to aggregate the work order costs up the location and asset hierarchies.

A work order is also related to a Purchase request, a Purchase Order and a AP Invoice

With regards to the design I am considering the following:

A FactWorkOrder table: This will contain all work orders in the system and a summary of all the cost transactions from each of the FactCost tables. This FactWorkOrder table will include the degenerate work order number. Should I also include the Work Order Class, Work Type, Work Order Status as degenerate attributes or create seperate dimension tables for them? The FactWorkOrder will also be related to a DimLocation and DimAsset. The FactWorkOrder table will contain a row for each work order number.

A seperate Fact table for each of the Costs: My uncertainty is how to handle the relationship with the work order and it's related attributes. Do I bring down all the atrtibutes down from the FactWorkOrder on to each factCost table e.g. Work Order Class, Work Type, Work Order Status, Dates etc. Or do I create a DimWorkORder table (which would be the same grain as the factWorkOrder) and put the WorkOrderId field on the FactCost tables? The costs will be related to both the Asset and Location Dimension

It is also required to analyse the purchase requests and purchase orders by the Work Order attributes. If I create a DimWorkOrder table I could simply add the workorderid as foreign key. Or should I just add the work order number as a degenerate and bring across any other related dimensions from the work order to enable PRs and POs to be analysed from a work order perspective?

Any thoughts or suggestions would be greatly appreciated.

Cheers
Kevin

Hi Kevin,

Looks to me like you've got a good plan in mind.

When I've built a workorder / maintenance DW solution, I've had both facts and dimensions for work orders.

The Work order dimension would include those attributes don't exist in other dimensions, but still might be needed for reporting. (e.g. Work Order Description).Often attributes that are maintained on the work order by a dropdown are candidates to be their own dimension (e.g. WorkType).

The FactWorkOrder is good example of an accumulating snapshot fact table. It should be used for reporting/analysis on the work orders themselves, not necessarily the costs. (e.g. How many Open Work Orders by Location, How many work orders created last month, How many have been open more than 30 days etc.). This fact would be associated with the work order dimension, plus other dimensions (e.g. DimWorkOrderStatus, DimLocation,DimWorkType etc).

Each of your cost related facts (Purchases, Receipts, Labour, Inventory etc) will most likely be their own facts, with some dimensions conforming with the work order fact, other unique to the cost measure (e.g. Supplier, Employee etc).

I think Andrew has mapped out a good solution for this important part of workflow management.

Just be clear about the Work order dimension. It contains almost nothing but the description for drill down rather than analytic purpose. All other attributes that seemingly belong to work order dimension should live in their respective standalone dimensions connected to work order fact table directly. These small dimensions may be referenced as conformed dimensions in other fact tables.

Use work order NK as DD in other fact tables instead of work order SK for the dimension, as most likely, the work order accumulating snapshot fact, with work order NK as its PK, will bring across all the usefull dimensions around work order.

Similar to work order, purchase order and purchase request should be modeled in their individual fact table with NK as DD which gives you a very convenient conformance point between fact tables.

Thanks for the feedback guys, it is greatly appreciated and re-assuring.

Cheers