by Elizabeth M. Ferrarini

Around 2003, McKesson Pharmaceutical, a $71 billion distributor of pharmaceuticals and a business unit of McKesson Corporation, couldn't take the consistency of its data for granted. The company was suffering from data proliferation brought on by multiple data repositories and reporting systems for order processing, inventory, and finance.

Things began to change when Brian Hickie, a 10-year McKesson veteran with experience carrying out and auditing IT systems, was asked to bring together an ERP system and a fledging business intelligence system. For three years, Hickie has been the business lead primarily responsible for the design, implementation, and adoption of one of the largest SAP Business Warehouse implementations in the world.

Enterpriseleadership.org recently spoke with Hickie, vice president of business intelligence at McKesson Pharmaceutical, about the challenges of getting the major phase of this business intelligence system off the ground in record time. He has spoken about business intelligence at computer industry conferences and business conferences, such as a recent conference put on by the Economist.

EL: What were some of the specific business reasons leading to the  business intelligence initiative?

BH: The senior executives knew it could provide good process improvements, gains in productivity, or close the profit leaks. If we had better information in these areas, they also knew we could derive some significant benefits to the bottom line.

We knew we needed a business intelligence implementation that integrated data across various applications. We wanted to look at the granular level details and bring all of our legacy systems into one location. Building out the analytics would give us a whole view of the entire process.

For example, our legacy warehouse systems contain certain inventory information, such as quantity. Our SAP system does most of the valuation of those inventory quantities. We already had a full picture of the distribution center from a quantity and a pricing perspective. We needed to build a business intelligence system to join these two systems together.

EL: What has been the bottom-line payback to the  company?

BH: For competitive reasons, I can't provide any dollar amount. Let me put it this way: It was a significant amount, and we've done a good job of hitting that target.

EL: Can you give a specific example of a process improvement you  derived from the business intelligence system?

BH: For a long time, we were pulling month-old data from our inventory adjustments within our distribution center. It took days to figure out what was happening. With the new solution, we get next-day analytics and can resolve any problems on a just-in-time basis.

EL: What are some of the analytical tools end users have in the  business intelligence system?

BH: We use the native functionality in the new SAP solution. It's a beefed-up version of an Excel-based tool. A plug-in enables you to do various drilldowns, robust sorts and switches, and characteristic and attribute switching. Each Excel workbook page can be turned into a Web page. It also has a scorecard and dashboard functionality through the Web application. Our financial users are happy with that type of analysis tool because they use Excel all the time.

EL: Can you discuss the types of users who benefit from the business  intelligence system?

BH: The bulk of our users come from the finance side of the house. However, we've reached out also to the operations people who run our 30 distribution centers, as well as the sales people.

EL: Just how much data do you pull off daily?

BH: We pull anywhere from 15 million to 20 million records a night out of the transactional systems and load upwards of 30 million to 40 million records a night through our data warehouse solution. On volume, we're one of the largest data warehouses for SAP. Our SAP data warehouse system resides on Oracle in a 10-terabyte data warehouse. We use IBM AIX hardware.

EL: You started out having people build their own queries and then  you stopped this procedure. Why?

BH: We still have people doing this because we haven't gotten to them yet. Our goal initially was to get as many of our analytical end users running on this solution. Some users were building queries, left and right. The number of queries at one point exploded to 5,000 queries being used by many different people. Then, some people were forgetting about the queries they built -- things got so out of control. We came up with a policy that required deleting the queries that hadn't been used in 90 days.

Our discussions with end users made us realize that despite the data dictionary, they didn't have a good understanding of their data. We looked at all of the queries and assessed how they were using them. My team came up with the "master query" concept. We took 700 queries in the cell area and reduced them to 50 queries. End users could now execute everything they needed to do within that master query. It allowed us to eliminate the number of queries that were out there, and also allowed everyone to be on a consistent page when it came to getting results for data. By working at the database level, the application level, and the query level, we were able to tune those queries to run really fast. We've gotten significant performance gains as well.

EL: How do you prioritize business intelligence  requests?

BH: I prioritize the business intelligence requests, but we also operate in the larger realm of governance. We're currently working with groups of end users to determine what are the highs priorities of things to get done, but the process is by no means perfect.

EL: When it came to the business intelligence system, how did you get  on the same page with the ERP folks?

BH: That was a challenge. This business intelligence system had been running in parallel with the ERP system, which was the SAP Sales Distribution and Materials Movement Module. The ERP team had been working on our system for several years. Business intelligence was sitting on the fringe, trying to build data warehouses.

I was asked to work with the ERP team and bring the two systems together. We had nine months to accomplish this. We had to capture the new data that was coming out of the ERP system and the financial data that existed on our legacy systems. Our goal was to get that data in the lower levels. The ERP team drove what the requirements were for the data provisioning aspect and some initial reporting. My team became more of the subordinate group and listened to what was going on from a transactional perspective and from the perspective of building the data provisioning. It worked very well, despite some cultural and political things. Within seven month, we provisioned the data and built initial analytics.

EL: Are you moving towards the Balanced Scorecard?

BH: We're moving slowly towards business performance management, such as the Balanced Scorecard. First, we want those folks who are closest to their data to really take the time to understand it. Business intelligence brings data to life in a different realm. As a result, you have to give these folks a chance to explain the issues that may come up from a Balanced Scorecard. We still have a lot to do in process-based analytics before we get there.

EL: What are some of your upcoming projects for business  intelligence?

BH: We're continuing to build the process-based analytics, but we are looking at operational business intelligence. We want to be able to provision the data more quickly across this environment. We also want to look at the processes and ask ourselves, how quickly can we get the data to end user? We need to address more of our business users.

--

Elizabeth M.  Ferrarini is a free-lance technology writer based in Boston,  Massachusetts.