In-Depth

From the field: Data warehouse implementations

• By Jack Vaughan, Michael W. Bucken, Rich Seeley
• October 3, 2002

Case Study 1: Data warehouse and cubes open door to information at Weiser Lock
David Edwards, a data warehousing specialist, had written 300 custom reports for end users at Weiser Lock when he realized there was a better way to get data from the company's Baan ERP system to its employees.

The Tucson, Ariz.-based lock manufacturing company was using Crystal Reports to pull information from Baan, but with 10,000 tables, accessing data required more experience than most end users had, Edwards said, so they were coming to IT staffers for help.

The trouble was, he said, that end users might have to ''wait a week or a month ... depending on how busy we are.'' In addition, the sales force lost competitive advantage if they had to wait for information on issues like product availability.

The solution was to find a way to get the data to end users without requiring them to take a database management course.

The key to unlocking information for Weiser's employees was to build a data warehouse, create cubes and provide an easy-to-use desktop interface.

To accomplish this, Edwards built a data warehouse on Microsoft SQL Server using the tools that come with it. He then installed ProClarity's interface tool on the end users' desktops so they could access the data.

''I'm using [Microsoft's] DTS extraction, transformation and loading tool to pull out the data from Baan and put it into the data warehouse,'' he explained. ''I'm using SQL Server, the database, to hold the data and [Microsoft] Analysis Services to build all the cubes -- basically the whole Microsoft data warehousing platform. ProClarity is the user interface, the front end.''

With one day of training on ProClarity, end users can now get information without asking IT for help. Users are currently on Windows 2000 or Windows XP systems in a client/server architecture, but this summer Edwards is rolling out a Web-based version that the sales force is awaiting anxiously.

''The sales force is really excited about that,'' he said. ''They can sit down with a customer, fire up an Internet browser and they can walk right through the information that we have in the data warehouse.''

Overall, Edwards estimates that the data warehouse system has already resulted in a ''tenfold increase in information output.''
-- Rich Seeley

Case Study 2: Customer-savvy system takes flight
As start-up Swiss International Airlines began this year to forge a new company out of the former SwissAir, IT managers were told to place customer satisfaction at the top of their priorities list. To monitor customer satisfaction at a host of ''touch points,'' Swiss chose E.piphany E.6 software to create a single, easily updatable, enterprise-wide view of each individual that flies Swiss.

The customer relationship management (CRM) and sales force automation (SFA) systems created by Swiss and San Mateo, Calif.-based E.piphany link to a centralized customer database that pulls together historical flight and frequent flyer information, as well as sales and marketing data. Because it is Web-based, the overall system can be accessed by the many Swiss employees who interact with the flying public.

When a passenger boards a flight, attendants already have a good view of many of that person's preferences, said Peter Baumgartner, Swiss general manager of core customer marketing. Flight crews get customer care info, which is shared during the crew briefing. They can feed back changes for the next crew report, said Baumgartner.

Extending the system to passenger touch points was also a key requirement.

''We were always quite advanced in having a centralized view of the customer,'' said Baumgartner, ''but Swiss identified weaknesses in doing something with what we had collected. That changed with this project.''

With the new system, even a negative event -- for example, misplaced luggage -- can turn into something positive, said Baumgartner, because it can trigger action on the part of the airlines to put right wrongs.

While technology projects like this are usually led by a CIO, this time leadership came from the business side, with IT people responsible to Baumgartner. This is a revenue driver, he added.

Baumgartner said Swiss conducted an extensive competitive evaluation of CRM vendors' wares and chose E.piphany based on its ability to deliver what he described as real-time customer intelligence. The software also allows the airline to set up partnerships within the travel market with a variety of vendors.
-- Jack Vaughan

Case Study 3: Enterprise data warehouse boosts airline mileage program
Since September 11, United Airlines has been seeking ways to provide its customers, especially frequent flyers, with enhanced service to help minimize the delays caused by new airport security rules.

With United counting 40 million members in its Mileage Plus frequent flyer program -- and offering flights to 134 airports in 27 countries -- just keeping track of delayed and cancelled flights, as well as lost luggage involves large amounts of data.

This data is in multiple computer systems that separately handle applications for customer accounts, operations and planning, according to an IBM case study on United's efforts at integrating those systems with an enterprise-wide data warehouse.

IBM is using the airline data warehousing project to showcase its DB2 Universal Database Enterprise-Extended Edition, Version 7.2, running on the IBM pSeries SP server. Handling 1,500 possible data attributes, the volume of data is projected to be as high as 6TB within a year, according to IBM.

The data warehouse will eventually provide a central repository for all of United's business intelligence.

The first phase, completed earlier this year with help from IBM Global Services consultants, provides information on a variety of issues -- such as baggage handling, flight misconnects and late takeoffs -- to an intranet for United's business users that allows the company to provide better customer service.

Pulling together information on lost luggage from the baggage handling system, as well as delayed or cancelled flights from flight operations systems, will give United's customer representatives a better idea of the problems and frustrations a frequent flyer has faced, according to Casey Hossa, director of sales and marketing applications at United.

''When we tie that information to our customer base, we can better understand a customer's experience,'' Hossa explained to IBM.

Counting on this better understanding to retain and attract more frequent flyers, United is projecting full payback of its data warehouse investment within two years, according to IBM.
-- Rich Seeley

Case Study 4: Tennessee project brings departments together
The state of Tennessee IT unit modernized the state's accounting systems in the 1980s by building the still-used State Accounting and Reporting System (Stars).

State officials say the system has maintained its ability to gather significant amounts of information, but its complexity forced most state agencies to build proprietary front ends for displaying the data and generating reports. ''Over the years, each department built its own process for making reports from the accounting data,'' said Jan Sylvis, the state's chief of accounts. ''There was a lot of re-keying, manipulating and reconciling of data in many departments.''

The lack of front-end standards -- along with a statewide strategic planning effort as the year 2000 approached -- led to a decision to create a data warehouse that could standardize the data analysis and report writing process. The project was overseen by the state's Department of Finance and Administration, said David Dealy, director of Stars development. ''We saw this project as a way to improve business at Finance and Administration,'' he said.

IT officials decided to use a relational OLAP architecture based on IBM's DB2 strategy and then chose the MicroStrategy reporting and analysis system from MicroStrategy, McLean, Va., said Sherrie Benn, a state data warehouse consultant. Officials said the MicroStrategy technology met requirements to enhance rather than replace the mainframe-based Stars system, and to support DB2 and Oracle databases used by the state.

Goals for the system include enhancing financial data analysis; providing summary-level reporting for management; creating consistent interpretation of financial data definitions and business rules; providing timely access to financial data; and adding comparative and exception reporting capabilities.

Sylvis said the plan is to implement the project in phases, starting with general ledger revenue expenditures and then expanding to the general ledger accounts and on to other accounts. The system was first rolled out to five pilot agencies and is now spreading to all state agencies, Sylvis added.

About 10 developers are assigned to the project, which boasts 300 users and plans to grow to 800 users soon, added Dealy.
-- Michael W. Bucken

Related stories:
Editorial: Volume 1, number 1 by Michael W. Bucken
Delivering high-quality data by Wayne W. Eckerson
Data Insight product guide compiled by Lana Gates and Donna Sussman