Exception/Compliance Dimension For Accumulating Snapshot Fact

View previous topic View next topic Go down

Exception/Compliance Dimension For Accumulating Snapshot Fact

Post  jball on Fri Jul 08, 2011 12:55 am

I have an Accumulating Snapshot fact storing the flow of an ordering process that covers 8 events associated with fulfilling the order. I'm getting the event timestamps from the source. Some timestamps are not complete and some are incorrect (showing dates earlier than the event occurred) - causing lag calculations to be negative in some cases. The fact ties to an order dimension. There is also a business rule to flag a row as an exception if order fulfillment is past a certain number of days.

So I sometimes end up with just one timestamp being incorrect while the timestamps for the other 7 events are good, so I still need to keep the entire row of data in the accumulating snapshot. I currently have created 16 fields for exceptions and placed those fields in the existing order dimension. There is an exception field for each of the events, fields for each of the important lags, one for order fulfillment taking to long, and a flag indicating any type of exception. I wonder if this is the best approach. It seems to accommodate all the different types of exceptions. I wonder though if it's more appropriate to put these 16 exception fields in a junk dimension rather than in the order dimension. Or if there is a better way in general to do this.

Are there any standards on creating exception or compliance dimension for cases like this or in general?

Justin


jball

Posts : 5
Join date : 2011-07-08
Location : United States

View user profile

Back to top Go down

Re: Exception/Compliance Dimension For Accumulating Snapshot Fact

Post  hang on Fri Jul 08, 2011 5:13 pm

It looks to me the exception attributes should be in a junk dimension that is reference directly by the fact table instead of by order dimension as these exceptions are about order movement life cycle, and possibly subject to change as well as some of the errors might get corrected during the cycle.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Exception/Compliance Dimension For Accumulating Snapshot Fact

Post  jball on Fri Jul 08, 2011 5:24 pm

Ok, that makes sense. Thanks so much!

jball

Posts : 5
Join date : 2011-07-08
Location : United States

View user profile

Back to top Go down

Re: Exception/Compliance Dimension For Accumulating Snapshot Fact

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