ETL (extract, transform, load) is the backbone of data processing for modern enterprises, helping you get the valuable insights you rely on day in and day out.
Yet despite ETL being a critical business function, too many users aren’t familiar with what’s causing the issues when they encounter ETL challenges. Below, we’ll discuss what you need to know about one of the most important distinctions in ETL: OLTP and OLAP databases.
OLTP vs. OLAP: What’s the Difference?
There are two main types of relational databases: OLTP and OLAP. While both are heavily used for a wide variety of business applications, they’re quite different in terms of their primary use case.
OLTP (online transactional processing) databases are primarily designed to handle large quantities of transactions. As such, OLTP databases need to comply with the ACID (atomic, consistent, isolated, and durable) properties. This ensures that each transaction will still be valid in the case of unexpected failures or errors. OLTP databases emphasize accuracy and integrity, which means that redundant and duplicate data should be kept to a minimum.
The classic example of an OLTP database is a financial system that needs to process user transactions on a day-in, day-out basis (e.g. bank account deposits, withdrawals, and transfers). Under the hood, these transactions are represented as INSERT, UPDATE, and DELETE statements in an OLTP database.
OLAP (online analytical processing) databases, on the other hand, prioritize quick, efficient reporting and analysis of large quantities of data. These databases focus on joining and aggregating data and crunching the numbers. As such, they’re generally used for BI and analytics workloads, and are not exposed to the end user. Data warehouses and data marts (specialized data warehouses for the use of a single team or department) are the most common types of OLAP databases.
This different use case means that in practice, OLAP databases look very different from OLTP databases. OLAP databases have more redundant information, and the data is more likely to be denormalized. They also typically have fewer (but larger) tables than OLTP databases.
OLTP and OLAP Databases in ETL
ETL commonly features both OLTP and OLAP databases. Data is extracted from one or more OLTP sources, then transformed and loaded into an OLAP system. Effective ETL workflows depend on this OLTP-OLAP symbiosis.
The most common type of OLAP system in ETL is the data warehouse: a database for storing large quantities of data that has been optimized for business intelligence and analytics. Data marts are smaller data warehouses that are intended for the exclusive use of one team or department (e.g. sales or finance).
Once it’s inside your OLAP system, data needs to be organized efficiently to promote ease and speed of analysis. Data inside an OLAP database is often collected in multidimensional arrays of data known as data cubes, also known as OLAP cubes or hypercubes. Each “dimension” of the cube corresponds to a different attribute of the data: for example, product, sales, or location.
Note, however, that OLTP and OLAP databases aren’t necessarily the only types of data repositories involved in the ETL process. Data lakes are unstructured repositories (not databases) that store raw, unprocessed data. Much of the contents of a data lake is stored “just in case,” and may or may not eventually be used for BI and analytics. If information in a data lake needs to flow into your data warehouse, you’ll need to account for this connection when building your ETL architecture.
OLTP and OLAP databases are just scratching the surface of ETL. Want to know how to solve some of the most pressing and common issues with your ETL workflow? I’ve written just the guide for you. Check out my white paper “The Top 5 Challenges of ETL (And How to Solve Them).”
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.