OLTP vs OLAP
filed in olap, oltp on Apr.24, 2009
In many Business Intelligence implementation cases, there’s a certain difficulty for IT Pro to explain or describe the difference between OLTP (OnLine Transactional Process) and OLAP (OnLine Analytical Process). I hope the table below might help a little.
source: 360decisions
| Item | OLTP | OLAP |
|---|---|---|
| Purpose | Automate the business | Optimize the business |
| Use | Transaction processing | Reporting, analysis, planning, forecasting and trending |
| Schema | 2dimensional normalized | Multi-dimensional, denormalized |
| Navigation | Based on transaction flow | Based on user requirements |
| Operation | Mostly insert, update and deletes | Mostly selects |
| Calculation | Aggregation, simple matrix | Aggregation, simple matrix, X-dimensional, formula-based |
| Implementation | Slow to deploy and difficult to change | Fast to deploy and easy to change |
| Data Usage | Operational | Historical trending, analysis and discovery |
| Data level | Atomic | Consolidated and customized |
| Availability | 24/7 dependency | No dependency on source |
| Refresh Frequency | Immediate | Periodic refresh (Hourly, daily or weekly) |
| Example | Flight online systems, order processing systems | Sales per employee |
| Goal | Capture complete data | Retrieve data in seconds / minutes |
| Design | Relational (RDBMS) | ROLAP, MOLAP, HOLAP (star, snow-flake or hybrid) |
| Data Mining & Discovery | Difficult to perform | Easy to perform |
| Clean Data | No | Yes |
| Transformed Data | No | Yes |
| View of Data | Single | Multiple (sales regions are separate then financial regions) |
| IT dependency | Yes | Some IT dependencies for complex query |
| Scalability | Poorly scalable | Highly scalable |
| Data level | Detailed, transaction data | Summarized data (mostly) |
| Use | Operational | Decission Support |
| Data Usage | Current Data | Historical Data |
| Update | Changing with business events | Fixed as of a specific point in time |
| Source | Only data for specific function of application | Data integrated from various applications |
June 30th, 2010 on 5:49 am
I like your site. Very cool. Will come back.