Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the correct site.

I am designing a database for a rudimentary BI system. At this moment I have hit a wall, which is this (explaining using dummy data):

Suppose my fact table contains this information:

John Doe flew from LAX to ATL on 1 Nov in flight AB-123

The dimensions and their attributes are:

  • Flyer - name, club
  • Airport - city, code
  • Date - year, month, date
  • Flight - code, std, delay, price

Now, from this I can easily generate a report like this:

Airport --> LAX  DFW  ORD  ATL Total
Gold         50   40   10   25   125
Silver      240  300   95  140   775
Bronze     1000 1500  800 1800  5100
Total      1290 1840  905 1965  6000

Using a query like:

select fd.club, ad.code, count(f1.id) from flyer fd, airport ad, fact1 f1
where fd.id = f1.fid and ad.id = f1.aid and month(f1.date) = 10
group by f1.club, ad.code;

But my problem comes from the fact that the "club" status of a flyer is a moving target. A flyer who is in Gold today could have been in Silver in October, so I am counting him in the incorrect group here. Thus, I imagine I need a separate fact table like this:

 John Doe entered Bronze club on 8/15
 John Doe entered Silver club on 10/20
 ...

"Club" drops out as an attribute of the original flyer dimension. Instead, a new club dimension emerges.

And then to generate the report I need, I join these two fact tables.

Am I on the right track? Or is there an alternative, simpler solution to this? One alternative I could think of is to include the club in the original fact table, handling it during the ETL process. So the fact becomes:

John Doe of Silver Club flew from LAX to ATL on 1 Nov in flight AB-123

Please let me know which approach is better, or if there is a third one.

share|improve this question
    
How often does a flyer's club change? –  Nick Chammas Nov 30 '11 at 19:04

3 Answers 3

up vote 2 down vote accepted

The way to do this is what Kimball called a Type-2 or Type-6 slowly changing dimension.. Essentially, a type-2 SCD has a synthetic dimension key, and a unique key consisting of the natural key of the underlying entity (in this case the flyer) and an 'effective from' date. The synthetic key is joined against the fact table, so you can attach it with a simple equi-join (i.e. you don't have to filter by date range in the query).

All the attributes (e.g. club in this case) are attributes of the flyer. If one of these attributes changes, a new row is created on the dimension recording the new state, effective from the date of the change.

The type-6 is like an ordinary type 2, but has a self-join to the current version of the row. Whenever a new row is created for a given natural key all rows for that natural key are updated with the self-join to the current row. You may or may not need this functionality.

You can query an as-at status by joining the fact tables against the row that was recorded on them - i.e. the state that was current. If you have a type-6 the current status can be queried through the self-join, which can also be materialised on the fact table if desired.

This data will also play nicely with ad-hoc reporting tools and cubes, although implementing complex cube hiererchies on a slowly changing dimension is a bit fiddly (you need to keep placeholders for the natural keys of the hierarchy levels and combinations over time).

A good point to start would be a google search on "type 2 slowly changing dimension"

share|improve this answer
    
This seems to solve my problem. I read up about SCDs, plus have already ordered (last week) Kimball's book. Thanks! –  ObiObi Dec 1 '11 at 16:59
    
+1 for a more general purpose approach. There is room for debate over whether SCD is overkill. I use them all the time when you have an unpredictable mix of management and BI reporting to do out of a datamart. If the reporting requirement is simple enough, star schema with denormalization is often adequate and harder for novice report writers to mess up. Type 2 SCD is apparently hard to get one's mind around for some app devs and power users I've worked with. –  Joel Brown Dec 1 '11 at 18:12
    
@ObiObi - If you're using SQL Server 2005+ I've got a type 2 SCD handler lying about that you can use. –  ConcernedOfTunbridgeWells Dec 1 '11 at 19:55

I would keep a separate table with

FlyerName, FlyerClub, StartDate, EndDate

This way you track changes over time, and can know at any given point what club someone was in.

The current record would have an EndDate of NULL.

share|improve this answer
    
A history table like this would be useful to feed a datamart but it is not generally used within the datamart itself when it is built using a star schema as implied by OP. Also, as an aside, end date of NULL is a religious war issue. Lots of people would argue for end date of max collating. –  Joel Brown Dec 1 '11 at 0:44
    
@JoelBrown I have a lot fewer issues with datetime datatypes having NULL than others - it's a lot less likely to be used in a NOT IN or IN clause –  JNK Dec 1 '11 at 3:08
1  
To me NULL for "don't know" makes perfect sense. However, an important advantage of max collating for the end date in a date range (or min collating for the start date) is that it makes finding date range overlaps and ranges that encompass a point in time much, much easier. –  Joel Brown Dec 1 '11 at 5:09
    
Depends on the usage. Sometimes a large value such as 9000-01-01 is quite useful for the last range in a sequence. If you want to match records by date range then you can query this more efficiently (i.e. easier to make s-arg-able) than a table that marks the last 'effective to' with NULL. –  ConcernedOfTunbridgeWells Dec 1 '11 at 13:32
    
In either case the design suggestion doesn't depend on the use of NULL :) –  JNK Dec 1 '11 at 13:36

In a datamart you need to denormalize time variant attributes to your fact table.

Your transactional source database will have the flyer's club level on the flyer table, or possibly in a dated history table related to flyer as suggested by JNK.

In your datamart, you need to apply the current club level of each particular flyer to the fact record that brings together flyer, flight, date, (etc)

In your case, club is a time variant property of flyer, but the fact you are interested in is the combination of a flyer and a flight. Therefore you need to record the FlyerClub on the flight transaction (fact table). This will work as long as you don't let flyers change clubs in mid-flight.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.