Data Warehouse Design Questions

Question

Data Warehouse Design Questions

Paul8112

SSC Enthusiast

Points: 154
More actions
August 12, 2010 at 6:37 am

#227726

Hello,
I'm having my first go at designing a data warehouse. I have an OLTP database that is used for an online booking system. We have a requirement for a solid reporting engine so I'm going to create a DW and then use Reporting Services.
There's a couple of things I'm unsure about, and would be grateful for any advice:
One report would be all students who haven't booked any lessons. A student can register for an instructor but not necessarily book a lesson. At present, I have an Instructor dim table and a Student dim table. The fact table is called LessonBookings and FK's to both the Instructor and Student dim table. In the OLTP I have a many to many table (InstructorStudents) which simply links the tables together. How would I replicate this in an OLAP database? I could easily get reports of how many lessons a student has had, but if the student hasn't had a lesson they won't appear in the LessonBookings fact table. I've heard about a process called bridging for M2M relationships. Is this what I need?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Paul8112 (8/12/2010)
Hello,
I'm having my first go at designing a data warehouse. I have an OLTP database that is used for an online booking system. We have a requirement for a solid reporting engine so I'm going to create a DW and then use Reporting Services.
There's a couple of things I'm unsure about, and would be grateful for any advice:
One report would be all students who haven't booked any lessons. A student can register for an instructor but not necessarily book a lesson. At present, I have an Instructor dim table and a Student dim table. The fact table is called LessonBookings and FK's to both the Instructor and Student dim table. In the OLTP I have a many to many table (InstructorStudents) which simply links the tables together. How would I replicate this in an OLAP database? I could easily get reports of how many lessons a student has had, but if the student hasn't had a lesson they won't appear in the LessonBookings fact table. I've heard about a process called bridging for M2M relationships. Is this what I need?

Issue is... you don't want to replicate on a Dimensional model what you have done in a non-dimensional OLTP database design.

1st rule... Avoid many-to-many relationships. When you bump into a many-to-many that means a bridge table is missing. So, the answer is yes, bridging is the solution.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

If you're looking for one of the best overviews (and detailed analyses) of M2M, especially in an OLAP/SSAS context, then Marco's is the best (IMHO :-)).

It's here.

Steve.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 3

PaulB-TheOneAndOnly (8/12/2010)
1st rule... Avoid many-to-many relationships. When you bump into a many-to-many that means a bridge table is missing. So, the answer is yes, bridging is the solution.

I agree. I've done a couple of data warehouses for schools on their state test data. Always difficult to link a teacher to a test fact table. Student and Teacher dimensions linked to a studentteacher bridge dimension is the only way to do it. Good luck.