Features
Case Study
Prescription
for Timely BI
Australian Pharmaceutical Industries plugs in new appliances to help improve its data warehouse.
by Cheryl D. Krivda
Getting useful business intelligence (BI) from the data warehousing system at Australian Pharmaceutical Industries (API) used to be difficult. The data warehouse component of the company’s enterprise resource planning (ERP) software was not designed to handle unexpectedly large data volumes, and data integration took 14 to 18 hours a day. As a result, users in the pharmacy division couldn’t query daily sales or inventory-process data until mid- to late afternoon.
Diagnosing the Problem
Through its pharmacy division, API provides wholesale distribution, retail, marketing and business services to some 4,000 community pharmacies across Australia. Each day, the company receives up to 250,000 order lines from these locations. To meet strict government service regulations, orders must be filled by that afternoon with stock stored in 15 distribution centers.
API needed accurate, timely inventory and sales data to meet its service and compliance requirements. Yet performance of the former data warehouse, part of an ERP software implementation, was less than optimal. Designed to rebuild the application data each night, the system lengthened the processing time of batch jobs. The effort of truncating and reloading the database nightly also limited the retrospective view on various business dimensions.
To enable reporting, the company deployed Crystal Reports software from SAP BusinessObjects. Users began requesting group data marts so they could generate reports faster, which further compromised overall system performance. The IT team moved some data from the warehouse to other locations on its IBM i and xSeries hardware. “We tried to run the best queries against it, just to try to get some business insight,” says Hart.
The Right Medicine
Recognizing the value of BI, API executives gave the go-ahead to pursue a new data warehouse and reporting solution in late 2006. The goal was to incrementally develop a best-of-breed architecture that could help create a single view of customers, finances and products. By helping API coordinate its processes, understand past trends and eventually generate forward-looking insight, the solution was expected to produce enough revenue-generating and cost-saving opportunities to pay for itself.
Over a seven-month period, the team defined the project’s scope and considered several technology providers and solutions. In August 2007, API chose Teradata because of the functionality and performance of its solution as well as the company’s integrated approach, positive references and affordable pricing.
“I didn’t think we would end up in the Teradata sphere, since we were an IBM shop,” explains Hart. “What impressed me most about Teradata was that they were actually delivering a solution for us. They weren’t just out to sell us a piece of equipment. They wanted to solve a business problem for us and gain us advantage. ”
"What impressed me most about Teradata was that they were actually delivering a solution for us. … They wanted to solve a business problem for us and gain us advantage."
—Brett Hart, application manager, API pharmacy division
The Optimal Outcome
With a staff of just four people, API’s data warehousing, reporting and BI team needed help with deployment. Hart assigned one member of his group to work with several consultants from Teradata Professional Services. To ensure continued support of the company’s business users, the other three API team members helped as their schedules permitted.
By mid-2008, the group migrated and con-solidated data from its xSeries SQL Server environment into a Teradata Data Mart Appliance. Effective knowledge transfer from the Teradata consultants helped the API team quickly become self-sufficient in their use and maintenance of the new solution.
“The Teradata consultants were highly intelligent, highly skilled and really passionate about making sure the problems were solved,” Hart says. “They didn’t just put in the product and walk away. They wanted to make sure that we got value.”
Compounding Benefits
Since the deployment of the Teradata Data Mart Appliance, API has converted all pharmacy division reporting to run on the new system. Because batch cycles have been significantly reduced, reports are delivered before the beginning of each business day, providing users with current, accurate data. “That has been a significant win for us,” says Hart. “The business users can actually do queries as soon as they get to work, because the data is already available.”
The new solution also enabled the API team to expand its archive models, reducing the amount of data carried within the operational ERP system. In addition to improving operational systems performance, this change allowed the organization to reclaim substantial space on its direct access storage devices.
The Teradata environment also provides a richer historical picture of the data as well as enhanced query capabilities. Users can perform queries against ERP data in near real time and new tools being deployed from MicroStrategy will enable users to generate more of their own reports.
What’s more, the IT team expects the data warehouse to create tremendous opportunities for cost savings and revenue generation. For example, business groups are using the solution to better understand the costs of processes such as product returns.
“Using the data we have now and the aggregations and models we’ve built, we are beginning to gain some insight into credit processes,” Hart says. “We can analyze sales in light of the credits we issue. Then we can break it down further by area or product, or we can roll it up to include other areas in the business. Before, it would take analysts weeks to pull the data together, and once they got the data, it was too old to take any action. Now we can investigate these issues on a daily basis.”
Now and in the Future
With the technology infrastructure in place, API is preparing to deploy sophisticated BI tools and capabilities from Teradata and MicroStrategy. The company also plans to expand its data warehousing and reporting solution to other divisions.
“The retailing part of API is very large, and it’s a significant player in the Australian market,” notes Hart. “We’re also in the process of setting up a pilot data warehouse project with the supply chain division.”
The ongoing success of the project has given API the confidence to consider new applications for the technology and make additional investments. For example, API recently deployed another member of the Teradata Purpose-Built Platform Family—the Teradata Data Warehouse Appliance. “We pushed the Teradata Data Mart Appliance to its limit,” says Hart. “We implemented a Teradata Data Warehouse Appliance as our production machine, and it has definitely given us performance improvements across the board from the extra processing power and disk capacity. The Teradata Data Mart Appliance is now a dedicated test-and-development machine.”
In the long term, Hart hopes to create an enterprise data warehouse to provide all divisions of API with sophisticated BI and analysis. “We need to convince the business that this is the way we need to go,” he says. “Some of the pharmacy areas are very encouraged by what they see, and they’re starting to get on board. There’s a lot of work to be done, and we’re quite young when it comes to data warehousing, but the future is very bright.”
Cheryl D. Krivda writes about the intersection of business and technology for publications and corporations around the world.