To understand the differences between OLAP vs OLTP we first need to define each of these really mean.
- OLTP – Online Transaction Processing
- OLAP – Online Analytical Processing
The abbreviations give us clues as to what each of the database design approaches accomplish. These are online database systems that process data. These two styles are easier to understand if we look at their practical applications. As a result, this can give us insights on how these databases are used on a daily basis.
Examples of OLTP
People commonly interact with databases every day. They just don’t know it or think about it. For example, we use self-scan machines or the clerk scans our items that store our transactions. As a result, these transactions are stored in an OLTP database.
Any item purchases we make are logged into database storage. The key word in the OLTP abbreviation being transaction. As another example, any time we make a withdrawal or deposit from our bank, that’s a transaction. Any time a transaction occurs we know that the database is most likely an OLTP design.
Examples of OLAP
A dimensional model (aka star-schema) is an OLAP style database. The key word of the OLAP abbreviation is analytical. We’re looking to analyze the data. For instance, we can use an OLAP database for aggregations, high level reporting, and to “slice and dice” data. These databases are a variation of the OLTP design approach.
We can generate reports that show:
- Year over year analysis
- Demographic performance
- Employee or department performance
- and much more…
OLAP vs OLTP
OLTP systems usually follow 3rd normal form (aka 3NF). This ensures that these systems effectively store the data. These systems are great for data entry and transaction processing. They are not great for data extraction and aggregated reporting. Transaction data can easily become very large in a short period of time. As a result, it’s likely that transaction data will be deleted if it is older than a certain period of time.
OLAP systems do not follow a standard database normalization but fall somewhere around 2NF. We use these databases for reporting and analytics. It’s important to maintain historical data in OLAP systems since OLTP systems tend to remove older transaction history. Because of this, data will source from OLTP systems and land in an OLAP system for reporting. We commonly refer to these OLAP databases as data warehouses.
To sum it all up, we use OLTP systems on a regular basis. OLAP systems are used as an OLTP modifier for reporting and analytics. We’re likely to use OLAP data warehouses in a corporate setting. In conclusion, we have two systems that accomplish very unique tasks.