ETL Informatica Developer

Summary

• Over 7 years of IT experience in Data Warehousing and Business Intelligence domain.
• Extensively worked with large EDW systems in all functional areas including Automobile, Manufacturing, Consumer products, Order Management and Health Insurance.
• Strong working experience in Informatica Power Center (PWC) Data Integration Tools – Repository Manager, Designer, Workflow Manager, Workflow Monitor.
• Experience in Informatica Metadata and Repository Management, was directly responsible for the Extraction, Transformation and Loading of data from Multiple Sources to Data Warehouse.
• Experience in Star schema, Snowflake schema, Kimball and Inmon methodologies.
• Performed the data profiling and analysis making use of Informatica Data Explorer (IDE) and Informatica Data Quality (IDQ).
• Applied the concept of Change Data Capture (CDC) and imported the source from Legacy systems using Informatica Power Exchange (PWX).
• Strong experience working with different RDBMS like Oracle, SQL Server, Teradata, DB2 and with different file systems like Flat Files, COBOL VSAM files and XML Files both as Source and as well Target.
• Worked with different Teradata utilities like Tpump, Fload, Mload and BTEQ.
• Experience working with OLAP reporting tools like Business Objects and Cognos.
• Proven experience in Design, Construction and Maintenance of Data Marts.
• Experience in designing E-R diagrams, Logical and Physical database designs using Erwin and Visio.
• Experience in understanding the Business requirements and translating them into Detailed Design along with Technical specifications.
• Created the Unit Test Case document and performed various testings like Unit Testing (UT) and System Integration Testing (SIT).
• Created the Deployment Plan and made use of versioning tool PVCS.
• Hands on experience in all aspects of Software Development Life Cycle (SDLC).
• Excellent communication & interpersonal skills.

TECHNICAL SKILLS

• ETL Tools: Informatica PowerCenter (PWC) 8.x/7.x/6.x, Informatica Metadata Reporter, Informatica Power Exchange (PWX) 8.x, IDE, IDQ, DataStage, SSIS
• BI Tools: Business Objects 8.x/5.x, Cognos 8.x
• RDBMS: Oracle 10.x/9.x/8.x/7.x, MS SQL Server 2000/2005/2008, DB2 on OS/390, Teradata, Access
• Languages: HTML, XML, C, C++, C#, Java, .NET, VB.NET, ASP.NET
• Modeling Tools: ERwin 4.x/3.x, Visio
• O/s: HP- UNIX 11.0, Windows NT/2000/XP/7, IBM OS/390, DOS
• Tools: TOAD, Oracle SQL Developer, SQL Plus, Teradata SQL Assistant, Erwin3.x/4.x, Import/Export, SQL*Loader, Hummingbird Telnet, FTP, SPUFI, ENDEVOR, PVCS, Ticket Management, Tpump, Fload, Mload, BTEQ

EDUCATION:

• Bachelor of Engineering, Computer Science.

Professional Experience

Client- Confidential, Southfield, MI
Aug 2008 to Till Date
Senior Application Developer
Blue Care Network of Michigan and its subsidiaries have more than 640,000 members. Blue Care Network features award-winning disease management programs and the largest HMO network of physicians and hospitals in the state, with more than 4,500 primary care physicians, more than 14,000 specialists, every acute care hospital in Michigan and most of the state’s other leading hospitals. Blue Care Network, a nonprofit corporation, is the affiliated HMO of Blue Cross Blue Shield of Michigan and an independent licensee of the Blue Cross and Blue Shield Association.

Projects:
Enterprise Data Warehouse:
The Power MHS Data warehouse source system enables updating of field values that comprise the primary key either from the power source table or the DW staging table. It allows deletion of entire records. These issues have affected the data of many business processes that end up in the DW (Data Warehouse). To overcome this, an ETL data flow process was accomplished and they are,

• Extract from source to DW staging.
• Merge to do basic cleansing and transformations, primary key error checking.
• CR (Current Row) & TF (Transform) - Current Row maintenance in the staging area, data warehouse staging for load, foreign key lookups, duplicate checking, insert vs. update determination, etc.
• EDW (Enterprise Data Warehouse) - load from staging area into the final DW destination table. Includes processes to expire the old records and create new current row records.

