Introduction
The New York City School Construction Authority (SCA) is a vital entity responsible for the planning, design, and construction of public-school facilities in New York City. As one of the largest educational infrastructure agencies in the United States, the SCA plays a crucial role in ensuring that students across the city have access to safe, modern, and technologically advanced learning environments.
Furthermore, the SCA maintains a strong commitment to fiscal responsibility and efficient project management. With a vast portfolio of successful school construction projects, the SCA leverages its expertise to deliver projects on time and within budget, while adhering to the highest standards of quality and safety.
Project Overview & Challenges
The SCA embarked on a journey to modernize its outdated reporting infrastructure, which previously relied on Oracle Discoverer — a product that reached end of life in June 2017. Without vendor support, report maintenance, enhancements, and accessibility became a growing concern. The project’s goal was to transition nearly 450 existing worksheets to a scalable, self-service data platform that supports real-time and scheduled reporting.
Key challenges faced:
- End-of-life for Oracle Discoverer
- Lack of real-time reporting
- Inability to perform self-service analytics
- Limited integration with modern BI tools
Azure-Based Solution Architecture
The proposed architecture uses the Azure data stack to achieve scalable ingestion, transformation, and visualization. Azure Synapse Analytics is used for orchestration and data integration, while Power BI is leveraged for building interactive and paginated reports.
Architecture Overview
The architecture is designed with modular and scalable cloud-native components that enable smooth integration, processing, and consumption of financial data.
Key Architecture Layers:
- Data Sources:
- Oracle Server
- SQL Server systems such as 1, 2, 3, and 4
- Ingest:
- Azure Synapse Pipelines are used to extract data from Oracle and SQL Server sources into the Azure platform.
- Store:
- Azure Data Lake Gen2 is used for storing both raw and processed data.
- Dedicated SQL Pools are used for structured reporting needs.
- Transform:
- Azure Synapse Spark Pools handle transformations, data cleaning, and modeling.
- Consume:
- Power BI Report and Power BI Paginated Reports is used for data visualization and reporting.
- Self-service analytics is enabled for business users.
- Security & Governance:
- Azure Purview for data cataloging and lineage
- Azure Key Vault for credential management
- Azure Active Directory for authentication
- Azure DevOps for CI/CD pipelines
Implementation Phases
The migration was broken into four well-structured phases to ensure operational continuity and technical completeness:
- Discovery & Assessment
- Inventory of existing Oracle Discoverer reports
- Classification of reports based on business criticality
- Identification of source systems (Oracle, SQL servers)
- Data Foundation Setup
- Provisioning Azure Synapse workspace
- Setting up Data Lake Gen2 for raw and curated zones
- Establishing connectivity to Oracle and SQL Server sources via Integration Runtime
- Migration & Development
- Rebuilding data ingestion using Synapse Pipelines (ELT approach)
- Transforming datasets using Synapse Spark Pools and storing results in Dedicated SQL Pools
- Recreating reports using Power BI and Power BI Paginated Reports
- Validation & Go-Live
- Parallel run with Oracle Discoverer, Power BI reports and Power BI Paginated Reports.
- User acceptance testing with business stakeholders
- Cutover and operationalization of reporting processes
Step-by-Step Technical Implementation
Step 1: Ingest Data Using Synapse Pipelines
- Connected to Oracle and SQL sources using Linked Services
- Scheduled data refreshes via triggers
- ELT pipeline stages: Copy → Load to Raw → Metadata Capture
Step 2: Store in Azure Data Lake
- Partitioned and stored files in hierarchical folder structure
/data/raw/oracle_server/yyyy/mm/dd/
/data/processed/oracle/yyyy/mm/dd/
Step 3: Transform with Synapse Spark Pools
- Used PySpark to clean, filter, and join datasets
- Created curated tables and delta views for reporting
- Registered processed data as external tables in Synapse SQL Pools
Step 4: Serve Data via SQL Pools
- Defined SQL views to expose curated data to Power BI
- Secured views with role-based access controls using Active Directory groups
Step 5: Built using power bi and power bi report builder
- Rebuilt Discoverer reports in power BI and Power BI Report Builder
- Built the custom visuals that helps in filtering multiple values
- Parameters and filters added for user interactivity
- Deployed reports to Power BI Premium workspace for distribution
Future Scope
The current implementation lays a solid foundation, but there’s more potential to be unlocked through:
- Real-Time Streaming
- Incorporate Azure Stream Analytics for real-time monitoring of transactions
- Predictive Analytics
- Leverage Azure Machine Learning with Synapse ML to predict budget overruns or construction delays
- Enhanced Data Governance
- Expand Azure Purview coverage for data classification, lineage tracking, and access monitoring
- Broader Self-Service Enablement
- Onboard additional departments with curated datasets and reusable Power BI templates
Summary & Final Thoughts
The SCA’s transition from Oracle Discoverer to Azure Synapse represents more than just a technology upgrade—it’s a transformation in how data is managed, visualized, and acted upon. With the adoption of Azure Synapse, Data Lake, and Power BI:
- Data accessibility is streamlined
- Performance is significantly improved
- Security and compliance are strengthened
- Self-service reporting becomes a reality for business users
This implementation not only addresses the immediate limitations of Oracle Discoverer but also sets the stage for a future-proof, analytics-driven organization. The NYC SCA is now better equipped to make informed decisions, manage construction budgets efficiently, and continue building the future of New York’s public education infrastructure.
