Amit Sathvara

Sr. ETL Informatica Lead/Developer

SUMMARY

• 8 years of experience in designing, developing and maintaining large business applications such as data migration, integration, conversion, data warehouse and Testing.
• Experience includes thorough domain knowledge of Business Financial system, Banking, healthcare information technology, Insurance Reinsure, Pharmacy claims systems, Telecom Industry.
• Professional understanding of Software Development Life Cycle SDLC .
• Expertise in data warehousing, ETL architecture, Data Profiling using Informatica Power enter 8.6/8.5/8.1/7.1 Client and Server tools.
• Through knowledge of Relational Dimensional models Star Snow Flake , Facts and Dimension tables, Slowly Changing Dimensions SCD
• Hands on experience in implementing Slowly Changing dimension types I, II III methodologies
• Experience in integration of various data sources like SQL Server, Oracle, Tera Data, Flat files, DB2 Mainframes into the staging area
• Business Requirements review, assessment, gap identification, defining business process, deliver project roadmap including documentation, initial source data definition, mapping, detailed ETL development specifications and operations documentation
• Expert in Installation and configuration of Informatica server with Sql server , Oracle and able to handle the Informatica administrator tasks like Configuring DSN, creating Connection Strings, copying moving mappings, workflows, creating folders etc,.
• Strong experience in complex PL/SQL packages, functions, cursors, triggers, views, materialized views, T-SQL, DTS, and SSIS.
• Expert in troubleshooting/debugging and improving performance at different stages like database, workflows, mapping
• Experience in writing UNIX shell scripts and Perl scripts
• Expert in design and development of Business Intelligence reports using BI tools Business Objects, and knowledge in Microstrategy
• Have Got Proven experience leading Team.
• Enthusiastic and goal-oriented team player possessing excellent communication, interpersonal skills and leadership capabilities with high level of adaptability.

TECHNICAL SKILLS:

PROFESSIONAL EXPERIENCE

Confidential

Sr. ETL Developer / Lead

The client is implementing enhancements into the existing Enterprise Data Warehouse.These new cubes that are added will enable the client to identify loyal customers and serve them better. It will also help them to analyze and implement strategies to expand their customer base and provide better deals to the existing ones. The data will also be funneled to various calculation engines and data-marts needed for internal reporting.

Responsibilities:

• Experience in Data Warehouse/Data Mart design, System analysis, Database design, ETL design and development, SQL, PL/SQL programming.
• Created mapping between the databases and identified the possibilities of incorporating the new business rules with the existing design.
• Created prototype reporting models, specifications, diagrams and charts to provide direction to system programmers.
• Prepared High level design HLD , LLD, Project functional Specification Document and Business Requirement documentation BRD .
• Participated in Design team brainstorm and user requirement gathering meetings.
• Played the lead role in Designing and implementing Pricing Analytics, Best Customer and Finance Master Data Alignment Services Data marts.
• Involved in managing the delivery of data extracts from the data sources to the warehouse and downstream applications.
• Involved in requirement gathering and database design and implementation of star-schema, snowflake schema/dimensional data warehouse using Erwin.
• Played the role of a Data warehouse architect in the Business Intelligence Enterprise Architect group for providing data architectural strategies.
• Involved in Architecture and design of Data extraction, transformation, cleaning and loading.
• Involved in Requirement gathering and source data analysis for the Data warehousing projects.
• Involved in the Logical and Physical design and creation of the ODS and data marts.
• Converted the Business rules into Technical Specifications for ETL process.
• Involved in all phases of SDLC from requirement, design, development, testing, pilot, training and rollout to the field users and support for production environment.
• Implemented mapping techniques for Type 1, Type 2 and Type 3 slowly changing dimensions.
• Developed ETLs for Data Extraction, Data Mapping and Data Conversion using Informatica Power Center.
• Involved in the Development of Informatica mappings and mapplets and also tuned them for Optimum performance, Dependencies and Batch Design.
• Worked on Informatica Power center 9.5 Tool Source Analyzer, warehouse designer, Mapping Designer, Workflow Manager, Mapplets, Worklets and Reusable Transformation.
• Wrote scripts for collection of statistics, reorganization of tables and indexes, and creation of indexes for enhancing performance for data access.
• Involved in Testing and Test Plan Preparation, Process Improvement for the ETL developments.
• Involved in Data Quality Assurance, created validation routines, unit test cases and test plan.
• The design document was finalized through conduct of design walkthroughs, and independent review by the user. Prepared Technical Specifications for all the utilities, as per the company's standards.
• 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.
• Prepared Technical documents for all the modules developed by our team.

Environment: Erwin, Informatica Power Center 9.5, SQL server 2008, SeaQuest Database HP Internal DB , Dial HP Internal DB , Yotta HP Internal tools

Confidential

Sr. Informatica Developer

Confidential a global leader in media and entertainment with businesses in television networks, film and TV entertainment and publishing, uses its industry-leading operating scale and brands to create, package and deliver high-quality content worldwide through multiple distribution outlets. The primary objective of the project was to read the data from Netezza and generate and send the XML file to Kenexa 2xBrassRing by invoking web services.

Responsibilities:

• Worked with the business and data analysts in requirements gathering and to translate business requirements into technical specifications.
• Understand and discuss the ETL requirements with the Business Unit and preparing the detail design documentation according to the standards.
• All the jobs are integrated using complex Mappings including Mapplets and Workflows using Informatica power center designer and workflow manager. Ver. 9.1
• Used the transformations like Expression, Lookup, Source Qualifier, Transaction Control, XML Generator, Web Service, XML Parser, Joiner, etc.
• Extracted the high volume dataset from the XML, Netezza Relational Tables and xml targets.
• Used Workflow manager for session management, database connection management and scheduling of jobs to be run in the batch process.
• Use debugger in identifying bugs in existing mappings by analyzing data flow, evaluating transformations and Create Mapplets that provides reusability in mappings.
• Involved in Performance tuning at source, target, mappings, sessions, and system levels.
• Performance tuning has been done to increase the through put for both mapping and session level and SQL Queries Optimization as well.
• Monitor and troubleshoot batches and sessions for weekly and Monthly extracts from various data sources across all platforms to the target database.
• Data Validation has been done for various sources to target mappings.
• Migrated ETL objects from DEV to UAT to PRD environments using Repository Manager.
• Provided support and quality validation thru test cases for all stages Unit and Integration testing.
• Prepared ETL Design-Document and Implementation Plan documents.
• Involved in Unit Testing and created various test cases and reviewed and fixed issues in the ETL code.
• Supported UAT and resolved issues. Also, worked on CR's change request by the business.

Environment: Informatica Power Center 9.1, Oracle 9i, DB2, SQL, PL/SQL, Netezza Database, Remedy.

Confidential Management division is one of the largest publicly traded global asset management firms in the world. The primary objective of the project was to construct an aggregated data warehouse DWH for analytics and reports used by business, risk management, asset management groups.

Responsibilities:

• Worked with the business and data analysts in requirements gathering and to translate business requirements into technical specifications.
• Load Monthly and Daily loads, track issues and resolve them based on the priority.
• Used Debugger to test the mappings and fixed the bugs.
• Used Workflow manager for session management, database connection management and scheduling of jobs to be run in the batch process.
• Extracted the data from the Flat files, Excel sheets, Oracle databases into staging area and populated onto Data warehouse.
• Used the transformations like Expression, Lookup, Source Qualifier, Normalizer, Aggregator, Filter, Router, Sorter, etc.
• Created number of complex Mappings, Mapplets, Reusable Transformations, Workflows, Worklets, Sessions using Informatica Power center 9/8.6.1 to implement the business logic and to load the data incrementally.
• Understand and discuss the ETL requirements with the Business Unit and preparing the detail design documentation according to the standards.
• Involved in Performance tuning at source, target, mappings, sessions, and system levels.
• Monitor and troubleshoot batches and sessions for weekly and Monthly extracts from various data sources across all platforms to the target database.
• Migrated ETL objects from DEV to UAT to PRD environments using Repository Manager.
• Worked on the UNIX scripts for running the workflows and threshold check for the incoming files.
• Prepared ETL Design-Document and Implementation Plan documents.
• Involved in Unit Testing and created various test cases and reviewed and fixed issues in the ETL code.
• Supported UAT and resolved issues. Also, worked on CR's change request by the business.
• Prepared Support Turnover documents in Production Support, for some interfaces.

Environment: Informatica PowerCenter 8.1/7.1, Oracle 9i, DB2,SQL, Teradata, PL/SQL, Sun Solaris, UNIX Shell Scripts, Erwin, Autosys, Remedy.

Confidential is a leading provider of insurance and other financial services throughout the world. The primary objective of this project is to develop Tampa DataMart for investment like securities and bonds using existing DWH called IDEAS. DM is developed in such a way that adjustments are applied directly by users with GUI.

Responsibilities:

• Interacted with business community and gathered requirements based on changing needs. Incorporated identified factors into Informatica mappings to build the DataMart.
• Developed a standard ETL framework to enable the reusability of similar logic across the board. Involved in System Documentation of Dataflow and methodology.
• Taken the whole responsibility and handled the project with 2 team members.
• Assisted in designing Logical/Physical Data Models, forward/reverse engineering using Erwin 4.0.
• Developed mappings to extract data from SQL Server, Oracle, Flat files and load into DataMart using the PowerCenter.
• Developed common routine mappings. Made use of mapping variables, mapping parameters and variable functions.
• Used Informatica Designer to create complex mappings using different transformations like Filter, Router, Connected Unconnected lookups, Stored Procedure, Joiner, Update Strategy, Expressions and Aggregator transformations to pipeline data to DataMart.
• Handled Informatica administrative tasks like moving, copying sessions, workflows from DEV to QA.
• Written shell scripts in UNIX to execute the workflow from GUI.
• Written shell scripts to run the workflows and automated them through maestro job scheduler.
• Written SQL Queries, Triggers, PL/SQL Procedures to apply and maintain the Business Rules.
• Troubleshooting database, workflows, mappings, source, target to find out the bottlenecks and improved the performance.
• Implemented various Performance Tuning techniques on Sources, Targets, Mappings, Workflows and data base tuning.
• Written Indexes, primary keys and checked other performance tuning at data base level.
• Conducted Database testing to check Constrains, field size, Indexes, Stored Procedures, etc.
• Created testing metrics using MS-Excel
• Worked closely with the business analyst's team in order to solve the Problem Tickets, Service Requests. Helped the 24/7 Production Support team.

