Analytics databases are optimized to be used for retrieval, aggregation, tabulation, dissemination, and analytics, under the general banner of business intelligence (BI). They deliver benefits in a BI environment because they are designed for high performance in this environment, whereas an online transaction processing (OLTP)-oriented database is optimized for a very different role.

OLTP-oriented databases, on the other hand, are designed for high performance in an on-line transactional environment, where additions, updates, and deletions are being continuously made to individual or small numbers of records in a random manner over time.

IBM’s DB2, Oracle, Microsoft SQL Server and Sybase’s ASE are the leading products in this segment.

Some of the major differentiating factors between OLTP and analytics databases include:

• Generally speaking, OLTP databases are designed to host operational systems that are used to run the business. Analytics databases step in when operational use of the data has ended, and the data are to be used for information and analysis.
• In terms of load and update timing, OLTP databases are designed to support continuous additions of, updates to, and deletions of individual or small numbers of records, scattered randomly throughout the tables. In an analytics database environment, data becomes available in batches, and can be bulk loaded; bulk adds or updates of a single column are efficient as well; records are usually not deleted, but if they are it is in large contiguous quantities.
• From an integrity perspective, OLTP databases are optimal in situations where it is critical that many small transactions are applied correctly, with back-out and roll-forward capabilities. In an analytics database environment, batch integrity for updates is the main concern.

• When it comes to concurrency, OLTP databases support environments where many concurrent users are retrieving, adding, updating, and deleting data on an unscheduled basis. Analytics databases, on the other hand, support environments where very many concurrent users are retrieving data but adds, updates, and deletes are done centrally on a scheduled basis by administration staff.
• The platforms also vary on retrieval dimension. In OLTP environments, retrievals are oriented towards single or few records where many or most columns from these records are required. In analystics databases, retrievals are oriented towards many or most records (statistical operations); few columns from these records are required.
• You should consider your information retrieval requirements when selecting the appropriate platform for your needs. If you expect to need detailed information from the databases, an OLTP database is the right choice for you. On the other hand, if you will mainly require aggregate or statistical results (counts, sums, means, etc.) from the database, an analytics database may be the best option.

If you’re looking for some examples of OLTP databases, think of the systems that businesses use for managing bank account transactions, reservations, and electronic commerce order-fulfillment. Analytics databases, on the other hand, are used for data warehouses, financial analysis, customer modelling and similar “Big Data” activities.

Why use an analytics database?

As analytics databases are designed to be used for statistical retrievals, using one of these products in an appropriate application instead of Oracle or SQL Server provides two major benefits.

First, analytics databases may offer lower costs. It is almost certain that in the appropriate environment, equivalent performance could be obtained at between 10 and 50 percent of the hardware and software license cost of an OLTP database solution.

Second, analytics databases provide additional capabilities not found in traditional OLTP database platfrms. Many of the operations that benefit from this class of product involve table scans, the requirement to retrieve most of or all of the rows in a table. This is a known weakness of OLTP databases. For example, I know of a number of client areas where this kind of capability is desired, but using their current OLTP database to meet it causes so many problems that it is either avoided completely, or done infrequently under heavy schedule constraints. I have personal experience of doing this with analytics databases, and the efficiency is suitable for ad-hoc use.

Where should we use an analytics database?

Broadly, analytics databases should be considered in environments where the data are relatively final, are to be exploited for tabulation and analysis, and are of historical interest (it is desired to retain the data for an extended period of time).

What are some examples of analytics databases?

Some of the major analytics database products include Teradata, IBM’s Netezza, Greenplum, and Sybase IQ.