Database Management Systems (Additional Topics)

Database Management Systems

Part 7: Additional Topics

Chapter 25. Data warehousing and decision support

OLTP: online transaction processing -- operational data: make small changes, a large number of transactions

decision support: complex queries <-- precomputing view definition can make queries run much faster

data warehouses: a centralized repository of all data -- where clause contains many AND and OR conditions, applications require extensive use of statistical functions, conditions over time or require aggregating over time periods, users need to pose several related queries

OLAP: online analytic processing -- complex boolean conditions, statistical functions, features for time-series analysis

exploratory data analysis: data mining

multidimensional data model: a collection of numeric measures, each measure depends on a set of dimensions

MOLAP: multidimensional OLAP -- OLAP systems that use arrays to store multidimensional datasets

fact table: relates the dimensions to the measure of interest

for each dimension, the set of associated values can be structured as a hierarchy

dimension tables: relations are much smaller than the fact table

ROLAP: relational OLAP stores all information, including fact tables and dimension tables, as relations

star schema: a combination of a fact table and dimension tables, centered at the fact table -- the bulk of data is typically in the fact table, which has no redundancy, usually in BCNF (dimension identifiers are system-generated identifiers, to minimize the size of the fact table)

dimension tables are usually not normalized, because they are static and updates are not important; space saved by normalizing dimension tables is negligible (dominated by fact table) --> instead minimize the computation time for combining fact table with dimension information (avoid additional joins)

materialization of summary tables (typically generated through queries using grouping): precomputed summaries used for answering ad hoc queries -- deciding which summary tables to materialize