Environment: Informatica PowerCenter 8.6/8.5, Teradata, SQL Server 2005/2000, Oracle 10g, SQL, PL/SQL,IBM AIX, UNIX Shell Scripts, SSIS, Erwin, STAR team, Remedy, Maestro job scheduler

Confidential ETL Developer Confidential warehouse is built to load the data of Customers, Loan Plans and Transactions from different sources through Informatica server and generating reports on quarterly basis to help the business Analysis's. Responsibilities:

• Project Life Cycle - from analysis to production implementation, with emphasis on identifying the source and source data validation, developing particular logic and transformation as per the requirement and creating mappings and loading the data into different targets.
• Involved in detail design and development of mappings using Informatica power center.
• Used to extract data from ODS and load it into EDW using Informatica.
• Performed Repository object migration from Development to testing and testing to production Environments.
• Worked with pre and post sessions, and extracted data from Transaction System into Staging Area.
• Performed Unit Tests and tested Mapping logic by passing sample messages.
• Responsible for Self and Peer Review of Informatica mappings under Development Phase.
• Schedule and Run Extraction and Load process and monitor sessions using Informatica Workflow Manager.
• Scheduled the tasks to be run using the Workflow Manager.
• Created Informatica users, maintained folders and given user privileges based on role.
• Implemented various Transformations like Joiner, Aggregate, Expression, Lookup, Filter, Update Strategy, Stored Procedures, and Router etc.
• Extracted data from Flat files and Oracle tables.
• Created and Monitored Sessions and various other Tasks such as Event-Raise Task, Event-Wait Task, Decision Task, Command Task etc. using Informatica Workflow.
• Worked on the UNIX scripts for running the workflows and threshold check for the incoming files.
• Moved the mappings from Development, Testing and to Production.

Environment: Informatica PowerCenter 7.1, Oracle 9i, PL/SQL, SQL Server 2000, Flat files, Sql loader, UNIX Shell Scripts, Micro strategy 8, Erwin.

Confidential ETL Developer Description: Farmers Insurance is one of the leading insurance companies in USA. The company has enormous amount of inconsistent and redundant data generated among various functional groups of an organization. The application was developed to integrate these isolated islands of data to provide users with better information and even faster access so that they identify opportunities more quickly and respond more appropriately.

Responsibilities:

• Extensively worked on Business Analysis and Data Analysis.
• Interacted with the users to convert business logic into ETL specifications.
• Extensively analyzed and used Ralph Kimball approach for building Data warehouse.
• Drafted Bus Matrix along with the other team members.
• Worked with Power Center - Designer tool in developing mappings and mapplets to extract and load the data from flat files, Oracle to Oracle.
• Created different transformations like Source Qualifier, Joiner transformation, Update Strategy, Lookup transformation, Rank Transformations, Expressions, Aggregator, Sequence Generator for loading the data into targets.
• Developed change data capture solutions CDC for the project, which captures and analyzes changes from daily feeds to maintain history tables.
• Monitored sessions using the workflow monitor which were scheduled, running, completed or failed. Debugged mappings for failed sessions.
• Assisted the team in the development of design standards and codes for effective ETL procedure development and implementation.

Environment: Informatica Power Center 7.1, Oracle 8i, MS SQL Server 7.0/2000, MS Excel 97, Flat files, PL/SQL, SQL, Windows 2000, Unix

Confidential

Oracle Developer

Confidential is India's largest private sector bank by market capitalization and second largest overall in terms of assets. It offers a wide range of banking products and financial services to corporate and retail customers through a variety of delivery channels and through its specialized subsidiaries and affiliates in the areas of investment banking, life and non-life insurance, venture capital and asset management. This project is about using Orcale SQL and PL/SQL to extract data from various sources into a data store for reporting

Responsibilities:

• Participated in discussions with clients to better understand business requirements
• Created forms for new policy entry details.
• Responsible for maintaining Policies as per the customer requirement
• Wrote PL/SQL Stored Procedures for data extraction.
• Creation of Reports that allows the user to retrieve complete information as required for the financial status like monthly reports, day to day transaction etc.