- IT professional with 8+ years of experience in the Development and Implementation of Data warehousing with Informatica Power Center, OLTP and OLAP using Data Extraction, Data Transformation, Data Loading and Data Analysis.
- Exposure with Installation and Configuring of Informatica tools.
- Experience in all phases of the Data warehouse life cycle involving Analysis, Design, Development and Testing of Data warehouses using ETL.
- Strong Data warehousing experience using Informatica Power Center 9X/8x/7x.
- Extensively used Informatica tools such as Informatica Server and Client tools like Designer, Workflow manager, Workflow Monitor, Repository Manager.
- Experience in implementing the complex business rules by creating transformations, re-usable transformations (Expression, Aggregator, Filter, Connected and Unconnected Lookup, Router, Rank, Joiner, Update Strategy), and developing complex Mapplets and Mappings, and SQL Stored Procedure, and Triggers.
- Extensive knowledge in architecture design of Extract, Transform, Load environment using Informatica Power Center.
- Developed Slowly Changing Dimension Mappings of type I, II and type III (version, flag and time stamp).
- Used Incremental Aggregation to update the values in Summary tables.
- Developed mapping using Parameters and Variables. Extensively used parameter file to pass mapping and session variables.
- Experience in Performance Tuning of sources, targets, mappings, transformations and sessions, by implementing various techniques like partitioning techniques and pushdown optimization, and also identifying performance bottlenecks.
- Extensive experience in implementing CDC using Informatica Power Exchange 8.x/7.x.
- Expertise in doing Unit Testing, Integration Testing, System Testing and Data Validation for Developed Informatica Mappings.
- Experience in using the Informatica command line utilities like pmcmd to control workflows in non-windows environments.
- Well Experienced in doing Error Handling and Troubleshooting using various log files.
- Experience using Repository Manager to create Repository, User groups, Users and managed users by setting up their privileges and profiles.
- Having command on troubleshooting with the help of error logs generated by Informatica server.
- Extracted data from various sources like Oracle, Flat file, SQL SERVER, XML files, loaded into Oracle database.
- Strong experience in developing Teradata Loading and Unloading utilities like Fast Export, Fast Load, and Multiload.
- Strong Knowledge in Relational Database Concepts, Entity Relation Diagrams, Normalization and De normalization Concepts.
- Experience with UNIX shell scripting for File validation and SQL programming.
- Practical understanding of Star Schema and Snowflake Schema Methodology using Data Modeling tool Erwin 4.0/4.2.
- Hands on experience in writing, testing and implementing Triggers, Stored, Procedures, Functions, Packages at database level and form level using SQL.
- Highly adaptive to a team environment and proven ability to work in a fast paced teaming environment with excellent communication skills.
- Excellent problem solving, interpersonal and communication skills with experience working in fast paced, challenging and dynamic environment. Highly motivated with passion for learning.
Operating Systems : Windows 2000/XP, UNIX.
ETL Tools : Informatica Power Center V9X/V8x/V7x, Informatica power Exchange V8x,
Informatica Power Connect.
Data Modeling Tool : Erwin 4.0/4.2
Databases : Oracle 8i/9i/10g/11g, Sql, Sql Server 2000/2005/2008, Teradata, DB2
Languages : C, C++, Sql, Html
BI Tools : Crystal reports, Business objects XI 3.1
Database utilities : Toad, SQL Loader, PL SQL developer, Oracle SQL Developer
Bachelors of Technology
Informatica certified DeveloperProfessional Experience
Confidential, Georgia Feb 2011 – Till Date
Role: Informatica Developer
T-Mobile operates the nation's most reliable and largest wireless voice and data network, including the largest 3G broadband network. The scope of the project is to develop operational and analytical reports for each module. The ETL part is handled by using Power centered Informatica. Oracle was Database used where the Facets supports all the Relational Database.
- Interacted with the Business users to identify the process metrics and various key dimensions and measures. Involved in the complete life cycle of the project.
- Developed FRD (Functional requirement Document) and data architecture document and communicated with the concerned stakeholders. Conducted Impact and feasibility analysis.
- Worked on dimensional modeling to design and develop STAR schemas by identifying the facts and dimensions. Designed logical models as per business requirements using Erwin.
- Worked with Power Center Designer tools in developing mappings and Mapplets to extract and load the data from flat files, XML files and Oracle (source) and loaded into Oracle (target).
- Prepared software requirement specifications through interaction with business analysts and designed Star schema, logical and physical database designs.
- Created reusable Mapplets transformations to load data from operational data source to Data Warehouse and involved in Capacity Planning and Storage of data.
- Created different transformations like Source Qualifier, Joiner transformation, Update Strategy, Lookup transformation, Rank Transformations, Expressions, Aggregator, Sequence Generator for loading the data into targets.
- Developed complex mappings such as Slowly Changing Dimensions Type 2, Type 3, type 1 - Time Stamping in the Mapping Designer.
- Used Informatica Workflow Manager to create Workflows, database connections, sessions, and batches to run the mappings.
- Monitored the workflow performance and the status with Workflow Monitor.
- Developed various command tasks to automate the Pre session jobs. Did performance tuning to improve the load. Wrote complex SQL Queries involving multiple tables with joins.
- Involved in writing of Triggers, Functions, and Packages.
- Converted SQL/Procedures and SQL Loader scripts to Informatica mappings.
- Used the Target Load Ordering with Stored Procedures to update database.
- Used Variables and Parameters in the mappings to pass the values between sessions.
- Worked with Informatica Debugger to debug the mappings in Designer.
- Scheduled Informatica jobs using Informatica Scheduler.
- Determined various bottle necks and successfully eliminated them to great extent.
- Performed Unit Testing and Involved in tuning the Session and Workflows for better Performance.
- Implemented Optimization/performance tuning techniques to identify bottlenecks- Query tuning (Explain Plan, SQL Trace), table partitioning, memory tuning and cache management.
- Debugged and sorted out the errors and problems encountered in the production environment.
- Generated Adhoc reports using Business objects.
- Developed Desktop Intelligence and Web Intelligence reports Using Business objects.
- Created graphical representation of reports such as Bar charts, Pie charts as per requirements.
- Created & tested reports using Business Objects functionality like Queries, Sections, Break points, variables, Formulae etc.
- Documented the entire process. The documents included the mapping document, unit testing document and system testing document among others.
Environment: Informatica 9.0.1/ 8.6, Oracle 11g/10g, MS SQL Server 2008, flat files, Windows XP, Business objects xi, Power Exchange 8.6.1, UNIX.
Confidential, Richmond, VA Dec 2009 – Dec 2010
Role: ETL Developer
Magellan Health Services is a specialty health care management company that delivers innovative solutions in collaboration with health plans, corporations and government agencies and their members nationwide.Magellan is a fortune 1000 company serving over 52 million members with 41 health plans and several pharmaceutical manufacturers and state Medicaid programs.Responsibilities:
- Worked with business analysts for requirement gathering, business analysis, and testing and project- coordination.
- Responsible for development, support and maintenance of the ETL (Extract, Transform and Load) processes using Informatica Power Center.
- Developed an ETL Informatica mapping in order to load data into staging area. Extracted from flat files and databases and loaded into Oracle 10g target database.
- Designed Mapping document, which is a guideline to ETL Coding.
- Created mappings using the transformations like Source Qualifier, Aggregator, Expression, Look Up, Router, Filter, Update Strategy, Joiner, Sequence Generators and Stored Procedure
- Worked with various Informatica Power Center tools –Source Analyzer, Target designer, Mapping Designer & Mapplet, Transformations.
- Used Slowly Changing Dimension Mappings of type II and I.
- Created reusable transformations and Mapplets and used them in complex mappings.
- Wrote Stored Programs (Procedures & Functions) to do Data Transformations and integrate them with Informatica programs and the existing application.
- Used Workflow Manager for Creating, Validating, Testing and Running the sequential and concurrent Batches and Sessions, scheduled them to run at a specified time.
- Used mapping Parameters and Variables.
- Worked on Parameterize of all variables, connections at all levels in Window NT.
- Created Sessions, Worklets and Workflows for carrying out test loads.
- Analyzed Session log files to resolve errors in mapping and managed session configuration.
- Created, configured, scheduled and monitored the sessions and workflows on the basis of run on demand, run on time using Informatica Power Center Workflow Manager.
- Involved in migrating the ETL application from development environment to testing environment.
- Extensively usedSQLandPL/SQLto write Stored Procedures, Functions, Packages and Triggers.
- Used PLSQL developer, TOAD to run SQL queries and validate the data.
- Worked on Teradata SQL Assistant to analyze the existing data and implemented new business rules to handle various source data anomalies.
- Extensively used Teradata utilities like Fast load, Multiload to load data into target database.
- Documented and presented the production/support documents for the components developed, when handing-over the application to the production support team.
- Prepared ETL standards, Naming conventions and wrote ETL flow documentation for Stage and ODS.
- Developed advanced reports using Report Studio – Using Tabular Objects, Sub reports, Cascading/customizing prompts and cross tabs, list, charts, Drill- through reports.
- Customized data by adding Calculations, Summaries and Functions.
- Experienced in writing complex reporting using sql performance tuning and providing operational support for OLTP and OLAP applications.
Environment:Informatica Power Center 8.6.1, Teradata V2R6, Oracle11g/10g, SQL Server 2008, TOAD, Business Objects XI, Linux, Windows XP.
Confidential, CLEVELAND, OH Feb 2009 – Nov 2009
Role: Informatica Developer
PNC Bank offers a wide range of services from individuals and small businesses to corporations and government entities. I worked for the Risk Analytics team. The main scope of the project is to capture capital and independent exposures from counterparties that PNC is involved in business with, which in fact helped the users in their package approval process and served several other purposes
- Involved in the Design, Analysis and Development of data warehouse using Informatica Power Center tools. The project involved in creating data warehouse/data mart which is further used for reporting purpose.
- Coordinated with Business system analyst (BSA) to understand and gather the requirements. Documented user requirements, translated requirements into system solutions and developed implementation plan and schedule.
- Analyzed Mapping, Session, Source, Target and System Bottlenecks to improve and tuned Performance of various ETL jobs.
- Designed and developed complex Informatica mappings including Type-II slowly changing dimensions.
- Worked extensively on different types of transformations like Source Qualifier, Aggregator, Joiner, Lookup, and Sequence Generator, Stored Procedure, Expression, Normalizer, Filter and Rank transformations.
- Categorized different alerts and exception types to be built from User’s Data Validation list and built mappings to generate exceptions and alerts accordingly.
- Coordinated with OBIEE Team regarding the reporting data for Answers and BI Publisher reports and also for the Dashboard Designs.
- Created UNIX scripts for file transfers, to remove unwanted characters from source files, and update the time dimension table that was maintained for reporting purposes.
- Involved in maintaining the data model, creating the tables and granting the privileges for read and write operations
- Widely used of Shared folders, project folders (Non-Shared folders) and short cuts to maintain integrity between Development, Test, Data validation and Performance.
- Frequently using import and export utility to migrate session from developer’s folder to subject folder.
- Extensively used parameter file to pass Mapping and Session Variables.
- Involved in many technical decisions and prepared technical design document along with vision diagrams for ETL.
- Worked as an individual team member to develop most of the process as well as working in a team to co-ordinate with other team members.
- Performed Unit Testing for all developed ETL mappings.
Environment: Informatica Power Center 8.6, SQL*Plus, Toad 8.0, Toad, Oracle 9i, OBIEE 10.1.x, UNIX Shell scripting, PL/SQL, CA-7 Scheduling.
Confidential, FL feb 2008 – Jan 2009
Role: Etl Developer
The Web Software Developer will collaborate with the technical team to develop software as service products. This position will collaborate with internal technical teams to design, develop, and implement databases for our software applications. This position is responsible for understanding end-to-end requirements and providing technical expertise in designing, developing, and maintaining our database architecture. Neighborhood America is seeking a talented, hands-on software engineers to join our team building enterprise social networking solutions.
- Analyzed the business requirements and framing the Business Logic for the ETL Process.
- Extensively used ETL to Load Data from fixed width as well as Delimited Flat files.
- Worked extensively on different types of transformations like Normalizer, Expression, Union, Filter, Aggregator, Update Strategy, Lookup, Stored Procedure, Sequence Generator and Joiner.
- Designed and Developed complex mappings, Reusable Transformations for ETL using Informatica Power Center.
- Loaded the data from .CSV file to Oracle and Teradata.
- Designed Mappings between sources to operational staging targets, using Star Schema, Implemented logic for Slowly Changing Dimensions.
- Developed and tested all the Informatica mappings involving complex Router, lookups and update strategies.
- Extensively wrote user SQL coding for overriding for generated SQL query in Informatica
- Created workflows and worklets for Designed Mappings.
- Implemented variables and parameters in the mappings.
- Scheduled the loads at required frequency by Setting up batches and sessions using Power center workflow manager, PMCMD and also using scheduling tools.
- Generated completion messages and status reports using workflow manager.
- Worked with Workflow Manager to import/export metadata, jobs, and routines from repository, and also created data elements
- Worked on Dimension as well as Fact tables, developed mappings and loaded data on to the relational database.
- Extensively worked in Performance Tuning of programs, ETL procedures and processes. Also used debugger to Troubleshoot Logical Errors.
- Wrote ETL specifications and unit test plans for the mappings.
Environment: Informatica Power Center 8.1, Oracle 9i,Teradata, SQL, PL/SQL, MS SQL Server 2005, TOAD, Erwin 4.1, UNIX Shell Script, Windows 2003
Confidential, IL Dec 2006 – JAN 2008
Role: Etl Developer
The Healthcare business of Thomson Reuters is the leading provider of decision support solutions that help organizations across the healthcare industry improve clinical and business performance. Our products and services help professionals and stakeholders understand healthcare markets, access medical and drug information, manage costs, and improve the quality of healthcare. The Healthcare business of Thomson Reuters offers business solutions for clinicians, hospitals and healthcare providers, employers, health plans, government agencies, pharmaceutical companies and researchers.
- Involved in the team during the entire ETL process and development of data marts using Informatica Power Center.
- Developed Transformation logic and created various Complex Mappings and Mapplets using the designer.
- Created set of Reusable Transformations and Mapplets.
- Implemented Type III Slowly Changing Dimensions.
- Debugged a valid mapping to gain Troubleshooting information about Data and Error Conditions.
- Employed Informatica Power Center Workflow Manager for session management, database connection management and scheduling of jobs to be run in the batch process.
- Worked with Variables and Parameters in the mappings to pass the values between sessions.
- Worked on Partitioning of the Sessions and Performance Tuning of Informatica.
- Created Multiple Sessions and executed workflows using Workflow designer and viewed the results using workflow monitor.
- Involved in Unit Testing for the validity of the data from different data sources
- Fine tuned transformation and mappings for better performance.
- Worked with PMCMD command line program to talk with the Informatica server.
Environment: Informatica Power Center 7.9.3, Windows 2000, Oracle 8i, SQL Server 2005/2000,Flat files and Toad, Cognos 7.0
Confidential, India Feb 2004- Nov 2006
Role: Software Developer
Version IT is a Hyderabad based firm offers a range of services that includes Custom Application / Software Development, Hardware and Networking Solutions. It is a leading software institute focused on delivering the best and most cost-effective solutions to the clients in areas such as Educational Institutions, E- Business, Healthcare, Hotels, and Industries. The objective of this project was to build a flexible real-time data warehouse.
- Extracted Data from Different Sources by using Informatica.
- Extensively used Informatica client tools Source Analyzer, Warehouse designer, Mapping designer and Mapplet Designer
- Extracted data from different sources of databases. Created staging area to cleanse the data and validated the data.
- Designed and developed complex Aggregate, expression, filter, join, Router, Lookup and Update transformation rules.
- Developed schedules to automate the update processes and Informatica sessions and batches.
- Analyzed, designed, constructed and implemented the ETL jobs using Informatica.
- Developed mappings/Transformations/mapplets by using mapping designer, transformation developer and mapplet designer in Informatica Power Center.
- Developed Shell scripts to setup runtime environment, and to run stored procedures, packages to populate the data in staging tables.
- Created Users, user groups, database connections and managed user privileges using supervisor.
Environment: Informatica Power Center 7.1.1, Oracle 8i, MS SQL SERVER 2000, SQL, PL/SQL, SQL*Loader, UNIX Shell Script.