While keeping student and educator data secure

About Wichita Public Schools

Wichita Public Schools provides K-12 public education in Wichita, Kansas.

Customer Need / Business Driver

• Move analytics from Cognos to a more modern architecture
• Demonstrate that cloud-based BI could meet the district’s strict security requirements
• Connect from its current on-premise sources to Power BI on Azure

Data Sources

• PeopleSoft on Oracle DB
• IPSP, a student and grading system running on SQL Server
• Synergy running on SQL Server
• 2 CSV and 2 Excel files

Azure Synapse Analytics

What KPI and Microsoft Delivered

• This project was executed in partnership with the Microsoft team
• Using Azure Data Factory, data pulled into Parquet files on an Azure data lake
  • 2 on-premise SQL Services sources, pulling 12 tables from one source and 100 from another
  • 20 PeopleSoft tables
  • 2 CSV files
  • 2 Excel files
• Connected Azure Data Lake to serverless Synapse with ADF
• A parameterized data pipeline
• Reports in Power BI that verified data was being pulled properly
• Power BI reports mimicking Cognos paginated reports
• Security linking Wichita Public Schools’ Active Directory to Power BI users set up in Azure
• Best practices for using serverless Synapse, including partitioning, schema inference, and appropriate data types
• Knowledge transfer

Benefits to the school system

• Modelled blended HR data, IPSP data, and data from the Synergy Education Platform with Excel and CSV files to discover new insights into teacher and school performance
• Improved productivity from Power BI compared to Cognos
• Integrated automated security model
• Best practices on data engineering, Synapse architecture and security incorporated without a lot of trial and error