Superdrug's Prescription for Prosperity

Information Technology (IT) is hyped as the magic pill to cure all that ails - from sagging profits to bloated inventories. In the AS/400 world, the 'over-the-counter' cure in vogue today is called client/server technology.

But, frequently the client/server remedy proves more harmful to the enterprise than the original needs precipitating it's implementation. So, when UK-based health and beauty giant, Superdrug stores, was looking to use a client/server application development tool as a productivity booster, they were intent upon avoiding the side effects commonly associated with the new technology.

Superdrug operates over 700 stores throughout the United Kingdom. Searching for new opportunities, in 1995 they ventured into the pharmaceuticals market. Dominated by just a few large players and highly regulated by the government, there were many barriers to success.

Undaunted by the challenge, pharmacy controller, Barry Simner, recalls that the chain's experience in customer service - in being the "friendly face of pharmacy" -would be crucial in differentiating Superdrug from its competitors. In addition, he relied on the company's tradition of implementing leading edge technology. "Superdrug is more advanced than a number of UK companies regarding technology. We have a will and desire to be at the forefront."

To succeed, Simner believed the power instantly to generate decision support information from Superdrug's vast AS/400 database would be crucial. "Other pharmacies have had years and years of experience. When they put a financial proposal together, it's relatively easy for them to be able to predict, for example, what the period growth will be. We started from scratch. It would have been simple for us to have made a lot of assumptions and mistakes. So, we have to monitor performance at all times.

Prescribing the Right Tool

Superdrug's IT department had been using IBM's Query for ad hoc enquiries on their Advanced Series F97 AS/400. However, Project Leader, Kelly Guy, says it was severely impacting processing time. "Because of the large volumes of data and the long processing times, the queries had to be canceled to allow the overnight processing to start." She also says Query's limited functionality required them to run fifteen to twenty queries to deliver on some information requests.

The search for a new tool, however, started when users wanted to view graphical data and easily download it to PC-based applications - something their current software simply couldn't deliver. Upon considering various client/server and PC-based application development tools Superdrug chose London-based michaels, ross & cole UK ltd.'s mrc-Productivity series 97. Guy cites the product's ability to deliver the client/server functionality they needed while still leveraging the enterprise AS/400's powerful number crunching and database capabilities. "The PC-based tools we considered could take up to two hours to run a query. mrc-Productivity Series 97 generates native RPG and Visual Basic code, so it performs much better. And using it to perform other functions, like complex calculations, is much easier than Query. You can do it all within one summary or inquiry. That's a marked improvement. It's excellent."

In terms of client/server power, Guy says that mrc's enterprise-centric product was the clear winner. "mrc-Productivity Series 97 offers a facility to let users download data directly into spreadsheets like Excel, for example. It's much better than PC Support. We felt it would improve programmer productivity."

Impacts & Trends

Tooled up and ready to go, Superdrug quickly saw that mrc-Productivity Series 97 would serve as a strategic corporate IT asset rather than simply as a Query replacement or download facility. The company's new pharmaceuticals division was the perfect proving ground for the tool's muscle.

To help measure the success of the fledgling pharmacies, Superdrug needed to understand the impact they were having on the overall business. Guy explains: "When we put the pharmacies in, we had to take out existing shelving. So, we wanted to see the impact on the business of the space reallocation."

Having instant access to trend data also would be critical. "We wanted to show a trend of the pharmacy's impact moving across the financial year - and compare the actual figures against the budget. This would show us the impact the pharmacy was having on the business."

Guy tested mrc-Productivity Series 97's data mining capabilities on the pharmacy division's request for this trend data. She decided to build a graphical executive information system (EIS) to empower managers to click on an icon from their PC's and pull up a series of graphs to view the desired data. "Our users like graphs because they create an impact which you just can't get on the AS/400. Any trend is going to jump out and hit you, so you don't have to sit and analyze figures on a report."

A Virtual Data Mart

In total, Guy built nineteen graphs with mrc-Productivity Series 97. "They examine, from five different levels of detail, NHS prescription medicine sales, over-the-counter medicine sales and sales for the rest of the store.

"The first level of graphs examines budgeted store sales trends in those three categories against those budgeted. The second current year sales with the previous year. Others look at weekly store sales by area and by region. Finally, the last set of graphs looks at overall sales trends versus those budgeted. They assist the finance department in budgeting and monitoring what's going on in the pharmacies."

