Leading-Edge Retail

Mervyn's gains a decision-support edge with a data warehouse/data agent technology

Marc Demarest
marc@noumenal.com

October 1994

   
          

Publication History

A version of this paper appeared in DBMS Magazine in December 1994 (v7 n13 p78).

          

In theory, the basic business formula of retail sales is deceptively simple: the right amount -- and just the right amount -- of the right merchandise, in the right styles, colors, and sizes, on the right shelves in the right stores at the right time. In practice, this formula requires that decision-support systems (DSSs) in retail organizations focus on the buyers who make merchandise purchasing decisions, as well as merchandise inventory and delivery personnel. These systems must also give these critical business decision makers the ability to change the mix of merchandise in individual departments of individual stores as indicated by advertising campaigns, seasonality, and buying trends.

As a leading retailer of moderate-priced clothing and home fashions to the 15 southern and western United States, Mervyn's, headquartered in Hayward, California, competes in a volatile, trend-driven market. On one hand, traditional department stores are attempting to move down into the middle tier of the clothing marketplace with more promotional pricing; and, on the other (and perhaps more important) hand, large discounters such as K-Mart and Wal-Mart are moving into the middle tier of the market with branded clothing lines and massive advertising campaigns. To defend Mervyn's market position and growth patterns, its management team has increased its fashion offering and improved its ability to manage inventory flow. Mervyn's inventory managers, buyers, and senior managers wanted to be able to do three things: one, analyze the performance of standard and trend items in the stores; two, spot, on a daily basis, upswings and downturns in the performance of trend merchandise; and three, replenish or authorize mark-downs for trend items, as necessary, to take best advantage of the buying trends in the stores. Effective inventory management -- having just the right amount of the right merchandise on the shelves for just the right amount of time -- minimizes overstocking and mark-downs, which boosts profitability.

This finely tuned level of business management required that inventory management and buyers have an identical view of the performance (both in units sold and sale prices) for some 300,000 stock-keeping units (SKUs) in 286 stores. In addition, these managers and buyers needed the ability to associate that information with in-store, print, and television advertising campaigns and advertising zones, as well as perform trend analysis across a reasonable period of time (ideally, 60 weeks). Mervyn's IS personnel also needed to respond to a demand from user communities for drill-down detailed analysis of item performance, what-if scenario evaluation, and exception reporting and handling -- all integrated into the user communities' existing Windows-based desktop environment.

To support these business objectives, Mervyn's chose to push the edges of open systems-based client/server technology by implementing a decision maker's workbench (DMW) for buyers and inventory managers that used Windows-based decision-support tools from MicroStrategy to access an Oracle 7.1 database on a 12-processor Sequent Symmetry 790 SMP Unix server.

However, Mary McCormick, director of Planning and Technology in Mervyn's MIS organization, points out that the project's objective was not to produce bleeding-edge technology, but to provide a platform for quick development and flexible reporting that can adjust to business changes, and produce detailed information on merchandise activity up to the previous day. According to McCormick, "To compete effectively, we must be able to identify trends in the stores immediately, and react swiftly to different sales patterns."

The Company Work Environment

Mervyn's is a division of Dayton Hudson Corp., an 818-billion dollar, Minneapolis-based retailing giant whose holdings include Target, Dayton's, Hudson's, and Marshall Field. Dayton Hudson, whose stock performance and earnings per share reflect its management's commitment to profitability in a troubled market, purchased Mervyn's in 1978.

Today, Mervyn's accounts for approximately 25 percent of Dayton Hudson's annual sales. It is managed with Dayton Hudson's portfolio as a promotional department store focused on "trend-right" clothing and home fashions at competitive prices.

The IS Organization

The IS organization takes its role as business enabler seriously. The IS vision statement reads: "Mervyn's MIS will produce total quality systems in short timeframes that fully support Mervyn's business needs and practices, are in alignment with Mervyn's business vision, and enable rapid and continuous business improvement." This overarching focus on the firm's business objectives is, as Vivian Stephenson, Mervyn's vice president of MIS, puts it, "the price of admission to the game," and it is reflected in the IS organization's latest successful project: the DMW.

