How to calculate ROI for optimizing an existing BI environment? (Part-1)

Posted by Mohammed Rafi
November 2nd, 2009

Once it comes to measure the ROI for a new BI solution, much has been written about and a wealth of information is available all over the place. Whilst deriving ROI for a new BI solution poses a set of challenges, deriving ROI for optimizing an existing BI solution is a different ball game altogether.

This blog post is about sharing my experience on deriving ROI through an optimization exercise of an ‘existing’ BI system. Since this kind of initiative is predominantly driven by IT, the flavors of business benefit parameters (top-line parameters such as Improved Sales, Working Capital, Business Productivity, Time to market costs, customer retention, etc and bottom-line parameter such as Reduced Inventory, Marketing, Operational costs, etc ) are not the drivers. The key drivers in this case are Number of CPU saved, Storage optimized, Productivity improved, SLA with Business, Reduced Infrastructure management services costs, etc.

What are the different phases for a structured optimization study?

Prepare:- Preparatory workshop with IT and Business sponsors before conducting the Audit:Without an agreement across the board on the parameters and how to derive the financial equivalent, an audit exercise fails because of the non-agreement of assumptions and calculation methods which only result in unrealistic ROI. At the end of such a workshop, ROI calculation methods should be agreed and the current financial equivalent of metrics needs to be derived, some of them can be: Dollar spent Per CPU, Dollar spent Per GB or TB, Dollar spent per FTE, Dollar spent on License per CPU/User. Only when you have base lined the current readings on the metrics this can be compared against the actual benefits that you get out of an optimization exercise. Since ROI is a derivative directly associated with money, this derivation of dollar equivalents on CPU, License, Storage and FTE are critical success factors for a successful optimization exercise and ROI derivation. On the cost side of the story, this workshop should baseline the costs associated with implementing the recommendations.

Audit:- Study BI environment:

At this moment, I’m not going to focus on how to conduct an Audit on a BI environment which is totally a different topic and will be writing on this shortly. Our company has developed a structured way of auditing a BI environment through a 10 – Point framework, which helps in covering all aspects of a BI solution. I’ll write more on this in subsequent blogs.

Recommend:- Consolidate optimization steps and prove:

A typical audit exercise, results in a health report and recommendations on ‘touch points’ such as Data Integration layer, Information Delivery layer and the Data warehouse layer. Recommendations can be situational and for now will just list down a sample out of my own experience:

Data warehouse layer:
Long running query analysis and recommend performance optimization; Dormant table analysis and optimize storage; Indexing and Statistics optimization; DBMS features; Data modeling optimization.

Data Integration Layer:
Identify ETL Job discrepancies by analyzing Job dependencies and duration information to reduce ETL batch window to meet business information delivery at SLA levels; ETL tool fitment analysis and Enterprise ETL tool standardization and optimization.

Information Delivery layer:
Consolidate reporting by identifying duplicate reports, scheduling (push report) optimization, multi-lingual reporting optimization, Enterprise Reporting tool standardization, Reporting tool upgrade path recommendations and benefits.

A testing strategy for proving that positive ROI can be realized out of the recommendations should be discussed and agreed upon during the prepare phase.

Derive and Submit ROI:-

In my experience, the major factors used for deriving the ROI of an existing BI solution are CPU, Storage, License and Productivity gains. I’ll be writing in detail on this section as part of my next blog posts.

Other success factors:-

Divide and Rule:
As part of this exercise, we have divided the BI Audit exercise into many streams and each stream can address one or all of the above factors. The ROI results are consolidated across streams and a business case could be produced by consolidating all the streams.

Say “Yes” to collaboration:
Another important success criterion for this kind of an exercise is the active involvement of the IT team of the organization with the vendor/consultancy during the entire duration of the exercise. Consultants should not be allowed to act in isolation because the business case after all has to be owned by the IT stakeholders of the organization to produce an agreeable and realistic business case.

My company also has developed an ROI calculator (FABIA- Acronym for “Financial assessment tool for Business Intelligence & Analytics”) which gets inputs on costs and benefits (top and bottom line) and gives the ROI output based on a discount rate, term and NPV. Simple as it may sound, the challenge is in deriving  the necessary input values to feed in the calculator and the assumptions and values will have to go multiple rounds of negotiation for an overall agreement, isn’t’ it?

Thanks for reading and please do share your views.

Comments (0)