Ken Pohl writes a thoughtful article on the issues of project management of a data warehouse project, and how this can differ from other IT projects. As he points out, a data warehouse project is unusual in that it is essentially never finished - there are always new sources to add, new types of analysis the customers want etc (at least there are if the project is a success: if it failed then at least you won’t have too many of those pesky customer enhancement requests).

As the article points out, a data warehouse project is ideal for an iterative approach to development. The traditional “waterfall” approach whereby the requirements are documented at ever greater levels of detail, from feasibility through to requirements through to functional specification etc is an awkward approach. I have observed that in some companies the IT departments have a rigid approach to project management, demanding that all types of projects follow a waterfall structure. This is unfortunate in the case of data warehouse projects, where end-users are often hazy on requirements until they see the data, and where changing requirements will inevitable derail the neatest functional specification document (see diagram).
Given a 16 month average elapsed time for a data warehouse project (TDWI) it is almost certain that at least one, and possibly several, major changes will come along that have significant impact on the project, which in a waterfall approach will at the very least cause delays and may put the entire project at risk.

By contrast a data warehouse project that bites off scope in limited chunks, while retaining a broad and robust enterprise business model, can deliver incremental value to its customers, fixing things as needed before the end users become cynical, and gradually building political credibility for the warehouse project. Of course the more responsive to change your data warehouse is the better, but even for a traditional custom build it should be possible to segment the project delivery into manageable chunks and deliver incrementally. The data warehouse projects which I have seen go wrong are very often those which have stuck to a rigid waterfall approach, which makes perfect sense for a transaction processing system (where requirements are much more stable) but is asking for trouble in a data warehouse project. Ken Pohl’s article contains some useful tips, and is well worth reading.

A brief article by Bill Inmon addresses a key point often overlooked - when is a data warehouse finished? The answer is never, since the warehouse must be constantly updated to reflect changes in the business e.g. reorganizations, new product lines, acquisitions etc.

Yet this is a problem because today’s main data warehouse design approaches result in extremely high maintenance costs - 72% of build costs according to TDWI. If a data warehouse costs USD 3M to build and USD 2.1M to maintain annually then over five years you are looking at costs well over USD 11m (let’s generous allow a year to build plus four years of maintenance) i.e. many times the original project cost. These levels of cost are what the industry has got used
to, but these are very high compared to maintenance costs for OLTP systems, which ttypically run at 15% of build costs annually. This high cost level, and the delays in responding to business change when the warehouse schema needs to be updated, contribute to poor perception of data warehouses in the business community, and high perceived failure rates. As noted elsewhere, data warehouses built on generic design principles are far more robust to business change, and have levels of maintenance around 15%.

If the data warehouse industry (and the business intelligence industry which feeds on it) is to continue to grow then it needs to grow up also, and address the issue of better data warehouse design paradigms. 72% annual maintenance costs are not acceptable.

A Gartner Group report mentions that at least 50% of data warehouse projects fail. Of course on its own this sounds bad, but just how bad is it, and what is meant by failure e.g. is being one month late failure, or does it mean complete failure to deliver? How do IT projects in general do? Standish Group run a fairly exacting survey which in 2003 covered 13,522 IT projects, a very large sample indeed. Of these just 34% were an “unqualified success”. Complete failure to deliver were just 15%. The rest are in the middle i.e. they delivered but were not perceived to be complete successes in some way. To be precise: 51% had “cost overruns, time overruns, and projects not delivered with the right functionality to support the business”. Unfortunately the Gartner note does not define “failure” as precisely as Standish; they define the “over 50% as being “limited acceptance or be outright failures”. It is also unclear whether the Gartner figure was a prediction based on hard data, or the opinion of one or more of their analysts.

The Standish study usefully splits the success rate by project size, with a miserable 2% of projects larger than USD 10M in size being complete successes, with 46% of projects below USD 750k being complete successes, 32% up to USD 3M and, 23% at USD 3-6M and 11% at USD 6-10M. The average data warehouse project is somewhere around the USD 2-5M range, with USD 3M often quoted, so indeed on this basis it would seem we should only expect around 25% or so to be “unqualified successes”. Unfortunately I don’t have data available for the failure rate split by size, which presumably may follow a similar pattern, and the rather loose definition that Gartner use makes it hard to compare like with like.

