Data Manager

Kalyani T

Senior ETL/ Informatica Developer

Email: *******.*******@*****.***

Mobile: 720-***-****

Summary:

Over10years of experience in System Analysis, Design, Development, Testing, Implementation, Maintenance and Production support of Data Warehouse and Data Integration Applications for Health care, financial sectors.

Strong experience in designing and developing complex ETL scripts to support large data volume movement using Informatica Power Center 9.6.1/9.1/8.x/7.x

Expertise in Data Warehouse/Data mart, ODS, OLTP and OLAP implementations teamed with project scope, Analysis, requirements gathering, data modeling, Effort Estimation, ETL Design, development, System testing, Implementation and production support

Experience in Informatica Data Quality (IDQ-Informatica developer 9.6.1/9.1) for cleansing and formatting customer master data.

Experience in Informatica Analyst to analyze customer master data.

Used Informatica Analyst to perform profiling.

Created reference tables for standardization.

Extensive testing ETL experience using Informatica 9.1/8.6.1/8.58.1/7.1/6.2/5.1 (Power Center) (Designer, Workflow Manager, Workflow Monitor and Server Manager) Teradata and Business Objects.

Strong experience in Dimensional Modeling using Star and Snow Flake Schema, Identifying Facts and Dimensions, Physical and logical data modeling using Erwin

Expertise in working with relational databases DB2 UDB 9.6/9.7, Oracle 11g/10g/9i, SQL Server 2008/2005.

Strong experience in Extraction, Transformation and Loading (ETL) data from various sources into Data Warehouses and Data Marts using Informatica Power Center (Repository Manager, Designer, Workflow Manager, Workflow Monitor, Metadata Manger), Power Exchange, Power Connect as ETL tool on Oracle, DB2 and SQL Server Databases.

Extensive experience in developing Stored Procedures, Functions, Views, Complex SQL queries using SQL Server, and Oracle PL/SQL

Solid experiences in implementing SCD TYPE I and SCD TYPE II, and working with mapping parameters and variables.

Thorough understanding of Data Warehouse concepts Ralph Kimball Methodology, Bill Inmon methodology, Star Schema, Snow Flake Schema, Dimensional Modeling.

Experience in all phases of Data warehouse development from requirements gathering for the data warehouse to develop the code, Unit Testing and Documenting

Worked extensively with complex mappings using different transformations like Source Qualifier, Expression, Filter, Joiner, Router, Union, Unconnected / Connected Lookups and Aggregator.

Strong Experience in developing Sessions/tasks, Work lets, Workflows using Workflow Manager Tools - Task Developer, Workflow and Work let Designer

Experience in ETL Performance Tuning/Optimization.

Experience in working with large data sets in ETL projects.

Highly experienced in identifying, researching and resolving ETL issues and producing root cause analysis.

Experience in using Automation Scheduling tools like Autosys and Control-M

Technical Skills:

Data warehouse Tools:Informatica Power Center 9.6.1/9.1/8.x/7.x (Mapping Designer, Workflow Designer, Workflow Monitor and Repository Manager), Informatica Power Exchange.

Cleansing Tools : Informatica Data Quality (IDQ9.6.1/9.5)

OS:Windows 2008/2007/2005/NT/XP, UNIX, MS-DOS

Databases: Oracle 11g/10g,MS SQL Server 2005/2008, DB2 v8.1

Languages: SQL, PL/SQL, UNIX, Shell scripts

Scheduling Tools:Autosys, Control-M

Professional Experience:

Oppenheimer Funds, Inc., Centennial, CO Oct 2014–Current

Sr. ETL /Informatica Developer/Data Quality(IDQ) Developer

Oppenheimer Funds, Inc. is one of the largest and most respected mutual fund managers in the United States. With more than 60 mutual funds and more than 6 million shareholder accounts, Oppenheimer has been serving the investment needs of financial advisors and their clients since 1960

1. EDE Project Overview (August 2015 to Current)

The goal of the project is the data layout in IDW and availability of both top level and security level data in the data library. The team will design the database structure to store the information in data warehouse. The goal for Phase 1 is top level and Phase 2 is security level data layout in IDW and availability in data library. Load existing Fact set attribution/contribution data into IDW. Enhance existing Fact set data to include: 3/5 years, market cap breakouts and 6 other data groups. Load 5 years of historical quarterly data. Parse the fact set excel files in to I stage raw data (S1) tables.

Responsibilities:

Develop ETL mappings, sessions and workflows to implement business requirements using Informatica Power Center 9.1

Communicated with business customers to discuss the issues and requirements.

Participated in Daily Stand ups

Performed profiling using Informatica Analyst and Informatica Developer.

