Dimensional Modeling

Advanced Dimensional Modeling Topics
October 23, 2008

The class will describe the most challenging data warehouse design problems the world of data warehousing has faced.

Among these requirements are: handling aggregation, heterogeneous product and transaction types, handling time and history, handling changing dimensions, handling late arriving data, supporting data with different rates of change and stability, supporting large scale database environments such as MPP (massively parallel processing).

Topics include:
· Additional Data warehouse architectures
· Required snowflakes
· Conformed facts and dimensions
· Heterogeneous dimensions and facts
· Changing dimensions and facts
· Mixed changes
· Late arriving data: facts and dimensions
· Fact to fact joins
· Predicate analysis for star joins
· Do all facts have count, amount; are all dimensions without them.
· Factless facts.
· Fact or dimension
· Design for parallel
· Multiple roles
· Use of surrogate keys
· Handling multi-valued dimensions
· Handling complex dimensions, such as hierarchical, ragged, multiple dimensions
· Problems with flattening T1 and T2 dimensions
· Designing aggregates
· Aggregates vs. on-the-fly
· Supporting restatement or aggregates
· Designing for trickle load
· Exercises