What are historical dimensions?
Historical dimensions provide historical information about staff, schools and students. If you need a school's previous address or a staff member's previous job title, you would use the appropriate historical table.
How are they different from regular dimensions?
In some cases, the information in the historical tables is the same (i.e. year school was founded). However, in other cases, these details change over time - a school moves to a new location, a staff member changes jobs, or a student moves to a new address. The historical tables are our "slowly changing dimensions."
Additional Notes
- Staff name is not in dimStaffHistorical, but the SystemStaffID is available. If a staff members' name changes over time, their new name is captured in place of their old name.
- Similarly, school name and student name are not captured in the historical tables, but the SystemSchoolID and SystemStudentID are stored in these tables.
- In some cases, these tables may capture erroneous data and flag as a change. For example, if Jane is a rising 8th grader but was accidentally enrolled as a 9th grader for two days a the start of the year, her correction to 8th grade would be logged as a change in dimStudentHistorical. Also, her incorrect enrollment in 9th grade would continue to persist in the table. Please keep this issue in mind!
dimSchool vs dimSchoolHistorical
What do these two dims do?
The school that is shown in dimSchool and dimSchoolHistorical for any particular fact record (e.g. an assessment record, enrollment record, etc.) is the school that is associated with that specific record, on the record date. Whether you use dimSchool or dimSchoolHistorical, you'll pull up information about the same school - the one that is associated with the fact record you selected.
If you choose to look at dimSchool, however, you'll see the current information about that school - its current address, name, highest/lowest grade levels, and other details. If you pull up dimSchoolHistorical, you'll see the historical information about the school on the date associated with the fact record in question.
Is the information I'd see between these two dims actually different?
In many cases, these two sets of information could be the same (e.g. year founded). However, in other cases, these details change over time - a school moves to a new location, adds additional grade levels, or changes its name, for example. dimSchoolHistorical is one of our "slowly changing dimensions."
When would I use one vs. the other?
You have the choice of which set of school information to use and it can vary depending on the report you are trying to build. If you’re building a historical enrollment report, knowing the highest grade level each year in a school that has been growing grades each year is important - look at dimSchoolHistorical. If you’re associating current and historical test scores to schools and you want to use the name of the school that everyone on staff this year is used to seeing - look at dimSchool.
Additional Notes
- SchoolName is not in dimSchoolHistorical, but the SchoolID is. If a school's name changes over time, the new name is captured in place of the old name.
dimStudent vs dimStudentHistorical
The most current information about students is stored in the dimStudent table. The dimStudentHistorical table houses slowly changing dimensions (information about students that was true when the event occurred). The "RowStartDate" logs when that information was first true about a student and the "RowEndDate" logs when the information stopped being true.
A common example:
-
Use address data from the dimStudent table to show where a student currently lives. Use address data from the dimStudentHistorical table to show where they lived when they were enrolled in kindergarten, first grade, etc.