Even if turns out that data warehouse projects aren’t any (or at least much) worse than other IT projects, this is not a great advert for the IT industry. The Standish data most certainly gives a clear message that if you can possible reduce the scope of a project to smaller, bite-sized projects, then you greatly enhance your chance of success. It has long been known that IT productivity drops as projects get larger. This is due to human nature - the more people you have to work with, the more communication is needed, the more complex things become, and the more chance of things being misunderstood or overlooked.

It is interesting that even very large data warehouse projects can be effectively managed in bite-sized chunks, at least if you use a federated approach rather than trying to stuff the entire enterprise’s data into a single warehouse. Projects at BP, Unilever, Philips, Shell and others have taken a country by country approach, or a business line by business line approach, with individual warehouses feeding up to either regional ones or a global one, or indeed both. In this case each project becomes a fairly modest implementation, but there my be many of them. The Shell OP MIS project involved 66 separate country implementations, three regional and one global. Overall a USD 50M project, but broken down into lots of manageable, repeatable pieces.

So, if you data warehouse project is not to become desperate, think carefully about a federated architecture rather than big bang. This may not always be possible, but you will have a greater chance of success.

There were some interesting results in a survey of 150 big-company respondents conducted by Ventana Research as to where customers saw the main benefits of master data management (MDM). The most important areas were:

• better accuracy of reporting and business intelligence 59%
• improvement of operational efficiency 27%
• cost reduction of existing IT investments 8%

It is encouraging that respondents place such a heavy emphasis on business issues compared to IT, since quite apart from this sounding quite right (MDM can improve customer delivery errors, billing problems etc) they will have a much better chance of justifying an MDM project if the benefit case is related to business improvement than the old chestnut of reduced IT costs (which so rarely appear in reality - surely IT departments would have shrunk to nothing by now if all the projects promising “reduced IT costs”over the years had actually delivered their promised benefits). A nice example of how to justify an MDM project can be found in a separate article today, in this example specifically about better customer information.

The survey also reflects my experience of the evolution of MDM initiatives, which tend to start in a “discovery”phase where a company takes stock of all its master data and begins to fix inconsistency, which initially impact analytic and reporting applications. Later, after this phase, companies begin to address the automation of the workflow around updating master data, and finally reach the stage of connecting this workflow up to middleware which will physically update the operational systems from a master data repository. This last phase is where many of the operational efficiency benefits will kick in, and these may be very substantial indeed.

Based on the rapidly increasing level of interest in MDM, in 2006 I expect to see a lot of the current exploratory conversations turning into more concrete projects, each of which will need a good business case. At present MDM projects tend to be done by pioneering companies, so it will be very interesting to see if the various projections prove accurate and MDM starts to become more mainstream.

The software industry is not good at learning from previous lessons and mistakes. We seem to re-invent the wheel at fairly regular intervals, perhaps because a lot of people working in the technology industry are quite young and perhaps because we assume that anything done ten or more years ago is inherently outdated. One area I regularly observe this collective blind spot is in estimating and project management. Software projects have a poor track record of coming in on time and budget, and this has a number of causes. One is unrealistic expectations. A wise aeronautical engineer once said: “better, cheaper, faster: pick any two, but never all three” yet we all still encounter projects where the end date seems to be set by some surreal remit (”the CEO wants it in time for Christmas” syndrome) without regard to the feasibility or effect on the project deliverable. Moreover, when a project does hit problems, as all too many do, there still seems to be the impression that throwing more resources at it will claw back the time lost. Sadly this is rarely the case.

There is some useful theory on this subject. A number of writers in the 1980s published algorithms to help estimate project duration and team size, based on the observation of many software projects. You can read more about this subject in books such as “Controlling Software Projects“by Tom deMarco, Software Engineering Economics by Barry Boehm and Measures for Excellence by Putnam and Myers. However these sources agree that the evidence is that in order to bring a project end-date forward you need to deploy exponentially more resources to do that. The theory actually shows an equation relating the elapsed time to effort as:

effort = constant/time to the power four