Captured DQ metrics( like Total record count, Distinct record count, Count of a distinct field,

Count of null, Referential checks etc.) on the inbound/raw data(i.e., Prep or Stage), and then again on the processed/output data (i.e., Base or Pub).

Work on developing the entire ETL design to retrieve data from various sources and build the stage data that will be used by DW process to load the final tables.

Created scorecards to review data quality.

Used Informatica Data Quality tool (Informatica Developer) to scrub, standardize and match customeraddress against the USPS database.

Design, Development and implementation of Informatica Developer Mappings for data cleansing using Address validator, Labeler, Association, Parser, Expression, Filter, Router, Lookup transformations etc.

Design the database structure to store the information in data warehouse

Production Support has been done to resolve the ongoing issues and troubleshoot the problems.

Develop and work on Slowly changing dimensions Type 1,2 and 3

Created complex mapping involving multiple transformations including procedure transformation to have the codes utilized.

Worked with mapping parameters, variables and parameter files and designed the ETL to create parameter files to make it dynamic.

Performance tuning was done at the functional level and map level. Used relational SQL wherever possible to minimize the data transfer over the network.

Pre and post session assignment variables were used to pass the variable values from one session to other.

Created detailed unit test document

Designed workflows with many sessions with decision, assignment task and control task

Identified problems in existing production data and developed one time scripts to correct them.

Load Historical data (2011, 2012, 2013, 2014-Quarterly, 2015 Monthly) in Dev and QA.

Develop, test and promote all of the code changes toQA and Production environment.

Test data against different levels of hierarchy, multiple dates and data points.

Environment:Informatica Power Center 9.1(Repository Manger, Designer, Workflow Manager, Workflow Monitor), Informatica Data Quality 9.1,SQL Server 2008, 2012, Flat files, SQL, Autosys

Scheduling Tools: Autosys

2. IDW Project Overview (Oct2014 to July 2015)

The main objective of the project was to migrate all the investment data warehouse data which was previously in the same server as the staging server to a new server so that the load performance improves and much more business can be flown in that is Current state of IDW has several databases (such as IDW, ISTAGE, Risk ODS, I DIST and IMETA) tightly coupled and co existing on the same database location. Third party recommendations to improve performance and scalability including: Separating IDW to a separate physical location and Migrating to newer/faster hardware and replacing large logical views with physical tables or materialized views

•The data was flowing through multiple sources to stage and from there it was being loaded to IDW, the sources were oracle 11g, sql server 2014, SFDC, flat files, mainframes. There are lots of stored procedures in sql server that were also used to transport data. The entire process involves data flowing ETL, stored procedure, data warehouse and Cognos.

Responsibilities:

Analyze the mappings which are effected due to separation

Rewrite existing mappings by separating IDW from other databases and make sure data loads and extracts will not fail without breaking the functionality

Designed appropriate ETL routines and mappings using Informatica Power Center 9.6.1/9.1

Develop, test, and promote all of the separation code changes in the existing non-separated environment.

Promote the updated code to the different environments (with logical separation)

Promote the updated code to the new hardware (with physical data base separation) and database version

Involved in enhancement and maintenance activities of the data ware house including tuning, modifying of stored procedures for code enhancements.

Effectively worked in Informatica version based environment and used deployment groups to migrate the objects.

Used debugger in identifying the bugs in existing mappings by analyzing dataflow, evaluating transformations.

Created JIL files to promote new ETL jobs.

Involved in QA Testing

Created ksh script and placed as an Autosys job through JIL (Job Information Language)

Participate in daily stand ups and project over view meetings

Involved in creating Project status report

Responsible for the up and running of UAT cycles in Autosys

Performed data validation testing

Identified problems in existing production data.

Fixed the invalid mappings.

Involved in Unit, Integration, and Performance testing levels

Participated in daily standups.

Environment:Informatica Power Center 9.1(Repository Manger, Designer, Workflow Manager, Workflow Monitor), Power Exchange 8.6.1, SQL Server 2008, 2012,2014, Flat files, XML files, SQL/PLSQL, Autosys, Windows NT

Scheduling Tools: Autosys

Independent Health Association (IHA), Buffalo, NY Feb 2013 to Jun2014

ETL Informatica Developer

Independent Health is a non-profit organization that provides a variety of health insurance products and related services (HMO, PPO, Point of Service, flexible spending accounts, Medicare) to some 400,000 members in Western New York. Independent Health named one of the best companies to work for in New York State.

Responsibilities:

Designed appropriate ETL routines and mappings using Informatica Power Center 8.6/9.1 based on the requirements and technical designs.

Integrated the data from DB2 UDB 9.6/9.7, Oracle 11g, and SQL Server for populating large scale DataMart and/or Data Warehouse.

