Job ID: WR-2019-0731-BP (90090809)1P
Data Warehouse/ETL Developer with SSIS, T-SQL, Kimball principles, Informatica Power Center, database modeling, PowerExchange for ADABAS, Cognos and TFS experience
Location: Olympia WA (DOT)
Duration: 22 months
Interview: In-person
Mandatory Knowledge, Skills and Abilities
Minimum 5 years of experience in leading requirements gathering sessions and developing requirements and technical documents based on those requirements
Minimum 5 years of experience in creating and maintaining Extract, Transform, and Load (ETL) processes using SQL Server Integration Services (SSIS)
Minimum 5 years of experience writing complex queries using Transact SQL for reporting and data validation purposes.
Minimum 5 years of experience using Microsoft SQL Server 2012, 2016 and 2017 software tools to administer and/or manage SQL Server databases.
Minimum 5 years of experience employing Kimball data warehouse principles and theories, including knowledge of the various tactical approaches to data warehouse development.
Minimum 5 years of experience report development
Desirable Knowledge, Skills and Abilities
Experience using Informatica Power Center
Understanding of database modeling and design principles and ability to use a data model diagram.
Experience using PowerExchange for Mainframe ADABAS.
Experience using Cognos Business Intelligence for reporting
Experience using Team Foundation Server
Scope of Work
The scope of work for the Materials Lab Data Mart requires one (1) senior level Data Warehouse Analyst to work with the Information Technology Division (ITD) Data Warehouse Business Intelligence Coordinator and Data Modeler to lead requirements gathering sessions with the State Materials Lab and stakeholders, and develop necessary requirements and technical documentation to support development of the Materials Lab Data Mart.
The Contractor shall then develop and perform the technical tasks required implement, maintain, enhance performance, and troubleshoot extract, transform and load (ETL) processes that move data from the Materials Lab transactional databases and flat files to the Data Warehouse.
Finally, the Contractor shall develop key reports identified in the requirements gathering sessions and provide support to report analysts and customers to ensure accurate and timely performance of reports utilizing information from the data warehouse.
The Data Warehouse operating environment is SQL Server 2016 on Windows 10 and our ETL tool is
Microsoft SQL Server Integration Services (SSIS).
Specific duties include:
Using client requirements and database design specifications, the Contractor shall:
• Lead requirements gathering meetings with State Materials Lab staff and stakeholders.
• Develop requirements documentation.
• Determine how identified data sources can provide the required data
• Develop technical spec documentation
• Test data structures to verify that the target databases work as designed.
•Set up ETL routines; write the code using SSIS packages that move data from the specified sources; transform it as required and write it into the data mart. The outcome of this task shall be error-free ETL routines that meet requirements and design specifications, and are regularly scheduled within WSDOT’s Data Warehouse loading process.
•Develop critical reports and provide technical assistance to staff in writing complex datamart queries, based on the detailed knowledge gained during the construction of ETL routines. The outcome of this task shall be the development of key reports needed by the State Materials Lab and the provision of technical information and ideas to support staff and query tool experts who are assisting users with advanced, complex datamart queries.
