ETL/Informatica/Oracle/PL/SQL/Unix

ANITHA VIJAYABALA

Senior ETL/Informatica Developer ******.****@*****.***

1-262-***-**** (mobile)

Summary

• More than 8 years of proven experience in the IT industry with development and implementation of Data warehousing and Database Applications using Oracle in UNIX and Windows Environment.

• Expertise in ETL techniques using Informatica Power Center 7.x, 8.x, Oracle PL/SQL Stored Procedures to Extract and Transform data from various data sources and Load to the Transactional/Dimensional Databases.

• Experience in all phases of SDLC including Requirements Gathering, Design, Development, Unit Testing, On-going support and Maintenance.

• Strong experience in Data warehousing, Data Integration, Data Migration and Conversion projects.

• Experience in Production Support (day-time and off-hours)

• Successfully optimized slow-running queries and Informatica code at Mapping and Session level.

• Extensive experience in UNIX Shell Scripting, AWK and file manipulation techniques.

• Extensive experience in Oracle Packages, Stored Procedures, Functions and Database Triggers using PL/SQL. Efficiently used Oracle utilities like SQL Loader, Export/Import in UNIX environment.

• Extensive experience in Documentation of Data Assessment, Data Mapping, Code Review, Test plan and operations.

• Extensive experience in Automating ETL processes.

Technical Expertise

Operating System Windows NT, Linux, UNIX (Sun Solaris, AIX)

Languages C, C++, Shell scripting, Pro* C

(R)DBMS Oracle 10g/ 9i/ 8i/7.x, DB2 7.2, Sybase ASE & IQ

Data Quality Informatica Data Quality, Knowledge in Trillium

ETL Informatica Power Center 7.x/8.x, Power Connect, SQL Loader

Reporting Business Objects 6.5

Enterprise Scheduler Control-M, Autosys

Version Control PVCS, CVS, Clear Case, VSS

Data Modeling ERwin, Power Designer

Database Tools Toad, SQL Developer, Rapid SQL

SCCM Tools MKS Integrity, Mercury Test Director

JPMorgan Chase Oct 2008 – Till date

Informatica Power Center 8.6, UNIX Shell Scripting, Oracle 10g, PL/SQL, Peoplesoft, Toad, PVCS, Control-M, MKS Integrity, Stratus.

Fraud Management System was developed to block the transactions that are associated with suspected account numbers and the issuing country, thereby reducing the risk status of the Merchants. Informatica was used for Data Migration and Extraction of data files.

Major Data Integration solution was implemented to takeover the merchant data from a third party service providing company after its separation from Chase. Informatica, Oracle and PL/SQL, UNIX Shell scripts were used for Data Conversion.

Senior Consultant

• Gathered Requirements from End Users and translated into successful technical designs.

• Designed and developed Informatica solutions to migrate data from the old system to the new Fraud Management System.

• Developed Informatica Mappings/Workflows to load domain data from Peoplesoft to Fraud Management System, create data files and case files to report error.

• Efficiently implemented Change Data Capture (CDC) to extract Merchant information from numerous Peoplesoft tables.

• Designed Informatica Mappings and Workflows to be re-startable after a failure.

• Designed tables required for the execution of the ETL processes using ERwin.

• Extracted data stored in a multi-level hierarchy using Oracle Stored Procedures and Connect-by-prior.

• Loaded Customer data in multiple levels (rows) using Oracle Stored Procedures and Cursors.

• Built a POC on rounding transaction amounts to 6 decimal places in Informatica.

• Used XML Parsers and Generators to handle inbound and outbound XMLs in Informatica.

• Loaded Dimension, Fact and Exception tables and automated email generation when exceptions occurred.

• Provided excellent support during QA/UAT/Beta testing by working with multiple groups.

• Optimized the SQLs and Informatica mappings which handled millions of records.

• Improved performance using Oracle Partitions, Indexes and other Performance Tuning techniques.

• Developed re-usable components in Informatica, Oracle and UNIX

• On-Call/Production Support provided during day-time and off-hours.

• Actively participated in Install/Deployment plan meetings.

• Proved accuracy in doing sanity checks, smoke tests after the install of new scripts used by ETL.

• Played major role in Informatica Upgrade from 8.1 to 8.6 and Linux transition for Grid Architecture.

• Prepared Data Mapping documents, process documents, Re-startability Matrix to help on-call team reduce the turnaround time when there is an issue.

• Efficiently handled multiple projects during resource crunch.

• Provided Training to the team with the functional and technical knowledge.

Harvard Business Publishing Feb 2008 – Aug 2008

