Difference in online analytical Processing System and Online Transaction processing system
Online analytical processing system and online transaction processing
system or both used for querying the database. OLTP queries are used
for live systems for transactions with real-time application.
In analytics queries need to be passed through with many where
sub query group by order by steps.
When these steps become many OLTP query engine not able to
performed query processing in expected time.This is a reason we need
OLAP. Microsoft Analysis services and Excel Pivot with Ryndata BI or
Visual studio data tools are common tolls.
Query performance:
OLTP query engine is designed to process quickly transaction data. when data volume is high and multiple joins are applied, join cost becomes high. More CPU and memory and search are required. Analytical queries are normally taken from several tables. It includes many group by, Order by and sum, min, ,max average etc operations. Usually time taken by OLTP engine for analytical query is more than 3 minutes to hours. Time processing time is not acceptable.
OLAP engine is designed in such way that it can return analytical query result in less than 10 seconds. Design using Dimension, facts, Measure group and cubes are well formed to make processing on fly. When cube processing happens it aggregates and compose possible results. All best possible query are computed at the time of processing. Delta changes are immediately processed when data changed through write back method.
Easiness for users:
To analyze or plan business key performance indicators (KPI), user need to to do two kinds of activity. Data comparison or evaluation and decision making. For data evaluation user view data on different levels. For example: sale by product, sub department, department, gender, geography, time series etc. These are dimensions to view KPI for decision making. For decision making user can set sales forecast or demand impact on each level (Dimensions). For example when planner want to set next month sales target by 3% for department “Sport wear” he can adjust department target and below all data by sub department, articles will get new target as per department.
Excel Pivot, Power BI, Micro strategy, Tablue, Ryndata planning tool are some common tool for end user.
Data Volume:
As soon tables size grow OTLP query is not able to serve query result in given time. While OLAP is able to made for the same. Up to 5 TB data in data warehouse OLAP can serve complex query response in below seconds.
Data Schema or Data Warehouse:
OLAP cubes are cooked from data warehouse database, it could be MS-SQL server, MySQL, Oracle, PostGreSQL. Fact tables and dimension tables are designed to connect each other with simple and fully connected relation mode. Granular storage mode of fact tables make data ware tables relation flexible and easy for adopt any kind of change in future. Data volume with size like Petabyte or more is unmanageable this approach. Here you need to jump on clustered computing model like Hadoop, Bigdata, Azure HD-Insight AWS RedShift etc.
To prepare data warehouse schema integration with business intelligence Ryndata BI engine helps. It’s works in top down solution. Define KPI from end user point of view, accordingly requires data warehouse will be generated or updated. Using Ryndata ETL service load data from source system to Data Warehouse. This is tool for developer, business user and integration team. Do not requires more technical knowledge. Quick prototyping and early feedback is key advantages of this software. Dashboard, Planning and data analysis given in excel and web. To Know more: Ryndatalab.com
Limitation:
Which OLAP data real time processing becomes challenging when delta change in data is very fast. Several users want to connect system simultaneously and wanted data context separation. There are several work around like cube partitioning, user role and permission setup, authorization model etc.