Sr. Etl Developer - Informatica Resume
New York, NY
PROFESSIONAL SUMMARY:
- 7+ years of extensive professionalexperience in Information Technology with strong background in the areas of ETL Design and ETL development in Data Warehousing using Informatica PowerCenter.
- Extensively expertise in working with all major components of Informatica PowerCenter like Repository Manager, Designer, Workflow Manager, and Workflow Monitor.
- Extensively expertise in using various transformations like Joiner, Lookup (Connected and Unconnected), Source Qualifier, Router, Filter, Expression, Rank, Normalizer, Sequence Generator, Transaction Control, Union, Aggregator, Update Strategy, Java, XML, Normalizer.
- Experience in building operational data store (ODS) and Enterprise Data Warehouse with the knowledge of logical and physical Dimensional modeling concept Star/Snowflakes Schema, fact and dimensiontable and proficient in identification of Facts, measures and dimensions.
- Expertise in installing and configuring Informatica server and client component in different environment as well as expertise in migration of repository server and its data from older version to newer version.
- Expertise in handling performance tuning of Oracle using SQL trace, SQL Plan, Oracle partitioning, join types and various indexes.
- Experience in data warehousing techniques for Data Analysis, Data Profiling, Data Cleansing, Transformation, Consolidation, Integration, Data Import, Data Export using various source (Oracle, MS SQL Server, DB2, Teradata, XML and Flat files) and target.
- Extensively worked with PL/SQL procedures, functions, packages, triggers, cursors and exception handling.
- Experienced in handling simple to complex mappings performance and optimization issues involved with complex mappings, sessions and workflows.
- Expertise in handling error, exception and performance issue in Informatica.
- Experience in Unix Shell Scripting for data manipulation and scheduling tasks.
- Hands on experience with third party scheduling tools like Autosys.
- Expertise in production support and handling production issues.
- Strong Knowledge in ETL tools IBM Ascential Datastage and SQL Server Integration Services (SSIS).
- Hand on experience with the Informatica PowerExchange.
- Experience in various stages of System Development Life Cycle(SDLC) and its approaches like Waterfall, Spiral, Agile, and Prototyping Model.
- Expertise in Health care domain like Medicare, Medicaid and Insurances compliance within HIPPA regulation and requirement.
- Experience with the healthcare data in HIPPA ANSI X12, 4010, 5010 formats including NDC, DRG, CPT, NCPDP, NSF code, ICD 10, ICD 9, 997,837,834,835,277,271,270.
- Knowledge about the Facets, EDI and ITS.
- Hand on experience with Mainframe JCL, COBOL and UNIX System.
- Strong understanding of languages like XML, VB Script, SQL, T-SQL, PL/SQL, C, C++, Java, Flash.
- Hand on experience with reporting tools like Business Objects, Micro Strategy.
- Excellent team player with the strong interpersonal communication, analytical skills, ability to work on multiple tasks, Quick learner, meets the strict deadlines as well as fast-paced and demanding environment.
TECHNICAL SKILLS:
Data Warehousing:Informatica PowerCenter and PowerExchange (9.x/8.x/7.x),
ETL Tool:IBM Ascential DataStage, Microsoft SQL Server Integration Services (SSIS).Dimensional Data
Modeling:Dimensional Data Modeling, Data Modeling, Star Schema, Snow Flake Schema, FACT and Dimensions Tables, Physical and Logical Data Modeling.Data Modeling
Tools:E/R Studio, MS Visio, Erwin, Oracle DesignerSoftware Development
Process Models:Waterfall, Agile, Spiral, Prototype ModelBI Tools:Business Objects XIII/XII/6.5/6.1, Micro strategy, SSRS,Databases:Oracle 11g/10g/9i/8i, MS SQL Server 2005/2008, Teradata, IBM DB2, Sybase, MS Access.Database Tools:SQL * Plus, SQL * Loader, SQL Developer, Toad.Languages:HTML, XML, VB Script, SQL, T-SQL, PL/SQL, Unix Shell Scripting, C, C++, Java, PHP, FlashEnvironment:Windows 2008/2003/2000NT/Windows 7/XP/Vista/, AIX 5L/5.2, HP-UX, Sun Solaris/Unix, Linux
PROFESSIONAL EXPERIENCE:
Confidential, New York, NY March 10 – Current
Sr. ETL Developer - Informatica
Confidential is the nation’s largest and oldest supplier of independent, network-based cost management solutions with almost 900,000 healthcare providers under contract. It’s a claim processing company with more than 110 millions claims processed every year which was founded in 1980.
The project was mainly focused in capturing the changes. It has four primary source systems TTC, HMA, Beech and NCN that process the claims. The source systems were the part of separate companies which by M&A through years became part of the same organization on the other hand they have Viant for their transactional process system which was acquired by Confidential in year 2010. Each source systems provide huge volume of data with different dataset they are Provider, Claims, Clients, Network which is the exact replica of the Viant side. The major job involves is to integrate the different dataset from different sources then to perform cleansing, comforting and transforming the data to the staging area then loading the data into the Data Mart (Data Warehouse) in such away so that the reporting team can easily extract and identify master claim and the child claim to capture the changes (CDC).
Responsibilities:
- Worked with architect, business managers, Golden Gate DBA group to understand the requirement and source system in order to prepared design documents specifying the various ETL approaches, pros and cons of the different approaches with suggestion of the best approach.
- Involved in the designing of Dimensional Model and created Star Schema using E/R studio.
- Extensively worked with Data Analyst and Data Modeler to design and to understand the structures of dimensions and fact tables and Technical Specification Document.
- Worked extensively on Informatica designer to design a robust end-to-end ETL process involving complex transformation like Source Qualifier, Lookup, Update Strategy, Router, Aggregator, Sequence Generator, Filter, Expression, Stored Procedure, External Procedure, Transactional Control for the efficient extraction, transformation and loading of the data to the staging and then to the Data Mart (Data Warehouse) checking the complex logics for computing the facts.
- Worked extensively on the HIPPA 4010 transactions and HIPPA 5010 transactions as the source data like 834,835, 277,276 and more.
- Involved in analyzing the ICD 9 and ICD 10 for the data mapping from ICD 9 - ICD 10 and ICD 10 - ICD 9 in source and target level.
- Involved in analyzing different modules of facets system and EDI interfaces to understand the source system and source data.
- Extensively used the reusable transformation, mappings and codes using Mapplets for faster development and standardization.
- Used reusable Session for different level of workflows.
- Involved in Performance Tuning in Informatica for source, transformation, targets, mapping and session.
- Created variables and parameters files for the mapping and session so that it can migrate easily in different environment and database.
- Implemented Slowly Changing Dimensions Type-1, Type-2 approach for loading the target tables.
- Designed various tasks using Informatica workflow manager like session, command, email, event raise, event wait and so on.
- Design the architecture for production support in order to scheduling the tasks (without any manual intervention) using Unix Shell Scripting.
- Used Unix Command and Unix Shell Scripting to interact with the server and to move flat files and to load the files in the server.
- Responsible for file archival using Unix Shell Scripting.
- Involved in resolving the issues related with the migration of designs from Dev Server to Prod Server and also helped in resolving issue related with the production support of the Data Warehouse.
- Wrote complex SQL queries PL/SQL function, procedure, packages, cursor and triggers to retrieve the data form sources system and to count and validate the data and data set.
- Actively involved in Data Migration from source to target Database.
- Perform Unit testing by writing simple test scripts in the database and involved in integration testing and minus SQL script comparing from source to target.
- Interacting with User Groups/Corporate Testing Groups/Business People.
- Worked with Business Objects for generating ad-hoc report.
Environment: Informatica Power Center 8.6, Oracle 10g/11g RAC, E/R Studio XE2, SQL, PL/SQL, Toad 9.7, UNIX Shell Scripting, Business Objects XIII, Win Runner.
Confidential, Downers Grove, IL Jan 09 – Feb 10ETL Developer– InformaticaConfidential a diversified national managed Health Care Company providing a full range of risk and fee-based managed care products and services including HMO, PPO, POS, Medicare Advantage, Medicare Prescription Drug plans, Medicaid, group health care insurance, Individual health care insurance, Worker Compensation, and more serving more than 5 million members in all 50 state across the USA.The primary objective of the project was to build the data warehouse system integrating insured member/agent policy and claims information to satisfy the company’s need for fast financial report generation. The immediate objective was to develop a Data Mart to handle health insurance member, their policy need and claim. Extracted data from fragmented data sources across the company was transformed and loaded into target Data Marts.Responsibilities:
- Worked extensively in full System Development Life Cycle like participating in requirement gathering, business analysis, user meeting.
- Interacted with functional teams and users for finalizing the data models and reporting structures.
- Worked closely with Facets 4.48, 4.51 and different EDI transaction file like837,834, 835, 270, 271 to understand the source structure and the source data pattern.
- Involved in ICD-9 4010 data analyzing with data analyst to define the structure of dimension and facts.
- Involved on various HIPPA claims validation and verification process to understand the source data.
- Worked closely with the Business Analyst, Data Modeler and Database Architect during the design and development of ETL technical specification document.
- Used Informatica PowerCenter as an ETL to extract data from source like MS SQL Server, Flat files, Oracle, DB2 and Teradata and loaded to target.
- Extensively worked with the various client component of Informatica like Source Analyzer, Warehouse Designer, Mapping Designer, Mapplets Designer, Repository Manager, Workflow Manager,and Workflow Monitor.
- Extensively used different transformations like Lookup, Joiner, Aggregator, Filter, Sorter, Expression, Update Strategy, Source Qualifier, Rank, Router to create several mapping and mapplets.
- Implemented extracting, cleansing, transformation, integration and loading into Data marts using Informatica.
- Implemented Slowly Changing Dimension phenomenon Type-1, Type-2 using Informatica ETL mapping.
- Created mapplets and reusable session for the performance tuning.
- Implemented error-processing strategy to reprocess the error data and manage notification of error data to corresponding business team.
- Worked with Informatica workflow monitor in running and debugging its components and monitoring the resulting executable version
- Involved in fine-tuning of sources, targets, mappings and sessions for performance optimization.
- Extensively used complex SQL queries to unit test the system and to test the existing project also used to validate the data in data warehouse.
- Extensively used SQL and PL/SQL tuning techniques to improve the performance.
- Daily monitoring of the mappings that ran the day before and fixing the issues
- Involved in unit, system and end-to-end testing of the design. Worked with DBA and Reporting team to generate Materialized View of the data warehouse.
- Worked on call for production support.
Environment: Informatica PowerCenter 8.1.1, Oracle 10g, SQL Server 2005, flat files, DB2, Teradata, Erwin R7, SQL, PL/SQL, SQL*PLUS, Shell Scripting, Micro Strategy 8.0, Unix System.
Confidential, Phoenix, AZ Aug 07 – Dec 08
Informatica Developer/Consultant
Confidential is an American multinational financial services corporation with the strong and profitable private client’s franchise. The company is best known for its credit card, charge card and traveler’s check business.
The project was aimed to selectively extracting, relating, transforming the data from various online transactions processing (OLTP) applications with heterogeneous data sources like Oracle, DB2, SQL Server, Flat filesand other sources and loading them into the Oracle Data Warehouse through which OLAP server can perform Business Intelligence Analysis Services. The project also involved the tuning of existing project for the better performance, which enables to know the current and the future need of the customers and the organization.
Responsibilities:
- Extensively interact with the user and involved in determining the data needed to address business user’s analytical requirements and design data mart to support these analysis.
- Involved in handling and selecting heterogeneous data sources like Oracle, DB2, SQL server and Flat Files.
- Worked with Unified Modeling Design concept to design platform independent modeling.
- Worked extensively in translation of business requirements into data warehouse design and developed ETL logic based on the requirement using Informatica PowerCenter 8.1.
- Contributed in logical and physical design of dimensional modeling and created 3rd Normal Snowflakes Schema.
- Extensively worked with all the client components of Informatica like Repository Manger, Designer, Workflow Manger, workflow Monitor.
- Developed complex mappings to populate and incrementally load the source data to the staging area using joiner, sorter, connected lookup, router, filter, update strategy, expression using Informatica designer taking into consideration of maximize performance.
- Developed ETL design using various transformations like Source Qualifier, Aggregator, Sorter, Joiner, Lookup, Stored Procedure, Router, Filter, Transaction Control, Sequence Generator, Expression, JAVA, and XML as per necessity for source-to-target data mappings and to load the target table.
- Worked extensively with variables and parameters files in the mapping and in the session to pass the value and to control the environment and source, target information.
- Contributed in performance tuning of the existing project from source to target level and debugged invalid mappings using break points, testing the stored procedure, functions, sessions, and batches and checking the target data.
- Used Shell Script to handle flat files in the source level.
- Worked with store procedure and packages in PL/SQL and Unix Shell Scripting for automated execution of the job in the production environment.
- Wrote complex queries in SQL and PL/SQL.
- Used pmcmd commands to execute the tasks, populate parameter files, and Unix Shell Scripts for the automation of the design.
- Involved in various level of performance tuning in Informatica from mapping level to session and parallel processing.
- Involved in designing the complete Decision Support System using Micro Strategy by creating different types of reports for trend analysis using filters, conditions and calculations.
Environment: Informatica PowerCenter 8.1.1, Oracle 9i, Flat Files, DB2, MS SQL Server 2005, Erwin, Oracle Designer, PL/SQL, Unix Shell Scripting, Toad 7.0, pmcmd command script, Micro strategy 7.0, Business object, SSRS.
Confidential, Beaverton, OR March 06 – July 07
ETL Developer – InformaticaConfidential is one of the major publicly traded companies categorized under rubber and plastic footwear, which produce sportswear and equipment. It’s a world’s leading supplier of athletic shoes and apparel. Current estimate show this company has annual revenue of 19.01 billion USD and employs a staff of approximately 34,400.
The project is about the existing project demand planning Data Mart (DPDM), whichcontains Global ForecastAccuracyMeasurement System for Apparel, Footwear and Equipment which incompliance to meet changing trends of the Business User’s and Client. DPMD mainly consist of primary data extracted from NSC’s Demand Planning Repository (DRP). DRP is loaded twice a week Monday and Saturday once DRP is loaded once it loaded AutoSys triggered via FTP job and initiate DPDM’s batch. In this way data in DPMD is updated regularly.
Responsibilities:
- Worked extensively in the study of the existing project Demand planning Data Mart and involved in all the phases of reengineering System Development Life Cycle (SDLC) from requirement, design, development, testing, training to production support.
- Extensively interact with the Client, Business User’s and reporting Team for requirement gathering and assisted to design the Functional Requirement Document.
- Worked closely with DBA and Data Modeler to understand the structure of existing dimension, fact and schema and designed the new Dimensional Model data mart.
- Extensively involved in moving all the design, mapping and data from Prod environment to Dev environment to study the flaws of the existing mappings and designs.
- Created several mapping in the Informatica designer 7.5 using several transformations like Source Qualifier, Joiner, Aggregator, Rank, Expression, Sequence Generator, Lookup (Connected and Unconnected), Update Strategy, Transaction Control, Java, Normalizer, Filter, Router etc.
- Implement Slowly Changing Dimensions phenomenon using Informatica ETL mapping using Lookup.
- Performed various levels of data analysis, data cleansing, data profiling, and data validation using different transformation in Informatica Designer.
- Extensively worked with Informatica workflow manager and workflow monitor to schedule, run, debug and test the application on development and to obtain the performance statistics
- Developed new Data Warehouse Repository using Informatica Repository Manager by importing the source and target database schema. Backup and restore the existing repository.
- Worked on existing system source to target performance issue as well as implemented the various level of performance tuning in the new design and mapping.
- Analyzed the existing stored procedure written in PL/SQL and Shell Script and designed new stored procedure and Shell Script to handle the work flow and flat files in the source.
- Analyzed performance tuning of Oracle 9i to improve its performance.
- Scheduled the mapping and tasks using Autosys.
- Used SQL *Loader for moving bulk data from external files and prod environment into Oracle database.
- Unit tested all the maps using the debugger and queries to perform data validation.
- Involved in quality assurance of data, automation of processes, programming for data manipulation and graphics, validation of programming, and presentation of results.
Environment: Informatica 7.5, Oracle 9i, Erwin 5.0, SQL, PL/SQL, Toad 6.0, SQL*loader, Shell Scripting, Teradata, DB2, SSRS, Business Object. Autosys.
Confidential Jan 05 –Jan 06
Database DeveloperConfidential Communication is one of the renowned Internet Service Providers in Nepal. It is the first company that led the nation in introducing new and appropriate information technology by giving individuals and small business the ability to use the Internet as a tool to make their lives more productive and their business more profitable.
The project deals with testing of the database of for its integrity, validation and consistency. It deals with populating the database with the data and also I was responsible for the take over, maintenance and enhancement.Responsibilities:
- Created SQL queries and ran against the database
- Worked to design database, database Schema, Table, and Indexes.
- Wrote procedure, function to access data from the database.
- Created packages to hold the related procedure and function together.
- Wrote cursors in the procedure and function to return the multiple rows in the queries.
- Created triggers in order to maintain the system integrity and constraints.
- Tested SQL queries and PL/SQL procedures and Functions against database.
- Used indexes and hints to tune the performance of the queries.
- Participated in development and testing of the database.
Environment: Oracle 8i, SQL, PL/SQL, SQL* Plus, SQL Developer.