Case Study:

Data warehouse saves millions in inventory costs

1 December 2006

With a customer base of ten million, South African retail chain Shoprite needed a robust and flexible business intelligence solution to facilitate its rapid growth.

The Shoprite Group of Companies, Africa's largest food retailer, has grown from eight outlets in 1979 to service a customer base of ten million people across Africa, southern Asia and the islands of the Indian Ocean. With annual revenues of approximately US$2.7 billion, the Shoprite Group consists of retail outlets providing service across the range of income brackets.

The company has long recognised the need for IT solutions in managing its business: "The African retail scene is demanding because we work with operating margins of sometimes less than two per cent," says Shoprite CEO Whitey Basson. "So it is very important to control and manage the business as precisely as possible, which requires accurate data."

As the company grew it found that the inflexibility of its existing operational reports system meant that managers often lacked confidence in the numbers generated. In seeking another solution that would support a business intelligence platform, Shoprite identified key needs including agility, data integrity, ease of use and deployment, and reporting that provided better visibility into the business.

Shoprite therefore replaced its existing mainframe reporting system with an n-tier data warehouse and data marts created using Microsoft SQL Server 2000 Enterprise Edition running on Microsoft Windows 2000 Advanced Server, with plans to upgrade to Windows Server 2003.

The solution includes multidimensional cubes created using Analysis Services, and a ProClarity front end for reporting. Shoprite uses SQL Server 2000 Reporting Services to replace information that was previously assembled using Business Objects. A dashboard interface created using Microsoft Visual Studio .NET development system and the Microsoft .NET Framework provides immediate feedback on how stores or other organisational units are doing against key performance indicators. All reporting is accessed through a Web portal created using Microsoft ASP.NET technology. SQL Server Data Transformation Services and stored procedures created using the Transact-SQL language are used for extract, transform, and load processing.

"My request to our IT department was to find the best platforms and the best systems, so that we would have the most modern structure available to cope with our expansion programs into other areas in the world," says Basson. "Our people decided to go with SQL Server 2000 and the Windows ?Server System. The business intelligence data warehouse they created has allowed us to manage our business in ways that simply would have been impossible to track before."

Reports generated by the Shoprite data warehouse have given store managers, buyers, and corporate decision makers the information they need to more precisely manage the business. Additionally, by giving suppliers and other partners access to data warehouse reports, Shoprite has extended its new efficiencies throughout its supply chain.

"Opening our BI platform to suppliers lets them see exactly what is happening with sales and inventory, with figures that are updated daily," Basson says. "This allows our partners throughout our supply chain to make the best decisions for any specific product." With everyone from top Shoprite executives to store managers and suppliers able to see exactly what is happening on a daily basis, inventory and related costs are going down. The Shoprite data warehouse has also given the company's corporate executives and managers in the field an essential building block for success: dependable data.

"Since deploying our data warehouse with SQL Server 2000 there are no longer questions about data integrity," says chief information officer Gert Mentz. "Rather than worrying about whether the numbers are accurate, we can concentrate on using our business intelligence strategically to enhance our performance as a company. The deployment was welcomed because we were immediately able to provide the business with data that they could use effectively in day-to-day operations, and won over a lot of hearts."

The data warehouse information is now considered such an important asset that managers are looking for new ways to use it. "In addition to giving our people data they can believe in, we're also giving them the ability, with our reporting functions, to explore the data on their own," says Mentz. "We are seeing a growing number of power users throughout the company. The skills and confidence of our users is growing continually, and a lot of good should come from that."

Shoprite managers also appreciate the visibility into the business they have gained from the data warehouse, and the ease with which they can access the data using the portal and dashboard to check key performance indicators and a world of other information.

"The data warehouse information we have today is incredible," says regional administration manager Nigel Scanlon. "We have so much at our fingertips and so much that we can use to actually help the business run more profitably and more efficiently. With our old system it would take two weeks to see data, by which time it was old and stale. Now we get new data daily. The information we're getting from our SQL Server 2000 data warehouse makes it easier to efficiently run our business."

The better visibility also helps Shoprite more precisely measure sales performance of items on a stock-keeping unit basis, including monitoring promotional results.

"The SQL Server business intelligence tools allow us to react to things far more quickly," says national buyer Mohamed Khan. "We get sales information overnight, and we can actually drill down from the company level to the divisional level, to the store level, and then go down to the SKU level. And if you are running a promotion, you can track the results exactly. We can see what products are performing, and which are under-performing."

The impact is impressive. "We have a core business of about 26 billion Rand (US$4.5 billion) worth of inventory turnover each year that we must manage professionally," Basson says. "The data warehouse we created using a Microsoft solution is helping us manage inventory much more efficiently. The system is so flexible and adaptive to the conditions that we trade in that it will have a great impact on our business operations and profitability. Over the next three years we anticipate inventory savings totalling about US$175 million."