Business Intelligence Specialist - Oracle - Senior

Job Title: Business Intelligence Specialist - Oracle - Senior

Location: Toronto, Ontario

Work Model: Hybrid

Duration: 6 months

Extension: 1 time

Project Description

The Agency requires a Senior Business Intelligence Specialist with strong Oracle experience to support the Ontario Clinical Report Exchange (OCRE) project. The resource will develop the data integration and reporting capabilities needed to extract information from Oracle and OCRE application sources, load it into Amazon Redshift, and make reliable operational and business information available to OCRE stakeholders. The role will build AWS Glue Python Shell jobs and supporting SQL to extract, transform, validate, and load data into Amazon Redshift, while using AWS Database Migration Service (AWS DMS) where replication or change-data-capture capabilities are required. In Redshift, the resource will design a star-schema reporting layer with fact and dimension structures, views, or materialized views appropriate to the reporting workload. The resource will then develop Power BI semantic models, reports, and dashboards for operational metrics, data-quality monitoring, and other reporting needs defined by OCRE business stakeholders. The work requires an understanding of the Ontario HRM Standard, FHIR data structures, and HL7 v2 data structures so that clinical and operational data is interpreted and modelled correctly.

Must Haves

• Oracle, Python, AWS Glue Python Shell, and AWS DMS experience, including complex SQL, source-data analysis, extraction, transformation, incremental loading, validation, reconciliation, and production support.
• Amazon Redshift and dimensional-modelling experience, including staging layers, fact and dimension structures, star schemas, views or materialized views, SQL optimization, and reporting-layer performance tuning.
• Power BI development experience, including semantic modelling, DAX, Power Query, dashboards, operational metrics, data-quality reporting, refresh processes, security, and performance optimization.
• Healthcare-data experience involving the Ontario HRM Standard, FHIR data structures, and HL7 v2 data structures, including mapping source data to consistent analytical definitions and metrics.
• Public Sector Experience: must have

Description / Responsibilities / Skills

Responsibilities

• Work with OCRE business and technical stakeholders to define reporting requirements, business measures, data-quality indicators, acceptance criteria, and dashboard priorities.
• Analyze Oracle schemas and OCRE application data sources to identify authoritative fields, relationships, business rules, data limitations, and extraction requirements.
• Implement full and incremental load patterns, transformations, validation rules, error handling, logging, reconciliation, restartability, and operational monitoring for data pipelines.
• Prepare technical designs, source-to-target mappings, dimensional models, metric definitions, test evidence, deployment instructions, runbooks, and knowledge-transfer materials.
• Tune Oracle queries, AWS Glue jobs, Redshift workloads, and Power BI models to meet agreed performance, refresh, scalability, and support requirements.

Knowledge Transfer

• The resource will provide ongoing knowledge transfer to the Ontario Health OCRE team throughout the engagement.
• The resource will participate in code reviews, design walkthroughs, interface and data-mapping reviews, demonstrations, release reviews, operational-readiness reviews, and technical handover sessions.
• All key source code, configuration, tests, technical documentation, runbooks, and lessons learned will be shared with Ontario Health leads before the end of the engagement.
• The resource will support transition planning so Ontario Health staff and successor resources can operate, maintain, troubleshoot, and enhance the delivered OCRE components.

Deliverables

• Production-ready AWS Glue Python Shell jobs and supporting Python or SQL components that extract data from Oracle and OCRE application sources and load it into Amazon Redshift.
• AWS DMS configurations, mappings, validation results, and operational documentation for replication or change-data-capture workflows assigned to the resource.
• Amazon Redshift staging, ingestion, transformation, and reconciliation components supporting full and incremental data loads.
• A governed Redshift star-schema reporting layer containing the required fact and dimension structures, relationships, history handling, and business definitions.
• Optimized Redshift views, materialized views, stored procedures, and queries that provide Power BI with reliable and supportable reporting datasets.
• Power BI semantic models, DAX measures, Power Query transformations, reports, and dashboards for OCRE metrics, operational monitoring, data quality, and stakeholder-defined reporting needs.
• Documented metric definitions, source-to-target mappings, data-quality rules, validation controls, lineage, and reconciliation results.
• Automated and manual test assets covering extraction, transformation, incremental loads, data completeness, data accuracy, report calculations, refresh, regression, and release validation.
• Operational logging, monitoring, alerting, restart, recovery, incident-investigation, and support procedures for the data pipelines and reporting solution.
• Technical designs, dimensional models, configuration notes, deployment instructions, runbooks, demonstrations, defect fixes, release support, and knowledge-transfer materials.

Experience & Skill Set Requirements

Rated Criteria 25 points each (Total: 100 Points)

• Oracle, Python, AWS Glue Python Shell, and AWS DMS experience, including source analysis, complex SQL, ETL development, incremental loading, validation, reconciliation, and production support. 25 points
• Amazon Redshift and dimensional-modelling experience, including staging structures, fact and dimension models, star schemas, views or materialized views, SQL optimization, and workload performance. 25 points
• Power BI experience, including semantic models, DAX, Power Query, dashboards, operational metrics, data-quality reporting, refresh, security, and performance optimization. 25 points
• Ontario HRM Standard, FHIR, and HL7 v2 data-structure experience, including healthcare-data mapping, metric definition, traceability, and interpretation of clinical and operational data. 25 points

Desired Skills

• Experience designing dimensional models using fact tables, dimension tables, conformed dimensions, surrogate keys, slowly changing dimensions, and auditable metric definitions.
• Experience with Redshift performance features such as distribution styles, sort keys, workload management, query plans, materialized views, and columnar data design.
• Experience with Power BI row-level security, deployment pipelines, scheduled refresh, gateways, workspace governance, usage monitoring, and accessibility practices.
• Experience supporting healthcare analytics solutions that require data lineage, privacy-aware access, auditability, reconciliation, production monitoring, and incident investigation.