My first Publication Agile-Data-Warehouse-Design-eBook | Page 29

8
Chapter 1
Star Schemas
Star schemas are
used to visualize
dimensional models
Real-world dimensional models are used to measure far more complex business
processes (with more dimensions) in far greater detail than could be attempted
using spreadsheets. While it is difficult to envision models with more than three
dimensions as multi-dimensional cubes (they wouldn’t actually be cubes), they can
easily be represented using star schema diagrams. Figure 1-3 shows a classic star
schema for retail sales containing a fourth (causal) dimension: PROMOTION, in
addition to the dimensional attributes and facts from the previous cube example.
Figure 1-3
Sales star schema
Star schema is also the term used to describe the physical implementation of a
dimensional model as relational tables.
Star schema diagrams are non-normalized (N3NF) ER representations of dimen-
sional models. When drawn in a database modeling tool they can be used to
generate the SQL for creating fact and dimension tables in relational database
management systems. Star schemas are also used to document and define the data
cubes of multidimensional databases.
ER diagrams work best for viewing a small number of tables at one time. How
many tables? About as many as in a dimensional model: a star schema.
Fact and Dimension Tables
Star schemas are
comprised of fact
and dimension
tables
A star schema is comprised of a central fact table surrounded by a number of
dimension tables. The fact table contains facts: the numeric (quantitative) meas-
ures of a business event. The dimension tables contain mainly textual (qualitative)
descriptions of the event and provide the context for the measures. The fact table
also contains dimensional foreign keys; to an ER modeler it represents a M:M