In Microsoft Dynamics AX 2012 database we can find an OLTP database, which stores the AX data and the AX Model store, and the SSAS database used for SSRS reporting. At the same time, the Microsoft SQL Server Analysis Services uses an on-line analytical processing (OLAP) engine that helps users analyze business data and identify trends.
In this post I would like to describe what OLTP and OLAP is, differences, and provide a day-to-day example about the two.
OLTP (On-line Transaction Processing) deals with operational data, which is data involved in the operation of a particular system and it is characterized by a large number of short on-line transactions (INSERT, UPDATE, and 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 addition, in an OLTP system, the data is frequently updated and queried and to prevent data redundancy and to prevent update anomalies the database tables are normalized, which makes the write operation in the database tables more efficient.
Example: In a banking System, you withdraw amount through an ATM. Then account Number, ATM PIN Number, Amount you are withdrawing and Balance amount in account are operational data elements.
An OLAP (On-line Analytical Processing) deal with Historical Data or Archival Data, and it is characterized by relatively low volume of transactions. In addition, the Queries needed for these systems are often very complex and involve aggregations as for OLAP systems the response time is an effectiveness measure.
Example: If we collect last 10 years data about flight reservation, the data can give us much meaningful information such as the trends in reservation. This may give useful information like peak time of travel, and what kinds of people are traveling in the various classes available (Economy/Business).
The following table summarizes the major differences between OLTP and OLAP system design.
| OLTP System | OLAP System | |
| Source of data | Operational data; OLTPs are the original source of the data. | Consolidation data; OLAP data comes from the various OLTP Databases |
| Purpose of data | To control and run fundamental business tasks | To help with planning, problem solving, and decision support |
| What the data | Reveals a snapshot of ongoing business processes | Multi-dimensional views of various kinds of business activities |
| Inserts and Updates | Short and fast inserts and updates initiated by end users | Periodic long-running batch jobs refresh the data |
| Queries | Relatively standardized and simple queries Returning relatively few records | Often complex queries involving aggregations |
| Processing Speed | Typically very fast | Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes |
| Space Requirements | Can be relatively small if historical data is archived | Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP |
| Database Design | Highly normalized with many tables | Typically de-normalized with fewer tables; use of star and/or snowflake schemas |
| Backup and Recovery | 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 |
