Crayola maker tickled pink

Crayons. Markers. These products are the bread and butter at Binney & Smith Canada. But data warehouses – well, that’s a different story.

Binney, the Lindsay, Ont.-based maker of the popular Crayola line of crayons and other art supplies, decided in 1997 to reassess how the company stored its crucial data.

“The package that we had is quite sophisticated, but it isn’t very good at reporting,” said Fred Geyer, Binney’s general manager.

That package includes sales and customer information modules from ERP vendor J.D. Edwards running on an AS/400 backbone. But like other businesses, Binney relies on up-to-date sales figures – something it had access to in 1997, but not very efficiently. Back then, sales information came to Binney via an EDI connection, and was stored in Microsoft Excel, or as AS/400 flat files. “So data from our different customers was (being stored) in different places,” Geyer said.

That made creating hundreds of scheduled and ad hoc reports an arduous task, and one that was eating up the IT department’s time and resources. It forced Geyer to consider a change. “Our six IT professionals were doing more and more reporting, and less and less business development,” he said.

So Binney issued an RFP, during which it saw a variety of data warehouse solutions. Geyer said he was looking specifically for a turnkey solution capable of running on Binney’s Windows NT server-based network, as well as something that wouldn’t scare away users working on Windows desktops.

Ultimately, he opted for Microsoft SQL Server 6.5. And though he saw lots of tools that could do the job, it was the Cognos Inc. PowerPlay and Impromptu front ends that came bundled with SQL that sold him.

Michael McKee, marketing manager for SQL Server with Microsoft Canada Co. in Mississauga, Ont., said the front end is an important factor in SQL sales, especially for those who are new to the technology.

“[SQL Server] gets you part of the way there,” he said. “We rely on some sort of front end tool…like Cognos (for the rest).”

The job of installing Binney’s new system went to Toronto-based Cognos partner Newcomp Solutions Inc. Michael Roberts, the systems engineer who headed the project, has worked with data warehouses from different vendors but prefers the Cognos\SQL Server pairing for first time users.

“For a company that’s performing its first implementation of a data warehouse, the learning curve is reduced by the friendly interface…and there are good resources available to support people who are doing development with SQL Server,” Roberts said.

He followed a typical installation framework, first making sure he understood what sort of data Binney wanted to analyse – in this case, mostly sales and marketing-related information — and what data would have to be pulled to facilitate that. Once that was completed, he quickly built a prototype for Binney’s assessment.

Roberts said this is a crucial part of data warehouse implementations, as it shows users new to OLAP technology exactly what they can accomplish.

“People get very excited when they see an OLAP system at work with their data in it. It’s unusual for people to see that if they’re used to standard column and row-based reports, so once they get the idea of what can be done, then they start to think of other things they’d like to pull into the OLAP system.”

Technically speaking, Roberts said the Binney project didn’t pose any big challenges. And he credits the Binney IT staff for helping ease the transition. “They were adept at producing extract programs to supply all the data to the warehouse that was required. That didn’t take long at all,” he said.

But users new to data warehouses are usually reluctant to change the way they do things, and tend to push for the same data extraction processes even in the new OLAP environment, Roberts said.

And according to Geyer, Binney users were no different. “Some people kept doing what they were doing. The marketing department had (to make) a little more adjustment…they wanted to do it exactly the same way.”

Fortunately for Geyer, Binney’s U.S. counterparts had recently finished a slow and troublesome sales force automation software implementation. Geyer said he learned an important lesson from that – namely, that when it came to new implementations, users must be forced to sink or swim.

“We identified the key routines that everyone had to do immediately. We wiped out a bunch of basic reports, and the only way to get at the information was to use the warehouse,” he said. “[Now], no one, and I mean no one, writes reports anymore.”

Geyer also found that the new data warehouse yielded some unexpected benefits. By bringing corporate data to the surface, the company was able to get a bird’s eye view of the information, and thus organize it better.

And though the warehouse was never intended to serve as an auditing tool “it helped us get after inaccuracies,” particularly in the company’s financial statements, Geyer said.

Since the original implementation, Roberts said he’s only returned once to oversee the addition of an external disk sub-system to the server, to accommodate point-of-sale information to queries. That went smoothly.

Today, customer data pulled from the J.D. Edwards modules is stored each night in the SQL Server data warehouse. And PowerPlay allows the IT department to view multidimensional information cubes, which gives users quick access to Binney’s data. One cube summarizes all data stored in the database at a given time, while another helps users find more detailed information.

Geyer is happy with how the SQL Server solution helped Binney, and he’s even considering adding new functionality in the near future. “Now what we’re looking at is how to make it Web functional…we see it as a valuable resource for our suppliers and customers.”