SUMMARY

• Software Professional having 12+ years of Strong data warehousing experience with extensive knowledge in SDLC: Data Analysis, Design, Development, Implementation and Testing using Data Extraction, Data Transformation and Data Loading (ETL) using Informatica Power center 9.5/9.1.1/8.6/8.1/7.1
• Participate in project planning sessions with Client project Managers, business analysts and team members to analyze business requirements and outline the proposed IT solution and building up an roboust ETL framework
• Working as a Technical Team Lead to help team members resolve any issues related to coding, testing, performance tuning of Informatica mappings and workflows
• Designing and development based upon the Kimball Datwarehouse methodologies and Dimensional modelling
• Extensive experience in Informatica 9.5 with implementing Error handling and ABC (Audit, Balancing and Control) process in multiple Data Integration projects
• Golden Gate replication from OLTP system to Operational schema for Enterprise Data warehouse projects
• Applied Push down optimization (Source, Target and Full) at database level for the mappings which involved loading data to target using target lookup to avoid load to Informatica server
• Extensive hands on experience in Informatica Administration areas pertaining to configuration and management of creating users, custom roles, groups, respective services and granting privilèges along with user account lockouts using Informatica Administration Console
• Extensive hands on experience using various External Teradata Loaders like Multiload, TPUMP, Fast Load, Fast Export and TPT (Teradata Parallel Transporter) as part of the ETL process for the Enterprise Data warehouse (EDW) applications
• Real time data processing using XML and web services transformations
• Vast experience in designing and developing complex mappings from varied transformations like Unconnected and Connected lookups, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Update Strategy, Union, Sequence Generator, Sorter, Normalizer etc
• Extensively used ETL methodologies for supporting data extraction, transformation and loading process, in a corporate - wide-ETL Solution using Informatica Power Center (9.5.1/7.1.4/8.6 )
• Extensively worked on Informatica Client Tools - Source Analyzer, Target Designer, Transformations Developer, Mapplet and Mapping Designer, Task Developer, Workflow Monitor
• Experience in writing UNIX shell scripts to support and automate the ETL process
• Developed several Enterprise Data warehouse applications for extracting, transforming, loading, cleaning and populating various Data marts, used Informatica in Very Large Database Systems (VLDB) environment with heavy parallelisms
• Strong skills on Transforming data from heterogeneous sources like flat files, Teradata SQL Assistant, Oracle, SQL data to Data Warehouse using Informatica
• Expertise with requirements gathering, analysis, study and documentation
• Strong skills in data mapping for the Slowly Changing Dimensions - SCD1, SCD2 (Flagging, Time Stamping and Version), and SCD3
• Highly talented in Performance tuning of the Queries and attacking the bottlenecks of the applications
• Possess excellent working skills in data warehousing tools like INFORMATICA from a design, architecture and admin areas which had helped me propose / implement robust solutions and resulting in gaining lots of value add to client's business
• Cloudera Data Analyst Certified for Hadoop

TECHNICAL SKILLS

ETL Tools: Informatica Administration console and Informatica 8.6/9.0/9.5, Ab Initio GDE (1.12/1.13/1.14 Co>Op 2.12.18/2.13.11/2.14 )

Languages: SQL, PL/SQL

RDBMS: Oracle 9i/8i/7.x, Teradata V14

Testing Tools: Test Director 7.X, Mercury Quality Center

Operating Systems: AIX, UNIX, SUN Solaris & Windows NT/2000

Schedulers: Cron, Tivoli, Autosys

PROFESSIONAL EXPERIENCE

Confidential, California

ETL Lead / Architect

RESPONSIBILITIES:

• Involved in ETL design strategy to populate the multiple staging tables-Landing tables and Staging tables leading up to the Dimension table
• Worked with multiple source systems such as Flat files, Teradata database
• Developed mappings by implementing the High Level Design Document, was involved in discussions in preparing the low level design document and used several transformations such as Filter, Expression, Aggregator, Connected and Unconnected Look-up, Router, Normalizer, Joiner, Stored Procedure, XML, Sequence generator and Ranker Transformations
• Configuration and Management of users,groups,Custom roles and granting the necessary privileges using Informatica Administration console for the entire Informatica users within Shared Business Intelligence Services (SBIS)
• Using Kimball's Data Modeling concepts including data modeling, data marts, dimensional modeling, star and snowflake schema, fact aggregation and dimension tables, slowly changing dimensions and surrogate keys
• Involved in Golden Gate replication from OLTP system to Operational schema for Enterprise Data warehouse projects
• Configuration and Management of various services (Repository service, Integration service, Model repository service, Reporting and Dashboards service etc) using Informatica Administration Console
• Manage the user account lockouts with Informatica Administration Console
• Applied SCD Type 1/2/3 Dimension concepts to populate the Data Warehouse Dimension tables
• Implemented the Informatica Error Handling Utilities feature for Unit testing and resolving any technical issues
• Unit Testing was done extensively with comprehensive test cases and scenarios to validate the data in the Data Warehouse tables
• Data profiling was done during the initial phase to determine relevant sources and to determine design strategy.
• Used Parameter files extensively for defining database connections along with external driven parameters
• Implemented performance tuning logic on targets, sources, mappings, sessions to provide maximum efficiency and performance and used various mechanisms such as indexing, partitioning, SQL tuning from both Informatica as well as database standpoint
• Extensively used various External loaders with Teradata in Informatica
• Worked on Heterogeneous sources including developing monthly scheduled delimited flat files that involves sourcing data from database tables
• Created UNIX scripts for invoking Informatica workflows along with certain file manipulations
• Worked on Production Support issues to meet SLA on a daily basis
• Worked on developing XML files from excel file data using the XML target definition that was developed using the XSD file
• Worked on impact analysis of different transformations, targets and sources by querying the Informatica Repository OPB tables
• Tuned Informatica mappings and sessions for better performance using various strategies based upon the analysis performed. The performance is monitored and relative steps were taken to enhance the performance
• Involved in Code peer review to identify/eliminate any known issues and identifying the bugs in existing mappings by reviewing the ETL architecture
• Involved in production support for the data warehouse Informatica jobs
• Involved in bug fixing and debugging of the issues during QA phases

Environment: Oracle 9.1.6, Teradata, Informatica, PL/SQL, UNIX Shell (ksh), Tiwoli, Toad, Teradata SQL Assistant.

Confidential, Hartford

ETL Lead / Architect

RESPONSIBILITIES:

• Worked on Informatica Power Center tools- Designer, Repository Manager, Workflow Manager, and Workflow Monitor
• Involved in building the ETL architecture and Source to Target mapping to load data into Data warehouse
• Created mapping documents to outline data flow from sources to targets
• Involved in Dimensional modeling (Star and Snowflake Schema) of the Data warehouse and used Erwin to design the business process, dimensions and measured facts
• Designed and customized data models for Data warehouse supporting data from multiple sources on real time
• Maintained stored definitions, transformation rules and targets definitions using Informatica repository Manager
• Extracted the data from the flat files and other RDBMS databases into staging area and populated onto Data warehouse
• Worked on different tasks in Workflows like sessions, events raise, event wait, decision, e-mail, command, worklets, Assignment, Timer and scheduling of the workflow
• Created sessions, configured workflows to extract data from various sources, transformed data, and loading into data warehouse
• Configuration and Management of various services (Repository service, Integration service, Model repository service, Reporting and Dashboards service etc) using Informatica Administration Console
• Involved in the maintenance and Management of Informatica platform pertaining to enabling and disabling the various Information services as required
• Used Type 1 SCD and Type 2 SCD mappings to update slowly Changing Dimension Tables
• Used Debugger to test the mappings and fixed the bugs
• Used various transformations like Filter, Expression, Sequence Generator, Update Strategy, Joiner, Stored Procedure and Union to develop robust mappings in the Informatica Designer
• Developed mapping parameters and variables to support SQL override
• Wrote UNIX shell Scripts & PMCMD commands for FTP of files from remote server and backup of repository and folder
• Involved in Performance tuning at source, target, mappings, sessions, and system levels
• Prepared migration document to move the mappings from development to testing and then to production repositories
• Interact with Business Analyst to assist him in Understanding the Source and Target System
• Responsible to pull the data from Teradata, XML files, Flat files (Fixed width & Delimited) and COBOL files by using complex transformations like Normalizer, XML Source Qualifier etc
• Extracted and transformed data from various sources like relational databases (Oracle, SQL Server)
• Created Mappings using Mapping Designer to load the data from various sources, using different transformations like Source Qualifier, Expression, Lookup (Connected and Unconnected), Aggregator, Update Strategy, Joiner, Filter, and Sorter transformations
• Extensively worked on Mapping Variables, Mapping Parameters, Workflow Variables and Session Parameters

Environment: Oracle 9.1.6, Teradata, INFORMATICA, PL/SQL, UNIX Shell (ksh), Autosys, Toad, PL/SQL Developer.

Confidential, Minneapolis

ETL Lead / Senior Developer

RESPONSIBILITIES:

• Understand business needs and overall business strategy of the business customer
• Participated in analyzing and interpreting user requirements into technical architecture and design specifications
• Contributed to the requirements analysis and solution design for the Clinical Reporting Platform
• Extensively used Informatica Workflow manager & Workflow Monitor tools to Load data from Teradata table sources into the Flat file
• Performance tuning of various Informatica workflows by identifying the bottlenecks in sources, targets, mappings and sessions
• Real time processing using Web services Provider, Consumer and XML Transformations with Informatica
• Developed an overall understanding of the end-to-end process from source systems to end-user analytic and reporting tools
• Installation of Informatica server and Client suite for the development team
• Setup and configuration of all Informatica administration activities using the Admin console as part of the Shared Analytics Environment (SAE) team
• Developed reusable transformations, Mapplets and Worklets to reduce development
• Applied Push down optimization at database level for the mappings which involved loading data to target using target lookup to avoid load to Informatica server
• Responsible for working with the business users, solution architects and development leads to define and implement overall application level architecture
• Development of various complex informatica mappings with different transformations like Aggregator, Joiner, Look-up, update strategy, Filter, Source Qualifier, Expression
• Involved in end to end data integration of Ovations (Care) data into ECODS
• Integrated of data from multiple ECODS disparate sources like stg care, VLP, optumods, optumint schemas and HC3 data warehouses to create a Clinical Reporting Platform (CRP) for end users
• Act as a single point of contact and SME from an ETL perspective in providing technical assistance and guidance to the team

Environment: Oracle 9.1.6, Unix, INFORMATICA, PL/SQL, UNIX Shell (ksh), Autosys, PL/SQL Developer.

Confidential, Omaha

ETL Developer

RESPONSIBILITIES:

• Interacted with clients, gathered business requirements and documenting in the Functional Specification plan
• Participated in project planning sessions with project managers, business analysts and team members to analyze business requirements and outline the proposed IT solution
• Done Impact analysis of the existing system and on the basis of this preparing high level design document
• Framed the design and feasible implementation approach to meet the necessary requirements for the business
• Anchored various initiatives involving development of reusable components and technical artifacts
• Prepared detailed Estimates for each Release Related to the application starting from Design Phase to Deployment Phase using the client approved ODP Estimation Tool
• Responsible for the design, build, and deployment of the project's data integration component
• Use the Informatica Data Integration platform to extract, transform, and load data
• Developed Informatica mapping design
• Created sessions for mappings and utilized workflow manager & monitor to create, schedule and monitor workflows
• Analyzed Informatica Session logs for failed processes and accordingly create and assign defects
• Tuned the existing mappings and sessions to improve the performance
• Involved in preparing test plans and testing all the ETL process
• Used SQL tools like TOAD to run SQL queries and validate the data loaded in to the target tables
• Developed Data Integration Workflows and load processes
• Ensured adherence to locally defined standards for all developed components
• Performed data analysis for both Source and Target tables/columns
• Provides technical documentation of Source and Target mappings
• Supports the development and design of the internal data integration framework
• Understands data integration processes and how to tune for performance
• Implement/coded the business requirements adhering to the tech design
• Developed Code based on Detailed Designs. (Includes Source to Stage, Stage to Integration, Integration to Dimension and Dimension to Fact) for the health care domain frame work .e.g. Type1, Type2 Dimensions, Factless Facts, Snapshot, Additive Facts
• Developed unit test cases for the business components that are created
• Implemented change requests and fixing of defects
• Scheduled of Health care domain framework related Informatica workflows using Client Approved Third Party Scheduling Tool Autosys
• Ensured quality process compliance though out the life cycle of the project.
• Post production support of the components
• Prepared WSR (weekly status report), project plans and Gantt charts and identify work breakdown structure allocation of the tasks among team members and track the progress of project assigned to them
• Reviews (Code, UT Plan, SIT Plan)
• Subject Matter Expert (SME) for my application and assisting the team members for any application specific related queries
• Conducted SQA reviews for my project on a quarterly basis with the SQA Audit team thereby updating PMP, QMP, CMP, doing CAR (Causal Analysis report)
• Tracked project deliverables by creating tasks for each and every release in GDD (Global Delivery Dashboard)
• Leaded the DEX (Delivery Excellence) team as part of Industrialization activity there by focusing on automation, creation of tools and assets in the projects, Continuous Improvements, effort savings etc

Environment: Oracle 9.1.6, Teradata, UNIX, INFORMATICA, PL/SQL, UNIX Shell (ksh), Autosys, Toad, PL/SQL Developer.

Confidential

ETL Developer

RESPONSIBILITIES:

• In depth understanding of client’s ETL framework and project execution methodology
• Anchored various initiatives involving development of reusable components and technical artifacts
• Suggested alternative solutions to a problem. (problem solving approach)
• Translated the solutions provided by systems designers into detailed program specifications
• Created various mappings using Designer which include Expression, Router, Aggregator, Stored Procedure, Update strategy and Look-up Transformations
• Created Mapplets using Mapplet Designer to reuse in different mappings
• Handled Performance Tuning by checking bottlenecks also created partitions, SQL override in Source qualifier.
• Done Impact analysis of the existing system and on the basis of this preparing High level design document.
• Interfaced with ETL designers and understand the Build requirements before percolating the relevant tasks to team members
• Mentored Senior. Developers and Developers working in the application team
• Created low level design artifacts and mapping documents from high level business requirements
• Managed data structures, performance tuning, query optimization and tuning, etc to improve database performance
• Participated in code reviews and ensure that all solutions are aligned to defined architectural and functional specifications

Environment: Oracle 9.1.6, Teradata, Unix, INFORMATICA, PL/SQL, UNIX Shell (ksh), Autosys, Toad, PL/SQL Developer.

Confidential, New York

Consultant / ETL Developer

RESPONSIBILITIES:

• Provided thought leadership and influence senior stakeholders on choice and implementation of AB INITIO platform in GIS
• Responsible for end to end delivery for my project (GIW-Global Information Warehouse)
• Framed and implementation of Technology Standards & Guidelines across applications within the project
• Effort estimation of AB INITIO based ETL projects within the organization
• Formulated design patterns as per project requirements and develop reusable components based on ETL AB INITIO framework
• Anchored, conducted various training sessions and mentoring AB INITIO resources
• Resolved technical issues which the team encounters on day to day basis
• Played a vital role as an onshore coordinator for my project for 18 months during my deputation period to New Jersey (USA)
• Done Impact analysis of the existing system and on the basis of this preparing High level design document
• Managed ongoing support of the systems for any break occurrences, including identifying the cause of the problem, identifying, developing, testing, and installing the fix to the system
• Involved in construction of an Enterprise Data Warehouse called GIW across the entire globe which was split into four regions as CEEMEA, North America, Western Europe and ASIA
• Created AB INITIO graphs across various regions to support Intraday and End of Day financial transactions for “STATEMENTS” application
• Involved in creation of Data marts depending on various subject areas like cash, security transactions etc
• Involved in supplying data by building AB INITIO graphs from Enterprise Data warehouse to downstream applications like CDS (Citidirect for Securities), Cable and Wireless etc for their customer reporting
• Build new graphs required to satisfy present/future requirements

Environment: Oracle 9.1.6, Teradata, AB INITIO, PL/SQL, UNIX Shell (ksh), Autosys, Toad, PL/SQL Developer.

Confidential, New York

ETL Developer

RESPONSIBILITIES:

• Involved in monitoring of proper execution of job schedules and proper run of online systems to ensure 24x7 availability of the application being supported
• Performed physical database design at the object level, create DDL scripts, define database snapshots, table partitions, and backup/recovery strategy for Oracle 9i/10g databases
• Oracle database tuning and administration including security management, instance performance monitoring, table space utilization, schema object management, database capacity planning, “bottlenecks” and “offensive” SQL activity monitoring and resolution
• Used Informatica to create and organize various Metadata types, including legacy Source and Data warehouse Target data definitions and ETL jobs
• Troubleshoot data issues, validated result sets, recommended and implemented process improvements.
• Responsible for performance tuning in Informatica Power Center at the Target Level, Source level, Mapping Level, Session Level, and System Level
• Created Tasks and Workflows using Task Manager and Workflow Designer
• Imported data from various Sources (Oracle, fixed width flat files) transformed and loaded into Targets
• Worked on Informatica Power center to map the source data into the target star schema
• Implement the business requirements adhering to the tech design
• Developed unit test cases for the business components that are created
• Ensured quality process compliance though out the life cycle of the project
• Post production support of the components
• Participated in code reviews and ensuring that all solutions are aligned to defined architectural and functional specifications

Environment: Oracle 9.1.6, Teradata, INFORMATICA, PL/SQL, UNIX Shell (ksh), Autosys, Toad, PL/SQL Developer.

Confidential

Project Engineer

RESPONSIBILITIES:

• Worked extensively on different types of transformations like Source Qualifier, Expression, Java Transformation, Aggregator, Router, filter, Update strategy, look up, Normalizer, stored procedure
• Written and modify SQL override in many mappings according to business requirements
• Used mappings parameters and variables for code reusability
• Developed Mappings to extract data from ODS to Data Mart, and monitored Daily, Weekly and Monthly Loads.
• Designed and developed unit test cases for system integration testing
• Deployment / Migrations of Repository folders and objects from Development to Test and from Test to Production environment.

Environment: Oracle 9.1.6, INFORMATICA, PL/SQL, UNIX Shell (ksh), Cron, Toad.