Posting will close to submissions on Wednesday, 8/17 at 10amEST
Background:
Ohio Department of Health is looking for a TS1 for an assignment, lasting approximately 2 years. This project’s estimated duration is 2 years, the Agency can commit to 1680 hours this fiscal year, with a start date of late August to early September.
Candidates must be available to work onsite M-F weekly. Local candidates preferred, but onsite interviews are REQUIRED. Do not submit candidates unavailable to interview onsite.
Please also set the expectation with candidates that a PO will take 2-3+ weeks to generate at DOH. A start date will likely be 3 weeks out from offer.
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.
REQUIRED: 5-10 years using Data Stage (preferable experience converting Data Manager to Data Stage)
Preferred: Experience mentoring other developers/strong communication skills needed
Preferred: Knowledge of the Agile/Scrum process
Our source database tool set is SQL Server (ideal candidate would have SQL exp.)
Preferred: Experienced ETL developer
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.
Description of Duties
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)
Project Current State
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.
Project Future State
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.
Data Manager to DataStage Migration
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.
Mentoring & Training
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 and 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.
Training Topics
The ideal candidate should be able to train the current team on the DataStage topics listed below.
>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
Data Profiling
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 report developer, 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 other developers.
ETL Development
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.
DataStage Administration
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 other developers on how to resolve issues. Monitor ETL sequence and make changes as needed. Mid-day data refresh as needed in Development and Production environments. Coordinate Cognos read access to Data Warehouse data sources.
