Data Project

ANUPRIYA GURUSAMY

Waukegan, IL Mobile: +1-224-***-**** E-mail: ************@*****.***

SUMMARY Around 7 years of IT industry Experience mainly in Data warehousing as Business Intelligence Data Warehouse developer- Support all aspects of analysis, design, development, unit testing and implementation of enterprise data warehouse.

Expertise in providing end-to-end Data Warehousing solutions by effective data modeling and ETL concepts and evaluate design solutions objectively. Strengths include object-oriented software development, problem solving, and a diverse base of functional and technical knowledge.

Possess strong Technical knowledge on development of Database, ETL based applications and Reporting and thorough understanding of Data Modeling in data warehouse environment such as star schema and snow flake schema.

Extensive knowledge on design patterns, architecture, development and testing. Ability to work single handedly and delivered quality solutions to complex applications in Healthcare and life science domain.

Demonstrates full understanding of Software Development Life Cycle(SDLC).

Experience in development of ETL Mappings using Informatica, load the database using Teradata utilities (TPT, Fastload, Mload, BTEQ and TPump) and scheduling the jobs using UNIX shell scripts in Autosys.

Good knowledge on writing SQL queries across different databases and worked with various development and database tools (Oracle SQL Developer, Teradata SQL Assistant, Toad).

Experienced in Batch System Scheduling using UNIX shell scripts.

Monitor the performance of the existing and new data integration applications to ensure successful completion, track performance and isolate areas of improvement and for general debugging.

Incorporated various performance tuning techniques in Informatica on mapping and session levels like DTM buffer/block size configurations, session partitioning and at databases by defining partition primary index, range by partition index and etc.

Provide accurate time estimate and status report of the jobs to the management as required.

Worked on various platforms including Windows NT, 2000, XP and UNIX.

Designing reusable, scalable and maintaining ELT templates and experienced in developing Data Mapping, Mapplet, Reusable Transformations, Workflows and Performance Tuning - Identifying Bottlenecks of sources, mappings, targets and sessions.

Extensive knowledge of managing development team and providing technical support from onsite and offshore.

. EDUCATION PORTFOLIO

Bachelor of Computer Science Engineering in Anna University, Chennai, TamilNadu, India

TECHNICAL PORTFOLIO

SKILLS

Database

: Oracle 10g,Oracle 11g, Teradata 12, Teradata 14, Microsoft SQL Server, My SQL

ETL tools

: Informatica Powercenter 9.1, Informatica 8.6, Abinitio

Reporting Tools

Database Tools

: Cognos, Discoverant 4.0,SpotFire

: Teradata SQL Assistant, Oracle SQL Developer, Toad

Scripts

Languages

: Unix shell Scripting, Teradata loader (Fastload, Mload and BTEQ) scripts, Visual Basic.

: SQL, PL/SQL,XML, WSDL, Java

Operating Systems

Software Quality Assurance

ALM

: Windows XP, Windows 7, Windows 8, Unix

: HP Quality Center

: PTC Integrity

ERP system

: SAP

Other tools

Methodologies

: Informatica B2B Data Transformation studio,Putty, WinSCP, Core FTP, Rational Rose, Microsoft Visio, UML, Rally

: SDLC, Agile

CERTIFICATIONS

Informatica certified Developer- S - PowerCenter 8 Mapping Design

Informatica certified Developer- R- PowerCenter 8 Architecture and Administration

1Z0-051 Oracle Database 11g: SQL Fundamentals

FUNCTIONAL PORTFOLIO

Development Project: More than 6 years of experience on development projects predominantly based on Informatica Powercenter and UNIX shell scripts. I have worked in all the phases of development lifecycle starting from requirement gathering to deployment.

Databases: Experience of databases like Oracle10g, Oracle 11, Teradata 12, Teradata 14, SQL Server and Teradata data load utilities mainly on Teradata Parallel Transporter.

People Skills: Experience of working in diverse work environment. Worked extensively with customers for requirement gathering discussion, third party vendor communication, shared knowledge base maintenance and disaster recovery systems implementations. Communicate and manage offshore teams based in USA and India.

PROJECT PORTFOLIO

Project: Sligo QCCALC

Client: Abbvie – Waukegan, IL Jan 2016 – May 2016

The business wants to monitor the success rate of the parts in Humira pen manufacturing .The inspection/Quality control data recorded in the machine are collected in the sligo plant in the SQL server. As part of this project, a separate datamart was built to capture auto visual inspection/CMM machine data to report the QCCACLC testing data point for the business.

The project is built in Informatica 9 ETL tool and Teradata parallel transporter is used to load the data into the Teradata Database. UNIX shell scripts are written to configure the ftp, invoke the workflows and scheduled through Autosys. Reports have been created based on the integrated warehouse data using Spotfire reporting tool.

Work with the Business team to design, develop, test and implement reporting interfaces and ETL solutions.

Involved in designing datamart structure using star schema and assist in designing the logical and physical model of the datamart using Microsoft Visio.

Document the software design (high level and low level), design analysis document and source target mapping sheet (ETL) for the project.

Develop new ETL mappings/workflows to pull the data from the source SQL server to the Integrated Data warehouse.

Creation of new UNIX scripts for updating the job control and audit tables.

BTEQ scripts were created for performing specific functionalities to support the ETL.

Perform Unit testing for the ETL mapping using HP Quality center.

Create release code packages and Maintain source code version control using PTC Integrity client.

Set up new ODBC Informatica connections for the SQL server and the new Teradata databases.

Maintain the business requirement tracker to streamline the client requirements and project tracker to trace the progress of the project and to deliver the project on time.

Document the production migration documents to record database setup, SIQ and release migration notes.

Tool/Technologies: Teradata 14, Informatica 9.1 Powercenter, SpotFire, Unix, HP Quality Center, PTC Integrity client, Putty, WinSCP, Teradata SQL Assistant, Windows 8, Notepad ++

Project: MAI (Modified Auto Injector) Humira Pen

Client: Abbvie – Waukegan, IL May 2015 – Jan 2016

Owen Mumford – the third party vendor for Abbvie has introduced four new sortimant machines to manufacture the modified auto injector Humira pen and modified the existing machine SL controls to capture advanced sortimant data. The business utilizes the new sortimant machine assembly data in Discoverant for creating various reports and trends.

The project involves analysis of the existing process and enhancement of the integrated data warehouse for the newly modified humira pen. Owen Mumford exports the assembly machine data from the SQL server and sent to Abbvie as a file through the ftp to the Abbvie server. As part of this project, existing process need to be enhanced to pull the new line files and load into the OM datamart within the Integrated data warehouse. The functionality of the system providing the confirmation of the batches of the existing lines needs to be enhanced to provide the confirmation of the new line batches as well.

The project is built in Informatica 9 ETL tool and Teradata parallel transporter is used to load the data into the Teradata Database. UNIX shell scripts are written to configure the ftp, invoke the workflows and scheduled through Autosys. Reports have been created based on the integrated warehouse data using Discoverant reporting tool.

Analyze the existing process and evaluate design solutions objectively and facilitate conflict resolution to streamline with the existing processes.

Setup new ftp process to pull the new files containing the new line sortimat assembly machine data.

Preparation and documentation of software requirements and specifications related to the ETL solutions.

Develop ETL mappings using complex transformations, define the target load plan and mapping variables/parameter.

Creation of new UNIX scripts for updating the job control and audit tables.

Perform Unit testing for the ETL mapping using HP Quality center.

Maintain source code version control using PTC Integrity client.

Validate the parameters in the configuration document and sign off the data reports created through Discoverant reporting tool.

Maintain the business requirement tracker to streamline the client requirements and project tracker to trace the progress of the project and to deliver the project on time.

Manage offshore work allocation, review and monitoring to ensure delivery quality.

Tool/Technologies: Teradata 14, Informatica 9.1 Powercenter, Discoverant, Unix,SAP, HP Quality Center, PTC Integrity client, Putty, WinSCP, Teradata SQL Assistant, Windows 8, Notepad ++

Project: Sligo PlugnPivot

Client: Abbvie – Waukegan, IL Nov 2014 – Apr 2015

Abbvie opens expanded manufacturing facility in Sligo at Ballytivnan where the pen for the Humira drug has been manufactured. SL control team has set up the machine to record the events and duration of events occurred in each station in process of manufacturing the humira pen and the parts rejected in each station/counter. Also, captures the test data performed to the humira pen at various points.

This project build out to provide Sligo Humira pen manufacturing batch reporting with the details of assembly machine events and rejects in each station/counter. Also provide support for the process and force curve reports which depicts the testing performed to the Humira pen at various points. The datamart need to be built to pull the Kistler station xml files and the batch data files from Sligo SQL server and load the data into integrated data warehouse to derive reports for business.

The project involves configuration of FTP to extract the xml and data files from Sligo sql server and ETL to load the data into integrated data warehouse for reporting. Informatica Data transformation studio cleanses the unstructured data files and transforms to a comma separated file to process in Informatica ETL tool. Teradata is used as a Database. Batch jobs to process the data from upstream system are scheduled through Autosys. Reports have been built on the integrated warehouse data using Discoverant reporting tool.

Informatica B2B Data Transformation studio is used to parse the unstructured data files and convert into a XML file.

Parser created through Informatica B2B Data transformation studio is published as service and integrated with the Informatica Powercenter using Unstructured data transformation.

Document the parsing done through Informatica B2B Transformation studio.

Prepared the software design document and source to target mapping sheet for the project.

ETL Mapping created with Unstructured Data Transformation to invoke the parsing service and XML Parser Transformation being used to parse the XML and passes the data to the downstream transformation.

Big challenge with the project is to load the test data comprising of huge number of xml files without affecting the performance which is achieved by defining primary and partition by range index on teradata tables and employing several performance tuning techniques of multiple xml reader and session partitioning in Informatica.

Creation of new unix scripts to configure FTP, update the job control /audit tables and provide confirmation of the successful execution of the Informatica jobs.

Perform Unit testing for the ETL mapping using HP Quality center.

Maintain source code version control using PTC Integrity client.

Manage the Offshore and lead the team in deliverables.

Coordinate deployment of project related modules to Production.

Validate the parameters in the configuration document and sign off the data reports created through Discoverant reporting tool.

Proactively recorded the changes in project scope to keep track of the change in business requirements.

Tool/Technologies: Teradata 14, Informatica 9.1 Powercenter, Informatica Data Transformation Studio, SAP Discoverant, Unix, Microsoft SQL Server, HP Quality Center, PTC Integrity client, Toad, Putty, WinSCP, Teradata SQL Assistant, Windows 8, Notepad ++

Project: MDCT Vetter

Client: Abbvie – Waukegan, IL Nov 2014 – Feb 2015

Currently the Vetter application uses Manufacture Data Capture tool to capture only the generic injector humira drug information. Abbvie has made the drug available in different forms in various concentration formulas.Vetter application need to be enhanced to capture the manufacturing process of high concentration formula drug, generic drug and drug stored in vial.

The project is to enhance the functionality of the existing MDCT Vetter application to capture newly available drug in different concentration and forms and load the data into integrated data warehouse for reporting. The user provides the input through an MDCT application which uses Oracle as database at the backend. ETL is used to extract the data from Oracle database into integrated data warehouse built on Teradata.

Discussion with Business Analyst to gather the requirements for the Creation of HLD and LLD documents using Microsoft Visio.

Validate the parameters- new data entry fields and data types enhanced in the Vetter application using MDCT tool.

Analyze the source tables in Oracle and create stored procedure using PL/SQL to write the necessary data to a file.

Develop ETL mappings to create dynamic parameter file and implement business logic, mapplets, reusable transformation objects and workflows to group the sessions/tasks based on the staging/ODS layer.

Perform unit testing for the new mappings and record the test results in HP Quality center.

Coordinate with the team to move the code to Production.

Maintain the business requirement tracker and code tracker to trace the progress of the project.

Tool/Technologies: Teradata 14, oracle 11g, Informatica 9.1 Powercenter, Discoverant, Unix, HP Quality Center, PTC Integrity client, Putty, WinSCP, Teradata SQL Assistant, Windows 8, Microsoft Visio

Project: RDM Enhancements-Campaign and MyRxchoice Reporting Data

Client: Express Scripts Incorporation May 2013 – Oct 2014

Express Scripts has acquired Medco Corporation in 2012. As a result of this acquisition, MEDCO claim process need to be streamlined with the existing claim process for the smooth functioning of the business. Also, new vendor details need to be loaded into the reporting datamart in order to produce reports.

The primary objective of this project is to enhance the Reporting Data mart capability to support MyRxchoice (MRC) reporting and Trend Central reporting and merge the existing ETL process in Informatica to Abinitio. The business requires the Book of Business (BOB), WellPoint (WLP) and Department of Defense (DOD) client data available in the RDM database in order to produce reports in monthly/quarterly basis through business objects. The business also wishes to track the refill and renewal claims for one year after the initial conversion of the patient and also analyze relapses. RDM database is enhanced to accommodate all the MyRxchoice and Trend Central Reporting requirements.

The project involves the following phases - requirement gathering, design, development, unit testing, integration testing and also support of the features rolled out. The whole project is divided into multiple phases so as to do a staggered release.

Played a key role in the project and involved in all the phases of the project.

Perform impact analysis on the existing RDM process and provide solutions to effectively merge the new claims with existing claim process.

Develop graphs in Abinitio to pull necessary data from MEDCO datasource and load into Oracle tables.

Enhance the impacted ETL mappings in Informatica to load the new client data into Reporting Datamart built on Teradata.

Build summary tables using BTEQ script to produce monthly/quarterly/annual reports.

Develop wrapper script to integrate the multiple BTEQ scripts and ETL mappings to schedule in Tivoli.

Perform unit testing of graphs in Abinitio, mappings in Informatica and Unix shell scripts.

Coordinate with the reporting team and drawn unit test plan for reload of complete existing system.

Provide necessary design documents and operational manuals using Microsoft Visio.

Prepare Knowledge transfer plan and provide KT to facilitate new-entrants into the project and also preparation and maintenance of training materials.

Tool/Technologies: Informatica 9.1 Powercenter, Abinitio,Cognos, Teradata 12, Unix, Putty, WinSCP, Teradata SQL Assistant, Windows 7, Microsoft Visio

Project: VEN campaign Management

Client: Express Scripts Incorporation Dec 2012 – Apr 2013

The primary objective of this project is to determine the impacted members who currently filling a claim at tier-2 pharmacy or out of network pharmacies and send out an implementation letter with the closest 3 pharmacies that will be in their tier-1 network in the calculated 30-60 days prior to benefit go live. Implementation letter for the following 3 products in scope: VEN Exclusive, VEN preferred, Smart 90

The project is built on Informatica ETL tool and BTEQ script which in turn calls Teradata Macro to perform specific functionalities. Wrapper script is written to inline the mappings and BTEQ scripts created for the project and the script is scheduled in Tivoli scheduler.

Develop complex ETL mapping and Teradata Macro to accomplish the project requirement.

Web Services Consumer consumes external dynamic geometry web services using SOAP messages over HTTP protocol to determine the latitude and longitude of the pharmacy location.

Teradata Macro is written to determine the closest pharmacies based on the latitude and longitude of the current pharmacy of the members using geospatial SQL functions.

Develop ETL mappings with Web service consumer transformation and the SOAP response is parsed using XML Parser transformation and passed to the downstream transformations.

BTEQ script is invoked from the command task in the workflow to determine the closest pharmacies and email has been sent with the pharmacy location information in the calculated 30-60 days prior to benefit go live.

Created operational manual document for the support team explaining the steps to execute the project with failure and recovery options.

Perform unit testing and drawn test case documents.

Created release notes for the project to migrate to Production.

Tool/Technologies: Informatica 9.1 Powercenter, Teradata 12, Soap UI, WSDL, Unix, Putty, WinSCP, Teradata SQL Assistant, Oracle SQL developer, Windows 7, Microsoft Visio

Project: Bundle 13 - Rebates Client Share Upgrade

Client: Express Scripts Incorporation May 2012 – Dec 2012

This project is to build out system capability to provide rebate, sharing and reporting support to legacy ESI clients who have been upgraded to the Medco POS claim adjudication platform. The existing Legacy ESI Rebate External Claim Interface (RECI) DFO process has to be enhanced to rebate claims adjudicated by Medco POS. This process, also known as Drug Formulary Only (DFO) will receive the adjudicated / billed claims via an FTP process, rebate (invoice) them, process receipts and adjustments passing on the claim and financial data to downstream systems where they will be processed for payment and reporting to clients. In DFO processing Rebates is responsible for the content of the claim and acts as the data source for ADW and EDW. This will be true for this project except that the focus will be on providing a solution to rebate and rebate business processes. This project does not support providing Claim Data for Enterprise / Legacy ESI reporting on claims adjudicated by POS. This DFO process will continue until there is a consolidated platform to support the 2014 rebate strategy.

Create an understanding document of the existing Legacy ESI Rebate External Claim process.

Enhance the impacted ETL mappings to support Medco POS claims.

Teradata unix shell script is written to implement the business logic.

Perform unit testing of the impacted and new mappings developed for the project.

Provided support to QA team in UAT/System testing.

Record minutes of meeting with the onsite coordinator and send out an email on a daily basis.

Tool/Technologies: Informatica 9.1 Powercenter, Teradata 12, Unix, Putty, WinSCP, Teradata SQL Assistant, Windows 7, Microsoft Visio

Project: Prescriber Master Data Management

Client: Express Scripts Incorporation Jan 2012 – April 2012

Prescriber Master Data Management (MDM) project will provide a single source of truth for prescriber data with automated merge/match logic and tools for Data Stewards to support audit/review of automation, and manual review/remediation of work-queue master data decisions.

Involved in understanding of requirement specification and Design.

Develop Teradata Fast export scripts to export the prescriber data into comma delimited files.

Created ETL mappings/workflows in Informatica using the delimited files as source to implement the business logic.

Develop unit test case scripts for the new mappings.

Provide support to Functional testing team and prepared documents for implementation of the system.

Tool/Technologies: Informatica 8.6 Powercenter, Teradata 12, Oracle 10g, Unix, Putty, WinSCP, Teradata SQL Assistant, Oracle SQL developer, Windows 7, Microsoft Visio

Project: ExpresServ CSDW - Email & WR

Client: Express Scripts Incorporation April 2011 – Dec 2011

This project comprises of two phases.

Phase 1 – Email Disposition Data Collection Development of ETL code to capture changes in the APN outreach and Messege center data and load into new EDW tables on a daily basis for Adhoc reporting. Data for reporting must be available within one day following the original email sent.

Phase 2 – Member Preference Data from CED to EDW Development of ETL code to capture changes in the CED member preference data and load into new EDW tables on a daily basis. History data load for member preference data from CED to EDW.

Perform the role as ETL developer for the project.

Create Technical design document, ETL source to Target mapping sheet based on the business requirements.

Develop ETL mappings and workflows in Informatica Powercenter to implement the business logic.

Worked as Defect Prevention Coordinator and prepared a detailed root cause analysis at the end of each phase.

Tool/Technologies: Informatica 8.6 Powercenter, Teradata 12, Unix, Putty, Core FTP, Teradata SQL Assistant, Windows XP, Microsoft Visio

Project: Next Generation Express Alliance - CSDW

Client: Express Scripts Incorporation Oct 2010 – March 2011

The main purpose of the project is to develop ETL code to transfer ACES/Campaign Management data from the EDW (Teradata) to the C&PS Opportunity data store in support of the new EXA application and backend reporting. EXA ETL transfers newly generated, active ACES/Campaign Management data from the EDW (Teradata) to the EXA Opportunity data store on a nightly basis.

Analyze technical requirements of the project and gather source system information.

Develop ETL mappings and workflows to implement the business logic based on souce target mapping sheet.

Prepare technical documents and unit test cases for the project.

Tool/Technologies: Informatica 8.6 Powercenter, Cognos,Teradata 12, Unix, Putty, Core FTP, Teradata SQL Assistant, Windows XP, Microsoft Visio

Project: NCCI

Client: Express Scripts Incorporation March 2010 – Sep 2010

This project is offload all the applications from the MVS mainframe system and place that functionality on other more open platform systems and database technologies, while streamlining the data flows, batch processes, and reporting that come from those systems.

Understand the requirements (both functional and non-functional) by going through the specifications and develop a code based on the requirements.

Document artifacts including unit test plans and ensure that the output is as per the given specifications.

Tool/Technologies: Informatica 8.6 Powercenter,Cognos, Teradata 12, Unix, Putty, Core FTP, Teradata SQL Assistant, Windows XP

Contact this candidate