Business Intelligence Best Practices -

Collaboration. Communication. Community.

 E-mail to friend
The Business Intelligence ROI Challenge: Putting It All Together

by Bill Whittemore
Getting approval for your business intelligence (BI) or data warehouse project requires being able to demonstrate business value to your decision makers. Among the most difficult tasks is providing a predictable ROI to senior management.

Calculating the ROI for data warehouse and business intelligence projects is a very complicated and perplexing task. Today’s decision makers can no longer accept being unable to calculate the ROI for data warehousing and business intelligence projects. This article provides a practical framework and process for calculating ROI for data warehouse and business intelligence projects.

In the early 1990s, mantras such as build it and they will come or building a data warehouse is a good thing to do justified many data warehouses. Soft statements such as essential to the business to remain competitive, improved access to data, and yields many intangible benefits are insufficient for most of today’s upper-level decision makers. As a result, data warehouse and business intelligence projects almost always require business justification through ROI analysis. Companies now want to know the business case, the value proposition, and the potential ROI of the data warehouse project before funds are committed. Projects that are funded deliver measurable, positive impact to the financial bottom line in either revenue generation or cost savings.

There is hope for project managers, executive project sponsors, and CIOs facing the task of assessing the benefits of data warehousing projects. One of the largest studies of data warehouse ROI, conducted by International Data Corp. in 1996, touts an average 401 percent, three-year return on investment for 62 organizations with data warehouses.

However, many companies have no uniform, demonstrated ROI processes. A TDWI study of 1,600 companies released in March 2001 showed only 13 percent of respondents tracking data warehousing ROI across the value chain. Thirty-seven percent said they plan to begin tracking ROI and 27 percent said they are not tracking returns and have no plans to do so, underscoring that companies continue to struggle with the ROI issue.

Calculating the ROI for a data warehouse is difficult because of the many intangible or qualitative benefits provided. Furthermore, the data warehouse is normally not directly linked with action. For example, the data warehouse may point out an area where sales can be increased through cross selling a certain product but a sales representative still has to make the sales call and close the sale to add to revenue.

In reality, despite what the tool vendors tell you, data warehousing and business intelligence don’t provide a direct, quantifiable financial return as an interest-paying bond or CD does. The return on data warehouse investment can be derived from improved decision making and productivity as a result of having better information sooner, as well as the new or modified processes enabled by the data warehouse. Business intelligence leads to other new or modified processes that result in reduced costs and higher revenues. It is still important to understand and quantify the costs and benefits necessary to make an informed decision. All data warehouse and business intelligence projects should have ROI justification prior to funding and each project or phase should be able to stand on the merits of the project ROI. Business intelligence efforts often can, and should, be justified through a comparison of estimated ROI to other uses for the funds.

ROI is calculated to provide an initial business value analysis assessment that quantifies the financial rewards of a business intelligence project, allowing managers to evaluate and prioritize various IT initiatives within their organization. As part of the post project review, recalculate the ROI and compare it to the original ROI figure. Following is a step-by-step approach for calculating the ROI for your data warehouse or business intelligence project.

Step 1: Define the Business Problem and Identify the Business Goals

Define and document the business problem and business objective, and identify the business goals needed to solve the problem or meet the business objective.

Begin by ensuring you have outlined the current situation, including identifying the key business problems or “pain” the company is experiencing, along with the overall need for the project. This involves confirming what is already known by reviewing existing documentation and through interviews and discussions with affected executives and managers.

All projects should have written business objectives tied to the organizational and possibly the sub-organizational (i.e., departmental) mission, strategy, and business plan. Business objectives are the desired results of business operations. Business objectives are typically more strategic in nature and could relate to areas such as growth, corporate net income, and market share. Business objectives can be broad and do not have to be measurable.

Business goals are tactical in nature and need to be defined for the overall initiative and for the incremental project phases. Business goals should be specific, measurable, achievable, realistic, and time related (SMART). An example of a business goal is “improving customer retention by 3 percent by March 2003.”

Step 2: Gather the Business Requirements

When gathering the business requirements, it is important to understand the current business processes, organization culture, and how decisions are made. For example, when defining sales analytics, it may be necessary to understand the bookings, billings, and backlog operational processes in order to define the analytical requirements for these processes. Understanding the organization’s culture, including how technology is used and how decisions are made, can have a major impact on how business intelligence and analytics will be used. Some organizations will require the ability to drill down to the lowest level of detail on a regular basis in order to make decisions while others will prefer summarized information.

Relate the requirements to the business objectives they support. For example, if the business objective is to improve cross sales through providing sales information by customer, requirements related to customer churn do not apply. Gather the performance measures that will gauge the involved processes to determine success or failure. For instance, order count and the duration time for order fulfillment are key performance measures for the fulfillment process.

