Etl Developer Resume
TX
SUMMARY
- With 7+ years of extensive IT experience in all aspects of Software Analysis, Design, Development, Implementation, Testing of Data warehousing Systems and Data marts.
- Experience Database development experience using SQL server 2005/2000/7.0/6.5/6.0 , Oracle 9i/8i/7.3, PL/SQL, NCR Teradata V2R5/V2R4/V2R3, SQL*Plus, Developer 2000, and MS Access 2000/97/7.0.
- Experience in OLTP/OLAP systems, Data warehousing concepts, Dimensional Star Schema and Snowflakes Schema methodologies used in relational, dimensional and multidimensional modeling, Data Profiling and Data cleansing.
- Hands on experience in tuning mappings, identifying and resolving performance bottlenecks in various levels like sources, targets, mappings, and sessions.
- Experience with Informatica Server Manager to create sessions and workflows to run with the logic embedded in the mappings.
- Experience in writing scripts like SQL scripts, PL/SQL Stored Procedures, Indexes, Functions, Views, Materialized Views and Triggers.
- Extensively worked on Informatica Client Tools - Source Analyzer, Target Designer, Transformations Developer, Mapplet and Mapping Designer, Task Developer, Workflow Monitor.
- 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.
- Expertise in Query Analyzing, performance tuning of Informatica mappings.
- Expertise in working with Oracle Stored Programs, Packages, Cursors, Triggers, Tables, Constraints, Views, Indexes, Sequence, and Synonyms.
- Working Experience in E-R modeling-FACT& Dimensions tables, Physical & logical data modeling.
- Extracted data from various sources like Oracle, DB2, Flat file, SQL Server, XML files and loaded into Oracle database.
- Experience in Complete Software Development Life Cycle (SDLC).
- Expert in Troubleshooting/ Problem Solving.
- Excellent communication and interpersonal skills. Ability to work effectively while working as a team member as well as individually.
TECHNICAL SKILLS
Data Warehousing: Informatica PowerCenter 9.5.1/8.1/7.1 , Business Objects X1 (R2) Desktop Intelligence/Web Intelligence, Star and Snowflake Schema, SQL*Plus, SQL*Loader. Informatica Versioning Tool
Dimensional Data Modeling Tools: Dimensional Data Modeling, Data Modeling, Star Schema Modeling, Snow-Flake Modeling, Fact and Dimensions Tables, Logical and Physical Data Modeling, ERWIN 4.0/3.5.2/3. x, Oracle Designer.
Databases: Oracle 9i/8i/8.0/7.0, DB2, MS SQL Server 2008/2005/2000/7.0/7.0 , MS Access 7.0/97/2000, Sybase 12.0/11.x, Teradata V2R5/V2R4/V2R3
Data Modelling: Erwin 3.5.2, Microsoft Visio, and ER Studio 9.1
Scheduling Tools: Autosys, Informatica Scheduler, Unix Scheduler
Profiling Tools: Informatica Profiler
Versioning Tools: Microsoft Visual Studio (Team Foundation Server), CVS (Concurrent Version System), PVCS Version Manager.
Operating Systems: UNIX, Linux, Windows NT/2000/XP
Programming: SQL, PL/SQL, IDE, SQL Query Analyzer 8.0, ISQL, ANSI SQL, Transact SQL, SQL Navigator, Windows X Professional, SQL Management Studio, SQL*Plus 3.3/8.0, SAP R3 Toad, Visual Basic 6.0/5.0, HTML, C, C++, Unix Shell Scripting, Python win 2.7.2, AIX, Control-M, Autosys
PROFESSIONAL EXPERIENCE
Confidential, TX
ETL Developer
Responsibilities:
- Understanding the existing business model and requirements, involved in requirements gathering and understanding business needs.
- Involved in analyzing the requirements and created Design Documents and Data Mapping Documents.
- Worked with Source Analyzer, Warehouse designer, Mapping, Mapplet Designer, Transformations, Workflow Manager and Monitor.
- Developed mappings using various transformations such as the Source qualifier, Router, Filter, Sequence Generator, Aggregator, Lookup (Connected & Un-Connected), Stored Procedure and Expression as per the business requirement.
- Used Mapping Parameters and Variables to implement object orientation technologies and facilitate the reusability of code.
- Developed mappings to implement Slowly Changing Dimensions (Type 1 & 2).
- Involved in performance tuning by identifying the bottlenecks at source, target, mapping, session, and database level.
- Design and development of UNIX Shell Scripts to handle pre and post session processes and also for validating the incoming files.
- Developing the SQL scripts in TOAD and creating Oracle Objects like tables, materialized views, views, Indexes, sequences, synonyms and other Oracle Objects.
- Debugged mappings by creating logic that assigns a severity level to each error, and sending the error rows to error table so that they can be corrected and re-loaded into a target system.
- Developing the unit test case document and performed various kinds of testing like unit testing, regression testing and system test in Dev, QA environments before deployment.
- Developed Audit Strategy to validate the data between Source and Target System.
- Involved in Production Support.
- Supported the Implementation and Post-Implementation process.
Environment: Informatica PowerCenter 8.6.1 (Repository Manager, Designer, Workflow Manager, Workflow Monitor), Power Exchange Navigator 8.6.1, IDE, SQL Query Analyzer 8.0, Oracle 9i/10g, SQL Server 2005, SQL Server Management Studio, Toad, SQL Developer 1.1.2, PVCS, Tivoli, AIX, Sun Solaris & Windows NT, Shell Scripting.
Confidential, Yardley, PA
Informatica Developer
Responsibilities:
- Responsible for Business Analysis and Requirements Collection.
- Parsed high-level design specification to simple ETL coding and mapping standards.
- Designed and customized data models for Data warehouse supporting data from multiple sources on real time.
- Loaded the data from different vendors to claims data mart.
- Involved in data modeling to implement star schema database.
- Involved in understanding the business requirement, identifying the grains, designing the fact tables with key performance indicators and establishing the relations between the dimensions.
- Worked on loading data at different levels Stage, ODS, Warehouse.
- Used Informatica to load data into Stage and ODS and created Oracle packages to load the data from ODS to warehouse.
- Also created complex Oracle packages, procedures, cursors, triggers to load the data into warehouse.
- Extensive use of partitioning and indexing for high performance of query retrievals.
- Building the ETL architecture for Source to Target mapping to load data into Data warehouse.
- Worked on transformations like filter, router, connected lookup and unconnected lookup, update strategy, expression etc.
- Worked on performance tuning of existing mappings.
- Extensively used reusable transformations, mapplets and worklets to reuse in them globally.
- Extensively used Erwin for data modeling and Dimensional Data Modeling.
- Responsible for the Dimensional Modeling and populating the business rules using mappings.
- Created adhoc reports for the business when ever asked.
- Responsible for performance tuning on tables having high volume of data (Terabytes) by partitioning the tables and indexing for user retrieval.
- Loading huge vendor flat files into data warehouse every day using Informatica and oracle packages.
- Created complex sql to test the data.
- Loaded data into targets using slowly changing dimensions type 2.
- Used SQLPLUS for spooling the huge tables to file’s for the end user’s.
- Implemented Explain Plan and SQL Trace for improving the performance of SQL queries.
- Designed ETL process flow for entire DWH application and developed data mapping spread sheets to define transformation rules for each stage of ETL process
- Involved in applying patches and updates to the server.
- Loading SAP R3 data through Informatica and using power connect to connect to SAP.
- Was responsible for the version control and up-gradation.
- Extensively used scripts for data cleansing and data loading.
- Coordinated with source system owners, monitored day-to-day ETL progress, performed Data warehouse target schema Design (Star Schema) and maintenance.
- Extensively used Workflow manager to create and schedule various Workflows and Worklets.
- Created Universe in Business objects in Designer.
- Developed various types of reports like Simple, Drill down Report, Cross Tab reports.
- On time delivery of all kinds of Adhoc reports with excellent performance.
- Worked in unit testing each workflow and then testing after all the workflows are run in a batch process.
- Designed and developed UNIX scripts as a Wrapper script as well as to implement Code.
- Involved in Data Quality Analysis to determine cleansing requirements.
- Designed and developed Informatica mappings for data loads.
- Performed Development, Test and Production mapping migration using Repository Manager. Also used Repository Manager to maintain the metadata, Security and Reporting. Tuned the Informatica Repository and Mappings for optimum performance.
Environment: Oracle 10G, Informatica 8.6.1 (Repository Manager, Admin Console, Designer, Workflow Manager, Workflow Monitor), Power Connect, SAP R3Toad, SQL Navigator, SQL Plus, Erwin 4.0, PL/SQL, Windows XP professional, Erwin 3.5.2, AIX, Shell Scripting.
Confidential, New York, NYETL Developer
Responsibilities:
- Study and analyze mapping document, the required source tables, data types, required transformations based on Business requirements and Technical specifications.
- Using Informatica PowerCenter, extracting data from Mainframes, SQL Server, XML files and Flat Files.
- Developed complex mappings in Informatica to load the data from various sources using different transformations like Source Qualifier, Look up (connected and unconnected), Expression, Aggregate, Update Strategy, Sequence Generator, Joiner, Filter, Update Strategy Rank and Router transformations.
- Expertise in Developing Mappings, Define Workflows &Tasks, Monitoring Sessions, Export & Import Mappings and Workflows and Backup, Recovery.
- Used SQL over rides in Source Qualifier and Lookups to improve mapping performance.
- Developing Reusable Transformations, Aggregations and created Target Mappings that contain business rules.
- Extensively worked with various Passive transformations in Informatica PowerCenter like Expression Transformation, Sequence Generator, and Lookup Transformation.
- Worked with various active transformations in Informatica PowerCenter like Filter Transformation, Aggregator Transformation, Joiner Transformation, Rank Transformation, Router Transformation, Sorter Transformation, Source Qualifier, and Update Strategy Transformation.
- Created and Configured Workflows, Worklets and Sessions to transport the data to target warehouse Oracle tables using Informatica Workflow Manager.
- Created Connected and Unconnected lookup transformation for better performance of the mappings and sessions.
- Developed simple &complex mappings using Informatica to load Dimension & Fact tables as per STAR Schema techniques.
- Optimized the performance of the mappings by various tests on sources, targets and transformations.
- Developed PL/SQL stored procedures and UNIX shell scripts for pre and post session commands, batch jobs.
- Developed UNIX Shell Scripts and SQLs to get data from Oracle tables before executing Informatica workflows.
- Providing extensive changes as a part of User acceptance testing and deployment of mappings at client place.
- Performed extensive testing on the mappings and wrote queries in SQL to check if the data was loading to the dimension tables and fact tables properly.
Environment: Informatica 8.6.1, ORACLE 10g, TOAD, SQL SERVER 2008, Control-M, Quality Center.
Confidential, Boston, MA
Sr. Informatica ETL Developer
Responsibilities:
- Worked on Customer Segmentation Cutover Process, part of which involves the update of all associated Informatica code, update of associated SQL views in the data mart tables.
- Worked on the SLA Development Project-For all Data Warehouse subject areas below the DW operations team is responsible to send a notice to the distribution lists if the data delivery expectations are not met.
- Extensively worked with Informatica Repository tables to analyze workflows, tasks, worklets run statistics and developed code to enable reporting of the delay in SLA for the scheduled batch runs daily and weekly.
- Worked on Model Execution Process Project which involved many updates and enhancements proposed by the business which need to be validated at the DW phase, the QA phase and finally the Reporting phase for the scheduled reports to be delivered to the business team.
- Worked on re-scheduling a weekly run job-part of which involved analysis of dependencies on daily run batch tables and weekly run batch tables. The intricate dependencies of all the daily and weekly batch tables were analyzed, as to whether they were time driven, event driven, driven off a schedule.
- Part of multiple implementation plans involving DW implementations, BI team implementations.
- Participated in an Agile BI/DW team setting, As a Core team member, participated in the daily scrum meetings for reporting updates on onsite work as well as the offshore development team.
- Participated on-call support to the Data Warehouse nightly batch monitoring.
- Worked on Production Support issues such as validation of SQL query, data-type mismatches, checking for database connectively, and Stored Procedure connectivity, Dropping indexes in independent session and re-creating them in different sessions, workflow session architecture setup.
- Worked with multiple source systems DB2, SQL Server, Oracle databases.
- 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 Expression, Aggregator, Connected and Unconnected Look-up, Router, Joiner, Store Procedure Transformations using Informatica PowerCenter 9.5.1.
- Unit Testing was done extensively with comprehensive test cases and scenarios to validate the data in the DW tables. Unit testing was documented at every stage of the code for the tables.
- Extensively worked with SQL for unit testing as well as during developmental phases.
- Extensively worked with nested workflows involving both reusable and non-reusable worklets.
- Extensively worked with Informatica Versioning and Informatica Scheduler.
- Developed, transformed and tested business required data enhancements using ETL processes.
- Worked on impact analysis of different transformations, targets and sources by querying the Informatica Repository OPB tables, extensively worked with Informatica dependency tool to identify the associated mappings, session, worklet and workflow to analyze dataflow from ODS to DM tables.
Environment: Informatica PowerCenter 9.5.1/8.1, SQL server 2008, Oracle 10g, DB2, Toad, Unix, Informatica Versioning Tool, TFS, Informatica Scheduler.
Confidential, Attleboro, MA
Informatica ETL Consultant
Responsibilities:
- Interact with Business Analyst to assist him in Understanding the Source and Target System.
- Responsible to pull the data from 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.
- Worked on different tasks in Workflows like sessions, events raise, event wait, decision mail, command, worklets, assignment, timer and scheduling of the workflow.
- Worked on Power Center Designer tools like Source Analyzer, Warehouse Designer, Mapping Designer, Mapplet Designer and Transformation Developer.
- Used Debugger within the Mapping Designer to test thedata flowbetweensource and targetand to troubleshoot the invalid mappings.
- Efficiently implemented Change Data Capture (CDC) to extract information from numerous Oracle tables and experienced Pushdown Optimization in Informatica Power Center.
- Designed and developed UNIX Shell scripts to report job failure alerts.
- Used Workflow Manager for Creating, Validating, Testing and running the sequential, parallel, and initial and Incremental Load.
- Used Version Control to check in and checkout versions of objects.
- Worked on SQL tools like TOAD and SQL Developer to run SQL Queries and validate the data.
- Schedule Informatica Jobs through Autosys scheduling tool.
- Involved in Unit testing, System testing and UAT to check data consistency.
- Assisted QA team to fix and find solutions for the production issues.
- Prepared all documents necessary for knowledge transfer such as ETL strategy, ETL development standards, ETL processes, etc.
Environment: Informatica Power Center 9.1, Oracle 10g, Erwin, PL/SQL, Autosys, MS-SQL Server 2008, Toad, MS-Visio, Windows XP
Confidential, Boston, MA
ETL and Reporting Developer
Responsibilities:
- Experience communicating with Business and the Analysts to Understanding the Source and Target Systems.
- Modified existing / developed new complex Informatica Power Center Mappings to extract and pull the data according to the guidelines provided by the business users and populate the data into Target Systems.
- Experience extracting data from different Source Systems like Relational (MS SQL Server), COBOL files and EXCEL files.
- Using different transformations like Source qualifier, Joiner, Look up, Update strategy, Normalizer, Aggregator, Ranker, Sequence Generator, Filter, Router and various other transformations for manipulating the data and loading it into targets.
- Efficiently implemented Change Data Capture (CDC) to extract information from numerous SQL Server Tables.
- Scheduled Workflows/Sessions and Batches on the Informatica Server using Informatica scheduler and designed/developed scheduling strategies taking into consideration various dependencies on other applications and also resource constraints.
- Involved in Writing Shell scripts to automate the Pre-Session and Post-Sessions Processes.
- 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 data base standpoint.
- Involved in preparing Model move Forms for migration of code across environments.
- Extensively used Quality center which served as a medium for communication across the Business analysts as well as the testing teams for addressing defects and other issues during the period.
- Worked on SQL tools like TOAD and SQL Developer to run SQL Queries and validate the data.
- Involved in Unit testing, System testing and UAT to check data consistency.
- Assisted QA team to fix and find solutions for the production issues
- Prepared all documents necessary for knowledge transfer such as ETL strategy, ETL development standards, ETL processes, etc.
Environment: Informatica PowerCenter 8.1, Business Objects 6.0, Oracle 9i, TOAD, UNIX, Windows NT, SQL assistant 6.2, CSV files, Fixed width files, VSAM files.
Confidential
Informatica Developer
Responsibilities:
- Involved in design & development of operational data source and data marts inOracle.
- Reviewed source data and recommend data acquisition and transformation strategy.
- Involved in conceptual, logical and physical data modeling and used star schema in designing the data warehouse
- Designed ETL process using Informatica Designer to load the data from various source databases and flat files to target data warehouse in Oracle
- Used Power mart Workflow Manager to design sessions,
- Created parameter based mappings, Router and lookup transformations.
- Created Mapplets to reuse the transformation in several mappings.
- Used Power mart Workflow Monitor to monitor the workflows.
- Optimized mappings using transformation features like Aggregator, filter, Joiner, Expression, Lookups.
- Created daily and weekly workflows and scheduled to run based on business needs.
- Created Daily/weekly ETL process which maintained 100GB of data in target database.
- Involved in Unit Testing, Integration Testing, and System Testing.
Environment: Informatica Power mart 7.0, Oracle 8i, Erwin 3.5, Shell scripting, Windows 2000.