Student Retention Fact Table

Hello dimensional modeling folks!

I have requirement to derive retention for students during a given term. The problem is I am not sure how model for this requirement? I am able to easily determine which students transfer out based upon a transfer out date that falls into a given start and end date. There is also a transfer in date that is provided. There is a field on the student record that states if they are active or inactive and also a status field to indicate if they have graduated.

I am at a loss to derive the total number of students that are enrolled for a given term? Do you look at who is active and add the new enrollments and take a snapshot of this information and store it into a snapshot fact table? Or would you look at enrollments as events and record them as such and then look at withdrawals similarly. I'm looking at the most straight forward easiest approach to get a count of all distinct students minus those who transfer out during an academic term. I'm also looking to do this using a dimension model.

Some of my ideas are to create a snapshot fact table at the beginning of an academic session. I'm not sure what to include on the table and then how to count the students who withdraw during this time period. I suppose it would be easy enough to include a field for the transfer out date in a view. The main issue is getting an appropriate count of students for the academic time period. I did a little research and it looks like a university was taking a snapshot before the start of the term. If this is done you could easily use this snapshot and the transfer out dates to get the right counts. The problem is this technique is not being done right now and they want an automated method to derive now.

Some issues that I face are students will come and go - meaning they have multiple transfer in dates. It's also possible to have more than one transfer out date. The trick would be to marry up the transfer in to the right transfer out date. Again, the easiest way seems to be to take a snapshot of those students enrolled at the beginning and then count any who transfer out to obtain retention.

Any ideas on how to model retention dimensionally would be much appreciated!

Here's a basic select statement that I am working on to load the fact table.

SELECT [StudentKey],
SourceSystem AS SourceKey,
Getdate() AS 'Snapshot Date' -- 02-11-2013
,
'02-11-2013' AS 'Term Start Date',
'04-05-2013' AS 'Term End Date',
'' AS 'Transfer Out Date'
FROM
[ods].[Student]
WHERE inactive = 'Yes'
AND sourcesystem <> 1
ORDER BY sourcesystem

I want to be able to count the number of students that have a transfer out date so I would need update this fact table with transfer out dates that fell between the start and end of a term or use a view for the transfer out date.

Here's the issue. Students can do multiple things during the year. The can enroll and transfer out. They can transfer in and then withdraw.

You need a table that picks up the "events". If a student enrolls and stays for the entire year, they are in the table twice. If the student enrolls, and then withdraws, they are in the table twice (the withdrawal would be a negative value). If the student transfers in, then are in once. If they transfer in and then withdraw, then they are in twice.

You could create this data on a monthly basis and then sum the changes for the year.

You could sum the changes for the month (new enrollment, transfer in, transfers out, withdrawl) and add them to the total enrollment for the previous month and get the new month's total.

Kimball calls this a coverage fact table. That's how I would approach this problem.