Nowadays, practically all organizations can’t but accept big data streaming and processing as the most valuable resource to profit in their business. Due to data mining and handling companies are able to apply analytics and collect vital insights for forecasts and future strategies.
OLTP and OLAP are online processing systems to operate data. In this article, we are going to describe each system and its function whereupon you will see a comparison of both.
What is OLAP
If you refer to the definition of OLAP, you will find out that it is short for Online Analytical Processing. This technology analyzes a large amount of data kept in a data warehouse. OLAP performs all the data in multidimensional cubes, enabling users to detail, consolidate, and rotate it to see it from various perspectives and analyze trends.
The procedure of filling the OLAP system from the source systems takes place by using ETL (extract-transform-load). These actions simplify the data management and simultaneously reduce the absorbed effort. MDX queries can be small, but mostly they are rather extensive and need much time to accomplish. Aggregated incoming OLAP queries accommodate all data of a specific subset. So users are able to analyze trends, averages, aggregations, etc. OLAP applications are exploited by techniques for Data Mining most often. OLAP database allows to set up the data by the criterion as the user wants it to be visualized.
OLAP is usually divided into 3 types: MOLAP, ROLAP, and HOLAP. MOLAP which is a multidimensional online analytical processing is a standard type of OLAP and is often mentioned as OLAP itself. ROLAP takes its name from “relational” as it operates specifically with relational databases. HOLAP is a hybrid and it aims to unite advantages of both MOLAP and ROLAP.
Example of OLAP
A simple example for a user would be a array containing data of the sales depending on territory and gender.
The MDX query, in this case, would look the following way:
1 2 3 4 5 6 7 8 9 10 11
SELECT NON EMPTY VISUALTOTALS(HIERARCHIZE(HIERARCHIZE([Employee].[Gender].Levels(0).Members)), '* - Total**') DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0, MEMBER_TYPE ON 0, NON EMPTY VISUALTOTALS(HIERARCHIZE(HIERARCHIZE([Sales Territory].[Sales Territory Country].Levels(0).Members)), '* - Total**') DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, CUSTOM_ROLLUP, UNARY_OPERATOR, KEY0, MEMBER_TYPE ON 1 FROM [Adventure Works] CELL PROPERTIES BACK_COLOR, CELL_ORDINAL, FORE_COLOR, FONT_NAME, FONT_SIZE, FONT_FLAGS, FORMAT_STRING, VALUE, FORMATTED_VALUE, UPDATEABLE, ACTION_TYPE
What is OLTP
OLTP is generally compared to OLAP as both of the systems operate data. OLTP is short for Online Transaction Processing, which is typically marked by far less complex queries which are conducted in a larger volume. OLTP is to process a big number of online transactions, though short, in real time dealing with such kinds of queries as Read, Insert, Update and Delete.
The main goal for OLTP systems is to conduct rapid query processing, storing data integrity in multi-access environments. The efficiency is determined by a number of transactions per second. In order to conduct vast series of successful transactions in OLTP system, those transactions have to be atomic, consistent, isolated, and durable (ACID).
Example of OLTP
A query selects all issues owned by a user with ID=1 with statuses 'Resolved', 'Published', 'Closed', 'Done', sorts them, groups them by projects, and displays their number.
1 2 3 4 5 6 7 8
SELECT tab2.`key` AS project_key, tab1.`status` AS issue_status, COUNT(*) AS t_count FROM jira_issues AS tab1 LEFT JOIN jira__project AS tab2 ON tab1.project_id = tab2.id WHERE tab1.user_assigne = 1 AND tab1.`status` IN ('Resolved','Published','Closed','Done') GROUP BY project_key, tab1.`status` ORDER BY project_key
Difference between OLAP and OLTP
As we have already mentioned OLTP handles transactions while OLAP serves as reporting for business intelligence. Whereas OLAP systems are mostly streamlined for reading, OLTP has to manage various kinds of queries: read, insert, update and delete. If OLTP needs to preserve transactions accuracy and integrity, it's not the purpose of OLAP. OLAP has a smaller group of users (executives, data scientists, marketers, managers) than OLTP systems and its interactions last longer. OLTP must be fast and sustained to mainstream plenty of transactions at a time, while OLAP ought to be large and powerful enough to encompass all relevant business data.
Let’s take a look at the systems’ comparison set out in a table:
|Category for comparison||OLAP system (Online Analytical Processing)||OLTP system (Online Transactional Processing)|
|Data Source||Historical / archive data||Operational data|
|Data Function||Multidimensional views of various business activities help planning, decision support||Running controlled business tasks, presenting an overview of ongoing business processes|
|Inserts / Updates||Periodic long-running batch processes refresh the data||Short and fast inserts and updates carried out by end users|
|Queries||Standardized simple queries||Complex queries containing aggregations|
|Transaction speed||Depends on the amount of data, typically longer than in OLTP||Very fast|
|Space requirements||Vast because of aggregation structure||Relatively small|
|Database design||Typically denormalized with fewer tables / star and snowflake schemas||Highly normalized with numerous tables|
|Backup and Recovery||As a substitute for regular backups like in OLTP, some environments can offer to reload the data as a recovery method||Obligatory. The intelligence is crucial to run the business. Operational data loss can result in serious financial loss and legal liability|
|Normalization||Tables are not normalized||Tables in the database are normalized in 3NF|
|Integrity||Due to rare modifications, integrity is not affected||Maintaining data integrity constraint|
|Users||Executives and data scientists||Not specialized users, staff|
OLTP is a system to modify data online whilst OLAP is an online multidimensional data retrieval system, which extracts the data that can help in business to analyze the performance, develop strategies and make decisions. OLTP system is a provider of data for OLAP. The systems are functional to different objectives but both of them help deal with a big amount of data. OLAP is up to let you figure out places, time and reasons your clients buy, foresee market tendencies and future requirements thanks to data mining and analysis, whereas OLTP can grant your clients with personalized loyalty programs and provide them with a credible service.