- LPU MCA Semester 1
- CAP570
- Unit 6
CAP570 · Unit 6: Advanced databases
OLAP and OLTP
11 practice questions · in the ETE syllabus
Key points
- OLTP: many short transactions on current, normalised data
- OLAP: analytical queries on historical data
- Roll-up summarises; drill-down adds detail
- Slice fixes one dimension; dice fixes several
OLTP (Online Transaction Processing) supports day-to-day operations such as bank deposits and bookings: many short transactions, frequent inserts and updates, current data, and highly normalised tables. OLAP (Online Analytical Processing) supports analysis and decisions: complex queries over large amounts of historical data, mostly reads, and usually denormalised star schemas in a data warehouse or data cubes.
| Aspect | OLTP | OLAP |
|---|---|---|
| Purpose | Run the business | Analyse the business |
| Operations | Short inserts, updates | Large aggregating reads |
| Data | Current | Historical, summarised |
| Design | Normalised | Denormalised (star schema) |
Typical OLAP operations on a cube of region, city and quarter:
| Operation | Meaning | SQL idea |
|---|---|---|
| Roll-up | Summarise to a higher level (city to region) | GROUP BY region |
| Drill-down | Go to more detail (region to city) | GROUP BY city |
| Slice | Fix one dimension | WHERE qtr = 1 |
| Dice | Fix several dimensions | WHERE qtr = 1 AND region = 'East' |
| Pivot | Rotate to show a different view | SUM(CASE WHEN ...) columns |
Running both on one system lets long analytical queries slow down short transactions, so they are usually separated.
Exam trap: roll-up goes up the hierarchy (less detail) and drill-down goes down (more detail).
Try two questions
Why are OLTP and OLAP workloads usually run on separate systems?
- Heavy analytical queries would slow down the short transactions of daily operations
- OLAP data can be stored only on tape and cannot be placed beside OLTP data
- OLTP systems cannot store any dates, so history must be kept elsewhere
- Separate systems are needed because SQL cannot be used for analysis
Show the answer
A. Keeping them apart protects transaction response time from long analytical scans.
Why not B: OLAP data is stored in databases or warehouses, not only on tape.
Why not C: OLTP systems can store dates.
Why not D: SQL is widely used for analysis.
Which description fits an OLTP database?
- Denormalised star schemas loaded in nightly batches for reports
- Read-only data cubes holding many years of history
- Normalised tables with frequent inserts and updates of current data
- Raw files with no schema collected from social media
Show the answer
C. OLTP favours fast, small, concurrent transactions on current data.
Why not A: That describes a data warehouse used for OLAP.
Why not B: That describes OLAP data cubes.
Why not D: That describes a data lake.