We provide IT Staff Augmentation Services!

Etl Designer/developer Resume

SUMMARY

  • 10+ years of experience in complete software life cycle – requirements gathering, analysis, design, development, testing and production support of Database and Data Warehousing applications
  • Experience writing functional specification, high/low level design and phase gate project documents
  • Extensive experience in both OLTP and OLAP data modeling
  • Proficient in data warehousing principles – star and snowflake schemas, relational and dimensional modeling, data mart design and implementation.
  • 10+ years of experience in implementing ETL and reporting applications using various tools – Informatica, Oracle PL/SQL, ETI, Cognos, Business Objects, etc.
  • 10+ years of experience in design and development of very large database applications on Oracle
  • Extensive experience in Oracle PL/SQL – using advanced features like partitioning, indexing, performance tuning, collections and records, analytical functions, etc
  • Very strong skills in designing and developing Oracle stored procedures, packages, functions, and triggers, materialized views etc.
  • Expertise skills working on OLTP systems and highly performance oriented programming.
  • Experience with data mapping/manipulation/conversion/replication using Oracle
  • Strong performance tuning skills using EXPLAIN PLAN, optimizer hints, SQL trace, TKPROF etc.
  • Expertise using Oracle utilities (eg: SQL LOADER, export ,import, DBMS_JOB, etc)
  • 10+ years experience in Unix shell scripts and Perl programming
  • Good analytical and problem solving skills with exceptional communication skills

SKILLS

Software Skills: Informatica Power Center 8.x, 7.x, IBM Cognos, Business Objects 5.0, and Oracle 8i, 9i, 10G, DB2, SQL Server, ETI
RDMS: Oracle 10G, 9i, 8i, SQL Server, DB2
Programming: ANSI SQL, PL/SQL, ProC, Unix shell scripting, windows batch scripting, Perl, C, C++, OpenGL
OS: Unix (Solaris 2.6.1, HP-UX, HP Superdome), Windows NT/XP, Linux, and MS-DOS
Other Software: Autosys, MS-Office, SVN Tortoise, HP Quality Center, JIRA, ClearCase, Clear Quest, Documentum, Excel, Access, Exceed, Visio, CVS, and RCS

EDUCATION:

  • B.Tech in Mechanical Engineering

CERTIFICATIONS:

  • Oracle certified Associate DBA

EXPERIENCE

Confidential, West Point PA 09/01/02 - Present
Project 1: Managed Care Claims DSS
Role: ETL Lead/Designer

Objective:
The Managed Care Claims DSS is a decision support system used by Contract Analysts to analyze the market share trends of the products within the PBM contracts over time, geography, Channel, and other dimensions.

Responsibilities:

  • Perform requirement analysis , mapping Business requirements to Functional specifications
  • Proactively identified issues and coordinated with different (business users, DBA, other interfacing application) teams for proposed resolutions/initiatives.
  • Involved in phase gate (requirement/data model/Design/Coding/Testing) reviews
  • Responsible for mapping Functional requirements to Technical requirements.
  • Responsible for logical and physical data model suitable for reporting applications with large data volume
  • Responsible for creating high level design document and transferring knowledge to the development team.
  • Used partitioning and sub-partitioning methods in Oracle to improve the efficiency of loading and reporting the data. Used local partitioned indexes to enable parallel load of data into various partitions/sub-partitions.
  • Design ETL procedures to efficiently process large volumes of data.
  • Design and implement complex business rules using various complex transformations, Oracle packages, stored procedures, function, triggers, analytical functions, pl/sql collections, etc
  • Led a team of developers to implement code for ETL
  • Review low level design and code during development phase
  • Review Test Plan/Cases with the testing team
  • Tune the ETL processes – Informatica workflows, Oracle SQL, Oracle PL/SQL procedures to improve the efficiency of the load process.
  • Worked on the Cognos framework manager to import the model into the BI tool
  • Tune the queries used by the Cognos reports to improve the response time.
  • Auotmated the entire load process using Unix shell scripting.
  • Ensured proper versioning is maintained for the code developed using Tortoise SVN
  • QA/UAT Support – Acted as a point of contact from the development team. Support development integration, quality analysts, user team for IT, QA and UAT testing. Ensure the defects are tracked in JIRA/Quality Center, analyzed, prioritized and closed.

Environment: Informatica PowerCenter 8.x, Oracle 10G, PL/SQL, Cognos 8, HP SuperDome, XP, MS Access, Perl5, UEdit, SVN Tortoise, JIRA, HP Quality Center, Erwin, MS Visio

Project 2: Inform Integration
Role: ETL Lead/Designer

Objective:
The source system of formulary data is changed from an internal system to an external vendor files. The objective of this project is to create a master data for formulary information for the organization.

Responsibilities:

  • Responsible for identifying the data requirements for the down stream systems and integrating between the different application needs
  • Responsible for performing GAP analysis – identifying the differences between the data in the new source and old source.
  • Responsible for analyzing business requirements and mapping to Functional specifications.
  • Proactively identified issues and coordinated with different (business users, DBA, other interfacing application) teams for proposed resolutions/initiatives.
  • Involved in phase gate (requirement/data model/Design/Coding/Testing) reviews
  • Responsible for logical and physical data model
  • Responsible for creating high level design document and transferring knowledge to the development team.
  • Developed ETL process using SQLLDR, PL/SQL collections and records, analytical functions to load and transform the data
  • Designed and developed an automated process that loads and transforms the input data using Unix scripts and crontab.
  • Designed the system to handle the specified archiving and reporting requirements.
  • Used table and local index partitioning in Oracle to improve the efficiency of loading and accessing the data.
  • Extensive usage of utilities like SQL Loader, data import/export.
  • Review low level design documents and the developed code
  • Review Test Plan/Cases with the testing team
  • Ensured proper versioning is maintained for the code developed using Tortoise SVN
  • QA/UAT Support – Acted as a point of contact from the development team. Support development integration, quality analysts, user team for IT, QA and UAT testing. Ensure the defects are tracked in JIRA/Quality Center, analyzed, prioritized and closed.

Environment: Informatica PowerCenter 8.x, Oracle 10G, PL/SQL, HP SuperDome, XP, MS Access, UEdit, SVN Tortoise, JIRA, HP Quality Center, Erwin, MS Visio

Project 3: Field Sales Analysis Report (FSAR)
Role: Technical Lead/Designer

Objective: The Field Sales Analysis report is used by Sales and Marketing team to analyze the field activity. The system helps analyze the various kinds of field activity of the sales representatives and understand the impact caused.

Responsibilities:

  • Work as a lead designer for the application.
  • Responsible for creating functional specifications from the business requirements and mapping to technical requirements
  • Create the data model for the reporting system – dimensions and facts
  • Create high level design of the entire process.
  • Work on designing processes for extracting, transforming, and loading data into Merlin BI data mart.
  • Lead a team of developers to develop the process.
  • Work with the BI team on creating reports to deliver the required data to the users.

Environment: Informatica PowerCenter 8.x, Oracle 10G, PL/SQL, MicroStrategy, HP-UX 11i, XP, MS Access, Perl5, UEdit, Autosys, SVN Tortoise, JIRA, HP Quality Center

Project 4: Targeted Physician Report (TPR)
Role: ETL Lead/Designer

Objective: The objective of this project is to provide a report for the business to determine bonuses to the sales representatives. Market share and share change for the products over various dimensions like Physician Targets, geography and time periods are calculated. The sources for this system are flat files from IMS and Oracle database.

Responsibilities:

  • Perform requirement analysis and map the business requirements to functional specifications
  • Map the functional specifications to Technical specifications.
  • Design data model to store various types of data for reporting purposes
  • Design and develop the ETL process to efficiently process large sets of data
  • Document the design process and perform knowledge transfer to the development team
  • Use Informatica mappings and oracle stored procedures to efficiently load the data and calculate the required metrics.
  • Use Rational ClearCase and ClearQuest for revision control and defect tracking respectively.

Environment: Informatica PowerCenter 7.x, Oracle 9.2, 8.1.7, PL/SQL, Microstrategy, HP-UX 11i, PL/SQL, Windows NT, XP, MS Access, Perl5, Business Objects 5.0, WinSql, Exceed, UEdit, Autosys, Rational ClearCase, Rational Clear Quest

Project 5: Government Reporting DSS
Role: ETL Lead/Technical Analyst

Objective:
The Government Reporting DSS is a decision support system used by Pricing Analysts to monitor and analyze the various prices, their changes over time.

Responsibilities:

  • Responsible for modeling/designing database for reporting data from government pricing and rebate systems
  • Responsible for developing extract, transform, load process to get data from various sources and load into the DSS system
  • Modifying existing product price system to include new data

Environment: Informatica PowerCenter 7.x, Oracle 9.2, 8.1.7, PL/SQL, Microstrategy, HP-UX 11i, PL/SQL, Windows NT, XP, MS Access, Perl5, Business Objects 5.0, WinSql, Exceed, UEdit, Autosys, Rational ClearCase, Rational Clear Quest

Project 5: MyCall Analytics
Role: Designer/Developer

Objective:
This system is used to analyze the MyCall system utilization by the field representatives while talking to the physicians. It provides metrics over the usage of MyCall system, the various screens in calls made by field representatives.

Responsibilities:

  • Responsible for designing new processes to bring in new data into MyCall Analytics system
  • Tune the process to improve the efficiency of the load process.
  • Worked with Cognos Transformer to create cubes for reporting by company

Environment: Informatica PowerCenter 7.x, 8.x, Oracle 10, 9.2, 8.1.7, PL/SQL, IBM Cognos 8, Cognos Transformer, HP Superdome, HP-UX 11i, PL/SQL, Windows NT, XP, MS Access, Perl5, WinSql, Exceed, UEdit, Autosys, Rational ClearCase, Rational Clear Quest

Project 6: Managed Care government pricing system
Role: ETL Designer/Developer

The objective of this project is to load data into I-Many software used for Government pricing.

Responsibilities:

  • Work on the complete system life cycle for a couple of modules in the project: develop the SLC document, design, develop, and unit & integration test
  • Provide assistance and guidance to the other developers in implementation procedures
  • Develop modules for error handling.
  • Perform defect tracking and revision control using Rational ClearCase and ClearQuest.
  • Develop shell scripts to automate the process
  • Use Autosys to schedule the jobs

Environment: Informatica PowerCenter 7.x, Oracle 9.2, 8.1.7, PL/SQL, HP-UX 11i, PL/SQL, Perl5, Autosys, Rational ClearCase, Rational Clear Quest

Project 7: PDM Weekly Rx
Role: ETL/BI Designer/Developer

Objective:
The objective of this project is to load the PDM script data on a weekly basis. The data is used to analyze the prescribing trends of physicians across the nation over time for various products.

Responsibilities:

  • Design the complete system based on the business requirements
  • Load large data files in a timely fashion
  • Create parallel load jobs to process the input data as soon as possible.
  • Design and develop ETL process using Informatica.
  • Develop Unix shell scripts to automate the process.
  • Develop scripts to build and drop indexes, truncate were developed.
  • Work with Oracle database partitions and sub-partitions to effectively manage large data
  • Design of the PDM Weekly Business Objects Universe.
  • Design and develop PDM QC and PDM weekly reports using Business Objects.
  • Work on optimizing the SQL queries used in the Business Objects Universe

Project 8: Medco Data Pull Through (Gencord CM)
Role: ETL Designer/Developer

Objective:
The objective of this project is to develop an automated process that generates data for Merck Contract Management. Medco sends data over to Merck Server and this data is used as an input to the process. The output of the process is various rollups of the input data for analysis.

Responsibilities:

  • Design and implement Medco Data Pull through process.
  • Work with large input files
  • Implement complex business logic in the ETL process.
  • Use Informatica to extract, transform, and load the data as per requirements.

Environment: Informatica PowerCenter 6.2, Oracle 9.2, 8.1.7, PL/SQL, HP-UX 11i, PL/SQL, Windows NT, XP, MS Access, Perl5, Business Objects 5.0, WinSql, Exceed, UEdit, Autosys, Rational ClearCase, Rational Clear Quest

Project 9: Merlin HQ Sales
Role: ETL Designer/Developer

Objective:
The objective of this project is to re-write the Mainframe MHQ Sales process to an automated Informatica process.

Responsibilities:

  • Gather the business requirements and design a new efficient system using Informatica
  • Use the Informatica partitioning to parallelize the process.
  • Develop various unix shell scripts for this purpose and for the automation.
  • Automate the process using crontab.
  • Perform testing of the new Informatica process.
  • Document the process for the Operations group.

Project 9: Quest Sales
Role: ETL Designer/Developer

Objective:
The objective of this project is to migrate the process from Mainframe to UNIX and automate. The process creates files for every Sales representative with relevant product Sales information.

