Fact Table Design Education

View previous topic View next topic Go down

Fact Table Design Education

Post  businessintelligence on Tue Jun 23, 2015 3:08 am

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

businessintelligence

Posts : 14
Join date : 2015-06-23

View user profile

Back to top Go down

Re: Fact Table Design Education

Post  nick_white on Tue Jun 23, 2015 6:34 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact Table Design Education

Post  businessintelligence on Wed Jun 24, 2015 12:29 am

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

businessintelligence

Posts : 14
Join date : 2015-06-23

View user profile

Back to top Go down

Re: Fact Table Design Education

Post  nick_white on Wed Jun 24, 2015 7:23 am

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_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact Table Design Education

Post  businessintelligence on Wed Jun 24, 2015 8:02 am

Nick very helpful! Thank You!

businessintelligence

Posts : 14
Join date : 2015-06-23

View user profile

Back to top Go down

Re: Fact Table Design Education

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