Modeling - Dimension/Fact - Need advice please

[marric01]

Hi,

I'm in the process of modeling a DW about courses took by employees (Paramedic).

I have identify possible grain.

Grain for employee trained (Fact_CoursesEmployee).
Grain for the course itself (Fact_Courses).
Grain for the trainer (Fact_CoursesTrainer).

More than 1 employee can participate to a course and more than one trainer can teach a class.

I have some dimensions that were identified by the field expert assisted with IT guy (me). But, I'm getting a little confused when its time for modeling the dimensions around the fact table.

Source DB (Quick view)
tblCourses-> tbl_Employee_courses -> tblEmployee
tblCourses -> tbl_trainer_courses -> tblTrainer
tblCourses-> tblTitleOfCourses
tblCourses-> tblLocationCourses
tblCourses -> tblTypeOfCourses
tblCourses -> OtherReferenceTable...

For the DW, i'm gone create a dimension called dimCourses but I dont know if I should include the information of the table tblTitleOfcourses, tblLocationOfcourses and tblTypeOfcourses inside the dimension dimCourses or threat them as separate dimensions (dimCoursesTitle, dimLocation, dimTypeOfCourses) and put the id of those dimension inside the Fact table. This is where i'm a little bit confused about where to put what. Or maybe both way are good too ;-)

I hope that I was clear !

Thanks for any advice

Richard

[BoxesAndLines]

It would seem to me that course title and type would be attributes of the course dimension. Location I would break into its own dimension as it is not dependent on a course.

[marric01]

Hi, thanks for the advice ...

Can you explain a little bit more about

as it is not dependent on a course.

Do you mean that because a location could be used for anything else. And a title of course can be used only for a course. even if in the OLTP database, they are using a specific table for the course title. So we dont automaticaly create a separate dimension each time we see a reference table in the OLTP database ;-)

So in the dimension dimCourses, I could store only the text field (titleOfcourse) and (typeOfcourse). Do I need to also store de PK of the OLTP database in case of change in a titleOfcourse or typeOfcourse ?

dimCourses
IDCourses
idTitleOfcourse
titleOfcourse
idTypeOfcourse
other attributes ...

thanks

[BoxesAndLines]

You got it. I can change a course id and it will not affect the value in location. This is how you go through a normalization process. Ralph has tried to distance discussions around normalization and dimension design but it does still provide value in dimensional modeling.

You always want to store the PK of the OLTP system in your dimension (ideally as an AK). That is how you will code the dimension lookups.

[Sponsored content]