Informatica Developer Sql

NIKHATH

SR. INFORMATICA DEVELOPER

SUMMARY

●Over 8 years of Data warehousing & ETL experience with emphasis on Requirement analysis, Data analysis, Application Design, Development, Implementation, Testing, Maintenance and Documentation.

●Extensive Data Warehouse experience using Informatica Power Center 10.X, 9.X, 8.X (Repository Manager, Mapping Designer, Workflow Manager, Mapplet Designer, Workflow Monitor, Source Analyzer, Transformations Designer, Warehouse Designer), Power Exchange.

●Extensively worked on the ETL mappings, analysis and documentation of OLAP reports requirements. Solid understanding of OLAP concepts and challenges, especially with large data sets.

●Designed and developed complex mappings, from varied transformation logic like Unconnected and Connected lookups, Router, Filter, Expression, Aggregator, Joiner, Stored Procedure, Update Strategy, SCD’s Type1, Type2 and Type3.

●Experience on Debugger to validate the mappings and gain troubleshooting information about data and error conditions.

●Extensively worked on Ralph Kimball and Bill Inmon methodologies.

●Expertise in Informatica Performance tuning and Database Performance tuning.

●Worked extensively with Dimensional modeling, Star Schema, and Snowflake modeling, Physical and Logical data modeling using Erwin, Data migration, Data cleansing, Data profiling, ETL Processes features for data warehouses.

●Transformation and Loading of data from multiple feeds and sources into Data Warehouse.

●Experienced in SQL Optimization and SQL Loader.

●Experience in integration of various data sources like Oracle, DB2, SQL Server, Teradata and Flat Files.

●Extensive knowledge of PL/SQL, SQL programming.

●Experience in working with UNIX Shell Scripts for automatically running sessions, aborting sessions and creating parameter files. Written number of shell scripts (Korn Shell) to run various batch jobs.

●Extensive work experience with Database programming in Oracle 10g/9i/8i/7, MS SQL server 2008/2005

●Good understanding with all phases of SDLC (System Development Life Cycle) including Planning, Analysis, Design, Implementation and Maintenance.

●Good exposure in projects with the Onsite - Offshore model.

●Strong analysis and design skills with an ability to work with business partners to understand complex functional needs, leading the creation and maintenance of technical specification documentation.

●Enthusiastic, team player, goal-oriented, independent, abreast with latest technology and literature

●worked on data conversion project using IICS

●created IICS mappings and task configuration in IICS

●worked on agile scrum methodology of project development of 2 weeks of iteration.

TECHNICAL SKILLS

Data Warehousing / ETL Tools

Informatica Power Center 10.4/10.2/9.6.1/9.5/9.1.1 HF2/8.6.1, Informatica Power Exchange, Informatica Meta Data Manager, IDQ, IDE, Oracle Warehouse builder (OWB), Business Objects, Micro Strategy, Cognos, OBIEE.

Data Modeling

Erwin, MS Visio, Star Schema Modelling, Snow Flake Modelling

Databases

Oracle 19c/11g/10g, Universal DB2, UDB 8.0/7.0/6.0, Teradata 13, MS SQL Server 2008 R2/2005/2003, MS-Access, Netezza.

Languages

C, JAVA, SQL, PL/SQL, T-SQL, BTEQ Scripts, Unix Shell Script, Batch scripting.

Tools

Toad, SQL Developer, SQL Server Management Studio, Netezza Workbench Agility, SQL Assistant 13, Fast Export, Putty, WinSCP

Operating Systems

Windows 95/98/NT/XP/2000/2003, Sun Solaris, Linux, Red Hat, AIX

Scheduling Tools

Informatica Scheduler, Tivoli, Control M, Crontab, Autosys

Business Domain

Utility, Finance, Oil and Gas, Pharmaceuticals, Retail.

Professional Experience

Bank of America, Charlotte, NC Feb 2020- Present

Sr. Informatica Developer

Customer onboarding (CO): Customer onboarding project involved the implementation of the customer reporting on the Waste Management trucks to facilitate the latest customer information to the truck driver on the onboard computers to facilitate the up to date information to the drivers. The prerequisite to the project was Customer Centric Database (CCDB) which was the legacy phase to the Customer onboarding data warehouse.

Responsibilities

●Created mappings for loading the data into OBIEE repository.

●Created the Visio document for the Informatica workflows to be included in DAC.

●Configured RPD and DAC for OBIEE.

●Worked extensively on SQL Scripts and UNIX shell scripts.

●Performed Unit testing and Data validation testing using Validation scripts

●created runtime environment in IICS

●used mapping templates to create IICS jobs

●created IICS mapping and task configuration in IICS

●Proven Accountability including professional documentation and status report.

ETL Testing:

●Created Data Validation document, Unit Test Case Document, Technical Design Document, Informatica Migration Request Document and Knowledge Transfer Document.

●Created about thousand test cases for data testing.

●Created numerous Jira tickets for the developers for bug fixing for UAT.

●Created automated Informatica data validation options (DVO) test scenarios.

●Created about 2000 test cases for Regression testing.

●Did black box/White box testing.

●Created User Acceptance testing (UAT)/Regression and Unit Testing documents.

●Created boundary values for black box testing.

●Created test cases for black box testing from the functional specification documents.

●Extensively wrote test plans with objectives, scope, approach and focus on performance testing.

●Worked closely with multiple application developers for Integration testing such as ETL Developers, BA’s and report developers and scheduled jobs as per the inputs from multiple application users.

Environment: Informatica Power Center 10.4/10.2, Oracle 11g, Oracle EBS, Flat Files, Netezza TwinFin3, TwinFin6, Netezza Skimmer, Netezza Work Bench Agility, UNIX, Win XP, SQL Developer, SQL Assistant, Control-M, Putty, OBIEE.

University of Maryland, College Park, MD Oct 2018- Jan2020

Sr. Informatica Developer

Student discrepancy Project at UMD: This project has been undertaken to report the discrepancies coming in from different source systems and creating the work request and work items for the source system data stewards in order for the data to be consistent in all the source systems.

Responsibilities

●Work with team, lead developers, Interfaced with business analysts, coordinated with management and understand the end user experience.

●Interacted with various business people in MVS and Facets side and gathered the business requirements and translated them into technical specifications.

●Documented business requirements, discussed issues to be resolved and translated user input into ETL design documents.

●Created custom plans in IDQ and incorporated it into Informatica PowerCenter as a Mapplet.

●Used Informatica Data Quality for standardization of names and addresses.

●Created SSIS packages/projects to load data from flat files into Oracle.

●Created SSIS package to get the file name dynamically using the ForEach loop container.

●Created batch script to archive the processed files and incorporated that into SSIS package using the Execute process task.

●Migrated Informatica code using repository manager.

●Validated the existing inventory information as well as capturing missing inventory information.

●Develop Informatica mappings for each of the inventory objects to move data from Field Audit Tool to Target Mappings.

●Synchronized the data up to the optimum level.

●Automation of daily runs of ETL, merging and consolidation using Informatica, Oracle and Windows Batch files

●Integrated the Informatica workflows into OBIEE RPD and created connections for source/target in OBIEE RPD.

●Worked closely with the OBIEE developers in creating the RPD of OBIEE (metadata layer).

●Used Multi load connection to load data into Teradata tables.

●Sourced data from Teradata to Oracle using Fast Export and Oracle SQL Loader.

●Created load scripts using Teradata Fast Load and Multi load utilities and procedures in SQL Assistant

●Extensively used WSDL workspace in the Power Center Designer to edit the XML views of the XSD’s.

●Designed the ETL mappings using several transformations. Created source and target transformations by importing WSDL into Designer.

●Created Process Control and Metadata for informatica jobs (Job control tables).

●Created mapping using various transformations like Joiner, Filter, Aggregator, Lookup, Stored Procedures, Router, Sorter, Rank, Expression, Normalizer and Update Strategy.

●Involved in performance tuning of the ETL process by addressing various performance issues at the extraction and transformation stages.

●Extensive performance tuning by determining bottlenecks at various points like targets, sources, mappings and sessions.

●Created and Executed workflows and worklets using Workflow Manager to load the data into the Target Database.

●Extensively worked on Mapping Variables, Mapping Parameters, Workflow Variables and Session Parameters.

●Developed Session Parameter files for the workflows.

●Extensively participated in System/Integration/Performance testing.

●Analyzed the source fields and wrote PL/SQL queries for field-field validation by referring source to target mapping document.

●Created visio diagrams for Autosys team to schedule Informatica jobs.

●Worked on Packages, Procedures and Views.

●Worked closely with the reports developers in building Business Objects reports.

