At Army, AF Pxes, cleanup duty starts with data

The Army and Air Force Exchange Service (AAFES) is becoming a big data warehouser. That
means big-scale house cleaning of the data up front.

As AAFES officials put their retail point-of-sale data on line in a 400G data
warehouse, they've learned first hand how small data-entry inconsistencies can distort
their sales forecasts--an important part of the warehousing project.

They've even found inaccuracies in the universal product codes that manufacturers affix
to products.

AAFES built its first sales data warehouse two years ago. Evolution of warehousing
technology "has caused us to monitor our data processes a lot more closely,"
said Larry Morris, a supervisory business analyst with AAFES. "Most people would be
surprised at how much bad data they've got."

AAFES, a $7 billion-a-year retailer, now runs all its point-of-sale data through an
elaborate series of mainframe validation checks before it enters the data warehouse, an
NCR Corp. Teradata database hosted on a 56-processor NCR 3600 Series server. The summary
table is populated with more than 250 million rows of data, Morris said.

To minimize the transmission traffic from each store to headquarters at night, the
AAFES system captures descriptive data from four or five different IBM Corp. IMS
databases'MDBO''MDNM'. The data validation and edit processes now take a full day to
complete, introducing a day's lag into the currency of the warehoused data.

"We'll trim down some of that processing as we refine our editing processes and
clean up our data,'" Morris said. Even though AAFES has been warehousing retail sales
data for two years, "we're still finding out a lot about our own data."

The 400 buyers and merchandise planners who use the warehouse can look at retail sales
data for any group of stock numbers, stores, suppliers or time periods they select. Their
goal: to get rid of slower-moving items and increase the inventory of better-selling,
high-margin items.

The desktop application that lets them do that is the Retail Access Model, written with
Microsoft Visual C++ and Visual Basic to run on desktop 486 machines under Windows for
Workgroups 3.11.

The AAFES warehouse stores about two years of weekly sales data and 60 days of daily
sales data. Buyers at headquarters in Dallas can look at the sales data worldwide or by
theater, region or military exchange-- "all the way down to individual stores,"
Morris said.

"The front end is so flexible" that buyers can find whatever information they
want themselves "without having to wait [for someone else] for a report," he

Even with only anecdotal evidence so far, Morris expressed confidence that AAFES is on
the right track investing in the data warehouse to boost sales. "There's been no
doubt about what we've seen so far," he said. "Profit has to be a motive,"
because one role of AAFES is to generate funds for military welfare and recreation

With the sales data warehouse project in its second and third phases, AAFES is adding
inventory data and developing sales forecast models. "You really need to bring in
inventory data to know how well you're managing," Morris said.

But programming the sales forecasting into the mix is more difficult, because so many
factors come into play. "You have to make the sales forecast far enough in advance so
you can place the order to arrive when it's needed," he said. AAFES will try to
predict sales for a particular week up to 16 weeks in advance.

On the massively parallel Teradata host, data for any single table might be stored on
four separate disk drives managed by a single processor. "When you submit a query,
all the processors share the work," Morris said. "People can ask for what they
want and get it back in seconds or minutes."

Stay Connected

Sign up for our newsletter.

I agree to this site's Privacy Policy.