Series "The Modeling Room: Design Out Loud for Collaborative Data Warehouse Modeling"

Who should read it?

Students, DW architects, computer scientists, BI users.

Series Articles

1. Modelstorming: modeling with people, not at them [coming soon]
2. Data stories and business events: the raw material of every warehouse
3. Dimensions: the nouns your business keeps talking about
4. Hierarchies and history: dimensions that change without lying
5. Business processes: the event matrix and the warehouse bus
6. From whiteboard to star schema
7. Fact table types and measures: counting without cheating
8. Who and what patterns: customers, products, and their monsters
9. When, where, why, and how patterns: calendars, causes, and context

Article 1: Modelstorming - modeling with people

Requirements meetings produce documents nobody reads; modelstorming produces models everyone owns.

Summary

Requirements documents die the day they are signed. Modelstorming keeps BI users and modelers on one whiteboard, turning spoken business events into star schemas with 7 simple questions. This part introduces BEAM* and the 7Ws shared language.

Non-technical angle

Meeting psychology; shared whiteboard ownership beats documents; why "the model is the requirements" changes the power dynamic between BI users and modelers.

What it covers

• "What is BEAM*?" (collaborative analysis + modeling)
• "Why does modelstorming beat traditional up-front requirement gathering?"
• OLTP vs. OLAP in plain terms
• ER modeling vs. dimensional modeling
• The 7Ws (who, what, when, where, how many, why, how) as the shared language, data model types.

Article 2: data stories and business events: the raw material of every warehouse

Every fact table was once a sentence someone spoke.

Summary

Every fact table was once a sentence someone spoke. Learn to turn that sentence into a data story, spot its type from prepositions like "every" and "between", and pick the fact table shape it becomes.

Non-technical angle

Storytelling as analysis; listening for verbs and prepositions in business conversations.

What it covers

• "What is a business event?"
• "What is a data story?"
• Data story vs. user story
• Story types
  • Discrete DE
  • Recurring RE
  • Evolving EE
• The fact tables stories become
  • Transaction TF
  • Periodic snapshot PS
  • Accumulating snapshot AS
• The 7Ws flow chart
• The 3Ds
  • Discover
  • Document
  • Describe
• Walking through modeling one event step-by-step
• Event granularity GD
• Naming events
• Story type codes
• Preposition clues
  • Every = recurring
  • 2 unknown "whens" = evolving

Article 3: dimensions: the nouns your business keeps talking about

Facts answer "how many?", dimensions answer everything else.

Summary

Facts answer "how many?"; dimensions answer everything else. This part discovers the nouns of your warehouse from 6 of the 7 questions, documents their attributes, and sketches the hierarchies your organization already thinks in.

Non-technical angle

Dimensions are the vocabulary of management questions; hierarchy = how organizations naturally think (organization charts, org reporting lines).

What it covers

• What dimensions and dimensional attributes are
• Discovering dimensions from 6 of the 7Ws
• Documenting dimensions
• Descriptive attributes
• Boolean attributes
• Mandatory attributes MD
• Missing values
• Mutually exclusive attributes Xn
• Defining characteristics DC
• Dimensional hierarchies intro
  • Attribute discovery
  • Candidate attributes
• Modeling queries

Article 4: hierarchies and history: dimensions that change without lying

The customer changed address; your report must not lie about last year

Summary

The customer changed address; your report must not lie about last year. Hierarchy types and slowly changing dimension rules decide how numbers roll up and how change is told. Get both right and the boardroom keeps trusting your reports.

Non-technical angle

Organizational memory and trust; why a report that contradicts last quarter's destroys credibility in the boardroom.

What it covers?

• Dimensional hierarchy types
  • Balanced
  • Unbalanced/ragged
  • Recursive/variable-depth
  • Multi-parent
• Hierarchy charts and diagram notations
  • Brackets
  • Circular path
  • Double bar
• Modeling dimensional hierarchies
• Completing hierarchies
• Hot levels
• Dimensional history and SCD types
  • Type-0 fixed FV
  • Type-1 current CV
  • Type-2 historic HV
  • Type-3 previous PV
• Telling change stories
• Corrections vs. changes
• Group change rules
• Effective dating
• Minor events.

Article 5: business processes: the event matrix and the warehouse bus

Model one event and you get a table; model the process and you get a data warehouse.

Summary

One event gives you a table; the whole process gives you a warehouse. The event matrix lines up the events of a business process, exposes the shared dimensions and the bus beneath your marts, and shows which events are missing before you build.

Non-technical angle

Politics of shared vs siloed reporting; conformance as an agreement between departments; prioritization as negotiation.

What it covers?

