JOHN A. ABRAMS

 

 

 

PROFILE

Senior Project Manager / Application Architect / Database Administrator

Primary focus on application design, application architecture, and database administration. Extensive experience as a developer and data modeler, with a concentration in RDBMS based applications. ORACLE DBA certification and expertise with logical and physical DBA activities with Oracle and SQL Server. Proven analysis, design and programming skills utilizing SQL, Perl, PL/SQL and TSQL.

 

Consulting concentration is Project Manager coordinating resources and architecting logical and physical application design. Solid technical and leadership experience includes: database and server side project management responsibilities, user requirement analysis, application design, physical database installation; configuration and daily administration; database logical and physical architecture analysis, design and deployment; analysis, design and development of data extraction, transformation and loading to support large and small multi-data source warehouses; performance tuning for the database, interactive data access from web-based and thick client applications and batch data load processing. Industry specific pharmaceutical sales and marketing, contracting, retail data analysis and large construction experience.

 

SYNOPSIS OF EXPERIENCE

 

Aspect Consulting, Inc. www.aspect-consulting.com 7/94 - present

President and Principal Consultant

Clients/Projects/Consulting Assignments:

 

Oracle Database Administration

(Nycomed, Alpharma, Actavis, Teleflex, Wockardt)

 

On going support multiple clients as a remote Oracle and SQL server DBA. Perform all duties such as installations, upgrades and day to day DBA activities. Routinely perform short term databse installation, configuration and tuning assignments.

 

Nycomed

 

Analyzed, designed and implemented the data interfaces to and from SAP. The following application interfaces were implemented: CarsIS and a sales reporting data warehouse.

 

Centocor.

Assignment was as the Data Architect / Project Manager for a team to implement multiple enhancements for the Ideal ECP application. This application performs the validation of electronic claims data for the Pharmaceutical industry. I successfully architected, managed and implemented the many enhancements to the ECP application providing additional enhancements beyond the scope of the initial project and still delivered the project 10% under budget.

 

Merck.

Assignment was as the Data Architect to implement multiple enhancements and solve the challenge of performance tuning the Ideal ECP application. This application performs the validation of electronic claims data for the Pharmaceutical industry and had major performance problems that caused large data submissions to not complete. I successfully implemented many validations changes and reduced the total processing time and enabled the application to process submissions over 18 million in size.

 

Altana Inc.

Assignment was as the Lead Application Architect to design and develop a custom application to manage the workflow of contract price addendums between the sales field and contracts group. This custom application provides representatives with a streamlined method of initiating contract addendums, while giving management a comprehensive tool to analyze, monitor, and guarantee the proper processing of the request within set policies. This client has a dramatically improved way to manage the many concurrent tasks associated with requesting and approving contract addendums. One tool automates several processes, decreases manual entry error, and centralizes requests. The application can be accessed by both remote and LAN users. The built-in approval hierarchy and audit trail of authorizations make the application fully SOX compliant.

 

Novartis.

Assignment was as the Data Architect / Team Lead to implement multiple enhancements and to solve the challenge of performance tuning the Ideal ECP application. This application performs the validation of electronic claims data for the Pharmaceutical industry and had major performance problems that caused large data submissions to run for multiple days. I successfully implemented many validations changes and reduced the total processing time from days to hours.

 

Astra-Zeneca.

Assignment was to solve the challenge of performance tuning the Ideal ECP application. This application performs the validation of electronic claims data for the Pharmaceutical industry and had major performance problems that caused large data submissions to run for 20+ hours. Within a week of my initial analysis, I successfully reduced the Phase I validation step to return in less than 50% of the time.

 

Altana Inc.

Assignment was to solve the challenge of an inefficient and error prone contract membership management. This pharmaceutical company manually managed over 150,000 members across more than 300 contracts in CarsIS. While utilizing the CarsIS Membership Exchange module, there were many performance, validation and quality issues. To solve, I designed, developed and implemented an efficient web-based application that feeds into CarsIS, and processes the contract membership lists. This custom member management tool supports multiple spreadsheet layouts, performs data standardization, DEA validation against the monthly purchased NTIS (US Department of Commerce National Technical Information Science) DEA list, while it enables detailed exception reporting and automated membership updates in CarsIS. This tool improved efficiency, data validation, and error reduction. There was a significant reduction in manual effort reducing what used to take 3 days to a few hours. The membership error on chargebacks was cut in half, realizing a savings of millions of dollars each month.

 

Also with this client, installed the RDBMS server and client software, created development databases. Developed, implemented, and tested the routine maintenance and monitoring processes associated with the RDBMS software. Developed, implemented, and tested a backup and recovery process and remote monitoring processes for the RDBMS data and software. Provide ongoing database administration support for the RDBMS. Daily and weekly monitoring of the database including space usage management, and performance and backup monitoring. Aspect Consulting remote servers periodically attempt connection to the Oracle databases and report any problems. Assisted the CARS/IS and CARS/Medicaid implementation staff with the installation of CARS/IS and CARS/Medicaid applications software. Provide day-to-day support for the Imany CarsIS and Analytics MicroStrategy products. Designed and developed additional ad-hoc query capabilities to the CarsIS data by creating a custom extract into an operation data store for efficient data analysis of the Charge-back, Rebate, direct and indirect sales data.

 

American Infrastructure.

Assignment was to solve the challenge of inconsistent task assignment and poor on-time completion rate analysis. With approximately 100 construction projects to manage concurrently, this large construction company needed to develop a standard operating procedure for assigning tasks, and to increase the rate of on-time completion for those tasks assigned, as well as the overall jobs associated with these tasks. The solution was a project management application with reporting and analysis capabilities. This custom application provided project managers with the ability to assign tasks to specific foremen for every project. Detailed reporting capabilities include daily and weekly task reports by project, project manager and foreman; multi-level completion percentage analysis; real time snapshots of open and completed tasks associated with every job; and the ability to create and choose templates of standard tasks for major job types. This progressive operating process and information system provided this client with a dramatically improved way to manage the many concurrent tasks associated with active projects. The data gathered in the application also provided a calculated percent completion factor to be used for rating, auditing, and analyzing types of projects, jobs, project managers and foremen.

 

Completed the analysis, design, development and implementation of a SQL Server 2000 data warehouse to perform multi-level cost versus budget and productivity analysis. This project included automatic nightly data interfaces from IBM DB2 and a manual browser based data maintenance application and browser delivered complex business intelligence reports created in Html and Excel.

 

Pfizer.

 

Assignment was to solve the challenge of error prone, manual price group reassignment in CarsIS. This pharmaceutical client had to manually tabulate hundreds of contracting organization-supplied Excel workbooks monthly. These workbooks contained tens of thousands of records comprised of utilization data to determine each contracting member�s product market share or percent attainment of baseline purchases. To solve, developed a standard operating procedure and custom .Net application for detaching the customer-supplied spreadsheets, storing them on the LAN, and automatically loading the data into an Oracle database. The data was then validated (DEA and NDC) against the contract membership and product definitions in CarsIS. Market share and percent attainment of baseline purchases were automatically calculated. Detailed, multi-tabbed Excel workbooks were generated and emailed to over 3,000 contract owners within this program. The entire process for determining price groups within CarsIS was reduced from weeks of manual effort to merely a few days. The manual process of moving members in CarsIS was replaced by an automated update in CarsIS. There has been a major reduction in errors and an increase in the quality of the data processed. For example, incorrect DEA�s and invalid NDC�s that were previously included in contracts have been virtually eliminated from the calculations, representing a significant savings for this client.

 

Endo Pharmaceutical.

Completed the analysis, design, development and implementation of a sales force compensation application and IMS data repository. This application managed sales force zip to territory, Universe and manual transferred physician alignments. The implementation of this application involved the loading and normalization of IMS Xponent data into a SQL Sever 2000 database. Responsible for the physical database administration associated with the SQL Server 2000 database.

 

Harte-Hanks.

Assisted with analysis, design, development and implementation for the conversion of a custom CRM IIS ASP application from SQL Server 7.0 to Oracle DBMS. Responsible for interactive, batch and web performance tuning, PL/SQL package and data interface analysis, design, development and implementation.

 

Faulding Inc - Alpharma.

Designed, developed and implemented a data warehouse to support contract sales, rebate and best price analysis. This included the logical and physical design of the database and the development and implementation of daily ETL processes. Performed the analysis, detail design and project management for a thin client web reporting application used to securely distribute contracting and sales data to corporate users. Installed and configured the Oracle RDBMS server and client software and created the databases to support the CARS/IS application. Developed, implemented, and tested the routine maintenance processes associated with the Oracle RDBMS software. Developed, implemented, and tested a backup and recovery process for the Oracle RDBMS data and software. Assisted the CARS/IS and CARS/Medicaid implementation staff with the installation of CARS/IS and CARS/Medicaid applications software. Re-engineered and completed coding modifications to the AR in, AR Out, product and wholesaler interfaces to support multiple companies after a merger. Provide continued Database Administration Support for the Oracle RDBMS. Daily and weekly monitoring of the database including space usage management, and performance monitoring.

 

WebLinc.

Completed physical and logical database tuning to support a Cold Fusion based high usage web site. Re-designed and re-developed PL/SQL functions and stored procedures to eliminate data lock contention. Improved web site query performance by re-designing the physical index structures and query DML.

 

