Dimension Attribute or Fact Attribute

View previous topic View next topic Go down

Dimension Attribute or Fact Attribute

Post  kinsun on Thu Mar 22, 2012 1:30 pm

Dear all,

I am struggling on the design of a dimensional model which is to measure the shuttle bus usage:

Current Thinking:
1) Dimension: D_DATE, D_TIME_OF_DAY, D_SHUTTLE, D_STATION
2) Fact: F_SHUTTLE_USAGE

D_SHUTTLE
=========
SK
Shuttle Bus Number
Departure Station
Arrival Station
Departure Date
Departure Time
Arrival Date
Arrival Time

D_STATION
=========
SK
Station
Station Grouping

F_SHUTTLE_USAGE
===============
D1. Shuttle SK
D2. Shuttle Departure Date SK
D3. Shuttle Departure Time SK
D4. Shuttle Arrival Date SK
D5. Shuttle Arrival Time SK
D6. Departure Station SK
D7. Arrival Station SK
F1. Shuttle Running Time in Minutes


My question is, as you might notice, those D2 to D7 fact attributes are also the attributes in the D_SHUTTLE dimension. Yet there is really a need, say, to analyze from the departure station or departure date perspective.

So does I make a sensible design?

Appreciate any comment from the experts. Thanks!

kinsun

Posts : 6
Join date : 2012-03-22

View user profile

Back to top Go down

Re: Dimension Attribute or Fact Attribute

Post  BoxesAndLines on Thu Mar 22, 2012 4:10 pm

This model needs some work. Don't you care about the passenger count? Pull dates of of the Shuttle dimension and only put on the fact table. Pull location information out of shuttle dimension and put in a location dimension. That should clean up the model redundancy.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimension Attribute or Fact Attribute

Post  ngalemmo on Thu Mar 22, 2012 4:14 pm

The shuttle dimension doesn't make much sense (if 'shuttle' represents a vehicle, then the attributes are all wrong). You may want a route dimension, a vehicle dimension, and a driver dimension.

The fact should have a run number and stop number, as degenerate dimensions, which is assigned from the beginning of a route.

An arrival and departure station doesn't make sense either, since the departure station should be the previous arrival station. No?

You may also want to include distance traveled (probably based on a static lookup table, unless very large earthquakes are a frequent problem) as a measure.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimension Attribute or Fact Attribute

Post  hang on Fri Mar 23, 2012 6:54 am

I wonder if you have predetermined (scheduled) trip details that could be different from that in actual shuttle run (transaction). If so, you need to have a coverage fact table holding the scheduled details, and another transaction fact table to store actually shuttled details so that you could not only aggregate the time but also work out utilisations. Otherwise just do as suggested by B&L and ngalemmo.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimension Attribute or Fact Attribute

Post  Vishy on Mon Mar 26, 2012 2:58 am



When vehicle is leaving the source station, you have above mentioned dim coming into picture.
Fact will be obvious.
You can have coverage table also as per you need.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Dimension Attribute or Fact Attribute

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum