Solved

Slowly Changing Fact Table?

Posted on 2009-07-16
6
1,536 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
free version of MSSQL for over 10GB DB 19 91
SQL replication over high latency link 10 67
Database ERD 4 81
Shrink multiple databases at once 4 57
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

726 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