Migrating to more precise teacher count based on weighting system [HPR:2022]

[Ghislain Hachey]

This issue is to put notes as we think through the process of migrating from the simpler assignment of a sector to a teacher based on its highest grade taught to a more precise weighted system.

I've looked at TeacherFtptSchool and this is great and will definitely supersede the old definition. As discussed we will need to polish things up likely next year.

However, there is one issue we are still faced with in the meanwhile. Not only the Count tables and views are based on the old Count tables. All the following are directly built using the old definition. And some several are in use in reports, online dashboard, public mobile phone app, etc.

In summary, the following ones are affected and will need careful review:

• SchoolStaffCount table (ok for now, we just use new TeacherFtptSchool)
• TeacherCountSchool view (ok for now, we just use new TeacherFtptSchool)
• TeacherCountTable view (ok for now, we just use new TeacherFtptSchool)
• TeacherCount[Disaggregation] views (ok for now, we just use new TeacherFtptSchool)
• TeacherFlowDistrict view (I don't see an easy way to base this on the new definition, anybody carefully looking at this data will notice by sector totals don't line up)
• TeacherLocation table (I don't see an easy way to base this on the new definition, anybody carefully looking at this data will notice by sector totals don't line up)
• TeacherLocationDistrict view (I don't see an easy way to base this on the new definition, anybody carefully looking at this data will notice by sector totals don't line up)
• PupilTeacherRatio[Disaggregation] views (I think I may have a solution to base this on new definition without braking anything, see my propose view in next comment)

Correct me if I am wrong but I don't think that the following tables are not affected by this new improvement on staff/teacher count.

• TeacherActivitySchool table
• TeacherActivityTable table
• TeacherActivity[Disaggregation] views
• TeacherJobSchool table
• TeacherJobTable table
• TeacherJob[Disaggregation] views

‌

[Ghislain Hachey]

For all the PupilTeacherRatio views I think we can have a quick win here.

I propose we consider changing the view [warehouse].[PupilTeacherRatioSchool] to use warehouse.TeacherFtptSchool instead of warehouse.SchoolStaffCount. To assess this I have created a temporary view [warehouse].[PupilTeacherRatioFtptSchool] and compare output with [warehouse].[PupilTeacherRatioSchool] While the number will inevitably change as explained in the docs of the view below I think this still works correctly without breaking anything.

USE [FEDEMIS-TEST]
GO

