I85PREP
⌘ K
DSST MIS study materials
DSST MIS study resources

DSST MIS: Data Warehousing: Dimensional Modelling, Grain and ETL

DSST MIS · Work through your exam, one topic at a time.

All guide sections

Data Warehousing: Dimensional Modelling, Grain and ETL

Data Warehousing

The exam tests the warehouse's purpose, dimensional modelling, ETL, and the differences between transactional and analytical systems. Expect questions on star schemas, grain, slowly changing dimensions and data quality.

Core concepts

  • OLTP vs OLAP. OLTP systems are normalised, high-volume, current-state and single-row oriented. Warehouses are denormalised, subject-oriented, historical and read-mostly.
  • Inmon vs Kimball. Inmon builds an enterprise warehouse top-down from a normalised 3NF model with dependent marts. Kimball builds bottom-up from dimensional marts, unified by conformed dimensions and a bus matrix.
  • Star vs snowflake. A star has one fact table surrounded by denormalised dimensions. A snowflake normalises dimensions into hierarchies - it saves space but adds joins.
  • Facts and dimensions. Facts hold numeric measures plus foreign keys; dimensions hold descriptive context (who, what, when, where). Grain is the meaning of one fact row, and must be declared before dimensions are chosen.
  • Additivity. Fully additive (sales dollars); semi-additive (inventory balance - add across stores, not across time); non-additive (ratios and percentages).
  • ETL. Extract, transform (cleanse, conform, deduplicate, derive) and load. Surrogate keys insulate the warehouse from source key changes; an unknown row handles missing references.
  • SCD types. Type 1 overwrites with no history; Type 2 adds a new row with version dates and a current flag; Type 3 adds a prior-value column.

Worked example

A retailer declares its sales fact grain as one row per product per store per day, with quantity and extended price. A manager asks for monthly sales by store. Is a simple SUM valid?

Yes. The grain is finer than the requested aggregation (day to month, product to all products), and quantity and price are additive across both product and time, so SUM grouped by store and month is correct. But if that fact table also carried an ending-inventory measure, summing it across days would double-count stock; it is semi-additive and must be averaged or sampled at period end.

Traps

  • Summing semi-additive balances across time.
  • Mixing grains in a single fact table.
  • Using natural source keys instead of surrogate keys.
  • Ignoring Type 2 history, so old facts end up pointing at the current dimension version.