Differences

This shows you the differences between two versions of the page.

--- emis_user_manual_data_warehouse [2020/06/29 07:55] – [Flows] ghachey
+++ emis_user_manual_data_warehouse [2024/12/23 11:16] (current) – [Flows] 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>
-===== Notes on School Disaggregations =====
+===== Notes on School Aggregations =====
 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>
+Details about the columns names in this table are included below:
+  * **SurveyYEar**: school year of survey to collect data
+  * **ClassLevel**: the class level or grade
+  * **YearOfEd**: the official year of education of the class level
+  * **OfficialAge**: the official age of the class level
+  * **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**: males intakes (new enrollments without repeaters)
+  * **intakeF**: females intakes (new enrollments without repeaters)
+  * **intake**: total intakes (new enrollments without repeaters)
+  * **nEnrolM**: males enrolled of official age
+  * **nEnrolF**: females enrolled of official age
+  * **nEnrol**: total enrolled of official age
+  * **nIntakeM**: males intakes of official age
+  * **nIntakeF**: females intakes of official age
+  * **nIntake**: total intakes of official age
+  * **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.
@@ Line 133: / Line 164: @@
 ==== Flows ====
+=== Views warehouse.CohortNation, warehouse.CohortDistrict and warehouse.CohortSchool ===
+The primary views to produce flow ratios indicators.
+<note important>Flows are more advanced indicators with slightly more advanced mathematics behind them then other ratios seen so far.</note>
+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:
+  * **Enrol**: the enrolments (e.g. Grade 5 enrolments for 2018-19)
+  * **Rep**: the repeaters (e.g. Grade 5 repeaters for 2018-19. In other words, Grade 5 cohort of previous school year staying in Grade 5)
+  * **RepNY**: the repeaters for the next school year (e.g. Grade 5 repeaters for 2019-20.)
+  * **TroutNY**: the transfers out for the next school year (e.g. Grade 5 transfers out for 2019-20)
+  * **EnrolNYNextLevel**: the enrolments for the next school year into the next class level/grade (e.g. Grade 6 enrolments for 2019-20)
+  * **RepNYNextLevel**: the repeaters for the next school year into the next class level/grade (e.g. Grade 6 repeaters for 2019-20)
+  * **TrinNYNextLevel**: the transfers in for the next school year (e.g. Grade 6 transfers in for 2019-20)
+The formulas are either identical or mathematical equivalent (or near equivalent) to the ones in the UNESCO UIS Education Indicators
+Technical guidelines.
+  * **Promotion Rate** $$= \frac{(EnrolNYNextLevel - RepNYNextLevel)}{Enrol}$$
+  * **Repetition Rate** $$= \frac{RepNY} {Enrol}$$
+  * **Survival Rate** (year on year; not to a particular year) $$= \frac{Promotion Rate}{( 1 - Repetition Rate)}$$ which also $$= \frac{\frac{(EnrolNYNextLevel - RepNYNextLevel)}{Enrol}}{( 1 - \frac{RepNY}{Enrol})}$$ which also $$= \frac{EnrolNYNextLevel - RepNYNextLevel}{Enrol - RepNY}$$
+  * **Dropout Rate** = $$1 - (Promotion Rate + Repetition Rate)$$
+<note important>
+Note that the formula used in some UN publications is more like this Survival by Grade Level (year on year) $$= \frac{(EnrolNYNextLevel - RepNYNextLevel)}{Enrol}$$ while we use $$\frac{(EnrolNYNextLevel - RepNYNextLevel)}{Enrol - RepNY}$$
+The difference being that we take into consideration the repeaters from the original cohort of the first year which will not be illegible for promotion. It provides a slightly more precise survival rate when data is accurately collected.
+</note>
+A sample workbook showing how these many flow indicators can be calculated including some analysis is included below.
+{{ :user-manual-data-warehouse:flows-ratios.xlsx |}}
@@ Line 139: / Line 208: @@
+=== Table warehouse.TeacherLocation ===
-==== Special Education ====
+The table warehouse.TeacherLocation sits on the transition between:
-=== Table warehouse.Disability ===
+  * teacher reporting derived from the School Surveys (i.e. PDF annual surveys, Excel Workbook); and
+  * teacher reporting derived from Appointments, and teacher training records from the web UI (i.e. manual entry of records in UI).
-Lowest level table in the warehouse holding disability data down to the school level by class level and gender.
+<note important>The tables Establishment and Teacher Appointments define the structure of the teaching establishment: i.e. the collection of teaching positions, and the teachers that are appointed to those positions.  This is where we expect the teachers to be. The Establishment and Appointment framework is not currently in use by most users but data is simply gathered using the survey method.</note>
-<note important>Having the school ID and the SurveyDimensionID an advanced user can further build a table by linking to the warehouse.dimensionSchoolSurvey table with all possible disaggregation supported by the system (e.g. District, Region, Authority, Authority Government, School Type, etc.). This is also provided as example in the sample analysis workbook.</note>
+<note important>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 could check that teachers are where they are supposed to be and report exceptions. In simpler setups (all of them currently) only the surveys are used and not the Establishment/Appointment teacher planning tool.</note>
-=== Table warehouse.tableDisability ===
+Regardless of the setup, this table brings these two sets of data together. 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 (i.e. an appointment or reported through survey), whether the teacher is qualified and certified as determined by any possible means, the education sector, the max ISCED level taught, which grades taught, and some other details.
-Similar to warehouse.Disability, this table contains disability information aggregated by Gender, Class Level, District, Authority, School Type.
+=== Tables/Views warehouse.TeacherJob* ===
-<note tip>Use this table if you are after some analysis based on class level (i.e. Grade 1, Grade 2, etc.)</note>
+Tables containing data about the number of staff and their job title, staff type (i.e. teaching and non-teaching) and teacher type (i.e. regular, special ed.). They come in various disaggregations:
+  * warehouse.TeacherJobTable
+  * warehouse.TeacherJobSchool
+  * warehouse.TeacherJobDistrict
+  * warehouse.TeacherJobAuthority
+  * warehouse.TeacherJobAuthorityGovt
+  * warehouse.TeacherJobRegion
+  * warehouse.TeacherJobIsland
+  * warehouse.TeacherJobNation
+  * warehouse.TeacherJobSchoolType
+=== Tables/Views warehouse.TeacherActivity* ===
+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.
+{{ :user-manual-data-warehouse:teachers.xlsx |}}
+==== Special Education ====
 === 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. Mainly this can go down to the individual school level and therefore all its possible disaggregations. More precisely it can disaggregate data by:
