UIS Data Publishing RESTful API [2021:HPR]

[Ghislain Hachey]

We need to ensure that we have RESTful API endpoints to extract all the data for a UIS submission. Most--if not all--of this is already available in a warehouse but let’s refine this to the point where it would be possible to easily get any of the following data for the UIS questionnaire. The ones in bold below are the ones the current UIS Exporter tool can already automatically produce with direct SQL connections but I think we can do much better through a dedicated API.

• A1: General information on the data collected in the questionnaire
• A2: Number of students by level of education, intensity of participation, type of institution and sex
• A3: Number of students by level of education, age and sex
• A4: Number of students in formal adult education by level of education, age and sex
• A5: Number of students and repeaters in initial primary education by age, grade and sex
• A6: Number of students and repeaters in initial lower and upper secondary general education by grade, age and sex
• A7: Number of new entrants to Grade 1 in initial education and prior enrollment by age and sex
• A8: Number of graduates by level of education, type of completion and sex (defaults to using graduate from direct outcome field, otherwise using reconstruction cohort proxy indicator data)
• A9: Number of classroom teachers by teaching level of education, employment status, type of institution and sex (of which newly recruited also included)
• A10: Number of classroom teachers by qualified and trained status, teaching level of education, type of institution and sex
• A11: Annual statutory teacher compensation (units of national currency) in public institutions, by teaching level of education- all programmes (general and vocational)
• A12: Number of educational institutions by level of education and type of institution - all programmes (general and vocational)
• A13: Number of educational institutions with ICT services, basic hygiene facilities and the provisioning of life skills-based HIV and sexuality education by level of education - all programmes (general and vocational)
• A14: Number of students enrolled in educational institutions that offer courses on basic computer skills/computing by type of institution and sex- all programmes (general and vocational)
• A15: Number of computers allocated to schools by level of education - all programmes (general and vocational)

This will be useful in extending the UIS Exporter to automatically complete the UIS questionnaire but also provide a means to publish/share this data more widely and in a timely manner.

Refer to #1020

‌

[Ghislain Hachey]

@Brian Lewis already has a working draft of this work. It ws agreed to build the UIS exporter tool directly in the Web UI. This exporter tool will make use of the UIS RESTful API.

[Brian Lewis]

@Ghislain Hachey ready to try uis survey from route /uis

You’ll need to:

. merge pacificemisdb, to pick up warehouse.uisa1…. warehouse.uisa6 procs

• build-deploy, in particular to pick up latest UisTemplate.xlsx from pineapples.client/assets and move to pineapples/assets

As well as downloading the Uis survey, you can download nation/enrol data from here , using dynamic download discussed in #1090

‌

‌

[Ghislain Hachey]

@Brian Lewis that’s great. What version of the UIS questionnaire is the UisTemplate?

[Ghislain Hachey]

@Brian Lewis I only see data in one sheet (A5). I sync’ed the DB, pull the repo and rebuilt the warehouse.

[Ghislain Hachey]

@Brian Lewis on the bright side the numbers are exactly the same as on stand alone UIS Exporter tool we have been using!

[Brian Lewis]

Template is derived from (identical to) UIS_ED_A_2021_EN.xlsx

the same as on stand alone UIS Exporter

good opportunity to retrospectively verify the old tool

‌

[Ghislain Hachey]

Note that the old tool fails with error when trying the 2021. Had to stick with with 2017 template which was a drag having to copy stuff from 2017 to new template. It’s nice it works with latest now.

[Brian Lewis]

missing data

Please change the ISCEDLevelSub table to match this: (ie put a dot in, and add some descriptions)

ilsCode ilCode ilsName
ISCED5.5 ISCED5 Short-cycle tertiary vocational
ISCED1.0 ISCED1 Primary
ISCED6.5 ISCED6 Bachelor's professional
ISCED6.6 ISCED6 Bachelor's orientation unspecified
ISCED7.5 ISCED7 Master's professional
ISCED5.4 ISCED5 Short-cycle tertiary general
ISCED6.4 ISCED6 Bachelor's academic
ISCED7.4 ISCED7 Master's academic
ISCED8.4 ISCED8 Doctoral Academic
ISCED0.1 ISCED0 Early childhood educational development
ISCED0.2 ISCED0 Pre-primary
ISCED2.4 ISCED2 Lower secondary general
ISCED2.5 ISCED2 Lower secondary vocational
ISCED3.4 ISCED3 Upper secondary general
ISCED3.5 ISCED3 Upper secondary vocational
ISCED4.4 ISCED4 Post-secondary non-tertiary general
ISCED4.5 ISCED4 Post-secondary non-tertiary vocational
ISCED7.6 ISCED7 Master's orientation unspecified
ISCED8.5 ISCED8 Doctoral professional
ISCED8.6 ISCED8 Doctoral orientation unspecified
ISCED9.9 ISCED9 Not elsewhere classified