Designed and developed ETL to load Conformed dimensions in Enterprise Data Warehouse.

Participated and assisted Data Architect/Data Modelers while designing the Data Warehouse.

Implemented Informatica loading techniques like Incremental loading (Change Data capture) and slowly Changing Dimensions SCD TYPE II and I.

Worked with mapping parameters, variables and parameter files and designed the ETL to create parameter files to make it dynamic.

Fine tuning the Informatica mappings.

Worked with the applications owners and system analysts to resolve data issues and refine transformations rules.

Worked with Informatica Power Exchange for extracting the data from SAP.

Designed ETL to load Snapshot fact tables used for reporting portfolios.

Writing complex SQL queries for extracting the data and to perform Data Analysis.

Build Informatica velocity mapping specifications, unit test cases, and standard procedures documents.

Worked with Informatica Deployment Groups in migrating the ETL code across environments.

Responsible for scheduling the jobs using CA ESP and Informatica scheduler.

Responsible for On Call Production support.

Nationwide, Columbus, OHJune 2011 to Jan 2013

Informatica Developer

Nationwide is one of the leading companies in the nation for the Auto, Home, property and Life Insurance with more than 10 million policyholders.

The Insurance DW project was developed in an effort to streamline the entire process of Insurance and to keep track of various financials on a daily, weekly, monthly basis. Claims, Premium, loss and contracts were some of the modules to deal with.

Responsibilities:

Interacted with the Business Personnel to analyze the business requirements and transform the business requirements into technical requirements

Prepared technical specifications for the development of Informatica (ETL)Process to load data into various target tables.

Extensively used Informaticapower center 8.6.1 for extraction, transformation and loading process.

Extensively developed various Mappings, which perform Extraction, Transformation and Loading of Source data into the OLAP Schema, and used different Transformations like Source Qualifier, Expression, Lookup, Update Strategy, Aggregator, Stored Procedure, Filter, Router, and Joiner etc.

Created workflows using Workflow manager for different tasks like sending email notifications, timer that triggers when an event occurs, and sessions to run a mapping.

Involved in Unit Testing and Integration Testing of the application.

Documented the entire process. The documents included the mapping document, unit testing document and system testing document among others.

Environment:Informatica Power Center 8.6.1 (Repository Manager, Designer, Workflow Manager, Workflow Monitor, Repository Administration Console), Power Exchange 8.6.1, Oracle Developer, TOAD 9.6, Oracle 11g, SQL Server 2008/2005, PL/SQL, UNIX (SUN Solaris), Flat Files.

IBM, IndiaJan 2007 to Apr 2011

ETL Developer

The project responsibility is to develop an Enterprise Data Warehouse (EDW).

Responsibilities:

Gathered business requirements and created mapping documents based on the data model and source system analysis.

Involved in initial design meetings

Integrated data from heterogeneous source systems extract and transform data with business rules and built re-usable Mapplets using Informatica Designer.

Extensively worked with transformations Lookup, Update Strategy, Expression, Filter, Stored Procedures, Router, joiner and others.

Created complex mappings and transformations using expressions, aggregators, filters, and sorter, and sequence generator, lookups to develop DataMart’s.

Worked on creating star schemaDataMart’s using the dimensional modeling approach.

Fine-tuned transformations and mappings for better performance.

Used Informatica Power Center workflow manager to create sessions, workflow to run with the logic embedded in the mappings.

Efficient documentation was done for all phases like analysis, design, development, testing and maintenance.

Associated with Production support team in various performances related issues.

Written documentation to describe program development, logic, coding, testing, changes and corrections.

HP, IndiaApr 2005 to Dec 2006

Informatica developer

The project scope was to design a salesDataMart by integrating data from OLTP systems and to build sales reports and dashboards sourced from the DataMart.

Responsibilities:

Created different transformations such as source qualifier, joiner, lookup, rank, expression, aggregator and sequence generator etc.

Reused mapping, Mapplets and transformations.

Scheduled sessions and batches on the Informatica Server using Informatica Server.

Tuned the transformations and mappings in Informatica.

Implemented slowly changing dimensions.

Worked with mapping variables, mapping parameters and variables functions like Set variable, Count Variable, set invariable and setmaxvariable.

Developed Mappings that extract data from ODS to Data mart and monitored the Daily, Weekly and Monthly Loads.

Created and monitored Sessions/batches using Informatica Server manager/ Workflow Monitor to load data into target Oracle database.

Environment:Informatica Power Center 7.x, Oracle 8i/9i, UNIX and Windows NT.

Education:

Bachelorof Technology in Electrical &Electronics Engineering, JNTU, India, 2004

Contact this candidate