RELEVANT EXPERIENCE

Senior ETL Analyst/InformaticaDeveloper with 8 years of experience with Informatica PowerCenter 9.5.1, 9.1, 8.6, 8.1.1 and in developing Administering ETL mappings with Designer Source Analyzer, Target Designer, Transformation Developer, Mapping designer, Mapplet Designer , Repository Manager, Workflow Manager, Workflow Monitor Admin console.

SKILLS INVENTORY

• Experience in data extraction, transformation and loading data using Informatica PowerCenter 9.0.1, 8.6, 8.3, 8.1/7.x Admin Console, Informatica Service, Repository Manager, Designer, Workflow Manager, and Workflow Monitor .
• Experienced in integration of various data sources like Oracle 10g/9i/8i, IBM DB2, MS SQL Server 2012/2008/2005, XML files, Mainframe COBOL sources into staging area and different target databases.
• Work on Informatica B2B exchange formerly known as complex data exchange to extract the data from the PDF source by defining the tags. Also used Informatica B2B exchange to read the unstructured data sources and load them into the target table.
• Strong understanding of data warehousing concepts, dimensional modeling, designing Star and Snowflake schemas.
• Data Modeling: 8 years of Dimensional Data Modeling experience on Data modeling, Erwin, Power Designer 12.1, ER Studio 7.6, Ralph Kimball Approach, Datamarts, OLAP, FACT Dimensions tables, Physical Logical data modeling and Oracle Designer.
• Advanced Knowledge of Oracle PL/SQL programming, stored procedures functions and tuning the SQL query Explain Pan, Hints for Optimization .
• Administration knowledge Admin Console, Repository Manager to install Informatica software, Creation of folders/Permission and upgrade the Informatica from various versions to 8.6 or 9.0.1, upgrading and migration of the repository from 7.x 8.x to 9x.
• Worked on Performance Tuning Informatica Targets, Sources, mappings and sessions using pushdown optimization, Partitioning and bulk writer Netezza bulk Writer, SQL Loader and MLOAD for Teradata
• Experienced in UNIX/Linux work environment, Shell Scripting SH, CSH, KSH to perform Pre Post Session operation, scripts to execute the workflow PMCMD .
• Implemented Informatica Data Quality 8.5 to do data cleansing, data matching, reporting and monitoring the XML files and Extensively worked on developing and debugging Informatica mappings, mapplets, sessions and workflows
• Worked on Informatica Data Transformation module DT along with DX to create new message patterns in DT along with DX .
• Worked on Incremental loading Target databases using SCD Type1/Type2 loads
• Experienced in preparing Technical design document, Business Requirement Document, Mapping documents, data definition documents, Detail design document for Source/Target mapping.

CHRONOLOGICAL SUMMARY OF EXPERIENCE

Senior ETL Analyst/Informatica Developer

Confidential

• Proposed strategic design and layout changes to improve Data Quality metrics capabilities, developed weekly/monthly reports.
• Conducted and contributed to JAD sessions for high level discussions with top level executives.
• Gathered business, system, and functional requirements by conducting detailed interviews with business users, stakeholders, and Subject Matter Experts SME's . Defined the scope of the project, financial projections and cost/benefit analysis.
• Actively interacted with other corporate departments, including executive, risk, legal, and information services as well as vendor relationships.
• Implemented the entire Rational Unified Process RUP methodology of application development with its various workflows and activities.
• Followed the UML methodology to Create UML Diagrams including Use Cases Diagrams, Activity Diagrams, Collaboration Diagrams, Data Flow Diagrams DFDs , ER Diagrams using Rational Rose and MS Visio and thus defining the Business Process Model and Data Process Model.
• Facilitated meetings with developers, system analyst and testers to collaborate resource allocation and project completion using MS Project.
• Helped design scope document and to coordinate with top management for the approval process.
• Used Rational Rose to create use cases for different scenarios and cases.
• Created UI Specifications and wire frames.
• Developed activity diagrams in Rational Rose software to have a diagrammatic view of the workflow.
• Created and managed the entire process flows and created the Requirements Traceability Matrix.
• Maintained various versions of the documents generated during the project using Rational Clear Case.
• Collaborated with QA team with User Acceptance and Manual Testing.
• Strong in Source to Target data mapping, Standardization Document Slowly Changing Mapping Creation, Star/Snowflake Schema Mapping Creation, RDMS, Building Data Marts and Meta Data Management.
• Created the Logical and Physical design of the Data Warehouse both Fact and Dimension tables using STAR Schema approach.
• Created DDL and DML scripts for complete analysis on data in the tables.
• Developed ETL process to define imports of the data, build scheduling packages, define integration flow, log data error exceptions and orphaned data, define database maintenance plans and send email notifications.
• Developed BI reports comprising line and pie charts, trend analysis reports and sub-reports according to business requirements, using SQL Server Reporting Services SSRS .
• Developed several stored procedures using T-SQL for data manipulation.
• Upgraded Informatica 9.5.1 Standard Edition to Informatica 9.6.0 Advanced Edition successfully without any defects/issues for 4 Environments. Configured Repository Service, Integration Service, Web Service, Model Repository Service, Data Integration Service, Analyst Service, Content Management Service, Metadata Manager Service.
• Developed database monitoring and data validation reports in SQL Server Reporting Service SSRS .

