Skip to main content
Financial Benchmarking and Insights Tool

SFB File Generation

Important: Legacy Process (Pre 2024-2025)
Starting from the 2024-2025 academic year (Pipeline Year 2025), the maintained_schools_master_list.csv and maintained_schools_transparency.csv files are automatically generated by the FBIT Python data pipeline natively. You do not need to run any SQL scripts locally.

The pipeline will automatically read the raw CFR_YY-YY_Data.csv file from the default blob storage path and construct the outputs internally.

The manual steps documented below are retained only for reference or for processing historical datasets prior to 2024-2025.

Run SQL Queries (Legacy)

  1. Meticulously edit all five (5) sql scripts copied over from last academic reporting year’s folder as mentioned under Prerequisite subsection in this documentation Overview page. Edits must reflect

    • All table reference in the SQL scripts must be in accordance to the current reporting academic year, for instance [CFR25].[dbo].[Dim_GIAS_ClosedSchools_2025] be edited to [CFR26].[dbo].[Dim_GIAS_ClosedSchools_2026] when generating 2025-2026 CFR downstream dataset.
    • All slowly changing dimension tables reference must be in accordance to the previous reporting academic year, for instance [dbo].[Dim_Workforce_2024] be edited to [dbo].[Dim_Workforce_2024] when generating 2025-2026 CFR downstream dataset.

  2. Confirm a total of fourteen (14) tables in CFR_yy local database.

    The below image corresponds to Fact and Dimension tables used for the CFR downstream dataset generation for 2024-2025 reporting academic year

    CFR_25 Database

  3. Run My_Step1.sql to output both [dbo].[SFB_Federations_Aggregated_20YYyy] and [dbo].[SFB_Federations_IndividualSchools_20YYyy] tables.

  4. Run My_Step2.sql to output [dbo].[SFB_Dim_20YYyy] table

  5. Run My_Step3.sql to output [dbo].[SFB_Maintained_20YY-yy] table

    The below image corresponds to all tables relating to the CFR downstream dataset generation for 2024-2025 reporting academic year

    CFR_25 Database

  6. Run My_Step4.sql and save the result of the query as maintained_schools_master_list.csv

  7. Run My_Step4.sql and save the result of the query as maintained_schools_transparency.csv

Save CFR Downstream Dataset

  1. Upload maintained_schools_master_list.csv to dev environment Azure blob storage default folder that corresponds to the end of the reporting academic year. For instance, 2025 when working on generating 2024-2025 CFR downstream dataset

  2. Backup local database and save remotely

  3. Upload maintained_schools_transparency.csv to Sharepoint Location