SUMMARY

• A Dynamic IT professional with 9 years of work experience in Data Analysis, design and development of ETL applications
• 8+ years of strong experience in designing and developing Business Intelligence solutions in Data Warehousing Systems using ETL tools Datastage PX … and with strong business understanding knowledge of Banking, Insurance, Financial, Healthcare and postal verticals.
• 3+ years of experience in designing and developing solutions using Informatica and Pentaho ETL tools.
• 5+ years of ETL lead experience in leading multiple projects in Finance and healthcare verticals.
• 6+ years of experience with SQL, and Database Stored Procedures using ORACLE (PL/SQL), SQL server and Teradata.
• Developer in Analysis, Design and Implementation of Business Applications using the Oracle Relational Database Management System (RDBMS).
• Effectively made use of Table Functions, Indexes, Table Partitioning, Collections, Analytical functions, Materialized Views, Query Re - Write and Transportable table spaces.
• 6+ years of experience in UNIX Korn shell and awk scripts, Sun Solaris8, IBM Unix
• 7+ years in ETL Datastage testing and involved in writing test cases for various complex scenarios.
• Involved in the data analysis for source and target systems, and good understanding of Data Warehousing concepts, dimensional schema, Star schema and Snow-Flake schema.
• 7+ years in preparing technical specification documents.
• 2+ years in Data Modelling and ETL Architecture of Data Warehouse which contains 70+ Marts.
• Expertise in RDBMS concepts, conceptual/logical/physical data.
• Experience with Performance Tuning for Oracle RDBMS using Explain Plan
• Working knowledge of ETL design and commercial frameworks that implement the data design patterns.
• Implemented Performance Tuning techniques at application, database, and system levels
• Worked in Squid programming to generate reports. Have worked in mainframe applications in banking domain
• Have knowledge on Epic Clarity database and its data model. Have knowledge on SSIS, SSRS and SSAS.
• Have working experience in Designing, developing and testing in Business Objects Reporting Tool.
• Expertise in developing staging components in Ascential and Infosphere datastage 7.5, 8x and 8.5 and execution of them in UNIX environment by using Control-M Scheduler.
• 2+ years of experience in designing, developing and testing Crystal XCelcius Dashboard Tool and Quality Stage 8.1 Tool.
• 3+ years of experience in Control-M scheduling Tool. 3+ years of experience in TWS (Tivoli Integrated Portal) and Pentaho ETL tool.
• Have work experience on Visual Source Safe and Service Now Deep understanding of developing transactional enterprise data models that strictly meet normalization rules, as well as enterprise data warehouses using Kimball and Inmon data warehouse methodologies.
• Strong analytical skills with ability to quickly understand client's business needs and create specifications.
• Excellent team player with strong written, communication, interpersonal, analytical skills, Capable of working under high stress environment with resource constraints.
• Willing to learn new concepts and accept challenges with positive attitude.

TECHNICAL SKILLS Languages: DataStage 9.1.2/11.3, Oracle 10g/11g, PL/SQL, Aqua Data Studio 15.0, UNIX shell Scripting, TWS, Tortoise SVN.WORK EXPERIENCE

ETL Datastage Developer

Responsibilities:

• Work with the BA and technical architect to prepare the design douments required.
• Provided solutions to implement this migration and designed data models to control the flow of data to different domains.
• Worked with technical architect to setup the end-to-end ETL framework for this project.
• Designed and developed jobs using ETL datastage to handle claims data. Worked in Lab and Galaxy exchange data to capture data from the galaxy, oxford and UDW sources.
• Developed jobs using ETL Datastage 11.3 to process SDS data for Retrospective and Prospective runs. This includes the data analysis and data generation for multiple sources in SDS.
• Look-ups on flat files and databases, aggregation and transformation stages involved before loading to target.
• Created Squid reports using Squid process to capture the Member keying data, audit and error reports.
• Involved in writing shell scripts to handle Squid process and Secure file transfer(FTP/SFTP) of the data files, audit and error files which includes various level of data and transfer validation.
• Developed UNIX shell scripts to handle data split for different domains consisting of 75 work areas which uses the Break Item and Major information used for data packaging replacing the squid process.
• Involved in unit testing and prepared unit test cases document. Coordinated with QA team helping them with regression testing for various domains.
• Worked with data fabric team in running squid reports and generating packaging for measure analysis.
• Created Shell Scripts for invoking SQL scripts and scheduled those using TWS.
• Used DataStage Director and TWS (Tivoli Integrated Portal) to schedule running the solution, testing and debugging its components, and monitoring the results executable versions (on an ad hoc or scheduled basis).
• Involved in developing Source to Target mapping document for all the ETL jobs including the Test Cases.
• Tuned DataStage jobs using appropriate usage of Environment variables.
• Worked on Db2 using Aqua Data Studio to extract data from galaxy and transform them as per STT and performed data profiling the various source related information.
• Created scripts to create new tables, views, queries for new enhancement in the application using Aqua Data Studio.
• Performed SQL and PL/SQL tuning and Application tuning using tools like EXPLAIN PLAN
• Involved in creation of database objects like tables, views, materialized views, procedures and packages using oracle tools like Toad, PL/SQL Developer and SQL* plus.
• Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application.
• Involved in migrating Datastage codes to higher environment and used SVN to maintain version control.
• Designed the application system structure and functional modules. Used the functional document to design logical and physical structure.
• Setting up the UAT platform for UAT testing and co-coordinating with Release Management, Data Management and Configuration Management
• Work with Project Managers in identifying the tasks, providing estimates, timelines and planning resources for the analysis, design, development, testing and implementation
• Work with Data Architect & cross-functional teams in design, development and support of ETL interfaces for data transfer.

