many to many relationship between dimension level
2 posters
Page 1 of 1
many to many relationship between dimension level
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
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
Re: many to many relationship between dimension level
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.
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.
Re: many to many relationship between dimension level
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
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
Re: many to many relationship between dimension level
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.
Similar topics
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Dimension Level
» Multi-Level Dimension
» Grain present at every level of a dimension
» Fact - Dimension relationship 1:1
» Dimension Level
» Multi-Level Dimension
» Grain present at every level of a dimension
» Fact - Dimension relationship 1:1
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|