The Data Warehouse

1
Chapter 13

• The Data Warehouse
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

2
Data Mining (continued)
3
Data Mining (continued)
4
SQL Extensions for OLAP
5
The ROLLUP Extension
6
The CUBE Extension
7
Materialized Views

• A dynamic table that contains not only the SQL
  query command to generate rows, but also stores
  actual rows
• Created the first time query is run and summary
  rows are stored in table
• Automatically update when base tables are updated

8
Materialized Views (continued)
9
Materialized Views (continued)
10
Summary

• Decision support is methodology designed to
  extract information from data and to use such
  information as basis for decision making
• Decision support system is arrangement of
  computerized tools used to assist managerial
  decision making within business
• Operational data are not best suited for decision
  support
• Requirements for a DSS DBMS are divided into four
  main categories database schema, data extraction
  and loading, end-user analytical interface, and
  database size requirements

11
Summary (continued)

• Data warehouse is integrated, subject-oriented,
  time-variant, nonvolatile database that provides
  support for decision making
• OLAP is advanced data analysis environment that
  supports decision making, business modeling, and
  operations research
• ROLAP provides OLAP functionality by using
  relational databases and familiar relational
  query tools to store and analyze multidimensional
  data

12
Summary (continued)

• Star schema is data-modeling technique used to
  map multidimensional decision support data into
  relational database
• Four techniques generally used to optimize data
  warehouse design normalizing dimensional tables,
  maintaining multiple fact tables representing
  different aggregation levels, denormalizing fact
  tables, and partitioning and replicating tables

13
Summary (continued)

• Data mining automates analysis of operational
  data with intention of finding previously unknown
  data characteristics, relationships,
  dependencies, and/or trends
• SQL has been enhanced with extensions that
  support OLAP type processing and data generation