There is an interesting piece in Advertising Age by Jack Neff discussing the increasing desire of companies to move to premier pricing, even at the cost of losing some customers.  It raises the critical question - how good is the information that companies have on the performance of their brands?  For a multi-national company in particular even tracking the profit margin of a particular product or brand can be a thorny issue - do the French and the Italian subsidiaries allocate all their costs in the same way as the Brazilians or the Japanese?  Chances are they do not, and plenty of internal meeting time is spent debating whose country’s operations are more effective.  Yet pricing is a powerful weapon. I recall when I worked on a project at Shell Retail that we had just put in a data warehouse that tracked every single (non fuel) transaction at all Shell’s shops in Germany.  For the first time the category managers were able to see comprehensive information about sales by store, by SKU etc each morning.  In this way they could track the behaviour of promotions, for example, and rapidly see the effect of other changes they made.  For example in one cluster of stores they experimented by raising the price of wine to see what effect it had on volume sold.  Interestingly, it had no effect at all, so they kept nudging up the price until an impact could be seen.  This single decision, when multiplied across all the stores in Germany, had a significant impact on profitability.

Pricing is a complex animal and hard to predict its effects in advance.  Hence it is critical that you have in place an information infrastructure that allows you to see the true operating margins of products and brands, and also the margin by customer and channel.  This may require a major investment, since if the cost allocation rules are not uniform in a corporation then you will need a system that can cope, and be able to continue coping when changes occur.  You may not need “real time” information, but as in the above example you at least want to be able to see the effect of decisions the next day, perhaps within hours.

The article mentions Unilever as a case study amongst others, and indeed its comprehensive network of linked data warehouses around the world give it precisely the ability to see true gross margin across all their brands.  This enables them to decide which brands to invest in further, and which to pare back, as well as enabling them to track more operational aspects of business performance.

This ability, sometimes called “3D marketing” i.e. to be track performance across multiple business dimensions like customer, channel and product, is a powerful enabler to more subtle and profitable pricing decisions. In some projects I am aware of serious pricing discrepancies causing very real gross margin issues, which were buried away in a maze of confusing systems and so went unnoticed, in some cases for years.  Clever pricing can be highly profitable just as inadvertently poor pricing can cost you money.  The common denominator is having the ability to track profitability at the detailed level, in any business dimension, across the globe. Companies like Unilever have shown that this can be done.

In an article in the beye network Mike Ferguson makes an interesting observation which many seem to have missed. A current “theme” is that business intelligence needs to be embedded into operational systems. This innocent sounding notion is of course entirely unrelated to the need for BI vendors to sell more licenses of their software in what has become a slightly saturated market. As I have written about earlier, it is by no means clear that everyone in an organization needs a BI tool, whatever the wishes of BI vendor sales staff. So trying this new tack of bundling BI in with operational systems (which many people do need, or at least have to use) is a cunning ploy. However, as Mike Ferguson notes, if this is done without a basis for common master data and common business rules, then all that will happen is that we will get lots of competing BI reports, all with different numbers and results. The whole notion of data warehousing was created in order to resolve the differences in definitions that exist between operational systems. Each separate ERP implementation will usually have slightly different data, never mind all the other operational systems. By going through a process of trying to get to an enterprise-wide common subset of definitions (Mike calls it a “shared business vocabulary”) then these differences can be understood, and data quality improved back in the source systems. Without such an underlying basis we merely have snapshots of operational data without resolving the data quality issues, and without resolving the inconsistencies. In other words, we will be pretty much back where we were before data warehouses.

There certainly may be valid examples where it makes sense to embed some simple BI capability on the top of operational data, especially in the context of operational reporting where you are only interested in the data in that particular system. However as soon as you want to take a cross functional or enterprise view, then that pesky data inconsistency and data quality has to be dealt with somehow. Putting complex BI tools in the hands of the front-line staff doing order entry is not going to resolve this issue -it may just confuse it further.

Margaret Harvey points out in a recent article that the effort of integrating the IT systems of two merged companies can be a major constraint and affect the success of the merger. Certainly this is an area that is often neglected in the heat of the deal. But once the investment bankers have collected their fees and an acquisition or merger is done, what is the best approach to integrating IT systems? What is often missed is that, in addition to different systems e.g. one company might use SAP for ERP and the other Oracle, the immediate problem is that the two companies will have completely different coding systems and terminology for everything, from the chart of accounts, through to product and asset hierarchies, customer segmentation, procurement supplier structures and even HR classifications. Even if you have many systems from the same vendor, this will not help you much given that all the business rules and definitions will be different in the two systems.

