SUMMARY

• About 8 years of experience in Information Technology with a strong background in Database development and Data warehousing (OLAP) and ETL process using Informatica Power Center 9.x/8.x/7.x/6.x, Power Exchange, Designer (Source Analyzer, Warehouse designer, Mapping designer, Metadata Manager, Mapplet Designer, Transformation Developer), Repository Manager, Repository Server, Workflow Manager & Workflow Monitor.
• Experience in integration of various data sources with Multiple Relational Databases like DB2, Oracle, SQL Server and Worked on integrating data from XML files, flat files like fixed width and delimited.
• Involved in the data analysis for source and target systems
• Implemented performance tuning techniques at application, database and system levels
• Experience in UNIX shell programming.
• Understanding of Relational (ROLAP), Multidimensional (MOLAP) modeling, data warehousing concepts, star and snowflake schema, database design methodologies and Meta data management.
• Experience with preparing functional Specifications, Low Level and High Level Design specification and Source to target mapping documents for the ETL programs/business processes
• Experience in working with Oracle Stored Programs, Packages, Cursors, Triggers, Database Link, Snapshot, Roles, Privileges, Tables, Constraints, Views, Indexes, Sequence, and Synonyms Dynamic SQL and SQL*Loader in distributed environment.
• Experience in using Informatica to populate the data into Teradata DWH.
• Experience in SQL tuning using Hints, Materialized Views.
• Experience in understanding Fact Tables, Dimension Tables, Summary Tables
• Exposure to development, testing, debugging, implementation, documentation, user training & production support.
• Ability to work effectively in a supervisory and non supervisory environment as a team member as well as an individual.
• Have good experience with onsite and offshore coordination
• Experience with 24x7 production support

TECHNICAL SKILLS

ETL: Informatica 9.5/9.0/8.6/8.5/8.1.1/7.1.2/6.1 , Data Stage 7.5/6.0, SSIS

Reporting Tools: Business Objects XI 3.1/r2, r1/6.x/5.x, Web Intelligence, Crystal Reports 8.X/10.X

Database Modeling: Erwin 4.0, Rational rose

Databases: Oracle 11g/10g/9i/8i/7.x, DB2, Teradata v 13/v12/v2r5, SQL Server 2012/2008/2005

Languages: SQL, COBOL, PL/SQL, JAVA, C

DB Tools: SQL* plus, SQL* Loader, SQL*Forms, TOAD

Web Tools: HTML, XML, JavaScript, Servlets, EJB

OS: MS - WINDOWS NT/2000/2003, UNIX, Linux

PROFESSIONAL EXPERIENCE

Confidential, Chicago, IL

Sr. Informatica Developer

Responsibilities:

• Coordinated with business analysts and data architecture team to ensure compliance with standards and consistency with long-term infrastructure plans
• Prepared Detail Design specification and functional Specifications for the ETL programs/business processes
• Designed and developed mappings/mapplets/workflows to load migrated data and execute converted transformation processes from legacy system to Enterprise Data Warehouse.
• Designed and developed complex Type 1 and Type 2 mappings for the stage to integration layer in the project.
• Involved in Design Review, code review, test review, and gave valuable suggestions.
• Worked extensively with different Caches such as Index cache, Data cache, Lookup cache (Static, Dynamic and Persistence) and Join cache while developing the Mappings.
• Created partitions for parallel processing of data and also worked with DBAs to enhance the data load during production.
• Performance tuned informatica session, for large data files by increasing block size, data cache size, and target based commit
• Developed simple & complex mappings using Informatica to load Dimension & Fact tables as per STAR schema techniques.
• Worked on CDC (Change Data Capture) to implement SCD (Slowly Changing Dimensions).
• Worked with data Extraction, Transformation and Loading data using BTEQ, Fast load, Multiload
• Used the Teradata fast load/Multiload utilities to load data into tables
• Used Teradata SQL Assistant to build the SQL queries
• Involved in writing a procedure to check the up-to-date statistics on tables.
• Used Informatica command task to transfer the files to bridge server to send the file to third party vendor.
• Used BTEQ, Unix Perl scripting to trigger the chron jobs having high impact against the production database during non-prime time.
• Did data reconciliation in various source systems and in Teradata.
• Took part in migration of jobs from UIT to SIT and to UAT
• Created FTP scripts and Conversion scripts to convert data into flat files to be used for Informatica sessions
• Involved in Informatica Code Migration across various Environments.
• Performed troubleshooting on the load failure cases, including database problems.
• Created database objects like Tables (also global/volatile), macros, views, and procedures.
• Documented all the designs from source to stage, stage to integration and integration to atomic layer.
• Co-ordinate with the off-shore team and mentored junior developers

