We provide IT Staff Augmentation Services!

Lead Etl Architect Resume Profile

Summary of Qualifications:

  • An accomplished IT professional with strong background in Database development and Data warehousing as a hands-on ETL Informatica Architect / Admin / Developer.
  • Strong understanding of wok experience in domains such as Healthcare Insurance, Retail, pharmaceutical and Financial/Mutual Funds Sectors.
  • Data Processing experience in designing and implementing Data Mart applications, mainly transformation processes using ETL/Informatica PowerCenter 9.01/8.5.1/8.1/7.1.3/6.2.1 Designer, Workflow Manager, and Workflow Monitor and Administration Console .
  • Well versed with use of PowerConnect for SAP NetWeaver, IBM MQ Series and PowerExchange for DB2 Data Maps, VSAM Files and Change Data Capture CDC for oracle source.
  • Hands-on Experience in installation, upgrading patches, configuration, and Administration of Informatica PowerCenter client/servers. Worked on installation and configuration of Business Objects 5.1.4 Client tools.
  • Expertise in data extraction and loading to and from the SAP R/3 Systems using iDOCS, LSMW, BAPI and ALE.
  • Extensive experience in implementation of Data Cleanup procedures, transformations, Shell Scripting, Stored Procedures and execution of test plans for loading the data successfully into the targets.
  • Emphasized on Performance of Mapping Optimizations in Informatica Client/Administration tools.
  • Expertise in developing SQL and PL/SQL block codes through various Stored Procedures, Functions, and Triggers to implement the business logics of database in Oracle, DB2 and SQL Server 2000/2005.
  • Worked on various heterogeneous databases: Oracle, SQL Server, DB2, Teradata, Sybase, Flat files and XML in creating reports, transactions and conversions.
  • Experience on databases such as Teradata V2R4/V2R5, Oracle 10g, 9i, and 8i , Sybase, IBM writer, SQR 6.1.4.
  • Experienced in using the Oracle SQL Loader for loading the data from Flat Files to DB tables for Bulk Loading.
  • Automation and scheduling of Informatica jobs using Crontab, AutoSys and Control M.
  • Understanding in writing Test plans, Test cases, Unit System testing, Integration testing and Functional testing.
  • Technical abilities are complemented by sound communication and good user interaction abilities, well versed with the quality processes.

Technical Competencies:

  • Job Function: Developer, Administrator, Maintenance, Analysis, Programming, Testing, Documentation, DPR Defect Prevention Representative , CM Configuration Manager
  • Data Modeling Tool: Erwin 4.0/3.5, Microsoft Visio, Power builder, OLAP, ROLAP, MOLAP, Rochade
  • Data Warehouse/ETL Tool: Informatica PowerCenter 9.0.1, v8.5.1,8.1.1,7.1.3,6.2 Informatica PowerMart v6.2, PowerExchange, MS SQL SERVER Integration Services
  • Databases: Oracle 10g,9i,8i,7 , IBM DB2/UDB 9.7, Informix, Sybase, MS SQL Server 2000/2005, Teradata, Siebel, MS Access, IBM Mainframes and AS/400.
  • Business Intelligence Tool: Cognos ReportNet 1.1, Business Objects 6.0/5.1/4.x
  • ERP Tools: SAP R/3 releases 4.6B, and 4.6C
  • Languages: PL/SQL, Core Java 1.5, C and C
  • Web Tools: HTML, DHTML, XML
  • Scripting: UNIX Shell Scripting, PERL
  • Tools: WinSQL, SQL Loader, MS Project 2003, MATLAB 9, AutoSys, Control M, TOAD, PowerBuilder 11.5, RAD, IBM Control Center
  • Version Control: MS Visual SourceSafe, IBM Rational ClearCase 7, PVCS
  • Operating Systems: Microsoft Windows 98/2000/XP/NT/Windows 7, UNIX, Linux 4, Sun Solaris 8/7

Professional Experience:


