We provide IT Staff Augmentation Services!

Etl Architect Resume Profile

SUMMARY

  • Above 14 years of experience using Informatica Power Center toolset.
  • Experience using Informatica Developer IDQ Big Data Edition.
  • Experienced ETL Architect/Technical Team Lead.
  • Experience in Providing Informatica Training to End Users.
  • Experienced working in Oracle SQL Server DB2 Sybase Hadoop/Hive Green Plum.
  • Informatica Certified Developer.
  • Experienced in Integration Datawarehousing and MDM projects.
  • Highly Experienced in Performance Tuning of Slow running jobs.
  • Expertise in User Requirement gathering.
  • Strong knowledge of Data Warehousing concepts.
  • Experience in all phases of Software Development Life Cycle.
  • Excellent communication skills and great Team Player.
  • Excellent work experience with ANSI X12 HIPAA standards and HL7 messages.
  • Active Member of Informatica Support groups in helping other young professionals.
  • Sound knowledge in UNIX shell scripting and Automation of ETL processes.

PROJECT EXPERIENCE

Confidential

ETL Architect/Lead

Domain Health Care

Well Care Tampa FL

The Well Care Data Lake project integrates and migrates the existing data warehouse/legacy systems into Big Data/Green Plum for downstream reporting purposes. Informatica Developer with Big Data edition was used to access data from Hive Databases massage it and load into Green Plum. Hadoop/ Hive Hue Query Editor File Browser Ambari Job Browser Oozie Editor and Dashboard were used actively on a day to day basis along with Informatica Developer BDE to achieve the projects objectives.

Responsibilities

  • Designed and Implemented the ETL Architecture and Process flow for Data Lake project.
  • Documented the Technical Design/Solution Overview related to ETL architecture.
  • Actively Developed Mappings in Informatica Developer to load into Big Data and GreenPlum.
  • Successfully lead a team of ETL Developers to achieve the project objectives.
  • Actively worked on Member Call-SLA MDM Cap-Expenses Auth Tracks related to project.
  • Actively resolved Informatica/Big Data limitations in Informatica Developer.
  • Actively used Hue to access Beehive HCatalog File Browser and Ambari on Hadoop.
  • Actively Used Informatica Power center when needed to achieve the projects objectives.
  • Developed Unit test cases and successfully documented them as per client s templates.
  • Actively resolved all performance issues arising out of Big Data Hive loads.
  • Developed IDQ mapplets for code reusability.
  • Parameterized connections to Hadoop within Informatica Developer.
  • Developed the standards document for using Informatica Developer with Hadoop.
  • Environment Oracle 11g UNIX Informatica Developer BDE Hive Hadoop Hue Green Plum Autosys Tableau Informatica Power Center 9.x.

Confidential

ETL Architect/Lead

Domain Health Care

Sunovion Pharma Marlborough MA

A Master Data Management solution MDM is comprised of the set of technologies and processes aimed at creating and maintaining an authoritative reliable sustainable accurate and secure data environment. The end state master data management environment for Sunovion for this project was for the Health Care Professional and Health Care Organization domains. The solution is meant to serve as a common customer master for Sunovion s Commercial Data Management organization.

Responsibilities

  • Designed and Implemented the ETL Architecture and Process flow for entire MDM project.
  • Documented the Technical Design/Solution Overview related to ETL architecture.
  • Successfully lead a team of ETL Developers to achieve the project objectives.
  • Designed the ETL source flow to maximize parallelism while loading into MDM database.
  • Developed the Data Model for Staging/Loading/IDQ areas and implemented the DDL.
  • Used Informatica Power Center/IDQ/Oracle/Unix tools on a daily basis for development.
  • Worked with DBA/Data Analyst/QA/Business teams to achieve project objectives.
  • Conducted Code Review meetings and Bug Tracker meetings.
  • Implemented Invoking Informatica IDQ and MDM from within Informatica Power Center.
  • Designed and Implemented Error Handling/Reporting in ETL process.
  • Designed and Developed Informatica Mappings to load the MDM Party Model.
  • Implemented the MD5 algorithm in Informatica to compensate lack of keys in target tables.
  • Designed and developed reporting extracts to be developed out of the MDM database making use of the Golden Record.
  • Successfully resolved all ETL Performance issues to ensure all the processes completed within timeframes agreed.
  • Developed Informatica Developer Mappings and used Data Quality Mapplets like Address Doctor /Validator to validate data.
  • Environment Informatica Power Center 9.x Informatica IDQ Informatica MDM Oracle 11g Toad UNIX.

