Fact Table Design Education

Hi,

I have already got a fact table which essentially captures the student registration process. The dimensions are term, section, student and registration status.

The business has a requirement to analyse the absence % of each term, section and student basically at the same grain as my existing registration fact.

The source system has a table which captures the absence % at the same level of granularity as the registrtaion fact. My question is should I create a new fact table which has the same grain as registration but the only addition would be the absence %.

Or should I add absence % to my registration fact. The difference is the registration fact occurs when a student is registered in a course, the absence % is calculated when a teacher enters attendance for a student.

thanks for the help

I would not hold a pre-calculated absence % as this will restrict the flexibility of your model.
I would record instances of absences (or the equivalent of what would be a 100% attendance record) in a separate fact table and then you can combine the two fact tables to calculate absence/attendance at any level of aggregation that you want.

Absence calculations would then be either:

absence / (absence + attendance)
or
(100% attendance - actual attendance)/100% attendance

Thanks Nick. So basically you are saying I should build a fact table which records the attendance entry and then calculate the absence %.

Which 2 fact tables do you refer combining and is it ok if both are at different granular levels?

Thanks

Hi,

your registration fact presumable shows what the student is registered for i.e. what they should be attending.

You then capture actual attendances or absences (whichever is easier). You also need to capture what a perfect attendance count would be.

Assuming your registration was for term/course/student then you'd need to capture the perfect attendance count at this level e.g. 10 week term, 2 lectures per course = 20 attendances).

Your attendance/absence would probably be at the term/course/student/date grain with a count of 1. Sum this by term/course/student and compare with the perfect attendance count to get the absence.

Hope this helps

Nick very helpful! Thank You!