OLTP Vs. OLAP Databases

Most businesses use Online Transaction Processing (OLTP) databases to gather and store the records generated by their daily operations. Typically, OLTP databases execute transactions, meaning that they add, update, or delete groups of records at the same time.

 For example, the database for a grocery store inserts and updates information about prices, purchases, and costs of goods and freight, usually at lightning speed.


However, the design that allows OLTP databases to record transactions quickly and accurately also makes it hard to analyze their data for several reasons. First, OLTP databases contain a large number of tables, sometimes hundreds. Those tables often have multiple relationships with other tables in the database. That complexity can make it hard to understand the database and know where to look for data.

Second, if you try to extract OLAP data from an OLTP database, you usually need to create and run stored procedures—groups of SQL statements compiled into a single execution plan.

OLAP and OLTP databases differ in several respects. First, IT departments usually keep OLAP databases isolated from OLTP databases. Doing so ensures that the transaction database performs well and that the OLAP database only receives historical business data. In addition, while the data in an OLTP database constantly changes, the data in an OLAP system never changes. Users never perform data-entry or editing tasks on OLAP data. All they can do is run mathematical operations against the data.

Second, OLAP databases use fewer tables and a different type of schema. For example, an OLAP database typically uses between five and 20 tables. In addition, they usually keep the number of joins to a minimum by arranging tables a star schema.

The central table in the schema is the fact table. Fact tables contain numeric data, such as zip codes, and additive data such as the total costs of freight for all beverages.
By themselves, numeric facts do not have much meaning. For instance, the number 206 by itself does not mean much. However, it takes on more meaning if you know that it represents an area code or the number of dishwashers sold yesterday. In a star schema, dimension tables contain the descriptive text that gives meaning to the numbers. Keep in mind that most analysis involves time, which makes time itself a key dimension.
The facts in a dimension are called members. By design, OLAP databases group the related facts in a member into hierarchies whenever the underlying data supports that type of structure. For example, the Time dimension in the preceding figure contains the following hierarchy:
• Year
• Quarter
• Month
• Order Date
Hierarchies use traditional parent/child relationships. For instance, Quarter is a child of Year, Month is a child of Quarter, and so on. If a child contains data that your OLAP system can aggregate, its parent level contains those aggregated sums. Some systems call those aggregated sums rollups. Whenever you drill up or down through your data, you navigate through those hierarchies.
The joins between the dimension and fact tables allow you to browse through the facts across any number of dimensions, as well as up and down any number of hierarchies. For example, you might query for:
Total sales and total costs for all beverages purchased in 1999 by customers in Colorado.
—or—
Total sales and total costs for beer purchased in 2000 by customers in Colorado.
The second query, of course, takes data from different hierarchy levels in the Time and Product dimensions.

The simple design of the star schema makes it easier to write queries, and they run faster. For example, running the total sales and costs query against an OLTP database could involve dozens of tables, making query design complicated. In addition, the resulting query could take hours to run.
Third, OLAP databases make heavy use of indexes because they help find records in less time. In contrast, OLTP databases avoid them because they lengthen the process of inserting data.