- 12 Years of strong Data Warehousing experience in analysis, design, development and implementing software solutions in various business applications with ETL/Informatica Power Center 10.1/9.6/9.5.1/ 9.0.1/8. x/7.x.
- 2.5 Years of real - time data Warehouse development experience using CDC tool Informatica Power exchange 9.0/8.6
- Extensively worked with various components of the Informatica Power Center - Power Center Designer(Source Analyzer, Warehouse Designer, Mapping Designer, Mapplet Designer, Transformation developer), Repository Manager, Workflow Manager, and Workflow Monitor to create mappings for the extraction of data from various source systems.
- Experienced in using IDQ tool for profiling, applying rules and develop mappings to move data from source to target systems
- Experience in implementing complex business rules by creating re-usable transformations, and robust mappings/mapplets using different transformations like Unconnected and Connected lookups, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Update Strategy, Stored Procedure, Normalizer etc.
- Proficient in full life cycle development of Data Warehousing, ETL strategies, reporting and hands on experience in Performance Tuning of sources, targets, transformations, mappings and sessions using Database Tuning, Partitioning, Index Usage, Aggregate Tables, Session partitioning, Load strategies, commit intervals and transformation tuning
- Extensive experience in integrating data from various Heterogeneous sources like Relational database (Oracle, SQL Server, Teradata, DB2), Flat Files (Fixed Width and Delimited), COBOL files, XML Files and Excel files and Salesforce, Active Directory, SAP into Data Warehouse and Data Mart.
- Experience in Database Design, Entity-Relationship modeling, Dimensional modeling like Star schema and Snowflake schema, Fact and Dimension tables.
- Worked with Slowly Changing Dimensions type 1 and type 2.
- Experience in Oracle PL/SQL Programming (Stored procedures, Triggers, Functions, Packages) and UNIX shell scripting to perform job scheduling.
- Good experience in documenting the ETL process flow for better maintenance and analyzing the process flow
- Demonstrated ability to take initiatives, grasp and expand on ideas, tackle and follow through difficult assignments in the fast-pace and changing environment.
- Good experience on scheduling tool Automic/Uc4 to create jobs, FTP jobs, job plans, variables, events, call objects and scheduling the jobs
- Independent yet team oriented with excellent analytical, problem solving, multi-tasking, oral and written communication skills.
ETL TECHNOLOGY: Informatica Power Center 10.1/9.6/9.5.1/ 9.0.1/8. 6/8.1/7.1/6.1 IDQ 9.6
DATA WAREHOUSE: Multidimensional Model Design, Star Schema Development
DATA MODELLING: Star-Schema Modeling, Snowflake Modeling
DATABASES: Oracle 11g/10g/9i, MS SQL Server7.0/2000, MS Access
PROGRAMMING: SQL, PL/SQL,UNIX Scripting
OPERATING SYSTEMS: Windows, MAC, UNIX, MS-DOS
APPLICATIONS: MS Office, MS Project, FrontPage, Toad 9.2/8.6
MANAGEMENT TOOLS: MS-Office, MS Project
Confidential, Houston, TX
Senior ETL/Informatica Developer
- Contributed as a member of the Operational Data Store (ODS) responsible for design, development and implementation of interfaces and conversions for different kind of data (HR, Accounting, Procurement) for Engineering Procurement and Construction (EPC) company
- Provided technical leadership to the Data Integration team
- Worked on various projects that included but not limited to:
- SAP - Oracle EBS migration that included creating new data integration interfaces and reworking on the existing datawarehouse jobs
- Architecture of DataMart for various subject areas - HR, Procurement, Global User Billing etc.
- KBR-Wyle Integration
- Worked with Business Intelligence team for data conversions and building the DataMart as per the requirements
- Worked on designing the technical documents
- Responsible for Codes Review, unit testing and process Documentation Review
- Worked on multiple projects using Informatica developer tool IDQ of latest versions 9.6
- Involved in migration of the mapps from IDQ to power center
- Applied the rules and profiled the source and target table's data using IDQ
- Implemented Slowly Changing Dimensions - Type I & II in different mappings as per the requirements
- Data Cleansing, Masking, Validation in the Landing/Staging Area
- Part of Production Support Team and Monitored systems and processes to ensure seamless loading of data in time
- Analyzed and communicated ETL development issues, including problems with data integrity, data design, and functional and technical issues
- Performed data quality analysis, gathered information to determine data sources, data targets, data definitions, data relationships, and documented business rules
- Extracting and transforming data from various relational databases, xml or flat files to the data warehouse
- Analyzed Business and Technical requirements and designed enhanced ETL Processes to meet that requirement
- Hands-on Experience in working with Source Analyzer, Warehouse Designer, Mapping Designer, Mapplets to extract, transform and load data
- Created Mapping Parameters, Session parameters, Mapping Variables and Session Variables
- Involved in extensive performance tuning by determining bottlenecks at various points like targets, sources, mappings, sessions or system which led to better session performance
- Used Informatica Power Center 9.6 for migrating data from various OLTP servers/databases to the data mart
- Created stored procedures for generating the data warehouse cycle dates and to drop, re-create the indexes
- Worked with various Active and Passive transformations like Source Qualifier, Sorter, Aggregator, Filter, Union, and Router Transformations, Sequence Generator and Update Strategy Transformations
- Handled versioning and dependencies in Informatica
- Developed Test Cases according to Business and Technical Requirements and prepared SQL scripts to test data for both Unit testing and UAT testing
- Developed schedules to automate the update processes and Informatica sessions and batches
- Resolving technical and design issues
- Discussed with customers and technical support on Data Migration and Data warehousing
- Developed standards and procedures to support quality development and testing of data warehouse processes
- Deployment of ETL and databases codes across environments
- Led the team of engineers that developed, maintained and supported the Operational Data Store and the Enterprise Data warehouse that included over 3000 nightly jobs and supported over 60 Business Applications
- Interviewed and selected the engineers for the team and mentored them in areas of expertise, such as solution and information architecture, data modeling, data governance
- In Automic/UC4 scheduler created informatica jobs using pmcmd command, FTP jobs, job plans, variables, events, call objects and scheduled the jobs, created loop jobs to loop the informatica workflows and process the flat files in loop
Environment: Informatica Power Center 9.6, SQL Server 2005/2000, Oracle 11g, Automic/UC4 scheduler, Start, Ticket Plus, Service Now/Dash
Confidential, Charlotte, NC
- Contributed as a member of the Data Management Group (DMG) responsible for design, development, and implementation of interfaces and conversions (Member, Member Eligibility, Claims, Pharmacy, Providers, and Prior Authorization) for Medicaid Management Information System clients.
- Developed and Tested Informatica mappings to load data into various dimensions and fact tables from various source systems.
- Analyzed business requirements and worked closely with the various application teams and business teams to develop ETL procedures that are consistent across all applications and systems.
- Implement slowly changing dimensions (SCD) - type I & II in different mappings as per the requirements and performed target load order in multiple target instances.
- Responsible for testing different versions of informatica and resolving the issues identified in the new version with the Informatica Support Team.
- Responsible for deploying the code from Dev to Prod. Responsible for creating folders and giving privileges to the new users.
- Formatted the data in a specific format and loaded it to the target flat files.
- Wrote Windows batch scripts to automate the batches.
- Performed ETL (extract, transform, and load) duties to include requirements validation, code development, source control, unit testing, and version deployment scripting.
- Developed program specifications/detail design documents.
- Codes, tests and debugs applications programs. Has participated in the application design of systems, including use of analytical techniques
- Created Informatica mappings with PL/SQL Procedures/Functions to build business rules to load data.
- Performance turning was done on various Informatica mappings to reduce the execution time.
- Production support was done on a weekly routine basis.
- Analysed and tested operational problems to determine presence and scope of potential code defects.
- After the loading was complete balancing scripts were developed in order to make sure that the number of rows read from the source was in agreement with the number of rows loaded in the target. This validation was done to check for the total counts and also for the data in all the fields.
- Worked extensively on various reusable transformations, mapplets and transformations such as lookup, joiner, expression, sorter, router, update strategy, aggregate, union etc.
- Extensively used FTP Pro to transfer files to and from FTP sites.
Environment: Informatica Power Center 10.1,Server Grid/HA, SQL Server 2005/2000, Oracle 11g, PL/SQL, Unix Shell Scripting, Windows 2007
Confidential, Jersey City/Parsippany, NJ
Sr Informatica Developer
- Worked with business analyst for requirement gathering, business analysis, and testing and project- coordination.
- Created the Detail Design Documents which have the ETL technical specifications for the given functionality, overall process flow for each particular process, Flow diagrams, Mapping spreadsheets, issues, assumptions, configurations, Informatica code details, shell scripts etc. and conducted meetings with the clients for the Approval of the process.
- Analyzed the mapping logic to determine the reusability of the code.
- Handled versioning and dependencies in Informatica.
- Developed complex Informatica mappings using various transformations- Source Qualifier, Normalizer, Filter, Connected Lookup, Unconnected Lookup, Update strategy, Router, Aggregator, Sequence Generator, Reusable sequence generator transformation.
- Extensively used SCD’s (Slowly Changing Dimension) to handle the Incremental Loading for Dimension tables, Fact tables.
- Configured Informatica Power Exchange connection and navigator.
- Created Registration, DataMap, configured Real-Time mapping and workflows for real-time data processing using CDC option of Informatica PowerExchange.
- Wrote Script to load multiple tables and used MLOAD utility.
- Used Debugger utility of the Designer tool to check the errors in the mapping and made appropriate changes in the mappings to generate the required results.
- Performing ETL & database code migrations across environments.
- Created Mapping Parameters, Session parameters, Mapping Variables and Session Variables.
- Involved in extensive performance tuning by determining bottlenecks at various points like targets, sources, mappings, sessions or system. This led to better session performance.
- Created and maintained the Shell Scripts and Parameter files in UNIX for the proper execution of Informatica workflows in different environments.
- Created Unit test plans and did unit testing using different scenarios separately for every process. Involved in System test, Regression test & supported the UAT for the client.
Environment: Informatica Power Center 9.0, Informatica Power Exchange 9.0, Toad, UNIX Shell Scripting, SQL Server 2008, Mercury Quality Center, Autosys, Linux.
- Analyzed business requirements and worked closely with the various application teams and business teams to develop ETL procedures that are consistent across all applications and systems
- Designed, Developed and Tested Informatica mappings for populating the data into the dimension, facts and history tables constantly from different source systems
- Recommended, designed and implemented strategies for managing the data in data warehouse
- Acted as a liaison with internal and external customers to research, analyze and propose solutions to technical, operational and test scenarios
- Analyzed requirements and specifications for complex processing to verify that current and future business requirements are met and obtained relevant test data. Designed, coded and reviewed accurate and thorough test material (Test Plan and Test Cases) with pre-determined results within specified scheduled dates
- Created copies for the non-production environments by masking the data to make sure test and development processes are not exposing sensitive information
- Performance tuning was done for various facts and dimension mappings to reduce the ETL load time
- Responsible for registering the AS400 tables for changed data capture using power exchange and creating CDC maps
- Involved in developing ETL mappings to load data from Flat files into the target Oracle / SQL Server database by applying business logic on transformation mapping for inserting and updating records when loaded
- Worked closely with executive sponsors and user decision makers to develop the transformation logic to be used in Informatica
- Designed the source to target mappings that contain the business rules for the server during the extract, transform and load process using Informatica ETL Tool
- Created reusable transformations and used them in different mappings
- Formatted the data in a specific format and loaded it to the target flat files
- Wrote Windows batch scripts to automate the batches
- Developed ETL Scripts for processing and transferring of raw data from the legacy systems into the warehouse every week
- Created post-session and pre-session scripts and mail-notifications
- Creating/Maintaining/Modifying Informatica ETL mappings that transfers data from AS400 to the warehouse
- Performed tuning of SQL queries for faster extraction of data
- Upgraded various DTS packages into SSIS
- Worked on Teradata utilities BTEQ, MLOAD and TPUMP and tuned SQL
- Created various SSIS packages and responsible for integrating child package into the framework and creating configurations to move data from Dev to Test and then to Prod
Environment: Informatica Power Center 9.1,8.6, Informatica Power Exchange 9.1,8.6, PL/SQL, Oracle 11g, SQL Server 2005, DB2 Mainframe, TIDAL, Unix Shell Scripting, Teradata 13, AS400,Windows 2003