Sr. Informatica/etl Developer Resume
Chicago, IL
Summary of Qualifications:
- Over all 5+ years of I.T experience in Design, Analysis, Application Development, Implementation and Maintenance in Client/Server architecture, Informatica Power Mart/ Power Center, Windows XP, Oracle 8.0/9i.
- 5+ years of strong experience as an ETL Developer/Business Intelligence Developer, which includes Data Warehouse Developer experience in Informatica Power Center 8, 7.
- Informatica Certified Developer.
- Strong Experience in developing strategies for Extraction, Transformation and Loading (ETL) mechanism using various ETL Tools. Handled 3 Terabytes of data warehouse database.
- Strong in Source to Target data Mappings and using Slowly Changing Dimension Mappings.
- Expert in various Warehouse Modules like Informatica Server and Client tools like Designer, Server Manager, Workflow Manager, Workflow Monitor, Repository Manager, Business Objects and OLAP.
- Experienced in performance and tuning of Informatica mappings and sessions for better performance.
- Experience in writing UNIX scripts and Windows Batch jobs.
- Experience in Database programming for Data Warehouses (Star Schemas), developed Strategies for Extraction Transformation Loading (ETL) mechanism.
- Written PL/SQL, SQL, triggers, and cursors in Oracle 10g, 9i, 8i Expertise in performance and tuning of triggers, stored procedures, queries. Creating tables, views and importing to the Informatica.
- Expertise in creating and scheduling Informatica jobs with control M and Cron Tab.
- High exposure to development, testing, debugging, implementation, documentation, user training and production support.
Technical Expertise:
- Operating Systems UNIX, Windows 2000/XP.
- Databases Oracle 10g/9i, MSSQL Server 2000, MS Access, MS Excel.
- Database tools Toad, SQL Developer and SQL Plus
- ETL & Business Intelligence Tool Informatica PowerCenter 8, 7.
- Data modeling & Data integration Erwin.
- Environment Win XP, MS DOS 6.22, UNIX.
- Reporting Business Objects XI
- Scheduling Control M, Informatica Scheduler
Education:Bachelor of Engineering
Professional Experience:
Confidential, Chicago, IL Feb 2010 - Till Date
Sr. Informatica/ETL Developer
Description: As the 7th largest U.S. commercial insurer and the 13th largest U.S. property & casualty insurer, CNA provide insurance protection to more than one million businesses and professionals in the U.S. and internationally. CNA itself is 89% owned by a holding company, Loews Corporation.
Project Scope: Consolidated, reconciled data repository that combines all core investment data represented by the GAAP and STAT Holdings Files that is solely maintained by the Investments Division needed for CNA and Loews portfolio management activities, management information, financial reporting while enriching the existing data with additional financial and non-financial data that are not currently available in a centralized location. Leveraging existing tools and knowledge build a reporting and query infrastructure supporting CNA and Loews portfolio management information needs.
Role & Responsibilities:
- Worked as a Technical Lead and participated in all the project phases starting from Requirements gathering through Deployment of this Program (SDLC).
- Worked as a part of Onsite - Offshore model. Being a Lead Informatica developer, I have taken the responsibility for creating checklists for coding standards, naming conventions etc. I have also developed reusable code components to maintain ETL standard load practices like Table Analyze script, Stage pre load, file archive and maintaining two months of archived files and logs.
- ETL Tool Informatica is used for Design and Development of the code.
- Data is sourced from 22 different Work Units. These Work Units span across huge variety of data sources like Oracle, Flat Files, MS Access and Spread Sheets.
- ETL flows are developed from Source to Stage, Stage to Work tables and Stage to Target Tables.
- Source and Target Definitions are imported from Source Analyzer and Target Designer connecting to databases using Relational and ODBC Connections.
- Developed Informatica Mappings for the complex business requirements provided using different transformations like Normalizer, SQL Transformation, Expression, Aggregator, Joiner, Lookup, Sorter, Filter, Router and so on.
- Worked in developing Mapplets and Re-usable Transformations for reusability and reducing effort.
- Created Work Flows with Command Tasks, Worklets, Decision, Event Wait and Monitored sessions by using workflow monitor.
- Migrated Informatica Folders from Development Env to Test and System Test Env and Worked with Admins to migrate the same to Production environments.
- Wrote PL/SQL procedures for reconciliation of financial data between source and target to automate testing phases and help business for preliminary validation.
- Wrote UNIX scripts, environment files for Informatica.
- Developed Metadata driven code for effective utilization and maintenance using technical metadata, business metadata and process metadata.
- To externalize the business logic instead hardcoding in the mapping I have used Parameter file in Informatica.
- Generated BO reports to test standardized reports as per business requirements.
- Tuned Mappings and Mapplets for best Performance on ETL Side and Created Indexes and Analyzed tables periodically on Database side.
- Organized the dataflow and developed many Control M jobs for Scheduling MINT program and moved to production.
- Primary resource in Production support team so, involved in emergency calls when application outage occurred and resolved defects when raised.
Environment: Informatica PowerCenter 8, SQL Server 2000, Oracle 10g, MS Excel, MS Access, Flat Files, Business Objects XI, SQL Dev., Unix/AIX , Windows XP, Erwin, Control M.
Confidential,Peapack, NJ Nov 2008-Jan 2010
Informatica/ETL Developer
Description: Pfizer discovers, develops, manufactures, and markets leading prescription medicines for humans and animals and many of world’s best-known consumer brands. It is a research-based organization, with three-business segments health care, animal health and consumer health care with presence in many countries.
Project Scope: To build a data warehouse, which could be operational, middle ground for data obtained by clinical trials for a number of drug projects, so that faster reporting, faster and easier data cleanup and data integration between functional areas in an enterprise wide scenario which subsequently results in faster time to market.
Role & Responsibilities:
- Designed and Implemented the ETL Process using Informatica power center.
- Involved in Data Extraction from Oracle, Flat files using Informatica.
- Prepared ETL flow of data from Staging to Data Mart.
- Used Source Analyzer and Warehouse designer to import the source and target database schemas.
- Created mappings from source systems to target systems. Implemented Join, Expression, Aggregate, Sort, Rank, Lookup, Update Strategy, Filter and Router Transformations in Mappings.
- Worked on Designer tool –Source Analyzer, warehouse designer, Mapping Designer & Mapplet Designer and Transformation Developer.
- Involved in complete software development life cycle (SDLC) of the project.
- Mapplets and Reusable Transformations were used to prevent redundancy of transformation usage and maintainability.
- Creating Reusable Transformations and Mapplets in a mapping.
- Involved in Creating tasks, worklets, workflows and Schedule, Run, Monitor sessions by using workflow manager and workflow monitor.
- Worked on moving mappings from Development to Test and from Test to Production environments.
- Migrated Mappings, Sessions, and Workflows from Development to Test and then to Production environment.
- Identified and fixed the Bottle Necks and tuned the Mappings and Sessions for improving performance. Tuned both ETL process as well as Databases.
- Created and monitored sessions and batches to run the mappings.
Environment: Informatica PowerCenter 8.1, SQL Server 2000, Unix/AIX 5.3.8.0, Windows Xp, Oracle 10g, Erwin.
Confidential,Columbus, OH Jul 2007-Nov 2008
Informatica Developer
Description:
Confidential, is one of the top most banks in USA. Here I work in a team Environment with 3 oracle developers, 1 informatica Admin and a team lead. As an Informatica Developer I used to look after the Early Resolution, EDW extract projects i.e., scheduling, monitoring and tuning them accordingly; configuring the Email task for sending the success or failure mails. Here I effectively utilized my programming skills to adhere to coding standards, procedures and techniques while contributing to the technical code and required documentation. Here I used CVS software, Release control for bugs fixing. Our main primary source was EDW. All the workflows used to run thru CRONTAB in UNIX.
Responsibilities:
- Worked with informatica power center, Sybase, oracle 10g and UNIX AIX. Worked in Data warehouse Development life cycle.
- Worked with different mappings like getting data from Oracle tables and flat files, getting data from dimension and fact tables in 2 separate data marts and loading to oracle target tables. Also used pre-loaded stored procedures.
- Worked with other teams for bug fixing, documenting QA DOCS (checking list, design document, performance test doc, creating ECM, business Approval doc, and code review doc) and getting approved.
- Run the install scripts thru RELEASE CONTROL, UNIX shell scripts to run the workflows, used CRONTAB for scheduling the scripts.
- According to transformation logic, we used various transformations like sorter, sorted aggregator, lookup, Expression, Normalizer, filters, Update Strategy, Joiner, Router etc in Mappings.
- Configured the Reusable command task for sending the triggers, invoking different scripts and Reusable Email tasks for sending failure mail along with session details and appending logs with it and success mails. Also used Event wait, Event Raise, Scheduler options in the workflows.
- Developed SQL, PL/SQL, Stored Procedures, triggers for Implementing Complex Business Rules, Transformations and Loading.
- Tuned various sql queries, mappings, targets and sources to improve the performance.
- Used session and workflow logs for debugging the session. Knowledge in using cognos for report generation. Good knowledge in using Versioning tools like CVS.
- Used debugger wizard to remove bottlenecks at source level, transformation level, and target level for the optimum usage of sources, transformations and target loads.
- Configured the workflows for running concurrently, used various types of migration techniques for moving code to Test environment from Development environment.
Environment:
Informatica Power Center 8.1, Oracle 10g, Toad, SQLplus, PL/SQL, Windows 2000, UNIX AIX 5.3.8.0
Confidential, CA. Aug 2006-Jul 2007
Informatica Developer
Description: Confidential, is one of the leading Pharmacy product distributors in USA. This company has two major transaction systems, viz., STAR and Distrack. AmeriSource has AS/400 System, which gives a Distrack data, and Bergen has Mainframe system, which gives Star data. All distrack data’s are coming by COBOL file format and star data’s are extracted from Mainframe files. Distrack files are coming one day before than star data. The aim of this project is consolidate the sales data for the both companies.
Responsibilities:
- Coordinating with the client and gathering the User requirements.
- Good experience in oracle 10g by running queries for evaluating the mappings. Preloaded procedures used and created triggers.
- Good experience in dealing with Data conversion functions, Extracting from sources, Transforming according to logic and loading into target tables using Informatica
- Used Timestamp on Rows, Version numbers on Rows, Triggers on tables, status indicators on Rows, SCD (type1, type 2, type3). SCD’sare used to simplify ETL in data warehousing applications.
- Identified the Dimensions and Facts for the Data Modeling
- Data Mart used is Sales Data Mart with target database as ORACLE.
- Used query tools like SQL Developer for Data Accessing i.e. to store and retrieve the data warehouse database objects.
- Designed, developed, demonstrated major Businesses Intelligence tools like cognos to drill down, slice and dice, pivot, and analyze for the ABDC Sales data.
- Used Control M to schedule and Run Informatica jobs.
- Done Various Performance Tunings in SQL. Creating Test cases for Unit Test, System Integration Test and UAT to check the data quality.
- Debugger wizard to remove bottlenecks at source level, transformation level, and target level for the optimum usage of sources, transformations and target loads.
- Migrating the mappings and workflows from development to Production.
- Did the Root cause Analysis to find the problem
- Responsible for loading data into warehouse from different sources using Loader Utility to load millions of records.
Environment: Informatica Power Center 7.1.3, Oracle 10g, SQL Server 2000, Mainframes, Erwin, Control M, Advanced Query Tool, Windows 2000, UNIX AIX 5.1