which for the less mathematically inclined means that the end date (project time) has a BIG effect on the effort needed. For example a project that was estimated at 18 months elapsed (a nice round number selected by IT management) could, if it was extended to 19 months, be done with 20% less effort. That’s right: by extending your elapsed time by 5% you need 20% less effort. When I first saw this it seemed almost absurd, until I got involved briefly in project estimating when I worked at Shell and observed two projects in the upstream. They were that rare thing: the same project, but being done in different Shell subsidiaries. They were independent, and were the same size and scope. One project was estimated to 13 months, the other was to take the same, but in one project a decision was taken to bring forward the elapsed time to 12 months in order to fit in with another project. Money was not a major factor on this particular project, and more resource was piled in to bring the date forward. Remarkably, the compressed project took 50% more effort to bring in than the one which ran its “natural” course, something that caused general bewilderment at the time but one which actually fits in tolerably well with the software equation above.

Why is the effect so dramatic? By adding new people to a project, people that were working on the project have to stop what they were doing an on-board the new people. Now there is a bigger team, the communication becomes trickier, as more people need to be involved and the project specification is open to interpretation by more people. As you add more and more people the problem worsens: now people don’t fit into one room any more, so they have to have a meeting in order to solve a problem rather than just turning to their neighbor etc.

The message is that if you have a project that is having problems with its schedule, it is much easier to reduce the scope of the project slightly, and deliver the rest of the functionality in a later phase, than it is to try and pile on more resources to cram it into the original schedule. If you can’t reduce the scope of the project then you can only make the people on project more productive (good luck with that) or add a LOT of resources. At least there is a formula you can look up to tell you many more resources you need, even if management won’t like the answer.

Tony Lock writes an excellent article in this week’s IT-Director.com newsletter, highlighting the communication gap between IT departments and their customers. A new survey by Coleman-Parkes found that amongst 214 FTSE 750 organizations, only 18 percent held weekly meetings between business managers and the IT teams. The research also indicated that 31 percent of those surveyed claim that they never or hardly ever have such meetings. In large corporate IT departments there can be a culture of avoiding contact with “users”, who always seem to have strange and unreasonable demands that don’t fit into the perceptions of the IT department. The atmosphere can become quite hostile if IT departments set themselves up as “consultancy” organizations that charge themselves out to their internal customers. The internal customers resent being forced to use an internal service that they often perceive as unresponsive, and can be outraged to find themselves being charged at similar rates to external service providers. Some of this is not reasonable - those same customers are forced to use internal legal counsel and are charged through the nose, whether or not they like it. However there is a peculiar frustration with many business users over their IT departments that can boil over when discussing charge-back mechanisms and service level agreements.

Over-elaborate internal billing systems can cause unnecessary cost and frustration. I recall when I was at Exxon seeing an instructive project to review internal data centre charges. The existing system was extremely elaborate and charged based on mainframe usage, disk storage, communications costs and a whole raft of items. Most users didn’t understand half the items on their bills, or played games to try and avoid hitting arbitrary pricing thresholds. None of this added one iota of revenue to Exxon. The project manager, a very gifted gentleman called Graham Nichols (on his way rapidly up the organization), successfully recommended replacing the entire system with a single charge once per year. This saved a few million pounds in administration and endless arguments, and people’s tempers were much improved all round.

Perhaps some of the problem is when an organization grows very large, it is difficult to keep perspective. Shell employed around 10,000 IT staff in the 1990s, directly or indirectly, so it perhaps not surprising that the IT staff concentrated on their own internal targets and objectives, rather than troubling themselves too much to align themselves with the objectives of the core energy business. At a time when the oil industry was struggling with oil prices heading down towards 10 dollars, and so with serious cost-cutting going on all round, the internal IT group, living through the internet boom, was hiring to keep up with demand e.g. dealing with the Y2K problem. Seeing redundancies going on in engineering and marketing at the same time as a hiring boom in internal IT, tempers became frayed, to put it mildly.

Clearly senior internal IT staff do need to spend more time with their business customers, and find out how they can help them achieve their objectives. Moreover they need to communicate this throughout their organizations. How many internal IT staff know the top three business objectives of their company this year? Without even a vague idea of the goals that the business is pursuing, it is hardly surprising that business leaders become frustrated with internal IT groups. Those 31% of internal IT groups who never or hardly ever meet with their customers need to change this attitude or get used to living on a Bangalore salary in the future.

