A Type 2 Dimension for the School Revenue Data Mart

School System is one of the focal points of analysis for the School Revenue Data Mart. One of the attributes of a School System is Enrollment Number which is the number of students in a school system and will be widely used in reports and ratios.

SYSTEM_ENROLL_NB is the number of students in a school system.  It's stored in a dimensional table, SR_DIM_SYSTEM, although it could have been added to a fact table called a "snapshot fact table".  The rationale behind using it in the dimension has to do with the type of computations and analysis I plan to do with the data mart.

To support the change with SYSTEM_ENROLL_NB, I've added two columns to the SR_DIM_SYSTEM dimension: SYSTEM_EFCTV_START_DT and SYSTEM_EFCTV_END_DT.  These two columns will work together to define a range, possibly open-ended, of dates for which the SR_DIM_SYSTEM record is effective.  This is a Type 2 dimension (Kimball).

When the number of students (SYSTEM_ENROLL_NB)  for a school system changes, a new record will be added and established as the current record for loading.  For the current record, the SYSTEM_EFCTV_START_DT is the current time while the SYSTEM_EFCTV_END_DT is a high value number (say the year '3000').  Some implementations may use NULL for the END_DT, but this requires special handling separate from the normal date comparisions and computations.

The prior current record is retained, and the SYSTEM_EFCTV_END_DT is set.

School Revenue Data Mart with Type 2 Dimension

LOAD_DT is added to all the tables.

A few date additions were added to the basic structure to handle changing enrollment and to shore up some operational requirements