We provide IT Staff Augmentation Services!

Lead Etl Consultant Resume

Mn, UsA

Summary

  • Lead ETL Consultant with 10 years of experience in analysis, design, development and implementation of full life cycle of Data Warehousing & Business Intelligence Projects.
  • 10 years Experience in Information Management Space as Lead ETL Consultant
  • 8 years hands on experience on Data Stage ETL Tool
  • 2 years hands on experience on Informatica ETL Tool
  • Diversified domain expertise implementing Solutions for Data Warehousing Projects.
  • Over 5 years Excellent experience in BFSI Domain
  • Very Good experience in Automobile and Health care Domains
  • Good experience in Sales & Marketing Domain
  • Expertise in BFSI Domain with Anti Money Laundering, Risk Management
  • Excellent Experience in working on Data Stage Server and Parallel Extender Jobs using Data Stage Versions 7.x / 8.x
  • Having excellent experience working flexibly both in Development as well as maintenance projects in efficient manner
  • Extensive experience in loading medium, high volumes of data into data warehouse
  • Proven track record in troubleshooting of data stage jobs and addressing production issues like performance tuning and enhancements.
  • Excellent Experience in SDLC, Strategic Planning, Operating Systems, Requirements gathering and Analysis.
  • Proven Expertise in Data Architecture for the projects worked
  • Good Experience in Data Modeling using ERWIN Tool
  • Strong database skills with Oracle and DB2
  • Very good experience with UNIX Shell scripting
  • Proactive in timely completion of project deliverables, handling and resolving issues.
  • Strong technical skills suitable for process design, development & Execution.
  • Strong interpersonal, written and verbal communication skills.
  • Flexible and versatile to adapt to new environment and working on new initiatives.

TECHNICAL SKILLS

SKILLS TOOLS
  • ETL Tools: Data stage 7.x / 8.x
  • Reporting Tools: Business Objects, COGNOS
  • DATABASE: Oracle , DB2
  • OPERATING SYSTEMS: Windows , IBM AIX , HP Unix
  • Testing Tools: HP Quality Center
  • Scheduling Tools: Autosys, Control M
  • Configuration Tools: IBM Clear Case, SVN, HP ChipRe
  • Data Modeling Tool: Erwin

Professional Work Experience

Confidential, WI, USAOCT’11 – Till Date
Project:Group Market Risk Lead ETL Consultant
Project Description:
This project is developed to calculate the market Risk for Bank by classification of systems and processes into batches. Customer data that exists on VSAM and DB2 must be made available for real-time searching in varying ways. Real-time updates do occur to all sources on which customer data currently exists. Current requirements dictate that searches might occur currently on Quoted Name, Insured Name, Customer Number, Claim Number, Agency code, Loss of Date, Policy Number and Bond Number Information.
Batches start at predefined batch run time by receiving files from the different Source Systems. The data in the files will be Extracted, Transformed and Loaded to Database tables in theSystem. From the system, the business for the concerning systems in the batch will get the reports. Based on the generated reports, the Business will be able to identify the risk involved for the concerning systems of the batch. By Analyzing reports at GROUP Level which consists of all systems, the business will estimate the Market risk for the bank.
Responsibilities:

  • Designed jobs, sequencers for E, T & L Parts of the project.
  • Participated in Tactical Solution design & Data Architecture of the Project
  • Designed parallel Jobs and sequencers for queryable data warehouse
  • Developed reconciliation jobs for E, T & L stages to run on daily basis.
  • Prepared detailed design and program specification documents.
  • Used Erwin Tool and Done Data Modeling for the project
  • Participated in client meetings, Handled Business & technical Dependencies
  • Impact analysis & estimations for change requests, enhancements and system integrations.
  • Involved in creating, administering repositories, Folders, Permissions
  • Used Data stage Administrator for defining environment variables
  • Coordinating with client to gather requirements and understand the processes.
  • Developed jobs, sequencers for new system rollout and successful implementation
  • Reporting the status from time to time to Managers and Client.
  • Involved in reviewing Business/System requirements and other deliverables.
  • Fine tuned Data stage jobs for optimal performance.
  • Prepared scripts for Database operations and UNIX shell scripts to support ETL process.
  • Provided support to the system testing and resolve issues raised by system testing team
  • Prepared Annual system run manual and service level agreement by involving all stakeholders

