SUMMARY

• 8+ years of strong Data Warehousing experience using Informatica PowerCenter 8.x//7.x/6.x/5.x (Source Analyzer, Data Warehousing Designer, Mapping Designer, Mapplet Designer, Transformation developer), Power Connect, Power Exchange.
• Extensive experience in Banking, Insurance, Pharmacy, Financial, Retail and Healthcare domains.
• Experienced in working with disparate databases including Oracle, DB2, MS SQL server 2008/2005 and capable of handling 4 terabytes worth of OLTP source data and 6 terabytes worth of target Data Warehouse data.
• Experienced in Installation, Configuration, and Administration of Informatica Power Center/Server.
• Experienced in complete life cycle Implementation of data warehouse.
• Well versed in OLTP Data Modeling, Data warehousing concepts.
• Experienced in OLTP/OLAP system study, analysis and ER modeling, developing Database schemas like Star schema and Snowflake schema used in relational and multidimensional modeling by using Erwin, Visio
• Involved in the entire SDLC (Software Development Life Cycle) process that includes Implementation, testing, deployment, documentation, training and maintenance.
• Experienced in Understanding & mapping client’s requirements / enhancements to the product and provide solutions for Enterprise Application Management, Custom Build Application Management, Database Migration, services management and Testing services Management.
• Good Knowledge in Normalization and De - normalization techniques. Extensively followed Ralph Kimball and Bill Inman Methodologies.
• Extensively worked on the ETL mappings, analysis and documentation of OLTP/OLAP reports requirements. Solid understanding of OLAP concepts and challenges, especially with large data sets.
• Ability to write complex SQLs needed for ETL jobs and analyzing data, and is proficient and worked with databases like Oracle 11x/10g/9i/8x/7x, SQL Server 2008/2005/2003/7.0/6.5, DB2, MSSQLExcel sheets, Flat Files, Sybase, COBOL files and XML files.
• Developed Complex mappings from varied transformation logics like Unconnected /Connected lookups, Router, Filter, Expression, Aggregator, Joiner, Union, Update Strategy and more.
• Implemented Performance Tuning, identifying and resolving performance bottlenecks in various levels like sources, targets, mappings and sessions.
• Experience in creating Reusable Tasks (Sessions, Command, Email) and Non-Reusable Tasks (Decision, Event Wait, Event Raise, Timer, Assignment, Worklet Control).
• Migrating data mapping to production and monitoring, troubleshooting and restarting batch processes using Informatica Power Center.
• Extensively worked on developing and debugging Informatica mappings, mapplets, sessions and workflows.
• Technical Proficiency in Oracle SQL Queries and PL/SQL procedures, functions, packages, SQL Loader.
• Strong in UNIX Shell and PERL scripting. Developed UNIX scripts using PMCMD utility and scheduled ETL load using utilities like CRON tab, Maestro, Control-M.
• Experience in creating new jobs, scheduling Informatica workflows and UNIX scripts in Control-M.
• Experienced in Installation, Configuration, and Administration of Informatica Power Center/Power Connect on Client/Server environment.
• Good knowledge about the architecture of the Teradata database. Developed Teradata loading and unloading utilities like Fast Load, Multiload and Fast Export. And also Developed BTEQ scripts to load data from Teradata Staging area to Data Warehouse
• Expertise in doing Unit Testing, Integration Testing, System Testing and Data Validation for Developed Informatica Mappings.
• Demonstrated ability to work and communicate effectively with both business and technical audiences.
• Sincere, well-organized, quick-learner, self-motivated team player and has experience in all phases of the Systems Life Cycle including project definition, analysis, design, coding, testing, implementation and support.

TECHNICAL SKILLS

Operating Systems: Windows 200X/XP/95/NT Server, UNIX (Sun-Solaris, AIX, HP/UX), Linux.

ETL Tools: Informatica PowerCenter 9.1/8.6.1/8.1/7. X, SQL*Loader

Databases: Oracle 8i/9i/10g/11x, IBM DB2 UDB 8.0/7.0, MS-SQL Server 2000/2005/2008, MySQL and MS-Access,AS400, MS Access, Teradata V2R6/V2R5

Programming: C, C++, UNIX Shell Scripting, Korn shell scripting, PL/SQL, T-SQL, SQL,XML

Reporting Tools: Business Objects XiR3/XiR2/6.0/5.1, Crystal Reports 4.0/6.0

Tools: TOAD, MS VISIO, SQL PLUS, SQL Loader, Control-M and Autosys, Unix Maestro

Modeling: ERWIN 4.0, Oracle Designer 2000, Dimensional Data Modeling, Star Schema Modeling, Snowflake Modeling, FACT and Dimensions Tables, Physical and Logical Data Modeling, ER Diagrams, Oracle Designer.

