Stage Manager Sql Developer

SUMMARY:

** *****’ experience in IT industry with 15+ years of experience in developing, testing and maintaining applications in Data Warehouse using ETL tool IBM InfoSphere DataStage, IBM Web Sphere DataStage and Ascential DataStage

Experience in Strategic Analysis, Design, Development, Maintenance, Testing and Implementation of Client/Server, Relational and Dimensional Modeling, Data Marts/ Data Warehouses and Business Intelligence Technologies using Relational Database Management Systems like Oracle, Teradata, DB2, SQL Server and Sybase.

Area of expertise encompasses Database designing, ETL phases of Data Warehousing. This is achieved with an emphasis on relational data modeling and dimensional data modeling for OLTP and OLAP systems

Strong working experience on Data Warehousing applications, directly responsible for the Extraction, Transformation and Loading (ETL) of data from multiple sources into Data Warehouse

Experience in design and implementation of Star, Snowflake schemas and multi-dimensional modeling

Experience in integration of various data sources like SQL Server, Oracle, Sybase, MS Access and Flat Files into staging area

Experience in writing, testing and implementation of the Packages, triggers, Procedures, functions at Database level and form level using PL/SQL

Strong knowledge of Extraction Transformation and Loading (ETL) processes using UNIX shell scripting, SQL Loader

Responsible for interacting with business partners to identify information needs and business requirements for reports.

Designed and developed complex mappings.

Extensive knowledge in handling slowly changing dimensions.

Strong in Data warehousing concepts and using Star Schema and Snowflakes methodologies

Execution of test plans for loading the data successfully into the targets.

Implemented Various Performance Tuning techniques on Sources, Targets, Mappings and sessions.

Experience in Database Management, Data Mining, Software Development Fundamentals, Strategic Planning, Operating Systems, Requirements Analysis, Data Warehousing, Data Modeling, Data Marts.

Experience in writing UNIX shell/Perl scripts.

A versatile team player with strong programming and problem solving skills

Excellent communication and interpersonal skills and strong ability to perform as part of a team and individually

Education:

Masters in Computer Science

Illinois Institute of Technology, Chicago, IL

TECHNICAL SKILLS:

IBM InfoSphere DataStage 11.7/11.5/11.3/9.1/8.7, IBM WebSphere Information Server 8.0, IBM WebSphere EE 7.5.1,IBM WebSphere DataStage 7.5.1/7.0/6.0 (Manager, Administrator, Designer, Director, Parallel Extender), Integrity, ETL, Data Warehousing, Metadata, Data mart, OLAP, OLTP, SQL*Plus

Reporting Tools Business Objects 6.5.1/5.1,Oracle Reports 6.0,Cognos 7.0,Crystal Report 7.0, MS Access Reports

Databases Oracle 10g/9i/8i/8/7.x, DB2, SQL Server 2000,Teradata, Netezza, MySQL 5.0, MS Access 7.0

Database Modeling Erwin4.5/4.0/3.5/3.0, Star Schema, Snowflake schema, Fact and Dimensions Tables, Physical and Logical Data Modeling, Dimensional Data Modeling

Programming

Unix Shell Scripting, C, C++, SQL, PL/SQL, HTML, Visual Basic

Database Tools SQL *Loader, SQL*Plus, TOAD, Data Studio 4.1.0.1 Client

Environment Windows NT/95/98/2000/XP, UNIX, MS DOS, RED HAT LINUX,AIX UNIX, SUNOS 5.10

WORK EXPERIENCE

LabCorp, Raleigh NC Apr 2021 – Jan 2022

Sr. InfoSphere DataStage Developer

Participate in meetings with Business users and Subject Matter Experts (SMEs) to Understand Business requirements in DRUG DEVELOPMENT and DIAGNOSTICS.

Involved in Data Modeling for Customer, Order, KIT, Product Entities.

Gap analysis with logical and physical data models for data warehouse and data marts.

