Logic and Setup
An order is considered a FAILURE if one or more lines on the order matches either (or both) of the following:
• The order line has an actual dispatch date later than the expected dispatch date
• The order line has an actual dispatch quantity less than the expected dispatch quantity
All orders dispatched are recorded in the ORDERS fact table.
Orders are categorised by ORDER TYPE, DELIVERY TYPE, and ORGANISATION.
All order lines which failed are recorded in the FAILURE LINES fact table.
Each failure line is attributed a REASON. One order may fail for multiple reasons.
I have posted the image of my model and I need to report on the following:-
My requirements are :-
• Orders Dispatched: Distinct Count of Order References in Orders fact table
• Orders Failed: Distinct Count of Order References in Failure Lines fact table
• Failure %: Orders Failed / Orders Dispatched
• Compliance %: (Orders Dispatched - Orders Failed) / Orders Dispatched
I m struggling with the relationship between the 2 Fact Tables. Would I be better of flattening the the Failures Table into the Orders Table? If not should I relate the two Fact tables via bridge Table that just contains Order Reference or something else? I really would be thankful for any help. I appreciate it is a big ask.