Modeling - Dimension/Fact - Need advice please

View previous topic View next topic Go down

Modeling - Dimension/Fact - Need advice please

Post  marric01 on Wed Aug 18, 2010 3:54 pm

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

marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Modeling - Dimension/Fact - Need advice please

Post  BoxesAndLines on Thu Aug 19, 2010 9:46 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling - Dimension/Fact - Need advice please

Post  marric01 on Thu Aug 19, 2010 10:00 am

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


marric01

Posts : 23
Join date : 2010-08-18
Age : 41
Location : Montréal, Québec , Canada

View user profile

Back to top Go down

Re: Modeling - Dimension/Fact - Need advice please

Post  BoxesAndLines on Thu Aug 19, 2010 1:46 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling - Dimension/Fact - Need advice please

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum