WP_Data Management Best Practices

W H I T E P A P E R

www.persistent.com

Because of these significant advantages, some vertical markets such as finance have developed advanced solutions

based on OLAP technology. On the other hand, the list of disadvantages includes:

—

Although Microsoft’s MDX is the closest standard access language, it is not widely implemented. Thus, OLAP is

non-standard, non-portable technology. Also, development expertise is fragmented by vendor; there is much

less expertise in the field as compared with SQL.

—

OLAP is sensitive with respect to some update types (e.g., to type 1 SCD change, see

—

For reasons explained in

OLAP cube technology does not generally replace relational technology in a

warehouse. Thus, adopting OLAP cube technologies may add to the total cost of the solution, especially if the

relational and OLAP technologies are bought fromdifferent vendors.

At one end of the spectrum, if you have very advanced analytics needs and a need for predictable, high performance

complex queries, and/or if you are in an industry where you can purchase off-the-shelf solutions that work best on

OLAP, and you are confident that you can hire expert developers, consider buying an OLAP server. At the other end, if

you want to establish commonality across your DW/BI projects around standard database languages without being

tied to any vendor, then consider a dimensional relational approach. In between these two extremes, you must take a

closer look to the relative pros and cons of each solution for your case.

4.4.1.9Aggregate Navigation

Aggregate navigation is the part of a query rewriting service that recognizes that a query can be satisfied by an

available aggregate table or cube rather than summing up detail records on the fly. The aggregate navigators receive a

user query based on the atomic level dimensional model metadata and shields the user application from needing to

know if an aggregate is present (just as with DBMS indexes). At the implementation level, there are a range of

technologies to provide aggregate navigator functionality, including: OLAP engines, relational materialized views, or

BI tools.

Kimball (see

page 354) argues that it is preferable to use aggregate navigation from the presentation layer

supporting systems (either OLAP or relational engines), rather than using one from the BI tool, the reason being that in

the latter case, the benefit would be restricted to a single front-end BI tool. In our experience, there is a case however

to bemade tomove aggregate navigation up the chain in the following cases:

a. When aggregate tables are implemented in straight relational DBMS tables, a quite common case, rather than

inmaterialized views or ROLAP systems that understand dimensional semantics on top of relational.

b. When all BI tools available implement this functionality (which is becomingmainstream).

c. In the case of mixed OLAP – relational implementations, where some aggregates are built in relational

implementations and others in OLAP, and the BI tool can recognize which aggregate to use based on the

incoming query –this needs, however, a sophisticated BI semantic layer that abstracts over relational and

OLAPmodels.