DATA WAREHOUSING: TWO VIEWS
Byline: MATT NANNERY
NEW YORK — Two information systems executives offered separate takes on data warehousing at the National Retail Federation’s annual convention held at the Marriott Marquis hotel here recently.
One stressed his company’s desire to make the data warehouse the prime source of information for forecasting, accounting and other corporate functions; while the other enumerated a litany of short-term, bottom-line benefits currently being exploited by buyers and logistics staffers via decision-support tools.
The speakers were Steve Junk, vice-president of retail systems at Sears, and Trevor Dukes, systems development manager at W.H. Smith, the British-based bookseller. While it would be incorrect to say that both men did not express some common goals for the data warehouses now acting as repositories of POS and other data at their companies, their presentations nonetheless played out in rather marked contrast.
It was W.H. Smith’s Dukes who homed in on the easy-to-grasp, bottom-line benefits of real-time access to POS data by buyers and merchandisers. While Junk offered compelling examples of similar benefits at Sears, he laid out a more grandiose corporate strategy for using the information in that company’s data warehouse to feed a new generation of operational systems at Sears.
W.H. Smith: Rewriting the book on efficiency
“Our buying and merchandising teams were quick to seize the commercial opportunities that the decision-support system provided,” Dukes explained, his accent slipping occasionally into street-smart cockney.
“The system gave us a more detailed understanding of the marketplace than our competitors possessed. It provided us with the opportunity to outmaneuver them. Our people can now tailor product ranges to individual store profiles. They can ID potential out-of-stocks, thereby preventing lost sales. They can ID opportunities for stock redeployment, thereby reducing stock holding and depreciation costs. And they can actively manage products that aren’t performing well out of the range.”
The system Dukes is referring to is the data warehouse W.H. Smith set up in early 1994. A sort of massive parallel database, a data warehouse operates independently from the separate operational databases from which it draws and then pools data. Since information from individual operational databases is copied into a data warehouse at regular intervals, users from any department could access and cross-reference information there without disrupting the critical departmental applications the independent donor databases were designed to support.
Dukes said W.H. Smith’s need for parallel processing was the direct result of its switch to electronic point-of-sale systems, EPOS as they are called in England. That switch occurred in 1985.
“Ten years ago, we were only able to manage retail at the product-group level — books, music, cards, etc.,” Dukes recalled. “There were 25 groups. But when we implemented EPOS, we suddenly found we could analyze the business into 50,000 individual line items. We were piling up so much data that, for a while, I couldn’t figure out if we were in the retail or the data-collection business.”
Dukes said W.H. Smith found itself in a similar situation to many other retailers who collect POS data: the extremely frustrating predicament of knowing you’re sitting on a great deal of critical fodder for decision making, but possessing no fluid means of accessing and evaluating that information.
“Finally, in 1989, after our databases had been growing at a faster pace than our stores, we consolidated all the information into one central database,” Dukes said. “Looking back, that was probably our first attempt at embracing the data-warehouse concept. This consolidated database gave us many operational advantages, particularly in managing our distribution center.”
He added, however, that accessing the information was still difficult — that W.H. Smith’s first central database really wasn’t a viable decision-support tool for the large majority of users. Finally, in November of 1993, the company set up a prototype data warehouse.
“We focused on supporting range and stock management,” Dukes said. “The three product teams chose for the trial started to achieve significant benefits. The system provided them with rapid access to stock and sales data. It allowed them to do complex queries on very large amounts of data. The system delivered an immediate payback.”
So evident were the short-term benefits, that Dukes and the IS staff had very little trouble convincing W.H. Smith’s top management to support the expansion of the data warehouse.
“We got approval and had the system up and running by the beginning of June 1994,” Dukes said.
He added that training users is important if they are to get the most out of the system as a decision-support aid.
“Crucial to the success was the intensive training given to the buying, marketing and logistics teams,” Dukes said.
He spelled out some specific successes W.H. Smith attributes directly to the data warehouse.
“By undertaking an analysis of sales by price points of all single greeting cards, the greeting card team was able to carry out a slight realignment of price points — reducing the number of variations and generating an increase in profits of $750,000 per annum,” Dukes said.
“Using the system to understand the performance of the classical cassette range, that team completely restructured the range to reverse a year-long decline from minus 15 percent to a plus 35 percent increase. This improvement has already generated $450,000 of additional profit.”
The data warehouse also allowed W.H. Smith to take a tough negotiating stance with its suppliers.
“Having a more detailed knowledge of the marketplace than our vendors allowed us to adopt a stronger negotiating position,” Dukes said. “Using the system to estimate the lost sales from a late delivery of calendars and diaries from a specific vendor resulted in the team receiving a check for $100,000 as compensation. And the biography and literature book teams managed to negotiate returns to publishers of excess, deleted and slow-moving stock to the value of $800,000.”
There data warehouse also wrought benefits at W.H. Smith that are more difficult to place a dollar value on.
“We have been able to eliminate virtually all paper-based reports to product teams,” Dukes said. “And this has been achieved with surprisingly few objections. That is probably due to the fact that we involved the product teams in the design of the systems. That involvement has been a major contributing factor to the success of the decision-support system.”
Dukes added that many involved in duties other than buying or logistics at W.H. Smith are now clamoring for access to the data warehouse.
“Although the system was built to handle requests from the buying and logistics teams, we are now finding that some of the heaviest uses is coming from other areas of the business, notably finance. And I currently have a queue of prospective users ranging from regional sales managers to other businesses within the organization,” he said.
Dukes enumerated a number of future goals for W.H. Smith that officials hope the data warehouse will help them reach.
“Now we want to use the warehouse to generate more profit from existing space, to improve the flow of goods through the supply chain and to accurately forecast the effect of promotional activity,” he said. “We want to monitor and evaluate competitive pricing activity and, most importantly, to link our customers to our products by analyzing our transaction data.”
Sears, Roebuck: Looking beyond decision support
Sears’ Steve Junk agreed with Dukes that the data warehouse offers real-time access to information that can help buyers and others make intelligent decisions on the spot. Sears, however, has more grandiose plans for its data warehouse, according to Junk.
“Decision support is the jumping-off point for most data warehouses, and ours does serve that purpose,” Junk said. “But, at Sears, the data warehouse will become the one and only feed for sales, margin and inventory information, and the general ledger systems. Putting the stock ledger on a data warehouse will allow us to switch from the retail to the cost method of accounting, if and when we choose to make that switch. The accounting accuracy that has to be built into the system is, therefore, a degree higher than many decision-support systems.”
Junk said accountants at Sears will be using information from the company’s data warehouse to “close our books at the end of the month.” Consequently, the company is working to ensure that the data in its warehouse is clean and accurate and that all users are in inputting information in standard formats from department to department.
“Dirty data has been a problem,” Junk said. “It’s amazing — the lack of quality, accurate data in our legacy systems. When you track data through 20-year-old systems, you see all the Band-Aids that were put on.”
But Sears is not only cleaning up data from departmental systems and making that data available through the data warehouse. As Junk mentioned, the company is turning the tables on those legacy systems. In the future, operational systems tied to individual departments will draw their data from the company-wide data warehouse. This will mean departments will be working with data that’s been compiled in a standard format, free of all the Band-Aids that crept into Sears operational systems over the years.
Sears has dubbed the project the “Strategic Performance Reporting System,” or SPRS (pronounced “spurs”).
“The intent of SPRS is to replace all our existing reporting systems with on-line viewable screens,” Junk explained. “This is not to say we are going to become paperless, because I don’t think we ever will. But we will not only use our data warehouse as a repository but as the core feed for our next generation of operational systems.”
Junk insisted that it was necessary to have the data warehouse up and running before the new operational systems are put in place.
“Just the existence of an information warehouse should cause a company to rethink the capabilities of its operational systems,” he said. “We said that if we want to revamp our operational systems, we want the core infrastructure of a data warehouse in place before we even attack that piece of the application portfolio.”
Junk said Sears sees the project as a base for a shift in its corporate structure.
“We recognized early on that the reinvention of a company requires a revamping of its systems,” he said. “Sears wants to push decision-making down to the lowest level of competency. Unless you have the data available that a data warehouse can provide, you are going to do a less-than-adequate job of getting that data to the merchants in your organization that need to understand those intricacies of thousands of customers in thousands of different markets.”
But in order for lower-level manager to make smart, snap decisions, Junk said Sears makes accessing the data warehouse simple and intuitive. If it isn’t, it’s Junk’s belief that many would simply not use it.
“Companies need to raise the competency of people at those lower levels,” he said. “And they can do that by providing those people with correct, actionable data in an accessible format. That’s what a data warehouse can provide.”
And that was part of the problem with the company’s legacy systems. Difficulties in accessing data and churning our reports meant decision makers at Sears were usually looking at “summary-level” data — data that often made it next to impossible to pinpoint the causes of problems and respond effectively to them.
“We had to go quickly to summary-level data because of costs and technical capabilities,” Junk said. “But the concept of a data warehouse is to keep the information at the lowest or the ‘atomic’ level of data, which allows for tremendous flexibility down the line when you want to look at that data in different ways.”
Currently, 1,800 users at Sears headquarters can access its data warehouse. And giving them digestible information is a priority.
“Our data warehouse had to be easy to use,” Junk said. “It had to be intuitive. We wanted to train 1,800 people in 90 days, but not just to train them, we wanted to make sure there was a high degree of acceptance and usage by these people.
“It was critically important to us to give them something familiar, so, when they log on, the first thing everyone sees is our national daily sales report. Intuitively, they immediately knew what the numbers meant — those numbers matched exactly to a report they had seen before and that made them comfortable. “
But that’s where the familiarity with the summary-data reports of the past ends. All users had to do was scratch the surface to realize that their familiar national daily sales report had metamorphosed into a much more potent tool.
“The beauty of the way the national daily sales report is presented now is that the users can drill down and spin it any way they want,” Junk explained. “They can even import the information into a spreadsheet program like Lotus.”
Even though Sears’ plan involves eventually using the data warehouse as the “data feed” for its operational databases, users are already using the data warehouse as a decision-support tool. Junk said quick access to information is helping them get the biggest bang from promotions.
“We run a heavily promotable business in many of our lines,” he explained, “and getting to reliable information within the promotional period, not after, is critically important.”
Ready access to information helped the retailer weather the blizzard that hit the East Coast in January. The company had dropped a circular just before the storm hit and was able to monitor the effect of the storm on promotions and soften its residual effect on inventory planning.
“Tuesday, when the merchants walked into our offices in Chicago,” Junk said, “they knew by store, by state on the East Coast exactly what was happening so they could handle our inventory positions according.”
The data warehouse also lets Sears respond effectively when other chains and category killers set up shop near its stores.
“We knew our sales were affected by 25 percent when a competitor enters one of our markets,” Junk said. “But to truly understand exactly how those sales are affected by SKU, by day, by price point and by color is something we weren’t capable of.
“Now, with some competitors, we know exactly what happens by item, by price point — and we can position a competitive response when they come into our markets by advertising, by pricing and with different assortments. That softens the blow when those competitors enter a market.”
Junk explained how merchandisers can utilize the data warehouse.
“Say I wanted to look at the Canyon River Blues line. Some of the merchandise is in women’s, some in men’s and some in kid’s. I couldn’t easily monitor how a line is doing across departments. Now, the user can easily fashion a request that picks out some items in one department and some in another and just includes the stores he wants. He’s not restricted to the merchandise hierarchy, or days of the week or specific stores. That’s the power of the data warehouse.
“Now we know that a product return rate may be eight percent, but may be as high as 14 percent in another part of the country, and we can do something about that,” Junk continued. “This kind of information was always there, but the ability to get to it in a reasonable amount of time wasn’t there. It’s only when people under time constraints can get information relatively painlessly are they able to make the best informed decisions.”