Page 61 - DCAP606_BUSINESS_INTELLIGENCE

Business Intelligence

Notes
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.
Question:

Analyse the case and provide an alternative solution.
Source: http://www.winmetrics.com/olap_casestudies.html
4.8 Summary

OLAP is a database expertise that has been optimized for querying and describing, rather
than of processing transactions.

OLAP user’s queries are neither predictable nor repairable and the results of one query
often frame the obligations of the next.

56 LOVELY PROFESSIONAL UNIVERSITY