Slowly Changing Fact Table?

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...
jahworldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

srnarCommented:
It often helps to share your opinion.

You don't have to use all dimensions for every measure group. If a dimension is not used than it is by default [all member] selected from a not binded dimension.

You can also use some specific dimension where some branches have more members as it is natural from your business view.  

You can also bind a measure group to a different level (than the key level) of a dimension if it has no meaning - for example you don't want to artificially divide a value.

Hope it helps.
0
Mark WillsTopic AdvisorCommented:
If in reality they are just different stages of the passenger then what are the additional measures, and what are the new dimensions ?

Isn't "Stage" just another dimension  ? Or is that being overly simplistic ?
0
jahworldAuthor Commented:
@Mark Wills, Yes.  In this scenario Stage is without doubt another dimension.

@srnar, that is essentially what I was thinking.  I guess what I'm looking for is confirmation that the direction I'm going is the correct one as I haven't worked with an "evolving" data stream from a BI perspective.  All the cubes I've designed and developed in the past were more static.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

srnarCommented:
Different measure groups are created for new measures (especially for distinct measures). The more measure groups you have the more complex the cube you create. Keep in mind that all is XML based and large cubes have surprisingly big overhead - we once run into large XML structures on project with many distinct measures.

Choose the most simple solution with one measure group although you are not happy with the number of the dimensions not used. You can implement perspectives if you want to create virtual cubes where there are more simple relationships.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Think I am still perplexed by the supposed complexity...

Stage as a distinct dimension is really a "state" of passenger. They have a reservation, are on-route, or finished, they cannot be all things at the same time. As a diemsnion, you might consider it a hierarchy, in so much as they must have been "en-route" before they finish...

It sounds more like a few measures might need to be conditionally populated, and the fact table for the person has a change of "state" rather than adding additional stages for the passenger.

In some regards, it might not be so different from an inventory sales model where product is ordered, packed, shipped, delivered, invoiced.

Now "tracking" a passenger is really through the use of measure not the "stage" diemension, and really those measure are applicable to multiple members of the "stage" dimension. It could well be the event that populates the different measure gives rise to the "stage" dimension.

Not sure what or how the passengers are being tracked, but for example, the cost of ticket remains the same cost of ticket regardless of stage, and the date of reservation may well be needed for "days to travel" regardless ot the trip having been completed. I guess some of it really does depend on the interactions of the various "stages" being tracked and any dependancy - it does indeed become more complex if subsequent stages are not dependant on prior stages.

So, might be worthwhile sitting back for a moment and examine the business requirement rather than the structure and then decide how it is best to construct.
0
Mark WillsTopic AdvisorCommented:
I would enjoy hearing back how you have actually resolved this issue, if you get the chance, and if you are so inclined, would be appreciated...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.