OLAP and OLTP systems

1. OLAP and OLTP systems

Hello again. Let's continue our discussion on data warehouse architecture. This video will focus on another core component of data warehouse implementations called OLAP and how it compares to OLTP systems.

2. OLAP systems

OLAP stands for Online Analytical Processing. It is a tool for performing multidimensional analysis at high speeds on large volumes of data from a data warehouse, data mart, or some other centralized data store. They are optimized for analysis. In data warehousing, most organizations have data organized into different dimensions, such as sales figures by country, state, and city. Another dimension example is time, broken into years, months, and days. Data warehouses store data in rows and columns. OLAP systems take this two-dimensional representation of data in rows and columns and reorganize it into a multidimensional format that enables fast processing for analysis. This multidimensional format allows for what is commonly called "slicing and dicing" the data. Data scientists and analysts typically work with OLAP systems.

3. OLAP cube

At the core of the OLAP system is the OLAP data cube, a multidimensional database that makes it possible to process and analyze multiple data dimensions faster than a traditional relational database. To provide more context, imagine we are interested in the organization's sales by region, year, and product. If we picture a cube, the cube's different edges, or height, width, and length, will represent one of these dimensions. We will have the total sales for those dimensions where these edges intersect. The data cube can drill down or aggregate the total sales by each dimension. In this example, the dimensions are region, year, and product, and total sales is the value that is aggregated or disaggregated based on the selected dimensions. Data cubes that have more than three dimensions are called hypercubes. So now, let's talk about OLTP.

4. OLTP

OLTP stands for online transaction processing. These systems are optimized for processing a large volume of simple database transactions and queries as quickly as possible. Typical uses of OLTP systems include cash terminals and reservation bookings. In these examples, the OLTP systems processes simple queries to the database, like inserting, updating, and deleting rows. Queries for OLTP systems tend to affect only a few rows of data within the database. OLTP systems are often critical for the business and not used for analysis. Organizations often use them in transactional databases or the source systems that feed into the data warehouse.

5. Example for a credit card company

Now let's look at an example. Meet Eli, a data engineer at a credit card company. He is responsible for maintaining the OLTP system that tracks each customer's purchase and updates their current balance. This system was designed to track thousands of purchases and their updates every second. However, this system was not designed to analyze customer purchasing patterns. Instead, Eli's team uses a data warehouse and an OLAP system that tracks purchases by year, customer age, location, and time of day. Analysts within the company use this data to make business decisions.

6. Summary

In summary, OLAP systems are designed to support data analysis. They execute complex queries of multiple rows or transactions faster than a traditional relational database. The core of the OLAP system is the data cube which represents the data in multidimensions allowing for data slicing. Comparatively, OLTP systems are designed to be very fast at performing simple database queries that focus on one or only a few rows of data. The core of these systems is the database table which stores the data in rows and columns.

7. Let's practice!

Alright, let's test your knowledge!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.