Dimensional Modelling For a Timetabling System

View previous topic View next topic Go down

Dimensional Modelling For a Timetabling System

Post  steve_p_1976 on Wed Jul 06, 2011 11:51 am


In the OLTP system we have 4 main timetabling tables:

REGISTER_EVENTS (event header information)
REGISTER_EVENT_SLOTS (event start and end information)
REGISTER_EVENT_DETAILS (Event Types linked to the event - People,Rooms,Courses)
REGISTER_EVENT_DETAILS_SLOTS (start and end information for all event types associated with an event)

An Event is a timetabled occurrence on the TT system. An event can hold multiple event types (Courses, People, Rooms).

For example, in register event details slots:

EventID EventType TypeID SlotID StartDate EndDate
1 COURSE 1234 1 " "
1 ROOM 77 1 " "
1 ROOM 78 2 " "
1 TUTOR 99 1 " "

This will form the basis for the FACT table as this is the lowest granularity but my question is: in order to pull Tutor Hours against Courses, Events and Rooms, will I need separate FACT tables - one for each event type? Or if it is possible to have a single FACT table, how can i join to COURSE, ROOM and TUTOR dimensions?

In order to join a COURSE dimension, the EventType will have to be COURSE for the TypeID to match up to its equivalent value in the COURSE dimension.

Thanks

Steve


steve_p_1976

Posts : 1
Join date : 2011-07-06

View user profile

Back to top Go down

Re: Dimensional Modelling For a Timetabling System

Post  hang on Thu Jul 14, 2011 7:08 am

I don't think it's good idea to mix up COURSE, ROOM and TUTOR in a single fact table. You should either have 3 fact tables separately or a single fact table, say TUTOR_HOURS, and work out the hours on other dimensions (COURSE and ROOM) through their relationships with TUTOR dimension.

hang

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

View user profile

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