fast changing dimension
5 posters
Page 1 of 1
fast changing dimension
I'm debating how to model a teacher work force where most often a teacher works for one school authority. The issue is where a teacher works for multiple school authorities. A substitute teacher for example could work for a different school authority every day of the week which has me worried about using an SCD approach.
Ideally in the teacher dimension I want a hierarchy where every teacher belongs to a school authority, any recommendations?
Ideally in the teacher dimension I want a hierarchy where every teacher belongs to a school authority, any recommendations?
robber- Posts : 41
Join date : 2009-02-28
Location : Canada
Re: fast changing dimension
Sounds like a FACTLESS FACT table might be useful
Presumably you have a School Authority dimension? in which case you would maintain the relationship between the Teachers and the School Authorities on each day in there
ie
Presumably you have a School Authority dimension? in which case you would maintain the relationship between the Teachers and the School Authorities on each day in there
ie
DateSK | TeacherSK | AuthoritySK |
20110109 | 1 | 10 |
20110110 | 1 | 11 |
20110111 | 1 | 10 |
20110109 | 2 | 10 |
20110110 | 2 | 12 |
20110111 | 2 | 12 |
meb97me- Posts : 34
Join date : 2010-07-28
Re: fast changing dimension
Definitely a consideration (factless fact table) however I would really like to have the school authority represented as a level in the teacher dimension for ease of analysis and reporting. I realize this can still be done with a factless fact table however before I abandon the pure dimension option I'll need to do further analysis about what percentage of the teacher truly work for multiple school authorities on a daily, weekly, monthly basis.
Thanks for the input.
Thanks for the input.
robber- Posts : 41
Join date : 2009-02-28
Location : Canada
Re: fast changing dimension
or the other option is to make the grain/NaturalKey of your teacher dimension table Teacher AND Authority
ie
then you could build a hierachy from your dimension
Authority
>>>>Teacher
but you would obviously see both teachers under both authorities as they have worked for both, i guess it depends how you want to view this hierachy? ie as in a point in time or just based on "current" authority
ie
TeacherSK | TeacherKey | AuthorityKey |
1 | TeacherA | AuthorityA |
2 | TeacherA | AuthorityB |
3 | TeacherB | AuthorityA |
4 | TeacherB | AuthorityB |
then you could build a hierachy from your dimension
Authority
>>>>Teacher
but you would obviously see both teachers under both authorities as they have worked for both, i guess it depends how you want to view this hierachy? ie as in a point in time or just based on "current" authority
meb97me- Posts : 34
Join date : 2010-07-28
Re: fast changing dimension
Technically, it's not a true hierarchy because a teacher can rollup to multiple authorities and that's not allowed in a hierarchy. The lowest level is really Teacher/Authority which rolls up to Authority and to Teacher as 2 seperate hierarchies.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: fast changing dimension
Agreed! As long as the relationship becomes m-m, it's not a hierarchy (1-m) at all, and the task has become report grouping from either end.Jeff Smith wrote:Technically, it's not a true hierarchy because a teacher can rollup to multiple authorities and that's not allowed in a hierarchy
Schools won't fit into teacher dimension unless you can nominate one as teacher's primary role. However you still need the factless/bridge table to cater for m-m associations.robber wrote:I would really like to have the school authority represented as a level in the teacher dimension for ease of analysis and reporting.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: fast changing dimension
What difference does it make to the user if it's one dimension or 2 dimensions? In most reporting tools, it's simply a matter of pointing and clicking which would be the case regardless of the table design. If the user groups by Authority and Teacher, then it's going to display the data correctly. The only thing they wouldn't be able to do in a reporting tool is to drill down from the Authority to the Teacher.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: fast changing dimension
You can't do it in one dimension. Make two dimensions and capture the teacher authority relationship in the fact table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: fast changing dimension
With proper backend structure and frontend implementation, I guess you should still be able to drill down and see the list of teachers teaching in a particular school, or the list of schools taught by a teacher.Jeff Smith wrote:The only thing they wouldn't be able to do in a reporting tool is to drill down from the Authority to the Teacher.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: fast changing dimension
Agreed, two dimensions it is. Thanks for the input/reality check!
robber- Posts : 41
Join date : 2009-02-28
Location : Canada
Re: fast changing dimension
You could see the teachers and school, but you couldn't create cube where you could drill down from the school to the teacher. I don't think the cube would even allow itself to be built. No doubt that if Teacher and Authority were different dimensions of the cube you could create and report that showed the teachers in each school - but I don't think you could define Teacher and Authority in the same Cube dimension.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: fast changing dimension
Jeff,Jeff Smith wrote:You could see the teachers and school, but you couldn't create cube where you could drill down from the school to the teacher.
I agree that you may not be able to drill down in the cube since no hierarchical relationship exists between teachers and schools, therefore hierarchy cannot be configured in the cube, at least not in SSAS. However what I referred to “drill down” is really a general reporting activity from the end-user perspective, not just limited to cubing.
For instance on a report implemented in SSRS, you can show a list of teachers along with the number of schools assigned to them, a feature called "Action" allows the user to click on the number and show the list of schools relevant to that teacher. The same interactivity may also apply to another report with list of schools with number of teachers involved. I believe other reporting tools can also provide similar functionalities.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Fast changing status transactional fact table
» Changing a slowly changing dimension
» Rapidly Changing Dimension
» Rapidly Changing Dimension for Manufacturing DWH
» Type 3 Slowly Changing Dimension
» Changing a slowly changing dimension
» Rapidly Changing Dimension
» Rapidly Changing Dimension for Manufacturing DWH
» Type 3 Slowly Changing Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum