- 6 + years of IT experience in Analysis, Design, Development and Deployment of technical solutions to business requirements.
- Extensively worked on Data Warehouse Full Life Cycle Projects for Health Care, Financial, Retail and Insurance industries.
- 6 + years of Strong Data Warehousing ETL experience using Informatica Power Center 9.1/9.0/8.6/8.1/8.0/7.1 (Workflow Manager, Workflow Monitor, Source Analyzer, Warehouse Designer, Transformation Developer, Mapping Designer, Informatica Server, Repository Manager)
- Experience in all the phases of ETL such as Extraction, Cleaning, Transformation and Loading using Informatica.
- Experience in using Oracle 11g for performing ETL functions using stored procedures.
- Extensive experience and exposure on several areas of the data warehousing and Business including requirements gathering, data warehouse architecture design, development, quality planning & assurance, data analysis and implementation.
- Working knowledge with Informatica CDC for daily loads.
- Excellent understanding of the System Development Life Cycle. Involved in analysis, design, development, testing, implementation, and maintenance of various applications.
- Extracted data from various sources ranging from various RDBMS like Oracle, MS SQL Server, DB2, Teradata and Sybase, and Flat Files to load data into data marts and data warehouse.
- Expertise in working with TOAD for creation of Tables, Materialized views and normal views.
- Proficiency in working with PL/SQL stored procedures, packages, and triggers.
- Actively involved in Performance Tuning, maintaining Staging area, Backup and Recovery process, product support on various platforms.
- Effective implementations of change data capture using Power Exchange for daily loads.
- Performed data quality checks using data quality tools.
- Good experience working with large databases and performance tuning.
- Efficient in Optimizing, Debugging and testing SQL queries and stored procedures.
- Excellent working knowledge of Shell Scripting and job scheduling on platforms like UNIX.
- Able to interact effectively with other members of the Business, Quality Assurance, Users and other teams involved with the System Development Life cycle.
- Excellent analytical, functional, design and development skills with excellent communication skills and the ability to interface with the executives and perform independently as well as team environments.
Data Warehousing / ETL : Informatica Power Center 9.X/8.X /7.1, Tera Data 13.X/12.X.
Database : Oracle 11g/10g/9i/8i, MS SQL Server,
Database Tools : TOAD, SQL Navigator.
Languages : SQL, PL/SQL, HTML, XML
Operating Systems : IBM Z/OS, Windows 95/98/NT/2000/XP and UNIX
Confidential , Clayton, MO
IT FRAUD WASTE ABUSE Project is built in pulling the claims from EDW (Enterprise Data warehouse) for particular members based on their business unit and their plan.
The Project acquires and Integrates Member, Eligibility, Claims, Benefits and Paid claims into HMS and submit the reports to Standard Vendor Outbound Paid claim.
- Worked with power center tools like Designer, Workflow Manager, Workflow Monitor, and Repository Manager.
- Created metadata queries which would help in restart and to find out any missing link conditions or disabled tasks in a workflow.
- Deployed reusable transformation objects such as mapplets to avoid duplication of metadata, reducing the development time.
- Used Informatica data services to profile and document the structure and quality of all data.
- Extensively used Informatica Transformation like Source Qualifier, Rank, SQL, Router, Filter, Lookup, Joiner, Aggregator, Normalizer, Sorter etc. and all transformation properties.
- Extensively used Various Data Cleansing and Data Conversion Functions in various transformations.
- Experience working with Teradata tools MLOAD, FLOAD, FEXP and BTECH script.
- Translated Business processes into Informatica mappings for building Data marts by using Informatica Designer which populated the Data into the Target Star Schema on Oracle 9i Instance.
- Developed ETL mappings, transformations using Informatica Power center 8.6
- Deployed the Informatica code and worked on code merge between two difference development teams.
- Identified the bottlenecks in the sources, targets, mappings, sessions and resolved the problems.
- Created automated scripts to perform data cleansing and data loading.
- Attended daily status call with internal team and weekly calls with client and updated the status report.
- Created and maintained mappings as needed.
- Tested application and implemented into production
- Modified mappings, sessions, workflows as needed to get them to work as designed in upgraded environment.
Environment: Informatica PowerCenter 9.1.0, Power Exchange 9.1.0, Oracle 11g, Toad, HP Quality Center, Tera Data,DB2, 13.X/12.X, Tidal Scheduler.
Confidential , Portland, OR
BIDW ETL Developer
The purpose of the INPATIENT Project is to integrate patients demographic, hospital, treatment data associated each facility and clinical data from NW Data warehouse, CBS and Clarity into a new, KPNW Data Mart that feeds the reporting.
The purpose of the Finance Project is to integrate all employees demographic and payroll data associated costcener and yearly budget data from NW Data warehouse, CBS, Clarity and One link into a new, central KPNW Data Mart that feeds the reporting.
- Responsible for reviewing Business Requirement document
- Translated user requirements into system solutions.
- Involved in Logical Data Modeling and Database design.
- Designed and developed Oracle Star Schema Datamart
- Involved in Data Analysis of the OLTP system to identify sources data elements
- Worked with Oracle, Flatfiles,Teradata and SQL Server source systems
- Prepared Technical Specification Document
- Prepared Source to Target ETL mapping Documents
- Designed and developed Informatica Mappings, Reusable Sessions, Worklets, Workflows, Dynamic Parameter files
- Designed and implemented Audit control and Exception control strategies
- Provided solutions for various performance bottle necks in Informatica Mappings
- Designed and Developed Autosys jil scripts to Schedule Informatica workflows
- Developed various shell scripts using Korn Shell to Integrate various components
- Created test cases and pseudo test data to verify accuracy and completeness of ETL process
- Involved in Unit testing, System Integration testing, User Acceptance Testing
- Involved in Informatica Code Migration across various Environments
- Involved in Production Support and Knowledge Transfer sessions
Environment: Informatica Power Center 9.5.1, Informatica power exchange 9.5, Oracle 11G, SQL Server, Flat files, PL/SQL, SQL*Plus, TOAD 9.1, UNIX, Shell Scripting, Tivoli, Erwin, Microsoft Visio
Confidential , Greenwood Village, CO
The PDI (Proclaim Data Integration) project is built in the process of expanding the scope of Confidential. The Project acquires and Integrates Member, Eligibility, Claims, Benefits and Accumulations data from Proclaim into the Information Factory. The Effort included stage, integrate and position the data making it available for the downstream data consumers.
- Especially worked on the benefits subject area of PDI project.
- Staged Data from legacy Proclaim IMS system into Oracle 11g Master Tables which involved extracting data from proclaim mainframe JCL and cobol
- Performed CDC capture registrations.
- Assisted in building the ETL source to Target specification documents by understanding the business requirements.
- Developed mappings that perform Extraction, Transformation and load of source data into Derived Masters schema using various power center transformations like Source Qualifier, Aggregator, Filter, Router, Sequence Generator, look up, Rank, Joiner, Expression, Stored Procedure, SQL, Normalizer and update strategy to meet business logic in the mappings.
- Created High Level Design document and Low level design document for the projects worked on.
- Worked on code enhancements for the assigned projects.
- Reusable transformations and Mapplets are built wherever redundancy is needed.
- Slowly Changing Dimension Type 2 is implemented to maintain last 3 years of historical data.
- Performance tuning is performed at the Mapping level as well as the Database level to increase the data throughput.
- Designed the Process Control Table that would maintain the status of all the CDC jobs and thereby drive the load of Derived Master Tables.
- Used tasks like Command, Decision, Event Wait, Event Raise, Assignment and Timer in the workflows and worklets to meet business requirements logic effectively.
- Created promotion packages in harvest for the code deployment to higher environments.
Environment: Informatica PowerCenter 9.1.0, Power Exchange 9.1.0, Oracle 11g, Toad, HP Quality Center, Tera Data,DB2, 13.X/12.X, Control M Scheduler.
Confidential , Cincinnati, Ohio
Confidential (ADW) captures data from Integrated heterogeneous sources (SAP, Siebel, manufacturing Data, Consumer, etc.) and physically stores Confidential ’s key business measures once in their most "atomic" form and then makes the data available via many logical business views (logical, regional and global) to create near real-time data feeds that address our Business Intelligence needs. The data is presented via the integration of the Procter & gamble Business Intelligence Portal and architected to utilize the same atomic data across business levels and areas of the business. This type of architecture provides for presenting Confidential atomic data to more areas of the business in less time, and makes the ADW scalable.
- Involved in the process of extracting data from integrated heterogeneous sources and sorting extracted data.
- Performed the preparation of technical design docs, source to target mapping (filed to field matrix). Prepared the strategies for performance tuning and reusable components in the Informatica.
- Performed data validation, data reconciliation on the target DB2 Server and source data.
- Created mappings using Informatica, prepared the test cases based on the business requirements and documented them in a specified manner.
- Involved in migration of the Informatica objects between the repositories with deployment groups.
- Designed and developed complex Informatica mappings using expressions, aggregators, filters, lookup and stored procedures to ensure movement of the data between various applications.
- Tuned all the mappings for better performance.
- Error checking and testing of the ETL procedures and programs using Informatica session log and various tracing options
- Implemented the exception handling for trapping various exceptions and reported the various errors.
- Designed and developed Oracle PL/SQL Procedures and wrote SQL, PL/SQL scripts code for extracting data to system
Environment: DB2, Oracle 9i, Informatica 7.2, UNIX.
Confidential , Branchville, NJ
Data Warehouse Developer
Selective is an insurance and reinsurance company with specialty in underwriting of property and causality insurance. Business and organizations can cover losses and provide for sharing the costs of losses among all insured. A digital cockpit, a web based application displays analytical data of Selective Company. Analytical data includes account details, pricing impact details, and claim and expense details. Digital cockpit application gets data from specialty data warehouse. Informatica interfaces are developed to load data from various sources database into the data warehouse
- Worked with Business analysts for requirement gathering, business analysis, and testing and project- coordination.
- Prepared technical specifications to develop Informatica ETL mappings to load data into various tables confirming to the business rules.
- Created the (ER) Entity Relationship diagrams & maintained corresponding documentation for corporate data dictionary with all attributes, table names and constraints.
- Research Sources and identify necessary Business Components for Analysis.
- Coordinating with source system owners, day-to-day ETL progress monitoring, Data warehouse target Schema Design (Star Schema) and maintenance.
- Created Informatica mappings as per technical specifications.
- Created different transformations for loading the data into target database.
- Performance tuning of mappings, transformations and sessions to optimize session performance.
- Created snapshot for the transactional tables in distributed databases and also created Triggers, Procedures and Functions for the backend Development.
- Developed Unix Shell Scripts and Stored Procedures to run from the Informatica pre and post sessions to create and drop the indexes to improve the speed of session execution
- Worked on issues with migration from development to testing.
- Carried out ETL Testing using Informatica and PL/SQL.
- Used SQL, PL/SQL to validate the Data going in to the Data Warehouse.
Environment: Oracle 8i, 9i, 10g, Informatica 8.X, Erwin, Windows, UNIX.
Confidential, Mysore, India
Confidential is a Maintenance and Development Project where we had to develop the actuate reports based on the requirements given from the client. The Database used here is Oracle Database where we write the queries or procedures as per the requirements and extract the data from the database using actuate.
- Extensively involved in requirements gathering and data gathering to support developers in handling the design specification
- Involved in designing and coding of functional specifications for the development of user interfaces
- Created tables, indexes, sequences, constraints and snapshots
- Developed packages, procedures, functions and PL/SQL blocks for data validation
- Developed PL/SQL scripts to validate and load data into interface tables
- Fixed software bugs and interacted with developers to resolve technical issues
- Designed and developed a Generic Billing system for a Telecommunication company
- Responsible for all pre-ETL tasks upon which the Warehouse depends including managing
Environment: Oracle 8i, 9i, SQL, PL/SQL, UNIX and Windows, Korn Shell.