Environment:Data stage 8.5, Oracle 9i/10g, Cognos, AIX, Erwin, Autosys
Confidential, MN, USAOCT’10 – SEP ‘11
Project: Sales Data MartLead ETL Consultant
Project Description:
Carlson Marketing Worldwide is a global marketing services company with the line of business including Carlson 1to1, Sales and Marketing Services, Meetings & Events. As part of the Sales & Marketing services CMW offers an innovative loyalty, rewards, and incentive & performance improvement solutions with in a scalable framework to its clients worldwide.
CMW is implementing a saleable loyalty platform solution that can be shared across multiple clients. As part of this initiative, CMW migrate the existing loyalty services application to the strategically enhanced Matrix system - Loyalty Saleable Solution and at the same time developing a reporting data mart for this new application. This data mart serves the purpose of monthly loyalty reporting requirements for Carlson and the reporting solution is designed using cognos.

Responsibilities:

  • Designed complex Dimension and Fact jobs to load data into queryable data warehouse
  • Prepared detailed design and program specification documents.
  • Prepared impact analysis & estimations for change requests, enhancements and system integrations.
  • Involved in Data Architecture and Data Modeling parts of the project
  • Validated the developed Data stage jobs with pre-defined ETL design standards.
  • Developed SQL Procedures for synchronizing data and extensively used SQL and PL/SQL coding in Data stage jobs for processing data.
  • Utilized the available processes to achieve job performance, configuration management of system resources in Orchestrate environment.
  • Prepared the Database scripts for newly added systems and countries
  • Involved in creating, administering repositories, Folders, Permissions
  • Used Data stage Administrator for defining environment variables
  • Ensure configuration management
  • Involved in testing in various phases like Unit Testing, SIT and UAT.
  • Prepared release notes, run manual document for new change requests, enhancements & rollouts
  • Resolved run time errors occurred during Daily, Weekly and Monthly Runs.
  • Involved in reviewing Business/System requirements and other deliverables.
  • Participated in Tactical Solution design & development for the application
  • Identified, resolved source file format issues for production loading & data quality
  • Involved in creating error logs and increase the performance of the jobs

Environment:Data stage 8.5, DB2, oracle, Business Objects, UNIX, Erwin, Autosys.
Confidential, OH, USAOCT’08 – SEP ‘10
Project:Data Hub Lead Data stage Consultant
Project Description:
This Project is for supporting the Trade Promotion processes, from Annual Planning through the Execution and Post Evaluation phases. It aims to extend the usage and functionality to the whole US business, improving some critical areas of the application and of the reporting system to fully support the business requirements. This is also aimed at incorporating some of the changes / enhancements being made to existing system. For this developed new jobs and modified existing jobs and some enhancement jobs, where source feed is Database, Flat Files and Legacy Systems.
To connect to three source systems via data stage and extracting and applying business rules and loading the data to target system. Data Profiling had done using IBM information analyzer. That means before extract the data into staging area need to do profiling. Implemented star and snow flake schema modeling for fact and dimensional tables. Loading is done on daily basis and monthly basis.
Responsibilities:

  • Involved in complete life cycle of Design, Development, Maintenance and Documentation Conversion Process.
  • Developed complex Dimension, Fact jobs and Job Sequences
  • Involved in Data Architecture and Data Modeling parts of the project
  • Prepared technical design, and supported Unit, System and user acceptance testing.
  • Performed export and import of Data stage components, table definitions and routines.
  • Used Data stage Director to clear the job logs, job resources and status files.
  • Involved in setting up the ETL standards and helped client to use ETL best practices.
  • Extensively used shell scripting for seam less interface of job runs
  • Identified performance bottle-necks and suggested improvements.
  • Provided hands-on participation, technical guidance and leadership to support existing Data warehouse applications, involving Data stage, Quality Stage.
  • Developed in writing transforms using stage variables for jobs and code for batch jobs to run jobs in parallel.
  • Involved in creating, administering Project repositories, Folders, Permissions
  • Profiled vendor supplied raw data using Information Analyzer.
  • Used Data stage Administrator for defining environment variables
  • Logged defects and analyzed the root causes and data validations.
  • Used Unix Shell Scripts Extensively to clear the redundant data
  • Analyzed the Data stage logs, documented the daily issues.
  • Resolved production issues and given support to application maintenance team.
  • Interacted with clients and clarified about validations in the COGNOS reports.

