It’s the dream of all IT managers — to create a system from scratch. That’s exactly what Neville Brown, IT manager for The Warehouse, and his staff have been doing for the past four months. The project is a $2 million data warehouse solution to improve inventory management and stock replenishment, and improve the collection, storage and analysis of data for such uses as analysis of customer returns, sales reports to buyers and rate-of-sale information.
The second largest retailer in New Zealand, The Warehouse opened or redeveloped seven stores and three Warehouse Stationery outlets in its 1997 financial year, increasing its inventory by 4%, or $3.7 million. Having achieved a 17% increase in sales for the period, Warehouse directors are comfortable with the amount of inventory on hand, but the company is not standing still on the matter.
Brown, formerly IT manager at the ASB Bank, says the data warehouse will provide a consolidated view of operations and enable comprehensive analysis.
“It’s not replacing anything, it’s absolutely brand new. It will take feeds from major systems, such as our TUI inventory database, warehouse management, finance and point of sale. The users will be the various operational departments such as our merchandise or buying office, marketing, finance and distribution.”
The huge processing power required for the data warehouse is provided by an NCR 4700 computer (one of the first to be sold in the world) with two nodes, each node comprising four Pentium Pro processors and 168 Gb of disk running Unix. Based on an SMP (symmetrical multiprocessing) architecture, the system is highly scalable — in the US, retail giant Walmart runs 97 nodes.
Sitting on the 4700 is NCR’s relational database Teradata, which, optimised for data warehousing, can run highly complex queries in parallel. The Warehouse IT staff say the system can load one million rows from outside the database to inside the database in five minutes, and can handle 16 table joins including multi-million row tables. The database is accessed via an NCR 4300 running Information Advantage DecisionSuite and WebOLAP.
The Warehouse is now running its first queries, the results of which will be used for Christmas planning, distribution and allocation of product to stores.
Meanwhile, users are being trained on the query, analysis and reporting tools and it is hoped suppliers will have access to the system also.
“The toolset is Web enabled and we want to release it to suppliers. To begin with we’ll just go to trial with a few but we’re working towards paperless trading and this is part of our e-commerce initiative.”
Brown says he found the arguments for data warehousing extremely compelling.
In a study of 62 cross-industry companies, research company IDC found that the ROI for data warehousing over three years averaged 401%. The average pay back was 2.3 years on costs of $US2.2 million.
In a study of more than 20 NCR retail data warehouses, NCR found the ROI ranged from 300% to 1000% over one to two years.
According to NCR, the main reason companies implement a data warehouse system is to reduce slow moving inventory, then to reduce out of stock position and non-promotional mark downs, to increase promotion effectiveness and lastly to improve vendor participation.
“A data warehouse can reduce slow moving inventory by 25-50% in three years, by identifying low margin products as a percentage of total business, by product, by store and adjust or eliminate low performing margin items,” says Don George of NCR.
“It can also optimise the price charged for competing products by tracking name brand and private label products by price and margin, and by neighbourhood location. Plus it can identify and eliminate slow moving items by tracking and comparing sales to plan, and their percentage contribution to the overall business, by margin dollars, by neighbourhood store.”
An interesting feature of the project has been the speed at which it has been implemented. The Warehouse issued a request for proposal in July and four months later was running its first queries against the database.
“We sent out an RFP to eight suppliers with a number of requirements. The successful party had to be global, it had to demonstrate it could bring benefit, it had to have technology that works, a proven skill set and expertise, and it had to have a team that was going to work with our team.”
Two main prospects emerged, Unisys and NCR, of which NCR “demonstrated strong capability in the areas we wanted. Their upgrade path is also very solid. NCR shared their development future with us and we’re very comfortable with it.”
A one month evaluation period followed which involved building a prototype to see what NCR knew about retail and how its team would work with The Warehouse team. Brown says the time frame for the proof of concept period was one of the most stringent aspects of the project. Eventually a team of eight to 10 people from both companies worked on the solution.
“A great example of the innovation shown by some of the guys is that they have designed a trickle feed for the loading system. Instead of doing it in batches, we’re loading on a very regular basis. This improves the timeliness of the data. As far as we know this hasn’t been done anywhere else.”