HW#1: DW Case Study
Case Studies of Data Warehousing Failures
Four studies of data warehousing failures are presented. They were written based on interviews with people who were associated with the projects. The extent of the failure varies with the organization, but in all cases, the project was at least a disappointment.
Read the cases and prepare a report that provides a substantive discussion on each of the following:
- What’s the scope of what can be considered a data warehousing failure?
- What do you find most interesting in the failure stories?
- Do they provide any insights about how a failure might be avoided?
Your discussion should be at least 2 pages in length with 1.5” spacing & 1” margins.
Case Study 1: Auto Guys
Auto Guys initiated a data warehousing project four years ago but it never achieved full usage. After initial support for the project eroded, management revisited their motives for the warehouse and decided to restart the project with a few changes. One reason for the restructuring, according to the project manager, was the complexity of the model initially employed by Auto Guys.
At first, the planner for the data warehouse wanted to use a dimensional model for tabular information. But political pressure forced the system’s early use. Consequently, mainframe data was largely replicated and these tables did not work well with the managed query environment tools that were acquired. The number of tables and joins, and subsequent catalog growth, prevented Auto Guys from using data as it was intended in a concise and coherent business format.
The project manager also indicated that the larger the data warehouse, the greater the need for high-level management support – something Auto Guys lacked on their first attempt at setting up the warehouse. Another problem mentioned by the project manager was that the technology Auto Guys chose for the project was relatively new at the time, so it was not accepted and did not garner the confidence that a project using proven technology would have received. This is a risk inherent in any “cutting edge” technology adoption. The initial abandonment of the project was undoubtedly hastened by both corporate discomfort with this new technology and the lack of top management support.
A short time after dropping the project, top management felt pressure to reestablish it. Because Auto Guys initially planned an enterprise-wide warehouse, they had considerable computer capacity. It was put to use on a much smaller project that focused exclusively on a single subject area. Other subject areas were due to be added once the initial subject area project was completed. Auto Guys expects to grow the warehouse to two terebytes within a year or two and eventually expand to their projected enterprise-wide data warehouse. The biggest difference between pre- and post-resurrection will be that the project will evolve incrementally.
Given his experience with the warehouse, the project manager made the following summary observations: (1) the management of expectations is critical to any sizeable data warehousing project; (2) proven technology, although not essential, does make the project easier to explain and justify; and (3) the construction of a sizeable data warehouse should be treated more like and R&D effort instead of a typical
IT project because of the time it takes to complete the project, the amount of money involved, and the short-term focus of top management.
Case Study 2: Government Research Laboratory
The Government Research Laboratory (GRL) has a finance department in each of the fifteen nearly identical laboratories that report to its national home office. As a member of the finance team, Bob was familiar with the monthly financial reports required by the home office. Although the financial reports themselves were not complicated, access to the mainframe where the data was housed was necessary, and an understanding of COBOL was needed to generate any report that differed from the standard. Once a month, reports would be distributed in paper form and each member of the finance team would sort through them and file them away. If the reports required any alteration, then someone from IS, or one of two people from finance familiar with COBOL, was contacted.
Because of these reporting difficulties, an IS manager made the suggestion that the company’s first data warehouse be constructed, and that the finance department be the primary beneficiary. Two people from IS began to work full-time on the project and a financial analyst also joined the group. The IS manager then offered a bonus to the IS technicians if they could get the data warehouse up and running by the end of the fiscal year which was just four month away.
Both the IS and the finance members of the team, firmly rooted in reality, knew this would be a difficult if not impossible task. But they resolved to give it their best shot and attempted a full transfer of all available reports to the warehouse. When it became clear that this was too ambitious, they cut out all of the detailed reports and focused on just the summaries, assuming the more detailed material could be integrated at some point after the initial deadline.
The team was successful and had all summary reports transferred to the data warehouse at the end of the fiscal year. The fact that the necessary tables were up and functional, however, was not an indicator of future success.
The first problem involved changes to the mainframe database which were initiated at the same time, but uncoordinated with, the data warehousing project. At the same time the foundation for the data warehouse was being laid, the planning system on the mainframe was undergoing modifications not captured in the data warehouse. In particular, changes in cost accounting standards within the organization changed the number of key summary categories from the standard five used in the past to seven, rendering the traditional five next to useless.
The second problem occurred when the goal to establish the data warehouse became the end goal.
As the GRL financial analyst for the team describes it, the feedback and modification period he had anticipated after September never came. The preliminary fix became the permanent solution. The analyst later learned that IS had always intended to set the system up but only funded its basic maintenance.
Modifications were not in the budget and the finance department, only minimally included in the warehouse project, never had a budget that would fund the inclusion of more data and alterations to the system.
Essentially, GRL found itself with a data warehouse that contained too little data and data that was outdated because of format changes in GRL’s cost accounting standards. Also, neither finance nor IS budgeted for changes necessary to create a fully functional data warehouse. Those two problems alone would have killed most data warehouse initiatives, but the problems did not end there.
The data warehouse was supposed to solve two accessibility problems. One involved the need for COBOL language expertise whenever a report required alteration, and the other involved the sheer mass of printed documents being disseminated and archived. Instead of providing a solution, reports theoretically available on a network were handled in much the same manner as the old reports. For one
thing, the data access software installed on each user’s PC was frequently incompatible with the mix of software already there. Many end-users, therefore, found access to the data warehouse difficult, and those who were able to access the data warehouse had such bad experiences with the new system they just did not use it. Also, the small minority that did not experience accessibility problems simply printed hard copies of the reports, which was no great change from how things had been done in the past.
Additionally, the programming barriers in existence when COBOL knowledge was necessary simply changed form. PowerBuilder, very much a programmer’s tool, was selected to build the user interfaces. Ironically, IS only had one individual with PowerBuilder skills, thus creating more of a bottleneck than had existed with COBOL.
The situation remained the same, if not worse, for three years following the first warehousing initiative. Finally, another IS project manager became interested in the idea of breathing life into the old warehouse. He was motivated by the organization’s solution to the Y2K problem, which involved abandoning the old mainframe system and transferring the old reports to the warehouse. Fortunately, his interest was accompanied by funding that allowed the enhancements anticipated at the very beginning of the first project to finally be realized. Also, all users are able to access Web-based reports.
Several things should have been done differently at GRL: (1) The warehousing initiative should have been in sync with mainframe changes and other IT initiatives throughout the lab; (2) Planning and resources should have been projected much farther into the future; (3) A pilot should have been done which probably would have identified a number of technical and fine tuning problems; (4) Deadlines should have reasonable.
Still, given the most recent developments, GRL’s financial analyst classifies his experience as a partial disappointment. “It could have been so much better,” he explains. “It could have been done right…for the right reasons.”
Case Study 3: Complicated Systems
The manager for Complicated Systems’ IT client information center started her job three years ago. That was six months after Complicated launched its data warehousing initiative that started with initial interest from the chief financial officer but shortly thereafter received its support from Complicated headquarters. A small group of people from Complicated’s main office, possessing no experience with data warehousing, decided which data would be appropriate and which data access tools would be utilized. This set limits on the future of the system based primarily upon the types of reports corporate headquarters assumed everybody needed and their arbitrary selection of OLAP tools.
With corporate headquarters championing the effort and supplying funds, the project had a lot going for it. However, end users were not brought into the picture even though they were the targeted beneficiaries. Information was immediately accessible to sales, service, marketing, and finance divisions around the world but it was not the right information.
Luckily for Complicated, certain things beyond strong corporate championing and finding worked to Complicated’s advantage. Extenuating circumstances included corporate’s initial design decision, the appearance of new initiatives from the marketing division, as well as top management, and the turnaround that the data warehousing project manager bought to the IS division.
First, an initial decision was made to Web-enable the database. This meant that although the information originally disseminated by the organization was of little value to those outside of top management, flexibility existed that would later allow the system to be put to use.
Second, independent initiatives from marketing and top management at headquarters, as well as more vocal end users than had existed in the past, started the move toward making the data more accessible and relevant to users. Specifically, marketing wanted access to valuable data already gathered.
At the same time, corporate headquarters was experiencing difficulties it thought might have solutions within the data warehouse. This further fortified the commitment of central management to their belief in the strategic benefit of the data warehouse and elicited moral support that went beyond the dollar commitment already made.
Third, and most difficult to assess, was the arrival of a new project manager to the IT client information center. When she arrived, the data warehouse, intended to support 140 branch offices and averaging four users per office, was going nowhere. The system contained data of little relevance to anyone outside of Complicated headquarters and the end users identified by management were unimpressed.
Turning the situation around required: (1) informing management of the lack of practical application of the warehouse; (2) obtaining adequate input from the end users to build a more useful system; and (3) translating all the input from marketing and central management into a technical solution. The past three or four months, according to the project manager, have seen the emergence of a system much more open to the needs of users. They have opted out of OLAP, selected tools more appropriate to a changing reporting structure, and bridged the communication gap between central management and the numerous branch offices.
Complicated was well on its way to a complete data warehousing failure for two significant reasons -no user involvement in determining information requirements and a poor data warehousing tool selection process. The situation is turning around, however, through the efforts of the data warehousing project manager, the continuing need for a data warehousing capability, and the fortuitous early decision to Web-enable the warehouse.
Case Study 4: North American Federal Government
A real-estate and property management unit in the North American Federal Government initiated and co-sponsored a data warehouse with the IT department. The IT department wrote a formal proposal. In it, an architectural plan was specified, costs were estimated at $800,000, the project’s duration was estimated to be eight months, and the responsibility for funding and manpower was defined as the business unit’s. The IT department never heard if the proposal was accepted but proceeded with the project assuming that there had been no problems with the proposal.
The project actually exceeded its eight-month schedule and lasted almost two years. Several factors contributed to the extra time. One was that the business unit stretched the detailed data analysis from one and a half months to nine months. Another was that the business unit kept expanding the planned user base. Over a six-month period, the number of planned users grew from 200 to 2,500. Also, to acquire the right technology for this project, a formal approval process of the Federal government took almost a year. Three weeks prior to technical delivery, the project was canceled by the IT director. The rationale was that the business unit was actually several months away from accepting deliver. Yet, six weeks after cancellation, a new interest in populating the warehouse emerged, but in the end, nothing was ever delivered and this failed endeavor cost the organization approximately $2.5 million.
There were three main reasons for the failure of this data warehouse project. One was lack of focus. The business unit had a difficult time identifying the scope of the project. It provided an information architecture and data framework but the details were defined very loosely. Also, the business unit kept pushing back the milestone dates which gave the impression that the project was neither urgent nor important.
Internal politics was another driving force behind this data warehouse disappointment. First, the business unit leader prevented analysts on the project from talking to the ultimate end users, but the reason was uncertain. Second, the business unit leader would go over the IT project leader’ head and
reassign staff to different tasks without informing the IT project leader. This further led to ambiguity as to what was to be accomplished and when. In the end, it was believed that the cancellation of the project was primarily because the IT director feared supporting a data warehouse. Staff and funding had recently been cut, and such an endeavor would further drain IT resources.
In hindsight, the IT project leader would have done two things differently. First, he would not have allowed the politics and overriding of authority to prevent inopportune and incorrect decisions as well as lack of direction. Second, the original plan for this data warehouse was to build a warehouse framework with a common language and then spin off subject area data from it. The manager believes that a better approach would have been to start with data marts and them work toward a full-blown data warehouse.
warehousing project was not represented. Project supporters assumed that the project would survive any budget cuts based upon the project’s past success and its relatively small budget, but they did not sit at the negotiating table. Additionally, Integrated Health’s central management knew very little about the project and political tension between the two hospitals still greatly influenced behaviors and attitudes. The decision was made to discontinue funding of the data warehousing initiative.
The project manager made the observation that many things occurring in concert brought a halt to Integrated Health’s data warehousing project. The first set of difficulties were corporate-based and rooted in past practices and personnel. Mistrust and suspicion at the hospitals added difficulty to the data integration project. Significant disconnect existed between the two hospitals and Integrated’s central management. Also, the lack of a champion at a level directly involved in the budgeting process eroded the effectiveness of the champion that did exist. Timing was a difficulty for Integrated Health since the project was too old in the sense that it threatened certain parties but too young in the sense that it had not yet proven itself. Had the project been either a year younger or a year older, guesses the project manager, it would have survived the budget cuts. Data warehouses are also more prone to failure, says the project manager, because they require a lot of funding and take multiple years to fully realize their potential. This is a stark contrast to corporate memory, which is short.