Solved

Slowly Changing Fact Table?

Posted on 2009-07-16
6
1,529 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
ID: 24876637
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
ID: 24877948
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
ID: 24881969
@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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 8

Accepted Solution

by:
srnar earned 420 total points
ID: 24890206
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
ID: 24890738
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
ID: 24890965
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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…

932 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

17 Experts available now in Live!

Get 1:1 Help Now