0

im tracking attendance of courses. There is a user dimension, course dimension and a fact table.

I need to track if a user attended a course and if they did not attend a course.

I was thinking of storing a record for each user and course in the fact table, one if they attended and one if they did not.

Should i store non attendance records or should absence of a record indicate non attendance?

wilson_smyth
  • 1,202
  • 1
  • 14
  • 39
  • Consider making your fact table a "snapshot", which contains 1 record per user per period per course. Then each record can contain a fact if the user attanded a course (i.e, Y/N). – RADO Dec 15 '18 at 18:26

1 Answers1

0

To your list of tables, I would also add a date dimension. I would name the user dimension something like a student dimension.

So your list of tables will look like:

Fact_Attendance

Dim_Student

Dim_Course

Dim_Date (The following link describes how a date dimension could be build: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ )

This fact can be created as a snapshot fact table. Create a row for every student per course per day. This would help you generate insights of the data to answer questions like :

How many students attended the course during a week/month? Did a particular student attend the course on a particular date?

A good starting point for you would be the Kimball Bus Matrix.

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/kimball-data-warehouse-bus-architecture/

NITHIN B
  • 214
  • 1
  • 9