OLTP versus OLAP

Databases either fall into two broad categories. The first is OLTP, which stands for On Line Transaction Processing. These are what most people think of when they think of a database. They are usually designed for single record access. This is an important caveat. This single record can be updated, inserted, deleted, or selected. The way the database is architected becomes very important in these instances. Typically, data in an OLTP database is normalized, meaning that data is split into multiple tables with various relationships between them, something we refer to as “atomicity” (the A in ACID).  I have worked with hundreds of databases over the years, and each one uses normalization to some degree, with some databases being more or less normalized than others. It is a design choice as it reduces the overhead of operating an OLTP database in a full production environment.

The alternative, and one I want to explore more in this blog, is the OLAP database. The OLAP database is more affectionally known as the Data Warehouse. These are architected much differently than standard OLTP databases. The main focus here is easy querying. I want to look at popular Data Warehouse technologies, how they are setup, accessed, reported on, and maintained. I want to focus on popular offerings both self-hosted and in the cloud environment. I want to look at “star schemas”, fact tables and dimensions. I want to look at ETL/ELT technologies. I want to understand all of the reporting options from SSRS to PowerBI and various points in between. I want to spend a few days just reviewing Data Warehouses and Business Intelligence in general to get a good survey, and then dive deeper into areas that interest me.