Involved in documentation of Conceptual design and Technical design documents.

Involved in business analysis and technical design sessions with business and technical staff to develop Entity Relationship/data models, requirements document, and ETL specifications

Translated business requirements into Data warehouse design and developed ETL logic based on requirements using DataStage.

Optimize DataStage jobs, SQL and Pl/SQL scripts to reduce run-time.

Designed and developed jobs to extract data from Amazon S3 in Jason format real time.

Developed jobs to parse Jason files using DataStage Hierarchical Data Stage to further transforming, loading into DataMart’s.

Worked on sourcing data from complex flat files using DataStage Complex Flat File and transforming, loading into DataMart’s.

Designed and Developed Audit jobs to capture the statistics of data loads for auditing using DataStage routines.

Created Generic jobs for historical loads from various databases and files using DataStage Runtime column propagation.

Involved in preparation of Autosys JIL scripts to schedule the jobs.

Environment: IBM InfoSphere 11.7 (DataStage Designer, DataStage Administrator, DataStage Director), Oracle 12c, Oracle SQL Developer, Amazon S3

Computer Merchant

Sr. ETL Lead Designer/Developer

Wyoming State Project – Remote Oct 2020 – Mar 2021

Requirement Analysis and design reviews.

Involved in reviewing documentation on Legacy system to Understand data structures and data values.

Work with data conversion team in data analysis, leveraging legacy data sets and proactively provide the results of the analysis to the client / internal teams to help accelerate legacy data corrections and/or formalization of business rules for effective data migration.

Work with data owners to verify and obtain approval that any transformed data retains its accuracy.

Develop all the data profiling, data cleansing, and loading scripts.

Perform Data analysis and Data Profiling on the Legacy data (Mainframe Files).

Develop Data cleansing scripts on Legacy data using PL/SQL stored program units/packages and UNIX shell scripting.

Responsible to attend sessions with Client for clarification on any data anomalies observed during data profiling.

Work with Application Team to identify the business rules and develop transformation rules to be applied on the data to be migrated.

Responsible for creating the processes and maintaining ETL development, test, and production environments (using DataStage tool).

Responsible for reading all the Mainframe COBOL Source Files and load to the Oracle database.

Responsible for handling all the Complex issues encountered during the design and development phase.

Responsible for Fine Tuning jobs /PL SQL Scripts for higher performance.

Execute one off data migration jobs as required.

Create unit test plans and unit test all data migration related processes.

Write unit tests (document and code) for the PL/SQL stored program units/packages and ensure migrated data is available to support the system and integration testing (SIT), Govt. acceptance testing (GAT), parallel test, and volume and performance test.

Environment: IBM InfoSphere 11.7 (DataStage Designer, DataStage Administrator, DataStage Director), Oracle 12c, Oracle SQL Developer

Nevada State Project – Remote May 2020 – Sep 2020

Analyze existing source system and ETL process and majorly contributed to developing a roadmap for building an enterprise data warehouse.

Participate in business requirement workshops to capture the business requirements for operational reporting

Interact with business users and document technical specs for the ETL developers.

Put together the logical model and build the source to target mapping document for physical model.

Involve in design, development, unit testing, implementation, production and post-production support of ETL using DataStage.

Create and manage the job schedules using DataStage Director and Tidal scheduling tool.

Experience working with Mantis Defect tracking tool, to log, view and update any tickets related to bug fixing.

Experience working on complex Oracle SQL queries, procedures and packages.

Have major contribution to successful rollouts of multiple projects, ERP system upgrade, ETL tool upgrade, and Reporting tool upgrade to name a few.

Proven experience with quick resolution and high accuracy.

Knowledge of developing data pipeline using Hive/ PIG and Impala.

Very good in time and task management.

Well recognized for the communication skills and work ethics by the business users and management.

Environment: IBM Information Server 11.3 (DataStage Designer, DataStage Administrator, DataStage Director),