Obtain both informational (data) and analytical requirements (e.g., performance measures and reports). Informational requirements relate to the business dimensions and the associated attributes needed to describe the performance measures. Focus on identifying the key business requirements that yield positive ROI. Discuss how having access to the performance measures will influence process changes. Business intelligence is a catalyst for process improvement through providing a better understanding of where the processes are not meeting expectations or goals and where they need to be improved or reengineered. Examine the extent to which completing the project on budget and on time will result in additional revenues or cost savings and when that incremental revenue or cost savings can be realized based upon the estimated project completion date. This directly relates to the overall ROI of the project.

Conduct facilitated joint application development (JAD) sessions to obtain requirements consensus with all affected stakeholders and to define business requirement priorities, including in-scope and out-of-scope requirements.

Step 3: Build the Project Charter and Blueprint

In addition to gathering business requirements, the overall project charter and blueprint needs to be defined. Include the following:

  • Project charter (define the business objectives, data warehouse mission and vision, business subject area(s) included, project objectives and goals, overall project scope, critical success factors, and key stakeholders)
  • Technical requirements and architectural constraints, including tools
  • Logical or conceptual architecture
  • The infrastructure (development, system test, production, and training environments)
  • High-level logical data model
  • Data source inventory and profiling
  • The estimating drivers (such as number of analytics, source tables, target entities, interviews, etc.)
  • Project approach and deliverables
  • Project plan, including effort, timeline, and investment

This up-front effort is essential to accurately define and estimate the project’s total cost of ownership and can be completed over a short time frame (several weeks) with experienced data warehouse and business intelligence resources. If this effort is excluded, the outcome will be a vague project definition and a ballpark estimate that often leads to non-approval or missed expectations.

Developing an accurate estimate is key to accurately estimating the initial investment. The estimating technique recommended is a bottom-up approach that quantifies the key estimating drivers (number of analytics, etc.) of the project based upon the business requirements and the proposed architecture. This information is incorporated into the project plan and used to derive the overall effort and time estimate. One of the most difficult areas to estimate in a data warehouse project is the overall extract, transform, and load (ETL) activities. Factor in time for the build and test of both the initial and incremental data loads and plan for multiple mock data loads to test the back end.

Step 4: Identify and Quantify the Benefits (Tangible, Strategic, and Intangible)

Identify the organization’s anticipated benefits from the data warehousing solution and the associated in-scope business requirements. The link between the benefit and how much business intelligence will contribute to each benefit may in many cases be subjective. In certain situations, past empirical evidence may support the business intelligence link to the benefit. Benefits fall into three major categories: tangible, strategic, and intangible. All three benefits should be provided in the ROI analysis.

Tangible benefits are specific, quantifiable, and verifiable. For example, if the focus is to increase customer acquisition through personalized marketing, how many new customers can be expected over a certain time period and what is the additional revenue that will result from the new customers? Strategic benefits typically focus on the broader, long-term corporate strategy and objectives and are more difficult to quantify. Intangible (qualitative) benefits are not reasonable to measure.

Executives and managers who set out to calculate warehousing benefits should start with the low-hanging fruit by using a straightforward justification based on the technology itself—examples are hours saved in report development, report image storage, report printing, and report distribution. These technology justifications can yield substantial ROI cost savings when there are thousands of reports involved.

Following technology justification, the business must be examined closely to determine the ROI that will result from process improvement coupled with better decisions that can be made through the information provided by the new business intelligence. Determine how the users will employ the new information provided by the data warehouse in their day-to-day jobs to positively impact the bottom line. This involves interviewing all classes of users, including executives, middle managers, analysts, and key end users.

When defining quantitative or tangible benefits, one approach that can be used is to associate the business requirement to the processes that the proposed business intelligence will analyze, such as inventory placement and control, to help identify tangible benefits. The business benefits and numbers must be generated by the business, often with IT or the data warehouse consultant playing the role of catalyst and analyst.

Arriving at consensus estimates of the benefit across all affected stakeholders is the most difficult challenge for business intelligence ROI. The biggest problem in getting consensus estimates for ROI is related to concern over potential accountabilities associated with quantitative measures of performance. This can be mitigated by being conservative regarding setting the performance target for reduction in cost or additional revenue. For example, if there is a strong probability that sales will be increased by 10 percent due to the new business intelligence, set a performance target of 5 percent.

Tangible benefit examples have been provided in Table 1 for both revenue generation and cost savings provided by business intelligence based upon real-world experience.

Table 1
Examples of Tangible Benefits
Revenue Generation Cost Savings

