Solved

Slowly Changing Fact Table?

Posted on 2009-07-16
6
1,527 Views
Last Modified: 2013-11-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...
0
Comment
Question by:jahworld
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:srnar
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 

Author Comment

by:jahworld
Comment Utility
@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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 8

Accepted Solution

by:
srnar earned 420 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
This article describes some very basic things about SQL Server filegroups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now