Showing posts with label OLTP-OLAP. Show all posts
Showing posts with label OLTP-OLAP. Show all posts

Monday, 19 May 2014

Difference Between OLTP and OLAP

OLTP vs OLAP - Definition and Differences

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it. 




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

Labels

#veryusefulcode (1) AIF (8) AOT Maps (1) Args (1) Ax 2009 Reports (2) AX 2012 navigation (1) Ax 2012 Interview Questions (1) AX 7 (2) AX Architecture (1) Ax Backup (1) AX Workflow (2) AX2012 (1) AX2012 R2 (1) Ax2012R3 (1) AX2012R3 Dynamics Connector Step by Step Installation and Configuration (1) AX2012R3 EP Step by Step Installation and Configuration EP R3 (1) AX2012R3 HelpServer Step by Step Installation and Configuration (1) AX2012R3 Rapid Start Connector Step by Step Installation and Configuration (1) AX2012R3 Report Server and Analysis Server Step by Step Installation and Configuration (1) AX7 (1) Best practices (1) Blocking user to enter (1) Collection Classes (1) Container (1) D365FO (3) Data Migration Frame Work ax 2012R3 (1) Deleting duplicate Record from Table – Ax2012 (1) Delivery due date notification workflow in Ax 2012 (1) Development Steps EP (1) Dimensions (1) DIXF (1) DMF in Ax 2012 R3 (1) Dynamics Ax 2012 Interview Questions (1) DYNAMICS AX 2012 INTERVIEW QUESTIONS PART 2 (1) DYNAMICS AX 7 (1) EDT relation Migration Tool (1) EP AX 2012 (1) Ep Lookup (1) Error (1) Event Handler (1) F5 (1) File Handling (4) Filter on AX2012 Listpage (1) filtering (2) financial dimensions in AX 2012 (3) form (1) images (1) Installation and Configration (4) Installation and Configuration (11) Installation of Management Reporter 2012 for AX 2012 (1) Interaction class in ax 2012 (1) Interview Question (1) Interview Questions For Ax 2012 (1) Invent DIm (1) Jobs (2) license (1) List page and form menuitem enable code (1) Methods (1) microsoft Dynamics AX 365FO (1) Number Sequence Generation – AX 2012 (5) Number Sequence2012 (1) OLTP-OLAP (1) Passing Args (1) Passing form caller and menu item caller in ax 2012 (1) Passing Multiple Records Args (1) Posting in Ax 2012 (1) POSTING PURCHASE ORDER (1) Query (1) Query Filter Form (2) Query Ranges in X++ (1) Question and Answer (1) Report (1) Reports Controller class (1) RLS in ax 2009 (1) SALES ORDER IMPORT/EXPORT FRAMEWORK BY DMF (1) Security (1) security roles (1) Security Sysqueryrangeutil (1) Sharepoint 2016 (1) SQL SERVER (1) SSRS (2) SSRS Reports Controller class (2) Table collections & Virtual company (1) Time (1) TIPS AND TRICKS (1) Web service AIF (3) Web Services on IIS (AIF) Step by Step Installation and Configuration (1) workflow ax2012 (1) Workflow installation (1) Workflow Method (3) X++ (1)