- Eight years of IT experience with special emphasis on design, development and testing of Database/Data Warehousing applications using Informatica Power Center in various domains like Pharmaceutical, Financial, Health Insurance and Aerospace.
- Strong experience in Informatica PowerCenter 9.5/8.6/7.1, PowerConnect for ETL (Extraction Transformation and Loading) of data from multiple source database systems in both UNIX/Windows environment.
- Practical understanding and experience with Data Mapping, Data Validation and Data Modeling (Dimensional & Relational) concepts such as Star - Schema Modeling, Snowflake Schema Modeling, Fact and Dimension tables. Expertise in normalizing data.
- Solid experience in Informatica Power Center (versions 9.5/8.6/ 7.1) including Power Center Designer, Workflow Manager, Workflow Monitor, Repository Manager, Repository Server Admin console and Teradata.
- Worked with Informatica Data Quality (IDQ) toolkit, Analysis, data cleansing, data matching, data conversion, exception handling, and reporting and monitoring capabilities of IDQ.
- Expertise in Oracle 11g/10g/9i/8i, PL/SQL, Functions, Procedures, Packages, Triggers and Materialized Views.
- Expertise in Master Data Management concepts, Methodologies and ability to apply this knowledge in building MDM solutions.
- Experience in installation and configuration of core Informatica MDM Hub components such as Hub Console, Hub Store, Hub Server, Cleanse Match Server and Cleanse Adapter in Windows.
- Knowledge on implementing hierarchies, relationships types, packages and profiles for hierarchy management in MDM Hub implementation.
- Excellent knowledge in Creating and Maintaining Database objects like Tables, Indexes, Views, Synonyms, Stored Procedures and Packages.
- Expert in writing complex SQL queries.
- Experienced in RDBMS design, data modeling, data normalization and SQL tuning using Indexes and working with UTL Files and Dynamic SQL.
- Developed Complex database objects like Stored Procedures, Functions, Packages and Triggers using SQL and PL/SQL.
- Extensively worked on UNIX shell scripting for creating and maintaining the parameter files, executing database procedures, calling Informatica workflows, and job scheduling.
- Expertise in various phases of a SDLC such as requirement gathering, design, generating program specifications, developing applications, peer reviews, implementation and post implementation support and agile methodology.
- Effectively communicate with business users, Business Analysts, project manager and team members.
- Strong ability to work within a demanding and aggressive project schedules and environments.
- Excellent analytical, problem solving skills and a motivated team player with excellent inter-personal skills.
Programming Languages/Scripting: C, C++, PL/SQL, Java, Unix Korn Shell BI/OLAP Tools: Cognos 8.x, 10 (Report Studio, Query Studio, Analysis Studio), Mircostrategy
Database Tools: Toad, SQL Developer, Embarcadero DBArtisan, QMF
ETL Tools: Informatica Power Center 9.5/8.6/7.1/6.1, SQL Loader
Databases: Oracle 11g/10g/9i, SQL Server 2005/2000, IBM DB2 and Teradata
Operating Systems: Unix, Windows 95/98/NT/2000/2003/XP/7/8, MS-DOS
GUI: Developer 2000(D2K-Forms 6i/9i, Reports 6i/9i), Oracle Workflow Builder and Oracle Discoverer.
BusinessObjects Modules: InfoView, Webi/Web Intelligence/Thin Client, Deski/Desktop Intelligence/Full Client, Webi Rich client, Designer, Import Wizard, CMC, Xcelsius, Crystal Reports, Informatica Power Center, Data Integrator
BusinessObjects Functionalities: Reporting Section, Break, Drill, Slice & Dice, Grouping, Alerts, Ranking, Advanced queries (Union, Minus, Intersection and Sub query)
Designer: Aggregate Awareness, Context, Security, Alias, Derived tables
Data Modelling: ER Studio, Microsoft Visio
Web Development: Microsoft .Net, Javascri
Informatica, pl/sql developer
- Interacted with the Business users to identify the process metrics and various key dimensions and measures.
- Worked with heterogeneous source to Extracted data from Oracle database, XML, JASON and flat files and loaded to a relational Oracle warehouse.
- Developed standard and reusable mappings and mapplets using various transformations like Expression, Aggregator, Joiner, Router, Lookup (Connected and Unconnected), Filter Update Strategy, Java Transformations and Re-usable transformations .
- Migrated day one data for existing members in PCMH program from EDW into OLTP system using ETL process.
- Performed tuning of SQL queries and Stored Procedure for speedy extraction of data to resolve and troubleshoot issues in OLTP environment.
- Troubleshooting of long running sessions and fixing the issues related to it.
- Worked with Variables and Parameters in the mappings to pass the values between sessions.
- Involved in the development of PL/SQL stored procedures, functions and packages to process business data in OLTP system.
- Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTO-TRACE
- Extensively worked on Informatica IDE/IDQ.
- Involved in massive data profiling using IDQ (Analyst Tool) prior to data staging.
- Carried out changes into Architecture and Design of Oracle Schemas for both OLAP and OLTP systems.
- Worked with Services and Portal teams on various occasion for data issues in OLTP system.
- Worked with the testing team to resolve bugs related to day one ETL mappings before production.
- Creating the weekly project status reports, tracking the progress of tasks per schedule and reporting any risks and contingency plan to management and business users.
- Design reference data and data quality rules using IDQ and involved in cleaning the data using IDQ in Informatica Data Quality 9.1 environment
- Planned, created and executed SSIS packages to integrate data from varied sources like Oracle, DB2, flat files and SQL databases and loaded into landing tables of Informatica MDM Hub.
- Performed land process to load data into landing tables of MDM Hub using external batch processing for initial data load in hub store.
- Involved in meetings with production team for issues related to Deployment, maintenance, future enhancements, backup and crisis management of DW.
- Worked with production team to resolve data issues in Production database of OLAP and OLTP systems.
- Used python coding to pull data from web and do address comparison for member details before loading to data ware house.
- Designed Informatica Mappings and Workflows to be re-startable after a failure.
- Designed tables required for the execution of the ETL processes using ERwin.
- Extracted data stored in a multi-level hierarchy using Oracle Stored Procedures and Connect-by-prior.
- Loaded Customer data in multiple levels (rows) using Oracle Stored Procedures and Cursors.
- Built a POC on rounding transaction amounts to 6 decimal places in Informatica.
- Used XML Parsers and Generators to handle inbound and outbound XMLs in Informatica.
- Loaded Dimension, Fact and Exception tables and automated email generation when exceptions occurred.
- Provided excellent support during QA/UAT/Beta testing by working with multiple groups.
- Optimized the SQLs and Informatica mappings which handled millions of records.
- Improved performance using Oracle Partitions, Indexes and other Performance Tuning techniques.
- Developed re-usable components in Informatica, Oracle and UNIX.
- On-Call/Production Support provided during day-time and off-hours.
- Working knowledge of JAVA, PYTHON, and AWS.
- Created Custom Hierarchies to meet the Business requirement in Tableau.
- Created Workbooks and dashboards for analyzing statistical billing data using Tableau 8.0.
- Created various connections, Data foundations and Business layers.
- Actively participated in Install/Deployment plan meetings.
- Proved accuracy in doing sanity checks, smoke tests after the install of new scripts used by ETL.
- Prepared Data Mapping documents, process documents, Re-startability Matrix to help on-call team reduce the turnaround time when there is an issue.
- Efficiently handled multiple projects during resource crunch.
- Provided Training to the team with the functional and technical knowledge.
Environment: Informatica Power Center 9.6, Workflow Manager, Workflow Monitor, Informatica Power Connect / Power Exchange, Informatica IDQ, Informatica MDM, Data Analyzer 8.1, Oracle 12g, PL/SQL, JAVA, Erwin, Visual Studio 2010, OWB,Tableau.
- Involved in gathering requirements from business users. Participated in the detailed requirement analysis for the design of data marts.
- Actively interface with other teams to gather requirements, design, code, debug, document, implement and maintain various DB projects.
- Worked per the specifications and restrictions of the company by strictly maintaining data privacy.
- Worked on Informatica Power Center tools- Designer, Repository Manager, Workflow Manager, and Workflow Monitor.
- 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.
- Worked with Informatica Data Quality (IDQ 8.6.1) for data quality measurement.
- Involved in building the ETL Source to Target mapping to load data into Data warehouse.
- Created mapping documents to outline data flow from sources to targets.
- Extracted the data from the flat files and other RDBMS databases into staging area and populated onto Data warehouse.
- Maintained stored definitions, transformation rules and targets definitions using Informatica repository Manager.
- Used various transformations like Filter, Expression, Sequence Generator, Update Strategy, Joiner, Stored Procedure, and Union to develop robust mappings in the Informatica Designer.
- Involved in implementing the Land Process of loading the customer/product Data Set into Informatica MDM from various source systems.
- Worked on data cleansing and standardization using the cleanse functions in Informatica MDM.
- Used Hierarchies tool for configuring entity base objects, entity types, relationship base objects, relationship types, profiles, put and display packages and used the entity types as subject areas in IDD.
- Defined the Trust and Validation rules and setting up the match/merge rule sets to get the right master records.
- Created mapplets to use them in different mappings.
- Developed mappings to load into staging tables and then to Dimensions and Facts using existing ETL standards.
- Worked on different tasks in Workflows like sessions, events raise, event wait, decision, e-mail, command, worklets, Assignment, Timer and scheduling of the workflow.
- Created sessions, configured workflows to extract data from various sources, transformed data, and loading into data warehouse.
- Used Type 1 SCD and Type 2 SCD mappings to update slowly Changing Dimension Tables.
- Extensively used SQL* loader to load data from flat files to the database tables in Oracle.
- Developed and modified triggers, packages, functions and stored procedures for data conversions and PL/SQL procedures to create database objects dynamically based on user inputs.
- Modified existing mappings for enhancements of new business requirements.
- Used Debugger to test the mappings and fixed the bugs.
- Involved in Performance tuning at source, target, mappings, sessions, and system levels.
- Prepared migration document to move the mappings from development to testing and then to production repositories
Environment: Informatica Power Center 9.6, Informatica IDQ, Informatica MDM, Workflow Manager, Workflow Monitor, Informatica Power Connect / Power Exchange, Data Analyzer 8.1, Oracle 12g, PL/SQL, Erwin, Visual Studio 2010, OWB.
- Extensively involved in requirements gathering, writing ETL Specs and preparing design document.
- Designed and developed ETL mappings for data sharing between interfaces utilizing SCD type 2 and CDC methodologies.
- Worked on parameters, variables, procedures, scheduling and pre/post session shell scripts.
- Developed code to extract, transform, and load (ETL) data from inbound flat files and various databases into outbound flat files and XML files using complex business logic.
- Expertise in performance tuning of the mappings, ETL Environments, procedures, queries and processes.
- Expertise in handling and loading high volumes of data into data warehouse in each load window.
- Expertise in preparing test strategy, test plan, test summary reports, test cases and test scripts for automated and manual testing based on user requirement documents and system requirement documents.
- Extensive experience in Data warehouses with strong understanding of Logical, Physical, and Dimensional Data Modeling to Design Star and Snow Flake Schema's.
- Utilized of Informatica IDQ 8.6.1 to complete initial data profiling and matching/removing duplicate data.
- Used SCD (type1, type2 and type3) to Load present and Historical Data to ODS, EDW.
- Involved in creating, monitoring, modifying, & communicating the project plan with other team members.
- Experience in ETL process using ETL Tools, creating and maintaining Repository, Source systems, Target Databases and developing strategies for ETL mechanisms using ETL tools like Pentaho.
- Gathering and analysis, design, development, testing, performance tuning, and production support.
- Experience in Unix Shell scripting, scheduling cron jobs and also Job Scheduling on multiple platforms like Windows NT/2000/2003, Unix.
- Strong technical skills in Oracle, SQL, PL/SQL, MySQL.
- Worked with architects and DBA in designing the rules engine and implementing the logical, physical and Dimensional model based on the rules and metadata.
- Responsible for preparing the technical specifications from the business requirements.
- Analyze the requirement, work out with the solution. Develop and maintain the detailed project documentation.
- Primarily responsible to convert business requirements into system design.
Environment: Pentaho 5.0, Mysql 5.5, Informatica 9.5, OWB,Oracle 11g/10g, PL/SQL, Autosys,,SQLyog, OWB 10.2 TFS, OBIEE 220.127.116.11.7, Tableau v7/8, Visual Studio 2010, .
Confidential, Phoenix, AZ
Sr. Informatica Developer
- Involved in gathering requirements from business users. Participated in the detailed requirement analysis for the design of data marts and star schemas.
- Created Technical design specification documents based on the functional design documents and the physical data model.
- Extracted data from several source systems like Oracle, DB2, Flat files, XML files, etc. and loaded data into Enterprise Data ware house. Designed many Multi Source Single Target mappings and vice versa. .
- Worked on Power Center Designer client tools like Source Analyzer, Target Analyzer, Mapping Designer and Mapplet Designer.
- Created Mapplets with the help of Mapplet Designer.
- Created reusable transformations by using Lookup, Aggregator, Normalizer, Update strategy,Expression, Joiner, Rank, Router, Filter, and Sequence Generator etc. in the Transformation Developer.
- Moved the data from source systems to different schemas based on the dimensions and fact tables by using the slowly changing dimensions’ type two and type one.
- Raised change requests, incident Management, analyzed and coordinated resolution of program flaws for the Development environment and hot fixed them in the QA during the runs.
- Informatica workflow manager was used to create, schedule, execute Sessions, Worklets,Command, E-Mail Tasks and Workflows. Performed validation and loading of the Flat files received from business users.
- Installed, upgraded and configured a multi-node Informatica environment and production Power Center systems covering 8.x and 9.x versions to provide the user community with the latest features.
- Wrote several oracle stored procedures, functions and packages for build and deployment process.
- Wrote UNIX Shell scripts to schedule the workflows and to extract the data from the various databases and file systems.
- Managed various data marts by collaborating with different organizations including IT teams and DBAs to build a quality system and adhering to standard operating procedures resulted in a highly available 24x7 system to business and product development consumers worldwide.
- Used Parameter files to reuse the mapping with different criteria to decrease the maintenance.
- Extensively used Informatica Debugger for testing the mapping logic during Unit Testing.
- Used Autosys to schedule Informatica, SQL script and shell script jobs.
- Optimized data marts to increase load frequency and performance in some cases by 15-20% improvements to meet service level agreements.
- Identifying the issues and resolutions by coordinating with the QA team and acted as a point of contact between the Dev and the QA team.
- Trained and provided knowledge transfer to colleagues and technical consultants who developed applications and generated more than 300 reports and metrics utilizing the Engineering Data Warehouse environment.
Environment: Informatica Power Center 9.5/8.6, Oracle 11g, DB2, PL/SQL, Microsoft SQL Server, UNIX Shell Scripts, Autosys, Informatica Power Exchange, Business Objects, Erwin, Windows 7, Unix.
Confidential, Maryland, MD
Sr. ETL Developer
- Involved in Full life cycle of Business Objects reporting Application from requirements gathering to deployment of the reporting system to the production environment.
- Designed and developed Reports and Universes based on the specifications and assigned them to respective domains.
- Created complex reports using Web Intelligence functionalities like Drill Filters, @Functions, Cross Tabs, Master/Detail, and Formulas.
- Developed Transformation logic and designed various complex Mappings in the Designer for data load and data cleansing.
- Responsible for using the capabilities of PowerCenter namely list files, pmcmd, pmrep, Target override, Persistent lookup, Dynamic & Static lookup.
- Develop Logical and Physical data models that capture current state/future state data elements and data flows using Erwin.
- Created and Monitored Workflows using Workflow Manager and Workflow Monitor.
- Created several Procedures, Functions, Triggers and Packages to implement the functionality in PL/SQL.
- Developed Informatica Mappings, Re-usable Transformations, and Mapplets for data load to data warehouse and database (oracle).
- Support between developers and end users, functional managers and Oracle regarding issues with database software, SQL programming, tuning, releases and development with Oracle tools like Oracle Warehouse Builder.
- Involved in creating ETL model (snowflake schema), normalizing and documenting.
- Involved in Re-Design that includes dynamic creation of the logic when the new data is added to the system avoiding recoding.
- Walked through the Logical and Physical Data Models of all source systems for data quality analysis.
- Involved in XML parsing and loading XML files into the database.
Environment: Informatica Power Center 8.1/8.6, OWB 10.2, Oracle 11g/10g, ERWIN 4.0, PL/SQL developer, XML, UNIX, Windows, Business Objects XIR4/XIR3.1 (Universe Design Tool/ Designer, BI Launch Pad/ Info view, Interactive Analysis/ Web Intelligence, Info view, Import Wizard, CMS, CCM, CMC).
Confidential, Pittsburgh, PA
- Involved in gathering, analyzing, and documenting business requirements, functional requirements and data specifications for Business Objects universes and reports.
- Created parameterized Crystal reports using Charts, Cross-Tab reports, sub-reports, Running Totals.
- Involved in Performance tuning of Universes by creating alternate views and indexes.Exported reports into PDF, CSV, and XML formats.
- Created and Customized Application Dashboards using Xcelsius to meet the requirements of higher level analysis.Migrated Business Objects XIR2 version reports to Business Objects XI R3.1.
- Took part in JAD sessions to define the Business requirement specifications and Functional specifications by directly interacting with the users and proposing corresponding deliverables.
- Analyzed, designed, developed, implemented and maintained moderate to complex initial load and incremental Informatica mappings and workflows to provide data for reporting purposes.
- Created complex mappings using different transformations such as Filter, Router, Connected & Unconnected lookups, Stored Procedure, Joiner, Update Strategy, Union, Expression and Aggregator transformations to pipeline data to DataMart. Also, made use of variables and parameters.
- Designing automation of ETL jobs using Informatica batches and Pre-Session and Post-Session UNIX scripts using Korn shell scripting in VI editor.
- Designed and created scripts for objects such as tables, indexes, views, sequences, object types and collection types.
- Used changed data capture type mappings to load slowly changing Type 1, 2 and 3 dimensions.
- Scheduled and executed sessions, sequential and concurrent batches for proper execution of mappings; sent notification e-mails using Server Manager.
- Extensively used Power Center command line utilities such as pmcmd, Pmrep embedded in shell scripts to perform various administrative tasks such as migrations, repository backup scripts, start/stop scripts, statistics collections etc.
- Tuned inefficient queries using combinations of index creation, alternate indexing strategies, join strategies and query level optimization hints
- Developed Oracle PL/SQL Stored Procedures.
- Performed Unit testing by comparing the source and target data.
Environment: Informatica Power Center 8.6, Oracle 11g, DB2, SQL Server, UNIX Shell Scripts, Autosys, Power Exchange, Erwin, Windows 7, PL/SQL, Unix (AIX), Business Objects XI R3.1/XI R2 (Web I, Designer, Info view, CMC, CMS, CCM), Administrator, Crystal Reports 2010/2008, Xcelsius 2008