To begin with the priority should be to understand business performance across the combined new entity, and this does not necessarily involve ripping out half the operational systems. When HBOS did their merger, both Halifax and Bank of Scotland had the same procurement system, but it was soon discovered that this helped little in taking a single view of suppliers across the new group given the different classification of suppliers in each system. To convert all the data from one system into the other was estimated to take well over a year, but instead they put a data warehouse system in which mapped the two supplier hierarchies together, enabling a single view to be taken even though the two underlying systems were still in place. This system was deployed in just three months, giving an immediate view of combined procurement and enabling large savings to be rapidly made. A similar appraoch was taken when Shell bought Pennzoil, and when Intelsat bought Loral.

It makes sense initially to follow this approach so that a picture of operating performance can quickly be made, but at some point you will want to rationalize the operational systems of the two companies, in order to reduce support costs and eliminate duplicated skill sets. It would be helpful to draw up an asset register of the IT systems of the two companies, but just listing the names and broad functional areas of the systems covered is only of limited use. You also need to know the depth of coverage of the systems, and the likely cost of replacement. Clearly, each company may have some systems in much better shape than others, so unless it is case of a whale swallowing a minnow, it is likely that some selection of systems from both sides will be in order. To be able to have a stab at estimating replacement costs, you could use a fairly old but useful technique to estimate application size: function points.

Function points are a measure of system “size” that does not depend on knowing about the underlying technology used to build the system, so applies equally to packages and custom-build systems. Once you know that a system is, say, 2000 function points in size, then there are well established metrics on how long it costs to replace such a system e.g. for transaction systems, a ballpark figure of 25-30 function points per man month can be delivered, which does not really seem to change much whether it is a package or in-house. Hence a 2000 function point transaction system will cost about 80 man-months to build or implement, as a first pass estimate. MIS systems are less demanding technically than transaction systems (as they are generally read only) and better productivity figures can be be achieved here. These industry averages turned to be about right when I was involved in a metrics program at Shell in the mid 1990s. At that time a number of Shell companies counted function points and discovered productivity of around 15 - 30 function points per man month delivered for medium sized transaction systems, irrespective of whether these were in-house systems or packages. Larger projects had lower productivity, smaller projects have higher productivity, so delivering a 20,000 function point system will be a lot worse than a 2,000 function point system in terms of productivity i.e. fewer function points per man month will be delivered on the larger system. Counting function points in full is tedious and indeed is the single factor that has relegated it to something of a geek niche, yet there are short cut estimating techniques that are fairly accurate and are vastly quicker to do that counting in full. By using these short-cut techniques a broadly accurate picture of an application inventory can be pulled together quite quickly, and this should be good enough for a first pass estimate.

There are a host of good books that discuss project metrics and productivity factors which you can read for more detailed guidance. The point here is that by constructing an inventory of the IT applications of both companies involved in a merger you can get a better feel for the likely cost of replacing those systems, and hence make a business case for doing this. In this way you can have a structured approach to deciding which systems to retire, and avoid the two parties on either side of the merger just defending their own systems without regard to functionality or cost of replacement. Knowing the true costs involved of systems integration should be part of the merger due diligence.

Further reading:

Software Engineering Economics
Controlling Software Projects
Function Points

Robert Farris makes some good observations in his recent article in DM Review. He points out that many companies have ended up with business intelligence being distributed throughout the company e.g. in various subsidiaries and departments, and this makes it very difficult to take a joined up view across the enterprise. As he notes, disjointed initiatives can result in poor investments. Hence it is critical to take an overall view to business intelligence, yet to do so is such a large task that it seems daunting.

In my experience there are a number of things that can at least improve the chances of an enterprise-wide BI initiative succeeding. It sounds like motherhood and apple pie, but the project needs business sponsorship if it is to succeed; IT is rarely in a position to drive such a project. However that statement on its own is of limited help. How can you find this elusive sponsorship?

The place to start is to find the people that actually have the problem, which is usually either the CFO or the head of marketing. The CFO has the job of answering the questions of the executive team about how the company is performing, so knows what a pain it is to get reliable numbers out of all of those bickering departments and subsidiaries. The head of marketing is the one who most needs data to drive his or her business, usually involving looking at trends over time and involving data from outside the corporate systems, and this is usually poorly provided for by internal systems. The CEO might be a sponsor, but often the CFO will be carefully feeding impressive looking charts to the CEO to give the impression that finance is in control of things, so the CEO might not be aware of how difficult data is to get hold of. The head of operations or manufacturing is another candidate, though this person may be too bogged down in operational problems to give you much time. If there is someone responsible for logistics and supply chain then this is often a fruitful area. Sales people usually hate numbers unless it is connected with their commissions (where they demonstrate previously unsuspected numerical ability), and HR usually doesn’t have any money or political clout, so marketing and finance are probably your best bet.

