fast changing dimension

View previous topic View next topic Go down

fast changing dimension

Post  robber on Fri Feb 11, 2011 10:41 am

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?

robber

Posts : 41
Join date : 2009-02-28
Location : Canada

View user profile

Back to top Go down

Re: fast changing dimension

Post  meb97me on Fri Feb 11, 2011 11:09 am

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

DateSKTeacherSKAuthoritySK
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

View user profile

Back to top Go down

Re: fast changing dimension

Post  robber on Fri Feb 11, 2011 11:23 am

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.

robber

Posts : 41
Join date : 2009-02-28
Location : Canada

View user profile

Back to top Go down

Re: fast changing dimension

Post  meb97me on Fri Feb 11, 2011 11:37 am

or the other option is to make the grain/NaturalKey of your teacher dimension table Teacher AND Authority

ie

TeacherSKTeacherKeyAuthorityKey
1TeacherAAuthorityA
2TeacherAAuthorityB
3TeacherBAuthorityA
4TeacherBAuthorityB

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

View user profile

Back to top Go down

Re: fast changing dimension

Post  Jeff Smith on Fri Feb 11, 2011 1:43 pm

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

View user profile

Back to top Go down

Re: fast changing dimension

Post  hang on Fri Feb 11, 2011 5:24 pm

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

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

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: fast changing dimension

Post  Jeff Smith on Fri Feb 11, 2011 5:30 pm

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

View user profile

Back to top Go down

Re: fast changing dimension

Post  BoxesAndLines on Fri Feb 11, 2011 5:38 pm

You can't do it in one dimension. Make two dimensions and capture the teacher authority relationship in the fact table.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: fast changing dimension

Post  hang on Fri Feb 11, 2011 7:07 pm

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

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: fast changing dimension

Post  robber on Mon Feb 14, 2011 10:32 am

Agreed, two dimensions it is. Thanks for the input/reality check!

robber

Posts : 41
Join date : 2009-02-28
Location : Canada

View user profile

Back to top Go down

Re: fast changing dimension

Post  Jeff Smith on Mon Feb 14, 2011 10:33 am

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

View user profile

Back to top Go down

Re: fast changing dimension

Post  hang on Mon Feb 14, 2011 5:47 pm

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.
Jeff,

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

View user profile

Back to top Go down

Re: fast changing dimension

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