Data Warehousing - Database Systems - page 375

24.3.3 Processing Requirements
Data warehouses have very different processing requirements from OLTP systems and
operational databases, as explained below.
Workload: Data warehouses are designed to accommodate ad hoc queries. The
workload of the data warehouse might not be known in advance, so a data warehouse
should be optimized to perform well for a wide variety of possible query operations. OLTP
systems and operational databases support only predefined operations. Your applications
might be specifically tuned or designed to support only these operations.
Data Modifications: A data warehouse is updated on a regular basis by the ETL
process (run nightly or weekly) using bulk data modification techniques. The end users
of a data warehouse do not directly update the data warehouse. In OLTP systems and
operational databases, end users routinely issue individual data modification statements
to the database. The OLTP database is always up to date, and reflects the current state of
each business transaction.
Schema Design: Data warehouses often use non-normalized or partially normalized
schemas (such as a star schema ) to optimize query performance. OLTP systems and
operational databases often use fully normalized schemas to optimize update/insert/
delete performance, and to guarantee data consistency.
The star schema was introduced in Chapter 5 (section 5.6); it is so widely mentioned
in database literature, it deserves a bit of attention: A star schema describes a mechanism
where there is a “central” table referred to as a fact table , and other so-called dimensional
tables that relate to the fact table via 1:M relationships. The dimensional tables contain
dimensional data about details stored in the fact table. While the star schema is
widely used in data warehouses, it is also applicable in operational databases as well.
Figure 24-1 provides an illustration of a star schema of five relational tables for tracking
the graduation statistics from a regional university that operates multiple schools and
programs in multiple locations. Tables Location , AcademicProgram , TimePeriod , and
School qualify as dimensional tables, while table GraduationSummary qualifies as the
fact table.