WP_Data Management Best Practices

29 / 96

W H I T E P A P E R
© 2017 Persistent Systems Ltd. All rights reserved. 29
13 A new benchmark is available for decision support systems: TPC-DS, which is designed to compare both traditional warehousing and big
data solutions. However, there has been no official results submission since it became a standard in 2012.
14 64 processors seem to be the limit (and the price tag is in the 5-million-dollar range).
www.persistent.com
4.4.1.7 Should I consider buying anMPP database, or will an SMP database do the jobwell?
Please refer to the glossary (chapter for a brief explanation of the terms MPP (massively parallel processor) and
SMP (Symmetric Multi-Processing).
Although PSL’s customers generally have already decided on the database engine they will use for their DW/BI
rd project, we have seen recently opportunities (see 3 solution of section where we have been asked to study
thematter andmake a recommendation, so we believe it is pertinent to review this aspect in this section.
MPP and SMP database systems have been measured on a well-known decision support benchmark, namely TPC-
13 H .Unfortunately, TPC-H has received multiple criticisms, and some of the most popular vendors (Teradata, IBM
Netezza, HP Vertica, Pivotal Greenplum) have withdrawn from it. Only a handful of DBMS vendors continue
measuring their systems, among which there are Microsoft (although not for their MPP database), Oracle, Actian,
Exasol, and Sybase IQ.
Nevertheless, the only MPP database still officially measured by TPC-H (Exasol, a little known German DBMS
provider, who markets an in-memory, MPP database) performs better than the other SMP systems, both in
performance and in price/performance. Other supporting evidence is that, in previous years, results for vendors with
both SMP and MPP products (IBM DB2, Oracle 11g/12g) were better for their MPP products. Finally, we have come
across products in the popular vendor MPP list and they indeed perform well in the high end when they are tuned
appropriately. Some results and further explanations can be found on sectio and below.
On the other hand, SMP Databases have their share of advantages with respect to MPP: they are relatively cheaper,
and they are easier to manage and configure. So, for relatively small to medium database sizes (in the order of a few
terabytes and below), DBMSs working on SMP machines remain good candidates. However, in SMP architectures,
the use of a single shared communication channel to communicate between CPU, memory and I/O becomes a
bottleneck to systemperformance as the number of CPUs and their clock speeds increas
4.4.1.8 Should I consider buying anOLAP server?
As we will discuss in chapter below, the single most dramatic way to affect performance in a large data
warehouse is to provide a proper set of aggregate records that coexist with atomic fact records. OLAP servers have
been designed from the ground up to natively support the dimensional model. However, the choice between deploying
relational aggregation tables or OLAP dimensional cubes is not necessarily easy. It is a multi-faceted decision. It is a
mistake to consider it as a tactical choice to be executed at the very end of the data warehouse development
only because aggregation is the last step when building the warehouse.
OLAP cube technology has the following advantages (over relational):
— They have far superior analytic capabilities. We will review this aspect in section below.
— When the cubes are designed correctly, they deliver much better performance than relational aggregations,
with less tuning effort.
— Security is more powerful on OLAP thanks to the parent-child semantics implicit in access languages.