OLTP and OLAP

Conception de la base de données

Lis Sulmont

Curriculum Manager

Our motivating question:

How should we organize and manage data?

  • Schemas: How should my data be logically organized?
  • Normalization: Should my data have minimal dependency and redundancy?
  • Views: What joins will be done most often?
  • Access control: Should all users of the data have the same level of access
  • DBMS: How do I pick between all the SQL and noSQL options?
  • and more!
Conception de la base de données

Our motivating question:

How should we organize and manage data?

  • Schemas: How should my data be logically organized?
  • Normalization: Should my data have minimal dependency and redundancy?
  • Views: What joins will be done most often?
  • Access control: Should all users of the data have the same level of access
  • DBMS: How do I pick between all the SQL and noSQL options?
  • and more!
It depends on the intended use of the data.
Conception de la base de données

Approaches to processing data

OLTP

Online Transaction Processing

Counter kiosk for check-out

OLAP

Online Analytical Processing

Data dashboard on laptop

Conception de la base de données

Some concrete examples

OLTP tasks

  • Find the price of a book
  • Update latest customer transaction
  • Keep track of employee hours

OLAP tasks

  • Calculate books with best profit margin
  • Find most loyal customers
  • Decide employee of the month
Conception de la base de données

OLAP vs. OLTP

OLTP OLAP
Purpose support daily transactions report and analyze data
Design application-oriented subject-oriented
Data up-to-date, operational consolidated, historical
Size snapshot, gigabytes archive, terabytes
Queries simple transactions & frequent updates complex, aggregate queries & limited updates
Users thousands hundreds
Conception de la base de données

Working together

OLAP and OLTP

Conception de la base de données

Takeaways

  • Step back and figure out business requirements
  • Difference between OLAP and OLTP
  • OLAP? OLTP? Or something else?
Conception de la base de données

Let's practice!

Conception de la base de données

Preparing Video For Download...