Work Order / Customer Order Design - Dimension or Fact

I have a situation here that I can see both sides of the argument to - looking for the recommended approach.

Our ERP system utilizes Work Orders, which are a four part key, but for this example can be considered a single BK.

The Work Order has very little detail about itself directly, most fields on the associated table are aggregates of its secondary tables - Requirements, and Operations.

When designing the Star Schema, I am considering placing the Work Order in a Dimension as its value fits the What, When, Who scenario and is a business needed lookup field.
I would FK the Requirements and Operation tables to the dimension as usual. (Moreover there are other fact tables that could leverage a FK to to a WO Dim.

My hang up here is that in the majority of cases, the Work Order dimension will be a 1:1 with the fact tables. 1 WO dim record for 1 Requirement record and 1 Operation record.
Is this the recommended approach? Or shall I DD the value into each fact table and then use separate queries with merge to link the fact tables for purposes of reporting?

---------------------

I have a similar issue with the Customer Order tables. Similar to the above, the Customer Order can be referenced by numerous other fact tables. The only difference is that in this case, by Customer Line grain, there is a unique text comment field which is always populated. Because of its text data type I want to place in a dimension, but placing a dimension of grain Customer Order Line - will give me an exact 1:1 with its fact table - i.e. Shipped Order Lines.

Suggestions?

Thank you.

What attributes do you have that would go into the WO Dim? If it is just a WO number then model it as a DD in the fact(s); if it has a number of attributes that you need to report on then then you'll have to create a proper Dim for it.

Regards

The WO has a four part key that I would like to get down to a simple SK, but moreover it holds agg values at the WO level that would be the summary of the fact tables Requirements and Operations.
These include total times, costing, etc. I was thinking of treating the WO dim as a type 1.

Thoughts?

Hi - these seem to be measures and therefore belong in a fact table and not a dimension. You say they are aggregates of data in the Requirements and Operations fact tables so I would expect these aggregates to be calculated, as needed, when you run your reports or, if this causes an unacceptable performance hit, pre-calculated in an aggregate fact table

Regards

Okay, yes I think that is the way I will go. All other attributes are either FK's to other dims, or measures themselves.

-----------

What do you suggest regarding the Customer Order portion? This is also got me a little confused due to the fact that all Customer Order Lines have a populated free text field (always populated). I would prefer to get this out of a fact table and into a dimension, but if I do that this would be a 1:1 with dimCustomerLineDescription and the fact table itself.

Thoughts?

If you have to have this free text field - and as presumably it has no analytical value I would argue strongly against including it in an analytical data warehouse (but it's an argument I would expect to lose with my users!) - then just put it in a comments dimension. You can use this for free text occurring anywhere else in your model. It will be 1:1 with your fact but there's not much you can do about that

Agreed with Nick, to treat WO as a fact. Actually most of ordering processes are modeled as fact tables, or more specifically accumulating snapshot facts. That's why the relevant measures/aggregates need to be frequently updated to reflect the current ordering status/progress. Normally a number of milestone date keys are also included in this sort of fact table.

I think the confusing part when modeling WO as a dimension is that the order number is referenced in many places, typically in other fact tables. It would be less counfusing if you think of WO number as something just like transaction number. So it's just a degenerate dimension DD and you would simply leave it in other referencing fact tables on its own. If you need other details associated to WO, the DD connects to the accumulating snapshot fact table that gives you a complete context around WO.