• Event sequences
• The event matrix
• Discovering process sequences
• Discovering missing events and missing event details
• Modeling multiple events
• Silo data marts vs integrated marts
• Conformed dimensions
• The data warehouse bus and dimensional matrix
• Modelstorming with the event matrix
• Prioritizing events
• Dimensions and report user stories.

Article 6: from whiteboard to star schema

The meeting is over, the sketch is agreed; now make it real.

Summary

The sketch is agreed and the meeting is over; now make it real. From agile data profiling over the model review to keys, audit attributes, and time dimensions, this part turns the whiteboard event table into a star schema a database can build.

Non-technical angle

Why surrogate keys SKs protect against real-world mess; review meetings as trust-building; profiling as listening to the data before asking it questions.

What it covers?

• agile data profiling
  • What
  • Sources
  • Annotating results
• The model review meeting
• The star schema anatomy
• Star schema design
• Database keys
  • Surrogate keys SKs
• Audit attributes and effective dating in the schema
• Intra-day vs. inter-day changes
• Time dimensions (calendar vs. clock)
• Converting event table to fact table
• Modeling facts
• Degenerate dimensions DD
• Roll-up dimensions RU
• The enhanced star schema
• Snowflake anti-pattern
• Physical schema
• Prototyping
• The physical matrix (data warehouse matrix) and event matrix vs. physical matrix

Article 7: fact table types and measures: counting without cheating

3 table shapes cover almost every business process you will ever model.

Summary

3 table shapes cover almost every business process:

• transactional
• periodic snapshot
• accumulating snapshot

Additivity and counting rules decide whether a measure can be trusted, with a worked example of an average that goes wrong.

Non-technical angle

Averaging averages; the executive who summed percentages; why measure semantics are a business trust issue.

What it covers?

• Fact table types in depth
  • Transactional TF
  • Periodic snapshot PS
  • Accumulating snapshot AS with worked examples
• Fact table granularity
• Fact additivity
  • Non-additive NA
  • Fully-additive FA
  • Semi-additive SA
• Counting correctly
  • order count examples
  • atomic vs. aggregated levels
• Factless fact tables and coverage tables
• Dummy facts
• Heterogeneous facts and Xn separation
• Evolving business events and measures

Article 8: who and what patterns: customers, products, and their monsters

Real customers have 2 million rows, 40 behaviors, and 3 addresses; here is the toolbox.

Summary

Real customers run to millions of rows and dozens of changing behaviors. Mini-dimensions, outriggers, swappable dimensions, hierarchy maps, and as-was versus as-is reporting keep your who and what dimensions honest as they grow.

Non-technical angle

HR org charts as data structures; why "as-was" vs "as-is" reporting matters for commissions, bonuses, and audits.

What it covers?

• Very large dimensions VLD
• Mini-dimensions
• Sensible snowflaking and outriggers
• Swappable dimensions
• Embedded whos
• Recursive relationships
• Variable-depth hierarchies
• Hierarchy maps HM with type-2 SCD
• Employee/HR patterns
  • Hybrid HV+CV as-was/as-is reporting
  • Year-end dimension as-at reporting
  • Previous value CV/PV
  • Multi-value hierarchy maps MV/HM with weighting factors
  • Historic MV/HM
• Product dimension patterns
• Balancing ragged hierarchies
• Parts-explosion/bill-of-materials
• Multi-level dimensions ML
• Services

Article 9: when, where, why, and how patterns: calendars, causes, and context

Time is the most used dimension and the most mis-modeled.

Summary

Time is the most used dimension and the most mis-modeled. Calendars, clocks, geography, causes, and audit trails answer "when", "where", "why", and "how" for every number you publish. The closing pattern toolbox of the series.

Non-technical angle

Calendars encode culture (fiscal years, holidays, seasons); language and geography as business identity; why cause dimensions answer the "why did sales drop?" question executives actually ask.

What it covers?

• Calendar dimensions
  • ISO/epoch date keys
  • Periodic calendars
  • Offset calendars
  • YTD comparison
  • Fact state tables
  • Fact-specific calendars
  • Conformed date ranges
  • Populating calendars
  • BI display formats
• Clock dimensions
  • Day clock
  • Seasonal clock
  • Time keys
• International time
  • Multi-national calendar
  • Date version keys
• Where patterns
  • Travel/geography
  • National language dimensions
  • Swappable language dimensions
• Why patterns
  • Causal dimensions
  • Text dimensions
  • Multi-valued dimensions MV
  • Weighting factors
  • Bridge tables
  • Optional bridges
  • Pivoted dimensions PD
• How patterns
  • Degenerate dimensions with comments
  • Range band dimensions
  • Step dimensions
  • Audit dimensions