One of the perennial issues that dogs IT departments is the gap between customer expectations and the IT system that is actually delivered. There are many causes of this e.g. the long gap between “functional spec” and actual delivery, but one that is rarely discussed is he language of the business model. When a systems analyst specifies a system they will typically draw up a logical data model and a process model to describe the system to be built. The standard way of doing the former is witti entity relationship modelling, which is well established but has one major drawback in my experiences: business people don’t get it. Shell made some excellent progress in the 1990s at trying to get business people to agree on a common data model for the various parts of Shell’s business, a thankless task in itself. What was interesting was that they had to drop the idea of using “standard” entity relationship modelling to do it, as the business people at Shell just could not relate to it.

At that time two very experienced consultants at Shell, Bruce Ottmann and Matthew West, did some ground-breaking research into advanced data modelling that was offered to the public domain and became ISO standard 15926. One side effect of the research was a different notation to describe the data used by a business, which turned out to be a lot more intuitive than the traditional ER models implemented in tools like Erwin. This notation, and much else besides is described in an excellent whitepaper by Bruce Ottmann (who is now with Kalido).
We use this notational form at Kalido when delivering projects to clients as diverse as Unilever, HBOS and Intelsat, and have found it very effective in communicating between IT and business people. The notation itself is public domain and not specific to Kalido, and I’d encourage you to read the whitepaper and try it out for yourself.

An article in Intelligent Enterprise asks “why can’t vendors and customers just get along?” after explaining the many issues at which they are usually at loggerheads. Having been both a customer and a software vendor, I think Joshua Greenbaum points to one key point in his article: honesty. As a customer I found that software vendors frequently made patently absurd claims about their software “this tool will speed up application development by 1000%” being one memorable example. Release dates for software were another issue: vendors fail to grasp that businesses have to plan for change all the time, so a release date slipping by three months is rarely an issue provided that you are told about it in good time. However if you have lined up a load of resources to do an upgrade, it just not turning up on the appointed day does cause real cost and annoyance.

Another bug-bear is testing, a tricky subject since it is impossible to fully test all but the most trivial software (see the excellent book “the art of software testing“) . However vendors vary dramatically on the degree of effort that they put in. At Kalido we have extensive automated test routines which run on every build of the software, which at least means that quite a lot of bugs get picked up automatically, though bugs of course still get through. Yet according to someone who used to work at Oracle, there it was policy to do minimal testing of software, where the testing strategy was described as “compile and ship”. This certainly avoids the need for lots of expensive testers, but is hardly what customers expect.

However, customers can be unreasonable too. Their legal departments insist on using sometimes surreal contract templates that were often designed for buying bits of building equipment rather than software, resulting in needless delays in contract negotiation (but in-house corporate lawyers don’t care about this, indeed it helps keep them busy). They can also make absurd demands: we recently lost a contract after refusing to certify that we would fix ANY bug in the software within eight hours, something which patently cannot be guaranteed by anyone, however responsive. A small vendor who won the bid signed up to this demand, and so will presumably be in breach of contract pretty much every day. Quite what the customer thinks they have gained from this is unclear. It is not clear why some customers behave in such ways, perhaps they feel like exacting revenge for previous bad experiences with vendors, or maybe some corporate cultures value aggressive negotiating.

From my experience on both sides of the fence, the best relationships occur when both parties understand that buying enterprise software is a long-term thing, in which it is important that both sides feel they are getting value. Vendors are more inclined to go the extra mile to fix a customer problem if that customer has been doing lots of reference calls for them, and actively participates in beta test programs, for example. As with many things in life, there needs to be a spirit of mutual respect and co-operation between customers and vendors if both are to get the best out of their relationship.

I permitted myself a wry smile when I first heard the hype about “real time” business intelligence, which is hyped again this week . The vision sounds appealing enough: as soon as someone in Brazil types in a new sales order, the ultra-swish business intelligence system in central office knows and reacts immediately. Those who have worked in large corporations will be entertained by the naivety of this, since most large companies would be grateful just to know who their most profitable global accounts are.

