SQL AND BUSINESS INTELLIGENCE

Every deployed RDBMS can be arbitrarily divided into two broad categories: online transaction processing (OLTP) databases and online analytical processing (OLAP) databases. Some deployed systems may represent a mix of both.

An OLTP system is designed to support transactions; for example, order processing, inventory tracking, recording employee data, and so on, in very granular detail. Such systems are designed to process large volumes of concurrent transactions as quickly as possible. In short, the main purpose of such a system is to accumulate structured information.

An OLAP system works with aggregates and is designed to make sense out of the accumulated data, allowing for analyzing data at various levels of abstraction. These systems are used to discover trends and analyze critical factors, perform statistical analysis, and so on. While important, speed is not the main feature of such systems, as OLAP queries typically process large amounts of data. Normally, OLAP databases extract information from several specialized databases called data marts.

The foundation of the OLAP is a data warehouse. Unlike OLTP databases, the data warehouses are not normalized (at least not to the same degree as OLTP databases). The data model of the data warehouse is different from the OLTP database, and there are at least two major flavors deployed: dimensional and relational. The former is advocated by Dr. Ralph Kimball and operates within the concept of a specialized ...

Get Discovering SQL: A Hands-On Guide for Beginners now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.