- 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
