Job Specification

Position Title:
ETL Programmer
Job Code:
17160 - Systems Officer 5
Job ID:

Purpose of Position :

To act as a Programmer/Analyst for the Asset Management unit. To provide specialized systems expertise, advisory services, detailed data analysis and systems design, programming and technical leadership in the design, development, testing and implementation of a province-wide data warehouse management system, involving data extraction, transformation and load (ETL) using Informatica as a development tool. To provide system support for periodic load and updates. To be involved in maintenance and enhancements of an existing Data Warehouse solution.

Duties / Responsibilities :

Asset Management unit is responsible for the provision of integrated sources of information in an Enterprise Data Warehouse for the use of the data in Business Intelligence tools to support Client Ministries' evidence based decisions such as measuring performance outcomes and performing trend analysis.

In this context, the position is responsible for:

1. Acts as a programmer/analyst and technical expert with respect to data extraction, transformation and load (ETL) for various Data Warehousing projects. Leads the design and installation of a data warehouse management and support function for the ministry to promote: information collection (including the collection, storage and validation applications and the related business processes) for financial and non-financial information; the development of an accountability framework to report information on individual schools and boards for measuring performance and comparison across the province; the development of a data warehouse capability to support accountability, decision support and reporting capabilities required by the funding and policy development activities within the ministry.

2. Designs and implements system solutions. Identifies and evaluates feasibility of various options, and develops recommendations, including opportunities for maximizing technology. Ensures that information technologies solutions are implemented to meet the business objectives corporate information requirements of the ministry.

3. Leads the development of detailed system/module/application specifications and designs. Translates technical systems specifications into working and tested applications. Develops detailed programming specifications. Performs system acceptance testing, drafts system documentation (procedures, process maps, operation manuals, etc.).

4. Aanalyzes, designs, develops, tests and implements ETL using Informatica, SQL, Oracle and DB2 stored procedures on a UNIX/NT platform. Works with operations area to set up ETL procedures.

5. Builds and manages data sets by analyzing data contained in the corporate database, legacy systems and other data sources; Prepares specifications for source data extracts and protocols working with technical resources of the source systems while preparing transfer protocols.

6. Leads the ongoing development, design modification and functionality enhancement of the data warehouse. Develops and implements solutions to information access problems to ensure quality information for client Ministries. Participates in developing quality assurance standards and performance measures.

7. Provides specialized expertise to management and end users. Provides advice to program staff and acts as a resource in the resolution of technical problems. Determines user training requirements. Develops and leads training/information seminars.

8. Contributes to strategic and operational IT plans in order to implement solutions to meet objectives and to integrate technical requirements with end-user application and set up. Provides analysis and technical design planning documentation. Develops and implements new processes/procedures.

9. Promotes the perception of the ministry as a leader in information collection, management, accountability and performance monitoring. Promotes Asset Management unit's solution services and activities. Provides advisory services to management in identifying existing/potential issues.

10. Coordinates the development and maintenance of user access and systems security policies and procedures.

11. Maintains regular contact with the technical team to obtain and confirm technical requirements and reporting specifications. Maintains frequent contact with program/technical staff within the Community Services cluster to provide information and services related to I&IT systems operational issues. Maintains contact with counterparts in other ministries to lead/participate on OPS I&IT committees and task groups.

Staffing and Licencing :


Knowledge :

Job requires knowledge of an ETL tool such as Visual Warehouse, Vision Pursuit, Informatica, with proven skills to act as a Programmer/Analyst for the EBI platform. Job requires knowledge and proven ability with respect to the principles and techniques of large-scale information management to provide a full range of specialized systems services (e.g. technical specifications, programming, design and construction). Job requires knowledge of structured methodologies for the development, design, implementation and maintenance of applications; knowledge of programming concepts and languages, development/testing concepts, methodologies and techniques; knowledge of relational and dimensional database concepts; demonstrated ability in the use of information retrieval packages and query languages (SQL). Job requires proven ability with respect to analysis and design to implement systems initiatives in support of Ministry strategic direction. Job requires ability to prepare detailed systems design documentation to meet technical program systems requirements. Job requires knowledge of the goals of the Enterprise Business Intelligence Unit, and ministry operations, strategic direction and current systems including application architecture within the Branch to provide effective programming/analysis service delivery. Job requires knowledge of business analysis and technology planning to prepare documentation in support of systems analyses and policy recommendations, and the ability to appreciate the inter-related aspects of large-scale systems implementation and operations.

Skills :

Job requires analytical and problem-solving skills to provide program leadership to project team; identify and assess feasibility of options; develop recommendations on the most appropriate systems solutions; translate corporate information requirements into system specifications and detailed programming/testing of applications; implement workable systems/solutions to meet business objectives; develop strategies for systems adjustments; accommodate changing data/information needs; provide analysis; provide advisory services to senior management; participate in the development of quality assurance policies and procedures to maximize operational efficiency.

Job requires: consulting skills to advise management on the use of the ETL tool , and to advise users on operational and programming changes required to fully utilize systems enhancements; interpersonal skills to clarify and discuss information requirements, and to propose alternate solutions and illustrate advantages and disadvantages of various options from both the business information need and the solution delivery perspective; written communication skills to document the operational manual and procedure process/information/application requirements, business justification, develop technical documentation. Job requires organizational skills to coordinate systems development and project implementation activities. Job requires to provide technical direction and guidance to systems staff and consultants.

Freedom of Action :

Job functions within the framework of established policies, procedures, guidelines, goals, strategic direction and corporate expectations. Job requires working within established practices and procedures for systems design programming concepts and languages, specialized technologies and systems.
Job acts as the expert with respect to ETL tool, where scope of program accountability is ministry-wide with long-term impact. Job requires defining technical requirements, options and opportunities; developing detailed system/module/application/programming specifications and designs; implementing solutions to meet business objectives; leading performance monitoring and fine-tuning with DBA and DBT's. Job functions with a considerable degree of independence within a groundbreaking, high profile, largely self-directed project, with well-defined parameters. Management relies on this position's level of expertise to provide advice and systems expertise to support and maintain the EBI Platform

You must enable JavaScript in your browser to access full functionality on the OPS Careers website.

Vous devez activer JavaScript dans votre navigateur pour avoir accès à toutes les fonctionnalités du site Carrières dans la FPO.

Ontario Logo / Logo de l'Ontario
This site is maintained by the Government of Ontario
Ce site est mis à jour par le gouvernement de l'Ontario

Privacy | Important Notices
Confidentialité | Avis importants

© Queen's Printer for Ontario, 2007
© Imprimeur de la Reine pour l'Ontario, 2007