Give Business Intelligence A Boost

Building the infrastructure to acquire information from, process, and distribute usable data back to more than one hundred stores around the globe is challenging enough when you've got a corporate mandate for stores to do so. When you're dealing with more than a hundred independent business owners the way party goods franchiser Partyland (Philadelphia) is, that challenge becomes even more evident. Franchisees, after all, are entrepreneurs with minds and business acumen of their own. But, Brian Watt, CFO at Partyland, says prior to building a relational database-driven data distribution model, many of his franchisees weren't even aware of what they were missing by not having good BI (business intelligence). "Entrepreneurs who open Parytland stores are confident and aggressive, but they don't necessarily come from a corporate retail background," he explains. But what they didn't know was hurting them, or at least, impeding their success. Store owners didn't have granular top and total sales by department data, for instance, which is so critical for making good merchandising decisions. Without good data and good data sharing, conversations between the corporate office and franchisees resulted in little more than half-baked assumptions on sales trends, and inventory replenishment wasn't accurate or timely.

Before BI, A New POS
As Partyland set out to build a new POS and BI system that would give corporate and store owners consistent data visibility, it faced challenges specific to the franchise model by which it operates. Each Partyland store carries a different SKU (stock keeping unit) mix, different vendors and products, and manipulates prices differently. Partyland corporate wanted to run all its stores on one system, yet maintain the product sourcing, merchandising, and pricing flexibility it gives its store owners. "We also had no intention of polling or using any data replication technologies," says Watt. "We wanted it to be real time, all the time." In 2000 when the retailer began to explore its options, it was running a variety of mismatched legacy systems, and its polling efforts were impeded by the inconsistency. In a sweeping POS upgrade, Partyland built a VPN (virtual private network) to support real-time data transfer via CommercialWare's hosted StorePOS system, a module of the vendor's CWStore suite of applications. This was the first step toward building a consistent infrastructure over which to share BI. After a seven-month switch from its legacy systems, stores are outfitted with wirelessly-networked Compaq Pentium 4-class machines and Symbol scanners.

Bye-Bye, Bad BI
Once the POS system was in place and the VPN up and running, Partyland focused its attention on refining BI. It used CommercialWare's StoreData product, also a module of CWStore, to build a Microsoft SQL Server-based data warehouse and data mart. It uses Microsoft's Data Analyzer as the querying tool. But once again, the inconsistencies of data management between stores and corporate made building the new data repository a challenge.

"The Microsoft Data Analyzer is a great tool, but it's not a stand-alone product," advises Watt. In fact, it took an expert at what Watt calls "data staging, data cubing, and data cleansing" two weeks to extract the data from Partyland's old POS systems and build the new data cube. "It was difficult, in terms of file transfers, to take all that SKU, price, and historical data from legacy, DOS-based programs running non-relational databases and to convert it all," he says. "We'd extract files from our old POS systems and send them to CommercialWare, but they weren't mapped correctly." For instance, in one store, the product description field might have been called "description," in another it might have been called "SKU name," and in still another it might have been called something completely different. "We had to map all of those things to make sure that when things landed, they landed in the right spot. Sometimes a database might have leading zeros in front of the SKUs, so we had to remove them to map them correctly. When you're talking about 15, 20, or 30 stores all at the same time, that's pretty difficult to do," he says. But while file-extraction is intensive and time consuming, Watt also calls the project enlightening. "Over time, we began to see and correct duplicate SKUs, missing items, all kinds of crazy things," he says.

Slow Selling SKUs, Be Gone
Once up and running, the BI tool brought more benefit than simply eliminating duplicate and incorrect SKU numbers. As a result of the sales reporting data the tool provides, Partyland has significantly scaled back its SKU representation in 7 of 13 store departments. "We found that some of our departments did not warrant the depth of inventory we were giving them," explains Watt. In fact, the retailer reduced its SKUs by more than half, trimming inventory from 15,000 SKUs to 7,000. "Realizing we didn't need that many SKUs was the biggest benefit. We used to keep $3,500 to $4,500 in greeting card inventory per store, and we've dropped that down to about $2,000. We've also reduced gift wrap inventory from about $5,000 to $2,000 per store," he says.

Even as the BI solution emphasized the need for a reduction in SKUs, its integration with the POS system resulted in a smart tool for avoiding stock-outs. "Everything in our inventory is decentralized to the store itself. We have min-max alerts built into the POS system, so when a certain SKU hits a certain level of depletion, a PO (purchase order) is automatically generated, which the store owner reviews and sends on its way," says Watt.

Without good BI, directives from corporate to franchisees to cut inventory in half would likely be met with stiff resistance. But Watt says special care was taken to work store owners through the change. "In a franchise environment, you're not dealing with store managers. These are entrepreneurs who have their own ideas, and we respect that." Rather than take a heavy-handed approach to SKU reduction, Partyland's corporate office began by using forums like its annual convention in Chicago to introduce store owners to granular SKU by department-level detail about their stores. "We had to educate them about how those dead inventory dollars would have a negative impact." That might be second nature to a large chain, but not necessarily to the entrepreneur who's not aware of the details.

Another hurdle Partyland had to clear with its franchisees was the cost of the DSL (digital subscriber line) or cable connection to the Internet that would enable the VPN. Today, it's a requirement for new stores to subscribe to an "always on" Internet connection.

Intersystem Communication Aids Interpersonal Communication
CommercialWare's StoreData (BI software) and StorePOS software both use Microsoft SQL Server architecture, which allows the POS application to send file updates to the BI system. Both systems reside on CommercialWare's server in Toronto. The file update process can be done as often as desired, but Partyland does it at the end of every month, then distributes fresh BI reports to each store at the beginning of the following month. An entire month's worth of POS data, such as dates, times, and quantities of sales, is uploaded to and processed by StoreData in 25 minutes or less.

"Now, when our store operations people call store managers, they have a report to discuss, and these are very substantive calls," says Watt. "We may call on someone who's doing fifteen dollars per ticket, while our average stores are doing twenty. The discussions can focus on real data that reveals why that is and what can be done to fix it," he says. "That's the challenge for any franchiser - how to get usable information to the franchisees. You don't want to bother them, you don't want to brood on them or tell them what to do. But positive relationships are based on substantive information."