Most companies store reams of data about their customers. The IT challenge has been how to integrate and massage that information so the business side can respond immediately to changes in sales and customer preferences.
Henry Schein has it figured out. The US$2.8 billion Melville, N.Y.-based distributor of health care products has designed and built a data warehouse with a standing in-house team of six IT professionals -- and little or no help from consultants.
But that wasn't the original strategy. CIO Jim Harding says he hired an advisory team from dot-com consultancy MarchFirst, but that company went bankrupt in 2001, six months into the project.
It was a rocky start. Harding says he knew that having the right skills was critical to the data warehouse project, yet at the time, Schein had zero warehousing experience in its IT shop. So he and Grace Monahan, vice president of business systems, hired people for what they call "Team Schein."
Finding People for Tools
Because Harding had chosen two key tools for the data warehouse -- data extraction software from Informatica and front-end software from MicroStrategy -- the focus was on finding people who had experience with those tools.
Monahan hired three people from outside: project director Daryll Kelly, data modeler Christine Bates and front-end specialist Rena Levy, who's responsible for the user interface and data analysis, as well as user support and training.
Dawen Sun, who handles extract, transform and load issues, and database administrator Jamil Uddin hold two other key positions. Another team member is rotated in from Schein's application development group.
"You really need to have your own talent pool," Monahan says. "Especially once we put the kibosh on the consulting effort."
Kelly, the team leader, "came with a lot of expertise right out of the gate," Harding says. It was the kind of data warehousing experience that was lost when MarchFirst folded.
That expertise is important because without it, "there's a whole bunch of really terrible mistakes that are made by a data warehouse team that tries to invent it by themselves," says Ralph Kimball, author of a series of data warehousing books and president of Ralph Kimball Associates.
The six months with the consultants weren't a complete waste, Harding says, because they left him with design and rollout plans, although they had to be revised significantly.
Besides having the right skills, the other top priority was ensuring data quality. "It seems kind of obvious," says Harding, "but sometimes these projects forget about (quality), and then the data warehouse ends up being worthless because nobody trusts it."
At the outset of the project, the team interviewed about 175 potential business users to determine the information they needed to access and the reports they wanted to see. Plus, the team analyzed the old paper reports and the condition of the data housed in the company's core transaction system.
Monahan says those steps brought to light the importance of cleansing data in a system that's designed for transactional purposes but not suitable for a data warehouse. That led to a long period of standardizing transactional codes in order to produce the sales reporting that business analysts needed.
Consultants can be valuable at the outset, but it's the in-house people who have "this gold coin of knowledge of how their systems really work, which data is really good and not so good, and how the end users really want to use the data," Kimball says.
"Data quality is the hardest part of the project, because it's very time-consuming and detailed, and not everyone appreciates it unless they've been through a couple (of projects), like Daryll (has)," Harding says.
And there was yet another tedious obstacle. The data warehouse was designed to provide a very granular level of detail about customers, "so we can slice and dice at will," Harding says. But the result was sluggish system performance. So the team created summary tables to make the queries work faster, and those tables needed to be tested. It was a lengthy process, Harding says, but in the end, it worked very well.
The journey has taken well over two years. The system went live 18 months ago but "really came into its own" in February, Harding says.
Of course, building a data warehouse is a never-ending job. New companies are acquired, products are added, customers come and go, and new features and enhancements are ongoing. But from an IT standpoint, the data warehouse is complete and has 85 percent of the data from the core transactional system. The next major goal is to provide the European operation with its own data warehouse system and tie it into the one in the U.S.
Harding says his project will surely justify the costs, but he lacks hard numbers. "We didn't have a formal ROI that you could track later. I don't even know how you would do it," he says. "The reason we're doing (the project) is because of the value it brings to the business."
Lou Ferraro, vice president and general manager of Schein's medical group, says the business benefits are outstanding. He can now figure out who his most profitable customers are, target customers for certain types of promotions and look at the business by product categories or sales territories.
Ferraro says the data warehouse also helps select customers for direct-mail marketing campaigns that range "upward of 25 million pieces annually."
One of the most valuable features of the data warehouse has been the ability to add fields to reports. "Once you create a basic report, draw a conclusion (and) drill further based on those assumptions, it allows you to use that data and go even further, as opposed to creating a new report, and another and another," Ferraro says.
The IT department used to create, edit, revise, run, download, reprogram and print piles of paper reports -- daily, weekly, monthly and quarterly -- for the analysis of sales and market trends. But today, business users search, sort and drill down for that information themselves in a fraction of the time.
The data warehouse has become "a part of our culture," says Harding. "It's got that kind of aura about it within the company."