Australian Pharmaceutical Industries plugs in new appliances to help improve its data warehouse.

Features

Case Study

Prescription
for Timely BI

Australian Pharmaceutical Industries plugs in new appliances to help improve its data warehouse.

Getting useful business intelligence (BI) from the data warehousing system at Australian Pharmaceutical Industries (API) used to be difficult. The data ware­house 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 distri­bution, 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.

Executive summary

Australian Pharmaceutical Industries

The company: Australian Pharmaceutical Industries Limited (API) is one of Australia’s leading health and beauty companies. API’s pharmacy business provides wholesale distribution, business and marketing services to community pharmacies across Australia. The retail division is a leader in the health and beauty market through its retail brand Priceline. The consumer division is a niche player in over-the-counter pharmaceuticals and is based in New Zealand.

The challenge: Australian Pharmaceutical Industries Limited (API) is one of Australia’s leading health and beauty companies. API’s pharmacy business provides wholesale distribution, business and marketing services to community pharmacies across Australia. The retail division is a leader in the health and beauty market through its retail brand Priceline. The consumer division is a niche player in over-the-counter pharmaceuticals and is based in New Zealand.

The solution: The organization deployed a Teradata Data Mart Appliance— and subsequently a Teradata Data Warehouse Appliance—with the help of consultants from Teradata Professional Services. With the original appliance now dedicated for test and development and the second used as the production system, the solution delivers accu­rate, current sales and inventory data in near real time.

The results: Business users can make decisions faster and more accurately. Using new tools from MicroStrategy, API’s pharmacy division is enhancing its BI efforts, examining processes such as credits for new efficiencies and cost-saving opportunities.

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 perfor­mance 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."

The Optimal Outcome

With a staff of just four people, API’s data warehousing, report­ing 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 envi­ronment 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 con­verted 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 opera­tional ERP system. In addition to improving operational systems performance, this change allowed the organization to reclaim substan­tial 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 opportuni­ties 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.”

Behind the Solution:
Australian Pharmaceutical Industries (API)

Database: Teradata 12

Platform: Teradata Data Mart Appliance development system and a 2-node Teradata Data Warehouse Appliance production system

Users: 25 direct, 80 indirect users

DBAs: 1

System: SUSE Linux

Storage: 23TB for both systems

Teradata utilities: Teradata Tools & Utilities 12—FastLoad MultiLoad and Teradata Manager; Priority Scheduler; Utility Pack—SQL Assistant and BTEQ

Tools/applications: Teradata applications and products from MicroStrategy and SAP BusinessObjects

Now and in the Future

With the technology infrastructure in place, API is preparing to deploy sophisticated BI tools and capa­bilities from Teradata and MicroStrategy. The company also plans to expand its data warehous­ing 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 encour­aged 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.”


Your Comment:
  
Your Rating:

Comments
Fuzzy Logix