UUY-DTS 4232 units400 LevelSecond Semester

Data Warehousing and Multidimensional Modeling

B.Sc. Data Science, University of Uyo

Designing the analytical database: dimensional modelling, star and snowflake schemas, slowly changing dimensions, OLAP operations, and the ETL that populates it all.

On Areté, UUY-DTS 423 comes with

  • 2 recommended textbooks
  • an AI tutor that has read this course’s outline and notes
Sign in to study UUY-DTS 423

Free for University of Uyo students — sign in with your email, no password.

Course outline

  1. 01Why analytical and operational databases differ: OLTP vs OLAP workloads
  2. 02Data warehouse architecture: staging, core warehouse, data marts; Inmon vs Kimball approaches
  3. 03Dimensional modelling: facts, dimensions, grain, and the four-step design process
  4. 04Star schema and snowflake schema; when to denormalise
  5. 05Fact table types: transaction, periodic snapshot and accumulating snapshot
  6. 06Dimension design: surrogate keys, hierarchies, conformed dimensions, degenerate dimensions
  7. 07Slowly changing dimensions: Types 0 through 3 and their trade-offs
  8. 08OLAP operations: roll-up, drill-down, slice, dice and pivot; cubes and MOLAP/ROLAP
  9. 09ETL design for the warehouse; incremental loading and change data capture

Recommended textbooks

  • The Data Warehouse Toolkit — Ralph Kimball & Margy Ross

    3rd ed. — the definitive book on dimensional modelling; this course is essentially it

  • Building the Data Warehouse — W.H. Inmon

    4th ed. — the alternative architectural school; know both for the exam

How to pass UUY-DTS 423

  • Kimball's four-step process (choose the business process, declare the grain, identify dimensions, identify facts) is the exam's favourite structured answer — memorise it and apply it to any scenario given
  • Declaring the grain is the step students skip and the step everything depends on. State it as a single sentence: "one row per…"
  • SCD Type 2 (add a new row with validity dates) is the most examined and the most used in practice — be able to draw the before/after table
  • Design a small warehouse for a scenario you know (university results, a shop's sales) — it makes every abstract term concrete

Other 400 Level courses

Lecture notes, practice questions and the AI tutor for UUY-DTS 423 are available once you sign in.

Sign in to Areté