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

XXII
Introduction
cause (why), and effect (how), we document new and established dimensional
techniques from a dimensional perspective for the first time.
Chapter 6: Who and What: People and Organizations, Products and
Services
Design patterns for
customer, employee
and product
dimensions
Modeling customers, employees, and organizations: Handling large, rapidly
changing dimension populations. Tracking changes using mini-dimensions.
Mixed business models: Using exclusive attributes and swappable dimensions to
model heterogeneous customers (businesses and consumer) and products (tangible
goods and services).
Advanced slowly changing Patterns: Modeling micro and macro-level change.
Supporting simultaneous current, historical, and previous value reporting re-
quirements using hybrid SCD views.
Representing complex hierarchical relationships: Using hierarchy maps to handle
recursive hierarchies, such as customer ownership, employee HR reporting struc-
tures, and product composition (component bill of materials and product bun-
dles).
Supporting variation within business events: Using multi-level dimensions to
describe events with variable granularity such as sales transactions assigned to
individual employees or to teams, web advertisement impressions for single prod-
ucts or whole product categories.
Chapter 7: When and Where: Time and Location
Design patterns for
time and location
dimensions
Modeling time dimensionally: Using separate calendar and clock dimensions and
defining date keys.
Year-to-date (YTD) analysis: Using fact state tables and fact-specific calendars to
support correct YTD comparisons.
Time of day bracketing: Designing custom business clocks that vary by day of
week or time of year.
Multinational calendars: Modeling multinational dimensions that cope with time
and location. Supporting time zones and national language reporting.
Modeling movement: Overloading events with additional time and location
dimensions to understand journeys and trajectories.
Chapter 8: How Many: Facts and Measures and KPIs
Design patterns for
modeling efficient
fact tables and
flexible facts
Designing fact tables for performance and ease of use: Defining the three basic
fact table patterns: transactions, periodic snapshots, and accumulating snapshots.
Using event timelines to model accumulating snapshots as evolving events.
Providing the basis for flexible measures and KPIs: Defining atomic-level addi-
tive facts. Documenting semi-additive and non-additive facts, and understanding
their limitations.
Fact table performance optimization: Using indexing, partitioning, and aggrega-
tion to improve fact table ETL and query performance.