· Improve customer acquisition and conversions through customer profiling and segmentation
· Reduce customer churn, increase customer loyalty, and increase sales using multiple performance measures, including customer lifetime value
· Increase revenue by increasing sales (i.e., “How many more clothes, policies, accounts, and airline seats can we sell as a result of the business intelligence?”)
· Increase profit from the propensity of existing customers to purchase more products and services due to the new processes initiated by the data warehouse
· Avoid sales lost to competitors
· Improve profitability with access to granular, profitability information across key business entities such as account, customer, product, business unit, etc.
· Subscription or data brokerage services such as providing customer demographics
· Increase market share
· Reduce time to market for new products and services
· Improve the quality of data collection activities, including Customer, Human Resources, Marketing, Finance, and Operations
· Increase pricing with improved access to revenue and cost information needed to combat challenges from customers
· Enable traditional list execution and management
· Market basket analysis and product affinity analysis
· Lay the foundation to be able to adapt to changes in business strategies
· Provide self service to employees, partners, suppliers, and customers
· Measure campaign effectiveness quickly and be able to make adjustments throughout the marketing campaign lifecycle
· Optimize marketing contacts
· Identify profitable customers in unprofitable segments
· Look at competition when pricing
· Determine incremental revenue generated from the lower value customers
· Up-sell and high-revenue analysis will enable your organization to identify and cherry pick among the lower revenue customers that share comparable profiles with the profitable and elite customer, therefore having higher revenue generating potential
· Create new business opportunities and business models
· Negotiate better prices from suppliers, identify the top suppliers, and provide discounts for quantity purchases; provide the ability to accurately analyze vendor performance and allocate purchases accordingly; supply score card (quality, delivery, and price) [for supply chain management projects]
· Reduce or redirect staff required to carry out business processes
· Increase productivity with better information sooner
· Improve cost control
· Reduce expenses
· Eliminate inefficiencies and reduce costs of operation by providing a “single version of the truth”
· Reduce losses due to fraud detection
· Reduce write-offs by having the information needed to combat challenges from vendors and customers
· Reduce claims (insurance)
· Reduce overproduction of goods (manufacturing)
· Provide just-in-time inventory; manage finished goods inventory; improved inventory management and stocking; reduce inventory carrying costs
· Eliminate canned reports and their associated support processes
· Reduce response time for report requests
· Analyze troubles, repairs, and defects and provide information to track and correct perpetual problems
· Reduce time to gather information for regulatory and compliance reporting
· Assess the performance of equipment and provide alerts when preventive maintenance must be performed
· Provide on-time delivery (transportation)
· Track product failures from cradle to grave (manufacturers)
· Consolidate and retire older systems leading to a reduction in operating expenses
· Reduce product returns
· Analyze productivity of employees
· Track trip profitability and provide performance analysis
· Support billing disputes
· Reduce customer dialogue costs through more efficient marketing
· Improve delinquency tracking
· Know what commissions have been paid
· Reduce credit losses by looking at customer usage and provide credit rating functionality
· Minimize accounts payable through effective contract analysis and integration into the payment process
Revenue Generation and Cost Savings Benefit Examples

The next step is selecting and, if necessary, modifying a measurement technique that can accurately reveal the returns delivered by the data warehouse relative to top business needs. Because the true value of a data warehouse must reflect tangible, strategic, and intangible benefits, the traditional ROI calculation—which analyzes tangible benefits minus costs—omits the “soft” benefits that help make a compelling case for a data warehouse (Table 2).

Table 2

· The ability to analyze pricing strategies
· The ability to identify and nurture the customers with the most potential
· Improved decision-making quality through informed,
fact-based decisions
· Faster decisions
· Greater management visibility as to what is taking place
· Support for business strategies
· Value of market share that would otherwise be lost to competitors
· Value of efficiencies added by the data warehouse
Potential Strategic Benefit Examples

Provide the qualitative or intangible benefits that cannot be measured but will still be achieved by the data warehouse project (Table 3).

Table 3

· Improved customer service
· Improved customer satisfaction
· Improved access to data through query, analysis,
and reporting
· More timely information
· Improved data accuracy
· Competitive advantage
· Better control of data
· Cost savings
· Less reliance on legacy systems
· Better data integration
Some Qualitative (Intangible) Benefits a Data Warehouse Typically Offers

Identify for each benefit the following:

  • Overall timeframe in which the benefit will be received.
  • Document whether the benefit will increase or decrease over time. Data warehouse maintenance costs typically decrease over time and benefits should increase over time, yielding an ROI that grows during the evaluation period. At some point, you may assume a straight-line cash flow but this is usually at a point in the future (five or more years).

Step 5: Establish a Measurement Baseline