PROFESSIONAL EXPERIENCE

Confidential, Dallas, TX

Sr. ETL/Informatica Developer

Responsibilities:

• Worked as a Business analyst, Responsible in gathering Requirements and IT review. Interacted with Business Users in the design of technical specification documents.
• Interacted actively with Business Analysts and Data Modelers on Mapping documents and Design process for various Sources and Targets in corresponds to project coordination. Worked directly with Senior Architect and other team members to create ETL design specifications and support the implementation.
• Extracted data from AS/400 DB2 files, Oracle tables and flat files to populate billing, service order and plant interface files, which are then loaded into the Citizens DPI Billing system.
• Used transformations like Source Qualifier, Aggregator, Filter, Router, Sequence Generator, look up, Rank, Joiner, Expression, Stored Procedure and update strategy to meet business logic in the mappings.
• Worked with Memory cache for static and dynamic cache for the better throughput of sessions containing Rank, Lookup, Joiner, Sorter and Aggregator transformations.
• Created Mapplets, reusable transformations and used them in different mappings.
• Created Workflows and used various tasks like Email, Event-wait and Event-raise, Timer, Scheduler, Control, Decision, Session in the workflow manager.
• Made use of Post-Session success and Post-Session failure commands in the Session task to execute scripts needed for clean up and update purposes.
• Extensively worked with Source Analyzer, Warehouse Designer, Transformation Designer, Mapping Designer and Mapplet Designer.
• Experience in writing Shell scripts for file manipulation, extraction and execution of Workflows
• Developed various mappings that use XML sources and also populated into XML targets.
• Extensively worked on Optimization/Performance tuning to increase the throughput of the data load.
• Involved in Unit testing, System testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
• Created Data Breakpoints and Error Breakpoints for debugging the mappings using Debugger Wizard.
• Actively involved in production support. Implemented fixes/solutions to issues/tickets raised by user community.
• Responsible for monitoring all the sessions that are running, scheduled, completed and failed. Debugged the mapping of the failed session.
• Developed, executed Test Plans and Test Cases to validate and check Referential integrity of data extracts before loading it to Data Warehouse.
• FTP scripts and Extraction of Unstructured Data from various Verizon Data formats using Perl/Shell Scripts. XML/flat file formats also extracted successfully.
• Designed Pmcmd & UNIX Shell scripts to in corporate dependencies and scheduled Cronjobs to run ETL processes.
• Extensively worked on SQL tuning process to increase the source qualifier throughput by analyzing the queries with explain plan, creating new indexes, partitions and Materialized views.
• Designed and developed error handling and rekey mechanism in Informatica
• Created repository & assigned grants to users to access the repository.

Environment: Informatica Power Center 9.1/8.6.1, Informatica Power Exchange 9.1/8.6, Oracle 11g, PL/SQL, XML, Flat files, UNIX (AIX 5.3), Toad, SQL Developer, SQL * Loader, AS400, DB2, Shell Scripting, HP Quality Center

Confidential, St Louis MO

Data Warehouse Developer (Informatica Developer)

Responsibilities:

• Gathered user requirements and reporting requirements for a better understanding of the business process and the current reporting system.
• Designed the target database by conducting Joint Application Development sessions. Got approvals from the business users.
• Used Power Designer for designing the target data mart.
• Generated DDL’s for the approved design and implemented them in the Data mart.
• Designed ETL strategies for migrating the required data from Edward to Tier med application database.
• Used various transformations like lookup, update strategy, Expression, Aggregator, router, filter, sequence generator, source qualifier/Joiner on data and extracted according to the business rules and technical specifications.
• Created and Configured Workflows, Worklets and Sessions to transport the data to target warehouse tables using Informatica Workflow Manager.
• Scheduled the sessions to extract, transform and load data into warehouse database as per Business requirements.
• Used workflow monitor to monitor the workflow and reviewed error logs that it generates for each session.
• Wrote complex stored procedures and triggers to automate the periodic backup of the critical data into the backup database.
• Documented the Source to Target Mapping Matrix, and Build Documents for the Database Users.
• Created test plans and involved in Integration Testing.
• Created complex BTEQ scripts to load the data into the landing zone tables and work tables and called these BTEQ scripts using command tasks in Informatica workflow.
• Used Fast Export and Fast Load utility connection strings to expedite the loading process.
• Implemented performance tuning logic on targets, sources, mappings, sessions to provide maximum efficiency and performance.
• Documented the process to describe program development, logic, coding,testing, changes and corrections.
• Created implementation plans to release re-engineered code and data structures and retire existing components with minimal impact to downstream processes and users.
• Understood the data model for the requirement and suggested changes when required.
• Generated DDL’s for the views, checked in SharePoint and got it reviewed and approved by management.
• Worked closely with Infa Admin for migration of Informatica mappings from environment to environment.
• Created data request documents to refresh data in the underlying tables from production environment to test environment.
• Coordinated with other teams that are using the same Edward tables and got approvals for refreshing the tables.
• Worked closely with business analysis team and business users team to ensure the application is working according to the requirements and reports are generated in timely fashion.
• Coordinated with Edward DBA’s to perform the requested data refresh, data migrations, space movements etc.
• Preparing and reviewing the test cases.
• Testing the views in all aspects of test cases, performed unit testing and modified the views as and how required.