ETL Architect / Admin / Developer

  • Large Area Group Experience Reporting Completed/Supporting , to build dimensional tables for reporting.
  • Group Renewals Completed/Supporting , to build monthly processes of different coverage's into new database systems for formula calc, reporting and user experience.
  • Agency Experience Programming Completed/Supporting , on distribution side for consolidating the agencies, agents and producers information into new Web Portal.
  • Agent Compensation Transformation Initiatives Working Currently , conversion project to house the entire FAS/UCLA Legacy data into single ODS environment. Also, to develop and accommodate compensations/Formula calculation data into IBM Varicent systems.

Architect - Roles and Responsibilities:

  • Helped creating ETL database modules for Design, Development, Analytical/Reporting purposes.
  • Followed Companies best practices, object naming conventions, design terminology and reporting standards.
  • Assisted various development groups and application developers, to chart out the overall successful flow of data into multiple target systems.
  • Identified potential risks/sub-standards and fixed them in the new projects.

Development - Roles and Responsibilities:

  • For the above projects scope and some not mentioned - My Major roles as Informatica SSIS Lead Developer is to help Guardian's Data Integration projects finish successfully and document them for future analysis.
  • Created UNIX shell Scripts to process jobs run scripts, generate parameters and merge statements , safe FTP transfer of Data Files and/or trigger completion ETL Workflow process.
  • Created complex Informatica mappings to support Slowly Changing Dimensions , Change Data Capture and Transactional loads for various systems
  • Worked on unit testing, system integration testing data validation, migrations to UAT Production environments.
  • Parameterize database connections, schemas, criteria's for smooth migration into different environments.
  • Created Technical documentation run-books, technical understanding and standards for project charter sign off.

Administration - Roles and Responsibilities:

  • On call Monthly rotation bi-weekly recycle repositories during scheduled window.
  • Meetings with Oracle DB teams for any downtime maintenance usually for upgrade patches /fix apply
  • Responsible for Informatica Admin duties, error handling, scheduling maintenance.
  • Automate Informatica Scheduling process using CRONTAB. Maintain Inventory for the batch jobs, person responsible for the each application group email lists
  • Assisted Informatica up-gradation to 9.0.1 on Client/Server and tested applications
  • Participated in semi annual DR Exercises and tested failover of the major ETL business batch jobs.
  • Supporting Microsoft SSIS Packages deployments, scheduling and debugging.
  • ServiceNow To process business tickets and incidents.
  • Environment: Informatica PowerCenter 8.1.1 SP5, DB2 UDB 9.7, MSSQL Server 2005/2008,SSIS/SSRS, Oracle 10g, Siebel, Teradata, Java/J2EE, Eclipse, IBM MQ series ,PL/SQL, SQL Loader, UNIX AIX , Lotus Notes


Lead ETL Developer

Development - Roles and Responsibilities:

  • Worked as a ETL Developer to furnish specs and document them for business requirements
  • Upgraded Informatica 8.1.1 SP5 on Windows environment.
  • Installed SQL Server 2005 client on windows for testing one of the target database.
  • Worked on Aggregator, Filter, Router, and Lookup, Update strategy, Expression and SAP/ALE IDOC transformation.
  • Processed Informatica to 'create Inbound IDoc Intermediate Documents Mappings' to send the SAP Inbound iDocs to SAP systems and to 'create custom Outbound Mappings' to read R/3 tables.
  • Worked on ALE application link enable /iDOCs, RFC/BAPIs multiple stream custom mappings, data conversion and extraction using BDC programs, reporting, dialog programming and enhancements.
  • Worked on Database Stored Procedures, Functions, Triggers and Database Constraints

Environment: Informatica Power Center 8.1.1, SAP ECC 6.0, BAPI/ALE, ABAP/4, Microsoft SSIS, MSSQL Server 2000/2005, Oracle 10g, PL/SQL, SQL Loader, Java/J2EE, Eclipse, IBM MQ series, IBM AIX


Lead ETL Architect/Developer

Architect - Roles and Responsibilities:

  • Worked on ETL Architecture to furnish business requirement specs and document them for Informatica developments.
  • Understood the business requirements, standards and best practices. Created technical mapping specifications for design/build of Informatica mappings.
  • Experience using database tools such as TOAD, ERWIN and create any models versions for the future target tables and data types.

