SUMMARY

• 9.5 years of overall IT exp in designing developing Extract Transform Load ETL applications for Datawarehouse , Business-to-Business B2B and Application-to-Application A2A Data Integration projects , Batch Processing applications and Client Server applications using Informatica Powercenter 9.0.1 , Oracle 11g/10g PL/SQL ,Tibco , Autosys , Unix and Axway Integrator
• 3 years of experience in ETL projects for data warehouse using Informatica Power Center , Oracle PL/SQL , Autosys and Unix
• 4 years of experience in B2B and A2A Integration projects using Oracle Batch processing , Oracle Advanced Queues Materialized views , Tibco Business Works and Axway integrator.
• 2 years of experience in developing Client/server and Reporting applications using Oracle PL/SQL
• Extensive experience in developing Informatica ETL Maps , Sessions and Workflows.
• Experienced in Informatica Transformations namely Source Qualifier , Router, Joiner, Expression , Lookup, Filter , Update Strategy , Aggregator , Normalizer Stored Procedure in Informatica
• Developed SCD Type 2 Dimension ETL , SCD Type 1 Dimension ETL and fact table ETL's with a Staging , Integration and DW 3 tier architecture.
• Extensive experience in creating Stored Procedures, Packages, Triggers, Functions, Tables, Indexes, Advanced queues, SQL Loader , External Tables , Cursors, Collections, Triggers, Scheduler Jobs, Dynamic Query and Materialized views.
• Rich experience in Performance Tuning of DW applications by Advance Query Rewrite, Bitmap index , Bitmap index joins , Parallel DML , Partitioning, Partition pruning and Local indexing
• Rich experience in Performance Tuning using EXPLAIN PLAN, TKPROF and DBMS PROFILER and applying Indexing , Result Cache techniques , Materialized views , Partitioning and Hints
• Experienced in Loading Flat Files to Oracle DB using SQL Loader and External tables
• Experienced in Tibco Business Works and Axway Integrator and developed Integration solutions with Oracle as backend for A2A, B2B and B2A integration components
• Trained in Oracle EBS R12 SCM technofunctional completed a POC in Oracle Purchasing, Inventory and implemented a P2P Cycle setup
• Developed Requisitions , Item and Purchasing Documents Interface to import requisitions and item information from legacy system , validate the data and load them into Oracle EBS Purchasing module
• Worked with DB development tools like TOAD ,SQL Developer and ERwin Data Modeler
• Recognized with Performance Excellence Award from the customer for delivery excellence, Leap Award for Technical leadership excellence and Diamond in the rough award

TECHNICAL SKILLS

• ETL Products: Informatica Powercenter 9.0.1
• Database: Oracle 11g,10g, 9i, SQL server
• Programming Languages: Oracle PL/SQL, C , UNIX, Core Java
• Domain Area: Data Warehouse , Insurance , Asset Management, Supply Chain Management SCM , Enterprise Application Integration EAI
• Development Tools: Informatica Powercenter 9.0.1, TOAD, SQL Developer, SecureFX, SecureCRT,WinSCP ,Putty
• EAI/Middleware product : Tibco business works , Axway integrator
• ERP products : Oracle E-Business Suite: Supply Chain Management SCM Applications R12
• Software Tools: PVCS, Microsoft VSS

PROFESSIONAL EXPERIENCE

Confidential

Senior ETL Developer

Project: Retirement Plan Data Enrichment

Project Description: This project is to incorporate enriched planpremier retirement plan and participant level data into newly built MSS-BI data warehouse. This project will enhance reporting to better understand product health and oppotrunites for improvement , delivery of requested data to key BD's. This project will provide the ability to perform deep dive analysis using more robust participant data to inform strategic inititiaves around asset retention and IRA rollover opputtunies , drivers of participant investment behavior.

Responsibilities:

