Data Manager
Resume:
ROY KUMAR
******.*****@*******.***
Over 7 years of IT experience with expertise in analysis, design, development and implementation of Data warehousing using ETL tools with Oracle, DB2, MS SQL server, Sybase and Teradata databases on windows and UNIX platforms.
Summary:
Expert level experience in Data Integration and Data Warehousing using ETL tool INFORMATICA PowerCenter 9.1/8.6/8.1/7.1 (Source Analyzer, Warehouse Designer, Mapping/Mapplet Designer, Sessions/tasks, Worklets /Workflow Manager). Knowledge of Informatica tools Power Exchange, Power Analyser, Power Connect, Data Mart, OLAP and OLTP.
Extensively used Enterprise Data warehousing ETL methodologies for supporting data extraction, transformation and loading processing, in a corporate-wide-ETL Solution using Informatica Power Center.
Expert-level mastery in designing and developing complex mappings to extract data from diverse sources including flat files, RDBMS tables, legacy system files, XML files, Applications, COBOL Sources and Teradata.
Experience in integration of various data sources like Flat files, EDI files, IDOC, Oracle, SQL server, SAP and MS access into staging area.
Proficient in implementing Complex business rules by creating re-usable transformations, workflows/worklets and Mappings/Mapplets.
Experience in Performance Tuning of targets, mappings and sessions.
Thorough knowledge of database management concepts like conceptual, logical and physical data modeling and data definition, population and manipulation. Expertise in logical and physical database design and Data Modeling using data modeling tools like Erwin.
Experienced in using Business Objects XIR3/XIR2 to build user defined queries and reports to enable drill-down and slice and dice analysis on multiple databases.
Involved different phases of SDLC from requirement, design, development, testing, training and rollout to the field user and support for production environment.
Demonstrated expertise utilizing ETL tools, including SQL Server Integration Services (SSIS), and Informatica and ETL package design, and RDBMS systems like SQL Server, Oracle.
Strong expertise in Relational data base systems like Oracle, SQL Server, MS Access, Teradata, Sybase, DB2 design and database development using SQL, PL/SQL, SQL PLUS, TOAD, SQL-LOADER. Highly proficient in writing, testing and implementation of triggers, stored procedures, functions, packages, Cursors using PL/SQL.
Worked on Data Loading from ASCII flat files to Oracle database using SQL*Loader.
Experience in Task Automation using UNIX Shell Scripts, Job scheduling (AUTOSYS) and Communicating with Server using pmcmd.
Good Knowledge on SFDC.
Domain Data warehousing experience including Investment banking, Credit card, and Pharmaceutical. Proven ability to implement technology based solutions for business problems.
Designed and written the scripts required to extract, transform, load (ETL), clean, and move data and metadata so it can be loaded into a data warehouse, data mart, or data store.
Provided guidance and expertise to resolve technical issues related to DW Tools and primarily Informatica.
Technical skills:
ETL Tools:
Informatica Power Center 9.1/8.6/8.1/7.1.2/7.1, Power Mart, Power Channel 6.2, Power Exchange 5.
OLAP/BI:
Cognos Impromptu, Cognos Power play, Cognos IWR, Business Objects 5.0/6.5.
Data Modeling
Erwin 4.0, Star-Schema Modeling, FACT and Dimension Tables
DBMS
Oracle 11g/10g/9i/8i, Microsoft Access, SQL Server 2005, MS Excel, Flat Files, Teradata, Sybase
Languages:
C, C++, Java, JavaScript,SQL, PL/SQL, T-SQL, HTML, DHTML, XML, UNIX, Shell Scripting, Visual basic, ASP, JSP, XML, Macromedia Software, JCL.
Scripting Languages
Java Script, VB Script and UNIX Shell Scripting.
Operating Systems
Windows 2008/2003/NT/XP, HP-Unix, Linux
Design Skills
Object Oriented Analysis Design using UML.
Others
MS Project, MS Office Suite, Toad (Tool of Oracle Application Development).
Professional Experience:
Industry: JM Family Enterprises Inc, FL October 2013-Current
Role: Informatica Developer
JM Family is a diversified automotive corporation whose principal businesses focus on vehicle distribution and processing; financial services; finance and insurance products; retail sales; marketing and consulting; and dealer technology products and services.
The Project outlines the strategy for data migration from SET CSV files, to the Oracle IR, to the Teradata DW, to the new Vehicle Sales & Stock Data Mart. The data integration platform Informatica will be used. This Project needs to customizes the vendor recommendations and templates wherever possible to meet the requirements of Vehicle Sales & Stock project.
This solution requires three distinct ETL processes: one in which data is extracted from flat files and loaded into the SET IR, one in which data is extracted from the IR and loaded into the Teradata Integration layer, and one in which data from the integration layer is flattened and loaded into the Reporting layer.
Responsibilities:
Interacted actively with Business Analysts and Data Modelers on Mapping documents and Design process for various Sources and Targets
Created complex mappings in Power Center Designer using Aggregate, Expression, Filter, and Sequence Generator, Update Strategy, Union, Lookup, Joiner, XML Source Qualifier and
Analyzing, designing and implementing complex SQL stored procedures, ETL processes and informatica mappings..
Implemented SCD1, SCD2 type maps to capture new changes and to maintain the historic data
Providing technical assistance during production phase of project development
Defined and developed technical standards for data movement and transformation as well as review all designs to ensure those standards are met.
Handled extraction of various types of source files Flat files, XML standard source data of different transactions and loading to staging area.
Designed and written the scripts required to extract, transform, load (ETL), clean, and move data and metadata so it can be loaded into a data warehouse, data mart, or data store.
Created new mappings and enhancements to the old mappings according to changes or additions to the Business logic.
Worked on SQL, Oracle, DB2, Teradata databases.
Worked extensively on Unit Testing the ETL mappings and preparing efficient unit test documentation for the developed code to make sure the test results match with the client requirement.
Provided enterprise data warehousing solutions, including design and development of ETL processes, mappings and workflows using Informatica’s PowerCenter.
Participated in design workshops, providing technical insight and knowledge.
Environment: Informatica Power Center 9.0.1, Oracle 10G, Cognos BI 8.3, Relational Junction,, DB2, Flat files, PL/SQL, SQL*Plus, TOAD, UNIX, Shell Scripting, Autosys, Erwin 4.2.
Industry: Liberty Mutual, Portsmouth, NH February 2013-October 2013
Role: Informatica Developer
Liberty Mutual Insurance is one of the largest property and casualty insurer which provides competitively-priced insurance products and services to meet customers' ever-changing needs.
The intent of current project at Liberty Mutual to perform Claim Next Generation Data Conversion process in order to move claims from Claims Workbench to the CNG Navigator application, where the claims will be managed using Informatica mappings, target files, target tables, and Navigator environments.
Responsibilities:
Running batch cycles which involves job trigger from Informatica, query table in Teradata /OracleSQl developing, report generation and claims archiving maintenance.
Involved in creating stored procedures and using them in informatica
Implementing claim Data Conversion process is to move claims from Claims Workbench to the CNG Navigator application.
Providing technical assistance during production phase of project development
Defined and developed technical standards for data movement and transformation as well as review all designs to ensure those standards are met.
Worked with command line program pmcmd to interact with the server to start and stop sessions and batches, to stop the Informatica server and recover the sessions
Designed Work Flows that uses multiple sessions and command line objects ( which are used to run the unix scripts)
Created source and target mappings, transformation logic and processes to reflect the changing business environment over time.
Provided enterprise data warehousing solutions, including design and development of ETL processes, mappings and workflows using Informatica’s PowerCenter.
Resposible for migration of the work from dev environment to testing environment
Provided guidance and expertise to resolve technical issues related to DW Tools and primarily Informatica.
Environment: Informatica Power Center 9.0.1, Oracle 10G, Cognos BI 8.3, Relational Junction,, DB2, Flat files, PL/SQL, SQL*Plus, TOAD, UNIX, Shell Scripting, Autosys, Erwin 4.2.
Industry: Carter's, Inc., Atlanta, GA February 2012-February 2013
Role: Informatica Developer
Carter’s, Inc. is a well-established and largest branded marketer of baby and children’s clothing in the U.S.A and marketing apparel under 2 of the most recognized and long-term brands in the nation. The project at Carter's, Inc was to use informatica approach in order to translate the Inbound Advanced Shipment (ASN) information maintained in WM into XML format.
Responsibilities:
Worked with Informatica PowerMart client tools like Source Analyzer, Warehouse Designer, and Mapping.
Worked on Informatica tools like Source Analyzer, Target Designer, Mapping Designer, Workflow Manager, and Workflow Monitor.
Extracted data from Flat files loaded them into EDW.
Worked on multiple inbound ASN files to create XML, created a list file with the entire Inbound ASN files list on UNIX and use indirect loading and transforming methods in Informatica
Handled extraction of various types of source files Flat files, XML standard source data of different transactions and loading to staging area.
Wrote Data loading stored procedures, functions using PL/SQL from Source systems into operational data storage.
Involved and Worked with DBA to build Dimension Tables and Fact Tables.
Created Complex Mapping for generating the parameter files.
Created source and target mappings, transformation logic and processes to reflect the changing business environment over time.
Designed and developed SSIS packages, store procedures, configuration files, tables, views, and functions; implement best practices to maintain optimal performance.
Utilized SSIS (SQL Server Integration Services) to produce a Data Warehouse for reporting.
Mainly involved in developing Star Schema (Facts and dimensions based on future requirements).
Developed source to target mappings and scheduling Informatica sessions.
Environment: Informatica Power Center 9.0.1, Oracle 10G, WLM, UNIX AIX, DB2, Flat files, PL/SQL, SQL*Plus, TOAD, UNIX, Shell Scripting, Autosys, Erwin 4.2.
Industry: Aviva Life Insurance CO, Des moines, IA September 2011 -February 2012
Role: ETL Developer lead
The Aviva Life Insurance CO main activities are general and life insurance as well as long-term savings and fund management. The purpose of current project is to comply with the legislated framework introduced by the European Union for Life Insurance and Property & Casualty Companies in Europe. This is a comprehensive risk management solution for insurance companies to comply with all facets of the project directive and provide the foundation for improved strategy and performance. The main objective of this project is to strengthen the capital adequacy regime to reduce the possibility of consumer loss or market disruption in insurance.
Responsibilities:
Involved in creating design document Informatica mappings based on business requirement.
Extracted data from Flat files loaded them into EDW.
Developing Complex Transformations, Mapplets using Informatica to Extract, Transform and Load data into Data marts, Enterprise Data warehouse (EDW) and Operational data store (ODS)
Used various Transformations like Joiner, Aggregate, Expression, Lookup, Filter, Union, Update Strategy, Stored Procedures, and Router etc. to implement complex logics while coding a Mapping
Worked on Informatica tools like Source Analyzer, Target Designer, Mapping Designer, Workflow Manager, and Workflow Monitor.
Created tasks and workflows in the Workflow Manager and monitored the sessions in the Workflow Monitor
Tested scripts by running workflows and assisted in debugging the failed sessions
Implemented Client Side validation using Java Scripts
Analyze functional requirements provided by Business Analysts for the code changes.
Writing UNIX scripts for unit testing the ETL Code
Execute the Test cases for the code changes.
Extensively participated in functional and technical meetings for designing the architecture of ETL load process
Environment: Informatica Power Center 9.0.1 Power Exchange, Teradata V13.0, SQL Server 2008, Oracle 11g/10g SOA, TOAD, SSH, WLM, UNIX AIX, Windows XP.
Industry: Fresenius Medical Care NA, Lexington, MA May 2010 – August 2011
Role: Sr.Informatica Developer
The purpose of the Ultracare Customer Contact (UCC) Project is to integrate commercially insured patients and associated facility and clinical data from Proton/KC and AMI/IRIS into a new, central UCC Data Mart which is used by nurse managers to improve patient outreach and service. The benefits of the project are improved patient satisfaction and higher retention rates for those patients covered under commercial policies. The objectives for this project are to extract patient and related facility and clinical data from Proton/Knowledge Center (Clinical System and Medical Manager Financial Application), extract patient and related facility and clinical data from AMI/IRIS and QMS and combine/integrate the above 2 extracts into a new, central UCC Data warehouse.
Responsibilities:
Documenting user requirements, Translate requirements into system solutions and developed implementation plan and schedule. Responsible for reviewing Business Requirement document.
Translated user requirements into system solutions.
Involved in Data Analysis of the OLTP system to identify sources data elements.
Prepared Technical Specification Document.
Prepared Source to Target ETL mapping Documents.
Designed and developed Informatica Mappings, Reusable Sessions, Worklets, Workflows, Dynamic Parameter files.
Provided solutions for various performance bottle necks in Informatica Mappings.
Coding UNIX scripts and ParmFiles for implementing business rules.
Created complex mappings with Indirect file list methodology.
Handled extraction of various types of source files Flat files, XML standard source data of different transactions and loading to staging area.
Have written lots of SQL Scripts in Pre-SQL and Post-SQL session tasks.
Designed mappings for populating Tables for one time load and incremental loads.
Worked on integrating and configuring BI Publisher with OBIEE
Developed different brand level Reports, Filters, and Prompts using Oracle BI
Created Complex BI Performance Management reports using Report studio that includes complicated formats, Cascading prompts with static prompt choices, Conditional formatting and drill through.
Created Batch jobs, scheduling and testing the reports.
Designed and Developed Autosys jil scripts to Schedule Informatica workflows.
Developed various shell scripts using Korn Shell to Integrate various components.
Developed and implemented Audit control and Exception control Strategies.
Worked on Business Objects Data Services, Designer, Repository, Job Server, Cleansing Packages,
Involved in Unit testing, System Integration testing, User Acceptance Testing.
Involved in Informatica Code Migration across various Environments.
Involved in Production Support and Knowledge Transfer sessions.
Environment: Informatica Power Center 9.0.1, Oracle 10G, OBIEE 10.1.3.4,Cognos BI 8.3, Relational Junction, DB2, Flat files, PL/SQL, SQL*Plus, TOAD, UNIX, Shell Scripting, Autosys, Erwin 4.2.
Industry: Alliance Pharmaceutical Corporation, SanDiego, CA August 2009– May 2010
Role: ETL DW Developer
Alliance Pharmaceutical Corporation is a research-based, growth-oriented global enterprise with core competencies in the fields of health care, nutrition and high-tech materials.
Responsibilities:
Gathered business scope and technical requirements.
Worked with Power Center Designer tools in developing mappings and mapplets to extract and load the data from flat files and Sybase (source) to Teradata (target).
Extensively used All Fusion-ERWIN for Logical / Physical data modeling and Dimensional data modeling, and designed Star join schemas.
Created different transformations for loading the data into targets using various transformations and also involved in creating Data Marts.
Created reusable transformations and mapplets to use the transformation logic in multiple mappings.
Worked on Repository manager to create and manage user profiles.
Scheduled Sessions on the Informatica Server using Informatica Workflow Manager.
Implemented performance tuning logic on targets, sources, mappings, sessions to provide maximum efficiency and performance.
Wrote Data loading stored procedures, functions using PL/SQL from Source systems into operational data storage.
Involved in fine tuning queries for better performance (includes indexing and pre-joining and materializing views).
Created views and altered some of the dimensional tables to satisfy their reporting needs.
Created Universes using Business Objects Designer, applied contexts and aliases to resolve cyclic dependencies and loops.
Created reports like, Reports by Department, Reports by Supplier, Reports by Period, Demographic reports and Comparative reports.
Created different types of reports such as Master/Detail, Cross Tab and Chart.
Used Analyzer and Explorer to view reports from different prospect like Slice and Dice and Drill down.
Environment: Informatica Power Center 8.1/7.1, Oracle 9i, PL-SQL, Erwin, Teradata, Sybase, SQL Server 2005, Business Objects, UNIX.
Industry: Affinion Group, Columbus, OH July 2008 – August 2009
Role: ETL Developer
Affinion is the part of Cendant marketing group which is in USA, the scope of this project. PGS, PGNG and s1032 applications are getting integrated in to new single application called PGNG2 for this we need to integrate three data models in to one PG new data model. Privacy Guard system is changing to Privacy Guard next generation for credit reporting and credit monitoring of the Business groups, Privacy Guard system is having two different databases S1032(file database) and Oracle PGS Oracle database. These are three data bases are merged in new data model of PGNG system. For integrating the data from these three different data bases to one single new database informatica is used.
Responsibilities:
Mainly involved in Preparation of Low level Designs (Mapping Documents) by understanding the system privacy Guard.
Used lookup, aggregator, Router, sorting, sequence, Stored Procedure transformations in the mapping to meet the business logic. Created the unit test scripts to check the data quality.
Mainly involved in developing Star Schema (Facts and dimensions based on future requirements).
Creating the Mappings, Sessions, workflows and Parameter files.
Pulling required data sets from General Ledger and making General Ledger Reports.
Used Power Connect for SAP to extract data from SAP FICO tables
Responsible of business requirement and analysis from business users. Created Complex Informatica mappings for Exception Handling Process and Loading of tables with Type 2 for CDC.
Extensively worked with Parameter files.
Created Complex Mapping for generating the parameter files.
Created complex mappings with Indirect File list methodology.
Responsible for tuning the bottle necks in different levels (mapping, sessions and source and Target).
Created reusable components for using them in across the projects.
Responsible for migrating from Development to staging and to Production (deployments).
Resolving the Bugs Issues in Mappings, Sessions & Parameter file.
Preparing release note documents for different releases.
Environment: Oracle10g SOA, S1032, Informatica Power Center 7.1, Power Connect for SAP,Data Profiling, Windows 2000/2003,UNIX (server), AIX, SAP, Toad, SFTP.
Industry: Ranbaxy, India November 2007 – June 2008
Role: Data Analyst
Ranbaxy is ranked amongst the top ten generic companies worldwide, a pharmaceutical company, which is a world leader producer of world-class generics, branded generics and a major supplier of its range of Active Pharmaceutical Ingredients.
Responsibilities:
Involved different phases of SDLC from requirement, design, development, testing, training and rollout to the field user and support for production environment.
Worked with Informatica PowerMart client tools like Source Analyzer, Warehouse Designer, and Mapping.
Created source and target mappings, transformation logic and processes to reflect the changing business environment over time.
Designed and developed mapping, transformation logic and processes in Informatica for implementing business rules and standardization of source data from multiple systems into the data warehouse.
Developed source to target mappings and scheduling Informatica sessions.
Maintained source and target mappings, transformation logic and processes to reflect the changing business environment over time.
Worked in Oracle SQL, PL/SQL, Query performance tuning, Created DDL scripts, Created database objects like Tables, Indexes, Synonyms, and Sequences etc., closely worked with DBAs to create Physical Databases.
Involved in designing the Data mart and entire data warehousing life cycle.
Involved and Worked with DBA to build Dimension Tables and Fact Tables.
Interacted extensively with user and Involved in gathering requirements, prototyping and prepared various documents like Interface Requirement Document, Customer Requirement document, Integration test plan, Unit test plan Release notes etc.
Environment: Informatica PowerCenter 7.1, PL/SQL, Oracle 9i, DB2, HMTL, DHTML TOAD, ERWIN 4.1, UNIX, Windows NT/2000.
Contact this candidate