Simner likes that the graphs are set up so he can see the data over different time periods. "We can view it on a weekly, periodic and annual basis, and then report on it in various ways."

Describing his AS/400 knowledge-base as "absolutely zilch," Simner is also pleased at the ease with which he can use mrc-Productivity Series 97 to access the wealth of data stored in the AS/400. "I'm a great believer in graphs," adds Simner. "They can very quickly impart a lot more information than reams and reams of statistics."

Simner also appreciates being able to access live AS/400 data without having to interact with the AS/400's text-based, black and green screen interface. "It's been set up for me such that I've got a series of icons with the graph titles in my Windows program group. I simply click on one as need be. From there I can break down how I view the data - from individual store totals to totals on a company-wide basis."

A New Perspective

A development tool's value can not be measured only in terms of features and functionality. Indeed, too often cutting-edge technology creates more ills than it cures. For instance, in the case of AS/400 client/server development, unnecessarily high costs, complexity, maintenance and staffing requirements frequently result when companies don't examine the tool's architecture from an enterprise perspective.

Superdrug found that the best yardstick to assess a development tool's success is how well it empowers individuals to do their jobs more productively and efficiently, while still preserving the integrity of their IT system.

By that standard, Simner believes mrc-Productivity Series 97 stands out. He says that the new technology has allowed him to forge a new relationship with AS/400 data and empowered him to use it more creatively. "Superdrug had never worked with trend data, and wasn't used to the peculiarities of the prescription dispensing business. We had to start from scratch, bring together the data and turn it into useful information. With these mrc-Productivity Series 97 applications, we now have the means to see, for example, how the level of awareness by the patient grew over a period of time. And it shows us the effect that awareness had on prescriptions."

Simner says because the client/server graphs have reoriented how Superdrug looks at data, even more possibilities are opening up. "We have been able to look at store data and break it down into categories to understand the trend information. Within that, we could actually see that there were exceptions to these overall company trends. That's leading towards a need for even more information because we need to look at what is causing these exceptions - why are some stores over performing, for example. Based on how Kelly set these graphs up, it's helping this data to jump off the page."

Simner sees how client/server access to AS/400 data can be used in new and creative ways. "I would like to take it much further in the future - go from the macro to the micro in terms of product categories and areas within the store we could link together. I'd like it all to come together to produce that overall picture."

A Healthy Prognosis

Superdrug's success with mrc-Productivity Series 97 suggests that AS/400 client/server technology can deliver on it's promises without harmful side effects - if architected for the enterprise. In Superdrug's case, that meant using a development tool designed to optimize their existing AS/400 resources, staffing and knowledge-base.

This empowers Superdrug's IT staff to leverage their existing AS/400 development skills and apply them in the client/server arena. "In the early stages, when we were thinking about how to build these graphs - before mrc-Productivity Series 97 came along - we thought it would be necessary to get someone in the department trained on Microsoft's Visual Basic development language. But we didn't have anyone here to train the others in it."

However, the software tool's ability to automatically generate Visual Basic code changed that assumption. "Now, we can create Visual Basic applications without knowing how to write Visual basic code. We've managed so far without anyone having to learn it. So we can complete projects without any overhead in training."

Just as important as leveraging current IT resources, Guy says, is catering to the users' evolving preferences. "They have been delighted with the mrc-Productivity Series 97 applications and the way in which the information is provided. "The graphs bring the information to life. I'm surprised at how good they look and the kind of excitement they have generated. It's such a move away from the typical AS/400 stuff. I personally think they've exceeded our goals."

Simner concurs: "We are a very lean company. It would be very easy to overlook important data if we didn't have this new system. We don't have the manpower resources that many of our competitors have. Without these mrc-Productivity Series 97 graphs, I'm afraid we would certainly be looking for another one to two people to monitor and do various bits and pieces, like scanning through the data and producing reports highlighting the various issues. The client/server systems lead the way for someone to do a lot better. They've given me a few more hours in the day to concentrate on other issues."

Guy, Simner and the rest of the Superdrug team must be doing something right. Entering the already saturated drug store market in 1995 with only four stores, they are currently up to 106. Using a combination of talent, teamwork and a thoughtful partnership with technology tools that support enterprise goals, Superdrug is only gaining momentum. "The team Superdrug has put together is always looking forward. The great thing about the whole company is that we want to be at the forefront of whatever is going on."