Environment: Informatica PowerCenter 8.6.1, Oracle 11g, Teradata V2R6, Teradata SQL Assistant 12, PL/SQL, Flat files, Business Objects XiR2, Clear Quest, Clear case, UNIX, TOAD.

Confidential, Chicago, IL

ETL Developer

Responsibilities:

• Interacted with the Business users to identify the process metrics and various key dimensions and measures. Involved in the complete life cycle of the project.
• Developed FRD (Functional requirement Document) and data architecture document and communicated with the concerned stakeholders. Conducted Impact and feasibility analysis.
• Identified and tracked the slowly changing dimensions, heterogeneous Sources and determined the hierarchies in dimensions.
• Acted as a team lead for the off-shore team with a group of 4 people.
• Created mappings using the Transformations like Source qualifier, Aggregator, Expression, lookup, Router, Filter, Rank, Sequence Generator, and Update Strategy.
• Used other features of Power Center namely pmcmd, pmrep, target override, target load order, Connected and Unconnected look up, Data, index, Persistence Dynamic & Static lookup cache.
• Created Mapplets, reusable transformations and used them in different mappings.
• Created Workflows and used various tasks like Email, Event-wait and Event-raise, Timer, Scheduler, Control, Decision, Session in the workflow manager.
• Made use of Post-Session success and Post-Session failure commands in the Session task to execute scripts needed for clean up and update purposes.
• Successfully Loaded Data into different targets from various source systems like Oracle Database, Flat files, ODS, SQL Server...etc into the Staging table and then to the target database Oracle and MDM.
• Implemented parallelism in loads by partitioning workflows using Pipeline, Round-Robin, Hash, Key Range and Pass-through partitions.
• Trouble shooting of long running sessions and fixing the issues.
• Involved in Version control of code from development to test and Production environments.
• Developed Web service enabled workflows that process the requests sent by the web service client and sends the response to web service using SOAP (Simple Object Access Protocol).
• Involved in writing SQL scripts, stored procedures and functions and debugging them.
• Prepared ETL mapping Documents for every mapping and Data Migration document for smooth transfer of project from development to testing environment and then to production environment.
• Involved in Unit testing, System testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
• Worked with reporting team to help understand them the user requirements on the reports and the measures on them. Helped them in creating canned reports.
• Migrated repository objects, services and scripts from development environment to production environment. Extensive experience in troubleshooting and solving migration issues and production issues.
• Involved in creating multi dimensional universe and reports in Business Objects Environment.
• Designed and developed ad-hoc and customized reports using Cognos 8.
• Lead the development team during the development process, this include provide help with the tasks and transfer knowledge of Informatica.
• Actively involved in production support. Implemented fixes/solutions to issues/tickets raised by user community.

Environment: Informatica Power Center 8.6.1/8.1.1, Oracle 10g, MS SQL Server 2008, My SQL, DB2,PL/SQL, MS Visio, Toad, Business objects XiR2, UNIX, ERWIN 4.2, Control-M.

Confidential, St. Louis, MO

Informatica Developer

Responsibilities:

• Gathered Business requirements by organizing and managing JAD sessions with business stake holders, development team, Business Intelligence team and Business Analysts on a scheduled basis.
• Analyze Different Data Sources like MS SQL Server, Oracle 10g and Flat files from which Customer, Products, Services and Inventory data is coming and understand the relationships by analyzing the OLTP Source.
• Involved in database design for both online transaction 3rd normal form and dimensional models, Data Warehousing, Data Migration, Data Staging, Applications Design and Business Process Re-engineering.
• Involved in the entire SDLC (System Development Life Cycle) process that includes Implementation, testing, deployment and maintenance.
• Understood all the business requirements and created database schemas.
• Active participant of Data Review Board and Change Review Board to review, analyze and provide estimates for requirements/enhancements/defects.
• Effectively managed data flow from initial data collection to data transfer across various internal databases by monitoring all potential issues and immediately rectify the same.
• Prepared Logical Process and Data Models that contained set of diagrams and supporting documents containing the essential business elements, detailed definitions, and descriptions of the relationships between the data elements to analyze and document Business Data Requirements.
• Used Erwin for creating the 2nd and 3rd normal form data models respectively based on the requirements and specifications.
• Involved in designing conceptual, logical and physical data model using Erwin data modelling tool.
• Understood UML based domain models and converted then into logical and physical data models.
• Developed enterprise 3rd normal form data models for OLTP Source systems.
• Extensively involved in Requirements gathering and Business Analysis using the Use-cases, functional specifications, and business rules.
• Modeled persistent and non-persistent staging tables for the data warehouse.
• Maintained warehouse metadata, naming standards and warehouse standards for future application development.
• Involved in four-step process for designing dimensional models which included, Preparing Transaction-level fact tables, Examining Additive and non-additive facts, Considering Causal dimensions, such as promotion, Used Degenerate dimensions, such as the transaction ticket number.
• Synchronized with Data Leadership Team that meets on a periodic basis to establish data policies. Also, Assisted Data Quality Analysis team to maintain data quality standards.
• Designed the Logical and Physical model of the database using Erwin.
• Extensively used Erwin for Dimensional Data Modeling.
• Used Repository Manager to create folders, which is used to organize and store all Metadata in the Repository.
• Created and developed Slowly Changing Dimensions tables to facilitate maintenance of history. (SCD type-1, type-2, type -3)
• Created various star and snow flake schemas with confirmed dimension and fact tables.
• Created SSA for each mapping. Created source to target mapping documents. Delivered the documents to the ETL tech leads.
• Created source to target mapping documents in a well defined and well organized manner.
• Extensive use of Triggers, Table Spaces, Sequences, Materialized Views, Procedures and Packages in Data Models.
• Rewrote manual loads using automated routines with DB2, SAS and Korn shell script routines.
• Tuned the SQL Queries by breaking down the predefined queries into smaller queries for processing, Pushed Query processing up from the client to application server level.
• Participated in the entire requirements engineering process right from requirements elicitation phase to the phase involving documenting of the requirements.
• Created multi-way aggregate fact tables when queries on granular data produce slow response time and large unwanted result set.
• Used Informatica Data Profiling tools to maintain the quality and consistent classification and identification of the data.
• Extensively worked on Oracle 11g Administration including indexing, hashing, partitioning and handling read, write permissions to the development team.

Environment: Informatica Power Center 8.1.1/7.X, Oracle 9i, SQL, PL/SQL, XML, MS Access, LINUX, UNIX Shell Scripting, Control-M.

Confidential, Camden, NJ

ETL Developer

Responsibilities:

• Created Dimension Tables and Fact Tables based on the warehouse design.
• Involved in creating Logical and Physical design of databases using Erwin.
• Participated in build of the Data Warehouse, which includes the Design of Data mart using Star Schema.
• Created ETL detail design document and ETL standards document.
• Created Repository using Informatica Power Center - Repository Manager.
• Extracted data form flat files and oracle database, applied business logic to load them in the central oracle database.
• Involved in Extraction, Transformation and Loading (ETL) Process.
• Created necessary Repositories to handle the metadata in the ETL process
• Worked extensively with complex mappings using expressions, aggregators, filters, lookup and procedures to develop and feed in Data Warehouse.
• Created reusable transformations and Mapplets and used them in mappings.
• Worked on SQL queries and tuned them to improve performance.
• Extensively used Shell scripts to automate the Pre-Session and Post-Sessions processes.
• Performed data manipulation using basic functions and Informatica Transformations.
• Developed various reports using Cross Tab, Master Detail and different charts including Line, Column, Area, and Pie Charts for analysis.
• Modified Universes and generated reports using Business Objects.
• Designed & developed reports, Drilldown reports using Business Objects Reporter.
• Designed reports with Slice and Dice & Drill down operations.

Environment: Informatica PowerCenter 7.1.1, Oracle 8i, SQL Server, ERWin 3.5, Business Objects 5.1, UNIX (AIX), Windows NT

Confidential

Data warehousing developer

Responsibilities:

• Involved in the requirement definition and analysis in support of Data Warehousing efforts.
• Created Repository using Repository Manager.
• Worked Extensively on Informatica tools -Repository Manager, Designer and Server Manager.
• Involved in Extraction, Transformation and Loading (ETL) Process.
• Created the Source and Target Definitions using Informatica Power Center Designer.
• Imported an XML file to Designer, did some modifications, used in the Mappings and exported into an XML file.
• Developed and tested all the backend programs, Informatica mappings and update processes.
• Created and Monitored Batches and Sessions using Informatica Power Center Server.
• Tuned the mappings to increase its efficiency and performance.
• Used Informatica Workflow Manager to create workflows
• Workflow Monitor was used to monitor and run workflows
• Involved in production support which also includes trouble shooting of connectivity issues.

Environment: Informatica PowerCenter 5.1/6.2, Oracle 8i, UNIX, TOAD, SQL * Loader, Windows NT.