Reprinted from CIO Magazine
By Jennifer Bresnahan
Between building an expensive data warehouse or adopting a quick
data mart that may cause integration nightmares down the road,
some executives conclude that the best course is to grow a warehouse
piece by piece.
This article will assist with
- Understanding the pros and cons of enterprisewide data warehouses
- Understanding the pros and cons of function-specific data marts
- Avoiding data mart “anarchy”
"Data warehouse projects can get out of hand since it is
difficult for IT managers to deny requests to change or
expand the design, making it impossible to deliver systems on
time and under budget."
-Wayne W. Eckerson
"We needed to have a quick-hit success story before we could
expect clients to fund further warehouse efforts."
J. Chris Baker
If the same platform is used throughout, data marts can be
scaleable and integrated with other data marts to grow into a
corporatewide warehouse.
The same principle holds true for data warehousing. An organization looking to build a data warehouse must carefully balance its users' needs with its strategic goals before deciding on an enterprisewide warehouse or a fast, function-specific data mart. For companies interested in changing their corporate cultures or integrating separate departments, an enterprisewide approach makes sense. Companies that want a quick solution to a specific business problem are better served by a standalone data mart. And some companies opt to build a warehouse incrementally, data mart by data mart.
The stakes are not low. In the short term, enterprisewide warehouses do not yield the same ROI as data marts that address a specific business need. The average ROI of an enterprisewide warehouse is 321 percent, compared with 532 percent ROI from a smaller, task-specific warehouse, according to a survey conducted by Stephen Graham, vice president of software research with International Data Corp. Canada Ltd. (see "Data Warehouse Data," page XX). (IDC is the sister company of CIO , both owned by International Data Group.) Yet, in the long run, having access to all company data in one place without worrying about integration issues may be a better value.
Enterprisewide Warehouses
Until recently, companies haven't had much choice but to store information in the data warehouse equivalent of Sam's Club. Behemoth warehouses span the entire enterprise, allowing for cross-departmental queries on every level of detail. According to data mart vendor Informatica Corp., the average centralized data warehouse takes about three years to build and costs $3 million to $5 million. Containing terabytes of data, dozens of subject areas and potentially hundreds of geographically disparate users, large warehouses integrate data from across the organization, revealing trends that might not otherwise be apparent.
But since the sheer volume of data bogs down the system, warehouses can frustrate everyone involved. "The goal of a data warehouse is to provide knowledge workers with a consistent, integrated and summarized view of relevant data from all areas of the business and with external data from suppliers, market feeds and data clearinghouses," says Senior Consultant Wayne W. Eckerson of Boston-based consultancy Patricia Seybold Group. "However, creating and maintaining data warehouses is not easy. We know of many large data warehouse initiatives that have stalled or failed. The chief culprit is usually the size and scope of the initiative.... [And] unfortunately, multiyear, multimillion-dollar systems projects are no longer in vogue in corporate America." Chris Horrocks, senior partner at CSC Consulting and Systems Integration in Hanover, Md., agrees. He estimates that as many as 90 percent of warehouses failed to meet expectations in 1995, partly because they were too ambitious in scope and partly because they weren't targeted to make economic goals. "Few IT shops have the expertise or stamina to integrate dozens of components from different vendors or to deal with the turf battles among users who must agree on a common set of semantics, definitions and business rules that are needed to define a data warehouse," says Eckerson. "Also, data warehouse projects can quickly get out of hand since it is difficult for IT managers to deny requests from business users and executive sponsors to change or expand the design, making it impossible to deliver systems on time and under budget."
Yet for companies looking to completely integrate or change their business processes from top to bottom, building an enterprisewide warehouse is a way to drive that change. Retail giant ShopKo Stores Inc., for instance, wanted to foster greater cross-functional collaboration. For years, the Green Bay, Wis.-based company's business divisions had each acted independently, basing decisions upon separate sets of data. That could cause problems if, for example, ShopKo's merchandising department sold 13 cartons of Product X, unaware that the distribution department usually shipped 12 cartons of Product X at a time. "Our data warehouse was the biggest opportunity we'd had in 20 years to integrate all these functions," says Jim Tucker, senior vice president and CIO. "It's easy to break things apart but difficult to tie all the pieces together. Our problems are not in analyzing each individual area but in getting them to work as a big machine -- a problem better suited to a warehouse than a data mart."
ShopKo had the advantage of already having a massively parallel processor in place, so it didn't have to invest in new hardware. The warehouse took about six months to build, using DSS Agent from MicroStrategy Inc. and data mining technology from IBM Corp. Now users in departments from merchandising, marketing and advertising are in sync and can analyze sales patterns and product usage.
Similarly, in 1994, a data warehouse was the key to integrating 10 separate hospitals -- each with its own systems, procedures, personalities and opinions -- into a newly formed health-care network, Promina Health Systems of Atlanta (see "Form Follows Function," CIO, Nov. 1, 1995). Promina's CIO and vice president of information services, Bill Dotson, knew the best way to create a coherent whole was to create a central information repository. "With 10 hospitals and all the complications and politics, and everybody thinking they have the right solution, we felt from a corporate standpoint that the key ingredient to success was a good information systems infrastructure," says Dotson.
With the help of Systems Techniques Inc. (STI), an Atlanta-based consultancy, Dotson spoke with over 50 Promina employees in all disciplines to learn their information needs. "We didn't want something that was going to take two or three years to accomplish," says Dotson, "so we got people's consensus on what they wanted and needed first. That's why we were able to get it up as fast as we did."
The warehouse took nine months to build with an Oracle Corp.-based data warehousing model purchased from Metasystems Inc. Dotson and STI standardized definitions and coding. Promina's warehouse now culls information from many of its hospitals. Users in a broad range of jobs can draw comparisons, such as which doctor takes the most patients or which medical procedure is statistically more successful. "I figured if the warehouse is my backbone, I wanted to make sure I did the system right so that I did not have to go back and redo it," says Dotson. "We were a young, integrated delivery system just forming and didn't have the opportunity to go for quick fixes."
Blue Cross & Blue Shield of Illinois, the health insurer based in Chicago, needed a way to bridge islands of information within different departments. "Nobody was talking about data marts back in 1990 when we started building our warehouse," says Julio Chavarria, Blue Cross's manager of the customer information service center. But now he wouldn't have it any other way. Blue Cross's lack of integration was a source of problems when situations out of the ordinary arose. For example, if a physician within the Blue Cross network referred a member to a specialist outside the network, each department recorded the situation differently. The financial department coded the transaction as within the health-care network, charging the patient as if she had gone to a network doctor. The utilization department, however, coded the transaction as non-network because the doctor was an "outsider." During the year and a half it took to build its 370GB warehouse from NCR Corp., Blue Cross standardized coding systems and made sure each department was in sync. Building separate data marts or a smaller warehouse wouldn't have solved the problem, says Chavarria. "Let's say I was going to pull information from four or five data marts," he says. "I would still have to physically go to the places where the marts are kept to get that department's information. That's just what we were doing before we considered a data warehouse."
Standalone Data Marts
For many companies that are less than eager to spend years and dollars on a data warehouse that might only be justified in a far-off future, a well-mannered, petite data mart makes more sense than a Goliath data warehouse. Mini data warehouses, or data marts, are fast and inexpensive to implement. Designed for individual business functions and small companies, they are ideal for quick adoption, though they aren't as robust as data warehouses. Typically, they contain fewer than 100GB of data and support 10 to 20 users. According to David Reiner, chief scientist and senior vice president of strategic development of database marketing consultancy Epsilon Data Management in Burlington, Mass., up to 80 percent of Epsilon's clients are now choosing standalone marketing information systems, or smaller data marts, over data warehouses. Cheaper and quicker, they enable information download more swiftly than a mainframe crammed with irrelevant information.
San Diego Gas & Electric is a prime example of an organization for which a data mart made sense. In the summer of 1995, J. Chris Baker, SDG&E's information management and security administration supervisor, needed to move fast to prevent the marketing department from buying its own, potentially incompatible data mart. Marketing needed a way to understand customer usage patterns -- fast. Baker knew the marketing department could not wait for management to decide to invest in a mega-data warehouse, so he went with a PowerMart data mart solution from Informatica that took three months to implement. He hoped that the single-function data mart would prove to his managers the value of data warehousing as well as prevent marketing from going off in the wrong direction. "We needed to have a quick-hit success story before we could expect clients to fund further warehouse efforts," says Baker. Sure enough, other departments within SDG&E are now vying for their own data marts; next Baker will tackle the power plant and financial services department.
Unlike SDG&E, the IS department at San Antonio-based La Quinta Inns Inc., had no involvement in the company's first data mart. Michael Nelson, La Quinta's director of financial planning and budgeting, specifically wanted his department to remain independent. In the past, financial planning and budgeting had to rely upon IS to create budget reports from a mainframe-based system. If changes were made, it could take IS up to three days to generate new reports. Nelson wasn't concerned with protocol and compatibility issues; he just wanted a quick fix for the general ledger system. "There was no need for intervention from IS," explains Nelson. "If we could not get something solved, then we talked to Oracle's help line."
Nelson not only wanted independence, he wanted speed. In May 1994, his department decided it needed a tool in place by August -- three months away. Nelson turned to Oracle's Financial Analyzer data mart. Six weeks later, just in time for the 1995 budgetary process, the department had its data mart. Now users can look at financial information on one or all of La Quinta's 238 hotels, drilling down even to the cost of a continental breakfast on any given day. As they see what it can do, other departments are starting to eye the data mart, says Nelson. He speculates that each department will buy the same product from Oracle but that La Quinta's data marts will never be integrated into a single warehouse.
Unfortunately, data marts also have a downside. They don't hold as much data as warehouses. "When you start building data marts around departments, with big efforts going toward business-process reengineering, today the department exists, tomorrow it doesn't," points out Kevin Strange, research director at Gartner Group Inc. in San Jose, Calif. But the main drawback to data marts is their inability to accommodate the inevitable "user creep."
Incremental Building
As users begin to understand the potential of warehouses, they look for new ways to apply their knowledge. "You begin to realize that you can ask questions and get answers that you couldn't before," says Judy Scheffler, Lucent Technologies common applications vice president. "People start to think differently." The usual means of accommodating this cultural change is to build another data mart. Pretty soon, multiple data marts with incompatible architectures mushroom, leaving IS to clean up the mess and integrate stovepipe applications. "Companies that allow groups to build data marts willy-nilly are creating the legacy systems of tomorrow that will be just as costly to integrate as the legacy systems of today," says Eckerson. (See "Metaphysics of Meta Data")
That was the problem at Northbrook, Ill.-based Allstate Insurance Co. Prior to 1990, when CIO Frank Pollard first realized the company needed a centralized information hub, Allstate representatives from 23 sites around the United States collected and stored customer information in paper files from various vendors. Each lot of information was virtually inaccessible to the others. A car insurance representative in Miami, for example, would have to wait weeks to obtain background information on a potential client from Boston. Worse yet, each state had a different coding system, so back office representatives had to manually compute the code for a speeding violation from state to state.
Pollard and his IS team had to dedicate 18 months to integrate all the data into a single, large warehouse built with decision-support systems provider Acxiom Corp. "Nothing smaller would have sufficed," says Pollard. "That's what we went away from." Now an Allstate agent can download information on a client from the company's warehouse while the client waits in the lobby. But Pollard concedes that life would have been easier if the company had started with an enterprisewide warehouse from the get-go.
Is There a Happy Medium?
With careful planning, data marts don't necessarily make an IS headache. Data mart "anarchy" can be avoided if the IS department keeps close tabs on all data warehousing activities to ensure compatibility with the organization's overall architecture, says Strange. If the same platform is used throughout, data marts can be scaleable and integrated with other data marts to grow into a corporatewide warehouse. "It's a lot more work getting back to the starting gate than if you had a consistent approach all along," says Jane Griffin, president and CEO of STI. "CIOs have to convince the department to sacrifice short-term objectives for the longer-term objective of the enterprise."
Trey Bradley, senior vice president and CIO of Dallas-based cosmetics company Mary Kay Inc., has done just that. Mary Kay's first application, which took six months to build and contains 10GB of data, enables sales agents to find trends such as the skin cream preferences of 30-year-old women in the Midwest. As soon as Bradley is satisfied that there are no glitches with the sales-force application, he will give the OK to expand the same warehouse to include applications for marketing, financial, sales compensation and other systems. Bradley has kept a close watch on each step of the process since it began early this year. "I don't think this system will get ahead of us because everything is happening through me," he says. "We'll keep with the same platform throughout."
Bradley wants the final warehouse to be completely user-friendly and able to address each user's needs. He also wants to remove IS completely from the daily workings of the warehouse. Bradley says that by growing a warehouse incrementally, "we have time to stop and try another approach when we run into something the users don't understand or that doesn't work. The thing has no deadline, no ending date. It will always evolve."
Think of it: a functional, never-ending, growing warehouse -- every CIO's fondest dream and worst nightmare.
Staff Writer Jennifer Bresnahan can be reached at jbresnahan@cio.com .
Metaphysics of Meta Data
Meta data standards will enhance connectivity
Meta data is the unseen lifeblood of a data warehouse. Just as the DNA within human blood cells carries the entire genetic makeup of a person, meta data contains information on the creation and maintenance of a data warehouse, including hierarchical relationships, stored formulas, item descriptions, data sources, data update status, formatting information, currency conversion information, time series information, notes for reporting, security and access controls, availability of precalculated summary tables and data storage parameters.
And just as different blood types are incompatible, data warehouses with dissimilar meta data platforms just don't mix. Until now, every data warehousing vendor has had a different way to store and organize the meta data about a particular data warehouse or data mart. That is not a problem if a CIO monitors the birth and growth of the warehouse, making sure that all warehousing products and their meta data are based on the same platform. But if a CIO doesn't plan carefully, or if business units start buying incompatible data marts, a CIO is in deep trouble when it comes time to integrate the warehouse pieces into a whole. "Vendors would be doing consumers a disservice by saying that if you buy a bunch of different data marts, then there's no challenge down the road," says Randy Betancourt, program manager for data warehousing at SAS Institute Inc. in Cary, N.C. "A lot of organizations are trying to face and grapple with this problem, but nobody's got a lock on the solution yet."
Realizing that the problem could doom the scaleable data mart concept, five vendors and several consultants formed a coalition last year to develop a common protocol for meta data (the Meta Data Coalition can be reached at www.metadata.org ). The first warehousing products with the standard protocol should appear in 1997, but still it's not the "silver bullet," says Jane Griffin, president and CEO of Atlanta-based consultancy Systems Techniques Inc. and member of the Meta Data Coalition, "There's still going to have to be a lot of effort on the part of IS departments," she says. "Ultimately, integration is their responsibility."
Until a standard becomes, well, standard, Wayne W. Eckerson, a senior consultant with Boston-based consultancy Patricia Seybold Group, has identified the following strategies to help CIOs ensure that their warehouses can share meta data regardless of platforms:
- Dispose and Replace: Convince individual business units to view their data marts as temporary fixes to be discarded once the company builds a data warehouse.
- Mutual Exchange: Connect business units to corporate data only if they agree to certain terms, such as standardizing the use of business terms and formulas.
- Physical Integration: Integrate heterogeneous data marts via a physical gateway that provides users with a single, unified view into multiple, geographically distributed data marts. This approach is sometimes called a "virtual data warehouse."
- Logical Integration: Allow business units to build data marts using only a pre-defined set of semantics and rules after documenting shared definitions.
- Grass-Roots Logical Integration: Adopt the data model and definitions of an initial group to build a data mart as a standard for all subsequent development, an approach that requires an evangelist to broker the activities of independent groups to make them see that working together is in their best interest.
- J. Bresnahan
