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

2 posters

Page

**1**of**1**## Student Course Registration Fact and Dimension Tables : How to model

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

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

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

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.

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.

**BoxesAndLines**- Posts : 1212

Join date : 2009-02-03

Location : USA

Similar topics

» How to design student quarterly registration fact table

» Large Student dimension or new Student Fact table?

» Student GPA Fact or Dimension

» Single fact table vs multiple fact tables - what is the right way in a dimensional model?

» Storing Date Keys in dimension tables versus fact tables

» Large Student dimension or new Student Fact table?

» Student GPA Fact or Dimension

» Single fact table vs multiple fact tables - what is the right way in a dimensional model?

» Storing Date Keys in dimension tables versus fact tables

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum