ETL

FRANCINE DOROTHY LOORTHURAJ

**********@*****.*** Ph: +1-945-***-****

PROFESSIONAL SUMMARY

Result-driven professional experience of 13+ years in the areas of Data warehousing and Business Intelligence. Extensively worked on Data warehouse, Data Conversion, Data Migration, Data Integration projects and Capable of providing end to end solution for Application Integration.

Certified Snowflake developer and have experience in Snowflake and AWS technologies

Experience in Informatica Power Center and IICS and AWS. Excellent hands-on in building Informatica mappings in Informatica power center and IICS

Core expertise in Oracle Retail Merchandising System (ORMS) and ReIM application with functional knowledge in RMS item foundation, supplier Management, purchase orders, transfers, shipping and receiving process, inventory management, auto match and stock ledger

Extensive experience in writing code on stored procedures, functions, packages, database triggers, sequences, indexes, materialized views using PL/SQL.

Key technical skills include relational databases such as Snowflake, Oracle. Exclusive hands-on skill in writing Unix Shell scripts and performance tuning.

Experience in working with Version Control System, RDMS concept, Slowly Changing Dimension, Performance Tuning, Data Optimization, Normalization and De-normalization of data structure for performance.

Experience with Data Modeling using ERWIN 4.0/3.5.2, SQL, PL/SQL, SQL Plus, Toad, Query Analyzer

Experience in agile development using JIRA, Confluence and Wiki.

TECHNICAL SKILLS

ETL tools

IICS, Informatica Power Center 9.6.1/10.1 (Designer, Workflow Manager, Workflow Monitor, Repository manager)

Cloud Technologies

AWS S3

Databases

Oracle 10g/11g, Snowflake, My SQL

Operating Systems

Unix AIX, Windows, Linux

Data Modelling Tools

MS Visio, CA Erwin Data Modeling Suit (ERWin v7.3 and higher), Power Designer

Languages

SQL, UNIX Shell scripts

Scheduling Tools

CA ESP Scheduler, Informatica Scheduler

Methodologies

Data Warehousing Design, Data Modelling, Logical and Physical Database Design, Software

Development Life Cycle (SDLC), Agile

Testing Tools

Application Lifecycle Management (ALM)

Special Tools

Toad for Oracle/ DB2, SQL Developer, Teradata SQL Assistant, Putty, WinSCP, JIRA, Confluence, SVN, Service Now,GitLab

CERTIFICATIONS / TRAININGS

Certified in Amazon Webservice Certified Solution Architect Professional

Certified in Snowflake Pro Core

Certified in Oracle Pl/SQL

PROFESSIONAL EXPERIENCE

Project Name: Health Dyne Pharmacy API Integration Apr 2024 – till date Client: US Renal Care (Contract)

Role: ETL developer /Oracle Pl/SQL Developer

Location : Plano

The main objective of this project is to provide seamless consumer experience with API integration capabilities in pharmacy industry . Health dyne provides pharmacy services to all types of healthcare organizations that ship medication to all types of heathcare organizations that need ship medications to patients. Health Dyne Pharmacy API allow developers to integrate our fulfillment capabilities into their organization health care application

Responsibilities:

Involved in creating Packages and procedures for JSON documents which are being sent from the front end application and extensively using collections and REF cursors

Involved in Designing and developing database schemas, stored procedures, functions, and triggers using PL/SQL.

Designing and developing database schemas, stored procedures, functions, and triggers using PL/SQL.

Optimizing database performance by tuning SQL queries and PL/SQL code in Well Dyne API.

Involved in tuning of the existing packages and procedure for performance optimization of existing API

Involved in processing and parsing JSON and XML files using Oracle SQL and PL/SQL.

Developing and executing test plans to ensure the quality and accuracy of PL/SQL code.

Troubleshooting and resolving issues related to PL/SQL code.

Participated in sprint planning, story grooming, design discussion and daily scrum.

Provided regular status on all work activities that ensures management to get updates on all issues and risks

Created mappings in Power Centre Designer using Aggregate, Expression, Filter, Sequence Generator, Update Strategy, Union, Lookup, Joiner and Stored procedure transformations

Development tools: Oracle PL/SQL Developer, Toad for Oracle, Unix Scripting, Winscp, putty, GitLab, CA Scheduler. Pl, Dot Net,Informatica

Company: Tata consultancy Services Ltd: June 2010 – April 2024

Project Name: IICS Supplier EDI Integration May 2020 – April 2024

Client: Rent-A-Center

Role: Lead ETL Developer

IICS, Informatica, Oracle Pl/SQL,Snowflake

This project is a big initiative for RAC strategic decision. RAC has decided to move to cloud based environment and migrate all the Informatica Power Center mappings to IICS. Plan is to change data warehouse from Oracle to Snowflake, EC2 as data intermediate layer and for reporting from Info burst to MicroStrategy. As part of this change, all the existing Supplier EDI integration will be moved to cloud integration. Suppliers will be created in IICS B2B module. Informatica Power Center mappings which have Developer client code (Parser) and serialize has to be

re-written using IICS Data Integration module and IICS B2B module.

Responsibilities:

Analyze the existing EDI system and design new system that satisfies the requirement.

Organized brainstorming session with business and stakeholders to capture the business requirement and functional specification.

Expertise in various services of IICS like data integration, application integration, B2B gateway, monitor, application integration services, Administrator, Data quality, profile and so on

Expertise in creating Data Synchronizing Task, Mapping Task, Mapping Configuration Tasks

Extensively worked with File Listeners, Schedulers, Connections, Swagger Files, Task Flows, Mapplets, Parameterized Mappings and configuring re-usable code.

Involved in code migration/deployments in IICS and involved in pre-release environment validations.

Expertise in using the various advanced transformations like Hierarchy Builder, Hierarchy Parser, Structure Parser, Webservice Transformation, Aggregator, Joiner, Lookup, Normalizer

Have extensively worked with various components like Business Services, Intelligent Structure Model, File Listeners, Fixed width file Formats.

Develop mappings, Process flow, Data Dictionaries, reference tables and mapping rules and tables in Snowflake.

Modified existing PL/SQL Packages and Procedures for the new enhancements on the integration and created new Packages and Procedures for the business to continue until the migration to the new system.

Designed new dimensional data model after understanding of existing processes with the help of DBA team.

Prepared the DDL’s for the staging / worktables and coordinated with DBA for creating the development environment and data models.

Created mappings in IICS (Data Integration) module which uses Hierarchical Builder and created vendors in IICS (B2B Modules) and integrating the same logic as in On Perm for the vendors to communicate using EDI messages

Perform analysis, design and implementation of batch processing using CAWA Scheduler and IICS Scheduling to move files from EC2 and S3 bucket

Participated in sprint planning, story grooming, design discussion and daily scrum.

Provided regular status on all work activities that ensures management to get updates on all issues and risks

and mentoring the new team members

Development tools: IICS, Informatica 10.2, Snowflake, Amazon S3 EC2, Snow SQL, Power Exchange, Toad for Oracle, Unix Scripting, Winscp, putty, GitLab, CA Scheduler.

Project Name: Retail Merchandise Analytics (RMA) / RMA Supplier Jan 2018 – Apr 2020 Client: JCPenney Corporation, Inc

Role: ETL Developer & Analyst

(Informatica/Oracle Pl/SQL)

This project is one of the major mile stone in JCPenney Business Intelligence (BI) space. The goal of the project is to implement and extend Oracle Retail Merchandising Analytics (ORMA) product to cater to the BI and Reporting needs for JCP. ORMA is the Analytics/Reporting component from Oracle Retail which utilizes Oracle Business Intelligence suite for reporting. The main objective is to replace the existing legacy report and build new report in RMA using OBIEE, Oracle, ODI, PLSQL and Informatica. As part of this change, all the existing integration that are currently point to legacy system will be repoint / rebuild in RMA Space. This involve changes in sales, planning & forecast, inventory, demand and finance stream.

Responsibilities:

Organized brainstorming session with business and stakeholder to capture the business requirement and functional specification.

Modified existing PL/SQL Packages and Procedures for the new enhancements on the integration with source RMS system

Modified the existing RPD and have developed user interface projects (reports, analysis and dashboards) and involved in deployment process

Created mappings in Power Centre Designer using Aggregate, Expression, Filter, Sequence Generator, Update Strategy, Union, Lookup, Joiner and Stored procedure transformations

Rewrite the PL/SQL packages from RMA system to Python functions to migrate from Oracle to Snowflake database

Create and Provide SQL scripts for DDL and coordinate with onsite Data Modeler to implement the changes in data model as well as in database.

Used Oracle OEM, EXPLAIN PLAN, Collect Statistics for performance analysis, monitoring and query tuning.

Maintained version control tool (SVN) to keep track both Pl/SQL and Informatica components.Practiced Agile Scrum for planning and prioritizing user stories through daily scrum, grooming sessions, retrospective and show and tell meetings

Development tools: Informatica 10.1, ODI, Power Exchange, Teradata (FAST LOAD, BTEQ, MLOAD, FASTEXPORT), Toad for Oracle / DB2, Teradata SQL Assistant, BTEQ, OBIEE 11g, Unix Scripting, Winscp, putty, SVN, ESP Scheduler, Netezza.

Project Name: RMS EDI Integration Feb 2014 – Dec 2017

Client: Rent-a-center Role: ETL /Oracle Developer

The Main objective of this project is that Rent-A-Center (RAC) requires a foundational integration capability and architecture that will enable suppliers and logistics vendors to communicate. In order to accomplish the integration needs, RAC systems will need to integrate with suppliers via EDI messages. Communication between the RAC and Suppliers are through EDI X12 messages. Below are Inbound and Outbound EDI messages transmitted to and from Suppliers.

Inbound EDI Transaction: 810,856,865,855,997 Outbound EDI Transaction: 850,860,997

EDI is integrated with RMS and Invoices sent from EDI are sent to RMS. The project also involves changes in RMS/REIM application and the modules involved are RMS item foundation, supplier Management, purchase orders, transfers, shipping and receiving process, inventory management, auto match and stock ledger

Responsibilities:

Involved in analysis of existing system architecture to understand system behaviour to support production issues.

Involved in development and minor enhancements in RMS

Involved in minor enhancements, production fixes to existing design and migration of WPG process to Informatica workflows.

Involved in all the design clarification calls with the clients to make sure that there is no deviation from the expected requirements.

Involved in all the high-level design and coding in accordance with coding standards.

Maintained version control tool (SVN) to keep track both Pl/SQL and Informatica components.

Participated in sprint planning, story grooming, design discussion and daily scrum.

Provided regular status on all work activities that ensures management to get updates on all issues and risks.

Create and Provide SQL scripts for DDL and coordinate with onsite Data Modeler to implement the changes in data model as well as in database.

Development tools Oracle Pl/SQL, Oracle SQL Developer, Unix Scripting, Winscp, putty, SVN, ESP Scheduler, RMS,REIM, Informatica Power center, Netezza

Project Name: JDA Integration Prospace Replacement Aug2011 – Jan 2014 Client: Target Corporation

Role: Sr Oracle PL/SL Developer

To Replace the Target's current version of Prospace (Planogram Tool) with JDA's most up-to-date planogram software, Space Planning (which is a part of the JDA Intactix Knowledge base suite) as Prospace is not compatible with Windows 7 and is unsupported by the vendor. The main objectives for the Prospace Replacement Program are:

Replace 2006 version of JDA Prospace with the latest version of JDA Space Planning which is part of the JDA Intactix Knowledge base suite.

To provide a planogram tool that is stable, supportable, scalable and sustainable.

Building the foundation for MPD with Space Planning & Suite of Applications.

Building new customized Oracle Stored Procedures to integrate IKB and MPD application through web service.

The Intent of this Project is to analyze the requirement integration layer between Target's new Intactix Knowledge base suite and interfacing Merchandise Planning Department ("MPD") Applications.

The Objective of the integration layer is to synch up planogram data between MPD application and the new IKB solution. There are 21 downstream Application that access the IKB data real-time. The data synch up is achieved by using WCF services and ETL.

Responsibilities:

Responsible to gather and analyze the requirements from the customer for Project Development & Maintenance.

To Integrate and Reconnect 18 major MPD applications to the new JDA software.

Created customised Oracle procedures to integrate IKB and MPD applications

Ensure to Maintain the Quality Standards in all Phases.

Responsible for meeting project deadlines.

Implement defect preventive measures on all the tasks received.

Review the project deliverables to ensure the quality and adherence to coding standards

Designed new dimensional data model after understanding of existing processes with the help of DBA team. Prepared the DDL’s for the staging / work tables and coordinated with DBA for creating the development environment and data models.

Training and Mentoring of new entrants and team members

Development tools: Pl/SQL Oracle SQL developer, Putty, Winscp,IKB Suite .

Project Name: Target VMC supply Jun 2010– Jun 2011

Client: Target Corporation

Role: Sr Oracle PL/SL Developer

Target Corporation has decided to identify redundancies and discrepancies existing among the vendor groupings within the Business Partner Management legacy system (BPM), cleansing of the redundant data and migration of the existing BPM vendor and business partner data to the new Vendor Management and Maintenance (VMM) system . BPM manages relationships between vendor account numbers and other business partner groups (i.e.) a system that manages a total corporate profile about Target business partners. A Business Partner (BP) is created within the BPM system to represent the total company structure - this could potentially include aspects of their business not directly engaged with Target Corporation. After the data Cleansing and Data Migration from BPM to VMM, VMM system would be free from data redundancy in vendors and business partners information and will be equipped with more robustness in handling complex vendor relationships

Responsibilities:

Responsible for analyzing existing BPM applications.

Responsible for preparing the Impact Analysis document.

