DataStage/ETL Developer

This job is no longer active. View similar jobs.

POST DATE 8/18/2016
END DATE 12/19/2016

Confidential Company Columbus, OH

Columbus, OH
AJE Ref #
Job Classification
Full Time
Job Type
Company Ref #



JOB TITLE: DataStage/ETL Developer

LOCATION: Columbus, OH 43215

DURATION: 12+ months (Strong possibility for an extension)


* The selected individual will be working with our current Cognos developers converting to Data Stage and assist in our efforts in moving to Cognos Analytics (v.11).
* Cognos BI/DW will bring together the Agency's data resources to create on-demand reporting across the enterprise. This will allow management to look at information for various Program areas to aid in better business intelligence, KPIs and decision making. This project is passed the pilot proof of concept sell to executive management and is now in the implementation stage.
* Candidate must be onsite for the duration of the assignment, working a full workweek onsite at Ohio Department of Health.
* Program areas within the Agency are prioritized and queued up for dash-boarding project work to last approximately a couple of years.


* 5-10years using Data Stage (preferable experience converting Data Manager to Data Stage)


* Experience mentoring otherdevelopers/strong communication skills needed
* Knowledge of the Agile/Scrum process
* Our source database tool set is SQL Server (ideal candidate would have SQL exp.)
* Experienced ETL developer


* 25% of Total Work Effort: IBM DataStage Migration
* 25% of Total Work Effort: Mentoring and Training
* 25% of Total Work Effort: ETL Development
* 14% of Total Work Effort: Data Profiling
* 10% of Total Work Effort: DataStage Administration
* 1% of Total Work Effort: Agile/Scrum process (TFS)


* Our current BI and Data Warehouse team is using Cognos 10.2 for reporting and Cognos Data Manager for ETL. We currently provide reporting and data services for a few business areas at Ohio Department of Health. Our current Data Warehouse database is on SQL Server platform.
* We are a growing team and are looking for a DataStage and ETL developer to help us with our projects.


* In the near future, we will be moving to Cognos Analytics (v11) for reporting and IBM DataStage for ETL. We will begin to service all business areas within the Ohio Department of Health.
* One of the main goals is to provide business areas with the tools they need to monitor performance and create their own reports as needed.
* Project deliverables will include multi-tab dashboards in Cognos along with self-serve data models in Cognos.
* The data used to feed the dashboard reports needs to be optimized so that it can be run quickly and also be reused for future reporting and end-user self-service needs. For this reason, strong SQL skills are required.


* The current ETL tool being used is Cognos Data Manager. This tool has been in place for ten years here at Ohio Department of Health.
* This tool is being phased out by IBM and support for this product is ending soon. For this reason, we have been looking for a different ETL tool.
* After researching many options we have selected IBM's DataStage as our new ETL tool. One of the benefits of using IBM's DataStage is that we will be able to utilize their conversion tool, which automatically converts existing Cognos Data Manager jobs to DataStage.
* This tool should do most of the work, though some manual changes will most likely be required.
* Cognos Data Manager Experience is preferred, though DataStage experience is required.
* The ideal candidate will have experience migrating from one ETL tool to IBM DataStage. Install and configure the new DataStage environment, utilizing best practices.
* Enhance migrated jobs in DataStage in order to take advantage of parallel processing and change data capture.


* The current developers on the team do not have experience with IBM DataStage.
* One of the responsibilities of this new role is mentoring and training the current team on DataStage.
* Examples of mentoring topics may include overview of an introduction to DataStage, deployment, DataStage administration, working with metadata, creating parallel jobs, accessing sequential data, partitioning and collecting, combining data, group processing stages, transformer stage, repository functions, working with relational data, job control, and intersecting with other information server products.
* Resource will also be expected to share learning lessons along with tips and tricks.
* Resource will shadow developers and guide them as needed.



* Describe the uses of DataStage and the DataStage workflow
* Describe the Information Server architecture and how DataStage fits within it
* Describe the Information Server and DataStage deployment options
* Use the Information Server Web Console and the DataStage Administrator client to create DataStage users and to configure the DataStage environment
* Import and export DataStage objects to a file
* Import table definitions for sequential files and relational tables
* Design, compile, run, and monitor DataStage parallel jobs
* Design jobs that read and write to sequential files
* Describe the DataStage parallel processing architecture
* Design jobs that combine data using joins and lookups
* Design jobs that sort and aggregate data
* Implement complex business logic using the DataStage Transformer stage
* Debug DataStage jobs using the DataStage PX Debugger
* Read and write to database tables using DataStage ODBC and DB2 Connector stages
* Work with the Repository functions such as search and impact analysis
* Build job sequences that controls batches of jobs
* Understand how FastTrack and Metadata Workbench can be profitably used with DataStage


* We currently are pulling data from a few source systems. We are currently gathering requirements for dozens of dashboards, each from at least one (and maybe more) source systems.
* Each dashboard project will require data profiling, ETL, and report development work. On some projects, the Cognos resource who will be developing the dashboard reports will also design the new reporting tables (ideally in dimension and fact tables).
* On other projects, the ETL developer will take on the data profiling responsibilities in addition to the actually ETL work itself.
* The candidate must have experience creating reporting tables. This involves working with the reportdeveloper, business analyst, end-user, and maybe the technical subject matter expert.
* The reporting table will need to contain all of the business logic required for the reports, so that the calculations are performed during ETL, and not in Framework Manager or Report Studio.
* There will be reporting tables created for each dashboard project. The ETL developer needs to have strong SQL skills in order to optimize the table builds, in addition to reviewing and optimizing the SQL code of otherdevelopers.


* Create ETL packages and jobs to pull data from source system and also create reporting tables.
* Create new ETL jobs and tune existing ETL jobs.
* Review code from data modeler and make recommendations and changes in order to improve performance. Create indexes for tables which are being pulled in as needed.


* Administer DataStage system by setting-up new data sources, monitor performance of ETL jobs and resolve all issues, communicate any issues to team and business users, implement practices to mitigate issues, and work with DBAs as needed to investigate any data source connection issues.
* Schedule ETL jobs to run at optimal times as to not interfere with live operational data, yet pull the most current data. Set-up notification emails to monitor success or failure of ETL jobs.
* Communicate status of ETL jobs to team and mentor otherdevelopers on how to resolve issues. Monitor ETL sequence and make changes as needed.