Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Slowly Changing Fact Table?

Posted on 2009-07-16
6
Medium Priority
?
1,544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Accepted Solution

by:
srnar earned 1680 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

610 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