Designing documents for Data Cleansing and Data Migration and preparation of HLD and LLD.

Creating PL/SQL Packages and procedure for Data cleansing the data from BPM system and migration data to new VMM system

Interface closely with business and technical clients for issues and maintenance activities.

Ensure to deliver all the client deliverables as per the quality standards defined and within stipulated timelines.

Training and Mentoring of new entrants and team members.

Prepared the Conceptual and Logical and Physical Data Model using the third-party tool CA Erwin v7.3.

Created standard abbreviation document and Version Control or Change tracker for Logical and Physical Data Models.

Create de-normalized Physical Data Model design to improve the query performance and ensure that subtypes and super types in the logical model must be eliminated in the physical model, typically by either rolling up the subtypes to the super type or rolling down the super type to the subtypes

Provided SQL scripts for DDL from the data models and coordinate with DBAs to implement the database.

Development tools: Oracle Pl/SQL, Winscp, putty, SQL Developer

Company: Cognizant Technology Solutions: Mar 2006 – May 2010

Project Name: AZ GDD IS Global S&M Jul 2008– May 2010

Client: Aztrazeneca

Role: Module Lead

AstraZeneca is a large global pharmaceutical company with sales of over $21 billion and employee strength of more than 65,000 worldwide. The client’s products are available in over 100 countries in the areas of: cancer, cardiovascular, gastrointestinal, infection, neuroscience, and respiratory and inflammation. AstraZeneca spends more than $14 million every working day on research & development of new medicines that meet patient needs.

AstraZeneca is working to create a more personal, more positive healthcare experience for patients. It provides health information, support, and resources. It offers access to a number of prescription assistance programs and is involved in hundreds of local, community-based initiatives. Their effort helps to enrich the quality of people’s lives all over the world.

Responsibilities:

Involved in analysis, design and testing of the project.

Translate business needs into technical requirements to ensure all Systems support and confirm to business specifications.

Provided SQL scripts for DDL from the data models and coordinate with DBAs to implement the database.

Understanding the Requirement Documents and Mapping sheet. Evaluate, redesign and modify existing Data Stage job

logic to improve overall system performance.

Validating the data loaded in the Target system. Providing Brief support in Dry Run and production phase

Develop/modify new/existing batch/online process in Data stage jobs for all new request from the customers.

Prepare Test plan and Validation Test Cases for the layout.

Review traceability matrix to keep track of development progress of the project.

Support for Implementation and User acceptance Testing.

Looking forward for continuous improvement and betterment of team.

Prepared the DDL’s for the staging / work tables and coordinated with DBA for creating the development environment and data models.

Development tools: Oracle Pl/SQL,SQL Developer,Toad, Datastage,

Project Name: Mellon UK-MEFS Noveta system Mar 2006– Jul 2008

Client: Mellon Europe Ltd, United Kingdom Role: ETL Developer

This project is all about developing and maintaining of MEFS (Mellon European Fund Services) ‘Noveta system.

The project deals about a CRM application named Maisie which has many applications integrated. The front end is Point Object Programming and backend is Oracle. Some of the Applications which are integrated are given below.

1.Formscape

This is a tool which is used to generate pdf files for the given input xml file based on the adhoc or periodic request. The process has to be created based from the client HLD specification and pdf’s which are generated are integrated with the CRM application using POP (Point Object Programming.

2.Crystal Reports

Crystal reports are created based on Client specification and these reports are integrated with the Maisie application using POP.

3.Batch Processing

The batch system loads all the input data such as address, pricing from various applications on a daily basis to CRM application so that it is up to date. The batch system developed using SQL Loader which loads all the input data into temporary table and the stored procedures load into main table.

.

Responsibilities:

Involved in developing oracle stored procedure, prepared database scripts and framed queries based on the need.

Involved in changing the process in Formscape and creating Crystal Reports based on Client specification.

Responsible for performing the impact analysis and was able find out the systems/programs that could be potentially affected by proposed change(s) and was involved in coding, testing and implementation activities.

Involved in the preparation of various technical and functional documents (where applicable depending on the request types) like Impact Assessment Document, Functional Specification Document, Detail Design Document, Unit Test Plan, Test log and Unit test log.

Development tools: Crystal Reports, Loader, Point Object Programming,Oracle Pl/SQL

PERSONAL AND EDUCATIONAL DETAILS

A married female born on June 17, 1983

Batchelor of Engineering in Electronics and Communication from Anna University (Kurinji College of Engg & Tech) – [April 2001- April 2005 ]

Possessing an Indian Passport N3304294

Linked In : https://www.linkedin.com/in/francine-dorothy-loorthuraj-0a67131a4/

Contact this candidate