Confidential

ETL Lead

Domain State Department Health Insurance

As the centerpiece of the Patient Protection and Affordable Care Act of 2010 ACA State-Based Health Insurance Exchanges offer the promise of creating a one-stop insurance marketplace where individuals and small businesses can shop and enroll in partially subsidized or non-subsidized insurance based on a side-by-side comparison. To coordinate the Commonwealth s effort to replace the current MA-21 eligibility system and Health Connector Massachusetts current health insurance exchange with a new Health Insurance Exchange/Integrated Eligibility System HIX/IES Massachusetts has developed a single project approach to coordinate development procurement and implementation of HIX and IES technology design and implementation.

Responsibilities

  • Designed and Implemented the ETL Architecture and Process flow for HIX Conversion.
  • Successfully lead a team of On Shore/Off Shore Developers to achieve the project objectives.
  • Extensively used Informatica/Oracle/Unix tools on a daily basis for development.
  • Worked with DBA/Data Analyst/QA/Business teams to achieve project objectives.
  • Developed Technical Specification Documents and conducted Code Review meetings.
  • Worked on Best Practices Code Promotions and Version Control Mechanism.
  • Successfully resolved legacy data issues JIRA issues and performance issues on a timely basis.
  • Created Materialized views Indexes function based Indexes temporary tables Partitioning options as needed to improve performance of ETL code.
  • Designed and Developed Informatica Mappings to load different Subject areas in HIX.
  • Implemented Error logging mechanism in Informatica making use of Repository tables.
  • Developed Reports to validate data which was been Converted.
  • Developed Cross Walk tables to store keys of legacy and new systems for audit purpose.
  • Implemented the MD5 algorithm in Informatica to compensate lack of keys in target tables.
  • Implemented Overriding the DB Isolation level in Informatica to avoid Read Deadlock s.
  • Promoted Informatica code created backups roles folders in the Repository as needed.
  • Environment Informatica Power Center 9.x Power Exchange Oracle 11g SVN Toad JIRA UNIX.

confidential

Technical Team Lead

Domain Publishing

confidential was founded in 1994 as a not-for-profit wholly-owned subsidiary of Harvard University reporting into confidential School. Their mission is to improve the practice of management in a changing world. Exchange of data Feeds between HBP and various universities corporations clients across the world would be developed and the data will flow across using ETL processes. Higher Education Corporate Learning and confidential Review Group were 3 of the major divisions using ETL Architecture.

Responsibilities

  • Worked closely with the Business Users to gather and deliver requirements.
  • Extensively used Informatica as the ETL tool on a daily basis for development.
  • Developed Technical Specification Documents and conducted Code Review meetings.
  • Successfully Lead an Off shore team of Developers to implement the project objectives.
  • Developed and Supported Complex Mappings across different business units.
  • Performed Optimization at both Mapping Level and Session Level.
  • Worked closely with the QA team for QA handoff and coordinate QA testing.
  • Worked on the Promotion of ETL/Unix code across all environments.
  • Involved in Unit/SIT/QA/Volume Testing of code developed.
  • Worked with IT operations to setup SFTP processes between HBP and external Vendors.
  • Followed Informatica recommended Standards and Best Practices.
  • Actively Used JIRA to document/update/track the projects working on.
  • Created DDR IBOTS Dashboards Reports using Anwsers BI Delivers in OBIEE.
  • Worked on making changes to existing Reports in OBIEE as per business needs.

Environment Informatica Power Center 9.x Oracle 11i OBIEE JIRA Toad UNIX.

confidential

Technical Team Lead/Senior Informatica Developer

Domain Health Care

Sorian Clinical/Financials Conversion Project

confidential Care North America is a wholly owned subsidiary of Fresenius Medical Care AG Co. Through their dialysis services entity Fresenius Medical Services operates more than 1 700 outpatient dialysis clinics in the U.S. Fresenius was replacing its legacy systems with products from Siemens. Informatica was chosen as the ETL tool for the conversion of data at its IT Operations Center in Lexington MA.