●Worked extensively on SQL, BTEQ Scripts and UNIX shell scripts.

●Responsible for ETL process under development, test and production environments.

●Handled Production issues and monitored Informatica workflows in production.

Environment: Informatica Power Center 10.1/9.6.1, Informatica IDQ 10, Oracle 10g, SAP BW Flat Files, SQL Server 2008, SQl Server business intelligence development studio (BIDS), Teradata 13, UNIX, Win XP, SQL Plus, Transact-SQL, Autosys, Putty, OBIEE 11g, SAP BOXI.

Shell Trading, Houston, TX July 2016-Sep 2018

Sr. Informatica Developer

Project: Liquidity Management Reports

Responsibilities:

●Involved in designing the specifications, Design Documents, Data modeling and design of data warehouse.

●Analyzing existing database schemas and designing star schema models to support the users reporting needs and requirements.

●Responsible for definition, development and testing of processes/programs necessary to extract data from operational databases, Transform and cleanse data, and Load it into data warehouse using Informatica Power center.

●Created the repository manager, users, user groups and their access profiles

●Extracted data from different sources like DB2, Sql server, flat files and loaded into DWH

●Created Logical & Physical models and used Erwin for data modeling and Dimensional Data Modeling.

●Created complex mappings in Power Center Designer using Expression, Filter, Sequence Generator, Update Strategy, Joiner and Stored procedure transformations.

●Created connected and unconnected Lookup transformations to look up the data from the source and target tables.

●Involved in design change impact analysis summary for each data domain, for the proposed changes to that domain, for analytics, reports, and data quality routines

●Modeling and populating the business rules using mappings into the Repository for Meta Data management.

●Constantly tested SQL for joins and contexts for new object creation. Using Rapid SQL conducted functional testing in the SQL of existing objects.

●Created informatica mappings to load data using transformations like Source Qualifier, Sorter, Aggregator, Expression, Joiner, Connected and Unconnected lookups, Filters, Sequence, Router and Update Strategy.

●Used Power Exchange to source copybook definition and then to row test the data from data files etc.

●Worked with the Cognos developers in creating the cognos cubes for multi-dimensional reports.

●Implemented Slowly Changing Dimensions (SCD’s, Both Type 1 & 2).

●Extensively worked on tuning (Both Database and Informatica side) and thereby improving the load time.

Environment: Informatica Power Center 9.6.1/9.5, Power Exchange, Windows NT, Agile/ Scrum Methodology, Oracle 9i, Teradata, VB, TOAD, DB2, Erwin 4.0, SQL, PL/SQL, SQL * Loader, Linux, Java, C++, T SQL, TOAD, Cognos EPlanning 8.4.1.

McKesson Pharmaceuticals, Houston, TX Aug2015 – June2016

Informatica Developer

The Aim of this project was to migrate the existing MS Access database to Oracle 9i for multi user access into a new format. The project involved with Health care product application, Identifying user Requirements and Analyzing the Existing Data Source and IMS data to Build Product Platforms into Standardized Data warehouse/ Data Mart.

Responsibilities:

●Extracted data transformed and loaded into Oracle database according to the specifications.

●Used various transformations like lookup, update strategy, router, filter, sequence generator, source qualifier/Joiner on data and extracted as per the technical specifications.

●Created Mapplets using Mapplet Designer to reuse Mapplets in the mappings.

●Experienced in performing the analysis, design, and programming of ETL processes for Teradata.

●Generated a flexible transformation framework from a message specification like avoid regulatory penalties and loss of data by keeping compliant with the latest standard versions, without having to wait for a patch or upgrade.

●Used Informatica to provide up-to-the-minute pre-built transformations in support of industry standards

●Created Mapping Parameters and Mapping Variables to use globally.

●Created debugging sessions before the session to validate the transformations and also used existing mappings in debug mode extensively for error identification by creating break points and monitoring the debug monitor.

●Improved the performance of the mappings by moving the filter transformations early into the transformation pipeline, performing the filtering at Source Qualifier for relational databases and selecting the table with fewer rows as master table in joiner transformations.

●Created partitions, and SQL Over ride in source qualifier for better performance.

Environment: Informatica Power Center 9.1.1 HF2/8.6.1, Oracle 8i, SQL, PLSQL, Java, C++, Business Objects 5.0, TOAD, Erwin 4.5/4.0/3.x, and Windows NT

Education: B S (Computers)

References: Available upon request

Contact this candidate