In-Depth
From the field: Data warehouse implementations
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
About the Authors
Jack Vaughan is former Editor-at-Large at Application Development Trends magazine.
Mike Bucken is former Editor-in-Chief of Application Development Trends magazine.
Rich Seeley is Web Editor for Campus Technology.