School Census - Dimension Data

The CFR data is processed along with a few dimension data to create the downstream dataset named maintained_schools_master_list and an accompanying transparency file. These dimension data are:

Pupil Referral Unit (PRU) & Alternative Provision (AP)
General Hospital schools
Get Information About School (GIAS)
School Census
Special Education Needs (SEN)
School Workforce Census

Pupil data from the school census is produced by the Department of Education and published in the Pupils and their Characteristics - GOV.UK School Education statistic website, this data is updated annually by July.

Getting School Census Data

School Census Dimension Data

Click on URL link to download school-pupils-and-their-characteristics_20YY-yy zipped folder to local folder, where 20YY-yy represents the reporting academic year. For instance school-pupils-and-their-characteristics_2024-25 when generating 2024-2025 CFR downstream dataset.
In the supporting-files sub-folder of the school-pupils-and-their-characteristics_20YY-yy zipped folder, access the
spc_school_level_underlying_data_20YY.csv file.
Clean data by replacing any value returned as z to 0.00 (zero).
Save and rename file to Dim_SchoolLevelPupilCensus_20YY.csv where 20YY is the end of the reporting academic year. For instance, Save As Dim_SchoolLevelPupilCensus_2025.csv for 2024-2025 academic year.

School Census Slowly Changing Dimension Data

In the page view of the Pupils and their Characteristics - GOV.UK School Education statistic website, navigate to Releases in this series to expand on the View releases button where previous academic year’s data can be accessed.
For School Census SCD data, access the previous data by clicking on Academic year 20yy/yy link where 20yy/yy represents the previous reporting academic year. For instance, Academic year 2023/24 would be accessed to download school-pupils-and-their-characteristics_2023-24 zipped folder when generating 2024-2025 CFR downstream dataset.
Repeat step 2 and 3 as listed above.
Save and rename SCD file to Dim_SchoolLevelPupilCensus_20YY.csv where 20YY is the downloaded academic year. For instance, Save As Dim_SchoolLevelPupilCensus_2024.csv when generating 2024-2025 CFR downstream dataset.

Flat File Ingestion into CFRyy Local Database

Using a database GUI Tool, ingest both flat files as a tables into the newly created local database
- Confirm that the two destination table names reflect their respective reporting academic years
- Confirm schema name for both destination tables is dbo
- In the Modify Columns tab, confirm column data type matches with GIAS Fields Data Type as detailed below

School Census Fields Data Type

Column Name	Data type
time_period	float
time_identifier	nvarchar
geographic_level	nvarchar
country_code	nvarchar
country_name	nvarchar
region_name	nvarchar
region_code	nvarchar
urn	float
old_la_code	float
la_name	nvarchar
new_la_code	nvarchar
estab	float
school_name	nvarchar
laestab	float
phase_type_grouping	nvarchar
trust	nvarchar
opendate	datetime
sex_of_school_description	nvarchar
typeofestablishment_name	nvarchar
middle_school	nvarchar
all_through_school	nvarchar
hospital_school	nvarchar
denomination	nvarchar
admissions_policy	nvarchar
district_administrative_code	nvarchar
district_administrative_name	nvarchar
parl_con_code	nvarchar
parl_con_name	nvarchar
ward_code	nvarchar
ward_name	nvarchar
urban_rural	nvarchar
school_postcode	nvarchar
academy_flag	nvarchar
headcount total female	float
headcount total male	float
full-time pupils	float
part-time pupils	float
headcount of pupils	float
fte pupils	float
male boarders	float
female boarders	float
total boarders	float
number of FSM eligible pupils taking a free school meal on census day	nvarchar
% of FSM eligible pupils taking free school meals	nvarchar
number of pupils known to be eligible for free school meals	nvarchar
% of pupils known to be eligible for free school meals	nvarchar
Number of pupils (used for FSM calculation in Performance Tables)	nvarchar
number of pupils known to be eligible for free school meals (Performance Tables)	nvarchar
% of pupils known to be eligible for free school meals (Performance Tables)	nvarchar
Infants taken a free school meal on census day	nvarchar
number of pupils classified as white British ethnic origin	nvarchar
% of pupils classified as white British ethnic origin	nvarchar
number of pupils classified as Irish ethnic origin	nvarchar
% of pupils classified as Irish ethnic origin	nvarchar
number of pupils classified as traveller of Irish heritage ethnic origin	nvarchar
% of pupils classified as traveller of Irish heritage ethnic origin	nvarchar
number of pupils classified as any other white background ethnic	nvarchar
% of pupils classified as any other white background ethnic origin	nvarchar
number of pupils classified as Gypsy/Roma ethnic origin	nvarchar
% of pupils classified as Gypsy/Roma ethnic origin	nvarchar
number of pupils classified as white and black Caribbean ethnic	nvarchar
% of pupils classified as white and black Caribbean ethnic origin	nvarchar
number of pupils classified as white and black African ethnic origin	nvarchar
% of pupils classified as white and black African ethnic origin	nvarchar
number of pupils classified as white and Asian ethnic origin	nvarchar
% of pupils classified as white and Asian ethnic origin	nvarchar
number of pupils classified as any other mixed background ethnic	nvarchar
% of pupils classified as any other mixed background ethnic origin	nvarchar
number of pupils classified as Indian ethnic origin	nvarchar
% of pupils classified as Indian ethnic origin	nvarchar
number of pupils classified as Pakistani ethnic origin	nvarchar
% of pupils classified as Pakistani ethnic origin	nvarchar
number of pupils classified as Bangladeshi ethnic origin	nvarchar
% of pupils classified as Bangladeshi ethnic origin	nvarchar
number of pupils classified as any other Asian background ethnic	nvarchar
% of pupils classified as any other Asian background ethnic origin	nvarchar
number of pupils classified as Caribbean ethnic origin	nvarchar
% of pupils classified as Caribbean ethnic origin	nvarchar
number of pupils classified as African ethnic origin	nvarchar
% of pupils classified as African ethnic origin	nvarchar
number of pupils classified as any other black background ethnic	nvarchar
% of pupils classified as any other black background ethnic orig	nvarchar
number of pupils classified as Chinese ethnic origin	nvarchar
% of pupils classified as Chinese ethnic origin	nvarchar
number of pupils classified as any other ethnic group ethnic origin	nvarchar
% of pupils classified as any other ethnic group ethnic origin	nvarchar
number of pupils unclassified	nvarchar
% of pupils unclassified	nvarchar
number of pupils of compulsory school age and above (rounded)	nvarchar
number of pupils whose first language is known or believed to be English	nvarchar
% of pupils whose first language is known or believed to be English	nvarchar
number of pupils whose first language is known or believed to be other than English	nvarchar
% of pupils whose first language is known or believed to be other than English	nvarchar
number of pupils whose first language is unclassified	nvarchar
% of pupils whose first language is unclassified	nvarchar
number of pupils who are a young carer	nvarchar
number_of_dual_subsidiary_registrations	nvarchar
school_time	nvarchar