The DMW's goal, according to Stephenson, is to provide "state-of-the-art" systems to the backbone of Mervyn's business: the buyers and the merchandise planning and logistics organizations. Mervyn's is focused on getting the right information to the buyers so they can make the right business decisions in the minimum amount of time.

According to Sue Little, manager of Merchandising Planning and Logistics Systems, this meant a common view of store transactions across the buying and logistics organizations. "It is frustrating and often impossible to make decisions when buyers are looking at everything from a dollar perspective, Inventory Management is looking at everything from a unit perspective, the data itself is incomplete, and there is no common view to tie the two kinds of numbers together easily."

"Easily," in this case, means a combination of factors: rapid response to queries (measured in seconds and minutes of end-user response time), the seamless integration of the DSS client into the Windows desktop environment on which the Mervyn's architecture is based, and the client tool's ability to support not only simple data extraction and analysis, but complex forecasting models and exception reporting and handling. The existing reporting system DMW replaces paper-based reporting, structured mainframe reporting, and manual analysis.

The Project

The DMW system uses a fairly typical data warehouse-based architecture to make transaction-level data from Mervyn's point-of-sale (POS) controllers useful and legible to buyers, logistics personnel, and Mervyn's senior managers. The steps proceed as follows (Figure 1 shows the system configuration):

1. POS data from Mervyn's retail stores "trickles" into an IBM mainframe in Merryn's Plano, Texas data center several times a day via an X.25 satellite-based network.

2. That data -- transaction-level detail from each store's POS controller, summarized by SKU store -- is downloaded on a daily basis from the mainframe to the Sequent Unix server (also located in Plano) via TCP/IP.

3. On the Unix server, the raw production data (approximately 500GB) is loaded into a DSS schema with sophisticated summarization and aggregation tables, built on top of a fault-resilient 750GB disk subsystem. The parallel loading and index-building features of Oracle version 7.1 are fully used, and were a main reason for Mervyn's selection of Oracle as the DBMS, according to McCormick.

4. Once updated, users at approximately 300 Windows desktops connected by TCP/IP-based LANs can access the Sequent/Oracle data warehouse.

Mervyn's Architecture

Figure 1 -- Mervyn's Architecture

While the Oracle/Unix combination provides the server-based query processing performance required by high-use DSS environments, the heart of the DMW environment -- from the business user's perspective -- is a sophisticated, customized DSS client application that is based on MicroStrategy's DSS Agent, a general purpose, ODBC-compliant Windows data agent.

Carol Pecoraro, the project manager responsible for the design and implementation of the DMW environment, is bullish on DSS Agent. "We never had any intention of writing the DMW client from scratch. There's no leverage for us in that. We began the pilot phase of this project with MicroStrategy's previous product, EISToolKit. When MicroStrategy showed us their plans for DSS Agent, we recognized that it offered us a kind of flexibility and power that was unavailable elsewhere in the commercial market. So we took a chance on an unreleased product, and it paid off."

DSS Agent is an intelligent data agent: a data-access application in its own right, as well as a powerful data-access engine for value-added in,house application development. Using an embedded multidimensional model and its own metadata dictionary, which is typically stored in the data warehouse, the product can map data to user-manageable categories. DSS Agent accesses this data through ODBC and exposes a set of interfaces, conveniently accessed via Visual Basic and other rapid application development tools, to let application developers focus on the functionality and depth of the application, not on data access and delivery. Treating data in a multidimensional model (that is, as a series of facts or data items accessed by a combination of dimensions such as store or region, advertising zone or campaign, or merchandise category) substantially eases the integration of the transaction-based data into the user communities' business models.

