Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Student Retention Fact Table

3 posters

Go down

Student Retention Fact Table Empty Student Retention Fact Table

Post  pitbull mix Mon Jan 07, 2013 1:44 pm

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.



Last edited by cdowney on Mon Jan 07, 2013 2:13 pm; edited 1 time in total (Reason for editing : To provide more detail on question asked.)

pitbull mix

Posts : 8
Join date : 2012-06-11

Back to top Go down

Student Retention Fact Table Empty Re: Student Retention Fact Table

Post  Jeff Smith Mon Jan 07, 2013 3:36 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Student Retention Fact Table Empty Re: Student Retention Fact Table

Post  BoxesAndLines Mon Jan 07, 2013 3:45 pm

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

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

Back to top Go down

Student Retention Fact Table Empty Re: Student Retention Fact Table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum