OLAP Expert Inside
Services Services
Case Studies Case Studies
Financial Services
Pharmacy Performance System
Large-scale ETL from Excel files
Special Offers Special Offers
Who We Are Who We Are
.NET Consulting .NET Consulting
Contact Us Contact Us

WinMetrics Corporation Business Intelligence Case Study: PharmacyPerformanceTracking
Pharmacy Performance System provides business intelligence to Fortune 500 health care organization This is the story of how a nationwide health care organization, which we will refer to as HEALTHCO (more), developed pharmacy productivity metrics and how we worked with them to deliver graphic performance reports to its Pharmacy Managers. HEALTHCO fills more prescriptions in California than any other organization. We'll start with the results to assure you that this story is worth reading. This project was completed before OLAP technology had matured. If PPS was reinvented using OLAP, the development time and expense would be reduced by at least 50%.

It was near the end of a long day of presentations at HEALTHCO's Inter-Regional Division Managers meeting. Irving Choi rose to present the Pharmacy Division's latest innovation: a Pharmacy Performance System (PPS). The room was hot and the audience was groggy. It was a good time to tell a good joke. But, Irving has no use for dialog that doesn't further the plot. Patiently and directly, he explained how PPS graphically presents performance targets, peer group averages, peer group rankings, individual pharmacy results and consolidated results for five key pharmacy performance metrics. He showed the how all of the graphs are updated via their corporate Intranet. He demonstrated how PPS gives each manager at every level of the organization a score card for assessing their performance supported by diagnostic reports to help isolate problems.

As soon as Irving concluded his 25 minute presentation, the room erupted in applause. After a year of directing the PPS development project while continuing to manage two pharmacies, Irving's efforts were rewarded.

" At the end of its first quarter, PPS showed that the overall labor cost efficiency improved by 4.2%, the number of prescriptions filled on time increased by 3%, OTC sales efficiency improved by 13.4% and central refill processing usage increased by 7.7% for the 100+ pharmacies that were using PPS. We were not nearly as certain about how the other pharmacies were doing. "
-- Irving Choi, PPS Project Manager

PPS delivers a graphical score-card to the Pharmacy Manager on a single Excel worksheet:

Labor Cost Per Rx Sold is tracked.
Customer Satisfaction Survey Results are tracked.
Percentage of Rx Processed on Time is tracked.
Pharmacy Performance Report (click for full-scale image)
Pharmacy Performance Report
(click to zoom in)
Over-The-Counter (OTC) sales per Rx is tracked.
Percentage Rx Filled by Central Refill Processing (CRP) is tracked.
Performance targets for peer groups are maintained with effective dates. Bonus amounts are tied to multi-level performance targets. This makes it easy set and raise the standards of excellence. Pharmacies can be divided into any number of peer groups to make the performance targets and rankings fair.
Peer group averages are reported, which legitimize performance targets.
Performance rankings in for all key success metrics are reported. Rankings foster healthy competition and reward best practices.
All PPS functions are instantly accessible from any Web browser on HEALTHCO's Intranet. This made it easy to deploy the system to 100 pharmacies within the first month of operation.
Performance metrics are presented graphically in Microsoft Excel, which makes the presentation easy to update, easy to reuse and produces excellent hardcopy.
Application security facilities that authorized access to all tables, input forms and reports.
The user interface is database schema-driven for most table maintenance dialogs. An application data dictionary and input form layout editor enable database schema updates to be deployed in minutes
Software updates are deployed without interrupting service to on-line users
On-line Programmers Guide with hyperlinks to ASP source code

Today, a HEALTHCO pharmacy manager can use any Web Browser on his Intranet or dial-up connection to find out how his store is doing. Senior managers find it just as easy to spot the winners and losers at every level of the organization. But, as Irving will tell you, it hasn't always been that easy.

" Before PPS, we didn't measure performance consistently across our organization. For those areas that shared a performance tracking method, the effort to gather the necessary data made it difficult to keep performance reports current. "
-- Irving Choi, PPS Project Manager

" PPS reports tells a Pharmacy Manager everything they need to know about their performance on a single page. "
-- Irving Choi, PPS Project Manager

1.1 Pharmacy Performance Reports

All of the pharmacy-level performance graphs include line graphs for peer group targets and averages. Also included is the pharmacy's ranking in their peer group alongside the first and second ranking pharmacies. The pacesetters and their pace are obvious.

Peer group line graphs and rankings are irrelevant in consolidated reports for higher organization levels.

Labor Cost Efficiency Graph: labor cost per Rx sold by pay period
Labor Cost Efficiency Graph (click for full-scale image)
Labor Cost Efficiency Graph
(click to zoom in)
Customer Satisfaction Survey Results Graph: percent satisfied, very satisfied, dissatisfied and very dissatisfied by quarter
Customer Satisfaction Graph (click for full-scale image)
Customer Satisfaction Graph
(click to zoom in)
Processing Time Graph: the percentage Rx filled locally in under the target number of minutes by pay period
Rx Processed On Time Graph (click for full-scale image)
Rx Processed On Time Graph
(click to zoom in)
OTC Sales Efficiency Graph: Over-the-Counter sales per Rx sold by pay period
OTC Sales Efficiency Graph (click for full-scale image)
OTC Sales Efficiency Graph
(click to zoom in)
CRP Usage Graph: the percentage of refills handled by Central Refill Processing by pay period
CRP Usage Graph (click for full-scale image)
CRP Usage Graph
(click to zoom in)
Budget Estimator Report
Budget Estimator Table (click for full-scale image)
Budget Estimator Table
(click to zoom in)

1.2 Performance Diagnostics

Should the performance reports listed above indicate an opportunity for improvement, Pharmacy Managers can use any of the following reports to help isolate the problem:

Hourly Volume and Median Processing Time: the volume of Rx filled locally per hour, the percentage Rx filled in under the target number of minutes and the median time to fill them per hour during a given day.
Hourly Volume and Processing Time (click for full-scale image)
Hourly Volume and Processing Time
(click to zoom in)
Daily Volume and Median Processing Time: the volume of Rx filled locally per day, the percentage Rx filled in under the target number of minutes and the median time to fill them per day during a given week.
Daily Volume and Processing Time (click for full-scale image)
Daily Volume and Processing Time
(click to zoom in)
Weekly Rx Processed On Time: the percentage Rx filled locally in under the target number of minutes for the six month period prior to a given day
Rx Processed On Time Weekly (click for full-scale image)
Rx Processed On Time Weekly
(click to zoom in)
Weekly CRP Usage and OTC Sales Efficiency: the percentage of Rx filled by Central Refill Processing and the OTC Sales per Rx Sold for the six month period prior to a given day
CRP Usage and OTC Sales Efficiency Weekly (click for full-scale image)
CRP Usage and OTC Sales Efficiency Weekly
(click to zoom in)
Pharmacy Performance Rankings: ad-hoc ranking reports for each success metrics for each peer group or organization level
PPS Labor Cost Per Rx Sold Ranking (click for full-scale image)
PPS Labor Cost Per Rx Sold Ranking
(click to zoom in)

In the summer of 1997, HEALTHCO's Pharmacy Division was led by Ken Scott. Ken wanted to achieve significant operating cost reductions without sacrificing customer service. HEALTHCO manages hundreds of internal pharmacies ranging in size from three to over a hundred employees. Over the years, HEALTHCO had grown through acquisition and there were three organization levels above the pharmacy manager: local area, service area and region. Consistency across such a large, diverse organization is difficult to achieve. Ken knew that he would only get one chance to deploy a new performance tracking method.

Ken had plenty of opinions on how best to get this done. But, he wanted to test his ideas against the health care industry's best practices. So, he brought in a world-class management consulting firm, which we will refer to as MANACO. Ken, his right-hand man Ron Tochioka and the MANACO team poured over pharmacy cost and revenue reports for several weeks before arriving at a concise set of metrics. From the start, they had the wisdom to know that pharmacy managers would only support the new program if they were convinced it was fair. So, pharmacies would be partitioned into peer groups by size and hours of operation. Each peer group would have its own set of performance targets.

The biggest pharmacy cost component is labor. Labor cost had to be measured, but in a way that measured its efficiency. Pharmacy Managers adjust staffing levels to satisfy the demand for service, which varies greatly over a 24x7 period. Demand also varies seasonally. So, Ken and his team decided to measure labor cost per Rx sold.

Ken wanted his organization to use their human resources more efficiently, but not at the expense of customers. So, the results of customer satisfaction surveys were added to the mix. They also knew that less than 2% of all customers would respond to the survey, but that all customers want to have their prescriptions filled in a timely manner. So, they decided to measure the percentage of prescriptions filled in less than the target minutes.

HEALTHCO's pharmacies not only fill prescriptions, they offer over-the-counter (OTC) products as well. Ken knew that once a customer is inside a HEALTHCO pharmacy to fill a prescription, it is convenient for them to purchase other products. So, they decided to measure OTC sales efficiency as OTC sales per Rx sold.

After customer fills their initial prescription, it is more efficient to fill their refill orders from a central location that HEALTHCO refers to as Central Refill Processing or CRP. By using CRP instead of filling prescriptions locally, inventory and handling is minimized as each local pharmacy. Ken wanted to encourage his pharmacy managers to maximize their use of CRP. So, they decided to measure the percentage of refills filled by CRP.

Ken was himself an experienced pharmacy manager. He knew that if someone handed him a report that revealed a deficiency, he would want the tools to isolate the problem. Thus, the team designed hourly volume, processing time and employee scheduling reports to serve this purpose.

By October, 1997, MANACO had developed a small prototype in the form of a Microsoft Excel workbook, that was deployed in some of Mr. Tochioka's pharmacies. Further refinements were made and, soon, it was time to roll it out to a larger audience. MANACO recommended that HEALTHCO bring in a software engineer to make the Excel workbook fool-proof. In December, they placed a call to The Logical Product, who brought in Carl Kelley of WinMetrics Corporation to handle the job.

Mr. Kelley reviewed the Excel prototype and the list of enhancements that needed to be made. Yes, the workbook could be enhanced to meet the specifications. But, decision support reports must change to keep pace with the business. The Excel workbook would be rolled out many times. And each time a new version is released, moving data from the old version to the new workbook would be difficult. Furthermore, there would be no convenient means of knowing whether or not the workbook was being used nor would it be easy to consolidate results at higher pharmacy organization levels. Mr. Kelley recommended that performance data be maintained in a central database.

Mr. Kelley also recommended that JAVA applets be used for delivering the decision support graphs. But, Mr. Scott made it clear that they would need high-quality hardcopy output from both Wintel and Macintosh PCs. He expected pharmacy managers to show hardcopy reports to employees in order to garner their support. Since JAVA applets could not be expected to produce high-quality hardcopy, MANACO suggested that the system produce the reports in the form of downloadable Excel workbooks. While possible, this alternative would have been cumbersome to use and it would have taxed the network. Fortunately, Mr. Kelley noticed that Microsoft had announced a Web query capability for Excel 97 and that it would be bundled on Excel 98 for the Macs and he developed a prototype as proof of concept.

Mr. Kelley met with the HEALTHCO/MANACO team three times in as many months to develop a detailed specification that included a database schema and report descriptions. He prepared a fixed cost development proposal which HEALTHCO accepted.

" Working with Mr. Kelley was easy. We didn't have to speak geek to get through to him. When he asked questions, it was usually because our ideas were not as solid as they needed to be. "
-- Ken Scott, PPS Project Sponsor

HEALTHCO needed someone to manage the PPS 1.0 development effort. That someone needed both the technical background to direct Mr. Kelley's efforts and the business background to understand pharmacy requirements. That someone needed to have credibility and good working relationships with key managers throughout HEALTHCO's pharmacy division. That someone had to be an early adopter of the management techniques that PPS was designed to automate. That someone was Irving Choi.

" Irving consistently delivered excellent results. We wanted everyone to emulate his pharmacy management techniques. "
-- Ken Scott, PPS Project Sponsor

Mr. Kelley's software development work on the PPS user interface was delayed until April, 1998, due to the difficulty HEALTCO's Pharmacy Division had gaining access to the necessary payroll and point-of-sale data. Sensitive employee data does not flow easily across major organizational boundaries and the reasons are not technical. Another software consulting firm, Tonari, was hired to automate the regular import of payroll and point-of-sale data.

In April, 1998, Mr. Kelley started to develop PPS from a baseline of 15,000 lines of VBScript, HTML, JavaScript, Java, SQL and Tranact-SQL source code. By April, 1999, PPS had grown to over 70,000 lines of code. During this 12 month period, Mr. Kelley averaged about 230 lines of debugged source code per day. The original schema for the PPS database included about 40 tables. The PPS database is now comprised of over 80 tables, 300 views and 50 stored procedures.

Mr. Choi, who resides in Los Angeles, and Mr. Kelley, who resides in Berkeley,CA, met face-to-face only three times the project. The purpose of these meetings was to discuss new requirements. Mr. Kelley never visited the site where the physical server is located for technical reasons. All of the software developed by Mr. Kelley was written and tested off-site and installed via FTP over a dial-up connection.

Contact us for a more detailed account of how PPS project milestones were reached, if necessary.

PPS Main Menu (click for full-scale image)
PPS Main Menu
(click to zoom in)

A system can only deliver its benefits if people use it. Here are some of the many conveniences built-in to the PPS that made it easy to learn and easy to use:

Simple, Consistent Look and Feel All PPS pages share the same header and footer information including a link to the main menu, logout and help. We limited the GUI to three colors and avoided Web page gimmicks that tend to annoy users over time.
Fast Web Page Display, Dial-up Support Of decision to avoid flashy graphics also minimizes the size of each page in bytes. This minimizes the burden on the network and makes it possible to have a satisfying session via a dial-up connection. So, managers can use PPS even when they are away from the office.
Search Any Column of Any Table The schema-driven search criteria page enables users to enter search criteria based on any column in the table. Date ranges, numeric ranges, multiple choice foreign key and encoded column selections are all supported. When the results are displayed, the search criteria used appears at the top of the hit list.
Dynamic Hit List Page Resizing Users can dynamically adjust the number of hits that appear in a page of selected records to best fit their screen.
Selected Record Set Navigation Users can click forward or backward through a set of selected records before locking one to be added or changed. A "Page 2 of 12" type display keeps the user informed as to where they are in the list of selected records.
Friendly Field Formatting Currency fields are automatically formatted with dollar signs and commas while browsing.
Report records are hyper-linked to table records Hyperlinks are automatically provided that make it easy to jump between report records and related table records to reveal more details and to facilitate updates.
Intelligent Go-Backs After Update Users are automatically returned to the appropriate hit list after an update procedure. The "Go to Last Query" link also takes you to the appropriate page where the hit list spans multiple pages.
Hierarchical Database Navigation A JAVA applet was developed so that users could point and click to select any record and report for a given pharmacy in their Web browser just as they would use Windows Explorer to locate files on their hard disk.
Persistent Option Selections In most cases, PPS remembers the users last option selection so that menus don't have to be pulled down more often than necessary.
Automatic Subordinate Record Maintenance Whenever a new employee is added, their user and user group membership records are added automatically.

Whenever a new pharmacy is added, a host of subordinate records with default settings are added automatically.

Multi-Window Support Users can take advantage of the Web Browser's "New Window…" function to spawn new PPS windows so that related data can be juxtaposed or unrelated operations can proceed in parallel.
Convenient Help Desk Access The footer of each help file presents the phone number and an email link to the PPS help desk. The PPS software version and screen ID appears in the footer at the bottom of each page to facilitate support. Program exceptions are automatically recorded in the application log file so that users don't have to transcribe error messages.
4.1 Data Entry Conveniences

Without good data, PPS reports cannot support good decisions. PPS relies on data that only HEALTHCO's pharmacy managers can supply. These people are not data entry clerks. The PPS database is both accurate and complete because its users were willing to maintain it. PPS was designed so that data entry effort is minimized. In the Ease of Maintenance section below, you will see how the input form layout tool makes it easy to start with a small number of data entry fields and gradually increase that number over time. The input form layout tool enables a non-programmer to hide, expose and rearrange the columns of any PPS table. Discrete value constraints can be added incrementally as well.

Here are some of PPS conveniences that make it easier to enter data:

Pull-Down Menus
PPS Update (click for full-scale image)
PPS Update
(click to zoom in)
Fields with discrete value constraints as well as fields that reference rows of other tables,a.k.a. foreign keys, are entered by pulling down a menu.
Consistent Error Handling Error messages always appear juxtaposed to the offending value.
Data Entry Hints A data entry hint can be defined in the PPS table column dictionary for any table column that can help users avoid errors.
TAB Navigation within forms As with any HTML form, the TAB button allows operators to quickly jump from field to field from left-to-right, top-to-bottom.