/****** Object:  View [warehouse].[PupilTeacherRatioSchool]    Script Date: 8/23/2021 2:40:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Ghislain Hachey
-- Create date: 23/08/2021
-- Description: Warehouse - Teacher Pupil Ratio based on newer warehouse.TeacherFtptSchool
-- 
-- For backwards compatibility:
--   * FTPT is used to translate to NumTeachers (i.e. includes all teachers whether full-time (only assigned teaching grades) 
--     or part-time (assigned teaching grades and other duties like admin)
--   * Add Sector column as before with based on the mapping in lkpLevels (i.e. ilsCode -> secCode) 
--
-- Before this was based on the Obsolete StaffCountSchool (TeacherCountSchool, etc.). The total numbers
-- by sector were whole numbers (no decimal) because the business rule was a teacher is assigned the sector
-- based on its highest grade taught. Now the total numbers by sector will be more precise but with decimal
-- This is because the new business rule is based on a weighted system as per UIS guidelines and is a net
-- improvement in data. If you were to compare to the old warehouse.TeacherPupilRatioSchool with this one
-- you would still find that totals across all sectors would be the same! For example, before you could have a
-- school in a year with 1 teacher in ECE, 3 in Primary and 2 in Secondary. While now you would have something like
-- 1 teacher in ECE, 4.333 in Primary and 1.666 in Secondary. The school still have a total of 6 teachers except now
-- the distribution in sectors is more intelligent.

-- Calculating Pupil Teacher Ratios requires having the Enrolment values on the same row as the
-- teacher numbers.
-- this view splits by sector within school where applicable,
-- and allow aggregations up from school
-- =============================================
CREATE VIEW [warehouse].[PupilTeacherRatioFtptSchool]
WITH VIEW_METADATA
AS

Select schNo
, SurveyYear
, DistrictCode
, AuthorityCode
, SchoolTypeCode
, Sector
, ISCEDSubClass
, sum(FTPT) NumTeachers
, sum(FTPTC) Certified
, sum(FTPTQ) Qualified
, sum(FTPTQC) CertQual
, sum(Enrol) Enrol
FROM
(
Select schNo
, SurveyYear
, DistrictCode
, AuthorityCode
, SchoolTypeCode
, secCode Sector
, ISCEDSubClass
, FTPT
, FTPTC
, FTPTQ
, FTPTQC
, convert(int, null) Enrol
From warehouse.TeacherFtptSchool TFS
LEFT JOIN 
(
SELECT DISTINCT ilsCode, secCode FROM [dbo].[lkpLevels]
) L
ON TFS.ISCEDSubClass=L.ilsCode

-- IMPORTANT now excluding 'Admin' and 'Other' contributions to identify 'Academic' staff
-- or more precisely 'Teaching staff' only
WHERE ISCEDSubClass != 'A' AND ISCEDSubClass != 'X'

UNION ALL
    Select E.schNo, E.surveyYear
    , DSS.[District Code] DistrictCode
    , DSS.AuthorityCode
    , DSS.SchoolTypeCode
    , L.secCode Sector
    , L.ilsCode ISCEDSubClass
    , null NumTeachers
    , null Certified
    , null Qualified
    , null CertQual
    , Enrol
    from warehouse.enrol E
        INNER JOIN lkpLevels L
            ON E.ClassLevel = L.codeCode
        INNER JOIN Warehouse.bestSurvey S
            ON S.schNo = E.schNo
            AND S.SurveyYear = E.surveyYear
        LEFT JOIN Warehouse.dimensionSchoolSurvey DSS
            ON Dss.[Survey ID] = S.surveyDimensionID
) U
GROUP BY
schNo
, SurveyYear
, DistrictCode
, AuthorityCode
, SchoolTypeCode
, Sector
, ISCEDSubClass
GO

-- Examine both old and new
SELECT schNo
    , SurveyYear
    , DistrictCode
    , AuthorityCode
    , SchoolTypeCode
    , Sector
    , sum(NumTeachers)
    , sum(Certified)
    , sum(Qualified)
    , sum(CertQual)
    , sum(Enrol)
FROM [warehouse].[PupilTeacherRatioSchool]
GROUP BY
    schNo
    , SurveyYear
    , DistrictCode
    , AuthorityCode
    , SchoolTypeCode
    , Sector
ORDER BY
    schNo
    , SurveyYear
    , DistrictCode
    , AuthorityCode
    , SchoolTypeCode
    , Sector

SELECT schNo
    , SurveyYear
    , DistrictCode
    , AuthorityCode
    , SchoolTypeCode
    , Sector
    --, ISCEDSubclass
    , sum(NumTeachers)
    , sum(Certified)
    , sum(Qualified)
    , sum(CertQual)
    , sum(Enrol)
FROM [warehouse].[PupilTeacherRatioFtptSchool]
GROUP BY
    schNo
    , SurveyYear
    , DistrictCode
    , AuthorityCode
    , SchoolTypeCode
    , Sector
    --, ISCEDSubclass
ORDER BY
    schNo
    , SurveyYear
    , DistrictCode
    , AuthorityCode
    , SchoolTypeCode
    , Sector
    --, ISCEDSubclass

‌

[Ghislain Hachey]

I’m starting to think that instead of replacing perhaps we should consider supporting in addition.

For example, we can have

• [warehouse].[PupilTeacherRatioSchool] (which is the version using the “dumb” definition based on the assignment of a sector to individual teachers based on their highest grade taught
• [warehouse].[PupilTeacherRatioFtptSchool] (which is the version above built on top of [warehouse].[TeacherFtptSchool] for a more precise allocation of teachers into sectors
• [warehouse].[PupilTeacherRatioFteSchool] (optionally a version like above but built on top of [warehouse].[TeacherFtptSchool] using the FTE values for an even more precise allocation of teachers into sectors

These two (or three) variations would have the same columns but following slightly different business rules from dumber/simpler to higher precision/more complicated. This could be done for most of the stuff we have for teachers in warehouse. Nothing breaks and we please as many as possible.

‌

[Ghislain Hachey]

Most of the stuff in here was done. Really just need more documentation on everything that is in the warehouse.