- 6+ years of strong Data Warehousing experience using Informatica PowerCenter 9.x/8.x/7.x/6.x(Source Analyzer, Data Warehousing Designer, Mapping Designer, Mapplet Designer, Transformation developer), Informatica PowerMart 7x/6x, Power Connect, Power Exchange.
- Extensive experience in Banking, Insurance, Financial and Retail domains.
- Experienced in Installation, Configuration, and Administration of Informatica Power Center/Power Mart Client/Server.
- Experienced in complete life cycle Implementation of data warehouse.
- Experienced in OLTP/OLAP system 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.
- Involved in different database modeling techniques like Object Oriented Design and Modeling, Database Design and Modeling, etc.
- Extensively worked with Oracle PL/SQL Stored Procedures, Triggers, Functions, Packages and also involved in Query Optimization.
- Ability to write complex SQLs needed for ETL jobs and analyzing data, and is proficient and worked with databases like Oracle 11x/10g/9i, SQL Server 2008/2005, Teradata, DB2, MSSQL, Excel sheets, Flat Files, Sybase, COBOL files and XML files.
- Expertise in Administration tasks including Importing/Exporting mappings, copying folders over the DEV/QA/PRD environments, managing Users, Groups, associated privileges and performing backups of the repository.
- Developed Complex mappings from varied transformation logics like Unconnected /Connected lookups, Router, Filter, Expression, Aggregator, Joiner, Union, Update Strategy and more.
- Well Experienced in doing Error Handling and Troubleshooting using various log files.
- Expertise in using UNIX and writing Perl and UNIX shell scripts.
- Experience in Performance Tuning of sources, targets, mappings, transformations and sessions, by implementing various techniques like partitioning techniques and pushdown optimization, and also identifying performance bottlenecks.
- Good exposure to Development, Testing, Debugging, Implementation, Documentation, End-user training and Production support.
- Expertise in doing Unit Testing, Integration Testing, System Testing and Data Validation for Developed Informatica Mappings.
ETL Tools: Informatica Power Center 9.0.1/8.6/8.1/7.1/6.2
Databases: Oracle 11g/10g/9i/, DB2 UDB, MS SQL Server 2008/2005/2000/7.0, MySQL, MS Access
Data Modeling: MS Visio 2005, ERwin
OS: MS DOS, Windows NT/2000, SCO UNIX 5.0.5, Linux 8.x
Languages: SQL, PL/SQL, TSQL, C, C++, Java
Querying Tools: SQL Plus, TOAD, SQL Navigator, Query Analyzer
Web: HTML, DHTML, XML, ASP 2.0/3.5
Applications: MS Office 2007
Confidential, OH April 2011 – Present
Role: Sr.Informatica Developer
Description: AAA is a national federation comprised of more than 50 individual clubs throughout the United States and Canada. AAA has grown to over 50 million members strong, while providing valuable membership services such as roadside assistance. Additionally, AAA has broadened its horizons to include all types of travel-related services, as well as offer a variety of insurance and financial products and services. I was responsible for the development, testing and support of the Oracle Data Warehouse, while also providing the analysis and reporting function.
- Responsible in gathering Requirements and IT review. Interacted with Business Users in the design of technical specification documents.
- Involved in creating logical and physical data models using MS Visio based on business requirements.
- Created and monitored Database maintenance plans for checking database integrity, data optimization, rebuilding indexes and updating statistics.
- Successfully Loaded Data into different targets from various source systems like Oracle Database, Flat files, ODS, SQL Server, etc into the Staging table and then to the target database Oracle.
- Developed various Mappings, Mapplets, and Transformations for data marts and Data warehouse.
- Created Stored procedure transformations to populate targets based on business requirements.
- Responsible for monitoring all the sessions that are running, scheduled, completed and failed. Debugged the mapping of the failed session.
- Analyzed and Created Facts and Dimension Tables.
- Used Informatica features to implement Type I, II, and III changes in slowly changing dimension tables.
- Mapplets and Reusable Transformations were used to prevent redundancy of transformation usage and modularity.
- Tuned performance of Informatica session for large data files by increasing block size, data cache size, sequence buffer length and target based commit interval.
- Written documentation to describe program development, logic, coding, testing, changes and corrections.
- Within specified time, projects are delivered making sure all the requirements gathering, business analysis and designing of the data marts.
- Involved in Unit and Integration testing of Mappings and sessions.
- Prepared ETL mapping specification document.
- Assisted Testing team in creating test plan and test cases.
- Involved in creating multi dimensional universe and reports in Business Objects Environment.
- Created Informatica mappings with PL/SQL Procedures/Functions and triggers in T-SQL to build business rules to load data. Environment: Informatica Power Center 9.1/8.6.1, Oracle 11i, MS SQL Server 2008, MS Visio, Toad, Power exchange 8.1, Business objects XIR3/XIR2, Perl.
Confidential, NY Jan 2010 - Mar 2011
Role: Informatica Developer
Description: J.P. Morgan is a leader in financial services, offering solutions to clients in more than 100 countries with one of the most comprehensive global product platforms available. I was handling applications each pertaining to various Business Areas like Human Resource (HR Data Mart), E-Learning (TIS – Training Integration System) and Finance (Fidelity, Medgate), Shared Service (ESA – Employee Shared Applications). Distributed data residing in heterogeneous data sources are consolidated into target enterprise data warehouses. This Data Warehouse is used to deliver reports and information to sales and marketing management.
- Understanding of the business requirements and enhancing the existing data warehouse architecture design for a better performance.
- Participated in designing the data model using Erwin.
- Used Informatica Designer to create, Load, Update mappings using different transformations to move data to different data marts in Data Warehouse.
- Developed PL/SQL stored procedures and triggers in T-SQL for various data cleansing activities.
- Identified and tracked the slowly changing dimensions, heterogeneous Sources and determined the hierarchies in dimensions.
- Created users and user groups with appropriate privileges and permissions, folders and folder permissions in Repository manager.
- Created source, target, transformations, sessions, batches and defined schedules for the sessions.
- Also used Data Integrators to club data from different kind of sources.
- Performed incremental aggregation to load incremental data into aggregate tables.
- Made substantial contributions in simplifying the development and maintenance of ETL by creating re-usable Source, Target, Mapplets, and Transformation objects.
- Involved in moving Mappings, Sessions and Workflows between development and production environments.
- Involved in Unit and Integrating testing of Informatica Sessions, Batches and the Target Data.
- Prepared ETL mapping Documents for every mapping and Data Migration document for smooth transfer of project from development to testing environment and then to production environment.
- Preparing and using test data/cases to verify accuracy and completeness of ETL process.
- Prepared Run books providing guidelines to troubleshoot the errors occurred during the run time and instructions on how to restart the loads.
- Actively involved in the production support and also transferred knowledge to the other team members.
Environment: Informatica Power center 8.6.1, SQL Server 2005, Oracle 11i/10g, Teradata, PL/SQL, Power exchange 8.1, TOAD, Windows XP, UNIX maestro, ERWIN 4.2
Confidential, WA Apr 2008 - Jan 2010
Role: ETL Developer
Description: Costco Wholesale is a multi-billion dollar global retailer with warehouse club operations in eight Countries. It is dedicated to quality in every area of business and respected for its outstanding Business ethics. Purpose of this project is to maintain a data warehouse that would enable the management to take corporate decisions. The project deals with building a real time view of enterprise wide data. A decision support system is built to compare and analyze product prices, their quantities and patient profiles. IMS Health data is combined with data from other sources and is made available for reporting. This Data Warehouse is used to deliver reports and information to sales and marketing management.
- Created Dimension Tables and Fact Tables based on the warehouse design.
- Involved in creating Logical and Physical design of databases using Erwin.
- Participated in build of the Data Warehouse, which includes the Design of Data mart using Star Schema.
- Created ETL detail design document and ETL standards document.
- Created Repository using Informatica Power Center – Repository Manager.
- Extracted data form flat files and oracle database, applied business logic to load them in the central oracle database.
- Involved in Extraction, Transformation and Loading (ETL) Process.
- Created necessary Repositories to handle the metadata in the ETL process
- Worked extensively with complex mappings using expressions, aggregators, filters, lookup and procedures to develop and feed in Data Warehouse.
- Created reusable transformations and Mapplets and used them in mappings.
- Worked on SQL queries and tuned them to improve performance. ? Extensively used Shell scripts to automate the Pre-Session and Post-Sessions processes.
- Performed data manipulation using basic functions and Informatica Transformations.
- Designed & developed reports, Drilldown reports using Business Objects Reporter.
- Designed reports with Slice and Dice & Drill down operations.
- Developed ETL mappings, transformations using Informatica powercenter
- Implemented source and target definitions in workflow designer.
- Developed mappings using various transformations like update strategy, lookup, stored procedure, Router, Filter, sequence generator, joiner, aggregate transformation and _expression.
- Mappings were designed to cater the slowly changing dimensions.
- Used various stored procedures in the mappings.
- Developed several reusable transformations and mapplets that were used in other mappings.
- Worked on the server manager to run several sessions and batches concurrently and sequentially.
- Mappings were designed to the utmost performance by optimizing expressions in expression editor of transformation, filtering source data at the source qualifier, and applying transformation logic.
Environment: Informatica Power center 8.6.1, Oracle 10g, SQL Server 2005, ERWin 4.2, Teradata, Perl, UNIX (AIX), Windows XP, Business objects XIR2
Confidential, Nepal Aug 2006 – Mar 2008
Role: Database Developer
Himalayan Bank is the second-largest bank in Nepal, with branches and extension counters and ATMs throughout the country. The application system provided end-to-end support for Loan processing for online loan application, loan interest rates, adjustable and fixed rate loans, fixed/ floating rate loans, balance transfer of existing loans from other banks, compliance, reporting and treasury operations. This system involves an OLTP system and Data Warehouse system for loan processing and reporting.
Environment: Informatica Powercenter, Oracle, Windows NT, PL/SQL, STAR Schema, Flat files.
Diploma in Science Computer Engineering
Associate Degree in Science, First Division, Honors Student
Major in Mathematics and Physics
US PERMANENT RESIDENT
References Upon Request