Informatica Power Center 8.5, PowerExchange, UNIX Shell Scripting, Oracle 10g, PL/SQL, Oracle E-Business Suite 11i.

The purpose of the project is to create a single view of the customers by generating a unique customer id, which the legacy systems lacked. This involved cleansing, consolidating and re-structuring of the data as part of the data conversion and ongoing data integration processes.

Informatica Data Quality was used for cleansing and matching the customer data. Real-time address cleansing was achieved using Informatica Power Connect for Web Services.

Senior Consultant

• Successfully Integrated Multiple XML sources and created a de-normalized, flat-structured file.

• Analyzed customer data from various feeds and raised the gaps between the source files/tables and the target tables.

• Designed and developed complex Informatica mappings to dynamically create and load customer data in the target system.

• Designed & developed Informatica mappings, mapplets, worklets and workflows to create load-ready data files for loading Oracle E-Business Suite interface.

• Profiled customer data and identified various patterns of the phone numbers to be included in IDQ plans.

• Integrated Data Quality routines in the Informatica mappings to standardize and cleanse the name, address, and contact information.

• Loaded data files into Oracle E-Business Suite interface using PowerExchange option in Informatica.

• Defined Interface parameters in Informatica mappings and sessions to initiate the concurrent programs (Stored Procedures) once the Interface tables are loaded.

• Created Stored Procedures to validate and load the data from interface tables to the Oracle E-Business Suite internal tables.

• Created Unit Test Cases and documented the Unit Test Results.

• Supported Integration testing by analyzing and fixing the issues.

Fidelity Investments Sep 2007 – Jan 2008

Informatica Power Center 8.1.1, UNIX Shell Scripting, Windows NT, Oracle 9i, PL/SQL, Autosys, Clear case (in UNIX)

The project is done to load the new or transitioning funds into the Capstone environment. A fund may be loaded because it is an entirely new fund to Pyramis or because it is a transitioning fund coming to Pyramis from FMR.

Senior Consultant

• Actively Involved in Informatica environment upgrade from version 7.1 to 8.1.

• Created Technical Specification and Data Mapping documents by gathering functional requirements from Business Analysts.

• Developed Informatica mappings and workflows to create intermediate data files by integrating data from feeds and database.

• Used SQL Loader to load data from delimited files into Oracle tables.

• Extensively developed UNIX Shell scripts to transfer and archive account files.

• Developed UNIX Shell Scripts and SQLs to get data from Oracle tables before executing Informatica workflows.

• Developed Oracle Stored Procedures to update the data in control tables and account tables.

• Maintained versions of code and supporting documents in Clear Case (UNIX)

• Prepared and deployed build from DEV to other environments.

• Created Autosys JIL scripts, Control files, Parameter files and UNIX Environmental files.

• Coordinated with QA, BSA and development teams during System Integration and User Acceptance Testing to accurately resolve the issues.

• Supported Production jobs during day-time.

• Created and tracked CRs for requirement changes and enhancements.

• Conducted Knowledge sharing sessions with the Development team.

Sprint PCS Feb 2007 – Aug 2007

Informatica Power Center 7.1.1, UNIX Shell Scripting, Windows NT, Oracle 9i, PL/SQL, Visual Source Safe.

This project is done for Commission group in Sprint PCS. Informatica is used to pre-process the data before calculating commissions for the stores, dealers & partners.

Senior Consultant

• Integrated data from Flat files and other Databases to load into Oracle tables using Informatica.

• Identified Mapping Bottlenecks and Tuned performance at the source, target and mapping levels.

• Identified and Fixed Bugs in existing production mappings after performing a detailed impact analysis.

• Developed Stored Procedures, Triggers, Functions and Packages to implement certain business rules.

• Developed UNIX Shell scripts for validating the files, running stored procedures & Informatica workflows.

• Defined Best Practices for the ETL.

• Created Design documents such as the source to target Data Mapping document.

• Participated in System Requirements Gathering with the users.

• Prepared and Executed the Test Cases during Unit Testing.

• Supported Testing team, UAT Team & Productions support team.

Abbey National Bank May 2005 – Aug 2006

Informatica Power Center 6.x/7.x, UNIX, Shell Scripts, Oracle 8i, PL/SQL, Control-M

Account Level Profitability system extracts data each month from various product application systems within Abbey to compute profitability at an account level. The ranking of the customer is done based on the profitability.

The data are extracted from the source systems to the Oracle database using Informatica mappings. These mappings are invoked from UNIX shell scripts using Control-M tool.

Senior Informatica Developer

• Helped Production Support team during monthly runs and crisis.

• Responsible for analysis and fixing of bugs.

• Implemented changes in the requirements and participated in the Impact-analysis meetings.

