What do we expect from our data warehouse? Well, to put it bluntly, we expect a multi-million dollar inventory reduction in our supply chain pipeline. Our warehouse will significantly recoup its cost in what we're able to do. A wholly owned subsidiary of Sears, Western Auto is the parent company of Parts America, a chain of 640 auto parts retail stores organized into 71 business districts across the United States. That gives us a major information management challenge. In addition to the sheer volume of data we handle, auto parts suppliers face a peculiar problem. Parts America's inventory lists more than 18,300 items, but any given store may stock 45 percent of those parts in quantities of one. Because of the large number of vehicle models from different years, it is not possible to stock several of each part in each outlet. If a customer needs a starter motor for a 1986 Fuego, he wants it now! If we don't have it, we lose the sale. A store may sell just one of those units in eighteen months--but it has to be there.

To complicate things, vehicle models and associated problems differ by region. In our business, understanding how those SKUs behave drives the value of a data warehouse. We used to be a mainframe shop, a legacy, batch-oriented retail systems group. Three years ago we began implementing a midrange platform solution for merchandise management, JDA's IMMS system running on an IBM AS/400. One component we wanted last year was an executive information system. We liked what we saw with JDA's Retail IDEAS (Interactive Decision Evaluation Action Support), a data warehouse developed in partnership with Silvon Software.

Parts America analyzes its warehouse data to manage profit margins by monitoring sales within different product categories. We also examine multiple variables including pricing, make and model information to discover which products sell well together and to detect sales patterns which reveal regional purchasing habits and unexpected seasonal demands. As the corporate information officer, I did not set out to search for new technology. Instead, we looked for new software to match our business functions and found a proven match with JDA.

To study the implications of scaling up Retail IDEAS on an AS/400, Adam Brown, our systems director for merchandising, and Theresa Orzel, lead programmer and analyst, took our case to IBM's AS/400 Teraplex Center. The team spent thirty days challenging the software/hardware combination to prove our concept, scaling up from Parts America's 275GB data warehouse, driving the midrange system to 510GB, the first time an AS/400e was ever scaled up to more than half a terabyte. IBM's Teraplex, a proof-of-concept testing facility for business intelligence systems, is to IBM and its partners what test tracks are to auto makers. IBM spent $47 million to create the most powerful test facilities in the world, where software and platform combinations can be pushed to their limits. Poughkeepsie, New York, is home to centers for IBM S/390 large servers and RS/6000 scalable parallel processors. Our team went to the AS/400 Teraplex Center in Rochester, Minnesota.

Their efforts paid off. We were getting ready to make a sizable investment in this huge data warehouse. I needed confidence that it would work. Our team went there with an in-depth set of performance criteria for our warehouse, including the types of queries we would make, what performance levels we would need, how long it would take to do different functions, and so on.

Our team came up with a great concept: to grow the size of the warehouse at the Teraplex so it looked like our business, without loading new data every time. In effect, the Teraplex gave us a replicate-in-place function.

Working with IBM and JDA at the Teraplex Center, we proved our point: Parts America can effectively double the size of its data warehouse. Our month at the Teraplex was a very valuable experience.

Apart from scaling for size, our team went into the Teraplex experience with what we could call "power-user" demands for query and reporting and managed query functions. Queries ran much faster than the minimum specifications we posted.

The next step for Parts America was to bring in an AS/400 for five weeks of in-house tests, where we confirmed the Teraplex Center results. We proved that we could maintain a batch load transaction volume of 500,000 records an hour, our minimum requirement and we're at 525,000. At one point the figure hit 750K/hour, with no degradation. The figure 525K represents an average feed to the interface of 4.2 million records a week. Given that any one record may update several different fields in the warehouse, those 4.2 million records create an average of 18 million entries a week.

What can we tell others? First and foremost, know your data. Our biggest challenge was the debate between whether the design of the warehouse would affect the size and whether we were right about the configuration of data, or whether JDA [the software company] was right about the configuration of our data. We saved a lot of grief by understanding the architecture of that warehouse and how every data change we made affected its growth and performance.

Next time you visit a Parts America store and the sales clerk says, "You're in luck. We've got one in stock," you'll know better. There's a lot more behind it than luck!

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access