How to cope with multiply changing history

View previous topic View next topic Go down

How to cope with multiply changing history

Post  malc001 on Thu Aug 06, 2009 3:40 am

We've come across a very interesting situation and would appreciate any ideas on how to deal with this.

In our Delivery Booking System (DBS) the status of a record (delivery) will change retrospectively, this may happen up to 4 weeks after the delivery physically took place if the Customers calls in and reports an issue. In addition the status of a record can change over time and it is essential for the business to see all changes in status so they can judge the actual effort a delivery takes.

Situation
There morning and evening shifts for deliveries. The evening shift finishes too late for information about what deliveries failed to be added to the system. Overnight all deliveries made by the afternoon shift where failure was not recorded are set to 'Completed'. The following day those deliveries which did fail have the status changed to 'Failed'.
The 'Completed' status is incorrect so I think we should overwrite the original record with a 'Failed' status as this is actually what happened it's just the system didn't know that at the time. Deliveries can be failed up to four weeks after initially set to Completed.

Changing records
A delivery can go through a limitless number of status changes and the business wants to be able to track each change, in particular the reason for each failure to try to identify patterns.
Deliveries are made up of services and/or goods. There can be many services and/or goods and each unit of service and/or goods can have a different status and reason for failure at any given point in time.

Questions
1. How to deal with changing history to make sure we don't lose any granularity of data but also don't have constantly changing historical figures?
2. How do we model the ever changing statuses? Is it as simple as one row for each?

Any thoughts and comments are greatly appreciated.

malc001

Posts : 1
Join date : 2009-02-09
Age : 40
Location : London

View user profile

Back to top Go down

Re: How to cope with multiply changing history

Post  BoxesAndLines on Thu Aug 06, 2009 9:34 am

1. The easy way to handle this is to update the row. This will change numbers though. Ideally most deliveries are successful and this is a small number.
2. You need a transaction fact at the delivery+service+status grain if you want to track all the status changes and failure reasons.

Ever notice the handhelds that FedEx and UPS use? They get real time updates on the status of their deliveries. Of course the customer can always call in the next day to file an insurance claim. That shouldn't impact the successful completion of a delivery though. It seems as though your workflow delivery system needs to recognize that an insurance or damage claim is just another state after completion.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

How to cope with multiply changing history

Post  DanColbert on Thu Aug 06, 2009 11:08 am

It looks like the front-end process is broken - not the way you are recording it in the DW. Is it possible to fix the front end to correctly handle those "unknown" statuses? Wouldn't it be better to have the system wait until it actually knows the status of the attempted delivery?

Regardless, here's how we handle the "pipeline" of our order system.

In the fact table, we have a date dimension reference for each status that is analytically interesting to us. For example, OrderDateKey, BackorderDateKey, ShippedDateKey, OrderCancelledDateKey. Each of these represents a status of the order, and when the order enters each of those statuses (across time) we populate the date key. Before it is set we assign all date key references to a -2 for "Not Set".

So when we have an order that is cancelled, we can slice-and-dice on rows with an OrderDateKey of x and a CancelledDateKey of y.

Each status that you are interested in can become a date dimension reference that gets set at the time of the status. The "fact" of a delivery doesn't change just because the status changes. It still counts.

It also helps you avoid having analytical results that change over time - unless there is actually something that changed.

Hopefully I haven't completely missed the mark, but we designed things here with foreknowledge of the exact problem you're facing now.

Hope this helps!

Dan
avatar
DanColbert

Posts : 11
Join date : 2009-02-03
Age : 48

View user profile

Back to top Go down

Re: How to cope with multiply changing history

Post  AmyB on Mon Aug 10, 2009 5:25 am

Thanks for that. The one problem with the proposed solution is that the record can go through the same state multiple times and because there is no limit on the number of times a process can be attempted and failed our users want to know when each attempt (and failure, with reason) happened. E.g. the delivery is scheduled, the customer is not in so the delivery fails; the delivery is rescheduled and again fails as the goods are damaged, it's then rescheduled again and fails again because the wrong goods are sent. We could have a first scheduled date, first failed date (with reason 'customer not at home'), second scheduled date, second failed date (with reason 'goods damaged'), third scheduled date, third failuure date (with reason ' wrong goods') but has anyone got any idea how we could accomodate the unlimited number of states?

AmyB

Posts : 2
Join date : 2009-08-10

View user profile

Back to top Go down

Agree with BoxAndLines

Post  Omaha on Mon Aug 10, 2009 8:33 am

I don't see this as a broken source system or even an uncommon problem. The same thing happens with health insurance claims. A particular line item may be processed multiple times as it gets rejected/negotiated/paid. What changes is the status, the processed date and the various claim amounts.

I agree with BoxAndLines. You are one level of granularity too high.

The fact table should be at the status level, not the delivery level. When you summarize all the facts (status changes) associated with a specific delivery, it sums up to the total delivery. You have a fact row for each status change, not each delivery.

As for the "Unlimited number of reasons", you could create a reasons dimension and use some lexical/fuzzy tools to categorize the text. If you need to preserve the exact text for each delivery attempt (not so great) so users can look up a single status record, now you're mixing ODS functionality and DW functionality. If you're forced to do that, you could create a separate "text" table to contain the status reason with a 1:1 linkage with the fact record (a one to one dimension table). The reason I'd put it in a separate table is to conserve RDBMS memory cache when I'm doing analysis and start pulling fact pages from disk to cache.


Last edited by Omaha on Mon Aug 10, 2009 8:51 am; edited 1 time in total

Omaha

Posts : 6
Join date : 2009-08-07

View user profile

Back to top Go down

Re: How to cope with multiply changing history

Post  AmyB on Mon Aug 10, 2009 8:42 am

Thanks. I'm pretty sure that the grain needs to be delivery/status/date so we don't lose any data. We can then aggregate meaningful measures which will be used in most of the reports.

AmyB

Posts : 2
Join date : 2009-08-10

View user profile

Back to top Go down

Re: How to cope with multiply changing history

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