Student System design question

View previous topic View next topic Go down

Student System design question

Post  RoyalWulf on Tue Jul 05, 2011 7:18 pm

We have a student system.

Students enrol in courses for the year. Students also drop out of courses.

So every night we get out of the source system all the enrolments for the year. The next night we get a revised set of enrolments for the year as new courses are enrolled in and some are discontinued.

I have a fact table that has enrolments for the year. I have dimensions for student, Course Enrolment, Teaching department etc. In this fact table I have a weighting called EFTS for the studentsí enrolment in the course. This fact table for 2011 gets over written every night. This works ok for the majority of reports. What I canít do though is reconstruct history. I donít know how many EFTS we had as at 1 March.

EFTS are determined by course size, teaching department splits, Start and End dates if it is a thesis student, and a student can discontinue with partial refund so we record a partial EFTS value.

The Course Enrolment dimension has an enrolment date and also a discontinued date. Therefore I could go through the Course Enrolment dimension looking for students who were enrolled on the 1 March, but how do I determine their EFTS at that point?

I am wondering if I need to use a slowly changing fact table. This seems to be a lot of overhead for the odd times we need to reconstruct history. Reconstructing history is vital though to track enrolments at the start of the year comparing this year with the same time previous years.

Any other thoughts on how to do this?


RoyalWulf

Posts : 9
Join date : 2010-04-18

View user profile

Back to top Go down

Re: Student System design question

Post  hang on Tue Jul 05, 2011 10:02 pm

ETF or FTE? Full-time equivalent. If itís latter, then I would assume a student could be enrolled in more than one course at one point of time. There are two options that I can think of to solve the problems.

Option 1, use periodic snapshot fact table to track student enrolment history with FTE directly in the fact table as it is likely an additive attribute. The other likely measurement is the count which normally defaults to 1. One student could appear in the fact table twice at one point of time, however FTE would add up to 1 for the same student. Of course you would have other FKs in the fact table, such as student key and course key etc.

Option 2, embellish the existing Course Enrolment dimension and make it a transaction dimension to capture relevant changes, including FTE attribute, with effective date pair. This approach can capture the historical FTE in the most compact format but needs proper periodic fact tables for trend (comparison or exception) analysis.

hang

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

View user profile

Back to top Go down

Re: Student System design question

Post  RoyalWulf on Wed Jul 06, 2011 12:59 am

It is EFTS which is the same as FTE stands for equivalent full time study.

Can you give me more info on option 1? The way I understand periodic snapshot fact tables is that you take a copy of all the records on a regular basis. I don't want to be taking a copy of all the enrolments every day. THe fact table would balloon out unnecessarily.

With option 2. I thought about putting the EFTS in the dimension but it really is a measure and should be in the fact. Our reports sum EFTS for Departments, for programmes, as well as for Courses, it doesn't nicely fit in the Course Enrolment dimension. Perhaps it needs to be in both?

Thanks,
John

RoyalWulf

Posts : 9
Join date : 2010-04-18

View user profile

Back to top Go down

Re: Student System design question

Post  hang on Wed Jul 06, 2011 5:43 am

With option 1, you don't take the snapshot daily, you may do it monthly or a few times a year depending on your business requirement. As I said, even with option 2, you still need some sort of snapshot fact table table to support your point of time analysis deep into history as joining transaction dimension to date dimension on the fly to produce such a snapshot view would be a performance killer.

Option 2 really just facilitates the history tracking of any interesting attributes in a more compact format. ETFS eventually will end up in snapshot fact table as a measure and you do have copy of all the enrolments for each snapshot. You could, if you like, have a current snapshot refreshed daily based on the transaction dimension, and a number of snapshots on a periodic bases, say monthly or a couple of times in each semester. However having option 2 in place, you can always produce a snapshot on demand for any point of time.

One thing you need to remember, unlike OLTP system that is supposed to produce the factual data through the relationship, OLAP system is about having materialised fact tables to cater for responsive performance, and if the fact table is very lean (normalised), you should not be too concerned about its going too deep. With table partitioning, I don't see any issues with billion rows in the fact table.

hang

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

View user profile

Back to top Go down

Re: Student System design question

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