Description
This fact table provides information pertaining to the enrollment schedule. Specifically, it contains fields for the timing & ordering (e.g., start and end dates), usage, and fees for child enrollments. The grain of this fact table is at the enrollment schedule level (class + child + cycle type + time slots grouping). An enrollment is an instance of a student on a roster for a organizational unit (business, school, or class) for a period of time. The roster is the list of the students that are the currently enrolled within an organizational unit during a time. The student may have multiple schedules for the same organizational unit within a time period but only one enrollment. This fact table, coupled with accompanying dimension tables, can help users to understand questions such as: What is the account's/child's enrollment history? What students are eligible to age up? How many new enrollments, disenrollments, continuing enrollments are there for the organizational unit for a period of time? What enrollments are expiring soon? (follow up questions to this question: Is there a new enrollment set up? If No then Why?)
Columns
| Column Name | Description |
|---|---|
contract_id |
Unique identifier for the enrollment schedule |
business_id |
Unique identifier for the business |
school_id |
Unique identifier for the school |
account_id |
Unique identifier for the account |
student_id |
Unique identifier for the student |
class_id |
Unique identifier for the class |
billed_fee_id |
Unique identifier for the billed fee (what is actually charged); connects to the dim_fee table |
original_fee_id |
Unique identifier for the original fee (the standard fee of the class); connects to the dim_fee table |
adjustment_id |
Unique identifier for the adjustment (adjustment is the difference between the original and billed Fees); connects to the dim_adjustment table |
frequency_type |
The frequency type of the enrollment schedule; enumerated: 'Weekly', 'Biweekly', 'Custom', or 'Casual' |
frequency_type_enrolled_proportion |
The proportion of the frequency type ('Weekly', 'Biweekly', 'Custom') for which the student is actually enrolled compared to the days that could have been enrolled (e.g., a student with a weekly schedule type attends Monday and Friday and the school is open M-F resulting in a 2/5 = .4 rate) |
daily_utilization |
The daily utilization rate (1 meaning full day, 0.5 half day) of the permanent fee |
average_daily_cost |
The average gross calculated daily cost for the enrollment schedule (without discounts applied) |
average_daily_discount |
The average recurring discount for the enrollment schedule |
average_daily_discounted_cost |
The average net calculated daily cost for the enrollment schedule (with discounts applied) |
enrollment_schedule_status |
The status of the enrollment schedule; enumerated: 'Future', 'Active', or 'Expired' |
reason_for_ending |
The reason for ending the enrollment schedule |
start_date |
The date when the contract for enrollment takes effect; a surrogate key to the dim_date table |
end_date |
The date when the contract for enrollment ends; a surrogate key to the dim_date table (for enrollment schedules with no end date set, '9999-12-31' will be used) |
scheduled_start_date |
The date when the first scheduled enrollment starts and is useful for computing unique enrollments counts for a period; a surrogate key to the dim_date table |
scheduled_end_date |
The date when the last scheduled enrollment ends or 2 years into the future (whichever happens sooner) and useful for computing unique enrollments counts for a period; a surrogate key to the dim_date table |
enrollment_period |
The start and end dates of the enrollment |
student_enrollment_order_business |
The order in which the enrollment schedule start dates occur within a student at a business |
is_initial_student_enrollment_business |
A flag indicating if the enrollment schedule is the first enrollment within a student at a business (no enrollments are set before this start date); enumerated: 'Yes' or 'No' |
is_latest_student_enrollment_business |
A flag indicating if the enrollment schedule is the latest enrollment within a student at a business (no enrollments are set after this end date); enumerated: 'Yes' or 'No' |
count_student_enrollment_business |
A count of enrollments within a student at a business |
is_max_student_enrollment_business |
An order based (not date based) flag indicating if the enrollment is the maximum enrollment order (i.e., student_enrollment_order_business = count_student_enrollment_business) within a student at a school (differs from is_latest_student_enrollment_business flag because there is only one is_max_student_enrollment_business per student while is_latest_student_enrollment_business can have multiple enrollments on the maximum date); enumerated: 'Yes' or 'No' |
account_enrollment_order_business |
The order in which the enrollment schedule start dates occur within an account at a business |
is_initial_account_enrollment_business |
A flag indicating if the enrollment schedule is the first enrollment within an account at a business (no enrollments are set before this start date); enumerated: 'Yes' or 'No' |
is_latest_account_enrollment_business |
A flag indicating if the enrollment schedule is the latest enrollment within an account at a business (no enrollments are set after this end date); enumerated: 'Yes' or 'No' |
count_account_enrollment_business |
A count of enrollments within a account at a business |
is_max_account_enrollment_business |
An order based (not date based) flag indicating if the enrollment is the maximum enrollment order (i.e., account_enrollment_order_business = count_account_enrollment_business) within a account at a school (differs from is_latest_account_enrollment_business flag because there is only one is_max_account_enrollment_business per account while is_latest_account_enrollment_business can have multiple enrollments on the maximum date); enumerated: 'Yes' or 'No' |
student_enrollment_order_school |
The order in which the enrollment schedule start dates occur within a student at a school |
is_initial_student_enrollment_school |
A flag indicating if the enrollment schedule is the first enrollment within a student at a school (no enrollments are set before this start date); enumerated: 'Yes' or 'No' |
is_latest_student_enrollment_school |
A flag indicating if the enrollment schedule is the latest enrollment within a student at a school (no enrollments are set after this end date); enumerated: 'Yes' or 'No' |
count_student_enrollment_school |
A count of enrollments within a student at a school |
is_max_student_enrollment_school |
An order based (not date based) flag indicating if the enrollment is the maximum enrollment order (i.e., student_enrollment_order_school = count_student_enrollment_school) within a student at a school (differs from is_latest_student_enrollment_school flag because there is only one is_max_student_enrollment_school per student while is_latest_student_enrollment_school can have multiple enrollments on the maximum date); enumerated: 'Yes' or 'No' |
account_enrollment_order_school |
The order in which the enrollment schedule start dates occur within an account at a school |
is_initial_account_enrollment_school |
A flag indicating if the enrollment schedule is the first enrollment within an account at a school (no enrollments are set before this start date); enumerated: 'Yes' or 'No' |
is_latest_account_enrollment_school |
A flag indicating if the enrollment schedule is the latest enrollment within an account at a school (no enrollments are set after this end date); enumerated: 'Yes' or 'No' |
count_account_enrollment_school |
A count of enrollments within a account at a school |
is_max_account_enrollment_school |
An order based (not date based) flag indicating if the enrollment is the maximum enrollment order (i.e., account_enrollment_order_school = count_account_enrollment_school) within a account at a school (differs from is_latest_account_enrollment_school flag because there is only one is_max_account_enrollment_school per account while is_latest_account_enrollment_school can have multiple enrollments on the maximum date); enumerated: 'Yes' or 'No' |
frequency_type_schedule |
A simple text based day of week schedule for 'Weekly' & 'Biweekly' frequency types in the form of 'Su, M, T, W, Th, Fr, S' for weekly and '(Wk 1: Su, M, T, W, Th, Fr, S) & (Wk 2: Su, M, T, W, Th, Fr, S)' for Biweekly |