- Around 8 years of experience in Informatica Data Analysis, Design and Development for various software Applications in client - server environment in providing Business Intelligence Solutions in Data Warehousing for Decision Support Systems.
- Extensive experience in Informatica Power center 9.x, 8.x, 7.x
- Strong Knowledge of Informatica Power Exchange while reading data in Real Time and Creation of Data mappings for reading the Source files into Informatica.
- Strong Data Warehousing fundamentals with expertise in Design and Development of ETL.
- Involving in the Architecture and Design of solutions delivered as part of various projects.
- Extensively worked on Power Center client (Repository, Designer, workflow manager and workflow Monitor) to extract, transform and load data.
- Designed mappings using Informatica Union, Lookup (Connected, Unconnected, Persistence, Dynamic and Static), Unstructured Data Object (B2B), Update strategy, expression, Transaction Control to create dynamic file names, Aggregator, Joiner, Expression, Router, XML, and Normalizer Transformations.
- Developed common logic using Reusable transformations and maplets.
- Developed and tested complex mappings for Slowly Changing Dimensions Type I, II (Flag and time stamp) and Change Data Capture based on requirements using Power Exchange 8x/9.01 HF3.
- Extensively used Informatica to load data into ODS, Data Warehouse on integration of various sources that involve relational databases-Oracle 11g/10g/9i, MS SQL Server 2008/2005/2000 , DB2 UDB 8.1, flat files, XLS and XML system.
- Strong skills in data analysis, data requirement analysis and data mapping for ETL processes.
- Hands on experience on Informatica Power Exchange for loading/retrieving data from mainframe systems.
- Good experience with Unix/Linux environments and writing UNIX shell scripts
- Good experience in performance tuning of large warehouse jobs (Database level fine tuning, Sql optimization, Workflow/session/mapping optimizations).
- Experience with Database migration and data cleansing.
- Experience with OLAP, ROLAP and other BI tools.
- Ability to prioritize and execute tasks in a high pressure environment.
- Experience in mentoring and providing knowledge transfer to team members, support teams and customers.
- Reliable, proactive, responsible, hardworking and good team player.
Data Warehousing ETL: Informatica Power Center 9.5/9.0/8.6/7. x/6.x and Informatica Server, Power Exchange 8.0,Power Mart4.7.2/5.1 andPower Connect.
Databases: Oracle 11g/10g/9i, MS SQL server 2000/2005/2008 , DB2 UDB 8.1, MS Access
Tools: TOAD, SQL *Plus, SQL* LOADER, Clear View, Excel, Word, Power Point
Operating System: MS-DOS, Windows 7/95/98/NT/2000/2003/XP/Vista/Server.
- Involve in designing the Data Migration Architecture - Source Extraction, Data Profiling, Target Mapping, Validation Reports, Conversion Reports and Data Management.
- Analysis of Data models of the Legacy System and Target System to understand the usage of both the data and provide conversion solutions.
- Understand the source VSAM files from the Legacy Mainframe Systems, And design ETL Framework to Validate and Load the Data.
- Understand the Data Mapping to the Target Data Model and develop INFORMATICA Mapping to Load the Next Gen Tables.
- Involved in functional testing of the Converted data thru Customer Portal Screen's and perform back tracking of data.
- Involved in Performance tuning of the INFORMATICA Mappings and PLSQL Programs for Handling 10+ million of records.
- Leveraged INFORMATICA's extensive ETL techniques to achieve the conversion of the most complex and exhaustive legacy System.
Environment: INFORMATICA 9.5, Oracle 11g, Rational Team Concert
Confidential, Billerica, MA
- Involved in Requirement Gathering, Design & Development, Testing and Documentation.
- Worked with Business Analyst and Architect to analyze data and document Functional aspects.
- Participated in scrum meetings to discuss the status of the everyday development.
- Designed and created Mappings, Sessions and Workflows using InformaticaPowerCenter Tools to load the data from Sources such as DB2/400, Oracle, XML, EDI and Flat Files into various targets.
- Developer the ETL processes to handle complex business logic for high volume data loads using various transformations such as Aggregator, Sorter, Filter, Normalizer, SQL Transformation, Lookup, Joiner, Router, Union etc.
- Processes to generate Daily, Weekly and Monthly data extracts were developed and the data files were sent across to the downstream applications.
- Used Source Qualifier & Lookup SQL overrides, Persistent caches, Incremental Aggregation etc for better performance.
- Defined and used Mapping Parameters and Parameter files to provide values dynamically at session run time.
- Defined and used Mapping Variables to capture changed data from the source tables.
- Created sessions to use pre & post session SQL commands to drop & create indexes on tables for better performance.
- Used Event wait Task, Control Task, Email Tasks, Command Tasks and Link conditions in workflows.
- Write PL/SQL procedures which are called from Stored Procedure transformation to perform database actions such as truncate the target before load, delete records based on a condition, rename the tables etc.
- Used Oracle SQL Loader utility to Bulk Load data from flat files into database tables.
- Created & modified Unix Shell scripts.
- Provided data loads for QA testing and worked on resolving the defects.
- Performed Informaticacode migrations.
- Created Technical Design documents, Test Cases documents and Application work books.
- Worked closely with Manager and Database Administrator
- Analyzed functional specs and created technical documentation.
- Extensively used all the features ofInformatica9.1 includingDesigner, Workflow manager and Repository Manager, Workflow monitor.
Environment: Informatica Power Center 9.5.1/9.0.1 , SQL Server 2008 express, Oracle SQL Developer, Flat files, XML Files, MS Visio, Toad 7.0, Windows 2000, Power Exchange 9.0.1., 9.5.1
Confidential, Fountain Valley, CA
- Responsible for developing, support and maintenance for theETL(Extract, Transform and Load) processesusingInformatica power center.
- Designed and developed severalETL scriptsusingInformatica, UNIX shell scripts
- Analyzed the source data coming fromOracle, Flat Files, and MS Excel coordinated with data warehouse team in developing Dimensional Model.
- Created ftp connections, database connections(ODBC)for the sources and targets.
- Implemented various workflows using transformations such asJAVA transformation, XML transformation, Normalizer, look up, aggregator, stored procedure and scheduled jobsfor different sessions.
- ImplementedSlowly Changing dimension methodologyfor accessing thefull history of accountsand transaction information.
- Scheduled Sessions and Batches on theInformaticaServer usingInformaticaWorkflow Manager.
- Develops and unit tests efficient ETL scripts
- Experience with writing and executingtest casesfor data transformations in Informatica
- Createdmedium to complex PL/SQL stored proceduresfor integration with Informatica using Oracle 10g.
- Upgrading and maintenance for data warehouse tools
- Co-ordinate with the off-shore teams and mentored junior developers.
- Analyzed performancetuningof Oracle 10g to improve its performance.
- Developed Shell Scripts to automate file manipulation and data loading procedures
- Proficient with Visual Source Safe for version control for Informatica objects, Oracle views and packages andtest scripts.
- Developed UNIX shell scripts to control the process flow for Informatica workflows to handle high volume data.
- Prepared Test cases based on Functional Requirements Document.
- Developed Procedures and Functions in PL/SQL for Data ETL.
- Involved in Unit, System, Performance, Concurrent and QA testing.
- Worked with complexCognosreports inReport Studiousingmaster-detail relationship, drill through, drill upanddrill down, burst options, andPROMPTSand also withLOV, aggregate aware.
- Createddashboardsfor a managerial overview withdrill up and drill downcapabilities.
Environment: InformaticaPowerCenter 9.1,Oracle 10g/11g, SQL developer, Flatfiles, TOAD,PL/SQL, MS SQL Server, Crystal Reports, Cognos, UNIX.
Confidential, Minneapolis, MN
- Participated in the Design Team and user requirement gathering meetings.
- Performed business analysis and requirements per high end users and managers and performed requirement gathering.
- Extensively used Informatica Designer to create and manipulate source and target definitions, mappings, mapplets, transformations, re-usable transformations.
- Created different source definitions to extract data from flat files and relational tables for Informatica Power Centre.
- Used Star Schema approach for designing of database for the data warehouse
- Developed a standard ETL framework to enable the reusability of similar logic across the board.
- Created different target definitions using warehouse designer of Informatica Power Center.
- Used mapping designer to generate different mappings for different loads.
- Created different transformations such as Joiner Transformations, Look-up Transformations, Rank Transformations, Expressions, Aggregators and Sequence Generator.
- Created stored procedures, packages, triggers, tables, views, synonyms, and test data in Oracle.
- Extracted source data from Oracle, Flat files, XML files using Informatica, and loaded into target Database.
- Created medium to complex PL/SQL stored procedure for Integration with Informatica using Oracle 10g.
- Developed complex mapping also involving SCD Type-I, Type-II,Type-III mapping in Informatica to load the data from various sources
- Extensively transformed the existing PL/SQL scripts into stored procedures to be used by Informatica Mappings with the help of Stored Procedure Transformations.
- Involved in extensive Performance Tuning by determining bottlenecks in sources, mappings and sessions.
- Created Models based on the dimensions, levels and measures required for the analysis.
- Validate the data in warehouse and data marts after loading process balancing with source data.
- Worked closely with the business analyst’s team in order to solve the Problem Tickets, Service Requests. Helped the 24/7 Production Support team
Environment: Informatica Power Center 9.1, Oracle 11g/10g, MS SQL Server, TWS, UNIX
Confidential, Watertown, MA
- Involved in identifying dimensions and facts to create enterprise data warehouse and model. Worked in a team for supporting and introducing Informatica into SDLC project development environment at Tufts.
- Converted the already existing PL/SQL extracts and procedures to Informatica. Built new logic and designs in mapping to help work the existing PL/SQL logic and rebuilt the entire process model in Informatica.
- Developed complex procedures, functions, packages, triggers, snapshots.
- Created medium to complex PL/SQLstored proceduresforintegrationwith Informatica using Oracle 10g.
- Used procedures and triggers to catch and handle exceptions at database level.
- Extracted and loaded data from flat files and Oracle sources to Oracle database usingInformaticamappings.
- Created mappings using varioustransformationslike update strategy, lookup, stored procedure, router, filter,JAVA, SQL, sequence generator, joiner, aggregate, and expression transformations in extracting data in compliance with the business logic developed.
- Developed complex mappings also involvingSCD Type-I, Type-II and Type IIImappings in Informatica to load the data from various sources
- Data extraction mappings were developed inInformaticabetween source systems toODSto Dimension data source.
- Worked in extensively testing and performance tuning of the mapping and logic. Created and worked on the unit test cases using Toad.
- Performance tuning of Oracle queries usingSQL trace, SQL plan, Quest SQL optimizer, Oraclepartitioning, various indexes and join types.
- Tuned the mappings to perform better using different logics and checking the Cache to provide maximum efficiency and performance.
- Helped and guided other team members at THP through the entire development process.
Environment: Informatica Powercenter 8.1.2, Oracle 10g, PL/SQL, TOAD, WINCVS, UNIX, Linux Box, Tidal Scheduling tool
Confidential, Los Angeles, CA
- Involved with requirement gathering and analysis for the data marts focusing on data analysis, data quality, data mapping between ODS, staging tables and data warehouses/data marts.
- Designed and developed processes to support data quality issues and detection and resolutions of error conditions.
- Working with the Business Analysts andthe QA team for validation and verification of the development.
- Analyzed the session logs, bad files and error tables for troubleshooting mappings and sessions.
- Implemented various scenarios related to slowly growing targets and slowly changing dimensions(Type1, Type2, Type3)
- Implemented various business rules of data transformations using various Informatica transformations like Normalizer, Source Qualifier,Update Strategy, Look up(connected/unconnected/static cached/dynamic cached), Sequence Generator, expression, Aggregator, XML(source and generator), Stored Procedures.
- Worked with newer Informatica transformations like Java transformation, Transaction Control.
- Provided administrative functions like creating repositories, backing up repositories, setting up users, assigning permissions and setting up folders in Repository manager.
- Worked on Oracle Warehouse Builder for already existing workflows to get the final source files for use.
- Wrote shell script utilities to detect error conditions in production loads and take corrective actions, wrote scripts to backup/restore repositories, backup/restore log files.
- Heavily involved with performance tuning of Oracle database - using TKProf utility, working with partitioned tables, implementing layer of materialized views to speed up lookup queries, working with Bitmap indexes for dimension tables, DBMS Stats package to update statistics, using SQL hints.
Environment: InformaticaPower Center 8.1.2, Autosys, Oracle 9i, MS PowerPoint, MS Visio, TOAD, PL/SQL, UNIX,SQL Loader*,SQL server 2005.