Environment: DataStage 9.1.2/11.3, Oracle 10g/11g, PL/SQL, Aqua Data Studio 15.0, UNIX shell Scripting, TWS, Tortoise SVN.

ETL Datastage Developer

Responsibilities:

• Coordinate with technical architect and prepare model jobs for ODS Extract.
• Coordinate with business team, SME and prepared the high level design documents.
• Worked with business analysts to identify the appropriate sources, data elements and created mapping documents for each table.
• Establish integration with Cache system, Oracle database, PL/SQL procedures using API stages & Flat files and build the ODS layer for 75+ tables.
• Provide the technical ESB integration expertise guidance on integrating between ESB and ETL frameworks.
• Designed a highly parallel load to process 100+ million records using datastage.
• Designed Server jobs to integrate the PL/SQL procedures and sequencers.
• Developed the data stage programs to extract data from CDC cache system and load into ODS and later load into staging tables.
• Developed the data stage programs to extract incremental data from ODS for claims, encounters, encounter diagnoses and adjudicated services.
• Designed the Datastage job using Datastage 8.5 & Unix scripts to handle Key validation, Business code validation, Data cleansing, Surrogate key generation, Landing, Staging, Fact load, Merging the data and preparing the aggregates tables.
• Created the Data Stage parallel & server jobs, Routines and sequencer for extracting and loading the data using various stages like ODBC connector, Oracle connector,DB2 connector, Flat File, Data Set, Transformer, lookup, sort, remove duplicate, peek, Change Capture, join, STP stage etc.
• Developed PL/SQL procedures to merge the data into data warehouse tables.
• Developed several complicated PL/SQL procedures using recursive loops and techniques that cannot be designed in datastage.
• Performed the performance and tuning at source, Target and Data Stage job levels using Indexes, Hints and Partitioning in DB2, ORACLE and Data Stage.
• Developed automated notification steps to send customer emails after each business critical step completion.
• Worked with EDI team to send files across the client and the vendor.
• Prepared Validation scripts in landing, Staging & Ware house layer to maintain the data accuracy & integrity.
• Designed and developed Batch Control process to run the Data Stage sequences in batches for restart ability and recoverability.
• Involved in the setup of ETL components, UNIX scripts, DB objects from Dev to Production environment.
• Involved in capturing all the test cases and facilitated to document the same. Involved in unit, functionality and integration testing
• Reviewing & fixing all the bugs during testing and updating in Quality center tool.
• Designed Health tables to maintain the batch ids, statistics, and parameters.
• Purge Process for Data warehouse Table.
• Designed the archival logic, Error handling & Reprocessing of the data
• Co-ordinate with the QA team in various testing phases by resolving the defects and ensuring smooth execution of the test plans.
• Creating the migration documents and migrating the code to the production environment.
• Investigating and fixing the bugs occurred in the production environment and providing the on-call support.
• Performed code review for Datastage jobs/packages created by other developers.
• Involved in Knowledge Management related activities during KT transition of the project.

Environment: Data stage 8x/8.5,Oracle 10g/11g, PL/SQL, Unix Shell script, SQL Developer, Visual Source safe, Project planning & implementation, Maintenance, Service now.

ETL Datastage Developer

Responsibilities:

• Worked in Scripps and Presence Health Client Implementation of the Optum Care Suite (OCS). Work with OCS clients in gathering requirements, customizations of the core product, mapping file layouts, technical design documents, conduct code reviews and create implementation plans.
• Leading a team on Scripps and Presence health client implementation project guiding the team members in understanding the requirements, conduct reviews and managing the project.
• Coordinated with the front end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data.
• Involved in developing datastage server and parallel (PX extender) jobs using Datastage 8.5. Designed routines and sequencer for extracting and loading the data using various stages like ODBC connector, Oracle connector, Flat File, Data Set, Transformer, lookup, sort, remove duplicate, peek, Change Capture, join, STP stage etc.
• Involved in writing UNIX scripts to check the incoming files and to kick off the DataStage jobs
• Created Shell Scripts for invoking SQL scripts and scheduled them using crontab.
• Used Datastage Director and TWS (Tivoli Integrated Portal) to schedule running the solution, testing and debugging its components, and monitoring the results executable versions (on an ad hoc or scheduled basis).
• Developed Source to Target mapping document for all the ETL jobs including the Test Cases.
• Tuned DataStage jobs using appropriate usage of Environment variables.
• Worked on SQL*Loader to load data from flat files obtained from various facilities every day.
• Developed PL/SQL triggers and master tables for automatic creation of primary keys.
• Created PL/SQL stored procedures, functions and packages for moving the data from staging area to data mart.
• Created scripts to create new tables, views, queries for new enhancement in the application using TOAD.
• Performed SQL and PL/SQL tuning and Application tuning using tools like EXPLAIN PLAN
• Involved in creation of database objects like tables, views, materialized views, procedures and packages using oracle tools like Toad, PL/SQL Developer and SQL* plus.
• Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application.
• Analyzed the data extracted from the different source systems based on the business rules.
• Involved in documenting ETL technical specifications for Scripps Health.
• Involved in Data modeling of Scripps health project.
• Designed the application system structure and functional modules. Used the functional document to design logical and physical structure.
• Worked as part of the ETL testing team in writing test cases and coordinate with the relevant Dev teams for optimization of jobs.
• Aggregation and transformation stages involved before loading to final aggregated tables.
• Setting up the UAT platform for UAT testing and co-coordinating with Release Management, Data Management and Configuration Management
• Work with Project Managers in identifying the tasks, providing estimates, timelines and planning resources for the analysis, design, development, testing and implementation.•
• Work with Data Architect & cross-functional teams in design, development and support of ETL interfaces for data transfer.

Environment: DataStage 8.5, Oracle 10g/11g, PL/SQL, SQL*LOADER, Toad, Pentaho 5.0, SQL, UNIX Scripting, TWS.

ETL Datastage Developer

 Responsibilities:

• Understand Business Requirements, Functional Requirements and Design Specifications.
• ETL Tech Lead for the project from Onsite and handling a team at offshore playing the role of an SME for the account.
• Performed Data Modeling for PS FIN application.
• Developed the complete ETL datastage Process which involves PL/SQL, ORACLE 10g.
• Wrote Procedures and functions required in the system to load the data from staging tables to Dimension tables and from dimension or stage tables to fact tables.
• Wrote Procedures and packages to implement the Business Rules for manipulating Claims data in PS FIN applications.
• Oracle logical and physical data\database tuning and optimization
• Wrote procedures and functions required in the system to update the Summary tables for the Reports.
• Played the role of ETL Datastage tester and coordinated with the Dev teams in fixing code and provided feedback on optimization of jobs.
• Worked in SQL PDW in querying and analyzing the claims data.
• Worked in creating ETL Datastage technical specifications documents related to PD FIN application.
• Extensively worked on setting up a new UNIX environment when migrating the project from Development to QA and from QA to Production server.
• Extensively worked on migrating the project from oracle 10g database to Oracle 11g database.
• Worked on moving the files from one server to different servers using FTP.
• Extensively worked on tuning and optimizing the SQL and datastage jobs and achieved in reducing the process completion time from 9 hours to 4 hours.
• Understand Business Requirements, Functional Requirements and Design Specifications.
• Maintained documentation of the procedures and performed unit testing to make sure the desired result is achieved.
• Involved in System maintenance, functionality addition and improvement.
• Daily Client interaction on the requirement and updating the status of the requirements.
• Monitor scheduled batch cycles via Control-M and Resolve Incidents.
• Worked on incidents and do the in-depth analysis of the issue and send a report to the manager about the root cause for the issue and provide effective solutions to make sure the issue is not raised in future.
• Involved in major enhancements and development of People Services HR and Finance applications.

Environment: Datastage 8.0/8.5,Oracle 9i/10g/11g SQL, PL/SQL, Toad, SQL Loader, Windows XP, Control-M, Star Team, UNIX Shell Scripting, Informatica, VPN, PUTTY.

Datastage Developer

Responsibilities:

• Understand Business Requirements, Functional Requirements and Design Specifications.
• Involved in development of the module for Transactions.
• Involved in logical and physical Data Modeling using normalizing Techniques.
• Created Tables, Views, Constraints, Index
• Developed datastage server jobs and parallel jobs using datastage 8.0 and designed sequencers for MDM EUCAN load.
• Involved in developing datastage server and parallel (PX extender) jobs using Datastage 8.0. Designed routines and sequencer for extracting and loading the data using various stages like Oracle connector, Flat File, Data Set,Fileset, Transformer, lookup, sort, remove duplicate, peek, Change Capture, join, merge etc.
• Wrote Unit test scripts and Performed Unit Testing for the same.
• Developed Complex database objects like Stored Procedures, Functions, Packages and Triggers using SQL and PL/SQL.
• Developed materialized views for data replication in distributed environments.
• Loaded Data into Oracle Tables using SQL Loader.
• Created Packages and Procedures to automatically drop table indexes and create indexes for the tables.
• Worked extensively on Ref Cursor, External Tables and Collections.
• Expertise in Dynamic SQL, Collections and Exception handling.
• Lead the MDM team gathering requirements and mentoring them in the specifications.
• Maintained documentation of the procedures and performed unit testing to make sure the desired result is achieved.
• Handled Change Request for MDM-Ireland and Switzerland data.
• Provided support for the MDM-China and Europe load jobs.
• Client interaction on the requirement and provide the update's on the requirement.
• Been part of Siperian support for quality check of data.
• Provided Datastage support for all datastage jobs in R&D.

Environment: Datastage 8.0, Oracle 9i, 10g, SQL*Plus, PL/SQL, Toad, SQL Loader, Windows XP, Control- M, Star Team, UNIX Shell Scripting, Informatica, VPN, PUTTY.

Datastage Developer

Responsibilities:

• Understand Business Requirements, Functional Requirements and Design Specifications.
• Wrote Procedures and packages to implement the Business Rules.
• Wrote Shell Scripts for Data loading and DDL Scripts.
• Wrote conversion scripts using SQL, PL/SQL, stored procedures, functions and packages to migrate data from SQL server database to Oracle database.
• Involved in Logical & Physical Database Layout Design.
• Maintained documentation of the procedures and performed unit testing to make sure the desired result is achieved.
• ETL Tech Lead for a team of 5 members.
• Developed parallel jobs in datastage 8.5 for TODS, HWDC, RDC and Terminal Dues source systems. Extracted the data from oracle into Teradata and wrote UNIX scripts for handling the spreadsheet data.
• Worked in Quality Stage and designed jobs for finding the match frequency and populate MDM with required data.
• Wrote test cases and performed unit testing.
• Prepared LLD for different source systems.
• Have been part of the FIT team and played the role of Fix Lead in Client's location (United Kingdom).
• Been part of the dashboard development in Xcelsius 2008.Also involved in code fixing.
• System maintenance, functionality addition and improvement.
• Client interaction on the requirement and provide the update's on the requirement.
• Writing packages, functions and procedures.

Environment: Datastage 8.5, Oracle 10g SQL/PLSQL, Toad, SQL Loader, Windows XP, UNIX Shell Scripting, Informatica, VPN, PUTTY.

Datastage Developer

Responsibilities:

• Understand Business Requirements, Functional Requirements and Design Specifications.
• Involved in interpreting Jcl Cobol programs
• Involved in development of the module for Add/Update screen.
• Wrote Procedures and packages to implement the Business Rules.
• Wrote Shell Scripts for Data loading and DDL Scripts.
• Developed and modified triggers, packages, functions and stored procedures for data conversions and PL/SQL procedures to create database objects dynamically based on user inputs
• Creating indexes on tables to improve the performance by eliminating the full table scans and views for hiding the actual tables and to eliminate the complexity of the large queries.
• Maintained documentation of the procedures and performed unit testing to make sure the desired result is achieved.
• Database testing is to be done to match both database and UI part. Test cases are prepared based on these scenarios.
• Played the role of a ETL Tech lead for applications related to claims.
• Wrote test cases to match the data in the database and UI part and done execution for these test cases.
• Worked in the testing part by writing queries in oracle to match the data in the database and UI.
• Designed, developed and tested the ETL jobs for the applications.
• System maintenance, functionality addition and improvement.
• Client interaction on the requirement and provide the update's on the requirement.
• Writing packages, functions and procedures. Resolving the tickets raised by the user.

Environment: Oracle 9i, 10g, SQL*Plus, PL/SQL, Toad, SQL Loader, Windows XP, UNIX Shell Scripting, Informatica, VPN, PUTTY.