Environment:Informatica 9.6.0 9.5.1, SSRS, SSIS, Informatica Data Quality 9.6.0 9.5.1, Oracle 11g, UNIX, MS SQL Server, MDM, Teradata, MS Access, PL/SQL, My SQL and flat files, HP ALM, Microsoft Dynamics AX.

Senior ETL Analyst/Informatica Developer

Confidential

• Responsible for writing requirements for automation of online contract management tool.
• Involved in the UAT testing of the Items Data Mart price files tool automation project.
• Responsible for handling Supply Chain Management Data Warehouse and related data base and online solutions.
• Work on writing use cases and requirements for Supply Chain Management data upload automation process.
• Involved in maintenance of reporting tools and processes utilizing Data Warehouse data.
• Worked with user support group to incorporate pharmacy contracts in the SCM Contract Catalog.
• Responsible for generating pricing reports from Finance Procurement Data Warehouse and Data Mart using Business Objects.
• Responsible for managing the online contract management repository and items data mart pricing information.
• Work on formulating the ETL rules using Informatica and Ab Initio.
• Used Informatica Power Center 9.0.1for migrating data from various OLTP databases to the data mart.
• Worked with different sources like Oracle, flat files, XML files, DB2, MS SQL Server
• Extracted data from Sales department to flat files and load the data to the target database
• Developed complex mappings using Informatica to load Dimension Fact tables as per STAR schema techniques.
• Extracted data from sources like fixed width and Delimited Flat files transformed the data according the business requirement and then loaded into Target Data mart.
• Created various Informatica mappings and mapplets to load the data mart. The mappings involved extensive use of transformations like Aggregator, Filter, Router, Expression, Joiner, Sequence generator.
• Configured the mappings to handle the updates to preserve the existing records using Update Strategy Transformation SCD Type-2 .
• Used Debugger to identify the errors in the mappings and fix them
• Performed unit testing to validate mappings and populate the database
• Used TOAD and SQL Plus to write queries and interact with Oracle database
• Implemented several different tasks Session, Assignment, Command, Decision, Timer, Email, Event-Raise, Event-Wait, Control in the workflow
• Involved in writing UNIX scripts and used them to automate the scheduling process
• Error Handling handled in Informatica Mappings sessions and also through validation script.
• Co-ordinated with DBA Team, Functional Team and offshore teams.
• Automation of job processing, establish automatic email notification to the concerned persons.
• Written documentation to describe program development, logic, coding, testing, changes and corrections.
• Used SQL Trace, Analyze, Explain Plan Utilities, and Hints for the performance tuning
• Managed the Metadata associated with the ETL processes used to populate the data warehouse.
• Tested the data and data integrity among various sources and targets. Associated with Production support team in various performances related issues.
• Worked with business users and gather data cleansing, data reconciliation and data convergence requirements
• Involved in Designing and developing the OBIEE/Siebel Metadata Repository .rpd using OBIEE/Siebel Analytics Admin tool by importing the required objects Dimensions and Facts with integrity constraints into Physical Layer using connection pool, developing multiple Dimensions Drill-Down, Hierarchies Logical Facts / Measures objects in Business Model Layer, and creating the Presentation catalogs in Presentation Layer.