So, you have a sponsor. The next step is to begin to sort out the cross-enterprise data that actually causes all the problems in taking a holistic view, which is these days being termed master data. If you have multiple charts of accounts, inconsistent cost allocation rules, multiple sources of product definition or customer segmentation (and almost all companies do) then it this is a barrier in the way of your BI initiative succeeding. There is no quick fix here, but get backing to set up a master data management improvement project, driven by someone keen on the business side. Justifying this is easier than you may think.

In parallel with this you will want a corporate-wide data warehouse. Of course you may already have one, but it is almost certainly filled with out of data data of variable quality, and may be groaning under a backlog of change requests. If it is not, then it is probably not being used much and may be ripe for replacement. To find out, do a health check. There is a bit of a renaissance in data warehouses these days, and these days you can buy a solution rather than having to build everything from scratch.

In truth your company probably has numerous warehouses already, perhaps on a departmental or country basis, so it is probably a matter of linking these up properly rather than having to do everything from the beginning. This will enable you to take an iterative approach, picking off areas that have high business value and fixing these up first. Once you can demonstrate some early success then you will find it much easier to continue getting sponsorship.

In one of the early Shell data warehouse projects I was involved with we had a very successful initial project in one business line and subsidiary, and this success led to a broader roll-out in other countries, and then finally other business lines came willingly into the project because they could see the earlier successes. This may seem like a longer route to take, but as noted by Robert Farris, this is a journey not a project, and if you start with something vast in scope it will most likely sink under its own weight. Much better to have a series of achievable goals, picking off one business area at a time, or one country at a time, delivering incrementally better solutions and so building credibility with the people that count: the business users.

Elephants need to be eaten in small chunks.

Madan Sheina, who is one of the smarter analysts out there, has written an excellent piece in Computer Business Review on an old hobby horse of mine: data warehouses that are unnecessarily large. I won’t rehash the arguments that are made in the article here (in which Madan is kind enough to quote me) as you can read it for yourself but you can be sure that bigger is not necessarily better when it comes to making sense of your business peformance: indeed the opposite is usually true.

Giant data warehouses certainly benefit storage vendors, hardware vendors, consultants who build and tune them and DBAs, who love to discuss their largest database as if is was a proxy for their, er, masculinity (apologies to those female DBAs out there, but you know what I mean; it is good for your resume to have worked on very large databases). The trouble is that high volumes of data make it harder to quickly analyse data in a meaninfgul way, and in most cases this sort of data warehouse elephantitis can be avoided by careful consideration of the use cases,probably saving a lot of money to boot. Of course that would involve IT people actually talking to he business users, I won’t be holding my breath for this more thoughtful approach to take off as a trend. Well done Madan for another thoughtful article.

Not all data warehouses are created equal. Indeed both custom-built and some packaged data warehouse products can have surprising limitations in terms of their functionality. Just as I referred recently to Dave Waddington’s excellent checklist of things that would indicate a master data management problem, I would like to propose a series of questions that could be used to assess the depth of functionality of your data warehouse, whether it is custom built or packaged. For this list I am indebted to Dr Steve Lerner (until recently IS Director, Global Finance Applications and Integration at pharmaceutical firm Merial), who was kind enough to set out a series of symptoms that he had found indicated a problem with a data warehouse application. What I like about these is that they are all real business problems, and not a series of features defined by a software vendor or database designer. They are as follows.

1. Do you have difficulty conducting what-if analysis for a variety of business or product or geographical hierarchies?

2. Would it be hard for your current system to determine the impact of a business organization change on Operating Income?

3. Would it be hard for your current system to determine the impact of realigning geographical associations on regional profitability estimates?

4. Do you have difficulty restating historical data?

5. Can you view historic data using both a time-of-transaction basis and a current basis?

6. Can you currently restate historical data using new account structures?

7. Do you have difficulty viewing composites of data from sources with different granularities along key dimensions (i.e., comparing daily sales for a month, to forecast sales done monthly, to your annual profit plan, and to a five year long-range projection)?

8. Do you have difficulty with “bad data” getting into your current data warehouse?

9. Do you have difficulty maintaining the accuracy of your reference data?

10. Do you have difficulty with traceability from source to report?