PBM Data Mart: To build PBM Data Mart for Marketing & Sales department using ETL Informatica Power Center 8.6.1 and load data from Rx Track into a Data Mart.
Enrollment Data Mart: To build ENRL Data Mart for Membership operations using ETL Informatica Power Center 8.6.1 and load data from EDW into a Oracle Data Mart.

Project Responsibilities:

• Extensively used Informatica 8x ETL tool for data transfers from Legacy systems to Oracle warehouse.
• Profiled the data using Informatica Data Explorer (IDE) and performed Proof of Concept for Informatica Data Quality (IDQ).
• Worked closely with project management and assisted in ensuring timely and effective implementation.
• Created the test cases and assisted in UAT testing.
• Actively participated in code reviewing and assists to ensure developers follow the IHA standards. Provide quality reviews for the work of other team members when required.
• Work with other team members to transition application technical knowledge for cross-training.
• Communicate and coordinate vendor and third-party consultant interactions.
• Identify opportunities to improve processes and cost effectiveness.

Production Support Responsibilities:

• Worked as an ONCALL production specialist with primary and secondary duties.
• Analyzed and resolved the incidents raised by the application users on priority (low, medium and high) through Altiris Enterprise queues.
• Modified and implemented the existing applications mappings, workflows, design documents and test cases as per the incident raised by users and internal Data issues.
• Coordinated with Technical product administration and Operations team in implementation of production break-fix, production error and production maintenance issues.
• Involved and completed new production applications Knowledge transfer and application turn over process as a part of Application management services.

Environment: Informatica Power Center 9.1.0/8.6.1/8.x/7.1.3, Power Exchange 8.6.1, Data Explorer 5.0, Oracle 10g/9i, PL/SQL, Toad 10.5/9.5, Cognos 8.4, Windows XP pro and AIX UNIX, PVCS, Tidal, Altiris, Magic ticket management, SQL Server, Teradata SQL Assistant, Teradata external loaders (Tpump, Mload).

Client- Confidential, Des Moines, IA
Apr 2007 to Jul 2008
Senior ETL Consultant

The Project was aimed to leverage technology to further automate Risk Evaluation and Policy Issuance with the goal of improving operational efficiencies, compress policy issuance time service and reduce per policy expenses. The Enterprise Claims System was to redesign the current Claims Data Mart Model to ensure application and guarantor reporting is available to monitor the Claims Premier Service Program.

Responsibilities:

• Worked with the Business analysts for requirements gathering, Business needs analysis, testing, and project coordination.
• Data modeling by ER diagrams and process modeling using data flow diagram in the design process.
• As a lead devised the rerun and load strategy.
• Mentor design/best practices to junior Informatica developers.
• Create architecture standards for Informatica usage.
• Created Preliminary and Detailed Technical Specifications for the data migration programs, as
  well as the necessary test plans used to ensure the successful execution of the data loading processes
• Created a dimensional access layer for members, groups, clients and products.
• Created different source definitions and Target definitions to extract data from flat files and
  relational tables.
• Workedwith database connections, SQL joins loops, aliases, views, and complex conditions.
• Involved in designing the ETL data load.
• Extensively involved in Unit, Integration and System testing and responsible for fixing the Program defects.
• Analyzed all the critical and High priority defects and fully responsible for fixing it.

Environment: Informatica PowerCenter7.1.3/6.1, Teradata, Oracle9i, Informatica Metadata Reporter, PL/SQL, SQL*LOADER, Oracle 9i, Oracle Enterprise manager, Toad, Sun Solaris, Unix Shell Scripting, Teradata SQL Assistant, Teradata external loaders, Windows-NT, IBM OS/390, MVS, DB2, VSAM, JCL.

Client- Confidential, Chicago, IL
Apr 2006 to Mar 2007
ETL Developer

Barclays Capital’s culture emphasizes teamwork and a collaborative approach. As a meritocracy, Barclays Capital recognizes and rewards talent and innovation, rather than position and tenure, while encouraging training and development among its staff. Barclays Capital applies an intellectual rigor to its strategy and day-to-day management, questioning each decision on the basis of its value to clients and shareholders.

Responsibilities:

• Understanding the reporting requirement and the dimensional data-marts.
• Documenting every ETL mapping specification. Preparing documentation for source analysis & ETL design, which are required prior to development.
• Providing input for changes in the Transparency Data Mart. Performing data analysis and working with DBA in designing tables in the Data Marts based on reporting needs.
• Involved in extraction, cleansing and loading of data into DB2 UDB database from flat files and DB2 tables.
• Identified anti-fraud, money laundering transactions making use of Actimize detection software.
• Identifying the sequence in which the sessions were to be executed and coming up with the workflow layout.
• Developed mappings in Informatica to load the data from various data sources into the Data Marts, using different transformations like Joiner, Aggregator, Update Strategy, Rank, Router, Lookup, Sequence Generator, Filter, Sorter and Source Qualifier.
• Writing stored procedures to perform certain unique tasks that are complex to be implemented within Informatica Power Center.
• Performing code reviews & testing the ETL code that was modified in connection with this conversion process.
• Performing ETL & database code migrations across environments.

Environment: Informatica PowerCenter7.1.2/6.1.3, Teradata, Oracle9i, Informatica Metadata Reporter, PL/SQL, SQL*LOADER, Oracle 9i, Oracle Enterprise manager, Toad, Sun Solaris, Unix Shell Scripting, Teradata SQL Assistant, Teradata external loaders, Actimize, Windows-NT, IBM OS/390, MVS, DB2, VSAM, JCL.

Client- Confidential, Chicago, IL
Aug 2005 – Mar 2006
Informatica Developer

Sales Reporting:
Sales Reporting project in GE Medical is to provide a one-stop shop for the sales personnel to find a key data needed to perform analytical and detailed reporting.
4U enterprise data warehouse will provide the key element for the areas UPIN, Renewals and Licensing. Data will organized and optimized in a EWD that is accessed by both power users for ad-hoc reporting and sales team for a development of sales reporting website reports.

Responsibilities:

• Analyzed the Source System and involved in designing the ETL data load.
• Developed the ETL Mappings with Complex Transformations.
• Developed joiner transformation for extracting data from multiple sources
• Created the Data Validation Scripts For Validating Multiple Data Loads
• Created detailed Reports that lists out the customer information and the conduct details for all File connect and Renewal Customers
• Responsible for the Migration execution process for the Major & Minor events
• Interacted with the Development and QA Team for the Release operations based on the Migration Documents.
• 24/7 Release support

Environment:Informatica PowerCenter7.1, Business Objects 5.1, Oracle9i, DB2 UDB v7.x, Merant VM 6.7, Remedy, ERwin 4.1, PL/SQL, SQL*LOADER, Oracle Enterprise manager, Toad, Sun Solaris, Windows-NT.

Client- Confidential, Northbrook, IL
Oct 2004 - Jul 2005
Informatica Developer

This project basically deals with various management activities of Allstate Auto Insurance policies within the organization and assists them by providing various interfaces and reports to make their decision making process smoothly. The project involved in creating Corporate Data Warehouse and migrating data from the OLTP systems to the Corporate Data Warehouse. Informatica was used as an ETL tool for extracting the Data from various sources running on Oracle, DB2, MS SQL Server databases, Flat files and load into Oracle Data Warehouse. This project involves planning, development, implementation, maintenance and supporting ETL applications of the corporate wide Data warehouse.

Responsibilities:

• Worked with the Business analysts for requirements gathering, business needs analysis, testing, and project coordination
• Designed and developed multiple Dimension and Fact tables
• Created Technical Specifications for the data migration programs, as well as the necessary test plans used to ensure the successful execution of the data loading processes
• Developed data Mappings between source systems and warehouse components using Mapping Designer
• Created different target definitions using warehouse designer of PowerCenter
• Worked with database connections, SQL joins loops, aliases, views, and aggregate conditions
• Developed joiner transformation for extracting data from multiple sources
• Implemented various transformations like Expression, Lookup, Sequence generator, and Source Qualifier, Rank, Aggregator etc.

Environment:Informatica PowerCenter 5.1/PowerMart 5.1, ORACLE 8, DB2 V7.1, ERwin 3.5, and UNIX (HP- UX 11.0), MS SQL Server, Business objects.