Environment: Data stage 8.1, Oracle 9i, Business Objects, AIX, Erwin, Control M

Confidential, CA, USAAug’06 – July ‘08
Project:Anti Money Laundering Data stage Consultant
Project Description:
To comply with the Anti Money laundering (AML) Laws for the concerning countries regulatory banks accord, JPMC has developed wholesale banking anti money laundering (WB AML) System. The bank transactional data, which comes from different source systems in the form of files are Extracted, Transformed and Loaded into Data base and target files. The files will be saved to Norkom file format and fed to the AlchemistSystem. Once loading is finished, the data will be moved to ALCHEMIST (NORKOM Model) in the form of files. Post Loading of Data for respective countries, COGNOS and SDMS Reports will be generated for Exceptions, Rejections & Reconciliation of Transformation and Loading Data. Business will analyze the COGNOS / SDMS reports. If any discrepancies found in the reports, Alerts will be generated.

Responsibilities:

  • Developed jobs to replace PAS system with EBBS system for country US
  • Created the Technical Design Documents for the jobs, Sequencers.
  • Attended walkthroughs and review meetings to discuss new change requests, enhancements
  • Delivering solutions for enhancements & change requests
  • Developed Common Sequencer Jobs which can be re-usable
  • Resolving different run time errors which occur in production environment.
  • Playing key role in resolving the problem tickets raised by the users.
  • Prepared Annual system run manual and service level agreement by involving all stakeholders
  • Communicated with the Business People and Operational Team for Transaction Monitoring Enhancement Project.
  • Sorted out the technical as well as functional issues through client interactions.
  • Identified, resolved source file format issues for production loading & data quality issues post loading
  • Involved in creating error logs and increase the performance of the jobs
  • Used configuration, problem & change management tools clear case, action remedy
  • Developed jobs for SDMS reports used on daily basis for business use
  • Developed reconciliation jobs for Extraction, Transformation & Loading stages to run on daily basis.
  • Developed jobs, sequencers for new country rollout and successful project implementation
  • Participated in Tactical Solution design & development for the application
  • Created Work estimations and time estimations for Development, SIT and UAT Phases.
  • Involved in creating, administering repositories, Folders, Permissions
  • Logged defects and analyzed the root causes and data validations.

Environment:Datastage 7.5, DB 2, Cognos, AIX and Autosys