Responsibilities:

  • Use informatica to extract Sales data from Oracle/DB2 databases.
  • Use Perl script to build the output file for each of the Quest processes.
  • Develop an automated shell script to ftp the output files from the Informatica UNIX box to the Data Builder (NT machine).

Project 10: Customer and Product Adherence reports
Role: ETL Designer/Developer

Objective:
The objective of this project is to compare how well the Sales representatives called the physicians as per the plan they were given. The report compares the Planned and Actual field activity for various customers and products.

Responsibilities:

  • Use informatic and Oracle to design ETL process to read the input files and produce the desired output.
  • Use Perl to format the data in the required layout.
  • Automate the process to ftp the output files to the Data builder machine.

Environment: Informatica PowerCenter 5.1.2, 6.2, Oracle 9.2, 8.1.7, PL/SQL, HP-UX 11i, PL/SQL, Windows NT, XP, MS Access, Perl5, Business Objects 5.0, WinSql, Exceed, UEdit.

Confidential, Dec ‘01 – Aug ‘02
Role: Senior ETL Developer

This is a huge Datawarehousing implementation involving ETL Informatica, Oracle 8i, HP-UX, MS SQL Server 2000 and Cognos.

ETL Responsibilities
  • Used MS SQL Server 2000, Oracle, ASCII files and legacy data files as sources and Oracle as target.
  • Designed and developed Informatica mappings for data loads and data cleansing. Extensively used almost all of the transformations of Informatica including complex lookups, Stored Procedures, Update Strategy and others.
  • Understanding and converting the financial hierarchy for Fact and Dimensions fields for ETL team to create the mapping.
  • Tuning the Informatica Server and Mappings for optimum performance.
  • Created process flow for Production environment.
BI Responsibilities
  • Requirements Gathering and Reports Design
  • Designed the BI reports using Cognos
  • Created Cubes using Cognos for pulling the Annual Buckets data for online reporting and Analysis.

Environment: Informatica Power Center 5.1 on HP-UX, Oracle 8.i, PL/SQL, Cognos Impromptu and Power play Transformer 6.6, Windows NT 4.0

Confidential, Dallas Sept’ 00 – Oct’ 01
Role: Senior Developer

This Datawarehouse provides billing history to its customers. The database is maintained in Oracle 8.0 on HP-UX 10.20. This data mart is built to do vendor performance analysis, to Monitor Receipts, Track & Record Deliveries.

ETL Responsibilities
  • Worked on Informatica tools – Source Analyzer, Data warehousing designer, Mapping Designer & Mapplet, and Transformations. Using Informatica Designer designed and developed Target Warehouse Entity for Oracle
  • Optimized Query Performance, Session Performance.
  • Extensively involved in creating database procedures, functions and triggers using PL/SQL.
  • Worked on Data Extraction, Data Transformations, Data Loading, Data Conversions and Data Analysis
Business Objects Responsibilities
  • Involved in Analysis of Business Requirements and data analysis for the Billing Platform
  • Involved in designing universes in Business Objects for generating reports. Worked with Business Objects Developer Suite (Business Objects, Designer)
Environment: Informatica PowerCenter 1.75 (Source Analyzer, Data warehousing designer, Mapping Designer, Mapplet, Transformations), Business Objects 5.0 (Business Objects, WebIntelligence, InfoView, Designer), Oracle 8i, HP-UX, SQL, PL/SQL, Windows NT 4.0

Confidential, Burlington, MA Jan’ 00 – July’ 00
Role: ETL/BI Developer

Sun Microsystems has an Operational System designed to provide enterprise information spanning the entire organization. The Data Warehouse was scheduled to get data from different databases of operational system, which then is loaded into staging area using Informatica. Business Objects was used to analyze the data and to generate reports

Environment: Informatica PowerMart (Source Analyzer, Data warehousing designer, Mapping Designer, Mapplet, Transformation developer), Business Objects, Oracle 8i, Sun Solaris 2.7

Personal Traits:

  • Experienced in environments requiring direct customer/business user interaction during all phases of SDLC
  • Self-starter, fast learner and a team player with strong personal and group communication skills and good trouble-shooting capabilities.
  • Very strong analytical and problem solving skills
  • Good Documentation Capability
  • Strong alignment to business goals

Hire Now