Requisition Title: Data Warehouse / ETL Developer – Level 3 – ORBIT Reporting Modernization project
Location: Toronto, ON
Duration: 6 months
Our public sector client is looking for a Data Warehouse/Extraction Transformation Load (ETL) Developer who analyzes, designs, and develops extraction, transformation, and load (ETL) processes that automate the movement of data between systems and data stores.
The Extraction Transformation Load Developer implements bulk ETL and real-time data integration solutions using enterprise data management tools, with emphasis on implementing best practices in the design, deployment and management of scalable, reusable and extensible integration components.
- ORBIT corporate reporting is used extensively for management reporting, fiscal plan rollups, and internal briefings, and is based on legacy versions of Oracle Essbase / Dodeca running on Windows 2003 in Kingston Data Centre (KDC).
- Windows 2003 is the Operating System which hosts the existing corporate reporting datastore. Support for Microsoft Windows 2003 ended globally in July 2018; no service packs or patches are available. This has introduced considerable risk – if security issues cannot be remediated locally, the service will be subject to removal from the OPS network as per CSB policy.
- Providing a stable, modern, expandable reporting data warehouse capable of meeting current and future needs is a foundational component to ORBIT reporting modernization.
- A modern reporting data warehouse can be used in conjunction with any front-end reporting tool and will be used to inform an assessment in the selection of a reporting platform.
- This phase of work is a critical step to modernizing and remediating the technology risks associated with the existing ORBIT reporting application.
- Works collaboratively within a development team to design, develop and optimize a Data Warehouse/ETL solution
- Performs Logical and physical data modeling for enterprise data warehouse
- Participates in the design of, and develops well-annotated, maintainable ETL processes according to specification and standards
- Performance tunes and troubleshoots ETL processes under development
- Participates in the development an enterprise data warehouse
- Contributes to the creation and maintenance of technical and business Meta data flows
- Understands and applies best practices for ETL development into standard warehouse models and dimensional data structures
- Creates documentation for ETL processes and reviews with other development resources for consistency
- Provides guidance and assists in resolving development issues
ETL Developer Skills/Experience
- Experience with Microsoft SQL Server (SSIS, complex T-SQL, stored procedures, views, triggers, indexes, scripting, database tuning, etc.) – 10 yrs
- Data warehouse / ETL design, development, best practices and optimization
- Logical and physical data modeling for enterprise data warehouse
- Writing complex ETL including slowly changing dimensions, aggregate tables, versioning, archiving
- ETL orchestration for performing updates to data warehouse (automation, logging, and notifications)
- Database performance tuning (monitoring and tuning queries / data loads)
- Working with business users to confirm / validate results
- Data warehouse capacity planning
- Strong analytical and problem-solving abilities
- Building data warehouse for OBIEE, Microsoft Power BI, Cognos reporting environment a plus
Nice to have skills:
- MSSQL version 2017
- Exposure to Data Warehousing design and concepts including (This is not this person’s primary role):
- Dimensional data modeling including star/snowflake schema;
- Levels of Data Model design (conceptual, logical and physical data model )
- Slowly Changing Dimension problem and approach;
- Other best practices for ETL / data warehouse design and development