OLAP

Processing Types

Online Analytic Processing (OLAP)

• Long running queries (minutes to hours)
• Few but complex queries
• Does not depend on an absolutely up-to-date database

Online Transaction Processing (OLTP)

• Quicker queries (seconds to minute)
• Most database operations
• Short, simple, frequent queries and/or modifications that involve a small number of tuples

Queries

• Common operation is to aggregate a measure over one or more dimension
  • Drill-down
    • The inverse of a roll up
    • Going down the hierarchy (given sales by state, get total sales by city)
  • Pivoting
    • Aggregation on selected dimensions
    • Obtain a cross tabulation (aggregate over columns, rows, and then the aggregations themselves)
    • Requires 4 queries for 2 dimensions
  • Slicing and dicing
    • Equality and range selections on one or more dimensions
    • Slicing the data into multiple sections, and then selecting one to view
• Techniques
  • Queries often start with a “star join” (a natural join with the table and the dimension tables)
  • Use materialized views to speed up queries by saving results that save enough information