Data Manager

Abhishek Shrivastava **********@*****.***

Sr. ETL Informatica Developer Phone: 425-***-****

Professional Summary:

• 5+ years of total experience in Information Technology including Data Warehouse/Data Mart development using ETL/Informatica Power Center.

• Extensive experience in Banking, Insurance, Financial and Healthcare domains.

• Good exposure in overall SDLC including requirement gathering, development, testing, debugging,

Deployment, documentation and production support.

• Experience with ETL process using Informatica Power Center 9.x/8.x Power Exchange, Designer (Source Analyzer, Mapping designer, Mapplet Designer, Transformation Developer), Repository Manager, Repository Server, and Workflow Manager & Workflow Monitor.

• Experience in integration of various data sources with Multiple Relational Databases like Oracle, DB2,

Teradata, MS SQL Server and Worked on integrating data from XML files, flat files like fixed width and

Delimited.

• Excellent expertise with different types of data load strategies and scenarios like Historical dimensions,

Surrogate keys, Summary facts etc.

• Experience in the successful implementation of ETL solution between an OLTP and OLAP database in

Support of Decision Support Systems/Business Intelligence with expertise in all phases of SDLC.

• Practical understanding of the Data modeling (Dimensional & Relational) concepts like Star-Schema

Modeling, Snowflake Schema Modeling, Fact and Dimension tables.

• Good understanding of views, Synonyms, Indexes, Joins and Sub-Queries. Extensively used Cursors.

• Extensively created mapplets, common functions, reusable transformations, look-ups for better usability.

• Extensive experience in Tuning and scaling the procedures for better performance by running explain plan and using different approaches like hint and bulkload.

• Experienced with Teradata utilities FastLoad, MultiLoad, BTEQ scripting, FastExport, SQL Assistant.

• Involved in Understanding and translating the business requirements and to High Level and Low-level design for ETL processes in Teradata, Data stage and Informatica.

• Experience in Performance Tuning of mappings, ETL procedures and process and involved in complete life cycle implementation of data warehouse.

• Experienced in OLTP/OLAPsystem study, analysis and ER modeling, developing Database schemas like Star schema and Snowflake schema used in relational and multidimensional modeling by using Erwin, Visio.

• Strong Knowledge in Relational Database Concepts, Entity Relation Diagrams, Normalization and

DE normalization Concepts.

• Extensive experience in implementing CDC using Informatica Power Exchange 9.x/8.x

• Ability to write complex SQLs needed for ETL jobs and analyzing data, and is proficient and worked with

Databases like Oracle 11x/10g, SQL Server 2005, Teradata, DB2, MSSQL, Excel 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.

• Expertise in doing Unit Testing, Integration Testing, System Testing and Data Validation for Developed

Informatica Mappings.

• Effectively communicate with business, project manager and team members.

• Excellent analytical, problem solving skills and a motivated team player with excellent inter-personal skills.

WORK EXPERIENCE

McKesson Specialty Health – Scottsdale, AZ - September 2017 to Present

Responsibilities:

• Analyzed the source data arriving from different source systems like Vendor Management Inventory, Invoice

Processing System, Materials Forecasting, Purchase Orders and Requisition on Stores.

• Interacted with business teams for gathering business requirements and involved in documentation of

Specifications.

• Designed and developed the ETL Mappings for the source systems data extractions, data transformations, data staging, movement and aggregation.

• Developed standard mappings using various transformations like expression, aggregator, joiner, source

Qualifier, router, lookup, and filter.

• Developed mapplets for re-usability in ETL process.

• Involved in performance tuning and unit testing.

• Used Pipeline partitioning and External Loader to improve Session performance.

• Scheduled and ran Extraction and Load processes and monitored tasks and workflows.

• Tuned the MMW databases (stage, target) for better, faster and efficient loading and user query performance.

• Involved in creating reports using Business Objects 8.1.

Environment: Informatica Power Center 8x, Erwin 4.1, UNIX Shell Scripting, Oracle 10g, PL/SQL,

BO 8.1, Windows XP/2007.

Sr. Informatica Developer

TENNCARE - Nashville, TN - December 2016 to September 2017

Responsibilities:

• Gathered user Requirements and designed Source to Target data load specifications based on Business rules.

• Used Informatica Power Center 10.1.1 for extraction, loading and transformation (ETL) of data in the datamart.

• Designed and developed ETL Mappings to extract data from flat files, MS Excel and Oracle to load the data into the target database.

• Developing several complex mappings in Informatica a variety of PowerCenter transformations, Mapping Parameters, Mapping Variables, Mapplets & Parameter files in Mapping Designer using Informatica Power sCenter.

• Transformation mapping for inserting and updating records when loaded.

• Expertise in conversions from SQL Server to Teradata.

• Created complex mappings to load the data mart and monitored them. The mappings involved extensive

Extensively used ETL processes to load data from various source systems such as DB2, SQL Server and Flat Files, XML files into target system Teradata by applying business logic on • Use of Aggregator, Filter, Router, Expression, Joiner, Union, Normalizer and Sequence generator transformations.

• Ran the workflows on a daily and weekly basis using Active Batch Scheduling tool.

• Examined the workflow log files and assigning the ticket to the Informatica support based on the error.

• Experience in developing Unix Shell Scripts for automation of ETL process.

• Involved in doing error handling, debugging and troubleshooting Sessions using the Session logs, Debugger and Workflow Monitor.

• Performed operational support and maintenance of ETL bug fixes and defects.

• Maintained the target database in the production and testing environments.

• Created Mapping Parameters and Variables.

• Worked with Teradata utilities such as Mload, Fload, export etc. to load or extract tables.

• Supported migration of ETL code from development to QA and QA to production environments.

• Migration of code between the Environments and maintaining the code backups.

• Designed and developed Unix Shell Scripts, FTP, sending files to source directory & managing session files.

• Done extensive testing and wrote queries in SQL to ensure the loading of the data.

• Developed PL/SQL code at the database level for the new objects.

Building Reports according to user Requirement.

• Designed, developed, tested, and maintained Tableau functional reports based on user requirements.

• Developed Tableau data visualization using Pareto's, Combo charts, Heat maps, Box and Whisker charts,

Scatter Plots, Geographic Map, Cross tabs, Histograms etc.

Environment: Informatica Power Center 10.1.1, Oracle 11g, PL/SQL, Teradata, Flat files, SQL Server 2008, Erwin, UNIX. Toad 9.0, Oracle SQL developer 11g,Tableau 10.2.

ETL Developer

Southern California Edison – Los Angeles, CA - Jan 2016 to December 2016

Responsibilities:

• Experienced working with team leads, Interfaced with business analysts, third party vendors and end users.

• Interacted with various business people in Human Resource, and Global Finance to gather business

Requirements and translated them into technical specifications.

• Documented business requirements, discussed any data issues with business people to get resolved and translated the input into ETL design documents.

• Worked with data analysts to find Source, target and RDBMS table's information to implement design of

Informatica mappings, transformations, workflows, scripts and commands to meet business requirements.

• Involved in ETL process from design, development, testing and migration to production environments.

• Involved in writing the ETL test scripts and guided the testing team in executing the test scripts.

• Used UNIX Shell Scripting to invoke Sessions in the workflow manager.

• Perform UNIX code migration from one environment to another environment for application execution

• Created BTEQ is in batch mode and executed using UNIX commands.

• Extracted data from various sources like Oracle, MS SQL server, Teradata, flat files, XML files and loaded into multiple targets like MS SQL server, Oracle database and created flat files and XML files.

• Worked extensively on Teradata Database to load data in EDW and Datamart using MLOAD, FASTLOAD, TPT and BTEQ.

• Created Collect Stats scripts for Teradata.

• Created mapping using various transformations like Joiner, Filter, Aggregator, Lookup, Router, Sorter, Rank,Expression, Normalizer, Sequence Generator and Update Strategy.

• Involved in performance tuning of the ETL process by addressing various performance issues at the extraction and transformation stages.

• Extensive performance tuning by determining bottlenecks at various points like targets, sources, mappings and sessions.

• Extensively worked on Mapping Variables, Mapping Parameters, Workflow Variables, tasks and Session

Parameters.

• Developed SFTP, FTP, and Session Parameter files for the workflows.

• Worked on Data Quality to trouble shoot data issues to meet the Project requirements.

• Involved in writing SQL queries to validate the data on the source and target databases according to source to target mapping document.

• Involved in creating staging tables using People soft App designer tool.

• Developed Pre-session and Post-session scripts, MS - DOS commands and scripts to rename files and move files from one share to another share location.

• Involved in ETL code reviews and followed Company standards in developing the interfaces.

• Worked with Support team to migrate the ETL code to Production servers from development servers by

Creating a mapping document and Request for Change (RFC) of the process.

• Involved in Knowledge Transfer sessions to Support team after completion of UAT signoff.

• Handled Production issues and monitored Informatica workflows in production.

Environment: Informatica Power Center 9x, Oracle 11i/10g, Unix, MS SQL Server, Teradata, TOAD, SQL Developer, BMC CONTROL-M, HP Quality Center, Windows 7/XP professional.

ETL Developer

Credit-Suisse – New York City, NY - July 2014 to Jan 2015

Responsibilities:

• Worked on Field analyzing like identifying the business logic for each field, Participated in team meetings and proposed ETL Strategy. Worked on documenting solution and detail documents for the High-level Design Specifications for ETL Coding and mapping standards.

• Performed Unit-testing by generating SQL scripts based on the pre-defined test plans.

• Extensively used various active and passive transformations like Filter Transformation, Router

Transformation, Expression Transformation, Source Qualifier Transformation, Joiner Transformation, Look up Transformation, Update Strategy Transformation, Rank Transformation, and Aggregator.

• Created User Defined functions, reusable transformations to be re-used during the development life cycle.

• Created and implemented Informatica work flows in UNIX and Windows environments.

• Written and used UNIX shell scripts extensively for scheduling and pre/post session management

• Standardized parameter files to define session parameters such as database connection for sources targets, last updated dates for Incremental loads and many default values of fact tables.

• Performed tuning of Informatica Mappings for optimum performance and SQL overrides in place of

Homogeneous sources. Designed and coded rejected row handling process.

• Implemented Data Validation and Exception handling logic in the mapping to handle at runtime.

• Implemented Slowly Changing Dimensions Type 1 and Type 2 to update the dimensional schema.

• Added enhancements such as mail functioning, success flag, pre and post session functions etc.

• Provided detailed technical, process and support documentation like daily process rollback and detailed

Specifications and very detailed document of all the projects with the workflows and their dependencies.

• Extracting data from Database, Excel and Flat files through Informatica.

• Exposure to Agile environment.

• Created data lineage documents for data flow and ETL process using Informatica mappings to support the project once it implemented in production.

Environment: Informatica Power Center 9.1.1 (Designer, Repository Manager, Workflow Monitor, Workflow Manager), Autosys, Oracle 11g, DB2 V 9.1, Flat Files, UNIX Scripting, Windows XP.

Informatica ETL Consultant

CIBC (Canada Imperial Bank of Commerce) – Toronto, Canada - March 2010 to June 2014

Responsibilities:

• Analyzed the source data arriving from different source systems like Vendor Management Inventory, Invoice

Processing System, Materials Forecasting, Purchase Orders and Requisition on Stores.

• Interacted with business teams for gathering business requirements and involved in documentation of

Specifications.

• Designed and developed the ETL Mappings for the source systems data extractions, data transformations, data staging, movement and aggregation.

• Developed standard mappings using various transformations like expression, aggregator, joiner, source

Qualifier, router, lookup, and filter.

• Developed mapplets for re-usability in ETL process.

• Involved in performance tuning and unit testing.

• Used Pipeline partitioning and External Loader to improve Session performance.

• Scheduled and ran Extraction and Load processes and monitored tasks and workflows.

• Tuned the MMW databases (stage, target) for better, faster and efficient loading and user query performance.

• Involved in creating reports using Business Objects 8.1.

Environment: Informatica Power Center 8x, Erwin 4.1, UNIX Shell Scripting, Oracle 10g, PL/SQL,

BO 8.1, Windows XP/2007.

ADDITIONAL INFORMATION

TECHNICAL SKILLS

Informatica Tools: Informatica Power Centre 8x, 9x, 10.1.1, Informatica Master Data Management (MDM) 9.5, Informatica Data Quality 9.5 (IDQ)

Reporting tools: Tableau (Desktop/Server, Public/Reader) 10.2

RDBMS: Teradata, Oracle 9i/10g/11g, SQL Server 2008/R2/2012, TOAD 12.8

Database skills: Stored Procedures, Database Triggers, Cursor, Packages

Languages: SQL, PL/SQL, UNIX

OS: UNIX, Windows 2003/2007/XP

Tools: Control-M 8.0, Autosys R11.3, BMC Remedy 7.6, Atlassian JIRA 6.4, HP, Quality Center, Microsoft Office, Microsoft Excel, Microsoft Visio

Contact this candidate