The mismatch between fantasy and reality is driven by two factors. The first is that business rules and structures (general ledgers, product classification, asset hierarchies, etc) are not in fact uniform, but are spread out among many disparate transaction system implementations - one survey found that the average Global 2000 company has 38 different sources of product master data alone. Yes, this is after all that money spent in ERP. Large companies typically have dozens or even hundreds of separate ERP implementations, each with a subtly different set of business structures from the next (plus the few hundred other systems they still have around). The second problem is that the landscape of business structures is itself in constant flux, as groups reorganize, subsidiaries are sold or new companies acquired.

Today’s business intelligence and data warehouse products try to sweep this reality under the carpet, producing tools to convert the source data into a lowest common denominator consistent set that can be loaded into a central data warehouse. This simplification is understandable, but means that local variations are lost, and many types of analyses are not possible. Worse, if the business structures change in the source systems, then the data warehouses and reports built on top of them are undermined, with any changes to the structure of the data warehouse taking typically months to bring about. In these intervening months, what happens to the “real time” business intelligence?

The problem comes down to fundamental truth: databases do not like having their structure changed. Adding data is fine, but something which affects the structure of a database (a major reorganization will usually do the trick) will cause pain. If you doubt this, ask a CFO how long it will take him or her to integrate an acquisition just enough to be able to run the management accounts as one combined entity. For some companies acquisitions are a way of life, with several undertaken a year. Such companies are always chasing their tail in terms of trying to get a complete picture of their business performance. This is not just inconvenient but also costly: one company built a large and well-used conventional data warehouse, costing USD 4 million to build. When they properly accounted for all aspects of maintenance, including business user time (which few companies do) they found it was costing USD 3.7 million per year to maintain. There was nothing wrong with the warehouse design; they were operating in a volatile business environment, with 80% of the maintenance cost caused by dealing with business change.

What is needed, and generally what the industry has failed to deliver, are technology solutions that are comfortable dealing with business change: “smarter” software. Today few IT systems can cope with a change in the structure of the data coming into the system without significant rework. The reason for this is in the heart of the way that databases are designed. They are usually implemented to reflect how the business is structured today, with relatively little regard to how to deal with future, possibly, unpredictable, change. Introductory courses on data modeling show “department” and “employee” with a “one-many” relationship between them i.e. a department can have many employees, but a person can be only in one department (and must be in one department). This is easy to understand and typical of the way data models are built up, yet even this most basic model is flawed. I have myself been in between departments for a time, and at another time was briefly part of two departments simultaneously. Hence the simple model works most of the time, but not all of the time: it is not resilient to exceptional cases, and IT systems built on this model will break and need maintenance to cope when such special cases arise. This is a trivial example, but it underlies the way in which systems, both custom built and packaged, are generally built today. Of course it is hard (and expensive) to cater for future and hence somewhat unknown change, but without greater “software IQ” we will be forever patching our systems and discovering that each package upgrade is a surprisingly costly process. If you are the CFO of a large company, and you know that it takes years to integrate the IT systems of an acquired company, and yet you are making several acquisitions each year, then getting a complete view of the business performance of your corporation requires teams of analysts with Excel spreadsheets, the modern equivalent of slaughtering a goat and gazing at its entrails for hidden meaning.

Some techniques in software are emerging that tackle the problem in a more future-oriented way, but these are the exception today. Unfortunately the vendor community finds it easier to sell appealing dreams than to build software to actually deliver them. “Real-time business intelligence” comes from the same stable as those who brought you the paperless office and executive information systems (remember those?) where the chief executive just touches a screen and the company instantly reacts. Back in reality, where it takes months to reflect a reorganization in the IT systems, and many months more just to upgrade a core ERP system to a new version, “real time” business intelligence remains a pipe dream. As long as people design data models and databases the traditional way, you can forget about true “real-time” business intelligence across an enterprise: the real world gets in the way. It is interesting that the only actual customer quoted in the techworld article, Dr Steve Lerner of Merial, had concluded that weekly data was plenty: “The consensus among the business users was that there was no way they were prepared to make business decisions based on sales other than on a weekly basis”.

On SAP and Zombies

