Slowly Changing Fact Table?
Posted on 2009-07-16
Looking for a some ideas:
I work for transportation company, and I'm building an EDW from scratch. The first segment of the EDW is that they want to track passenger: Reservations, Ticket Purchases, Actual Trip, Closed (refunded or reissued). These are looked at in the organization as separate entities, but in reality they are simply different "stages" of the passenger. These stages share about 85% of data elements and 90% of dimensions.
The issue that I'm struggling with is that as the reservation moves through the stages, there are more data elements, more measures and more dimensions.
Therefore, if I put them in one cube, this would cause a significant number of "Unknown" members in dimensions and zero value measures for the earlier stages as these won't be "complete" until reaching the final stages.
I know I create measure groups for the different stages, but on the dimension side?
My instincts are the they are so similar that they should be one cube, but the perfectionist in me is struggling with the empty members.
Since I don't have anyone here to expand the idea with, I thought I would come here. Any thoughts you might have could spur some innovative thinking!! Look forward to hearing from you...