Development - Roles and Responsibilities:

  • Directed implementation of Informatica PowerExchange 8.x install including options SAP R/3 extraction, VSAM creation from Mainframes data map, Batch/real- time CDC, SQL Server CDC .
  • Expertise in data extraction and loading to and from the SAP R/3 Systems using iDOCS, LSMW, BAPI and ALE. Involved in creating iDOC Formats using iWay adapter from different SAP modules.
  • Capture changes to the master and transactional data in SAP R/3 using ALE/iDocs. PowerExchange for SAP NetWeaver can extract data from SAP RFC/BAPI in near real time and load into SAP R/3 systems.
  • Created Informatica batch jobs for loading iDocs, Flat Files and database objects to Sunoco ODS environment.
  • Created SSIS packages and configuration files to load data into SQL Server 2005. This includes complex T-SQL queries Common Table Expressions and Functions, profiles, configurations.
  • Worked on data management Tool Rochade administration and deployment.

Administrator - Roles and Responsibilities:

  • Helped in installation, upgrading and applying patches from Informatica 7.1.1 to 8.5.1.
  • Utilized Custom, Stored Procedure Aggregator, Filter, Router, and Expression, Connected and Unconnected lookup, Sorter, Normalizer, Joiner, Java and Update Strategy transformation.
  • Optimized heavy-load queries in DB2/AS400 and Oracle environment for using in Informatica mappings.
  • Tune the system run EXPLAIN Plan, SQL Trace and TKPROF against long running queries.
  • Expertise in different types of loading like Normal and Bulk loading challenges. Involved in Initial Loads, Incremental Loads, Daily loads and Monthly loads.
  • Used SQL Override to reduce the no. of transformations and improved the performance of the sessions.
  • Having command on troubleshooting with the help of error logs generated by Informatica server and testing of the ETL procedures using Informatica session log.
  • Worked Extensively on SQL Server 2000/2005 Target databases to find solutions and rectify them in views.
  • Bundle the process into a SSIS package that will load and build new a Year accounting for Sunoco Holidays into a Table and indicating the exact Business Day of the month.
  • Migrated new database objects for WebFocus team to create Masters for the reports.
  • Created set of views accordingly to the requirement and notified to the WebFocus team for creating their reports
  • Synchronized and maintained ODS, consolidated applications and gained a holistic view of all relevant data.
  • Well documented and designed the table load plans including aspects of surrogate keys, dimension tables, fact tables and the aggregate tables.

Environment: Informatica Power Center 8.5.1/7.1.1,PowerExchange 8.1,PowerConnect, Microsoft SSIS, MSSQL Server 2000/2005, ORACLE 10g, DB2 UDB 8.2, IBM Mainframes DB2/AS400 iSeries ,DBMS, Web Focus 7, PL/SQL, SAP R/3 4.6B, SQL Loader, PowerBuilder 11.5, IBM-AIX


Sr. Informatica Architect/Developer

Architect - Roles and Responsibilities:

  • Most of the Informatica Architecture got involved was to creating POC proof of Concept for upgrading Informatica PowerMart 6.x v to major changes in Informatica PowerCenter 7.1.1v architecture.
  • In Phases divided into groups - Gigantic task included overhaul of the 35 clients 40 repositories Informatica.
  • Involved in implementing new ETL transformations like lookups on flat files, union and java logic wherever necessary.
  • Developed and maintained industry wide best standard policy document and enforced them time-to- time.

Developer - Roles and Responsibilities:

  • Worked as Informatica Developer for small client business within NM bringing in the structured data into Oracle database.
  • Involved in development of BAPI programs to extract data from different modules of SAP.
  • Created IDOC Format for data extraction and loading into the data warehouse.
  • Created BDC Batch Data Communications based on both batch and session input with the help of SAP team to automatically load data into SAP R/3 system.
  • Uploaded vendor master data from legacy system to SAP R/3 using direct input method.
  • Used LSMW to migrate data from legacy to SAP.

