SUMMARY

• 9+ Years of experience in Complete Software development Life cycle (SDLC) which includes Business Requirements Gathering, System Analysis, Design, Data Modeling, Development, Testing and Implementation of Data Warehouse.
• In depth expertise in building Operational database design, History Data migration, Data warehouse and OLTP/OLAP systems in Financial, Insurance and Healthcare.
• Extensively worked on Relational Databases RDBMS like Oracle 11g/10g/9i/8i, MS SQL Server, MySQL, Teradata, DB2, and Sybase. Also worked on various Source files like flat files, XML files, COBOL files, CSV files.
• Worked with Dimensional Data warehouses in Star and Snowflake Schemas, Slowly changing dimensions and created slowly growing target mappings.
• Well acquainted with Informatica Designer Components - Source Analyzer, Warehouse Designer, Transformation Developer, Mapplet and Mapping Designer.
• Worked extensively with complex mappings using different transformations like Source Qualifiers, Expressions, Filters, Joiners, Routers, Union, Unconnected / Connected Lookups and Aggregators.
• Designed complex ETL mappings like slowly changing dimensions, critical performance tweaking stages with partitioning, persistent cache and extensive use of mapplets for common sub routines.
• Knowledge on Teradata Utility scripts like FastLoad, MultiLoad to load data from various source systems to Teradata.
• Experience in Post production support, handling new enhancements and customer interaction.
• Experience in End-to-End Data warehouse Development, Performance Tuning, Optimization of SQL and Production Support
• Experience in UNIX shell scripting, CRON, FTP and file management in various UNIX environments.
• Good experience in SIT - System Integration Testing - post integration of all the modules/components of the application and UAT - User Acceptance Testing to verify whether the business requirements have been met.
• Experience in full life cycle implementations, which includes drafting implementation plan, drafting delivery note, implementing / supporting go-live and facilitating implementation sign off.
• Good experience in documenting the ETL process flow for better maintenance and analyzing the process flow.
• Proficient in having good customer interaction and co-ordination with onshore/offshore teams.

TECHNICAL SKILLS

ETL Tools: Informatica 9.x/8.x/7.x (Power Center/Power Exchange)

Data Modeling: Erwin 4.0/3.5, Star Schema, Snow Flake Schema, MS VisioRational Rose Suite (UML)

Databases: Oracle 10g/9i/8i, MS SQL Server 2008/2005, DB2, DB2/400, Teradata.

Reporting Tools: Business Objects XI R2.

Languages: T-SQL, PL/SQL, Perl, Unix Shell Script, Visual Basic, XML, Java and ASP.

DB Tools: Toad, SQL* Loader, DB Visualizer, SQL Navigator

Scheduling Tools: Control-M, JobTrac, TWS.

Operating Systems: Windows, AIX, Sun Solaris, UNIX, Linux

PROFESSIONAL EXPERIENCE

Confidential, Detroit, MI

ETL Developer

Responsibilities:

• Parsed high-level design specification to simple ETL coding and mapping standards.
• Maintained warehouse metadata, naming standards and warehouse standards for future application development Parsing high-level design spec to simple ETL coding and mapping standards.
• Worked on loading data to warehouse from data feed like Cerner, Meditech, EPIC and Semiens.
• Extracted the data from RDBMS databases (Oracle 11g, SQL Server) and load into Teradata landing area and populated onto Data warehouse.
• Perform RCA (Root Cause Analysis) of job failures
• Experience with developing complex SQL statements (Using Teradata and Oracle)
• Developed shell scripts for creating Parameter file at runtime.
• Developed mappings to extract data from various source systems and load them into target tables.
• Developed Informatica mappings with Pushdown Optimization (PDO) to load data from Staging to the EDW layer.
• Worked with various levels of software developers to load data into the data warehouse and identify potential problem areas in the source system by data profiling.
• Extensively used Control-M for scheduling the workflows and monitoring loads.
• Performed Unit testing, Integration testing and System testing of Informatica mappings.
• Prepared post production support documents and detail design documents.
• Tuning the Informatica Mappings for optimum performance.
• Prepared ETL mapping Documents for every mapping and Data Migration document for smooth transfer of project from development to testing environment and tan to production environment.
• Preparing and using test data/cases to verify accuracy and completeness of ETL process.
• Prepared Run books providing guidelines to troubleshoot the errors occurred during the run time and instructions on how to restart the loads.
• Actively involved in the production support and also transferred knowledge to the client team.

Environment: Informatica PowerCenter 9.6.1, Tableau, Teradata, Teradata SQL Assistant, Oracle 11g, SQL Server 2012, UNIX AIX and Control-M.

Confidential, Detroit, MI

Lead ETL Developer

Responsibilities:

• Interacting with the end users to get the business requirements, reporting needs and created ETL Architecture diagrams.
• Interacted with Data Modelers and Business users to understand the requirements and creating impact analysis of the new ETL on INBOUND and OUTBOUND Jobs.
• Parsed high-level design specification to simple ETL coding and mapping standards.
• Maintained warehouse metadata, naming standards and warehouse standards for future application development Parsing high-level design spec to simple ETL coding and mapping standards.
• Extracted the data from the flat files and other RDBMS databases and load into staging area and populated onto Data warehouse.
• Developed shell scripts for running all workflows in UAT to test multiple times.
• Developed mappings to extract data from various source systems and load them into target tables.
• Worked with various levels of software developers to load data into the data warehouse and identify potential problem areas in the source system by data profiling.
• Made substantial contributions in simplifying the development and maintenance of ETL by creating re-usable Mapplets, worklets and Transformation objects.
• Written SQL code to extract, transform, and load data ensuring compatibility with all tables and customer specifications.
• Created complex mappings which involved Slowly Changing Dimensions, implementation of Business Logic and capturing the deleted records in the source systems.
• Created workflows and Worklets with parallel and sequential sessions that extract, transform, and load data to one or more targets.
• Implemented Slowly Changing Dimension Type 1 and Type 2 for inserting and updating Target tables for maintaining the history.
• Extensively used TWS for scheduling the workflows.
• Performed Unit testing, Integration testing and System testing of Informatica mappings.
• Prepared post production support documents and detail design documents.
• Tuning the Informatica Mappings for optimum performance.

Environment: Informatica PowerCenter 9.5.1, Bo XI, Oracle 10g, SQL Navigator, UNIX AIX and TWS.

Confidential, Madison, WI

Sr. ETL Developer

Responsibilities:

• Prepared detailed ETL design documents explaining the mapping logic in technical as well as Business terminology.
• End-to-end ETL development of the Data Mart. Data Quality Analysis to determine cleansing requirements. Designed and developed Informatica mappings for data loads.
• Responsible for the Dimensional Data Modeling. Understand the business needs and implement the same into a functional database design
• Extensively used ETL and Informatica to load data from MS SQL Server, CSV, flat files, Cobol books into the target SQL Server database.
• Implemented various Transformations like Joiner, Aggregator, Expression, Lookup, Filter, Update Strategy, Stored Procedures, and Router etc.
• Worked on Informatica Power Center tool - Source Analyzer, Data warehousing designer, Mapping & Mapplet Designer and Transformation Designer.
• Development of Informatica mappings and also in tuning of mappings for better performance.
• Tuning Informatica Mappings and Sessions for optimum performance.
• Used Informatica features to implement Type me & II changes in slowly changing dimension tables.
• Performed Source System Data analysis as per the Business Requirement. Distributed data residing in heterogeneous data sources is consolidated onto target Enterprise Data Warehouse database.
• Made substantial contributions in simplifying the development and maintenance of ETL by creating re-usable Mapplets and Transformation objects. Partitioned the sessions to reduce the load time.
• Optimized/Tuned queries, stored procedures and mappings for better performance and efficiency.
• Designed ETL Flow diagram in Visio to show the flow of jobs in Control-M scheduler.
• Designed and developed ETL strategies and mappings from source systems to target systems. ETL strategies were designed for initial load and incremental load.
• Created complex mappings which involved Slowly Changing Dimensions, implementation of Business Logic and capturing the deleted records in the source systems.
• Tested all the modules and transported data to target Warehouse tables, scheduled, ran extraction and load process and monitor sessions and batches by using Informatica Workflow Manager and log files.
• Precise Documentation was done for all mappings and workflows.
• Responsible for ETL process under development, test and production environments. Extensively used Mapping Variables & Parameters, Workflow Variables and Session Parameters.
• Responsible for developing several complex mappings for processing the feeds, re-defined existing and developed E-Mail Tasks in the Workflow Manager for sending successful or failure messages.
• Extensively involved in capturing the incremental changes in the source systems for updating in the staging area and data warehouse respectively.
• Wrote test plans and executed it at UNIT testing and also supported for system testing, volume testing and USER testing. Also provided production support by monitoring the processes running daily.
• Used windows Scripting and Scheduled PMCMD to interact with Informatica Server from command mode
• Attending reviews, status meetings and customer interaction on functional or technical changes.
• Involved in scheduling the Informatica workflows using Jobtrac or Control-M.
• Involved in validating job flow while migrating of scheduler from Jobtrac to Control-M.
• Bug Tracking by using HP Quality Center, sharepoint and provide resolution
• Teamed together with client resources to design the to-be processes, applying standards and facilitating the industry best practices.
• ETL Workflows are executed with the standard Batch jobs which is invoke by Control-M or Jobtrac scheduler through schedule process.

Environment: Informatica Power Center 8.6/9.1, Bo XI, SQL Server 2005/2008, ERWIN, Control-M and Jobtrac.

Confidential, Chicago, IL

ETL Developer

Responsibilities:

• Interacting with the end users to get the business requirements, reporting needs and created ETL Architecture diagrams.
• Developed mappings to extract data from various source systems and load them into target tables.
• Worked with various levels of software developers to load data into the data warehouse and identify potential problem areas in the source system by data profiling.
• Made substantial contributions in simplifying the development and maintenance of ETL by creating re-usable Mapplets and Transformation objects. Partitioned the sessions to reduce the load time
• Written SQL code to extract, transform, and load data ensuring compatibility with all tables and customer specifications.
• Created complex mappings which involved Slowly Changing Dimensions, implementation of Business Logic and capturing the deleted records in the source systems.
• Used Change Data Capture (CDC) to implement Incremental Data Extraction so that only the modified and new records will be extracted to the destination rather TEMPthan full extraction
• Created workflows and Worklets with parallel and sequential sessions that extract, transform, and load data to one or more targets.
• Implemented Slowly Changing Dimension Type 1 and Type 2 for inserting and updating Target tables for maintaining the history.
• Extensively involved in Recovery process for capturing the incremental changes in the source systems for updating in the staging area and data warehouse respectively.
• Designed and developed Mapplets for faster development, standardization and reusability purposes.
• Worked on various tuning issues and fine-tuned transformations to make them more efficient in terms of performance.
• Tuned performance of Informatica Session by increasing block size, data cache size, sequence buffer length and Target based commit interval, and mappings by dropping and recreation of indexes.
• Developed PL/SQL stored procedures for database updates and to create the necessary indexes in the target tables.
• Created and Scheduled Sessions, Jobs based on demand, run on time and run only once using Workflow Manager.
• Extensively used Autosys for Scheduling the Sessions and tasks.
• Performed Unit testing, Integration testing and System testing of Informatica mappings.
• Provided post production support.
• Maintained warehouse metadata, naming standards and warehouse standards for future application development Parsing high-level design spec to simple ETL coding and mapping standards.
• Maintain Development, Test and Production mapping migration Using Repository Manager. Also used Repository Manager to maintain the metadata, Security and Reporting.
• Tuning the Informatica Mappings for optimum performance.
• Modeling and populating the business rules using mappings into the Repository for Meta Data management.

Environment: Informatica PowerCenter 8.6, Cognos 8.4, DB2 UDB 9.5, DbVisualizer 6.0.15, UNIX AIX and TWS.

Confidential, Hartford, CT

Informatica Developer

Responsibilities:

• Partly involved in gathering business requirements and worked on various Application and Business teams to develop Data Model, ETL procedures to design Data Warehouse.
• Involved in designed and developed star schema model for target database using ERWIN Data modeling.
• Imported various Application Heterogeneous Sources, created Targets and Transformations using Informatica Power Center 8.1.
• Extensively used ETL Informatica tool to extract data stored in different databases, Flat files and finally loaded into a single Data Warehouse.
• Used various active and passive transformations such as Aggregator, Expression, Sorter, Router, Joiner, connected/unconnected Lookup, and Update Strategy transformations for data control, cleansing, and data movement.
• Involved in the design and development of mappings and tuned for better performance.
• Developed complex mappings using heterogeneous sources and targets in different Relational Databases and flat files.
• Optimized/Tuned queries, stored procedures and mappings for better performance and efficiency.
• Developed various shell scripts for different purposes. For example, starting workflow, balance checks, reset sequence generators, purge database tables.
• Used pre and post session emails to monitor success or failure of sessions.
• Developed shell scripts for workflow scheduling.
• Extensively worked with Informatica Workflow Manager to Design and Develop workflows and workflow dependencies.
• Developed and scheduled Workflows and monitored the results.
• Created and used reusable Worklets, sessions and tasks and monitored them.
• Moved the Informatica objects between various environments and testing the sessions and mappings.
• Developed Technical Specs, and Release manuals.
• Involved in pre and post session migration planning for optimizing data load performance.
• Provided 24 *7 Production Support during all the production release weeks.
• Wrote test plans and executed them at UNIT testing and also supported for system testing, volume testing and USER testing.
• Also part of on call schedule to provide production support by monitoring the processes.

Environment: Informatica Power Center 8.1, Business Objects XIR2, Oracle 9i, DB2, PL/SQL, UNIX, Windows XP/2000/NT, TOAD 7.0 and Erwin 4.0

Confidential

Software Developer

Responsibilities:

• Prepared mapping transformation logic documentation as per user requirements
• Designed the ETL processes using Informatica to load data from Flat File and Oracle source into the target Oracle 8i database.
• Developed mappings using Informatica for data loading.
• Used various Informatica transformations like Lookup, Joiner, Aggregator, Expression, Filter, Router and Update Strategy for framing business logic in the mapping.
• Used Debugger to validate transformations by creating break points to analyze, and monitor Data flow.
• Worked along with the QA Team and provided production support by monitoring the processes running daily.
• Created sessions for mappings and utilized Workflow Manager & Monitor to Create, Run and Monitor workflows.
• Created PL/SQL Procedures and Views for some DW requirements.
• Performed Unit testing during the mapping developing phase to ensure proper and efficient implementation of business logic in the transformations.
• Wrote UNIX Shell Scripts and PMCMD command line utility to interact with Informatica Server from command mode.
• Generated Universe and Reports using Business Objects 5.1
• Developed customized and ad-hoc reports using Templates and Filters for Business analysis using Business Objects with features of drilling options drill up, drill down and drill anywhere

Environment: Informatica Power Center 7.1, Business Objects XIR2, Oracle 8i, SQL, PL/SQL, SQL Navigator and UNIX Shell Scripting.