Oracle 12c, JDE 9.1, SQL Server, TOAD 11, Oracle SQL Developer, Arcplan 9.1, Cognos, Tableau, LINUX, Tidal,

Mantis

Environment: IBM Information Server 11.3 (DataStage Designer, DataStage Administrator, DataStage Director),

Oracle 12c, JDE 9.1, SQL Server, TOAD 11, Oracle SQL Developer, Arcplan 9.1, Cognos, Tableau, LINUX, Tidal,

Mantis

Environment: IBM InfoSphere Server 11.7 (DataStage Designer, DataStage Administrator, DataStage Director), Oracle 12c, JDE 9.1, SQL Server, TOAD 11, Oracle SQL Developer

IBM, Raleigh NC May 2012 – May 2020

Sr. InfoSphere DataStage Developer

Project – This project involves building various DataMart’s like Customer Data Mart, Customer Revenue Data Mart, and Products Data Mart. The source for these DataMart’s include Cloudant DB

Responsibilities

Working with the Product Owner to get the business requirements and creating High Level Designs for the DataStage Jobs as per the cases given by the business team.

Analyze the requirements and document the technical requirement document with business rules/transformation mapping rules.

Involved in Data Modeling discussions.

Analyze the source data before creating the design and provide mapping document for the design.

Designed the DataStage jobs and Sequences for both batch and Full Loads.

Created reusable DataStage jobs and sequences that could be used by Dimension and Facts that have common functionality

Performed Data Analysis and report data discrepancies to Source system for rectification.

Fine-tuned the DataStage jobs and DB2 Procedures for the performance

Mentored the new team members on the existing procedures and helped them in understanding the requirements.

Monitoring the Batch in System/Integration and UAT Testing.

Written Complex SQL queries and SQL programs.

Scheduled the DataStage jobs/Sequences using the DataStage scheduler and Cron-tab.

Provided production support for any production issues with the data/code.

Environment: IBM WebSphere DataStage 11.7/11.5/9.1, IBM WebSphere DataStage 8.7/8.5/8/1, DB2 10, Shell script, Cloudant DB, CRONTAB, TWS

Health Alliance Plan, South Field MI Sep 2010 – May 2012

Sr. InfoSphere DataStage Developer

Health Alliance Plan Healthcare is one of the largest insurance companies in the United States which provides medical insurance plans to individual/family and companies. The purpose of the project was to create a centralized Data Warehouse by integrating its Policy and Claims databases for providing better support to organizations decision support systems.

Involved in the development of Operational Data Store (ODS) and Enterprise Data Warehouse (EDW) which are mainly used for reporting through Cognos.

Extensively used the Aces Frame Work which stores all the parameters that are required for running the jobs.

Involved in design and development of data warehouse environment and translated business process into DataStage jobs.

Involved in the analysis, data modeling, detailed system design, development and technical documentation.

Involved in modeling the Party Model and developed DataStage jobs to load the Member, Subscriber, Provider, Practitioner/Facility data into the model.

Identified and documented data sources and transformation rules required to populate and maintain data warehouse.

Designed DataStage Parallel jobs involving complex business logic, update strategies, transformations, filters, lookups and necessary source-to-target data mappings to load the target.

Applied the change data capture (SCD II) logic for all the tables before loading the dimensions into Enterprise data warehouse.

Designed and developed the generic jobs for Change data Capture using Generic Stage and loaded the data from FACETS to ODS and developed jobs to load the data to the Facts and Dimensions in EDW for reporting with Cognos.

Created UNIX shell scripts to schedule the DataStage sequence jobs passing all job parameters from the ACES Tables to execute dynamically during run time.

Extensively worked on Job Sequences to Control the Execution of the job flow using various Activities & Triggers (Conditional and Unconditional) like Job Activity, Wait for file, Email Notification, Sequencer, Exception handler activity and Execute Command.

