August 12, 2010 at 6:37 am
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?
August 12, 2010 at 9:38 am
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.November 3, 2010 at 2:19 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply