Student Profile - Fact Table

View previous topic View next topic Go down

Student Profile - Fact Table

Post  mashadag on Mon Jul 15, 2013 3:22 am

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.

mashadag

Posts : 2
Join date : 2013-07-15

View user profile

Back to top Go down

Re:Student Profile - Fact Table

Post  hkandpal on Mon Jul 15, 2013 10:59 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Student Profile - Fact Table

Post  mashadag on Tue Jul 16, 2013 9:31 am

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?

mashadag

Posts : 2
Join date : 2013-07-15

View user profile

Back to top Go down

Re:Student Profile - Fact Table

Post  hkandpal on Tue Jul 16, 2013 10:33 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Student Profile - Fact Table

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