Saturday, December 1, 2007

BI (2) OLTP vs. OLAP

Purpose

  • OLTP: To support business processes.
  • OLAP: To assist with making better business decisions.


Where the data comes from

  • OLTP: Online entry, point of sale devices, data loggers etc.
  • OLAP: OLTP databases, flat files.


Criticality

  • OLTP: Generally considered missioncritical. Regularly back up to protect from data loss.
  • OLAP: Important to decision support; however; organizations can continue to operate without it. Back up less often than OLTP databases.


Database Size

  • OLTP: Relatively small database size if the history information is regularly archived. Can quickly bloat if history is not removed.
  • OLAP: OLAP database can be very large. Contains historical information generated by various OLTP databases within an organization.


Concurrent Users

  • OLTP: Potentially very high numbers of users. Some OLTP databases support thousands of concurrent users.
  • OLAP: OLAP databases support a much lower number of users than their OLTP counterparts.


Response Time

  • OLTP: All application queries should complete in less than a second.
  • OLAP: Queries commonly take seconds, minutes, and hours.


Data Changes

  • OLTP: High number of insert, update, and delete operations are generate by user transactions.
  • OLAP: Very low number of data changes generated by users. Data is updated by bulk import batches.


Ad hoc querying

  • OLTP: Poor ad hoc query performance. Indexes are only created to support defined application queries.
  • OLAP: Very good ad hoc query performance. Indexes are created to support queries possible queries.


Querying complexity

  • OLTP: Data is highly normalized requiring many joins to retrieve information.
  • OLAP: Data is denormalized requiring few joins to retrieve information.

Professional SQL Server 2000 Data Warehousing with Analysis Services

blog comments powered by Disqus