View the original community article here

Last tested: Aug 28, 2018
 

OLTP and OLAP are 2 different ways of structuring your database for analysis, each with its own unique strengths and weaknesses.

OLTP

OLTP stands for On-Line Transactional Processing. It’s used to run fundamental business tasks by enabling fast query processing through maintaining data integrity in its environment. OLTP databases typically have the most detailed and current operational data and is highly normalized with many tables (3NF).

Settings where OLTP databases excel include retail, restaurants, commerce, where transactions occur rapidly and the end user (ie. business user) is making relatively short, standardized queries with the purpose of running ongoing business processes. Effectiveness of OLTP databases are measured by transactions per second. 

OLAP

OLAP stands for On-Line Analytical Processing. This type of database is used for planning, problem solving, decision support through the use of historic or archival data. Typically this involves relatively low volume of transactions and queries that tend to be complex, requiring aggregations. OLAP databases are typically structured in a multi-dimensional schema (star or snowflake), which are highly de-normalized with few tables. 

Settings where OLAP databases excel include research, data-mining, machine-learning environments, where the end user (ie. statistician, analyst, etc.) is typically looking at a large set of historic data to gain insight on patterns/correlations, and support decision making processes. Larger businesses may also choose to utilize this, as they often collect data from many different systems, which leads them with the challenge: how to get all the data together to make informed and reliable decisions. Effectiveness of OLAP databases are measure by response time.