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