Validating Teacher levels taught against school enrolments

[Brian Lewis]

This issue arises in exploration around:

A new view to list students under a teacher was added in a similar way teachers can be listed under a school

We want to know what students are enrolled at the level the teacher teaches.

We take the survey responses of Staff page on Grade taught, which find their way directly to warehouse.TeacherLocation.

However, we may not necessarily have any student enrolled at a particular level the teacher is claimed to teach at.

‌

[Brian Lewis]

This does have an implication: the teacher ‘activity’ divides the teacher’s time (1 = FT) between the class levels they teach at. Therefore, including a class they do NOT teach at diminishes their allocation to the classes they do teach, and this will change the reporting of FTE right the way up to the UIS Survey.

Conversely, the class levels the teacher is claimed to teach at will, to that extant, overstate teacher allocation.

[Brian Lewis]

Probably first steps to addressing this should be:

• SQL view to find such ‘phantom classes’ (hereinafter to be referred to thusly!)
• SQL proc to clear the phantom classes from warehouse.TeacherLocation

‌

[Ghislain Hachey]

@Brian Lewis I am not sure I understood the implications correctly but if it does in fact get us to deviate from the current FTE model which was already inline with UIS and the expectations of the SPC team then we need to discuss this a bit more before proceeding in this direction.

[Brian Lewis]

There is an opposite mismatch to consider also: that students are enrolled in a grade, but no teacher teaches that grade.

This query finds these:

Select DISTINCT SE.schNo, SE.stueYear, SE.stueClass
from StudentEnrolment SE
    INNER JOIN DimensionLevel L
        ON Se.stueClass = L.LevelCode
LEFT JOIN warehouse.TeacherLocation TL
    ON SE.schNo = TL.schNo
    AND SE.stueYear = TL.surveyYear
    AND choose(L.[Year of Education]+1,T00,T01,T02,T03,T04,T05,T06,T07,T08,T09,T10, T11,T12,T13,T14, T15) = 1
WHERE TL.tID is null

‌

Turns out (FEDEMIS) to be quite a few.

[Brian Lewis]

@Ghislain Hachey

No this doesn't change the model or the logic of calculating FTE FTPT. It just highlights some anomalies in data.

Here’s some examples in FEDEMIS production:

look at CHK001 in 2021. warehouse.TeacherLocation shows 3 staff, one teaches ECE GR5 GR6. No-one is shown as teaching grades 1 2 3 or 4 , but there are enrolments in those grades.

(In the workbook, there are 4 more teachers, teacherGR2, GR4, GR5 GR6 - but they are flagged as Inactive and so not loaded.)

Conversely, look at YAP458 in 2016. In warehouse.TeacherLocation, every teacher is shown as teaching grade 1 , and most Grade 2

But this is a high school with enrolments GR9 and above.. No-one is actually teaching any Gr1 or 2. So one of these teachers would contribute .60 ~ .75 FTE to ISCED2 because of their allocation to GR1 and/or 2. They should contribute 1.0 to FTE for ISCED2.

Its a small issue I guess in the bigger scheme of things, and certainly doesn’t invalidate the logic or process we are using to calculate FTE - its only that some of the input data to that calculation could be more precise.

As noted above it came to attention thinking about:

A new view to list students under a teacher was added in a similar way teachers can be listed under a school

Note there are a few complications in this ‘view’.

1. A teacher may teach at more than one level
2. More than one teacher may teach at a given level.
3. As explained above: a teacher supposedly teaching at a given level may not match any students enrolled at that level
4. As explained above: students enrolled at a given level may not have a teacher matching that level.

Where 2) holds, we cannot directly identify the pupils taught by a particular teacher. The UI will need to be clear on this.

‌

[Brian Lewis]

Note also that any SQL attempting to ‘phantom classes’ would need to consider that a warehouse rebuild would put them back again!

‌

[Ghislain Hachey]

@Brian Lewis I think we need to discuss this with Weison before proceeding into what seems like a bit of a substantial undertaking with various implications.

[Brian Lewis]

Looking at the extent of issues, I’m thinking now we dont do anything to adjust any existing data - its not going to make so much difference as to be worth the effort in changing stuff and explaining why previously release material is different.

Something to consider when reviewing incoming workbooks.

[Ghislain Hachey]

I agree. This is closer to what I had in mind. Work on preventing this going forward.

A note on the work herein. I think the original requirement requested here was something more simple. For example, we know from census which students are in which school and which teachers in which school. So we know which teacher (may) teach which student by listing all students of the school where that teacher teaches. This is obviously not as good as the more precise version you describe above. I think we need to discuss this with local team and explain this before proceeding.

[Brian Lewis]

go to #1166 for discussion of the original requirement:

A new view to list students under a teacher was added in a similar way teachers can be listed under a school