Databases for Business Intelligence (graded) We\'ve been focusing on operational
ID: 3652393 • Letter: D
Question
Databases for Business Intelligence (graded)We've been focusing on operational databases, often called online transaction processing (OLTP) systems. These OLTP systems handle the day-to-day business of the company and are accessed by multiple users simultaneously updating the data in short transactions.
Business intelligence systems are sometimes called online analytical processing (OLAP) systems. These OLAP systems are used to make decisions and are characterized by few users submitting long running queries that do not update the database to analyze the historic data of the company. What are some other differences between OLTP and OLAP systems? How are data warehouses similar or different from these two database models?
Explanation / Answer
The access patterns of readers and writers typically differ: Readers are more prone to larger analysis types of queries, and writers are more prone to singleton inserts, updates, and deletes. When these activities are separated, the administrator can focus on recovery strategies for a smaller, more manageable transaction processing system. OLTP databases tend to be much smaller than data redundant decision-support or analysis-oriented databases. Having said this, however, this distinction cannot always be clearly made. After data is copied, transformed, and archived to an analysis-oriented database, the data must be maintained and/or rebuilt periodically. Users definitely benefit from looking at a transactionally consistent version of the database; however, that version of the database no longer has current data, may take many hours to build and index, and might not be what the user really wants. In this situation, snapshot isolation and read committed isolation using row versioning may be useful. The main issue with the use of transactional replication in earlier versions is that the movement of data from the distribution database to the subscription database can experience the same issues that occur in systems that try to support OLTP and reporting on the same server: Long-running reports in the subscription database can block the replicated data that is arriving from the distribution database. This blocking can cause the subscription database to become increasingly out of synch and behind the publication database. This could frustrate call center employees who are trying to help customers that have recent purchases or reservations that have not yet arrived in their system. The blocking problem can be lessened by using the new SQL Server 2005 behavior for the read committed isolation level: read committed using row versioning.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.