Student Course Registration Fact and Dimension Tables : How to model

View previous topic View next topic Go down

Student Course Registration Fact and Dimension Tables : How to model

Post  pitbull mix on Fri Aug 31, 2012 2:47 pm

Each day I load new data related to courses for the student. After working with the data I realized that some rows that were present on previous loads are no longer part of the next days load of data.

My question is actually three questions. First I'd like to know how to handle the changes in the data for the transactional or transactions that occur related to the courses for a student in the fact table?

Right now I have loaded all the courses for students and they match what is in the source system. What do I do to keep rows up to date? For example what happens when the source system no longer has a row already loaded? Do I delete the row or mark it as deleted? If there are changes I assume that I can just update the row.

The second question relates to creating a periodic snapshot for student course rows. Is it good design to create a periodic snapshot for this type of data and if so what would it look like? How often would you load and what grain?

The third question relates to a the details related to the fact table. Because a fact table usually only contains facts / measures I've not put in all the detail related to the measure. At first I thought I should create a dimension with the details of the fact row that relates with a surrogate key to the fact table containing all the details. I noticed that this was a one to one relationship and that this isn't supposed to be a good dimensional design. If this isn't good design then how do you drill down to the details for the course and the related outcomes? While in limbo I've added a field that will let me query for details at the ODS layer and kept the original dimension with the details. However, if the student drops or changes a course this data is gone so there is no way to get the details on the course. If the course is a current course and in the source system I can always do the drill down. So two things on this part of the question. How do I model or handle for the one to one between dimension and the fact table without duplicating the volume of rows in a dimension? Also how do I handle for the changes in detail which would typically be in a dimension and the fact table?

The following diagram represents my current model for student course registration.



Thanks,

Chad

pitbull mix

Posts : 8
Join date : 2012-06-11

View user profile

Back to top Go down

Re: Student Course Registration Fact and Dimension Tables : How to model

Post  BoxesAndLines on Fri Aug 31, 2012 4:53 pm

1. Add some sort of status dimension (Active, Completed, Dropped) to handle deleted rows. If a student drops a course, set the status to dropped. Now you can see how many students dropped a course, etc. As a rule, you never want to delete rows.

2. I would go transaction fact table here. I can easily find all courses filtered by semester, year, etc. I can see all courses added and dropped across time. With this fact, you could see me taking assembly language three times. Waaaaah!

3. I think the status dimension handles this case. I'm not sure what you have in Course Registration dimension though. This looks like a one-to-one. In which case you should break into smaller dimensions with lower cardinality.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

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