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

How to Model a Data Warehouse
7
plex join paths. Such temporal database designs can defeat even the smartest BI
tools and developers.
Laying out a readable ERD for any non-trivial data model isn’t easy. The mne-
monic “dead crows fly east” encourages modelers to keep crows’ feet pointing up
or to the left. Theoretically this should keep the high-volume volatile entities
(transactions) top left and the low-volume stable entities (lookup tables) bottom
right. However, this layout seldom survives as modelers attempt to increase read-
ability by moving closely related or commonly used entities together. The task
rapidly descends into an exercise in trying to reduce overlapping lines. Most ERDs
are visually overwhelming for BI stakeholders and developers who need simpler,
human-scale diagrams to aid their communication and understanding.
Large readable ER
diagrams are
difficult to draw: all
those overlapping
lines
The Case For Dimensional Modeling
Dimensional models define business processes and their individual events in terms
of measurements (facts) and descriptions (dimensions), which can be used to filter,
group, and aggregate the measurements. Data cubes are often used to visualize
simple dimensional models, as in Figure 1-2, which shows the multidimensional
analysis of a sales process with three dimensions: PRODUCT (what), TIME
(when), and LOCATION (where). At the intersection of these dimensional values
there are interesting facts such as the quantity sold, sales revenue, and sales costs.
This perspective on the data appeals to many BI users because the three-
dimensional cube can be thought of as a stack of two-dimensional spreadsheets.
For example, one spreadsheet for each location contains rows for products, col-
umns for time periods, and revenue figures in each cell.
Dimensional models
appeal to
spreadsheet-savvy
BI users
Figure 1-2
Multidimensional
analysis