4.2 Payroll and Point-of-Sale is Imported

Accounting systems must feed PPS the raw data so that PPS can track performance. Data integrity must be managed when transferring data from one application to another and the PPS import utility provides extensive error checking.

PPS Import Utility (click for full-scale image)
PPS Import Utility
(click to zoom in)
For ad-hoc imports, e.g. from the Personnel database, the PPS database administrator may select any one of the available import files for processing. Importing records into PPS is a two-pass process. If any errors are detected on the first pass, no records will be posted.

The data import facility offers the following conveniences:

Comprehensive Input Validation All imported records are validated as though they were being entered manually.
Import Record Layout Specifications are Explicit If an formatting error is detected in the input text stream, the problem can be quickly isolated.
Optional Detailed Error Messages Brief and verbose error message modes are supported.
Restart at N-th Record The import process may be started at either the beginning of the import file or the n-th record. This speeds up the process of recovering from errors.
Records are Inserted or Replaced If a record is already on file, it will be replaced to facilitate accounting adjustments.
Import Log Files When records are posted to the database, all accept/reject messages are recorded in a separate import log file.

PPS is designed to allow HEALTHCO to perform many database administration tasks without the assistance of a programmer. Consequently, they are able to adapt quickly to new requirements without expensive software upgrades or down time.
5.1 Data Dictionary Supports Rapid Database Schema Updates

PPS database maintains a lot of information about itself including:

  • Table captions
  • Column labels
  • Column data entry hints
  • Discrete values for encoded columns
  • Encoded column value descriptions
PPS Form Layout (click for full-scale image)
PPS Form Layout
(click to zoom in)

Field validation criteria is driven by ADO properties. As a result, the many database maintenance operations that can be performed without the assistance of a programmer including adding a new column to an existing table.

5.2 Browser-based Security Administration

PPS form privileges (click for full-scale image)
PPS form privileges
(click to zoom in)
Access to PPS is authorized by user IDs and passwords maintained in the PPS User table. After a successful login, the user inherits all of the privileges that have been granted to all of the User Groups to which the user belongs as determined by the User Group Membership table. The User, User Group and User Group Membership tables are maintained by the PPS Database Administrator. PPS are spared feelings of being denied access because only authorized resources are presented.

This visibility of sensitive employee information including Social Security Numbers and wages is dynamically enabled or disabled depending on the user's organization level relative to that of the employee record that they have retrieved.

For accountability, PPS records the last login date of each user session and displays the full name of the user in the status bar that appears at the bottom of each screen. Potential security violations are recorded in the application error log.

Server Microsoft IIS Microsoft Internet Information Server (IIS) 4.0 with Active Server Pages supported by Windows NT 4.0 Server SP4 running on a Compaq Proliant 6500 computer with dual 200 MHz Pentium CPUs, 576 MB RAM and a RAID 5 disk controller with 12 GB of Ultra-Wide SCSI disk storage.
Database Management Systems Microsoft SQL Server Microsoft SQL Server 7.0
ODBC Drivers Microsoft MDAC 2.0 ODBC driver for SQL Server version
Client PC Configurations
  • Intel-based PCs: Microsoft Excel 97, Netscape 3.0, 4.x or Microsoft Internet Explorer 3.02, 4.x over Microsoft Windows 95 with 32MB RAM and a Pentium 133 or better.
  • MacIntosh Power PCs: Microsoft Excel 98, Netscape 4.x or Microsoft Internet Explorer 4.x over MacOS 7.3 or better with 90MB RAM.
Application Languages VBScript for most application programming, HTML for the GUI, JAVA for the tree control, JavaScript for some client-side input validation, ANSI-standard SQL for database queries and view definitions, Microsoft Transact-SQL for SQL Server trigger procedures.
" PPS passed its biggest test during a change in senior Pharmacy management. Carey Cotterell assumed control of the Pharmacy Division just after PPS was deployed. He evaluated PPS and then decided it was in the best interest of HEALTHCO not only to maintain it, but to extend its features and expand its use. Mr. Cotterell also decided to reward Irving Choi with a big promotion. "
-- Carl Kelley, PPS Software Developer

The Measure of Success
Any Comments? 1997-2009 WinMetrics Corporation. All rights reserved. Terms of Use.