Differences Between a BI/Data Warehouse System and an OLTP System

Differences Between a BI/Data Warehouse System and an OLTP System

 

1. Level of detail: The OLTP layer stores data with a very high level of detail,whereas data in the Data Warehouse is compressed for high-performance access(aggregation).

 

2. History: Archiving data in the OLTP area means it is stored with minimal history. The Data Warehouse area requires comprehensive historical data.

 

3. Changeability: Frequent data changes are a feature of the operative area, while in the Data Warehouse, the data is frozen after a certain point for analysis.

 

4. Integration: In contrast to the OLTP environment, requests for comprehensive,integrated information for analysis is are very high.

 

5. Normalization: Due to the reduction in data redundancy, normalization is very high for operative use. Data staging and lower performance are the reasons why there is less normalization in the Data Warehouse.

 

6. Read access: An OLAP environment is optimized for read access. Operative applications (and users ) also need to carry out additional functions regularly,including change, insert, and delete.

 

 

 

In the figure above, you can see that there are fundamentally different demands on an OLTP system compared with a Data Warehouse/ BI (OLAP) system. It is therefore most advantageous to technically separate all aggregated reporting-related demands made on the Data Warehouse from the OLTP system.