• Developed Informatica Power Center mapping for loading the feed files from FASCORE into MSS-BI staging database using simple pass through mappings
• Created Informatica mappings to load the data from staging tables to the Integration tables. This ETL involved Connected look up , Unconnected lookup , Dynamic Look ups , Filter , joiner and router transformation to handle data enrichment , fetching the key values from DW tables.
• Created Informatica mappings to load data from Integration tables to the datawarehouse tables. This layer involved CDC Change data capture logic accordingly for type 1 and type 2 ETL's.
• Developed SCD Type 2 Dimension ETL , SCD Type 1 Dimension ETL and fact table ETL's with a Staging , Integration and DW 3 tier architecture.
• Handled Update else Insert feature for duplicate records in the incoming feed file.
• Developed all the ETL's with Restartability feature to enable reprocessing the same monthly load
• Handled Error logging in the ETL by using Normalizer transformation,
• Developed Sessions and Workflows for staging , Integration and DW ETL process
• Used Transformations like Source Qualifier , Router, Joiner, Expression , Lookup, Filter , Update Strategy , Normalizer and Stored Procedure in Informatica workflows.
• Developed stored procedure transformation to handle expiring records which are not received in the montly load.
• Developed oracle process to do Data conversion from the existing historical data in SMART datawarehouse to the new MSS-BI datawarehouse.
• Created Staging tables , Integration tables , DW tables and sequences in Oracle 11g database
• Created Autosys JIL's Job Information Language scripts to call Informatica Workflows , File watcher programs , File removal programs , SQL commands For table truncations as Autosys Jobs
• Created Technical documentation to describe program development, coding, testing and corrections.
• Created Test cases for the ETL components developed and then created integration Testing Document
• Used HP ALM for defect tracking and test case execution

Environment: Informatica Powercenter 9.0.1, Oracle 11g database , Autosys, Unix, SecureCRT , SecureFX , HP ALM

Confidential

Senior ETL Developer

Project: SAAS Epiphany Data warehouse

Project Description: Prudential's dataware system for Sales analytics is SAAS and the sales business application is epiphany. Epiphany system has inbound and outbound interfaces with SAAS datawarehouse and the data flow happens through Informatica ETL mappings.The ETL mappings load the data from SAAS to the epiphany staging tables and from staging tables data is loaded into Epiphany using Oracle PL/SQL components . Epiphany also receives data from various other vendors through oracle External tables. This project involves maintaining the epiphany application and its interfaces with SAAS and other vendors

Responsibilities:

• Designed and developed the staging database for loading the data temporarily before processing to DW
• Carried out Performance Tuning of Datawarehouse applications by applying Advance Query Rewrite, Bitmap index , Bitmap index joins , Parallel processing , Partitioning, Partition pruning and Local indexing.
• Designed and Developed Packages, Procedures, Triggers, Functions, tables, Scheduler jobs ,Partitions and Indexes in Oracle 11g and ETL mappings in Informatica Power center 9.0.1
• Used Oracle External tables to load the feed files from various vendors of Prudential into Epiphany staging database.
• Used Informatica Power Center for extracting, transforming and loading of data from SAAS data warehouse into Epiphany staging database.
• Developed Various Informatica ETL Maps , Sessions and Workflows.
• Used Transformations like Source Qualifier , Router, Joiner, Expression , Lookup, Filter , Update Strategy and Stored Procedure in Informatica workflows.
• Used Informatica Power Center Workflow manager to create Sessions and Workflows to run the mappings.
• Carried out various Performance tuning measures on the Oracle packages and procedure used for moving data from staging DB to epiphany DB.
• Created JIL's Job Information Language scripts to deploy the Oracle Objects and Informatica Workflows as Autosys Jobs
• Created Technical documentation to describe program development, coding, testing and corrections.
• Created Test cases for the ETL components developed and then created integration Testing Document

Environment: Oracle 11g database, Informatica Powercenter 9.0.1, SQL Developer, Autosys

Confidential

Senior Oracle ETL Consultant

Project: MetLife MIAME Data warehouse

Responsibilities:

• Analysed various data feeds of Metlife and arrived at generic feed file layout structures.
• Gathered requirements from the customer and did Data Analysis and Data Cleasing
• Carried out a POC with Informatica Powercenter for the ETL framework and underwent training in Informatica Powercenter 9.0.1
• Developed dynamic query generating processes to process million of records from monthly feeds into the Datawarehouse
• Designed and developed Metadata tables to make the staging process more generic and configurable and extendable for any type of feed/schema from metlife.
• Carried out application level Performance tuning by gathering stats pack, Applying Result Cache techniques, indexing, Partitioning, memory management, Log Management , Purging and Query level Performance Tuning using Explain plan
• Developed the Staging database ,packages and procedures for transformation , conversion and loading of data into the datawarehouse
• Designed and developed Complex dynamic parsing procedures to handle complex layout structures with multiple child layouts repeating within a parent layout.
• Designed and developed a ETL framework using Oracle SQL/PLSQL to load the majority of Metlife's administration system feeds into the datawarehouse based on metadata configurations.
• Performed query performance tuning by using EXPLAIN PLAN , TKPROF and DBMS PROFILER Utilities
• Worked with SQL loader and control files to load the data from Metlife admin Feeds.
• Prepared technical specification documents and design documents for the ETL framework

Environment: Oracle 11g database, SQL Developer, Informatica Powercenter 9.0.1

Confidential

Oracle Technical consultant

Project: Mattel Application Support

Project Description: The project is aimed at supporting the Oracle Application 11i/R12 system of Mattel, the worlds largest toy manufacturer. Assigned as Oracle Technical consultant to customize, design ,implement and support the Oracle Purchasing and Inventory application interfaces. This project includes the development of Interfaces and providing support to the existing interfaces.

Responsibilities:

• Gathered requirements for various interfaces and created technical design documents
• Developed a custom item interface program for loading legacy items information into Inventory Item Interface tables, by validating items according to their predefined item categories and category sets.
• Worked on Purchasing Documents Open Interface to import Standard PO into the PO Module using SQL Loader and PL/SQL.
• Developed custom Requisitions and Purchasing Documents Interface to import requisitions information from legacy system. Used SQL Loader, PL/SQL packages to validate the data before inserting into base tables.

Confidential

Senior Oracle Consultant

Project: Integrate Common Applications in Oracle/TIBCO Platform , WWL

Project Description: Integrate Common Applications INCA project is an Integration solution for WWL in Oracle 10g and TibcoPlatform. A common SOA based framework is developed with Oracle 10g database and Tibco platform.Oracle Stored procedures, packages, Functions are developed and are wrapped as Web services and are called from Tibco processes. Dynamic query processing is extensively used in this project. This integration application processes millions of transactions into the application and dynamically calls the business logic components in the form of packages and procedures.

Responsibilities:

Developed Oracle job process with high performance capabilities which serves as reusable framework components

• Did Performance tuning by gathering stats pack,Tuning Job Parameters,indexing,Partitioning, memory management, Purging and Query level Performance Tuning using Explain plan
• Created Materialized views for transactional data replication from operation application to the Customs repository application
• Partitioning of the transactional tables holding millions of records for better performance
• Developed tables, Global temporary tables and created indexes, constraints, triggers, synonyms, database links, table spaces, roles etc in different schemas.
• Did the Oracle database table design and applied Normalization techniques
• Developed Packages, Triggers, Functions, tables, Scheduler jobs and handled patchCreation, deployments
• Distributed query performance tuning by using EXPLAIN PLAN and TKPROF Utilities
• Worked with SQL loader and control files to load the data in different schema tables.
• Developed Rollback plan and release plan for deliverables
• Developed Tibco BW processes using Tibco designer to process the data from Application and pushing to the subscribed application
• Developed Parsing programs using XMLTYPE and XMLTABLE function and XML file creation process using Oracle XML features like XMLELEMENT , XMLFOREST , XMLAGG
• Created Test case documents , design documents and Integration Specification Document creation

Environment: Oracle 10g, TOAD, SQL Plus, Tibco BusinessWorks, Tibco EMS, Tibco ADB, Microsoft VSS

Confidential

Sr Oracle Developer

Project: Oracle Advanced Queues Platform , WWL

Project Description: This is an Integration project for WWL using Oracle Advanced queues. The aim of this project is to eliminate the complexity and to decouple the applications from integration layer. The complex and dependent layers of integrations which were costly to maintain were migrated to a Queue based framework. This framework built in a publish/subscribe model , decoupled applications from Integration layer , provided more throughput, increased availability, prevented loss of data and eliminated transactional data persistence in Integration layer. Oracle AQ provided the message management functionalities to control the flow and asynchronous communication needed for application integration.

Responsibilities:

• Key person in designing and developing the new framework using Oracle Advanced Queues
• Did the Oracle database table design and applied Normalization techniques
• Designed the Advanced Queue Structures by analyzing the business data tables
• Created Oracle packages for pushing and pulling the data from/to the applications to the Oracle AQ's in each application.
• Created a common routing application using PL/SQL which will receive the messages from the AQ , identify the subscriber and its destination and then pushes the data to the corresponding destinations
• Carried out Proof of Concept POC and performance testing for AQ framework, with extensive analysis in oracle AQADM packages.
• Single handedly coded the complete framework ,meeting performance expectations
• Created scripts from database to connect to Oracle OJMS servers
• Gathered Stats pack and tuned time consuming Oracle jobs to meet the performance standards
• Created partitions and indexes for transactional tables for better performance of the database.
• Created purging plan and job frequency analysis for scheduler jobs in oracle.
• Mentored new members on technical and functional aspects of the application.
• Unit testing and performance testing for the new integration application and supporting UAT

Environment: Oracle 10g, TOAD, SQL Navigator, SQL Plus, Putty, Axway Integrator, Amtrix Datamapper, Microsoft VSS

Confidential

Oracle Developer

Project: Trading Partner Application TPA ,WWL

Project Description: Trading Partner Application TPA was developed to enable the Trading partners and Vendors of WWL to share documents, business transaction in standard method. This application used Oracle database's capabilities for handling business logic, temporary data storage, data enrichment and Integrator for mapping the data from one format to another and for creating a work flow.

Responsibilities:

• Developing Oracle stored procedures, packages, DBMS jobs and DBMS Scheduler jobs
• Used Ref cursors and Collections for accessing complex data resulted from joining of large number of tables and providing the result set to the application
• Created Pragma Autonomous procedures for running as separate transactions within the job sessions.
• Used controllable Exception Handling for the ease of debugging and displaying the error messages in the application.
• Created transactional tables with Primary key and Foreign key constraints to maintain data integrity
• Performance tuning of high resource utilizing oracle jobs/Integrations by proactive monitoring
• Developed Dynamic query execution using Execute Immediate
• Used ERwin Data Modeler ERwin software tool for data modeling and generated the table scripts
• Used advanced Bulk techniques FOR ALL, BULK COLLECT to improve performance.
• Experience of upgrading Oracle 9i to 10g by analyzing existing objects and testing
• Co ordination with release and other project teams for the planned releases.
• Mentoring production support team members on the newly developed integrations.
• Automation of painful tasks as part of continuous improvement program using Oracle stored procedures and UNIX scripts.

Environment: Oracle 10g/9i, TOAD, SQL Navigator, SQL Developer, SQL Plus, Putty, Win SCP, UNIX, Microsoft VSS

Confidential

Oracle PL/SQL Developer

Project: Global Integration Standardization and Streamlining GISS for WWL

Project Description:

• The GISS project is the standardization and streamlining of transactions processing across various applications of WWL. Using Oracle 9i database along with XIB and Amtrix servers, data is processed by batch processing from one application to another application .This solution resulted in phasing out point2point regional EDI interfaces of WWL and gave a more standard framework for building integrations.
• The data from the business application are mapped to another Common View database, where data will be temporarily stored. From the common view database, data will be pulled by various applications. An Oracle Job for each application will be responsible for push and pull of data.With Integrator, FTP, SFTP, DB and email protocols were used to share data across business and applications.

Responsibilities:

• Developed Oracle procedures for handling data manipulation , based on the business requirements of the EDI message
• Created database objects like tables, views, procedures, packages using Toad, SQL Plus.
• Used Bulk Collect features in PLSQL block of code for loading data from staging tables to main Transaction tables
• Wrote Packages, Stored procedures and functions for accessing, inserting, modifying and deleting data in the database from Customs Application.
• Preparing Detailed Technical Documents.
• Impact analysis for oracle objects in Integration during changes in Associated applications
• Created session based Global Temporary tables to hold transactional data
• Developed Row level and statement level Oracle triggers to handle event based message triggering
• Created Subscription rules for Content based routing of EDI transactions
• Created test plans and test cases for testing procedures, functions and triggers.
• Documentation of business requirements and design of EDI message

Environment: Oracle 9i, TOAD, SQL Navigator, SQL Developer, SQL Plus, Putty, WinSCP, Microsoft VSS