So, how did your data warehouse application score? If it did not do well (i.e. failed on several of these ten points) then you should be concerned, because business users are likely to do exactly these types of things with the data in the warehouses, if not today then at some point. When they struggle, they will come looking for you.

A potential application of this checklist would be identify the best and worst data warehouses in your company. This type of “health check” could be useful in prioritising future investment e.g. it may highlight that some systems are in urgent need of overhaul or replacement. If you work in an IT department then going to business users with this kind of health check could be seen as being very pro-active and enhance the IT department’s credibility with the business. If you are a systems integrator then creating a process for measuring the health of a data warehouse along these lines could be a useful tool that could be sold as a consulting engagement for clients.

Rick Sherman writes an interesting article about how data warehousing, despite being quite venerable in IT terms, is still poorly understood. He makes a good point, discussing various typical implementation approaches and how thee fail to get to the “single version of the truth” dream. Let’s consider for a moment a few architectural choices:

(a) direct access (EII)
(b) data marts only - no pesky warehouse
(c) a single warehouse for the enterprise
(d) a federation of linked warehouses.

The first approach is limited to only a small subset of the reporting needs, and is insufficient to meet most enterprise reporting requirements. To have only single subject data marts was still surprisingly commonly advocated as late as the mid 1990s (born mainly out of the frustration of lengthy or failed data warehouse projects) yet pretty clearly is not going to scale for a company of any size. The sheer number of combinations of data sources required to build the marts means that the problem of resolving inconsistency is being done every time a mart is built, rather than being dealt with in the warehouse, so each mart either becomes a major project in itself, or (more likely) people just give up and go with some data source without getting a complete or even accurate picture.

The single giant warehouse certainly has a lot of appeal, as it resolves the semantic differences of source systems just once, allowing dependent data mars to be deployed easily. The trouble is one of practicality: for a large corporation the sheer scale of the task is scary. Large enterprises have hundreds (and usually thousands if they are counting properly) of applications where data is being captured, and these applications are often duplicated by country or major business lines. Hence the sheer scale of getting hold of all these sources and bring them into line is going to be a massive challenge. In the cases of certain industries (retail, Telco, retail banking) the scale of the data itself is also daunting, bring major technical challenges.

Hence for any large corporation it seems to me that a federated warehouse approach is what you will end up with, whether you like it or not. Few companies will have the energy or resources to deliver the single giant warehouse, and even those few that do will, in reality, have a series of skunk works data marts/warehouses dotted around the corporation since such a behemoth warehouse will be a bottleneck, hard to change and inevitably slow to respond to rapidly changing business needs.

The most pragmatic approach would seem to me to acknowledge this reality and architect for a federated approach, rather than staying in denial. It is practical to build a warehouse for either a country-level subsidiary (or groups of countries) or each business line, let that deal with the needs of that particular country or business line, and then link these together to a global warehouse which deals at the summary level. The global warehouse does not need to store every transaction in the enterprise; at that level you need to know what the sales were in Germany yesterday by product, channel and perhaps customer, but not that a particular customer bought a specific item at 14:25 at a store in Rhine-Westphalia. The detailed information like this is the domain of the country-level warehouse. Because the transaction detail is not needed at the enterprise level, you avoid the problems of technical scale that may otherwise occur, and only deal with the data that makes sense to look at across the enterprise as a whole.

Data warehouse technology is rarely associated with the glamorous world of mergers and acquisitions, usually the domain of sharp-suited investment bankers, late night board meetings, top lawyers and sometimes bizarre behaviour (see Barbarians at the Gate). But once the deal is signed, the party is over and the bankers and lawyers get their modest fees, what happens next? You can be sure that there is a hard-pressed person, possibly the CFO, who is put in charge of delivering all the vast synergy benefits that were promised by the chief executives to their shareholders. Do not envy this person. According to Deloitte: “Between 50-70% of mergers fail to deliver shareholder value after the deal.” Moreover, to emphasize just how important quick results are, according to Accenture: “For an acquirer expecting to reap $500 million in yearly cost savings from an M&A transaction, a one-month delay reduces the net present value of the deal by more than $150 million (assuming a 10 percent cost of capital). A seven-month delay costs nearly $1 billion in lost value, or approximately $3.5 million per day.”

Given this type of background, it is perhaps understandable that the first thought from the woefully underpaid consultants from the big systems integrators is not “let’s build a data warehouse then”. Yet understanding the cross-enterprise picture is immediately critical realizing benefits. For example, when HBOS merged, one of the key areas for quick savings was identified as procurement. Yet to just pick one of the existing procurement systems, switch off the other and convert all the data from one to the other was estimated at taking well over a year. Instead, what they did was to implement a packaged data warehouse, map the two sets of data from each bank, and in this way get a single view of the procurement spend across torganizationion without having to convert all the data in the underlying systems. This was achieved in just three months, giving an immediate view of post-merger procurement that allowed huge business savings to achieved. For more on this award-winning project click here.

