Ranet OLAP
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

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 comparisonOLAP system (Online Analytical Processing)OLTP system (Online Transactional Processing)
Data SourceHistorical / archive dataOperational data
Data FunctionMultidimensional views of various business activities help planning, decision supportRunning controlled business tasks, presenting an overview of ongoing business processes
Inserts / UpdatesPeriodic long-running batch processes refresh the dataShort and fast inserts and updates carried out by end users
QueriesStandardized simple queriesComplex queries containing aggregations
Transaction speedDepends on the amount of data, typically longer than in OLTPVery fast
Space requirementsVast because of aggregation structureRelatively small
Database designTypically denormalized with fewer tables / star and snowflake schemasHighly normalized with numerous tables
Backup and RecoveryAs a substitute for regular backups like in OLTP, some environments can offer to reload the data as a recovery methodObligatory. The intelligence is crucial to run the business. Operational data loss can result in serious financial loss and legal liability
NormalizationTables are not normalizedTables in the database are normalized in 3NF
IntegrityDue to rare modifications, integrity is not affectedMaintaining data integrity constraint
UsersExecutives and data scientistsNot specialized users, staff

 

Conclusion

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.


Write US