- Around 8 years of experience in Information Technology with emphasis on Data Warehouse/Data Mart development using developing strategies for extraction, transformation, and loading (ETL) in Informatica Power Center 9.5/9.1/8.6 from various database sources.
- Strong work experience in Data Mart life cycle development, performed ETL procedure to load data from different sources like SQL Server, Oracle, Teradata, COBOL files & XML Files and flat files into data marts and data warehouse using Power Center Repository Manager, Designer, Server Manager, Workflow Manager, and Workflow Monitor.
- Involved in understanding of Business Processes, grain identification, identification of dimensions and measures for OLAP applications.
- Highly motivated and goal - oriented individual with a strong background in SDLC Project Management and Resource Planning using AGILE methodologies
- Strong knowledge of Entity-Relationship concept, Facts and Dimensions tables and developing database Schemas like Star schema and Snowflake schema used in relational, dimensional and multidimensional data modelling.
- Comprehensive experience of working with Type1, Type2 methodologies for Slowly Changing Dimensions (SCD) management.
- Proficient in the Integration of various data sources with multiple relational databases like Oracle11g /Oracle10g/9i, MS SQL Server, Teradata, Flat Files into the staging area, ODS, Data Warehouse and Data Mart.
- Extensive experience in developing Stored Procedures, Functions, Views and Triggers, Complex SQL queries using SQL Server, TSQL and Oracle PL/SQL.
- Performed the data profiling and analysis making use of Informatica Data Explorer (IDE) and Informatica Data Quality (IDQ).
- Experience on Debugger to validate the mappings and gain troubleshooting information about data and error conditions.
- Good working knowledge of various Informatica designer transformations like Source Qualifier, Dynamic and Static Lookups, connected and Unconnected lookups, Expression, Filter, Router, Joiner, Normalizer and Update Strategy transformation.
- Extensively used Teradata Utilities like Tpump, Fast-Load, MultiLoad, BTEQ and Fast-Export.
- Worked on Performance Tuning , identifying and resolving performance bottlenecks in various levels like sources, targets, mappings and sessions.
- Experience in Task Automation using UNIX Scripts, Job scheduling and Communicating with Server using pmcmd.
- Extensively used Control-M for Job monitoring and scheduling.
- Excellent interpersonal and communication skills, and is experienced in working with senior level managers, business people and developers across multiple disciplines.
ETL Tools: Informatica Power Center 9.5/9.1/8.6, Informatica B2B-DT, Informatica Power Exchange, Informatica Data Quality, and Informatica Power Connect.
RDBMS: Oracle 11g/10g/9i/8i, MS-SQL Server 2008/2005/2000 , MS-Access and Teradata 12.0/13.0/14.0 , Sybase, IBM DB2.
Tools: TOAD, MS VISUAL STUDIO, SQL *PLUS, ERWIN, Business Objects, AUTOSYS, Control-M.
Programming Languages: UNIX, Shell Scripts, SQL, PL/SQL, T-SQL, XML, VB, and ASP, HTML, Java Script, CSS.
ETL Developer, SUNNYVALE, CA
- Used Workflow Manager to load data from different sources (Oracle, SQL Server, Text files) to the target database, which was Oracle.
- Populated and maintained Fact and Dimension tables in Daily Data Star Schema.
- Imported various Sources and Targets, created Transformations using Informatica Power Center Designer.
- Designed and developed Informatica Mappings and Sessions based on requirements and business rules to load data from source flat files and Relational target tables. Used transformations like Connected and Unconnected lookups, Aggregator, Expression, Update, Router and Sequence generator.
- Used joiner transformation to extract data from different tables.
- Implemented Error handling and E-mail notifications using pre-session, post-session stored procedure transformation (Connected and unconnected).
- Used Informatica Workflow Manager to create, schedule, monitor and send the error messages in case of process failure.
- Created global and local folders and granted permissions using Informatica Repository Manager.
- Did performance tuning at source, transformation, target, and workflow levels.
- Created staging and dimension tables in SQL Server database using SQL Query Analyzer and SQL Server Enterprise Manager.
- Constantly interacted with Data warehouse Architecture team to understand the requirements and implement them seamlessly.
- Wrote user defined stored procedures using SQL Query Analyzer.
- Provided daily production support by monitoring the processes running.
- Did Unit testing for data errors based on timestamp, and checked counts against daily summarized data and weekly summarized data.
Environment: Informatica Power Center 9.1, SQL Server Enterprise Manager, SAP PowerDesigner, SQL Query Analyzer, SQL Server 2000, Flat Files.
INFORMATICA Developer, REDMOND, WA
- Involved in analysis of source systems, business requirements and identification of business rule and responsible for developing, support and maintenance for the ETL process using Informatica.
- Created / updated ETL design documents for all the Informatica components changed.
- Extracted data from heterogeneous sources like oracle, xml, DB2, flat file and perform the data validation and cleansing in staging area then loaded in to data warehouse in oracle 11g.
- Made use of various Informatica source definitions viz. Flat files and Relational sources.
- Made use of various Informatica target definitions viz. relational data base targets.
- Created Informatica transformations/mapplets/mappings/tasks/worklets/workflows using Power Center to load the data from source to stage, stage to persistent, stage to reject and stage to core.
- Used Informatica B2B data transformation to read unstructured and semi structured data and load them to the target.
- Made use of various PowerCenter Designer transformations like Source Qualifier, Connected and Unconnected Lookups, Expression, Filter, Router, Sorter, Aggregator, Joiner, Rank, Router, Sequence generator, Union and Update Strategy transformations while creating mapplets/mappings.
- Made use of reusable Informatica transformations, shared sources and targets.
- Created different parameter files and changed Session parameters, mapping parameters, and variables at run time.
- Implemented various loads like daily loads, weekly loads, and quarterly loads and on demand load using Incremental loading strategy and concepts of changes Data Capture (CDC).
- Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating transformations and tuned accordingly for better performance.
- Created mappings for Type1, Type2 slowly changing dimensions (SCD) / complete refresh mappings.
- Extensively used various Data Cleansing and Data Conversion functions like LTRIM, RTRIM, TO DATE, Decode, and IIF functions in Expression Transformation.
- Extensively used the Workflow Manager tasks like Session, Event-Wait, Timer, Command, Decision, Control and E-mail while creating worklets/workflows.
- Worked with “pmcmd” command line program to communicate with the Informatica server, to start, stop and schedule workflows.
- Created Job Stream and added job definitions in Control-M and executed
- During the course of the project, participated in multiple meetings with the client and data architect / ETL architect to propose better strategies for performance improvement and gather new requirements.
Environment: Informatica Power Center 9.5,Informatica B2B, Erwin, Oracle 11g, DB2, XML, Flat Files, HTML, Java Script Win7, SQL * Plus, Control-M, Toad and UNIX.
Confidential, Madison, WI
- Involved in requirements gathering, analysis, function/technical specifications, development, deploying and testing.
- Prepare/maintain documentation on all aspects of ETL processes to support knowledge transfer to other team members.
- Used Informatica Power Center for migrating data from various OLTP databases to the data mart
- Worked with different sources like Oracle, flat files, XML files, MS SQL Server.
- Created Mappings using Mapping Designer to load data from various sources, Made use of various Designer transformations like Source Qualifier, Connected and Unconnected Lookups, Expression, Filter, Router, Sorter, Aggregator, Joiner, Normalizer, Rank, Router, Sequence generator, Union and Update Strategy transformations.
- Created mapplets using Mapplet Designer and used those Mapplets for reusable business process in development.
- Worked on different tasks in Workflows like sessions, events raise, event wait, decision-mail, command, worklets, Assignment, Timer and scheduling of the workflow.
- Performance tuning of Informatica designer and workflow objects.
- Used Teradata Utilities (SQL Assistant, BTEQ, MultiLoad, and FastLoad) to maintain the database
- Created pre/post session commands and pre/post session SQLs to perform tasks before and after the sessions.
- Build a re-usable staging area in Teradata for loading data from multiple source systems using template tables for profiling and cleansing
- Implemented slowly changing dimensions (Type I and Type II) for customer Dimension table loading.
- Created UNIX KSH shell scripts to kick off Informatica workflow in batch mode.
- Invoked Informatica using “pmcmd” utility from the UNIX script Teradata using “BTEQ”.
- Involved in Unit testing, Iterative testing to check whether the data loads into target are accurate, which was extracted from different source systems according to the user requirements.
- Provided support for the applications after production deployment to take care of any post-deployment issues.
Environment: Informatica PowerCenter 9.1, Informatica IDQ, Teradata 14.0, Teradata SQL, UNIX shell scripting
- Involved in business analysis and technical design with business and technical staff to develop requirement document and ETL specifications.
- Developed the detailed database design document which included data dictionary, data estimates, entity relationship (ER) diagrams, dimensional model (Snowflake schema) using Erwin, MS Office and MS Visio
- Extensively Used Informatica Designer as an ETL tool to extract data from legacy source (Mainframe) systems to Target system.
- Extracted data from multiple operational sources for loading into staging area, Data warehouse, Data Marts using SCD's (Type 1 and Type 2) loads. In this process, Informatica is used to implement business rule, transform and load.
- Created ETL Mappings, sessions and workflows using Informatica Power Center to move Data from multiple sources like XML, DB2, SQL Server, and Oracle into a common target such as Data Marts and Enterprise Data warehouses. And used direct and indirect flat files (delimited and fix width) as a source of data.
- Used Informatica Designer to create complex mappings using different transformations like Filter, Router, Connected & Unconnected lookups, Stored Procedure, Joiner (all type of joiner), Update Strategy, Expressions and Aggregator transformations to pipeline data to Data Mart and Enterprise Data Warehouse.
- Extensively used parameter file to pass mapping and session variables, and parameters.
- Tuned performance of sessions and mapping. Identified source and target bottleneck, transformation error, and resolve the problem.
- Involved in Unit testing. Created the test case documents for individual mappings and tasks.
- Automation and scheduling of UNIX shell scripts and Informatica sessions and batches using Control-M.
Environment: Informatica Power Center 8.6, Solaris Server 10, Oracle 10g/11g, DB2, Control-M, Toad, Erwin, MS Office and MS Visio.
- Documented user requirements, Translated requirements into system solutions.
- Extensively participated in functional and technical meetings for designing the architecture of ETL load process.
- Developed Mapping Documents indicating the source tables, columns, data types, transformation required, Business rules, target tables, columns and data types.
- Developed numerous Complex Informatica Mappings, Mapplets and Aggregator, Joiner, Filter, Source Qualifier, Expression, Router, Lookup and Update Strategy Transformations.
- Created different Tasks in workflows which included Sessions, Commands, Worklets, Decision Task, E-mail, Event-Wait etc.
- Generated Email Notifications through scripts that run in the Post session implementations.
- Worked with complex SQL queries for data validation and reporting.
- Worked on Performance Tuning of the complex transformations, mappings, sessions and SQL queries to achieve faster data loads to the data warehouse.
- Prepared Test Cases for testing each of the interfaces which were used by the QA team.
- Performed unit testing, system integration test as well as user acceptance testing of the developed code.
- Extensively used Informatica Metadata to analyze and manage repository efficiently.
- Performed Code review to ensure that ETL development was done according to the company's ETL Standard and that ETL best practice were followed.
Environment: Informatica 8.1.1 Power Center, Teradata, Oracle 10g, Windows XP.
RELIANCE TECHNOLOGIES INC.
- Developing the earlier Custom Web portal application using ASP.NET and HTML5.
- In-depth understanding of processes involving data modeling and building data marts and translated the business processes into Informatica mappings.
- Developed complex mappings using various transformations like Aggregator, Joiner, Union, Lookup, Expression, Filter, Update Strategy, Router and Transaction Control.
- Developed ETL workflows, sessions for initial full loading and incremental loading.
- Implemented entire ETL job scheduling using Autosys and monitored the Informatica jobs on daily basis and made a force start whenever job fails in DEV, QA and UAT environments.
- Created shell scripts to FTP files from different server to Informatica server.
- Worked on making session runs more flexible through the use of mapping parameters and variables and used parameter files and variable functions to manipulate them.
- Performed Unit testing and created Mapping documents, code review documents, Unit Test Plan documents to track the overall SDLC process.
- Experienced in Data Partitioning to improve performance of the mapping.
- Efficient in all phases of the development lifecycle, coherent with Data Cleansing, Data Conversion, Performance Tuning and communicated Defect Status Reports to business in project meetings.
Environment: Informatica Power Center 7.1.2, Toad for Oracle, PL/SQL, Toad for SQL Server, Autosys, Windows, UNIX.