In this way HBOS cleverly avoided a common trap, neatly summed up by McKinsey: “To succeed, a merger requires the smooth integration of IT systems and services, but the task often plunges the CFO responsible for ensuring the savings into uncharted territory. Confronted by an immediate technical challenge, companies typically choose one of two questionable routes. Some, fearing costs and complexity, never fully integrate their acquisition’s systems and thus gain few synergies. Others focus on the promise of synergy gains and improved performance but, in their haste, simply choose one system over another, often alienating both customers and employees.”

Other companies that have successfully used a data warehouse in this fashion are Shell, Intelsat, Unilever and Cadbury Schweppes. What is critical in such cases is the need for the warehouse to be able rapidly deployed, so that the business can see quick results. For those toiling away on an unrewarding data warehouse project, remember that next time your company buys another, a data warehouse could be a key part of the solution. Just ask HBOS.

There is a thoughtful article by Mike Garrett which has the original notion that the amount of complexity in an information system is constant, and that you can only move around the complexity e.g. by putting more effort into the back-end system to shield some complexity from the end user. His article is discussing SAP BW but I like the idea. It is certainly true that in a truly bespoke reporting system everything is shielded from the user, but that requires huge IT resources, while just plonking a complex reporting tool on the user’s desktops and hoping they will make sense of the data reduces demand on IT but is pretty much doomed because must users won’t be able to navigate the complexity of the database (especially for something as complex as SAP, with 32,000 tables and counting).

However I do believe that a reasonably happy medium can be found if a layer is presented to the end user that uses their own terminology and hides the physical database implementation. This, after all, was what made Business Objects so successful with its “semantic layer”. However this certainly passed some complexity back to the IT department, who then had to spend significant effort in building and maintaining the Business Objects “universes”. If you go one step further and have a data warehouse that is driven from a business model, then this itself can generate a meaningful environment (such as a Business Objects universe) from the warehouse. This is what Kalido does, for example. Again, it is fair to say that the complexity has not entirely disappeared, but now some effort is needed to build the business model in the data warehouse. However where I think the article’s neat idea falls down is to assume that the magnitude of the complexity is always the same. It seems clear to me that there is more effort in customizing every report to an individual user than there is in delivering an environment like a Business Objects universe (or even a carefully built Excel pivot table) which gives the end user a fair degree of freedom of formatting a and exploration. There us still less effort involved if you push the effort back one layer into the warehouse, since the warehouse can then generate multiple Business Objects universes, and not require each one to be customized by hand. Hence the further back in the stack you start the business modeling, the more ripple-through benefits you get by having less separate things to customize by hand. The end user still gets a flexible environment in terms that are meaningful, but there is less effort in total in delivering this environment. Further benefit would occur if all the operational systems were business-model driven, but this is just a pipe-dream today.

Complexity is one thing that should not be conserved if at all possible. Driving things from a business-model as far back in the stack as practical won’t make complexity extinct, but may at least make it a little endangered.

There is a thoughtful article in McKinsey quarterly on managing supply chains. It highlights the problem that even if you have perfectly consistent and accessible information in your company, in many situations e.g. with mobile phone, there is a web of separate companies between the designer and the customer e.g.

components supplier -> distributor -> ODM -> OEM -> distributor -> customer

Each of these is dependent to some extent on the other, and so if you want to know how your sales are going or how is product quality, you will want to interact with information from other companies further back in the chain. This presents the problem that the systems in other companies will not use the same terminology and coding structures as yours, meaning that you will need to resolve these differences in some way e.g. through a data warehouse project. The article points out that in many cases companies have not built these links and so have no visibility up and down the supply chain. This information is not just nice to have:

“Bridging these gaps pays off. In one case, a leading enterprise-computing company started gathering better data from field services, which gave it information on the incidence of failures and their costs. By feeding that data to design teams, the company developed products that could be serviced and repaired more easily. The result: total costs over the product life cycle fell by 10 to 20 percent.”

Clearly such savings are worth having. The article is an excellent illustration that the issues of dealing with multiple semantics are not confined to internal systems, and indeed in such cases standardization is literally unattainable. Instead software solutions are required that can map multiple business structures together and make sense of them. Companies that invest in such data warehouse solutions are, as this article shows, getting very tangible results.