Page 60 - DCAP606_BUSINESS_INTELLIGENCE

Unit 4: Understanding OLAP

Notes

Case Study ABC, Inc.

H ow 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 in just 9 months.
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 analyse 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.
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 consensus 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 an
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?

Contd....

LOVELY PROFESSIONAL UNIVERSITY 55