10 rules for successful data warehousing
It's probably no big surprise to hear that many of the rules for developing a successful data warehouse are identical to those for implementing any IT system: Understand your business process, get buy-in from top management, involve the users in the development. But that doesn't mean it's easy. Because the tools and techniques are still relatively new and data warehousing is complex, failed efforts abound. The two case studies below provide insight on how to succeed with data warehouses. The ten rules that follow are lessons learned from these cases and others, and can point you toward short- and long-term success with data warehousing.
Y2K and timely data
Take the case of Westinghouse Security Electronics (www.wse.com), which manufactures and sells access control systems in more than 70 countries. WSE faced a double challenge: bring its computer system into compliance with Y2K and provide its worldwide sales force with up-to-date information about dealer activity. Partly because of the global environment in which the company operates, getting reports to the sales force about dealer purchases was a challenge. The reports were thick, and faxing them to areas where office systems are turned off at night was onerous. Delays in getting out hard copy meant that salespeople did not have recent information. Electronic distribution was a good answer.
Rishi Chowdhury, director of information technology at WSE, decided to solve both the Y2K problem and the data access problem simultaneously by implementing a data warehouse. In the process of cleaning up the data for the warehouse, the new date fields for Y2K could be added, and use of an OLAP tool could provide the analysis needed to present meaningful summary data rather than thick paper documents. Existing reporting was maintained, but new capabilities were added, such as a dealer profile that highlights ordering patterns and provides accounts receivable data.
In an important first step, WSE selected one activity--sales--to be served initially by the data warehouse, primarily because it accounted for more than half the reports being created. Second, it chose MicroStrategy's (www.strategy.com) DSS Suite, a set of business intelligence products that is compatible with AS/400 and DB2 database. Third, it introduced the system gradually. The data warehouse for sales was developed first and tested by "power users," then deployed on the WSE intranet. In the next phase, the manufacturing data warehouse will be introduced, followed by the finance data warehouse. Chowdhury has been pleased with the robustness, flexibility and scalability of MicroStrategy's DSS Suite.
WSE made judicious use of consulting services from MicroStrategy to develop its own staff expertise, and was able to do virtually all of the development in-house. The implementation was done on a modest budget, and the time spent on development was also reasonable, approximately six months. "Our most immediate benefit will come from providing salespeople with timely status reports on dealer accounts," said Chowdhury. "Sales can then make informed decisions about which accounts receivable to focus on, with a clear bottom-line impact."
The path to knowledge
Lesco (www.lesco .com), a manufacturer of turf maintenance products, took a somewhat different path in implementing a data warehouse. The company made a conscious decision to convert its staff to knowledge workers. To do that, Lesco needed to provide ready access to enterprise data, so each worker could get a full picture of the company's operations. That effort, conceived from the beginning as an iterative process, was launched in 1995 and is described by CIO Wayne Murawski as a work in progress. That description is not a euphemism, because the system has succeeded on all counts.
Murawski challenged the conventional wisdom of having a high level of communication between the IT staff and the users, building a "brick wall" between the two groups. The IT staff was responsible for managing the data itself, and the users, including sales, marketing, accounting and manufacturing, defined the system and now "own" it. Although the data warehouse manager from IT sits on the user committee, most of the IT staff do not even know how to use the OLAP tool. As a result of that approach, roles and system ownership were clearly defined.
In addition, Lesco overturned the "careful planning" phase of the project that is usually advocated, choosing not to spend a great deal of upfront time. The rationale was that the system was intended to change over time and would be "flexible by design." Instead of devising a meticulous query profile, the company requested that users think about what information they needed to do their jobs on a day-to-day basis (with the goal of having 80% of those questions answered in summary form upon rollout), and what information they would need to do their jobs better. The result was a good set of questions, with a quick turnaround.
Information is grouped by area--for example, orders, sales and inventory. Any user can look at any area. Those conceptual groupings prevent user overload that might result from looking at too many fields simultaneously. The multidimensional cube generated by Cognos' (www.cognos.com) PowerPlay is an intuitive way to present the data visually. In addition, users have the ability to move easily from PowerPlay to Impromptu, Cognos' reporting tool. Lesco started out with Cognos' products five years ago because of their ease of use and some particular data management features, and found that over time, they continued to meet the company's needs as new functionality was added to the data warehouse.
Lesco's transaction-intensive business (more than 3 million transactions per year) mandates that data be summarized to show meaningful patterns. "Typically, users are in fact able to get 80% of their answers from the multidimensional PowerPlay presentation," said Murawski. "The focus can then be on the problematic exceptions, where we use Impromptu drill-backs for the most granular issues." Trouble spots are shown in yellow or red. That is the stage at which data becomes knowledge, because it leads to action. "The CEO looks at the red," he added.
The data warehouse is used to target-market specific customer and/or product segments to increase sales, which the company estimates has brought several million new dollars to the top line. In addition, the quality of decisions has significantly improved because of newly available information. That phenomenon is evident at every level, all the way up to the board, Murawski said. For Lesco, the data warehouse and OLAP capability have been essential tools in the transition to a knowledge enterprise.
Lessons learned:
- Have a clear business goal. A wide range of goals can be achieved through implementing data warehouses, but only if the goals are defined. Both WSE and Lesco knew what they wanted to accomplish. Keep goals in mind throughout the development process to avoid the dreaded "scope creep."
- Answer the build vs. buy question early. "Buy" systems have been developed for a variety of vertical markets. If none matches your business, building is the best option. Next, make a candid assessment of your in-house capabilities and determine whether the existing staff has the skills needed to develop the system. If not, seek outside help. Most data warehousing vendors offer consulting.
- Manage the business/IT gap. That can be done in a variety of ways: by defining separate roles, the way Lesco did, or by having either a business-savvy IT person or a technically inclined user on the team to facilitate communication. Some turf battles should be anticipated. IT may have complained about demands placed on them for reporting, but putting power in the hands of users can be threatening.
- Start small, but allow for growth. Select a small chunk of data so you can deliver a product quickly that includes information users can't get anywhere else. For example, information on whether a product was sold through a regular promotion or a special sale is valuable, but unavailable in many current point-of-sale and legacy systems. "For a retail data warehouse, says Micro Strategy's Julia Pastor, "this is a winner." Meanwhile, allow for future expansion of the system. One implication is that you should choose a flexible front-end product so that you can modify it for various OLAP applications.
- Structure the warehouse efficiently. Rather than making the entire database accessible directly, a popular structure now is a hub-and-spoke design with the warehouse at the center and data marts as the spokes. Cognos' Tom Camps said that structure can provide meaningful data to users and prevent the users from being overwhelmed. It also reduces network traffic. But beware of data marts that create unrelated islands of data.
- Clean up the data. The extraction, transformation and load (ETL) process, although tedious, provides an opportunity to make data more consistent and accurate. However, the data discovery phase has great potential for putting a project behind schedule as inconsistencies and quality control issues are uncovered and need to be dealt with.
- Develop a meaningful query profile. The questions users can ask should reflect the business goals. However, making choices that eliminate some options can be tough. If users are not able to state directly what they want, a set of interviews with forced prioritization of options can facilitate the process.
- Don't settle for Q&R. Sometimes the data warehouse simply duplicates previous query and reporting functions. While those might be a required part of the system, they do not effectively use the analytical capabilities of OLAP tools.
- Consider adding external data. Don't restrict your options to in-house data. Adding demographic information, for example, can help you leverage your legacy data to discover and exploit new markets.
- Prepare for maintenance. Data warehousing has been likened to parenting--great excitement at conception and delivery, then a long haul to nurture and sustain the system (Douglas Hackney, DM Review, February 1999). Resources for maintenance should be factored in to account for this.