The inside story of a fatally flawed data warehouse project
Wasted time. Squandered money. Lost
jobs. Ruined reputations. Jeopardized careers.
Project failures are the dirty little secret of data warehousing. Anyone who has
spent time around data warehouse projects is likely to have chalked up a failure
or two. The majority of those debacles can be attributed not to technology
snafus but to lack of strong executive sponsorship and to poor management.
For many, being part of an abysmal data warehouse project is a rite of passage.
But that doesn’t make it any easier. The project manager and consultant who
were central figures in the datawarehousing effort at the company we’ll call
Close Call Corp. deserve credit for candidly sharing their experiences here.
Through a spokesman, the CEO of Close Call—who pushed for the warehouse in the
first place—declined to comment.
Because it’s so hard to do data warehousing well, the
kinds of things that went awry in this case study could afflict any
implementation in any industry. Do not, therefore, assume your company is safe
from a failure of this magnitude. If you look closely—and are honest about it—chances
are, you’ll recognize some of the red flags raised here.
Quotations:
Despite the early uncertainties, optimism prevailed at the
project launch. " The director of MIS had so much positive energy. I really
thought together we could make this happen." –Jackie Pemberton
"They had wanted their production warehouse in place in four months and at
the end of eight months we said, ‘Here’s your itsy-bitsy little one-page
pilot, and guess what, you’re not even getting your warehouse.’"
–Jackie Pemberton
Editor’s Note: All names in this case study have been changed.
The road to hell is paved with golf games. For the CEO of
Close Call Corp., his trip netherward began with a day on the links in the fall
of 1995 with a software vendor. At the time, the CEO was particularly vulnerable
to a " business-transforming" pitch. He knew he needed to make
technology changes—and fast. His teleservices company, which he’d founded
with $200 in the 1970s and had grown into a modest empire worth $100 million,
was at a crossroads. Intent on choosing the straightest path through a
double-digit growth period, he was searching for technological help. So when the
vendor tempted him with visions of integrated data flow and information on
demand, the CEO couldn’t resist.
Historically, Close Call’s outbound (telemarketing) and inbound (catalog sales) business units had operated as totally separate companies. The company had recently gone public, and rapid growth was putting major pressure on its antiquated and proprietary systems. The vendor made a convincing case that a data warehouse was the answer to Close Call’s prayers. Nothing to it, went the siren call, you can be up and running on a fully functional data warehouse in three to four months. The CEO began salivating at the thought of providing a unified view of Close Call’s business data to its autonomous business units.
But with 1996 already shaping up to be a banner year for Close Call, the timing
for a data warehouse project could hardly have been worse. The company was
planning to expand from six call centers to 116, implementing new, open
switching systems in the new centers to enable automatic dialing and call
routing. In addition, information systems (IS) was updating all of Close
Call’s internal management systems by deploying new human resources and
general ledger software.
When the CEO returned from his fateful golf game and sounded the call for a data
warehouse, he ensured that 1996 would be a year to remember—but not for the
reasons he’d hoped.
Unrealistic Expectations
The CEO expected the can-do culture that he’d nurtured
from the early days of the company to carry it through this period of
exponential growth and technological change. He believed that making all the
systems changes— including building the data warehouse—in a very short time
frame was just a matter of getting the right people for the job, according to a
consultant we’ll call Michael Farraday, cofounder of the data warehousing and
decision support consulting firm that was called in to do the warehouse design
and data modeling. "He put decisions in place and said, ‘Go make this
happen.’ That approach had always worked in the past. Not this time,"
says Farraday.
Based on the few technology tidbits he had obtained from
the software vendor on the fairway, the CEO was convinced he could have a 500GB
production data warehouse up and running in four months despite the fact the
existing IS staff was already stretched to the limit. Before the project team
was on board, he set the project deadlines and a budget of $250,000, the
ultimate no-no, says Farraday, who has 12 years’ data warehousing experience.
Because no one in-house had data warehousing experience or
time to devote to the project, five outside people were hired. The outsiders
included Manager of MIS Jackie Pemberton (not her real name), who was brought on
in part to manage the data warehousing project, and a director of MIS, both of
whom had proven data warehousing track records and a combined total of nearly
three decades of experience in the field. Pemberton was also put in charge of
the general-business systems software rollout.
Unrealistic time and resource allocations alone might not
have grounded Close Call’s data warehouse had the business ranks been
clamoring for the information it could provide. But because they’d never been
exposed to an analytical environment before, the users didn’t know what they
were missing. Indeed, the business unit managers were quite content with the
canned reports they could get from a DOS-based Reflex database. The few who
needed more analysis entered the report numbers into a spreadsheet and did
rudimentary manipulations.
The lack of demand for a new reporting system foreshadowed
a virtually insurmountable problem, says Farraday: Down the road, the users
would not be willing to commit the time and effort required to make the
warehouse a success. "The challenges go up dramatically if the data
warehouse represents the first analytical environment," says Farraday.
"[The users] never really understood what a data warehouse could do."
The Project Launch
The new director of mis assembled the initial project
team, consisting of Pemberton, two senior managers from sales and telemarketing
and a database administrator as well as Farraday and another consultant from his
firm. Pemberton, the director of MIS and the consultants pushed back on the
scope of the project, the preset deadlines and budget, but the CEO stuck to his
guns. Ultimately, he grudgingly accepted the idea of a pilot in five months
rather than insisting on the full-blown production warehouse. But he never
seemed to understand the magnitude of the undertaking, says Farraday.
From the start, the data warehouse lacked a clearly
defined business objective, mostly because the users had never asked for greater
analytical abilities. Pemberton believed she would be able to articulate the
business case herself after gathering detailed user requirements.
Early on, Pemberton met weekly with the director of MIS,
who in turn met separately with the executive sponsors (the CEO and the CFO)
every week. Only when things began to fall apart several months later did the
four begin to meet face to face each week. Also, the general managers of the
business units said they were too busy to get involved in the requirements
assessment stage of the project. "The feeling was, ‘Go talk to my guys.
They’ll tell you what they need,’" says Pemberton. In retrospect, she
says the director of MIS didn’t reach out to the business users as much as
perhaps he should have.
Despite the early uncertainties, optimism prevailed at the
project launch. "[The director of MIS] had so much positive energy. I
really thought together we could make this happen," says Pemberton.
Collecting User Requirements
Farraday and his consulting colleague saw the process of
gathering user requirements for the warehouse as critical. Along with Pemberton,
they embarked on three painstaking months of interviewing the key users as 1996
began. Instead of merely asking users what data they needed from a data
warehouse, they invited them to talk in general terms about their jobs, homing
in on how their job performance was evaluated and how they managed people.
Although the users complained the interviews took too much
time, everyone felt this stage had been a resounding success. In spite of the
very separate business units, the team was beginning to build the consolidated
picture of the business they’d need to begin constructing the warehouse.
Building the Business Model
As they collected user requirements, Pemberton and
Farraday set about creating a business model that would capture the business
professionals’ requirements in their own terms. Independent of technology,
this model would define the business dimensions (for example, looking at the
business in terms of products, locations or time periods), attributes,
relationships, facts and logical navigation paths, all of which would translate
to the design of the warehouse. The challenge was to get the distinct and
autonomous business units to settle on these critical definitions.
This, too, went much more smoothly than anticipated.
"People from different groups were agreeing on things they had never agreed
on before," says Farraday.
The bad news was that the model revealed a highly complex
set of business requirements and an inconsistent group of data "facts"
that would populate the warehouse. Unlike a retail data warehouse in which the
quantity of a particular SKU sold at a particular time becomes an unalterable
"fact," performance of customer service representatives—what Close
Call was trying to measure—was much more subjective and obscure. Business
managers had created their own customized spreadsheets into which they reentered
the data they wanted to examine from the Reflex database reports. Because the
managers looked at things differently (for example, some defined "revenues"
as actual revenues, others considered them estimated revenues), defining the
fact groups therefore became a hellish ordeal that required sorting through
literally hundreds of calculations based on subjective assumptions. This process
alone took nearly a month, says Farraday, which meant the team couldn’t start
building the relational format and user interface. It was a delay the executive
sponsors were not inclined to forgive.
Source Data Crisis
Then came the biggest blow to date. With the pilot
deadline at hand, Close Call’s IS veterans, who believed their jobs were
threatened by the data warehousing project, finally admitted to the project team
that there was no way to populate the warehouse. The only data available was
basic customer and transaction information that had been captured by Close
Call’s proprietary telecommunications switching systems as a byproduct of call
routing. Writing a program to extract that data for the warehouse would be too
expensive and time- consuming even to consider. Clearly, the old switching
systems in the original six call centers would have to be updated with open
technology to capture the data for the warehouse electronically, says Farraday.
Panicked at the thought of breaking that news to the
executive sponsors, the team jury-rigged a way to populate the pilot by parsing
the DOS-based Reflex reports and manipulating the report data into a relational
database format. But the handwriting was on the wall—without replacing the
proprietary switching systems, there would be no data warehouse.
By this point, Pemberton and her team were in overdrive,
working 15- hour days, six days a week. Twice-weekly trips to Close Call’s
other major corporate office in the next state also took their toll. "I was
trying so hard, but it was an emotional nightmare," says Pemberton.
The Pilot
The pilot contained a small summary-level set of data
that the team had manipulated and manually loaded into the database. After the
pilot was complete in August, the team faced an unpleasant revelation. Even if
there had been pristine source data, the users weren’t having any of it.
"They said, ‘You’re giving me what I
already had, and it’s harder for me to get it and the data doesn’t even
match my hard copy,’" recalls Farraday. "We lost the users at that
point."
The anger began to build and hit a crescendo in
the executive suite. " They had wanted their production warehouse in place
in four months and at the end of eight months we said, ‘Here’s your
itsy-bitsy little one-page pilot, and guess what, you’re not even getting your
warehouse,’" says Pemberton.
It was all over but the shouting.
Crashing Down
A few weeks after the pilot debacle, Pemberton found
herself with a new boss. The director of MIS, who had been promised the CIO spot,
ended up with two new bosses, who shared the responsibilities of IS management
as vice presidents of technology. To no one’s surprise, the director of MIS
elected to leave the company shortly thereafter.
Undaunted, Pemberton assembled a team of 57 IS staff and
business users and in a few weeks created a detailed plan for reengineering the
outbound business processes and updating their systems. With her heart in her
mouth and passion still unspent, she presented her plan to the executives in
October 1996, telling them it would take two years to reengineer before they
could even begin the data warehouse. She said she believed nothing less than the
survival of the company was at stake. Her words fell on deaf ears. "They
said, ‘Thanks for your work, but no thanks,’" says Pemberton. No one
spoke in favor of her proposal at the meeting.
The Aftermath
Close call’s CEO canceled the data warehousing project
in October 1996. Though it was hardly a shock, Pemberton was shattered. "I
had never been involved in a situation where I failed at my job. I had gone in
with such high hopes. I really wanted to give them a data warehouse," says
Pemberton. In February 1997, she quit without a new job nor any prospects of one
and took three weeks off to recover. (Pemberton reports she is now a senior data
warehousing manager for another company and has just delivered a pilot of a data
warehouse.)
From the CEO’s point of view, the entire project had
been a fiasco, says Pemberton. The anemic pilot was delivered four months later
than the initial (highly unrealistic) deadline for the fully functional
warehouse. Although the CEO had budgeted a paltry $250,000 for the project, the
team had spent nearly $750,000 on hardware, software and services.
"Management finally said, ‘We have spent a
lot of money, and we have gotten no value,’" says Pemberton. "It was
devastating."
Besides wasting money and time, the project cost Close
Call 50 percent of its IS staff, about half of whom quit, according to Farraday.
Today, Farraday reports that the company is reengineering its outbound business
processes with an eye toward another data warehousing attempt. But in the
meantime, Close Call’s stock price has taken a beating, losing more than two
thirds of its value between October 1996 and September 1997. Farraday attributes
the company’s change of fortune to attempting too many technology projects at
once. "They bit off way more than they could chew," he says.
So much for believing everything you hear on the golf
course