OLAP and OLTP systems

Data Warehousing Concepts

Aaren Stubberfield

Data Scientist

OLAP systems

  • OLAP (online analytical processing)
  • Designed to support analysis of large amounts of data
  • Example dimensional organization:
    • country, state, city
    • years, months, days
  • OLAP reorganizes data into multidimensional format
Data Warehousing Concepts

OLAP cube

  • OLAP cube key to OLAP system
  • Faster processing vs. traditional relational databases
  • Hypercubes have more than three dimensions

Image of a cube with axes labeled region, year, and product

Data Warehousing Concepts

OLTP

  • OLTP (online transaction processing)
  • Designed for processing simple database queries
  • Used in source systems to data warehouse

Portrait of a person receiving money from a cash terminal

1 Photo by Rodnae-Productions on Pexels
Data Warehousing Concepts

Example for a credit card company

OLTP:

  • System tracks customer's purchase
  • Processes large amounts of simple database updates to account balances

OLAP:

  • Designed for analyzing purchase data
  • Data organized by multiple dimensions

Portrait of a happy confident young african american professional standing with his arms crossed looking at camera

Data Warehousing Concepts

Summary

Differences OLAP OLTP
Optimization: Complex read-only queries for analysis Simple queries
Data Representation: Multidimensional Rows and columns
Data Warehousing Concepts

Let's practice!

Data Warehousing Concepts

Preparing Video For Download...