Business Intelligence OLAP Case Studies

How a Financial Services Company Developed a Performance Report for Clients, Saved $200K and Sold $167,000,000 of Equity in Just 9 Months

This story is based on our success at ABC, Inc., the leading outsource collection agency for government debts in the US. We are using the pseudonym "ABC" to protect their confidentiality. Please allow us to recount how we applied OLAP technology to develop a flexible performance report for ABC clients, saved $200K in accounting software expenses and helped ABC sell some equity for $167,000,000 (see press release) in just 9 months.

The OLAP report tracks the key measure of performance Like any other financial services company, ABC must provide regular reports that measure its performance to its clients. ABC measures its performance with what they call their CARE report. The recovery percentages that appear in the CARE report are the primary measure of performance for ABC clients. ABC first deployed their CARE report via a 40-page C program. But, the CARE report for all clients was taking over 24 hours to process and the resulting 500-page report was inflexible. There was no way to quickly focus in on a single client or client contract and there was no way to change the level of detail. There was also no way to further analyze the results, e.g. by loan type, so they could discern what portions of their business are most lucrative. And there was no convenient way to validate or understand a sum by examining the detail records that it represents. What they needed was CARE information delivered in the form of an Excel pivot table.

Merrill Eastman, ex-CEO of Bestfoods and then acting CEO of ABC, suggested that we give Online Analytical Processing (OLAP) a try. Our first assignment was to transform the old CARE report into an OLAP cube. OLAP looked like the answer because it pre-computes numeric aggregations for the cross-product of all relevant dimensions so that summary information for any combination of dimensions can be displayed on demand. If you are familiar with Excel, it suffices to say that OLAP transforms a relational database into a pivot table.

There are a number of OLAP software alternatives out there, but we quickly settled on SQL Server Analysis Services because:

1.	ABC already owned Microsoft SQL Server licenses and appreciated its ease of use and administration.
2.	Microsoft has bundled Analysis Services with every copy of SQL Server since 1998. So, ABC didn't have to buy anything to give it a try.
3.	SQL Server Analysis Services became the OLAP market leader in 2003 (more).
4.	SQL Server OLAP Services is tightly coupled with MS Excel. Like most other companies, ABC uses Excel exclusively for all financial reports and analysis.

Developing the OLAP CARE report proceeded slowly at first because it was difficult to reach concensus on CARE Report specifications. Analysis Services is easy to use, but it was still very difficult to figure out how to get the content of the old CARE report out of a OLAP cube. The major challenges we learned to overcome included:

	How to export 80M facts and dimension rows from Informix to SQL Server in less than 4 hours?
	How to transform exported information into a SQL Server data mart with no referential integrity errors?
	How to compute distinct counts within the cube that have a different granularity than the basic revenue facts?
	How to map the same facts to multiple members within the same dimension?
	What ragged hierarchies should be used as dimensions of the cube?
	How to support drillthrough to facts so that cube aggregates can be validated and understood?
	How to tie CARE cube aggregates to the General Ledger so that data integrity could be validated?

It took about 8 weeks to deliver the first CARE cube. A few weeks later, we delivered a sister cube that provided more comprehensive recovery analysis. By then, ABC was a believer in SQL Server OLAP Services and the rush was on to expand its use. We trained three ABC software engineers to build cubes and they set about developing General Ledger, General Ledger Budget, Payroll, Collector Performance and Revenue Forecasting cubes in parallel.

The General Ledger cubes delivered immediate benefits. ABC was using OSAS accounting software. They were not satisfied with the reports that OSAS produced, but was reluctant to invest an estimated $200K to acquire a new package and train accounting personnel to use it. Instead, they purchased an ODBC driver to export OSAS data and we built a cube to generate their reports. Today, their Balance Sheets and Profit and Loss Statements are implemented in an account rollup dimension. They can drill down from a few lines at the top to any level of detail. The drill-down feature is particularly useful in the GL Budget cube. If budget variances are detected at the highest levels, they just double-click on their OLAP pivot table to drill down until they discover the roots of the variance. The OLAP accounting reports reduced the time required to close ABC's books by 5 days. As a result, they can make critical business decisions that much faster.

Meanwhile, ABC's impressive performance attracted outside investors. A venture capital firm became the primary suitor and a team of business analysts set out to understand ABC's business. After exhaustive due diligence, the VCs decided to invest $167,000,000. They did so because ABC has a rock solid business. But, the deal might not have happened without the OLAP cubes. The OLAP cubes answered due diligence questions more quickly and in much more detail than the VC had seen in previous deals. The Billing cube that we developed at the VC's request was fundamental to their belief that future revenues would grow fast enough to support the necessary ROI.