Then change lkpLevels to refer to these codes:

codeCode codeDescription ilsCode
GPRES Pre-school ISCED0.1
GK Grade ECE ISCED0.2
G1 Grade 1 ISCED1.0
G2 Grade 2 ISCED1.0
G3 Grade 3 ISCED1.0
G4 Grade 4 ISCED1.0
G5 Grade 5 ISCED1.0
G6 Grade 6 ISCED1.0
G7 Grade 7 ISCED2.4
G8 Grade 8 ISCED2.4
G9 Grade 9 ISCED3.4
G10 Grade 10 ISCED3.4
G11 Grade 11 ISCED3.4
G12 Grade 12 ISCED3.4

These ISCED codes are matched to range names in the template.

Note that changing these won't break the old tool which hardcodes the association between class level and ISCED level.

[Brian Lewis]

Did you get the other option to work ( Download National Enrolment Data) ?

‌

[Ghislain Hachey]

I’ll try with that change in data. I did get the other option to work. We’ll have to discuss where we are going with this in next call.

[Brian Lewis]

This may help:

USE [FEDEMIS20210104]
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED5.5', N'ISCED5', N'Short-cycle tertiary vocational')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED1.0', N'ISCED1', N'Primary')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED6.5', N'ISCED6', N'Bachelor''s professional')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED6.6', N'ISCED6', N'Bachelor''s orientation unspecified')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED7.5', N'ISCED7', N'Master''s professional')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED5.4', N'ISCED5', N'Short-cycle tertiary general')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED6.4', N'ISCED6', N'Bachelor''s academic')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED7.4', N'ISCED7', N'Master''s academic')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED8.4', N'ISCED8', N'Doctoral Academic')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED0.1', N'ISCED0', N'Early childhood educational development')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED0.2', N'ISCED0', N'Pre-primary')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED2.4', N'ISCED2', N'Lower secondary general')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED2.5', N'ISCED2', N'Lower secondary vocational')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED3.4', N'ISCED3', N'Upper secondary general')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED3.5', N'ISCED3', N'Upper secondary vocational')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED4.4', N'ISCED4', N'Post-secondary non-tertiary general')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED4.5', N'ISCED4', N'Post-secondary non-tertiary vocational')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED7.6', N'ISCED7', N'Master''s orientation unspecified')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED8.5', N'ISCED8', N'Doctoral professional')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED8.6', N'ISCED8', N'Doctoral orientation unspecified')
GO
INSERT [dbo].[ISCEDLevelSub] ([ilsCode], [ilCode], [ilsName]) VALUES (N'ISCED9.9', N'ISCED9', N'Not elsewhere classified')
GO

[Ghislain Hachey]

@Brian Lewis

is there a good reason for the “.” in the ISCEDSub? Since the official code has no “.” we’d better need a good reason to do so.

And this will affect EducationPrograms…

‌

[Ghislain Hachey]

@Brian Lewis And it does break the old UIS Exporter tool. A solution to this during the transition for comparing output of both tools is to run the old UIS within Visual Studio but changing the SQL queries with all the new codes with a “.” everywhere it is relevant.

‌

[Ghislain Hachey]

I am assuming the A2 sheet below reporting M (missing) is because the lkpLevels list a PreK but there is actually no enrolment in that class level.

This brings up another question. How will enrolments in vocational ISCED sub levels be reported?

[Ghislain Hachey]

@Brian Lewis FYI here is some feedback from SPC’s Greg.

• ISCED 01 – code should be Z for not applicable since FSM does not have ECED programmes for 0-2 year olds. Note that head start programs are included in pre-primary category.
• ISCED 4-  include all students enrolled in COM certificates, including teaching program and FSM maritime certificates

But this is more likely a configuration rather that something to fix. And COM, etc. enrollments are not yet available in the EMIS but this should also soon be addressed.

[Ghislain Hachey]

From @Brian Lewis

These notes are very important and thus cleaned up and documented at https://docs.pacific-emis.org/doku.php?id=ftpt_fte_teacher_uis_notes also

Notes on Reporting Teacher Numbers in UIS Survey

Teacher numbers are reported disaggregated by Gender, ISCED Level, and Public/Private. Sheets A9 and A10 both present numbers for Full-and-Part-Time (FTPT), and Full-Time-Equivalent (FTE).

