|
|
|
Services |
|
|
Case Studies |
|
Financial Services
|
|
|
Pharmacy Performance System
|
|
|
Large-scale ETL from Excel files
|
|
|
|
Special Offers |
|
|
Who We Are |
|
|
.NET Consulting |
|
|
Contact Us |
|
The Measure of SuccessTM |
|
|
Business Intelligence Case Study: Pharmacy Performance Tracking |
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.
"
PPS delivers a graphical score-card to the Pharmacy Manager on a single Excel worksheet:
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.
"
"
PPS reports tells a Pharmacy Manager everything they
need to know about their performance on a single page.
"
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.
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:
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.
"
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.
"
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.
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:
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:
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.
The data import facility offers the following conveniences:
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:
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
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.
"
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.
"
|
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.
Microsoft SQL Server 7.0
