Business Intelligence - OLTP vs OLAP (Differences)

Before I go to bed, I would like to share about OLTP and OLAP. This article is taken from 2 source: MicroStrategy and Datawarehouse4u. I think this article will be useful for us in order to understand more about the foundation of Business Intelligence. Enjoy. :)

---MicroStrategy---

#Source Systems (OLTP)

Source systems are typically databases, file sources, web sources or mainframes that store transaction processing data. As such, they are an OnLine Transaction Processing System (OLTP). Transaction processing involves simple recording of transaction like sales, inventory, withdrawals, deposits, and so forth.

The data in source systems have the following characteristics:

• Data access is read/write (numerous selects, inserts, updates, and deletes).
• Data is aligned by application (business activities and workflow).
• Data formats are not necessarily uniform across different source systems.
• Data history is typically limited to recent or current data.

A bank is one example of a business with many different types of source systems. The average bank offers their customers a variety of services and, therefore, needs these different type of source systems to support their services, each of which has a different workflow.

For example, consider the Automated Teller Machine (ATM) and the bank teller. At an ATM, you can withdraw or deposit money as well as check your account balance. However, if you need a money order, you have to go inside and speak with a bank teller. The bank has different operational system, each design to perform tasks that are specific to the service.

To take our example further, if a bank wanted to see a unified view of a particular customer - such as a customer's ATM activity, loan status, account balances, money market account information, and so forth - all of the customer's information from each of the different source systems would have to be collected. After this data is collected, it is stored in a centralized data warehouse.

#Data Warehouse (OLAP)

A well-designed and robust data warehouse lies at the heart of the business intelligence system and enables its users to leverage the competitive advantage that business intelligence provides. A data warehouse is an example of an OnLine Analytical Processing System (OLAP).

While source systems are designed and optimized for transaction processing, data warehouses are designed and optimized for analytical

processing. Analytical processing involves manipulating transaction records to calculate sales trends, growth patterns, percent to total contribution, trend report, profit analysis and so forth.

The data in a data warehouse have the following characteristics:

• Data access is typically read-only (IN-SQL, mostly selects are used. Generally very few inserts, updates, or deletes are used).
• Data is aligned by business subjects.
• Data format are uniformly integrated.
• Data history extends long term, usually 2 to 5 years.

A data warehouse is populated with data from the existing operational systems with an ETL process.

#Summary of Differences - OLTP VS OLAP

The following table summarizes the differences between OLTP and OLAP systems:

#ETL Process

The extraction, transformation, and loading (ETL) process contains information that facilitates the transfer of the data from the source systems to the data data warehouse.

Specifically, the ETL Process does the following:

• Store information about the source system structure and content
• Store information about the data warehouse structure and content
• Correlates source system structure and content to that of the data warehouse
• Provides information to the data extraction tools that physically execute the transfer of data from the source systems to the data warehouse

Source : MicroStrategy PDF - Reporting Essential

---Datawarehouse4u---

#OLTP vs. OLAP

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.

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).


The following table summarizes the major differences between OLTP and OLAP system design.

Source : datawarehouse4u.info