Large Student dimension or new Student Fact table?

View previous topic View next topic Go down

Large Student dimension or new Student Fact table?

Post  Duncan on Thu Jul 14, 2011 2:45 am

Hi all,
at the moment we have a Student Course fact table keyed to a Student dimension as per normal design methods, however additional reporting requests have come up around columns in the student dimension which can have multiple records. (e.g Home/mail/term address , citizenships)

Can you have a student (or customer) schema to cater for multiple instances of addresses, citizenships, cohorts - any issues / drawbacks with this method?

Or would you use a student dimension with multiple instances of the columns (eg Home Address, Mailing Address)

Thanks,
Duncan

Duncan

Posts : 1
Join date : 2011-07-14

View user profile

Back to top Go down

Re: Large Student dimension or new Student Fact table?

Post  Dave Jermy on Thu Jul 14, 2011 4:36 am

There are a few ways to deal with dimension attributes that can have multiple values at the grain of your dimension.

The first is to look at your dimension and see whether it makes sense to change the grain of it to include the multi-valued attribute. In your case that's probably not feasible, because you've got several such attributes and a dimension with one row per student, address, citizenship and cohort doesn't really make sense.

The second is to move the attribute into its own dimension and then either join it directly to the fact table (if it makes sense to do so) or link it back to the original dimension with a bridge table. One consideration here is whether you're talking about a single dimension attribute, or a group of related attributes. It may make sense for you to create an address dimension.

Third, you can create multiple columns in your dimension to hold the various values. So, you could have PrimaryCohort, OtherCohort1, OtherCohort2 etc. This is only worth doing if the possible number of different values has a small upper bound, say 3 or 4 at the most. And be aware that it can make queries trickly later on.

Hope that helps.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

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