The world of retail is harsh and unforgiving. When it comes to products that consumers want, if you haven't got them on the shelf you can't sell them, and if you can't sell them there's no point stocking them. But what can you sell - or not - and when and where and why? And have you got it to sell in the right places at the right time in the right quantities to maximise sales and profits?
Matching supply to demand is a fine art and for fashion and sportswear retailer Blacks, an increasingly critical key to making that match ever more successful is its data-warehouse. Here, the crucial link between what comes in and what goes out is made, keeping the supply chain as close to customer demand as possible.
"It's a juggling act," admits Blacks' management information systems manager John Horner.
The company wants to keep stock held at its 400 retail outlets to a minimum so that the maximum amount of space can be given over to the sales area for the customers to browse, yet the last thing it wants to do is run out of stock that it could happily sell that day because it's sitting in a distribution warehouse waiting to be delivered to the shop.
Even though staff are trained to cross-sell where possible, if the first requested item is out of stock, running out of anything, says Horner, "is a potential missed sale". An opportunity to make a sale is gone, possibly forever.
Tightening the loop between stock walking out of the shop and being replenished round the back is the datawarehouse, taking in feeds from the company's operational transactions systems.
"The main data it takes in is firstly the daily electronic point of sales (Epos) right down to the level of the sales receipt identification so that we know what people are buying," says Horner, "and next the distribution centre stock data. All the tills data is backloaded onto the server at head office so we can do the stock and distribution management."
Taking the two data sources together gives Blacks an accurate, timely view of stock flow in and out in great detail.
"On a daily basis we can see what is in stock, and in what sizes and colours and so on, and at the end of the day we know exactly what is in each branch," says Horner.
The trick, obviously, is to stock exactly what will sell out, but no more and no less. But how to know what that magic number is? Like scientists and historians, retailers look to what has already happened to find a clue as to what will happen next time. By interrogating the Epos and supply chain data in the datawarehouse, Blacks can watch the patterns of ebb and flow forming.
"Every weekend we analyse which branches ran out so we can look at replenishment levels and build a model," says Horner.
"It's difficult to model," he admits, and the priority is to select the company's flagship stores and ensure that they, at least, do not run out of stock.
Each store has a stock maximum for every product sold. "For example, a shop in Newcastle will have three pairs of trainers, and if it sells two it receives two more," says Horner.
However, he adds that every store gets a daily delivery from Monday to Friday. What Blacks wants to avoid is a situation in which a branch is out of stock after the Friday delivery and before the Monday one, as it could be selling that product over the weekend.
"If a store sells three pairs of that trainer on Thursday it won't get another three until the Friday afternoon, and if it sells out by the end of Friday it won't have any for the weekend," he says.
However, he says that if they interrogate the datawarehouse and then build up sales and stock analysis the company should be able to build a replenishment model for each branch. Moreover, the system itself "should know when the branch needs restocking".
At this point, the warehouse moves into that much-desired realm of operational datawarehouse - not just supplying information for off-line analysis, but actually feeding back into the transaction systems and triggering the replenishment systems automatically.
Even so, Horner points out that the warehouse is only refreshed nightly at the close of business when the transaction systems are flushed. "We don't know what we've sold until the night," he says.
Changing over to real-time online Epos feeds could, he believes, bring disadvantages as well as the advantage of having a real-time picture of how stock is moving. "What would we do if we lost the connection?" asks Horner.
He says the whole idea of having automatic stock replenishment is very attractive from a business point of view.
"Our business people are all for it because it frees up time. We have about 200,000 lines of products, taking into account different styles, colours and sizes, in our distribution centre."
Perming 200,000 variants by the hundreds of outlets to ensure each store has its maximum stock level for each product line makes the task very time-consuming - and tedious.
"It takes ages and it's error prone," says Horner.
It's also not the most interesting job in the world, so it has an impact on staff retention. Automating the process would enable staff "to concentrate on analysis", Horner says.
He believes datawarehousing has a role to play in improving staff morale.
"I think one of the main benefits that is not perhaps widely appreciated is that this sort of system can help your user base to do their jobs better and this can be a great morale booster aiding staff retention, which is important for long-term stability and progress."
In terms of the kinds of benefits that company accountants, rather than personnel managers, like to see - the kind with pound signs attached - Horner identifies two key winners when it comes to getting return on investment from the datawarehouse.
"It's saving a hell of a lot of time," he says. "It's cutting tasks by 50%."
The second major bottom-line benefit is that the company can avoid being out of stock at key selling times, like the weekend.
"When we first started we found that around 100 stores might run out of stock at the weekend. After six weeks it's down to 50. That means we're making 50% more sales at the weekend - that's an awful lot of money," says Horner.
Moreover, of course, the on-going analysis of sales trends and how consumer tastes evolve means that the datawarehouse also plays a key part in range planning. The information in the datawarehouse is not just useful to Blacks, but to its suppliers as well, who obviously have a clear interest in how their products are selling.
"We do provide some information back (to suppliers such as Nike) and it's clearly an area we're looking at," says Horner.
The nuts and bolts of the datawarehouse system at Blacks
Blacks' IT is based on open systems. Transaction systems such as purchasing, distribution, stock management and sales, plus the financial systems, all run on Hewlett Packard HP/UX Unix servers, and the predominant database architecture is Oracle 7. The 150 gigabyte Penine Galaxy datawarehouse, on which Blacks spends around 10% of its IT budget and which went live in March 1998, runs on a separate Unix server, and was migrated from Oracle 7 to Version 8 last year. Although held in a single database as sets of tables and scripts, the warehouse consists of a collection of data marts, such as the till sales datamart and the branch stock datamart - all holding different information at different levels of granularity.
"They can be brought together," says MIS manager John Horner, "but they need to be stored in different marts because they are in different schemas."
The warehouse is accessed by users running Business Objects on their desktop PCs. Business Objects fires users' queries to the datawarehouse, retrieving the requisite data from the data marts, processing the data on the server to yield the answer and returning it to the user's PC.
"There's a year's worth of data (in the warehouse) making 700 million rows of data, so picking out, say, 100 rows, is a sizeable chunk of processing and it's done on the server," says Horner.
When the warehouse was first deployed, Business Objects was well suited as the means of user access, and still is for the 25 core users.
"In the main these are the merchandisers - they are the people who have to get the right stock to the right place at the right time, who need to know who's buying what, at what stores," says Horner.
But increasingly Blacks wanted to be able to make the data in the datawarehouse available to a larger range of users, most of whom would not need to do heavy-duty analysis and who therefore probably did not need the very rich range of functionality that desktop Business Objects provides.
"It would be a costly overkill," says Horner, to deploy Business Objects on the desktop of every employee who might need some of the datawarehouse information, some of the time.
"We needed Business Objects at first, but we don't need to mass-distribute it," he points out, particularly not when it came to rolling it out to all the stores. Licence costs apart, "rolling Business Objects out to 400 stores would have been a maintenance problem," says Horner. With only eight staff in IT it would inevitably have meant contracting out maintenance.
"There was no need and no benefit" to mass deploying Business Objects, he says bluntly.
In order to open the warehouse to the masses, therefore, Blacks decided to adopt Web delivery. At the time, Business Objects did not support Web delivery, so Business Objects' resellers, Acuma, which had implemented the query and reporting tool for the initial core of users, installed its Information Distribution Server (IDS) software. This sits on its own Windows NT server, together with a Business Objects client (although IDS can also take in data from data feeds not in the warehouse).
IDS comprises a management console, allowing users to specify every aspect of how they want information delivered, including content filtering options and output format. The report generation and distribution is driven by the IDS Engine, comprising a core set of functions supplemented by a series of plug in processes that allow IDS to accept input from different sources than the datawarehouse and output to a number of delivery channels.
IDS can be set up and scheduled to run reports in combinations of formats, including Business Objects, Word, Excel, Adobe Acrobat, HTML and rich text, and distributed to different people at different times. Some of the information put upon the Intranet can be seen by everyone, some only by designated users depending on their security levels. At the moment the Web server sits on the same box as IDS, but, as the Intranet, grows it will probably get a box of its own, says Horner.
"One of the key benefits is that we can deliver (datawarehouse information) to our regional and area managers who could never have seen such reports before, such as our list of top 20 best sellers, which we analyse on a weekly basis", says Horner.
In the next six months this will be extended to the 400 store managers using thin client technology, without the need for installing PCs in the branches. Thin clients will allow the company to run the software on the head office servers, but allow store staff to access the Intranet.
"They'll be able to get on board and communicate with head office - up until now they've only had the Epos till providing a very limited e-mail," says Horner. "PCs would be a nightmare to maintain (in 400 stores)."
"IDS has taken us to the next level of data warehousing."
Blacks is the UK's largest retailer of leisure-related goods, it has about half a dozen retail fascias, including First Sport, Pure Woman and AV. It turned over £200m last year and has 400 retail outlets. There are eight people in IT and three main IT departments - data warehousing, PCs and networking, and the main transaction processing systems. Blacks recently acquired the Outdoor Group whose most famous fascia is Millets. At the moment, the two IT infrastructures are quite separate.