Incoming!

Contributing Writer, Computerworld |

Once a week, Jim Revak, IT manager at The Sherwin-Williams Co. in Cleveland, faces the daunting task of collecting business intelligence from 15,000 third-party retailers that sell the company's paint products and downloading that information into a data warehouse.

Customer demographics and data on 4,000 products arrive from Sherwin-Williams' major retailers in disparate formats. Add to that the handful of external data the company collects from other sources, and you've got a data warehousing nightmare.

"The good news is, you have a lot more information on how your products are doing out there with your customers. But the bad news is, it's so overwhelming. We don't have enough arms and legs" to analyze all the information, Revak says.

Welcome to the world of external data overload.

External information in corporate data warehouses has increased during the past few years because of the wealth of information available, the desire to work more closely with third parties and business partners, and the Internet, says Warren Thornthwaite, co-founder of data warehousing consultancy InfoDynamics LLC in Menlo Park, Calif.

Don't be fooled by the abundance of "reliable" data for sale or the deceptive simplicity of integrating it, industry observers caution. Not only does the additional data consume disk space and go largely unanalyzed, but the amount of "pick-and-shovel work" required to clean up all of those files also increases exponentially for the IT department, says Revak.

The key is getting the data from trusted sources and creating a full design and development life cycle.

Acquiring the Data

Krispy Kreme Doughnuts Inc. in Winston-Salem, N.C., sells more than 2 billion pastries annually at 218 franchises and at thousands of grocery-store bakery cases across the country. But keeping track of its sales at myriad stores became a challenge.

So Krispy Kreme acquired external data from Deerfield, Ill.-based Efficient Marketing Services Inc., which takes checkout scanner data from grocery stores, refines it into codes compatible with Krispy Kreme's data warehouse and transmits it via file transfer protocol to Krispy Kreme each Friday morning. That information helps the company speed up its billing cycle and understand how its pastries get lost or stolen. It also helps Krispy Kreme reach more customers and markets, says Frank Hood, the doughnut maker's vice president of information services.

Each grocery store could have been asked to send its scanned data directly to Krispy Kreme in return for information on how that store is doing compared to its competitors. But Krispy Kreme's IT executives preferred a faster integration method purchased by subscription from a third party.

"Using an external group to help you manage that process allows a more consistent data stream," says Hood. Plus, the third-party provider has contingency plans in place in case data is unavailable, he adds.

Other companies, like Sherwin-Williams, prefer to get their data straight from the source. Its major retail partners, including Wal-Mart Stores Inc., Kmart Corp., Target Corp., Lowes Co. and The Home Depot Inc., "have IT departments big enough to consolidate point-of-sale data, with departments to break it down," Revak says. For Sherwin-Williams, "It all ties into the relationship sales and marketing people have with their customers," he says.

In either case, Thornthwaite advises that companies find a trusted resource to own half the process and work as partners on consistency, formatting, reliability and contingency plans.

Timing is critical in Krispy Kreme's electronic billing cycle, so if data doesn't arrive, its homegrown application will identify it as missing or kick out any exceptions. The IT department can then decide whether to substitute a figure based on an average. "But usually the provider has come back and given us the data that hasn't put us outside our window," Hood says.

No matter how well planned the process, observers say there's no substitute for customer contact and having tactical detail behind census data or other information. "Data, unless you control it and generate it, may not absolutely be correct," says Hood.

Collett is a freelance writer in Sterling, Va.