Environment: Informatica PowerCenter 9.0.1 Informatica Services, Repository Manager, Designer, Workflow Manager, and Workflow Monitor , Data explorer, Oracle 10g, , SQL Server 2012, PL/SQL, Flat files, PLSQL Developer.

Senior Informatica Developer

Confidential

• Collection of requirements from business users and analyzed based on the requirements.
• Extensively used XML files as source and designed and developed complex Informatica mappings using expressions, aggregators, filters, lookup and stored procedures to ensure movement of the data between various applications.
• Designed and developed advanced mappings using various transformations in Designer to extract the data from sources like Oracle, SQL Server, XML files and flat files to perform mappings based on company requirements and load into Oracle tables.
• Performance Tuned Informatica Targets, Sources, mappings and sessions for large data files by increasing data cache size, sequence buffer length and target based commit interval.
• Extensively worked in the performance tuning Range and round robin partitioning and optimization technique . Error checking and testing of the ETL procedures and programs using Informatica session log.
• Well versed with Database operation DML, DDL and DCL like creating and altering Table, Triggers, Sequences and other utilities of Oracle 9i and 10g
• Worked on Informatica B2B to read the unstructured XML files and load the data into the target table.
• Created XSD for XML files, imported into Designer as XML source qualifier
• Developed UNIX shell scripts to automate applications, Schedule jobs, develop interfaces and Created pre-session and post-session shell scripts and mail-notifications
• Performed error handling for removing database exceptional errors
• Used Debugger utility of the Designer tool to check the errors in the mapping and made appropriate changes in the mappings to generate the required results
• Worked with tools like TOAD, WINSQL and PLSQL Developer to write queries and generate the result
• Provided Knowledge Transfer to the end users and created extensive documentation on the design, development, implementation, daily loads and process flow of the Informatica mappings

Environment: Informatica PowerCenter 8.6 Informatica Services, Repository Manager, Designer, Workflow Manager, and Workflow Monitor , Mainframe, Solaris 10, Oracle 10g, Informatica PowerExchange, SQL Server 2008, PL/SQL, Flat files, XML Files

Informatica Developer

Confidential

• Involved in the design, development and implementation of ETL process using Informatica.
• Generated workflows using reusable tasks, worklets and workflows containing sessions, commands, event raise/wait, decision and timers for flow control
• Involved in optimization of ETL programs and SQL queries overriding SQL query in order to increase performance
• Worked with relational sources and flat files
• Involved in rigorous testing of ETL mappings
• Extensively involved in monitoring the jobs in order to detect and fix unknown bugs and track performance
• Provided excellent Informatica production support
• Used Debugger to test the mappings and fix the bugs
• Implemented Informatica Data Quality 8.5 to do data cleansing, data matching, reporting and monitoring the XML files

Environment: Windows XP, Informatica Power Center 8.1.x, DB2 8.1, WinSql 5.5, UNIX

Informatica Developer

Confidential

• Worked as Informatica Developer to design and develop Informatica mapping. To perform the Extraction, Transform and Load process by studying the business requirement from the users and the mapping documents
• Extensively used COBOL copy book to import the VSAM files from the Mainframe Source. The Mainframe flat file lists were indirectly accessed and implemented mapping with parameters and variables
• Designed and developed complex mappings using various transformations in Designer to extract the data from sources like Oracle, SQL Server, XML files and flat files to perform mappings based on company requirements and load into Oracle tables
• Well versed with creating and Altering Table, Triggers, Sequences, and other DDL, DML and DCL utilities of Oracle 9i and 10g
• Created XSD for XML files, imported into Designer as XML source qualifier
• Performance Tuned Informatica Targets, Sources, mappings and sessions for large data files by increasing data cache size, sequence buffer length and target based commit interval
• Created pre-session and post-session shell scripts and mail-notifications
• Performed error handling for removing database exceptional errors
• Used Debugger utility of the Designer tool to check the errors in the mapping and made appropriate changes in the mappings to generate the required results
• Provided Knowledge Transfer to the end users and created extensive documentation on the design, development, implementation, daily loads and process flow of the Informatica mappings

