An Overview of Data Warehousing
and OLAP Technology
q OLTP applications vs. OLAP applications
n Architecture of data warehousing
n Back end tools
n Conceptual model
n Front end tools
n Some examples
Introduction (OLTP applications)
n OLTP applications typically automate clerical data processing
q such as order entry and banking transactions
n These tasks are structured and repetitive, and consist of short,
atomic, isolated transactions.
n The transactions require detailed, up-to-date data.
n Consistency and recoverability of the operational database are
Introduction (OLAP applications)
n OLAP applications are designed for supporting decision making.
n Historical, summarized and consolidated data are often used.
n The workload of data warehouses are query intensive with
mostly ad hoc, complex queries that access millions of records.
n Query throughput and response times are critical.
Introduction (problem statement)
n Given that operational databases, trying to execute complex OLAP
queries would result in unacceptable performance.
n Decision support requires data that might be missing from the
n Decision support requires consolidating data from many heterogeneous
n Supporting the multidimensional data models and operations requires
special data organization, access methods and implementation
n =>Data Warehouse
Architecture of data warehouse
Back end tools
n Data extraction
q ODBC, Oracle Open Connect, and Sybase Enterprise
n Data clearing
q Tools that help to detect data anomalies and correct them.
q Tools that help to load data into the warehouse.
q Refreshing a warehouse consists in propagating updates
on source data.
q Star schema
n Index structures
q Bit map indices, join indices
n Materialized views
n Transformation of complex SQL queries
n Parallel processing
n SQL extensions
q Aggregate function
q Reporting features
q Multiple group-by
Front end tools
q Operations for spreadsheet
n Data mining tools