Client: Supply Ontario – RQ00812
We are seeking a
Senior Business Intelligence Specialist – Oracle with strong expertise in Oracle, Python, AWS Glue Python Shell, AWS Database Migration Service (AWS DMS), Amazon Redshift, and Power BI. The successful candidate will support the Ontario Clinical Report Exchange (OCRE) project by developing reliable data integration pipelines, designing dimensional reporting models, and delivering operational and business intelligence dashboards. The role requires hands-on experience with complex SQL, ETL development, incremental data loading, data validation, reconciliation, performance optimization, healthcare data standards, and production support. The ideal candidate will also have experience interpreting the Ontario HRM Standard, FHIR data structures, and HL7 v2 data structures to ensure accurate, consistent, and meaningful healthcare reporting and analytics.
Role Snapshot
- Job Title: RQ00812 – Business Intelligence Specialist – Oracle – Senior
- Client: Supply Ontario
- Work Location: 525 University Avenue, Toronto, Ontario, Canada
- Work Arrangement: Hybrid, with onsite attendance as required by the Hiring Manager
- Estimated Start Date: November 23, 2026
- Estimated End Date: May 20, 2027
- Business Days: 124.00
- Extension: Option to extend for up to an additional 64 business days, at the Agency's discretion
- Hours: 7.25 hours per day, 5 days per week
- Public Sector Experience: Must have
Must Have
- 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.
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.
Required Experience
- Senior-level Oracle experience, including complex SQL, PL/SQL, data extraction, performance tuning, data profiling, and analysis of production data structures.
- Hands-on experience developing AWS Glue Python Shell jobs and Python-based ETL processes that extract, transform, validate, and load data into Amazon Redshift.
- Strong experience with Amazon Redshift, AWS DMS, dimensional modelling, star schemas, incremental data loads, reconciliation, and data-quality controls.
- Strong Power BI experience, including semantic models, DAX measures, Power Query, report design, dashboard development, refresh processes, and performance optimization.
- Practical experience interpreting the Ontario HRM Standard, FHIR data structures, and HL7 v2 data structures for healthcare reporting and analytics.
Responsibilities
- Work with OCRE business and technical stakeholders to define reporting requirements, business measures, data-quality indicators, acceptance criteria, and dashboard priorities.
- Analyse Oracle schemas and OCRE application data sources to identify authoritative fields, relationships, business rules, data limitations, and extraction requirements.
- Develop, test, and support AWS Glue Python Shell jobs that extract data from Oracle and OCRE application sources and load it into Amazon Redshift.
- Configure or support AWS DMS replication and change-data-capture processes where they are used to move Oracle or application data into the reporting platform.
- Implement full and incremental load patterns, transformations, validation rules, error handling, logging, reconciliation, restart ability, and operational monitoring for data pipelines.
- Design and maintain the Amazon Redshift reporting model, including staging structures, fact and dimension structures, keys, history handling, and star-schema relationships.
- Create and optimize Redshift SQL views, materialized views, stored procedures, and queries that expose governed reporting datasets to Power BI.
- Develop Power BI semantic models, DAX measures, Power Query transformations, reports, and dashboards for OCRE metrics, operational performance, and data quality.
- Translate Ontario HRM, FHIR, and HL7 v2 data elements into consistent analytical definitions while preserving clinical meaning, traceability, and source-system context.
- Profile source and target data, identify anomalies or gaps, implement data-quality rules, and reconcile dashboard results to Oracle and OCRE source data.
- Tune Oracle queries, AWS Glue jobs, Redshift workloads, and Power BI models to meet agreed performance, refresh, scalability, and support requirements.
- Prepare technical designs, source-to-target mappings, dimensional models, metric definitions, test evidence, deployment instructions, runbooks, and knowledge-transfer materials.
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.
Rated Criteria
- Oracle, Python, AWS Glue Python Shell, and AWS DMS Experience - 25 Points
Experience with Oracle, Python, AWS Glue Python Shell, and AWS DMS, including source analysis, complex SQL, ETL development, incremental loading, validation, reconciliation, and production support.
- Amazon Redshift and Dimensional Modelling - 25 Points
Experience with Amazon Redshift and dimensional modelling, including staging structures, fact and dimension models, star schemas, views or materialized views, SQL optimization, and workload performance.
- Power BI Experience - 25 Points
Experience with Power BI, including semantic models, DAX, Power Query, dashboards, operational metrics, data-quality reporting, refresh, security, and performance optimization.
- Healthcare Data Standards and Structures - 25 Points
Experience with the Ontario HRM Standard, FHIR, and HL7 v2 data structures, including healthcare-data mapping, metric definition, traceability, and interpretation of clinical and operational data.
Deliverables
The successful candidate will be responsible for delivering the following:
- 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.
Additional Terms and Working Conditions
Engagement Duration and Extension
The term of this Engagement Assignment is for
124 Business Days, with an option to extend for up to an additional
64 days at the Agency's discretion. The Engagement Assignment may be extended for unused Business Days at the Agency's discretion.
Agency Policies and Procedures
The resource will comply with the Agency's policies and procedures.
Equipment and Home Office Requirements
The resource will be issued an Agency laptop and is expected to provide at least one additional monitor, an external keyboard, and a mouse at their own expense.
In addition, when the resource is working from their home location, they must have a private office space and are not permitted to work in an open communal space. The internet connection must be hard-wired and provide suitable bandwidth to support the IT requirements of this role.
If these conditions cannot be met within the resource's home office space, the resource will transition to full-time work in the office, and the hybrid option will no longer be available.
Geographic Restrictions
The Agency systems cannot be accessed from outside the province of Ontario, and Agency assets, including laptops and related equipment, cannot be removed from the province of Ontario without prior written approval from the Agency.
Assignment Type and Onsite Requirements
This position is currently listed as
Hybrid. The resource under this request will be required to work onsite at the Hiring Manager's sole discretion.
Location: Onsite as needed.
How to Apply
If you are interested and your profile matches the requirements, kindly send the following mandatory documents to
hrsmss@smsoftconsulting.com by
Monday, October 26, 2026, at 12:00 PM EST.
Mandatory Documents
- Updated Resume in Word format (Mandatory)
- Skills Matrix and References (Mandatory)
- Expected Hourly Rate (Mandatory)
- Visa Status (Mandatory)
- LinkedIn ID (Mandatory)
Without the mandatory documents, we cannot submit a candidate.
Apply Online: Please submit your application through the SMSS career website.
WhatsApp Group
For daily job updates, you can also join our WhatsApp group using the link below:
Join our WhatsApp Group
Email is the best way to reach us.