Environment: Informatica Power Center 9.5/9.1, Oracle 11g, Teradata V 13.0, Fast load, Multiload, Teradata SQL Assistant, MS SQL Server 2012, SQL, PL/SQL, T-SQL, SQL*Plus, TOAD, Erwin, AIX, Shell Scripts, Auto sys, Unix

Confidential, Dallas, TX

Sr. Informatica Developer

Responsibilities:

• Collaborated with Business analysts for requirements gathering, business analysis and designing of the Enterprise Data warehouse
• Involved in requirement analysis, ETL design and development for extracting data from the heterogeneous source systems like DB2, UDB, MS SQL Server, Oracle, flat files, XML files and loading into Staging and Teradata Data Ware House
• Involved in massive data cleansing prior to data staging.
• Maintained warehouse metadata, naming standards and warehouse standards for future application development.
• Designed Type1 and Type2 mappings
• Extensively used Informatica client tools Source Analyzer, Warehouse designer, Mapping Designer, Mapplet Designer, Transformation Developer, Informatica Repository Manager and Informatica Workflow Manager.
• Design and Development of ETL routines, using Informatica Power Center within the Informatica Mappings, usage of Lookups, Aggregator, Java, XML, Ranking, Mapplets, connected and unconnectedstoredprocedures / functions / Lookups, SQL overrides usage in Lookups and sourcefilter usage in Sourcequalifiers and data flow management into multiple targets using Routers were extensively done.
• Configured workflows with EmailTask, which would send mail with session, log for Failure of a sessions and for Target Failed Rows.
• Used ServerManager to create schedules and monitor sessions. And to send the error messages to the concerned personal in case of process failures.
• Extensively used pmcmd commands on command prompt and executed Unix Shell scripts to automate workflows and to populate parameter files.
• Worked on loading of data from several flat files sources to Staging using Teradata TPUMP, MLOAD, FLOAD and BTEQ.
• Working with Teradata ETL tools like Fast Load, Multi Load, Tpump and Fast Export.
• Used Teradata system Priority Schedule in controlling the load of the system.
• Teradata PMON is used to monitor the system performance when it is in load
• Performance Tuning, Query optimization (Explain plans, Collect statistics, Primary and Secondary indexes).
• Build tables, views, UPI, NUPI, USI and NUSI.
• Designed and developed the logic for handling slowly changing dimension table’s load by flagging the record using update strategy for populating the desired.
• Documented ETL test plans, test cases, test scripts, test procedures, assumptions, and validations based on design specifications for unit testing, system testing, expected results, preparing test data and loading for testing, error handling and analysis.
• Involved in Unit testing, User AcceptanceTesting to check whether the data is loading into target, which was extracted from different source systems according to the user requirements.

Environment: Informatica Power Center 8.6.1, Oracle 11g, Teradata V12.0R2, Teradata SQL Assistant 12.0, BTEQ, Fast Load, Multiload, Fast Export, MS SQL Server 2008, SQL, PL/SQL, T-SQL, SQL*Plus, TOAD, Erwin, Unix, Oracle Applications 11i, Sun Solaris

Confidential, Wilmington, DE

Sr. Informatica Developer

Responsibilities:

• Involved with requirement gathering and analysis for the data marts focusing on data analysis, data quality, data mapping between staging tables and data warehouses/data marts.
• Designed and developed processes to support data quality issues and detection and resolutions of error conditions.
• Working with the Business Analysts and the QA team for validation and verification of the development.
• Extract data from flat files, Oracle, DB2, Mainframe files, SQL Server 2008, and to load the data into the Enterprise Data warehouse.
• Wrote SQL scripts to validate and correct inconsistent data in the staging database before loading data into databases.
• Analyzed the session logs, bad files and error tables for troubleshooting mappings and sessions.
• Implemented various scenarios related to slowly growing targets and slowly changing dimensions(Type1, Type2, Type3)
• Implemented various business rules of data transformations using various Informatica transformations like Normalizer, Source Qualifier, Update Strategy, Look up(connected/unconnected/static cached/dynamic cached), Sequence Generator, expression, Aggregator, XML(source and generator), Stored Procedures.
• Worked with newer Informatica transformations like Java transformation, Transaction Control.
• Provided administrative functions like creating repositories, backing up repositories, setting up users, assigning permissions and setting up folders in Repository manager.
• Wrote shell script utilities to detect error conditions in production loads and take corrective actions, wrote scripts to backup/restore repositories, backup/restore log files.
• Involved with performance tuning of Oracle database - using TKProf utility, working with partitioned tables, implementing layer of materialized views to speed up lookup queries, working with Bitmap indexes for dimension tables, DBMS Stats package to update statistics, using SQL hints.
• Scheduled workflows usingautosys job plan.
• Did QA of ETL processes, migrated Informatica objects from development to QA and production using deployment groups.
• Provided production support and involved with root cause analysis, bug fixing and promptly updating the business users on day-to-day production issues.
• Co-ordinated with the off-shore teams and mentored junior developers.

Environment: InformaticaPower Center 8.6.1, Oracle 10g, Autosys, Erwin 4.5, CMS, TOAD 9.0, SQL, PL/SQL, UNIX, SQL Loader, MS SQL Server 2008.

Confidential, Gardena, CA

Sr. Informatica Developer

Responsibilities:

• Responsible for gathering the requirements both functional and technical and documentation.
• Worked with the business analysts in requirement analysis to implement the ETL process
• Created Data dictionary using ERWIN modeler.
• Involved in LowlevelDesign for the scripts of the database sequences, constraints, triggers and stored procedures.
• Creating Low level documents for creating maps to load the data from the ODS through the warehouse.
• Merging several flat files into one XML file.
• Created mappings, workflows for Confidential extended services north America NESNA and Confidential acceptance holding company NAHC
• Used loading techniques like Slowly Changing Dimensions and Incremental Loading using parameter files and mapping variables
• Used the Power Center client tools Designer, Workflow Manager, and Workflow Monitor
• Worked with various Active transformation like Filter, Sorter, Aggregator, Router, and Joiner transformations
• Worked with Slowly Changing Dimensions Type1, Type2, and Type3 for Data Loads
• Tested Informatica 8.1 with all functionalities for migration of Informatica 6.2/7.1.2 to 8.1
• Migrated repository & folders from 7.1.2 to 8.1
• Developed batch file to automate the task of executing the different workflows and sessions associated with the mappings.Created workflows using Workflow manager for different tasks like sending email notifications, timer that triggers when an event occurs, and sessions to run a mapping.
• Used designer debugger to test the data flow and fix the mappings. Tuned Informatica Mappings and Sessions for optimum performance
• Implemented integration of Datamart with Weblogic Server for creating connection pools and Data Sources of Oracle, SQL drivers.
• Responsible for Performance Tuning at the Mapping Level, Session Level, Source Level and Target Level
• Worked with the Joiner transformation using Normal Join, Master outer join, Detail Outer Join and Full Outer Join
• Worked with Connected look up and un connected lookup and configured the same for implementing complex logic
• Designed Mappings using Informatica Designer to load incrementally.
• Created re-usable transformations and mapplets
• Responsible for determining the bottlenecks and fixing the bottlenecks with performance tuning
• Worked with Session Logs, and Workflow Logs for Error handling and Troubleshooting in DEV environment
• Involved in Unit Testing, User Acceptance Testing (UAT) to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
• Worked as an onshore-offshore coordinator

Environment: Informatica Power center 8.1/7.1.4, Informatica Power Exchange, Oracle 9i/10g, Sql Server 2005, Toad 8.6, DB2, Unix AIX 5.2, ERWIN 4.0, Flat Files, Hyperion 8.3

Confidential, Kansas

Sr. ETL Consultant

Responsibilities:

• Worked with users to understand their reporting requirements and translate those requirements to extract data, and load data in the form of a report.
• Analyzed business and systems specifications and developed logic flowcharts
• Designed and developed mappings using Source Qualifier, Expression, Lookup, Router, Aggregator, Filter, Sequence Generator, Stored Procedure, Java, Update Strategy, joiner and Rank transformations.
• Implemented Informatica Power Center for building Star Schema in Oracle Data Warehouse from different OLTP systems
• Implemented bulk load method with SQL Loader for loading history data to staging area.
• Defined UNIX Batch scripts for automation of execution of Informatica workflows.
• Executed Multi load scripts for daily batch jobs.
• Responsible to tune ETL procedures and schemas to optimize load and query Performance.
• Implemented business rules by using database triggers.
• Generated Drill Up, Drill Down and Drill Through reports using Business objects based on user requirements.
• Improved Application performance by fine-tuning application using TKPROF and EXPLAIN PLAN
• Created several materialized views for reporting purpose and better performance.
• Expertise in setting up UNIXcronjobs using cron tab in UNIX.
• Scheduled informatica jobs using AutoSys scheduler to run at regular intervals.
• Experience in SQL tuning using Hints, Materialized Views.
• Extensively worked in the performance tuning for the mappings and ETL Procedures both at designer and session level.
• Using Dynamic SQL and SQL*Loader in distributed environment.
• Expertise in working with Oracle Stored Programs, Packages, Cursors, Triggers, Database Link, Snapshot, Tables, Constraints, Views, Indexes, Sequences.
• Co-ordinated successfully with the offshore clients.

Environment: Informatica Power Center 7.1.2, Oracle 10g, SQL, PL/SQL, Business Objects, UNIX, Shell Scripts, TOAD, AutoSys, Sun Solaris.

Confidential

ETL Consultant

Responsibilities:

• Requirement gathering and Understand the Functional Business processes and Requirements given by the Business Analyst.
• Involve in Designing High level Technical Documentation based on specification provided by the Manager
• Involved in writing Shell Scripting for ETL JOBS to run.
• Power Exchange Change Data Capture has been done for data updates
• Technical Documentation has been done for all the mappings for presentation with Business logic
• Worked with tuning the SQL statements and database.
• Involved in Unit testing, User Acceptance Testing to check whether the data is loading into target, which was extracted from different source systems according to the user requirements.
• Maintaining issue log of the issues during the UAT phase for future reference.
• Actively involved in the production support and also transferred knowledge to the other team members.
• Used Debugger to test the mapping and fixed the bugs.
• Tuned the mappings and workflows to improve performance.
• Developed Shell Scripts to automate file manipulation and data loading procedures. \Worked on SQL stored procedures, functions and packages in Oracle.
• Involved in creating Dimensional Model for building the Data Warehouse that would integrate data from various sources.
• Created and used different tasks like Decision and email tasks.

Environment: Informatica Power Center 7.1, IBM DB2, Mainframes, Oracle 9i, SQL, PL/SQL, UNIX

Confidential

DW Developer

Responsibilities:

• Created Stored Procedures to transform the Data and worked extensively in PL/SQL for various needs of the transformations while loading the data.
• Used Informatica -Designer for developing mappings, using transformations, which includes aggregation, Updating, lookup, and summation. Developed sessions using Server Manager and improved the performance details.
• Created transformations like Aggregate, Expression, Filter, Sequence Generator, Joiner, and Stored procedure transformations.
• Created reusable transformations called mapplets and used them in mappings in case os reuse of the transformations in different mappings.
• Involved in the process design documentation of the Data Warehouse Dimensional Upgrades. Extensively used Informatica for loading the historical data from various tables for different departments.
• Involved in creating Technical Specification Document (TSD) for the project.
• Involved in gathering business requirements, logical modeling, physical database design, data sourcing and data transformation, data loading, SQL and performance tuning.
• Involved in the development of Data Mart and populating the data marts using Informatica.
• Created sessions to run the mappings. Created mapplets to improve the Performance.
• Worked with offshore clients and maintained a good relation with them.

Environment: Informatica 6.2, Oracle 8i, PL/SQL, SQL, Sybase, Cognos, Windows 2000