Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension Attribute or Fact Attribute

5 posters

Go down

Dimension Attribute or Fact Attribute Empty Dimension Attribute or Fact Attribute

Post  kinsun 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

Back to top Go down

Dimension Attribute or Fact Attribute Empty Re: Dimension Attribute or Fact Attribute

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Dimension Attribute or Fact Attribute Empty Re: Dimension Attribute or Fact Attribute

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Dimension Attribute or Fact Attribute Empty Re: Dimension Attribute or Fact Attribute

Post  hang 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

Back to top Go down

Dimension Attribute or Fact Attribute Empty Re: Dimension Attribute or Fact Attribute

Post  Vishy Mon Mar 26, 2012 2:58 am

Dimension Attribute or Fact Attribute C:\Documents and Settings\E20017242\Desktop\demo

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

Back to top Go down

Dimension Attribute or Fact Attribute Empty Re: Dimension Attribute or Fact Attribute

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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