* OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually3NF). Example: In a banking System, you withdraw amount through an ATM. Then account Number,ATM PIN Number,Amount you are withdrawing, Balance amount in account etc are operational data elements.
* OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). Example: If we collect last 10 years data about flight reservation, The data can give us many meaningful information such as the trends in reservation. This may give useful information like peak time of travel, what kinds of people are traveling in various classes (Economy/Business)etc.
Online Transaction Processing (OLTP)
|
Online Analytical Processing (OLAP)
|
Designed to support Daily DML Operations of your application.
|
Designed to hold historical data for analyses and forecast business needs.
|
Holds daily Latest Transactional Data related to your application.
|
Data is consistent up to the last update that occurred in your Cube.
|
Data stored in normalized format.
|
Data stored in denormalized format.
|
Databases size is usually around 100 MB to 100 GB.
|
Databases size is usually around 100 GB to a few TB.
|
Used by normal users.
|
Used by users who are associated with the decision making process, e.g., Managers, CEO.
|
CPU, RAM, HDD space requirement is less.
|
CPU, RAM, HDD space requirement is higher.
|
Query response may be slower if the amount of data is very large,it can impact the reporting performance.
|
Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
|
T-SQL language used for query.
|
MDX is used for querying on OLAP Cube.
|
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability.
|
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method.
|