Calculate the tangible operational costs associated with performing existing functions and making decisions that will be influenced by the new system. Accurately measuring ROI hinges on establishing a baseline, or a snapshot of the way the organization operates without a data warehouse. A baseline serves as a comparison point so that companies can estimate the expected benefits. The baseline should include data on such criteria as time, human resources, cost, performance, results, etc. For example, how long does it currently take to locate, extract, understand, and apply data? What is the average cycle time to bring a new product or service to market? What are the demands on time and resources to develop and deploy a customer-centric application, and how do these costs impact overall productivity? Are results delivered in time to act on a competitive threat or new product release?

Step 6: Calculate Total Cost of Ownership

Calculate the total cost of ownership (TCO), including hardware, software, consulting services, internal resource costs (salaries and benefits), and other costs such as hardware and software maintenance and ongoing training.

As part of the investment calculations, include the IT resource development cost. Maintenance is typically one of the greatest costs of a system and needs to be included.

Step 7: Calculate the Return on Investment

Once the benefits are stated and quantified as much as possible, calculate the ROI using your organization standard. The standard annual ROI calculation divides the annual benefits by the total annual costs to determine the annual return on investment percentage.

The project’s overall ROI calculation calculates the net present value (NPV) of projected cash flows derived from the savings generated by the business intelligence project divided by the initial investment and the maintenance cost. NPV gauges tomorrow’s return in today’s dollars. The NPV allows you to determine the value today of $1 one or more years in the future.

For example, assuming the total first year NPV savings of a business intelligence project is $2,000,000 and the initial TCO investment and one year maintenance cost is $1,000,000. The ROI for the project would be 200 percent of the initial investment. The ROI formula is as follows:

  NPV of Savings  
X 100
  Initial Investment + Maintenance Cost  

Unfortunately, technology projects rarely recoup costs in the first year of operation so ROI calculations typically use a three-year time scale. With a three-year time scale, the ROI calculation is now the average net benefit (benefits less additional costs) per year divided by the initial cost, times 100, or:

ROI = [ (Net Year 1 + Net Year 2 + Net Year 3) / 3 / Initial Cost ] X 100

Other techniques can be used to augment ROI analysis such as cost displacement, which compares the cost of the new system against the one it will replace, and business value added, which measures technology not in dollars but in terms of its support of key strategic business goals and metrics. Even if the warehousing project clearly supports strategic business objectives, calculating the ROI is still very important and should be strongly considered.

Step 8: Determine the Payback Period

Identify the point in time at which benefit savings surpass the total costs of the project. Benefit savings can be discounted to allow for inflation.

The payback period calculation determines the number of years that are required for the discounted projected cash flows to equal the initial investment and the ongoing maintenance cost.

For example, assuming that the initial investment for a business intelligence project was $1,000,000 and the average annual NPV savings of that project was $2,000,000, then the payback period would be $1,000,000/$2,000,000 or 6 months (0.5 years).

The payback period formula is as follows:

Payback period = Initial Investment + Maintenance Cost

(NPV of Savings / Years/n)
n = The total number of years for which the NPV calculation was applied

Step 9: Measure the Investment and the Actual Benefits

Collect the investment cost (initial investment and annual maintenance cost) and benefit measurements for the business intelligence project over the payback time scale period (e.g., three years). Without this, it is not possible to determine whether the original calculations were accurate. At the end of each year, submit a report to management that provides the actual ROI calculations compared to the original ROI calculation. This will provide the ROI scorecard for management to measure how well the organization is doing relative to its ROI goals. This is an essential step for continual improvement.

Step 10: Determine How to Retain Benefits as Organization Objectives Change

Determine how to retain these benefits as organizational objectives change. According to Wayne Eckerson, director of education and research for TDWI, “For certain companies struggling to build these warehouses, the next step is how to drive the warehouse into the fabric of the company, so that it’s part of its business culture, as opposed to a technological addendum.” Business intelligence is developed for business users versus IT users. If the business does not effectively integrate the data warehouse business intelligence into its daily jobs, the business intelligence benefits will be difficult to achieve.


Calculating ROI is both a science and an art. The art involves working with the business community to determine the quantitative and qualitative benefits of the business intelligence solution and obtaining buy in and accountability for the quantifiable benefits. Given that the core of business intelligence relates to performance measurement, it stands to reason that using a quantifiable ROI approach to data warehousing and business intelligence projects offers management concrete evidence of an implementation’s success and provides a mechanism for managing and retaining its benefits throughout the data warehouse’s lifecycle. Without an ROI scorecard, it is difficult to understand the true benefits to the organization—which is what business intelligence is all about.

Bill Whittemore -

Bill Whittemore is a Principal of the Transportation Practice at Gazelle Consulting, Inc., a consultancy specializing in the design and implementation of large-scale data warehousing and business intelligence solutions. Mr. Whittemore has more than 22 years of experience, including data architecture, physical database design, and system architect responsibility on numerous large, full lifecycle, custom data warehouse projects.