Responsibilities

  • Actively interacted with the Business Analysts to gather and deliver requirements.
  • Successfully lead a team of Developers to implement the project objectives.
  • Developed Functional and Technical Specifications.
  • Active Member of P3 Results Soarian to Knowledge Center Projects.
  • Experience working on Health Care Orders and Charges Projects.
  • Extensively used Informatica as the ETL tool.
  • Developed and Supported 360 Staging/SCD/Complex Mappings in 3 different Projects.
  • Involved in Unit/SIT/Volume Testing of code developed.
  • Implemented Error Processing for Informatica Mappings and Workflows.
  • Developed extensive Recovery Strategies for Informatica Mappings.
  • Developed plan and successfully implemented Retroactive data fixes.
  • Followed Informatica recommended Standards and Best Practices.
  • Performed Optimization at both Mapping Level and Session Level.
  • Improved query performance by creating indexes explain plan use and analyzing tables.
  • Developed an Informatica Audit Process which compared differences between Database and Informatica Repositories and generated Reports.
  • Implemented Real time processing logic using both Informatica and Unix Methodology.
  • Created Autosys jil scripts to automate the Informatica Workflows.
  • Created UNIX scripts to perform audits manipulations of flat files to perform rcp of files across servers and other tasks.
  • Used Remedy/HP Quality Control Center to Review/Fix/Close Bugs.
  • Assumed additional responsibilities to achieve Targets as and when required.
  • Prepared/Reviewed/Verified Release Plans to migrate Objects from DEV to QA/PS/PROD.
  • Effectively archived documented all Development work done.
  • Performed Code Reviews of all work done by developers on a weekly basis.
  • Implemented Snapshot Isolation feature in SQL SERVER at Informatica connection level.
  • Implemented Deadlock Retry Logic in Informatica to overcome database deadlocks.
  • Interacted with Informatica support/Offshore teams as and when needed.

Environment Informatica Power Center 8.x Oracle 10g Sql Server 2005 Remedy HP Quality Center Autosys Toad Unix CMS.

confidential

Technical Team Lead/Performance Tuning Consultant

FEDEX Smart Post Project

Confidential has embarked on the journey of building a datawarehouse for all its reporting needs and also to move its data from sybase to oracle.The current Datawarehouse was being migrated to oracle and the role also required to maintain the existing systems in production.Data coming in from flat files Sybase oracle Teradata had to be sorted cleaned and migrated according to business rules. Informatica was the ETL tool used to select massage and load data into the new enterprise system.

Responsibilities

  • Extensively used Informatica Client tools-Designer Workflow Manager Workflow Monitor and Repository Manager.
  • Involved in creating Naming Standards Best Practices for ETL development.
  • Extensively used Informatica Power Center 8.1.1 to extract data from various sources which included Flat files Sybase Oracle MS-Access and XML.
  • Worked extensively on Informatica transformations like Source Qualifier Expression Filter Router Aggregator Lookup Update Strategy Stored Procedure Sequence generator Joiner Union Normalizer and Transaction Control.
  • Involved in creating Mappings using Mapping and Session Parameters in the mappings and workflows to eliminate hard coding wherever possible.
  • Created Active and Passive Mapplets wherever necessary to facilitate reusability of business logic.
  • Created Reusable Transformations Worklets and made use of the Shared Folder Concept using shortcuts wherever possible to avoid redundancy.
  • Used Debugger making use of Breakpoints to monitor data movement and troubleshoot the mappings.
  • Developed Test cases for Unit Testing of the Mappings and also was involved in the Integration Testing.
  • Implemented Error Processing for Informatica Mappings and Workflows.
  • Followed Informatica recommended Standards and Best Practices to improve Performance of ETL mappings.
  • Performed Optimization at both the Mapping Level and Session Level.
  • Monitored and improved query performance by creating indexes execution plan and analyzing tables.
  • Reviewed the lock mechanism on the database and implemented Dirty Reads as and when possible to avoid deadlocks by configuring connection environment sql.
  • Improved the performance of mappings by implementing Informatica partitioning.
  • Implemented Dynamic Parameter Files in Informatica to Improve Performance.
  • Wrote UNIX scripts to perform several operations on flat files.
  • Effectively documented all Development work done.

Environment Informatica Power Center 7.1.2 Sybase 12 Oracle 9i MS-Access CVS/SVS 1.4.2 UNIX.

confidential

Senior ETL Developer/Administrator

