Order fulfillment accumulating fact - problems with status movement

View previous topic View next topic Go down

Order fulfillment accumulating fact - problems with status movement

Post  rannandale on Wed Jul 27, 2011 9:39 am

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

rannandale

Posts : 2
Join date : 2011-07-27

View user profile

Back to top Go down

Re: Order fulfillment accumulating fact - problems with status movement

Post  BoxesAndLines on Wed Jul 27, 2011 5:30 pm

What happens if you just update when the milestone resets?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Order fulfillment accumulating fact - problems with status movement

Post  rannandale on Thu Jul 28, 2011 5:14 am

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!

rannandale

Posts : 2
Join date : 2011-07-27

View user profile

Back to top Go down

Re: Order fulfillment accumulating fact - problems with status movement

Post  BoxesAndLines on Thu Jul 28, 2011 8:20 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Order fulfillment accumulating fact - problems with status movement

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum