Order fulfillment accumulating fact - problems with status movement

Hi there,

The is the first post I'm making here, so I'm just going to jump right in.

We are busy with a first pass model for our EDW using dimensional modeling. One of the fact tables tracks the milestones for an order -from Pending, to In Progress, to Installed, to Rework (if something went wrong) to Completed. I thought we should use an accumulating fact, because you just make an entry at each milestone for the day on which it happened, being an FK to the Date Dimension.

As with accumulating facts, the idea is obviously then to have one entry per order line. The problem that I've got is, that it's not abnormal for the source system to have orders regress in status, mainly from something like Installed to In Progress - this produces a second entry because one unique order has gone into In Progress twice. This obviously causes double (or more) counting and is not the desired effect.

I'm hoping some of you have had to deal with this issue before, and can give me some tips on how to deal with it? I've thought of a couple ideas but they don't cover everything, e.g.:
Use an SCD2 status dimension - the problem with this is that you will have n number of orders, going through a (potential) total of five statusses, meaning you'd have ~ n * 5 rows in the dimension, where n would be the count of rows in the fact table.
Use SCD2 in the fact table? (not sure if this is good practice) based on the status attribute.

In both examples the fact table is no longer an accumulating fact, which brings in a couple of challenges in terms of stuff that previously would've been easy e.g. "How long does it take, on average, for an order to go from pending to complete?" - in an accumulating fact, I can just do a value of avg(Order_CompleteFK - Order_PendingFK )

Excuse me if I ramble a bit, but this really has my mind going for a loop.

Any comments/help/suggestions would be highly appreciated.

Thanks,
Riaan

What happens if you just update when the milestone resets?

Thanks for the reply!

I've thought about doing something like that, the problem comes in that business might want to do analysis on these events.

Which, I suppose, you could then pull out of an audit dimension...

It's probably worth trialing...

Thanks!

It's either reset the date or capture a new row. If you add a new row, you can add a status dimension to highlight active or inactive orders or just add an indicator column so you can easily eliminate inactive orders. You will also need to address the natural primary key as well. Perhaps you could add a reset_date that equals the order date if all goes well. If you reset a date column, set the reset_date to the current date. As long as no order resets twice in a day, you're golden.