Confidential, INDIAAug’04 – July ‘06
Project:Risk Vision Data stage Developer
Project Description:
Risk Vision is a Risk management system, a product of Misys using around the world by top banks and Financial Institutions. Objective of risk management system is to put in place and operate a systematic process to give a reasonable degree assurance to the top management. All the risks that come in way of the institution achieving the goals it has set for itself would be managed properly by the Risk Management system. In Risk Vision 6.0 application functionality wise remains same as Risk Vision5.4 but it is migrated to new look and feel. Sybase Stored procedures are replaced with code to support database independence. There are three modules called RV Trades, RV Data and RV Work flow.
Project Responsibilities:

  • Analyzed Project Documents and Design Specifications
  • Designed jobs, sequencers for E, T & L Parts of the project.
  • Developed complex data stage jobs to load Dimension and Fact jobs for data warehouse
  • Participated in client meetings.
  • Handled Business and technical Dependencies
  • Prepared impact analysis & estimations for change requests, enhancements and system integrations.
  • Modified Data Stage Jobs to satisfy the business requirements.
  • prepared the Database scripts for newly added systems and countries
  • Written scripts and Procedures to have interface between different modules
  • Involved in testing in various phases like Unit Testing, SIT and UAT.
  • Involved in UAT and implementation of code to production.
  • Prepared test cases for UAT for enhancements.
  • Prepared Phase wise Project Plan for jobs and sequencers.
  • Configuration management using clear case
  • Prepared release notes, run manual document for new change requests, enhancements & rollouts
  • Participated in developing compare system to do analysis on data post loading
  • Extensively used change and problem management tool action remedy
  • Involved in Migrating Data Stage jobs from old version to new version.
  • Prepared detailed design and program specification documents.
  • Resolved run time errors occurred during Daily, Weekly and Monthly Runs.
  • Interacted with clients and team members for fixing the bugs in SIT.
  • Resolved production issues and given support to application maintenance team.
  • Mentored Team members and peers about the Risks for existing system to quickly understand the Applications and processes.

Environment:Data stage 7.5, DB 2, Cognos, Unix & Control M.

Confidential, IndiaAug ‘02 - July ‘04
Project:Ford – Enterprise Data Warehouse Informatica Developer
Project Description:
Ford is the major manufacturer of motor vehicles. The main objective of the project is to outsource the Strategy, Design, Development, Coding, Testing, Deployment and Maintenance of the Enterprise Data warehouse. This Project would cover the subject areas such as Vehicle Product Offering (VPO), Volume Mix Rates (VMR), Posts Structure Cost (PSC), and Account for Production Material (APM) and Control Vehicle Financials (CVF). Data from various source systems is gathered into a staging area, where data is cleansed and then finally moved into an Enterprise Data warehouse Database. Informatica 6.2 is used for extracting data from various Sources to Staging area; Staging area to DW. Business Objects is used as the reporting tool.
Project Responsibilities:

  • Development of the Informatica Mappings as per the Functional specifications.
  • Developed standard and reusable transformations and mapplets
  • Created sessions and workflow for designed mappings.
  • Extensively used Informatica Client tools – Source Analyzer, Warehouse Designer, Mapping Designer, Mapplet Designer, Informatica Repository Manager and Informatica Workflow Manager.
  • Created complex Informatica mappings to load the Datamart. The mappings involved extensive use of transformations like Aggregator, Filter, Router, Expression, Joiner, Sequence generator…etc.
  • Developed PL/SQL Procedures/Functions to build business rules to load data.
  • Regularly monitor the Informatica jobs, validate the target data with source files.
  • Manage the data integrity & organize the data in case of source system failure
  • Prepared the system delivery Documentation Configuration Management.
  • Created the Complex mappings, done the performance tuning for the developed mappings.
  • Created test cases and test scenarios to test the mapping according to business logic.
  • Involved in the unit testing, system testing & User Acceptance Testing
  • Loaded the data for bulk and range as per the requirements
  • Providing fixes for critical bugs and assisting code moves to SIT/UAT/PROD
  • Backup and restore the repositories and deployment of code in QA/UAT servers
  • Fixing the production/UAT bugs & Preparing the Release Notes for Service Delivery people
  • Performance tuning of the developed mappings.
  • Created partitions at Informatica session level to improve the performance of Informatica workflows.
  • Application Maintenance and Enhancements

Environment:Informatica 6.2, Business Objects 5.1, Oracle 9i, Windows NT 4.0 and Unix

Education
M S Computer Science fromConfidential, India.

Hire Now