Extensively used the Sequential File stage, Hashed File Stage, Modify, Dataset, Filter, Funnel, JOIN, Lookup, Copy, Aggregator, and Change Capture during ETL development.

Used Data Stage Director to Run and Monitor the Jobs for Performance Statistics.

Developed Provider process to load provider related information into provider table after various checks and validations including ETL architecture design and development.

Developed Group process to load group related information into group tables after various checks and validations including ETL architecture design and development.

Worked with Data Stage Manager to import/export jobs and routines from repository and also created data elements.

Implemented logic for Slowly Changing Dimensions Type II by using Date methodology.

Involved in Performance Tuning of Parallel Jobs using Performance Statistics.

Extensively worked with database objects including tables, views, indexes, schemas, stored procedures, functions, and triggers

Environment: IBM InfoSphere DataStage 9.1, oracle 11g/ 9i, Windows, Oracle, Flat files, Sequential files, Fixed Width Files, TOAD 9.6., Facets

Bank of America, Remote Jun 2009 – Aug 2010

Sr. DataStage Developer

Involved in design and development of data warehouse environment and Translated business processes into DataStage jobs for building data marts

Involved in developing business required documents along with business analyst and extensively analyzed and designed ETL processes for better performances.

Identified and documented data sources and transformation rules required to populate and maintain data warehouse.

Created DataStage parallel jobs to load data from Oracle Databases, sequential files, flat files and MS SQL Server.

Extensively used DataStage Designer for creating new job categories, metadata definitions and data elements, import/export of projects, jobs and DataStage components, viewing and editing the contents of the repository as well as writing routines and transforms.

Used DataStage Designer to design and develop jobs for extracting, cleansing, transforming, integrating, and loading data into different Data Marts.

Used several stages like Sequential file, Aggregator, Funnel, Change Capture, Change Apply, Transformer and Lookup during the development process of the DataStage jobs.

Created parameter sets to group DataStage job parameters and store default values in files to make sequence jobs and shared containers faster and easier to build and worked on troubleshooting, performance tuning and performance monitoring for enhancement of DataStage jobs.

Converted complex job designs to different job segments and executed through job sequencer for better performance and easy maintenance.

Analyzed and enhanced the performance of the jobs and project using standard techniques.

Extensively used Autosys for automation of scheduling for UNIX shell script jobs on daily, weekly monthly basis with proper dependencies.

Performed Unit testing and System Integration testing on mappings to check for the expected results and documented the purpose of mapping to facilitate better understanding of the process and to incorporate the changes as and when necessary.

Extensive usage of Toad for analyzing data, writing SQL, PL/SQL scripts performing DDL operations.

Environment: IBM InfoSphere DataStage 8.7/9.1, Oracle 11g, MS SQL Server 2005, MS Access, Flat files, TOAD 9.1, SQL, PL/SQL, Autosys, UNIX Shell Scripting

HSBC, Mettawa IL Aug 2008 – June 2009

Sr. DataStage Developer

Used IBM WebSphere DataStage 7.5.2/8.0.1 ETL tool effectively to implement complicated transformations

Developed DataStage jobs using Parallelism and Partitioning

Used Transformer stage for different transformations and aggregations

Used Lookup transformations to manipulate the information

Developing Parallel jobs using various stages including Lookup, Aggregator, Join, Transformer, Sort, Merge, Filter, Funnel, Remove Duplicates, Change Data Capture, Copy, Row Generator and Column Generator

Used DataStage Designer to develop jobs for extracting, cleansing, transforming, integrating, and loading data into data warehouse database

Responsible for creating/executing and scheduling DataStage jobs

Was effectively involved in code reviews

Tuned of SQL queries for better performance for processing business logic in the database

Created and worked with shell scripts for the automation process of the jobs and also to read job parameters from the files

Leading complex technical data warehouse discussions on modeling, integration, and overall technology solutions and design.

Gathered client requirements, validated and analyzed client data to find trends and relationships