Environment: Informatica PowerCenter 8.1.1 Informatica Services, Repository Manager, Designer, Workflow Manager, and Workflow Monitor , Mainframe, Solaris 10, Oracle 10g, Informatica PowerExchange, SQL Server 2008, PL/SQL, Flat files, XML Files, ERwin 4.0, TOAD 8.6

Informatica Developer

Confidential

• Involved in the design, development and implementation of the Enterprise Data Warehouse EDW
• Worked on ETL Migration process from legacy scripts to Informatica PowerCenter 7.1.1
• Involved in the creation of logical and physical designs using ERwin data modeler to develop Database schemas in dimensional modeling
• Worked on Informatica PowerCenter Designer - Source analyzer, Warehouse Designer, Mapping Designer and Transformation developer
• Used various Informatica transformations to recreate data in the data warehouse
• Involved in Data cleansing before loading into Oracle data warehouse
• Designed and developed Aggregate, Join, Lookup transformation rules business rules to generate consolidated fact/summary data identified by dimensions
• Used Lookup, Sequence generator, Router and Update Strategy transformations to insert, delete, update the records for Slowly Changing Dimension tables
• Worked on change data capture for slowly changing dimensions SCD-2 to update a target table to keep full history
• Extensively used Mapplets and Reusable Transformations to prevent redundancy of transformation usage and maintainability
• Automated scheduling of sessions at regular intervals using Informatica Scheduler
• Worked on identifying Mapping Bottlenecks in Source, Target and Mappings to Improve performance
• Extensively used debugger to troubleshoot logical errors
• Used workflow manager for session management, database connection management and scheduling of jobs
• Monitored workflows and session using PowerCenter workflow monitor

Environment: Informatica PowerCenter 7.1.4 Informatica Server, Repository Server, Repository Manager, Designer, Workflow Manager and Workflow Monitor , Windows XP, UNIX, Solaris 9, Oracle 9i, MS SQL Server 2000, XML, Flat files, ERwin, TOAD

Informatica Developer

Confidential

• Analyzed the systems, architecture, met with end users and business units in order to define the requirements.
• Involved in the requirement definition and analysis in support of Data Warehousing efforts.
• Developed ETL mappings, transformations using Informatica PowerCenter 6.x.
• Extensively used Informatica tools Source Analyzer, Warehouse designer, Mapping Designer, Mapplet Designer, Transformation Developer.
• Implemented various Informatica transformations like Expression, filter, Aggregator, Router, Ranker, update strategy, joiner, Lookup, Sequence generator, stored procedure and Source Qualifier. To load the data from Staging area to DW.
• Developed data Mappings between source systems and warehouse components using Mapping Designer.
• Involved in analyzing source systems and designing the processes for Extracting Transforming and Loading the data.
• Responsible for creating DLL for report design in HTML format and calling them from ASP pages to provide more security for Crystal Reports.
• Involved in the Performance Tuning.
• Created and ran sessions and workflows using Workflow Manager.
• Monitored the sessions and workflows using Workflow Monitor.
• Trouble shooting of connectivity problems. Looked up and read session, event and error logs for troubleshooting.

Environment: Informatica PowerCenter 7.1 Informatica Server, Repository Server, Repository Manager, Designer, Workflow Manager and Workflow Monitor , Oracle 9i, DB2, Sybase, Cobol files, MS SQL Server 2005, TOAD, UNIX, PL/SQL, SQL Loader, Windows 2000/XP

Database Developer

Confidential

• Written SQL Queries for handling data in the database.
• Maintaining the data coming from the OLTP systems.
• Tuned the database queries to improve the performance of the query and indexing strategies.
• Coded database triggers to record the changes made to the database objects.
• Coded functions and stored procedures to perform the user defined functions and execute the required query whenever needed.
• Document the work performed each day.
• Helped coding shell scripts for database scripting.
• Establishing an application's security measures during development of Application in collaboration with DBAs.
• Involved in Physical schema implementation, objects like table-space, table rollback segments.
• Created database structures, objects and their modification as and when needed.
• Managed users roles for database security.
• Maintained system security, control and monitor user access to database
• Assigned predefined profiles and roles to the users to maintain database security, CPU activity, idle time and quota management on table-spaces.

Environment: Oracle 9i, SQL Plus, PL/SQL, T-SQL, Windows XP