I worked for many years in Exxon and Shell and noticed something curious about large (are there any other kind?) SAP implementations: something odd happens to the people on them. Previously reasonable people would start to view the world entirely through the eyes of SAP, as though by using the software they had joined some secret society or cult. Despite any evidence to the contrary, it was as if these people had their critical faculties removed when discussing SAP applications – which could do no wrong even when there were clear problems or issues. If, for example, you mentioned some issue with the software or project, a glazed look would come into their eyes as of they were extras in “Invasion of the Body Snatchers” http://www.imdb.com/title/tt0049366/combined

I noticed this for the first time after being involved in an SAP roll-out at Exxon in the late 1980s, one of the first large-scale SAP projects outside of Germany. The project was justified because Esso UK (where I worked) had very old transaction systems that needed replacing with something, and a previous attempt to implement software from Walker had been a fiasco. The business case rested on getting rid of all the accounts clerks who raised invoices and processed orders, the idea being that the rest of us employees would do this instead using SAP. So if you wanted anything from stationery to a new part for petrol station, you would use the system instead of involving people from finance. Unfortunately SAP at the time was still partly in German, and had a quite complex user interface that involved remembering various esoteric codes to do anything like post an invoice, so although we were all sent on training, things did not go well. After a period of denial, it emerged that Esso’s suppliers were not being paid to such an extent that there were issues about the company’s credit rating, and so all the old finance clerks (and more) were re-hired to sort out the mess. To save face, they were distributed around the business lines in order to not make it look like there were now more admin and finance staff than there were before the system. This was the first instance of denial around SAP that I observed – the project was “too big to fail”.

A little later I moved to Shell UK and was invited to a presentation by a gentleman from Shell Centre who will remain nameless; let’s call him Roger. We were to hear about Shell’s new IT strategy. Flanked by consultants from PWC, Roger proceeded to explain that Shell was going to implement SAP. The bulk of the presentation was done by PWC, and was light on details e.g. the only business case seemed to be “er, other big companies are doing it”. When I mentioned that the Esso UK implementation had not delivered its promised benefits there was another zombie-like experience, with Roger saying that he had been on exotic trips to all sorts of companies in warm locations in order to research the area, and there would be no problem. I asked “Have you ever seen an SAP screen” to which the reply “I don’t need to” was not the comforting response I had hoped for.. If you were about to spend a large but unspecified amount of money on a system and you were in charge of the recommendation, would it not have been prudent to have cast a quick glance at what was being bought? Apparently not. Fortunately the consultants from PWC, who at the time got 11% of their worldwide revenue from SAP implementations and so were utterly objective advisors, saw no problem at all either.

The space-out stares continued when we were selecting a finance, time-writing and billing system for Shell Services International, the internal IT arm of Shell at the time (1998). Despite that fact that SAP was designed for manufacturing companies and not services companies, and despite the fact that none of the consultancy firms implementing SAP used it for their internal tracking, SSI management selected SAP for this purpose. What was required was very simple and could have been done in a dozen commercial packages, or indeed probably an Excel spreadsheet on steroids, but SAP it was. The cost of this, for an organisation with under USD 1 billion in total revenue (and loss making at that) was estimated at USD 50 million i.e. 5% of total revenues, which should have set off some alarm bells (most big companies spend less than 2% of their revenue on IT, never mind one project). Not a bit of it, and the project duly clanked into life. What did it cost? Not a popular question, and eventually someone admitted that it had cost USD 70 million “up to the time they stopped counting”. Did anyone get into trouble over this? Far from it. Were the customers happy with their new billing systems? I can assure you they were not.

So what is it that causes such odd behaviour? I can only speculate that once a project reaches a certain size then it indeed cannot be seen to fail – too many senior people had their name on the decision to implement, and so problems dare not be acknowledged. The people on the project, comforted by the sheer scale of the thing going on around them and concentrating on delivery, have trouble seeing the wood for the trees. The scary thing was the inability to even raise issues for fear of being labelled “not a team player”. I’m not sure if this is something that occurs on any very large IT project, as I doubt there is anything peculiar about SAP that induces such lack of objectivity. However it is an odd experience, seeing those around you who you formerly trusted seemingly oblivious to all issues and suddenly incapable of critical reasoning. I felt like Kevin McCarthy’s character Miles Bennel in the “Invasion of the Body Snatchers” movie at the end, running around shouting: “you’re next, you’re next!”