Insurance Sector

2nd Generation Business Systems Project

  • A large Portion of the Trust s long term goals address improving the speed to market for new plans flexibility in health and dental plans and data quality.
  • The 2nd Generation Business Systems Project was aimed mainly to convert the existing legacy data into a new Enterprise System. QNXT the new enterprise system which was purchased from QCSI Inc was designed to meet most of the WEA Trust needs. Those business process requirements that QNXT does not satisfy were supplemented with additional systems and custom designed and developed software. Data was coming in from Multiple Sources which included Sql Server DB2 Flat Files Ms-Access Database and XML. Informatica was the ETL tool used to select massage and load data into the new enterprise system.

Responsibilities

  • Actively interacted with the Business Analysts and Business Users to gather verify and validate various Business Requirements.
  • Extensively used Informatica Client tools-Designer Workflow Manager Workflow Monitor and Repository Manager.
  • Involved in creating Naming Standards Best Practices for ETL development.
  • Involved in developing the Deployment Strategy for Deployment of Source Code between the Various Repositories Dev QA and Prod .
  • Extensively used Informatica Power Center 7.1.2 to extract data from various sources which included Flat files DB2 SQL Server Ms-Access and XML.
  • Worked extensively on Informatica transformations like Source Qualifier Expression Filter Router Aggregator Lookup Update Strategy Stored Procedure Sequence generator Joiner Union and Normalizer.
  • Involved in creating Mappings using Mapping and Session Parameters in the mappings and workflows to eliminate hard coding wherever possible.
  • Created Active and Passive Mapplets wherever necessary to facilitate reusability of business logic.
  • Created Reusable Transformations Worklets and made use of the Shared Folder Concept using shortcuts wherever possible to avoid redundancy.
  • Involved in creating Static and Dynamic Parameter Files.
  • Followed Informatica recommended Standards and Best Practices to improve Performance of ETL mappings.
  • Performed Optimization at both the Mapping Level and Session Level.
  • Used Debugger making use of Breakpoints to monitor data movement and troubleshoot the mappings.
  • Developed Test cases for Unit Testing of the Mappings and also was involved in the Integration Testing.
  • Implemented Error Processing for Informatica Mappings and Workflows.
  • Effectively documented all Development work done.
  • Used Enterprise Manager Query Analyzer to access database objects and execute queries against them.
  • Monitored and improved query performance by creating indexes execution plan and analyzing tables.
  • Reviewing the ETL process design ETL design documents with the business analysts end users data quality team and enterprise test team.
  • Wrote Windows Batch Jobs to perform several operations on text files.
  • Involved in the Design Process of ETL mappings workflows from Legacy to Staging.
  • Created Automated Tests using Ruby Scripting Language and by making use of the PMCMD command.
  • Experience using CVS/Subversion as the Versioning tools for Informatica Objects.
  • Used CC.NET as Centralized Build for Continuous Integration and for deployment to various environments.
  • Experience using CLEAR QUEST to file resolve closing Defects.
  • Experience using JIRA TWIKI to log project level issues and to view documentation.
  • Experience in Informatica Administration work.
  • Provided Training to end users in Informatica.
  • Estimated Time required developing Mapping Workflow Test Cases based on BA requirements.

Environment Informatica Power Center 7.1.2 Microsoft SQL Server 2000 Query Analyzer 8.0 Enterprise Manager 8.0 MS-Access DB2 AS400 CVS/SVS 1.4.2 RUBY Windows XP CC.NET 1.1.0 Clear Quest JIRA TWIKI

confidential

Informatica Developer

Strategic Reinsurance Automation Project

  • The objective of the Strategic Automation project is to replace the existing way that Reinsurance is processed by a new Enterprise System. Enterprise system will provide all necessary functions of the retiring systems or superseded by improved functionality. System also is interfaced out to various areas within the organization downstream and outside business partners.
  • The enterprise solution consists of the following
  • ProCede Sherwood International software to support detection/calculation/billing/accounts receivable for ceded reinsurance
  • Reinsurance Data Repository Application Data Repository to support interface requirements management reporting and all required customer functions not supported by ProCede Application .

