Task 13: OLAP vs OLTP

I read a lot about these differences between Transaction Processing and Analytical Processing but today I better clarified myself the concepts. The idea is a simple one:

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. 

• Transaction is achieved by the concept called OLTP. 
• Analysis is achieved by the concept called OLAP. 

Online Transaction Processing is driven by Operational needs and Online Analytical Processing is driven by Strategic needs. Collective OLAP is termed into one concept knows as BI. There are some OLTP applications like: reservation systems, point of sales, inventory control, automated tellers, manufacturing systems. The requirements are current data, rapid data update, high availability. As client/server systems: DB2, Oracle,Access, SQL Server, Departmental or small business data, normalized design. 

You need to know that OLTP systems are not easy to query because ad-hoc queries do bad things to transactional systems and its performance but the problem is solved by ETL process and OLAP. Data is extracted from various sources, the extracted data is transformed into related information for advanced BI processing and finally it is loaded into destinations as tables or materialized views.

These materialized views are database objects that contains the result of a query.Basically it is a process by which necessary data is collected from various remote tables and stored into one local area. The original data in the remote system is safe from changes because the materialized views are read-only and can also be synchronized periodically with the main data, thus keeping them updated. 

OLAP starts from where ETL process ends. It is a group of technologies and applications that collect, manage, process and present multidimensional data for analysis and management purposes, making use of the information from ETL processes output. OLAP effectively makes use of ETL process output which is mostly of materialized views. 

- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, 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 OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). 

- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).