Dimension Attribute or Fact Attribute

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!

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.

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.

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.

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.