many to many relationship between dimension level

View previous topic View next topic Go down

many to many relationship between dimension level

Post  GBS74 on Mon Oct 26, 2009 10:51 am

I need help to design dimension model. Detail of Dimension are as following:
Dimension : Student, School
Fact : Grade

1. Class_Year
2. School
3. Area
4. City
Analysis Requirement are to drill up /down by above given hierarchy

there is :
M:1 relationship between Area and City
M:1 relationship between School and Area.

Issue : there is M:N relationship between Class_year and School. Every school have class year 6,7 and 8 and vice versa.

fact table consist of grade of students.

I have designed dimension Student as stud_id/name,address etc.
Dimension School as school_id / name, Class_year / Area_code / City.

but in BO reporting, Drill down/up is not working properly, reason may be Many to Many relationship between Class_year and School_id.

Could you please help me to design dimension tables, Do I need to split dimension 'school' into number of dimensions, if yes how.

Regards

GBS74

Posts : 4
Join date : 2009-07-29

View user profile

Back to top Go down

Re: many to many relationship between dimension level

Post  ngalemmo on Mon Oct 26, 2009 12:00 pm

Take class year out of the school dimension. Make it its own dimension, or if there are no attributes, create a degenerate dimension.

School, area and city are (unless there is a major earthquake) static highly correlated geographic attributes, while year is not. Combining uncorrelated attributes into a single dimension can cause problems.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: many to many relationship between dimension level

Post  GBS74 on Mon Oct 26, 2009 12:55 pm

thanks !
Class_year does not have any other attributes, Is there any disadvantage if I create separate dimension rather then degenerate dimension. Description can be added to Class year.

regards

GBS74

Posts : 4
Join date : 2009-07-29

View user profile

Back to top Go down

Re: many to many relationship between dimension level

Post  ngalemmo on Mon Oct 26, 2009 5:27 pm

If you add a description, then it has attributes, so it cannot be a degenerate dimension. Even if there are no descriptions now, if there is a potential to add them in the future, you should create a proper dimension table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: many to many relationship between dimension level

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