RQ00812 - Business Intelligence Specialist - Oracle - Senior

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.

• Analyze 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, restartability, 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.

Required Skills:

• 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.

Rated Criteria:

• 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

Total: 100 Points

Requirements

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.

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.