2

I'm building a data warehouse based on NFL statistics for educational purposes as I learn this paradigm - I have the following modelling problem

Players can play for different teams different years and similarly coaches can coach different teams different years of their career; players could also potentially play different positions in different years too (rare but possible)

What is the best way to model the assignment between players, coaches and teams for different years?

Would I store the different years' roster information in a dimension? eg DimTeamRoster which would have a TimeKey, TeamKey and CoachKey (since a team can only have one Head Coach) with a FactTeamRoster with a TeamRosterKey, PlayerKey, Positionkey

Or would I have a FactTeamRoster which would have a TimeKey, TeamKey, PlayerKey, PositionKey? But then would this approach make sense because this fact-table wouldn't really be storing any measures, it would be simply storing the assignment for that year

What're some of the other possible solutions and pros / cons / correctness of each approach?

manning18
  • 45
  • 5

1 Answers1

2

For starters, this would do -- I think.

enter image description here

dimRole may look something like

RoleKey     RoleName        RoleGroup
---------------------------------------
  1         quarterback      player
  2         tackle           player
  3         head coach       coach
  4         assistant coach  coach
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Nice, clean. Out of interest, what tool did you use to create the diagram? – Ian Nelson Sep 30 '11 at 20:04
  • Great start and pretty close to what I envisioned - my only question is that, does it make sense to store it as a fact table, since this fact table doesn't actually store any measures and is simply storing an assignment? – manning18 Oct 03 '11 at 14:49
  • @manning18 yes, see this http://stackoverflow.com/questions/6788008/warehouse-store-and-count-non-fact-records/6789847#6789847 – Damir Sudarevic Oct 03 '11 at 15:48