Managed mix teams of on/off shore resources in day to day development.

Created shared containers to use the business logic in multiple jobs

Worked on developing data mapping documents and mapping rules to illustrate data flow from source system to target tables

Environment: IBM WebSphere DataStage 7.5.2/8.0.1 (Designer, Director, Manager, Administrator), DB2, UNIX, Shell Scripting

Sears, Hoffman Estates IL Jun 2008 – Aug 2008

Sr Data Integration Designer

Created technical solutions and design for Business Intelligence Data Integration (ETL).

Managed review and approval of solution designs, technical specifications, and ETL/ELT processing designs

Managed and mentored technical staff whose support is needed to build and/or deploy Data Warehouse

Served as primary point of contact for customers and other organizations to resolve data warehousing issues

Managed mix teams of on/off shore resources in day to day development.

Documented/developed test cases and used them to run through each process for each Parallel job, Sequencer jobs for both the Unit and Integration testing

Responsible for creating/executing and scheduling DataStage jobs

Experience in Parallel Extender (DS-PX) developing Parallel jobs using various stages including Lookup, Aggregator, Join, Transformer, Sort, Merge, Filter, Compare, Funnel, Remove Duplicates, Change Data Capture, Peek, Head, Tail, Copy, Row Generator and Column Generator

Designed and Developed Extract, Transform and Load (ETL) processes from a variety of Transactional systems including legacy systems utilizing SQL, DataStage Server and Unix shell Scripts.

Environment: IBM WebSphere DataStage 8.0.1 (Designer, Director, Manager, Administrator), Oracle 10g/9i, Teradata, Netezza, DB2/UDB, PL/SQL, UNIX, Shell Scripting, Micro Strategy

Walgreens, Chicago, IL Nov 2004 – Jun 2008

Sr. DataStage Developer

Worked with the Business analysts and the DBA for requirements gathering, business analysis, testing, and project coordination

Involved in Logical and Physical Database design and Star Schema design. Identified Fact tables, Transaction tables

Worked extensively with Parallel Jobs and Job Sequencers.

Worked with stages like Data Set, Merge, Join, and Look Up for performing lookup functions on the source and lookup datasets

Worked with different stages like Copy stage, Funnel Stage, Transformer stage, Aggregator stage, Filter stage, Sort stage

Developed DataStage jobs using Parallelism and Partitioning

Designed and created custom routines, functions to perform business rules

Tuned the Parallel jobs for better performance

Worked with Row Generator and Column Generator stages to create test data and peek stage, Head Stage, Tail Stage is used for debugging purpose

Documented/developed test cases and used them to run through each process for each Parallel job, Sequencer jobs for both the Unit and Integration testing

Tuned of SQL queries for better performance for processing business logic in the database

Created and worked with shell scripts for the automation process of the jobs and also to read job parameters from the files

Environment: IBM WebSphere DataStage 8.0.1/7.5.2 (Designer, Director, Manager, and Administrator), Oracle 10g/9i, PL/SQL, Windows NT, UNIX, Shell Scripting

MIMIT, Melrose Park IL Jan 2002 – Oct 2004

Oracle Developer

Involved in composition of functional specification/high level design documents based on customer requirements

Close work with different teams assigned to different functional areas.

Validation of designs with client through interviews and design walkthroughs

Interaction with clients in remote locations to verify customer requirements

Wrote embedded SQL statements and PL/SQL stored procedure calls shared across modules

Creation of UNIX shell scripts to implement various system/environment administration tasks

Enhancement and modifying existing modules

Developed several packages, stored procedures, functions, cursors, collections and triggers in PL/SQL using both static and dynamic SQL with error handling routines.

Developing the code for the backend PL/SQL validation using triggers for BEFORE INSERT, DELETE and UPDATE on tables.

Environment: Oracle 7.0, Reports 2.5, SQL, PL/SQL, UNIX

Contact this candidate