Siebel Systems, Inc.

Completed the production implementation and automation of data interfaces and data maintenance utilities to support an in-house client.

 

Triversity CRM Solutions.

Completed the conversion of a custom DSS application from SQL Server and Redbrick to Oracle. The physical database configuration is Oracle RDBMS versions 8.1.6 (8i) running on NT utilizing database partitioning for performance and data truncation. The design and development involved PL/SQL stored procedures, functions and platform independent Perl and Java modules. Daily administration support of existing client installations and project work required complete knowledge of SQLServer 6.5/7.0/2K and Redbrick 5.1.6 RDBMS and RDBMS tuning for NT and DEC/Compaq Unix. Designed and developed a Business Intelligence data mining data warehouse to support retail data analysis. The physical database configuration is SQLServer 7.0 running on NT. Performed the database administration for a proof of concept data mining project utilizing IBM DB2 RDBMS and data mining software running on NT. Completed a proof of concept using Microsoft SQL Sever OLAP Services building a small retail data cube and evaluating MDX query capabilities.

 

Centocor, Inc.

Completed the design, development and implementation of a multiple data source sales and marketing data warehouse. Data included IMS, EDI, SFA call data and contracts management contract and membership affiliations. Completed the data management and interface processes to configure and maintain a two sales force implementation of the Siebel sales force automation (SFA) application. Completed Oracle RDBMS and application migrations from version 7.3.4 to 8.0.5. Designed the logical and physical database structures to support an implementation of the Brio Query tool. Completed the Oracle RDBMS and batch configuration of for the installation of the Cars contracts and rebates application. Assisted the CARS/IS and CARS/Medicaid implementation staff with the installation of CARS/IS and CARS/Medicaid applications software.

 

Rhone-Poulenc Rorer.

Performed application and Oracle RDBMS tuning analysis for an 800+ Gig Sales and Marketing data warehouse. Responsible for the coordination of interface process and Oracle Sales Analyzer application modifications to support a sales force and product dimension realignment.

 

Merck & Co. , Inc.

As part of an OLAP tool proof of concept, configured and implemented Oracle Sales Analyzer 1.5 and designed the logical and physical Express multidimensional database to support the tool.

 

Stores Automated Systems, Inc.

Performed physical DBA activities for SQL Server and Redbrick RDBMS environments. Developed and implemented database synchronization process between SQL Server and Redbrick databases.

 

Wyeth-Ayerst Labs.

Designed the logical and physical Oracle database, data load processes and tool interfaces for a mid-sized data warehouse to support an ad hoc relational OLAP tool and custom GUI application. The application was developed using Oracle Express Server, Express Objects and Power Builder. The application assisted in the resolution of Medicaid claim process discrepancies.

 

Du Pont Merck Company.

Responsible for the migration and upgrade of several Oracle RDBMS application environments from VAX VMS to HP UX servers. This migration included the relational database, supporting application code and coordination with the application development and user teams to perform validation.

 

Computers for the Professional, Ltd (CPL) & Century 21 Real Estate Corporation.

Provided CPL with design modifications to assist the Web page development team with database design and Web server interface issues. Completed the development of the load process for the Oracle RDBMS component of an Oracle Web Server 2.0 application.

 

Educators Mutual Life.

Assisted in the design of the logical and physical databases to support proposal, ratings and certificate applications using the Oracle RDBMS and Delphi GUI tools. Performed RDBMS tuning and development of stored procedures.

 

Pasteur Mèrieux Connaught.

Configured and implemented Oracle Sales Analyzer 1.5 and designed the logical and physical Oracle database to support the tool.

 

Centocor, Inc.

Completed the upgrade of Oracle Sales Analyzer version 1.1 to 1.5. In addition to the upgrade, design changes were made to the load and multidimensional build processes to support additional levels of data.

 

Astra Merck.

Member of a DBA group responsible for the upgrade, tuning and support of a diversified and dynamic Oracle client/server environment using TCP/IP SQL*Net and ODBC API interfaces and RDBMS versions ranging from 6.X to 7.3.2. Provide ad hoc support and logical / physical database design and implementation for application development project teams. Designed and implemented the logical and physical database of a large sales and marketing data warehouse to support an OLAP/DSS application. This warehouse contained IMS, contracting and SFA data. As part of the tool selection phase, completed the configuration and implementation of a proof of concept environment using the Micro Strategies DSS Agent Multi-Relational product and the Oracle Sales Analyzer products. The application was developed using Oracle Express Server, Express Objects and Oracle Sales Analyzer. The system incorporated external sales, chargeback and rebate data for analysis.

 

The Du Pont Merck Company.

Primary Oracle and Rdb DBA and secondary Ingres DBA. Performed migrations of Oracle databases from V6.036 and V7.016 in exclusive mode to V7.1.3 parallel mode on VAX�s running OpenVMS 5.5-2. Provided support for multiple Rdb databases and an Oracle client/server environment using the TCP/IP SQL*Net API interface; also provided DBA and application support for several Ingres applications and databases. As part of a project team, designed and developed a world wide document management system. Responsible for the system design and data modeling using the ERwin product as well as assisting in the application development using SQL*Forms30 and SQLAssist.

 

Centocor, Inc.

Completed a migration of an Oracle V6.037.3 parallel served VAX OpenVMS 5.5-2 (VAX 4100 & 4600) validated production environment to a V7016 client/server Alpha AXP OpenVMS 6.1 configuration. The final configuration was an OpenVMS 6.1 cluster with the RDBMS running exclusively on an AXP 2100 server with 2 VAX's (4100 & 4600) running as Clintrial clients. This configuration used the DECnet/SQL*Net API interface. Designed and developed SQL*Forms30 and SQR reports for a Spontaneous Adverse Event system. Performed a migration of the SAS development environment from a VAX 9210 OpenVMS 5.5-2 SAS 6.07 to an Alpha AXP 2100 OpenVMS 6.1 SAS 6.09. The migration involved the modification of DCL command procedures, conversion of SAS datasets and the movement of SAS programs.

 

Integrated Systems Consulting Group - Wayne, PA 12/90 - 7/94

Consultant

 

Contributed to the design, development, and instruction of a 30 hour SQL*Forms class for Consultants. Developed the agenda for and chaired a 40+ member ORACLE special interest group. Performed the DBA duties for both version six and seven ORACLE databases. Completed an upgrade of ORACLE to v7.0.13 RDBMS while performing DBA duties in this environment.

 

Consulting Assignments:

Centocor, Inc.

Responsible for the database administration of all ORACLE-based applications. DBA responsibilities included security, maintenance, software upgrades, and tuning of the ORACLE database environments. Completed the migration of the production ORACLE database for Clintrial, SQL*Forms(2.3-3.0) and SQL*Textretrieval applications from a VAX 6610 to a VAX 4600. Performed the installation of the v6.0.37.3 RDBMS and the complete ORACLE tool set. Created and populated ORACLE database instances running in parallel server mode across a VAX cluster in a v6.0.37.3 production environment. Responsible for the re-design and implementation of a new change control system to manage the Clinical Data Management SAS programs and data.

 

American Cyanamid Company.

Solely responsible for the re-design of an ORACLE-based Global Pesticide Tracking System. The system tracked registrations and custom label information for domestic and international agricultural products. Completed a re-design of the database scheme, screens and reports. The system was developed utilizing SQL*Forms, SQL*Menu and SQR for reporting in a VAX/VMS environment. Responsible for all enhancements to the completed system based on user specification. Involved in the user meetings to further define system requests.

 

Roy F. Weston, Inc.

Participated in the development and implementation of a customized Project Control System for a major environmental engineering consulting company. The system enhanced control over the management of projects resulting in increased profitability. Responsibilities included writing conceptual and detail design specifications, and developing modules using the ORACLE RDBMS family of products including SQL*Forms, SQL*Menu, SQL*Loader, SQL*Plus, and Pro*COBOL. All development was completed in a VAX/VMS environment. Performed all unit and integrated systems testing and documentation. Provided support and maintenance to the user community.

 

Centocor, Inc.

Upgraded and enhanced manufacturing system software utilizing MANTIS, SPECTRA and COBOL languages. The system also used DCL command procedures, and personal computer software packages.

 

Unisys - Blue Bell, PA 3/89 - 12/90

Senior Analyst

 

Responsible for the development and support of International Pricing Systems. Maintained and enhanced the sales and marketing systems and provided user support. Involved in all stages of the software development life cycle. The systems were developed using COBOL and MAPPER.

 

Shared Medical Systems - Malvern, PA 1/87 - 3/89

Programmer Analyst

 

Created design specifications, developed interfaces, and provided technical support for hospital information systems using COBOL and MUMPS. Responsible for system availability, application enhancements and application bug fixes. Performed delivery systems and VMS operating system upgrades.

 

RDBMS ENVIRONMENTS / LANGUAGES � TOOLS

Oracle

SQL Server

Redbrick

SQL

Transact-SQL

UNIX shell

Perl

Java

PL/SQL

DOS Scripting

 

 

EDUCATION

� B.S., Information Science, University of Pittsburgh, 1986.

� ORACLE7 Database Administrator Certification, 1996.