Administrator - Roles and Responsibilities:

  • Created New Repositories and configured them to the UNIX environment also created script profiles to be invoked by the user instead of them directly connecting to the server engine.
  • Experienced processing daily requests for creating users permissions ,Object/Folder migration to Production Repositories, creating new DB connections Backup Restore Informatica repositories
  • Monitor load process during high usage and health check pmserver, pmrepserver, pmrepagent and other running objects every 15 minutes.
  • Audit the production repository by running the queries against the OPB tables and revoke the obsolete user access. This also gave flexibility to prepare timeline for present users to be checked out
  • Worked on different sources like SQL Server 2000, Flat files, Main Frames and loaded data into target Teradata Data warehouse and testing for Data Integrity and Consistency.
  • Extensively used Multi-load and Fast-load utility to populate the flat file data into the Teradata database.
  • Worked on parameterization of Source, Target lookup DB Connection values, during migration it was easy to set parameter for many lookup accessing same DB.
  • Worked on Database Triggers, Stored Procedures, Functions and Database Constraints.
  • Created and scheduled Worklets, Configured email notifications, set up Workflows to schedule the loads at required frequency.
  • Identified and fixed the Bottle Necks and tuned the Mappings and Sessions for improving performance.
  • Performance Tuning was key aspect to increase the productivity of each job and also decrease CPU utilization of different environments during peak hours.
  • Involved in Optimizing and Performance tuning logic on targets, sources, mappings, and sessions to increase the efficiency of session and Scheduled Workflows using AutoSys and/or Control M.
  • Installed and configured PowerExchange CDC for Oracle and mainframe sources like VSAM, IMS and COBOL files. Processed migrating Striva data Informatica later acquired and bundles PWX using PowerConnect.
  • Maintain UNIX scripts and profile in sync with the different environments.
  • Developed PL/SQL packages for building temporary extract tables based on complex selection criteria, business rules and calculations.
  • Worked closely with database team for handling any of the effects on Informatica from Oracle 10g RAC installation, patching, configuration and fine-tuning.
  • Created test CA AutoSys jobs to Schedule jobs in the production environments.

Environment: Informatica Power Center/PowerMart 7.1.1/6.1, PowerExchange 5.1, PowerConnect, Business Objects 6.5, Rapid SQL, PowerBuilder 11.5 ,Teradata V2R4/V2R5, UDB 8.0, AS/400, IBM Mainframes, Siebel, SQL Server 2000, ORACLE 10g , RAC, PL/SQL, SAP R/3 4.6B, SQL Loader, IBM-AIX


Informatica Developer

Roles and Responsibilities:

  • Technical Communications with the client which includes the understanding the requirement of the client in the architecture TrueComp 3.7 TrueComp5.1.2 system, environment setup and ETL process in the Development and Testing Environment.
  • Worked on TrueComp 3.7 transactions which must be converted into the new transaction format through pre-pipeline process to properly manage retroactivity.
  • Experience in Callidus True Comp software application and commissioning system.
  • Analyzed and documented the clients requirement process logic for Data Conversion
  • Updated TrueComp 3.7 system using Informatica/ETL Partial Conversion approach.
  • Customized several delivered ETL mappings per requirements.
  • Created Source and Target Definitions in the repository using Informatica Client tools.
  • Worked on different sources like SQL Server 2000, Flat files, Main Frames and loaded data into target oracle 9i Data warehouse.
  • Identified source systems, connectivity, tables, and fields, ensure data suitability for mapping.
  • Creation of Sessions and executing them to load the data from the source system using Informatica Server Manager.
  • Designed and developed complex join, look up transformation rules BR to generate consolidated data.
  • Error checking and testing of the ETL procedures and programs using Informatica session log.
  • Validated mappings and generated custom SQL override for some of the mappings
  • Worked on DB2 control center for populating and handling the TrueComp DB2 data using complex SQL Queries
  • Installed Business Objects 5.1 and created Users and assigned the privileges using Supervisor module of Business Objects.
  • Configured all Components of Business Objects, Configured and monitored the Broadcast Agent, Built Universes, retrieved data using Universes and Personal data files.
  • Created stored procedures and Freehand SQL methods and creating complex Adhoc Reports using the Business
  • Involved with onsite team for onsite-offshore coordination

Environment: Informatica Power Mart 6.1, Business Objects 5.1, Erwin 4.0/3.5, Oracle 9i, UDB 8.0,, Sybase 11.2, PowerBuilder, PL/SQL, SQL Loader ,TOAD, UNIX

Hire Now