The DSS Agent-based client application focuses on drag-and-drop trend analysis, merchandise performance analysis, and inventory stock management, and provides users with the ability to customize their views of information and ways of performing their analyses through custom filters that save sales, inventory, financial, and vendor criteria of interest to the user. In addition, the DMW application will use DSS Agent's alerts and agency capabilities to sift through the data warehouse for exceptions and notable trends, and notify appropriate users when the data changes significantly. DSS Agent also enables the DMW application to integrate seamlessly into Microsoft Word, Excel, and Mail, making the sharing and distribution of analyses a simple, intuitive process.

"Buyers and inventory analysts can now look online, and see, by advertising zone, how sales of fleece products such as exercise clothing peak and trough over the fall season, or how they vary across regions or stores," says Pecoraro. "In the past, this would have been a long and somewhat aggravating process that required the analyst to consult both online and offline data sources. But because the DMW marries the query processing power of symmetrical multiprocessing hardware and software with the sophistication and ease of use of DSS Agent, we've improved both the quality and depth of the decision-making process, and cut the cycle time required to perform the analysis from hours to minutes. That's timely decision making."

Design and Programming

The project took eight months from conception to implementation, according to McCormick. "The results were immediate. [Mervyn's] now has a system that is flexible, visually appealing, and easy to use, which in turn produces analyses that are intuitive and easy to understand."

The DMW is now in its second iteration. Mervyn's is still training its user communities to use the DMW, but it is an ongoing process that will run in parallel with its design and development activities. Mervyn's is already developing the prototypes for DMW2.

According to Vivian Stephenson, the first DMW is a stepping stone, not an end-state. "We know that we must develop an environment that supports the entire flow of work in a user community as soon as possible. That includes not only answering business questions, but promoting information sharing and linking business analysis to the need for changes in the current state of the business. In other words, we have to link our DSS capability to both messaging and workflow, in order to support the business teams and our transaction-processing systems, and [we must] be able to close the business loop in a managed, monitored way."

For Carol Pecoraro, the first step in this advanced, closed-loop business processing is the transformation of DMW from a pull-based environment (in which data is available as requested, when requested), to a push-based system (in which data appears on the desktops of Mervyn's employees on a regular basis just as it is required). "We're working, right now, on a newspaper model -- an extension to DMW, using DSS Agent, that considers a user's role in a business process, and delivers the necessary information at the beginning of every day."

"DSS has never been characterized as mission,critical," McCormick points out, "but, next year, this system [will become] mission-critical when we provide the newspaper model and integrate transaction processing into the analytic environment. That means that we will have to reevaluate our enterprise infrastructure to ensure that we can meet our quality-of-service commitments to the business."

Onward and Upward

This year, the success of Mervyn's application of client/server technologies to business problems has helped Mervyn's garner a ComputerWorld/Smithsonian Award for innovative use of information technology. In a time when in-house client/server projects are overrunning budgets and schedules, if not dying out altogether, Mervyn's business-centered approach to the DMW project exemplifies at least three key rules of successful client/server DSS development:

  • know the business and its goals;

  • partner with reliable suppliers, and integrate them into your working team; and

  • manage the expectations of your user communities and the changes in work practices associated with your projects.

"This kind of technology, applied to business problems, drives you into change management." remarks McCormick. "If you want a successful project, you need more than excellent technology and a good project plan. You need to manage the change that technology brings to the work environment."

Vendors Mentioned

MicroStrategy Inc., One Christina Centre, Wilmington, DE 19801; 800-927-1868, 302-427-8800, or fax 302-427-8810.

Oracle Corp., 500 Oracle Pkwy. Redwood Shores, CA 94065; 800-672-2531, 415-506-7000, or fax 415-506-7200.

Sequent Computer Systems Inc., 15450 S.W. Koll Pkwy. Beaverton, OR 97006-6063; 800-854-0428, 503-626-5700, or fax 503-578-9890.

          

Last updated on 06-22-97 by Marc Demarest (marc@noumenal.com)

The text and illustrations in this article are copyright (c) 1994 by DBMS Magazine and Marc Demarest.

The authoritative source of this document is http://www.noumenal.com/marc/mervyns.html