-<note tip>Mainly this can go down to the individual school level and therefore would be used if this is what is needed. Otherwise, similar slighter higher level versions of this table could be used to do the same (e.g. view warehouse.SpecialEd)</note>
+  * School
+  * Education Level
+  * Class level (grades)
+  * Gender
+  * Age
+  * Authority
+  * District
+  * AuthorityGovt
+  * SchoolType
+  * Region
+  * Ethnicity
+  * SpEd Environment
+  * Disability
+  * English Learner
 === View warehouse.SpecialEd ===
-Similar to table.enrolSpEd with special education data (i.e. Environment, Disability, EnglishLearner, Ethnicity) but it does not disaggregate down to the School level, only by
+Similar to table.enrolSpEd above but it does not disaggregate down to the School level, only by
   * Education Level
@@ Line 172: / Line 307: @@
   * SchoolType
   * 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
@@ Line 178: / Line 317: @@
 {{ :user-manual-data-warehouse:special-education-analysis.xlsx |}}
+{{ :user-manual-data-warehouse:special-education-in-depth-analysis.xlsx |}}
 ==== School Accreditation ====
@@ Line 261: / Line 402: @@
 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>You do not need to use this table directly.</note>
-==== Flows ====
@@ Line 327: / Line 475: @@
 <note important>This table is geared towards advanced data people.</note>
-Consolidates the overall result of inspections. Filter on InspectionType to restrict to accreditations
+Consolidates the overall result of inspections with all the typical school disaggregations. Filter on InspectionType to restrict to specific inspections (e.g. School Accreditation, WASH, etc.)
 <note important>This table is useful for pivot tables, and crossfilters, allowing multiple dimensions to be manipulated interactively</note>