emis_user_manual_data_warehouse
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
emis_user_manual_data_warehouse [2020/06/24 02:43] – [School Accreditation] ghachey | emis_user_manual_data_warehouse [2022/02/17 15:38] – [Enrolments] ghachey | ||
---|---|---|---|
Line 47: | Line 47: | ||
<note tip>The tip note is is geared towards more other users either providing tips on how to consume the data or pointers in more friendly tables and views.</ | <note tip>The tip note is is geared towards more other users either providing tips on how to consume the data or pointers in more friendly tables and views.</ | ||
- | ===== Notes on School | + | ===== Notes on School |
The Pacific EMIS and therefore the data warehouse offers a very wide range of possible disaggregation. In particular there is one group of disaggregation they will repeatedly come back. Any data that is available by school will also automatically be available to aggregate by all the following: | The Pacific EMIS and therefore the data warehouse offers a very wide range of possible disaggregation. In particular there is one group of disaggregation they will repeatedly come back. Any data that is available by school will also automatically be available to aggregate by all the following: | ||
Line 119: | Line 119: | ||
<note tip>The table warehouse.EdLevelERDistrict is essentially the same but with the added ability to disaggregate by districts.</ | <note tip>The table warehouse.EdLevelERDistrict is essentially the same but with the added ability to disaggregate by districts.</ | ||
+ | |||
+ | Details about the columns names in this table are included below: | ||
+ | |||
+ | * **SurveyYEar**: | ||
+ | * **ClassLevel**: | ||
+ | * **YearOfEd**: | ||
+ | * **OfficialAge**: | ||
+ | * **enrolM**: males enrolled | ||
+ | * **enrolF**: females enrolled | ||
+ | * **enrol**: total enrolled | ||
+ | * **repM**: male repeaters | ||
+ | * **repF**: female repeaters | ||
+ | * **rep**: total repeaters | ||
+ | * **psaM**: male pre-school attenders | ||
+ | * **psaF**: female pre-school attenders | ||
+ | * **psa**: total pre-school attenders | ||
+ | * **intakeM**: | ||
+ | * **intakeF**: | ||
+ | * **intake**: total intakes (new enrollments without repeaters) | ||
+ | * **nEnrolM**: | ||
+ | * **nEnrolF**: | ||
+ | * **nEnrol**: total enrolled of official age | ||
+ | * **nIntakeM**: | ||
+ | * **nIntakeF**: | ||
+ | * **nIntake**: | ||
+ | * **nRepM**: male repeaters of official age | ||
+ | * **nRepF**: female repeaters of official age | ||
+ | * **nRep**: total repeaters of official age | ||
+ | * **popM**: male population as per most recent population projection in use | ||
+ | * **popF**: female population as per most recent population projection in use | ||
+ | * **pop**: total population as per most recent population projection in use | ||
The workbook below shows some sample analysis. This workbook does a bit more than simple pivot tables and charts on the data but also computes new data using formulas. | The workbook below shows some sample analysis. This workbook does a bit more than simple pivot tables and charts on the data but also computes new data using formulas. | ||
Line 135: | Line 166: | ||
+ | === Views warehouse.CohortNation, | ||
+ | The primary views to produce flow ratios indicators. | ||
- | ==== Special Education ==== | + | <note important> |
- | === Table warehouse.Disability === | + | Each of these views produce the same data but with different disaggregations (i.e. down to school level, district level and nation wide and by gender). Each record will have the following data which must be used in various formulas to produce the final indicators. There is a bold emphasis on the most important ones to produce most flow indicators with enough precision. These flows use the reconstructed cohort methods and must have consistent collection of enrolments and repeaters for two consecutive years. Details about the columns names in this view are included below: |
- | Lowest level table in the warehouse holding disability data down to the school level by class level and gender. | + | * **Enrol**: |
+ | * **Rep**: | ||
+ | * **RepNY**: the repeaters for the next school | ||
+ | * TroutNY: the transfers out for the next school year (e.g. Grade 5 transfers out for 2019-20) | ||
+ | * **EnrolNYNextLevel**: | ||
+ | * **RepNYNextLevel**: | ||
+ | * TrinNYNextLevel: | ||
- | <note important> | + | The formulas are either identical or mathematical equivalent |
+ | Technical guidelines. | ||
- | === Table warehouse.tableDisability === | + | * Promotion Rate = (EnrolNYNextLevel - RepNYNextLevel) / Enrol |
+ | * Repetition Rate = RepNY / Enrol | ||
+ | * Survival Rate (year on year; not to a particular year) = Promotion Rate / ( 1 - Repetition Rate) | ||
+ | * Dropout Rate = 1 - (Promotion Rate + Repetition Rate) | ||
+ | * Transition Rate (year on year) = (EnrolNYNextLevel - RepNYNextLevel) / Enrol | ||
- | Similar to warehouse.Disability, | + | A sample workbook showing how these many flow indicators can be calculated including some analysis is included below. |
- | <note tip>Use this table if you are after some analysis based on class level (i.e. Grade 1, Grade 2, etc.)</note> | + | {{ : |
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ==== Teachers ==== | ||
+ | |||
+ | |||
+ | === Table warehouse.TeacherLocation === | ||
+ | |||
+ | The table warehouse.TeacherLocation sits on the transition between: | ||
+ | |||
+ | * teacher reporting derived from the School Surveys; and | ||
+ | * teacher reporting derived from Appointments, | ||
+ | |||
+ | The tables Establishment and Teacher Appointments define the structure of the teaching establishment: | ||
+ | |||
+ | The TeacherSurvey table holds the records of where teachers are reported to be by the School Survey. This is where teachers are. Ideally, TeacherSurvey functions as a verification of TeacherAppointments. We can check that teachers are where they are supposed to be and report exceptions. In simpler setups only the surveys are used and not the Establishment/ | ||
+ | |||
+ | Regardless of the setup, this table brings these two sets of data together (In fact considerably more detailed views in the Establishment planning component can also perform this reconciliation.) In some systems, we may have the requirement to report | ||
+ | |||
+ | In there there is a record for each teacher for each year they were active. It contains the gender, DoB, age group of teacher, the school, role, source | ||
+ | |||
+ | === Tables/ | ||
+ | |||
+ | Tables containing data about the number of staff. They come in various disaggregations: | ||
+ | |||
+ | * warehouse.TeacherJobTable | ||
+ | * warehouse.TeacherJobSchool | ||
+ | * warehouse.TeacherJobDistrict | ||
+ | * warehouse.TeacherJobAuthority | ||
+ | * warehouse.TeacherJobAuthorityGovt | ||
+ | * warehouse.TeacherJobRegion | ||
+ | * warehouse.TeacherJobIsland | ||
+ | * warehouse.TeacherJobNation | ||
+ | * warehouse.TeacherJobSchoolType | ||
+ | |||
+ | |||
+ | === Tables/ | ||
+ | |||
+ | Tables containing data about the number of staff teaching at specific grades. They come with various disaggregations support. | ||
+ | |||
+ | * warehouse.TeacherActivityTable | ||
+ | * warehouse.TeacherActivitySchool | ||
+ | * warehouse.TeacherActivityDistrict | ||
+ | * warehouse.TeacherActivityAuthority | ||
+ | * warehouse.TeacherActivityAuthorityGovt | ||
+ | * warehouse.TeacherActivityRegion | ||
+ | * warehouse.TeacherActivityIsland | ||
+ | * warehouse.TeacherActivityNation | ||
+ | * warehouse.TeacherActivitySchoolType | ||
+ | |||
+ | They are generally used to produce Pupil-Teacher Ratios (PTR) with higher accuracy. The PTR = Enrol / Teachers. The warehouse tables provide the following columns: | ||
+ | |||
+ | * Enrol: enrolments for the grade (and whatever other disggregation) | ||
+ | * Staff: total teachers teaching at that grade (regardless of how much time in a day they teach there) | ||
+ | * StaffW: teachers full time equivalent teaching at that grade | ||
+ | * GradePTR: the PTR calculated without regards for full time equivalent | ||
+ | * GradePTRW: more precise PTR calculated with StaffW (i.e. full time equivalent) | ||
+ | |||
+ | A sample workbook on teacher data analysis is included below. | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ==== Special Education ==== | ||
=== Table warehouse.enrolSpEd === | === Table warehouse.enrolSpEd === | ||
- | This is the foundation for special education student data. It can be used directly and supports the highest flexibility and disaggregation power. | + | This is the foundation for special education student data. It can be used directly and supports the highest flexibility and disaggregation power. |
- | <note tip> | + | * School |
+ | * Education Level | ||
+ | * Class level (grades) | ||
+ | * Gender | ||
+ | * Age | ||
+ | * Authority | ||
+ | * District | ||
+ | * AuthorityGovt | ||
+ | * SchoolType | ||
+ | * Region | ||
+ | * Ethnicity | ||
+ | * SpEd Environment | ||
+ | * Disability | ||
+ | * English Learner | ||
=== View warehouse.SpecialEd === | === View warehouse.SpecialEd === | ||
- | Similar to table.enrolSpEd | + | Similar to table.enrolSpEd |
* Education Level | * Education Level | ||
Line 169: | Line 305: | ||
* SchoolType | * SchoolType | ||
* Region | * Region | ||
+ | * Ethnicity | ||
+ | * SpEd Environment | ||
+ | * Disability | ||
+ | * English Learner | ||
Use this view for higher level statistics about special education data across any of the above. For example of how to use this warehouse view refer to the same file attached in warehouse.enrolSpEd | Use this view for higher level statistics about special education data across any of the above. For example of how to use this warehouse view refer to the same file attached in warehouse.enrolSpEd | ||
- | |||
- | === Special Education Sample Analysis Workbook === | ||
The following analysis workbook makes use of all the above tables and views and provides some examples of analysis that can be done with the data. | The following analysis workbook makes use of all the above tables and views and provides some examples of analysis that can be done with the data. | ||
{{ : | {{ : | ||
+ | |||
+ | {{ : | ||
==== School Accreditation ==== | ==== School Accreditation ==== | ||
+ | |||
+ | === View warehouse.Accreditation[Aggregation] === | ||
+ | |||
+ | Many views are essentially the same but offering different aggregations. For example, | ||
+ | |||
+ | * warehouse.AccreditationAuthority | ||
+ | * warehouse.AccreditationAuthorityGovt | ||
+ | * warehouse.AccreditationAuthorityGovt | ||
+ | * warehouse.AccreditationDistrict | ||
+ | * warehouse.AccreditationNation | ||
+ | * warehouse.AccreditationSchool | ||
+ | |||
+ | All offer the same data but doing the analysis by authority, authority government (public/ | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | === View warehouse.AccreditationByStandard[Aggregation] === | ||
+ | |||
+ | All the views starting with " | ||
+ | |||
+ | * warehouse.AccreditationByStandardAuthority | ||
+ | * warehouse.AccreditationByStandardAuthorityGovt | ||
+ | * warehouse.AccreditationByStandardDistrict | ||
+ | * warehouse.AccreditationByStandardNation | ||
+ | * warehouse.AccreditationByStandardSchool | ||
+ | * warehouse.AccreditationByStandardTable | ||
+ | |||
+ | All offer the same data but doing the analysis by authority, authority government (public/ | ||
+ | |||
+ | {{ : | ||
=== View warehouse.AccreditationClassic === | === View warehouse.AccreditationClassic === | ||
Line 197: | Line 367: | ||
* time series | * time series | ||
- | <note tip>Use this view for the most powerful analysis of school accreditation data such.</ | + | <note tip>Use this view for the most powerful analysis of school accreditation data such as in-depth analysis of criteria and sub-criteria.</ |
- | TODO sample spreadsheet. | + | <note important> |
- | === View warehouse.Accreditation[Aggregation] === | + | TODO sample spreadsheet |
- | + | ||
- | Many views are essentially the same but offering different aggregations. For example, | + | |
- | + | ||
- | * warehouse.AccreditationAuthority | + | |
- | * warehouse.AccreditationAuthorityGovt | + | |
- | * warehouse.AccreditationAuthorityGovt | + | |
- | * warehouse.AccreditationDistrict | + | |
- | * warehouse.AccreditationNation | + | |
- | * warehouse.AccreditationSchool | + | |
- | + | ||
- | All offer the same data but doing the analysis by authority, authority government (public/ | + | |
- | + | ||
- | {{ : | + | |
<note warning> | <note warning> | ||
Line 243: | Line 400: | ||
Same of warehouse.enrol except this table aggregates this data up to District, Authority, SchoolTypeCode and is used as the basis for higher level aggregations of any of these enrolment data items. | Same of warehouse.enrol except this table aggregates this data up to District, Authority, SchoolTypeCode and is used as the basis for higher level aggregations of any of these enrolment data items. | ||
+ | |||
+ | ==== Teachers ==== | ||
+ | |||
+ | === Table warehouse.TeacherQual === | ||
+ | |||
+ | Intermediate table to assist in calculation of Certified / Qualified. This table shows, for each teacher, the qualifications reported, and the first year of that report. It is assembled from both the School Surveys, and any named qualifications reported in the web UI. | ||
+ | |||
+ | <note warning> | ||
- | ==== Flows ==== | ||
Line 309: | Line 473: | ||
<note important> | <note important> | ||
- | Consolidates the overall result of inspections. Filter on InspectionType to restrict to accreditations | + | Consolidates the overall result of inspections |
<note important> | <note important> |
emis_user_manual_data_warehouse.txt · Last modified: 2023/12/13 20:52 by ghachey