• Involved in Upgrading the Informatica mappings from version 6.2.1 to version 7.1.1 E.g.) modified certain functions in the mappings that are not compatible with the higher version.

• Prepared Testing Strategy for the upgraded mappings.

• Involved in testing and Reconciling of the data loaded from v7.x with v6.0.

Central Statistics Office May 2004 – Apr 2005

Informatica Power Center 6.x/7.x, Sybase ASE, Sybase IQ12.5, Windows NT, Oracle 9i, PL/SQL, CVS.

The Data Management System originates from the need to migrate the existing survey-processing systems from the VMS-based environment to a client/server environment i.e.) the DMS application acts as an integrated system for all the surveys and resides on a corporate database. The project extensively uses Informatica to load the survey-data from SAS datasets and flat files to the corporate database in Sybase ASE & IQ.

Senior Informatica developer

Offshore

• Analyzed and validated the data elements against the migration specification and prepared Clarification and Gap documents.

• Analyzed the quality and pattern of the source data and prepared Data Assessment reports.

• Prepared source to target Data mapping documents, Unit Test case and Test log documents.

• Developed Informatica mappings, workflows to populate various metadata tables, survey specific tables in the DMS, control tables and the post-load statistics tables.

Onshore (Ireland)

• Coordinated and delegated work to Offshore team.

• Reviewed the deliverables from offshore and communicated changes to developers.

• Actively participated in Clarification Meetings with the Clients.

• Performed System testing of Data Migration using Target Statistics Reports.

• Automated ETL process by scheduling the jobs using Informatica Scheduler.

• Performed Informatica Administration activities such as Create, Delete, Backup and Restore Repositories.

• Control the versions of the repository in CVS.

JP Morgan Chase Oct 2003 – May 2004

Data warehousing, ETL, UNIX Shell scripting, DB2 7.2, SQL, Autosys, Microstrategy.

This project involves the ETL processing for the Data Warehouse of JPMC-CCOE group. The data for various streams are extracted from AS400 and pushed to UNIX staging area, from where the data get transformed using business rules and loaded into the Enterprise Data Warehouse and Customer-specific Data marts for analytical purposes. Microstrategy was used for populating the metadata and Reports generation.

Datawarehouse developer

• Developed DB2 SQLs for the ETL and for populating run statistics.

• Developed UNIX Shell scripts for File Manipulation, Executing DB2 SQLs and archiving log files.

• Created DB2 SQLs for Quality Management load that determines the accuracy of data loaded in the EDW.

• Actively involved in the preparation of JIL scripts for Autosys jobs.

• Played major role in Bug fixing & Production Support.

• Tuned Performance of the SQLs by evaluating the usage of Indexes in joins.

• Prepared Technical Documents related to design, development and testing as per CMM standards.

Cognizant Technology Solutions Jul 2003 – Oct 2003

Business Objects, Windows NT, Oracle 8i, ERwin

The project involves extraction and loading of HR data from the various source systems, with an intermediate Operational Data Store for MIS reporting purposes, and a final integrated Human Capital Management Data warehouse that forms the base for all OLAP Analytics activities. The scope of this project phase was limited to only Requirement study.

BO Developer

• Gathered requirements from the Human Resources team who are the end users.

• Identified tangible requirements and gaps between the Source data and the requirements.

• Identified and populated Dimensions and Measures with source data.

• Demonstrated analytical reports that can be generated from the Data warehouse, by developing a Prototype using Business Objects.

Citibank Mar 2001 – Jun 2003

Oracle, UNIX, Informatica 4.7, Trillium, Pro*C, PL/SQL, UNIX Shell Scripts, AWK scripts

This project is done to converge all the products of the bank into one database and also to give a single sign-on for the user who uses the application. This project uses the de-duplication process to enable each customer to have a common identification number.

Software Engineer

• Involved in identifying the gaps between ODS and the Siebel environment.

• Designed and Developed Informatica mapping and sessions to create flat files and metadata.

• Developed PL/SQL Stored Procedures, Packages and Database triggers for implementing Business logic.

• Developed PL/SQL scripts for Data loading and Data extraction.

• Involved in Informatica Administration activities such as creating & managing Informatica users, groups and privileges, taking backup of the repository, restoring repository etc.

• Tuned the performance of Mappings, Sessions, relational source and targets.

• Interfaced Trillium to cleanse, de-duplicate and identify household Customers.

• Used Trillium Converter and Parser modules to standardize data and file formats.

• Prepared Software engineering quality documents related to design, development, testing as per CMM standards.

• Responsible for tracking the logged defects in Test Director.

• Analyzed impact and Implemented Change Requests.

Contact this candidate