CODEWITHSUNDEEP
olap
Paul
Paul

Reputation: 3142

Data Warehouse Design 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.

Upvotes: 0

Views: 349

Answers (3)

Daniel Macho
Daniel Macho

Reputation: 1

You need a factless fact table. Look at the following resource that refers to an example close to your need

http://www.kimballgroup.com/1996/09/02/factless-fact-tables/

Upvotes: 0

Ben
Ben

Reputation: 11

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.

Upvotes: 1

Santiago Cepas
Santiago Cepas

Reputation: 4094

You need a many to many dimensional model.

Upvotes: 0

Related Questions