October 22, 2014 \ Ananth TM OLTP Vs OLAP or Data Warehousing systems A tabular comparison of OLTP or transaction processing system with Data Warehouse or a OLAP with all major criteria considered with some examples This compares OLTP vs OLAP or OLTP vs DW (Data Warehouse) OLTP OLAP Indexes Few Too many Joins Many Some Data duplication Normalized database (usually 3NF or 3rd Normalized form – optimize update/insert/delete performance, and to guarantee data consistency De-normalized or partially de-normalized schemas (such as a star schema) to optimize query performance. Derived data & aggregates Rare Common Workload Only specifically designed queries will execute Supports ad hoc queries, but workload can be planned ahead Tuning Optimized based on predetermined queries Optimized for wide array of queries Data modifications In OLTP systems, end users routinely issue individual data modification statements to the database. A data warehouse is updated on a regular basis by the ETL process (run nightly or weekly) from data from the sources using bulk data modification techniques. The end users of a data warehouse do not directly update the data warehouse. Typical operations A typical OLTP operation accesses one or a handful of records. For example, “Retrieve the current order for this customer.” A typical data warehouse query scans thousands or millions of rows. For example, “Find the total fees charged for all customers in the last year.” Historical data OLTP systems usually store only the current version of the data or recent ones as needed to successfully meet the requirements of the current transaction Data warehouses usually store many months or years of data. This is to support historical analysis.