We collect data now of each class level taught by a teacher, as well as teachers performing Admin or 'Other' activities.

A teacher may teach across multiple grades - sometimes these grades will be in a single isced group, but a teacher may teach in grade levels that belong to different ISCED Levels. We can identify this from the collected survey data.

The principle for the allocation of Full-Part-Time is that:

-- a teacher is counted in an ISCED Level if they teach any grade in that ISCED Level. If they teach across multiple levels, they are counted as 1 teacher in each of their ISCED Levels. Effectively, we are counting such a teacher as a "part-time" teacher in each of their ISCED Levels.

Since a teacher may be counted in more than one ISCED category, the total teachers across all ISCED categories may well exceed the total number of teachers. However, this abstract total is not presented anywhere on the UIS Survey.

The principle for allocation of FTE is based on:

-- the weighting of the teacher's time across all their activities. The totals of these weights for a single teacher add to 1. The weight is equally divided across all class activities ( and 'Admin' and 'Other').

This means that the sum of all weighted activities will equal the total number of teachers - since each teacher contributes 1 to this total.

However, since the Uis survey only considers teaching activities, the FTEs reported in that analysis will be less than the total number of teachers by the amount of teacher time spent on Admin and Other activities. This difference will include those teachers whose entire time is spent in Admin and Other activities (ie the weight of the Admin and Other activities is 1) as well as the weighting of 'Admin' Other' for teachers who do mixed duties.

Examples:

All examples are from FSM data, year 2021.

tID = 55 A single activity, grade 7. GRade 7 is ISCED 24, so this teacher contributes 1 to ISCED 24 FullTime PartTime, and 1 FTE for ISCED 24.

tID = 80 A single activity, grade 5. GRade 5 is ISCED 1, so this teacher contributes 1 to ISCED 1 FullTime PartTime, and 1 FTE for ISCED 1.

tID=1351 A single activity - Admin. This teacher does not contribute to the UiS Survey.

tID=1350 2 Activities - Grade 7 and Grade 8. These are both ISCED 24. This teacher contributes 1 FTPT to ISCED 24, and 1 FTE to ISCED 24. (ie all this teacher's time is spent in ISCED 24)

tID=2533 2 Activities - Grade 6 and Grade 7. Grade 6 is ISCED 1 , Grade 7 is ISCED 24. This teacher contributes 1 FTPT to ISCED 1, 1 FTPT to ISCED 24, .5 FTE to ISCED1, .5 FTE to ISCED 24.

tID=4009 2 activities Grade 8 and X. This teacher contributes 1 FTPT to ISCED 24, but .5 FTE to ISCED 24.

tID=1278 3 activities Grade 10, Grade 11, Grade 12. All are in ISCED 34. FTPT: ISCED 34=1 FTE ISCED34 = 1

tID=4772 3 activities Grade 6, Grade 7, Grade 8. FTPT: ISCED 1=1 ISCED 24=1 . FTE: ISCED 1 = .3333 ISCED 24=.66666. The FTE reflects the fact that the teacher teaches 2 classes in ISCED 24, but only 1 in ISCED 1.

tID=2563 3 activities: Grade 6, Grade 7, X. FTPT: ISCED 1 = 1, ISCED 24 = 1. FTE: ISCED 1 = .3333 ISCED 24 = .33333 This teacher spends 2/3 of their time teaching, they contribute .6666 FTE in total.

Missing grade data

We introduced the concept of 'Unspecified' teacher to deal with those situations where the survey records a teacher as teaching, but no grade level data is supplied. warehouse.TeacherLocation records these in field T=1, and provides a "best guess" ISCED SubClass in this case, which can be used in the Uis Reporting. Specifically, these teachers with T=1 record ftpt=1 and FTE=1 for the ISCEDSubClass recorded on warehouse.TeacherLocation.

Example: tID=861 : T=1 to FTPT=1, FTE=1 for ISCEDSubclass ISCED 24.

Other considerations: Qualified and Certified.

The above considerations all apply when restricted to only Qualified or Certified (ie Trained) teachers. Sheet A10 of the Uis Survey is completed using the same logic for Full-Time-Part-Time and FTE. Note there is a minor simplification here in that a teacher teaching across two ISCED levels could theoretically be Qualified or Certified for one but not the other. This distinction is ignored.

Other considerations: In service training

This will also be actioned based on a flag at the tID/survey year level.

‌

[Ghislain Hachey]

There will be no need to have a RESTful API for this I believe. Procedures to get the data into excel is sufficient. This work can be considered complete.