What is the difference between OLAP and
OLTP ?
* 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.
|