Responsibilities

  • Responsible to Convert all data Direct Assumed and Ceded necessary to support ceded reinsurance processing billing accounts receivable/payable recording reporting and Regulatory reporting. Data converted into the ProCede database
  • Responsible for staging the data from Legacy Systems REINS SEAS MIS PACER CETS LOC Letter of Credit and an assortment of PC desktop applications and manual procedures
  • Developed data maps to extract data from mainframe systems using Informatica mainframe connectivity tool Detail Navigator.
  • Extensively used Informatica PowerCenter 6.2 to extract data from flat files Oracle to Oracle.
  • Developed and implemented Process Control for each step involved in Data extraction.
  • Worked extensively on Informatica transformations like Source Qualifier Expression Filter Router Aggregator Lookup Update Strategy Stored Procedure Sequence generator and joiner transformations.
  • Created multiple mappings using mapping and session Parameters Variables Reusable Transformations and Mapplets.
  • Followed Informatica recommended Standards and Best Practices to improve Performance of ETL mappings.
  • Studied Reject Files and Session Log files to correct errors in mappings and sessions.
  • Used Debugger making use of Breakpoints to monitor data movement and troubleshoot the mappings.
  • Implemented Error Processing in all the Mappings to detect and keep a log of Errors which were subsequently corrected after analysis.
  • Created and scheduled sessions and workflows based on demand run on time run only once using Informatica workflow manager.
  • Used TOAD PL/SQL Developer to develop and debug oracle PL/SQL Functions Procedures and Packages.
  • Extensively Used SQL queries to check validity of data.
  • Monitored Queries using Explain Plan and improved query performance by creating Views Indexes Regular and Function based and Hints.
  • Understood and Executed Process Control for all the Mappings in Informatica.
  • Implemented Error Processing for Informatica Mappings and Oracle Procedures.
  • Responsible for loading extracted data into ProCede system using remote services and batch processes.
  • Wrote Unix Shell Scripts to perform several operations on text files and to run multiple database sessions in parallel.
  • Performed activities including execution of test plans with test live data and design of exception handling strategy. Completed documentation in relation to detailed work plans and mapping documents.
  • Member of the Production Support Team now Providing quick solutions to real time run problems looking to improve performance on a ongoing basis.
  • Used PVCS version control and PVCS Tracker in Production Support.
  • Used Autosys to Monitor Jobs in Production Support.

Environment Informatica PowerCenter 6.2.1 Oracle 9i DETAIL Navigator v4.2.0 TOAD 7.3 PL/SQL Developer Unix UltraEdit v9.00 Host Presenter v5.00.1 ProCede 8.4.1 Windows NT AutoSys 2.0 IBM Mainframe PVCS version Control PVCS tracker.

confidential

Informatica Developer

Enterprise Data Warehouse

America Online data warehouse is developed for Entire Corporation of US. The main objective of customer Data Mart was to help migrate the organization from product-centric orientation to one that driven by customer needs. It provides a standardized means for accessing customer profiles scattered across the heterogeneous systems in different states. Transactional interaction demographic behavioral and self-provided profile data were the sources. Data was coming in from Flat Files Mainframe data Relational tables and was transformed using Informatica and then loaded into the target which is in Oracle.

Responsibilities

  • In the process of development worked with Aggregator Lookup Filter Expression Stored Procedure and Sequence Generator transformations.
  • Created reusable transformations and Mapplets in the designer using transformation developer and Mapplet designer tools.
  • Developed PL/SQL stored procedures to perform complex calculations in the database.
  • Created and scheduled Sessions based on demand run on time and run only once using Informatica Server Manager.
  • Used Parameter files to pass mapping and session parameters to the session.
  • Monitored Workflows and Sessions using Server Manager.
  • Involved in migrating the mappings and workflows from Development to Testing and then to Production environments.
  • Involved in writing UNIX shell scripts to work with flat files and to create pre and post session commands.
  • Using the debugger in Informatica tested the data in the warehouse.
  • Efficient Documentation was done for all mappings and workflows.
  • Environment Oracle 7.x Flat Files Informatica PowerCenter 5.1 SQL PLSQL.

SKILLS

Data Warehousing

InformaticaPowerCenter DETAILNavig Cognos OBIEE

Database

Oracle DB2 SQL Server /2005 MS Access Teradata

Tools

Toad Iseries PL/SQL Developer Erwin UltraEdit Autosys Controlm

Software

Microsoft Office Suite Visio Clearcase

Operating Systems

Unix Windows

Hire Now