Question: how was SchoolSurvey data smoothed?

[Ghislain Hachey]

Background: a digest for the years 2015-2020 was prepared with the assistance of UNICEF consultant. The consultant used a smoothing method to change various enrolment data. From the UNICEF report, the “missing teachers data” were also estimated using a “linear interpolation to ensure a consistent timeseries over 2015-20 period”.

A couple of things to clarify:

1. I don’t see any smoothing of teacher data in KEMIS. Does this mean the teacher data from KEMIS will not match the Digest 2015-2020? I’m not sure why they would opt for some linear interpolation with teacher records as they are more often similar or identical to the previous then not.
2. It looks like all those smoothed figures have been loaded into KEMIS table [dbo].[smoothedData]. I can see how the data in dbo.Enrollments was “smoothed” based on this [dbo].[smoothedData] using the procedure [dbo].[ApplySmoothedEnrolment_] (seamingly one record (school&year) at a time.) And I see that comparing the following yields identical results. But it is unclear to me how SchoolSurvey’s summary enrolment data came to be “corrected”?

SELECT ssID, svyYear, schNo, ssEnrolM, ssEnrolF, ssEnrol
FROM [SchoolSurvey]
WHERE ssID IN (
    SELECT ssID
    FROM [dbo].[Enrollments]
    WHERE enOrigin = 'SMOOTHING'
    GROUP BY ssID
)
ORDER BY ssID

SELECT ssID
    , sum(enM) enrolM
    , sum(enF) enrolF
    , sum(enSum) enrolSum
FROM [dbo].[Enrollments]
WHERE enOrigin = 'SMOOTHING'
GROUP BY ssID
ORDER BY ssID

‌

‌

[Ghislain Hachey]

Furthermore, we discussed that in the built warehouse tables under estimate column:

• 0 means truly reported as such
• 1 means estimated (from a previous enrolment as far bar as allowed through sysParam
• 2 a new value that means the data was smoothed.

The question is, I see not a single record in the KEMIS warehouse with the estimate value of 2.

‌

[Brian Lewis]

Teachers: teacher numbers in Pacific EMIS are always obtained by counting the teacher records (TeacherSurvey) at the school. There is no feasible way to implement these “smoothed” teacher counts, since we cannot realistically fabricate a record in TeacherSurvey to support the manufactured total.

‌

[Brian Lewis]

The 2 in Estimate is only used on Enrollments. Its there to get around a technical difficulty- it enables the enrolment numbers to be used from enrolment (like Estimate=0), but still go back to previous year for Repeaters, Disability etc (like Estimate=1) since no attempt was made in the smoothing process to make corresponding changes to these ancillary tables.

[Ghislain Hachey]

The 2 in Estimate is only used on Enrollments.

I still do not see this ‘2'. In dbo.Enrollments I see the string “SMOOTHING” in enOrigin. But I don’t see this 2 anywhere.