Student Profile - Fact Table

I am designing a DW for a student profile in which our organisation has a student status per day. Statuses being Allowed in Class (A) and Not Allowed in Class (N) and Withdrawn (W). My query comes as regards the fact table.

Our transactional system stores the last recorded status using the schema as below; Note, in a given day the student status may change. Also, a student may have the same status from a given date to date e.g. a student Joe Bloggs would have been withdrawn (W) from last month.

StudentStatusID
StudentID
Date Key
StudentStatus

My issue is with the design of the Fact table.
Should I create a record for each day per student - this implies that even for students that withdrew years ago I still maintain a W status per day or;
Should I create a schema with the "last recorded status" similar to the OLTP design?

I am looking for the best solution when it comes to trending statuses on a day by day day basis.

Any help will be greatly appreciated.

Hi,

it depends upon the requirement, you can have a table where you will capture the even whenever the changes occur or you may capture daily once and get the status (even if a student status has changed multiple times in a day you will have only one row.

How do you want to report, will drive down the table structure.

thanks

Thanks hkandpal

I want to capture only one record per student per day i.e. the last status per day.

So is my best bet to store the "last recorded status" or for each and every student for each and every day I have I store their status?

Hi,

if the requirement is to capture only one per day than you can capture that.
If nothing changes than also do you want to capture the rows ?
One way you could have is capture only when it changes and in the presentation layer put the logic to pick up the last changed row if you dont have any updates for a particular day.


thanks