Data Warehouse Design Question

Question

In my OLTP database I have a layout consisting of instructors and students. Each student can be a student of any number of instructors. A student can also sign up for an instructor, but not necessarily book any tuition (lesson).

In a data warehouse, how best would this be modelled? If I create a dimension table for Lessons, Instructors and Students and a fact table for the lessons students have taken then this will work when an instructor wants to see what lessons a student has taken.

However, how will an instructor see how many students are REGISTERED with the instructor but has not yet taken a lesson?

In my OLTP, I have a many to many table (InstructorStudents) that links each student with one more more instructors. In an OLAP database, this isn't appropriate.

What would be the best schema in this case? Would a many to many be appropriate in this instance? I can't store a list of which students are registered to which instructors in the student table, so I feel another dimension table is necessary but cannot work out what should be contained in it.

Answer 1

You need a many to many dimensional model.

Answer 2

If a fact represents a transaction, you seem to have two different facts here: Sign ups & Lessons. There are always a lot of ways to go but, perhaps, you need two fact tables. They may have similar dimensionality except the sign-up table will have a Class dimension (class name, instructor name, etc.). The Lessons table will tie to the class dimension but, also, to a Lesson dimension (date, classroom used, etc.).

There are a few other ways to do this but they will be more difficult from a programming & reporting perspective.