July 17, 2002 - FlashPoint: MOLAP and DOLAP: Apples and Oranges
By
Cindi Howson
FLASHPOINT---July
17, 2002
Copyright 2002. The Data Warehousing Institute. All rights reserved.
When
selecting business intelligence tools, companies increasingly put desktop OLAP
(DOLAP) tools on the same list as multidimensional OLAP (MOLAP) tools. This is really comparing apples with
oranges. Worse, companies that have
both types of tools remain unclear about when to use which tool, increasing
implementation costs and failing to meet important business information needs.
DOLAP tools
dynamically create a micro cube on either the client PC or, in a web
deployment, on a midtier application server.
To create the micro cube, users launch a SQL query using a business view
of the data warehouse. The DOLAP tool then sends results back to the desktop
and formats it into a cube. The micro cube format allows users to perform
certain functions such as drill-down that historically were only available with
MOLAP cubes. It also allows users to
issue complex SQL statements, combining data from different fact tables or even
spreadsheets, yet presenting the users with one seamless document. The main benefit of this approach over
MOLAP is flexibility; users do not have to define their information
requirements ahead of time in a precise way as with MOLAP. Also, a relational
database can store more data than MOLAP cubes, giving users freedom to analyze
larger data sets. The downside is slower response times and limited
multidimensional calculations. Examples
of DOLAP tools are Business Objects and Brio.
Let’s call these the apples.
Meanwhile,
MOLAP tools are databases pre-populated with information from different data
sources. The cube is essentially
static and updated when IT runs another extract from the source systems. The
benefit of this approach over DOLAP is predictable, fast response times and
robust multidimensional calculations.
The downside is that users must pre-define their requirements (usually
to an IT professional who builds the cubes) without the cube getting too
big. Hyperion Essbase, Microsoft
Analysis Services, and Cognos Powerplay are the leading MOLAP tools. Let’s call
these the oranges.
(Note: MS
Analysis Services can store data in relational tables (ROLAP) but MOLAP storage
is more common and provides the fast access characteristic of MOLAP tools. Some analysts would classify Powerplay as a
desktop OLAP (DOLAP) tool since it grew from the desktop to the server and
because it does not allow write access. However, since the cubes must be
pre-built and are server based, I classify it as MOLAP.)
So, both
types of tools use OLAP cubes to analyze business questions, but they do it in
very different ways. To understand
this, let’s look at five different types of business questions:
1. Time Period Comparisons: Let’s
assume that both tools will use the same data warehouse and star schemas. The fact table consists of monthly sales
data. Product,Time and Amount are columns in the fact table:
U.S. Fruit
Shipments[i]:
|
Apples |
FY2000 |
50249 |
|
Apples |
FY2001 |
50191 |
|
Oranges |
FY2001 |
8828 |
|
Oranges |
FY2001 |
9681 |
Users want
to analyze 2001 sales versus the previous year’s. In a DOLAP tool, such time
period analysis is not built in, although there are a number of ways to
accomplish it. Most approaches involve
putting the intelligence in the report.
Both Brio and Business Objects support cross tab reports. Business Objects recently added a new
feature that allows users to calculate variances between two columns with the
click of a button. In Brio, users can create a formula that may take a few more
clicks but is still fairly easy.
However, if
users want year-to-date versus same period last year comparisons, the procedure
in both DOLAP tools is much more complex than in MOLAP. Only the most technically savvy users who
know how to use date functions and if-then-else statements can actually do
this.
Alternatively,
the universe/dictionary developer can build intelligence into the business view
to define a column called “Current Year Sales”. The SQL here is quite complex and can affect query
performance. Lastly, if this is a
common reporting requirement, the data warehouse team may create a separate
star schema that builds the time period intelligence into the columns of
data. The DBA would create separate
columns for current month sales, year to date sales, last year sales, etc.:
|
|
FY2000 |
FY2001 |
Current Month |
YTD |
Last Year YTD |
|
Apples |
50249 |
50191 |
|
|
|
|
Oranges |
8828 |
9681 |
|
|
|
This
structure makes it easier for variances to be stored in the fact table or
easily added to the report; however, it’s a different star design.
In MOLAP,
these types of time period analysis are standard and built into the database
structure. “Scenario” is typically a
dimension that characterizes data as current year, last year, budget. “Time” is another dimension with the
individual months and quarters. This
type of structure makes time period comparisons a straight-forward ratio
calculation for either users or administrators to build-in. MOLAP tools take
the analytics one step further enabling period-to-date comparisons with
built-in options that require no complex programming.
2. Instance
in Time/Balance Sheets: In
analyzing things like inventory and account balances, users will often want to
total the changes made in a month (a period of time) and compare that to the
end of month balance (an instance in time).
In desktop OLAP tools, users really need to create two separate queries,
and manually join the results together in the report. Users who do not understand the finer details of SQL and how the
data is structured may inadvertently sum inventory or balances across time
(which is wrong) while simultaneously summing material movements or debits/credits
across time (which is correct). MOLAP tools provide special functions that can
take one value from a given period.
3. Market
Share Calculations: SQL allows only same-row calculations and cannot do
forward calculations such as % share that first needs total sales:
|
|
FY2001 |
Share |
|
Apples |
50191 |
31.72% |
|
Oranges |
9681 |
6.12% |
|
Bananas |
84271 |
53.26% |
|
Pears |
14091 |
8.91% |
|
TOTAL |
158,234 |
100% |
In DOLAP
tools, the percentage is once again calculated in the report. Because these calculations are done in the
report, the total line must be brought back to the client. In a MOLAP tool, the
calculation is done on the server, so one could select the top 5 selling
products (out of millions), without bringing the detail back to the
client.
4. Ratios
and averages also require careful consideration. For example, if I want to know to know the
average price for a group of fruit products, I should take the amount/lbs or
119499.54/158234.
|
|
FY2001 |
Price |
Amount |
|
Apples |
50191 |
$1.29 |
$64,746.39 |
|
Oranges |
9681 |
$0.98 |
$9,487.38 |
|
Bananas |
84271 |
$0.39 |
$32,865.69 |
|
Pears |
14091 |
$0.88 |
$12,400.08 |
|
|
158234 |
$.76 |
$119,499.54 |
Desktop
OLAP tools do not calculate weighted averages; they will only do an average
from each unit price which would give .89 rather than the $.76 we are looking
for. To get a weighted average, one would need to create a new calculation
(amount/quantity) in the report for each break level (i.e., Total Region
Amount/Total Region Quantity, Total
Quarter Amount/Total Quarter Quantity, etc.). In a MOLAP tool, ratios and
averages are calculated at the server for all levels in a hierarchy. Administrators have control over the order
in which the calculations are done.
5. Attributes: At this stage, you
may be thinking that MOLAP is the only way to go (but don’t underestimate the
data volume and flexibility issue!).
However, there is a key business requirement in which DOLAP tools have
long been superior: attribute analysis. Because DOLAP tools were initially
based on relational databases, they can handle whatever data is stored in the
data warehouse. If a user wants to
know sales revenue by a product and all its attributes (color, size, density),
the DOLAP tool can easily present this information. Users can form questions around the attributes themselves (“Show
me a list of fruits we sell by bag versus crate”) as well as the measures (“do
we sell more by bag versus crate?”)
MOLAP tools
are less adept at attributes. Some
treat them as separate dimensions, losing the association of the attribute with
a particular product. So one can still
answer “do we sell more by bag versus crate” but it is much harder to arrive at
a conclusion like “we sell more Braeburn apples than Macintosh because we sell
them by bag.” As well, it is impossible to get lists of attribute details; the
information is there solely for analysis purposes. Finally, attribute calculations are generally not pre-calculated
so attribute analysis can be quite slow.
CONCLUSION
MOLAP and
DOLAP tools are very different, and therefore, most large companies need both
tools. The DOLAP vendors increasingly
recognize that MOLAP tools are another data source, not direct
competitors. In the quest to select one
tool, customers mistakenly put MOLAP and DOLAP tools on the same lists and
vendors respond by making their demos appear strikingly similar. However, there are significant differences
in their underlying architecture that affect how the same business questions
are answered. The key to success is in
understanding these differences so that the businesses’ needs are met with the
least user effort. You need to know if
they want apple pie or orange cake.
Cindi Howson is an independent consultant and has helped
clients select, implement and optimize OLAP tools since 1994. She can be reached at cindihowson@askcindi.com