1 / 14

Dimensional Modeling in Data Warehousing: Concepts and Examples

Understand the fundamental concepts of dimensions, data cubes, and modeling in data warehousing, with practical examples and implications for query formulation. Learn about dimension attributes, density and sparsity in data cubes, and relational modeling in the context of a multidimensional data model.

kcushman
Download Presentation

Dimensional Modeling in Data Warehousing: Concepts and Examples

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 3.2 Basic Concepts of the MDD-Model Def.: A Dimension is a data type (almost always finite), which is used as a component of a composite (multidimensional) key. Def.:Dimension-Members are elements of a dimension. Examples: frequently enumeration types or intervals: Month =( January, February, …, December) Day = [ 1:31] Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  2. Further examples: BMW-engines = {1600, 1800, 2000, 2300, 2800, 3000, 3500, 4000, 5000, 2500D, 3000D} BMW-bodies = {3er, 5er, 7er, 7erL, 3er Kombi, 3er Cabrio, 5er Kombi, 8er, Z3} Note : not all combinations of engines and bodies are built Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  3. Data Cube Def: Data-Cube with m dimensions D1, D2, …, Dm and k fact –types F1, F2,…, Fk is W = {( d1, d2,…, dm) ( f1, f2,…fk)}: di  Di for i = 1, …, m  fj  Fj for j =1, …k  (d1, d2,…, dm) is key} Def.: cell-address = (d1,…, dm)cell-content = (f1,…,fk) Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  4. Dimension Product Cell 35 Facts (Measures) Trekking Bike Time GB Region Data Cube Example 25 Dimension- 30 Members Mountain Bike June D Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  5. Single cube vs. Multiple cubes Note: model as 1 cube with fact (f1,…,fk) or as k cubes Wi with fact fi for i = 1, …, k and all Wi have the same dimensions Choice depends on practical considerations and performance, e.g. (sales#, sales€) Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  6. Consequences for query formulation and execution: 1 cube : select key, sales#, sales€ from W Multiple cubes:select key, sales#, sales€ from W1, W2 where W1.key = W2.key Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  7. Def.:Dimension-Attributes: additional attributes for a detailed description of the dimension members. Examples: • Number of days per month dimension Month = ((January,31), (February, 29), : (April, 30),..., (December,31)) • Gasoline type and number of cylinders of an engine dimension BMW-engine = {(1600, Super,4),..., (2500D, Diesel,5),..., (4000, Regular,8)…} Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  8. Density and Sparsity Def.: Dense data-cube: all combinations of (d1, …, dm) occur.Sparse data-cube is not dense Def.: Note: • logical model assumes dense cubes • physical storage model deals withdense and sparse cubes. Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  9. Example: BMW Sales BMW-engines Months BMW- bodies Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  10. Systematic Sparsity of the MDD-Model: 3er 3erKombi 3erCabrio 5er 5erKombi 7er 7erL 850 Z3 … 1600180020002300280030003500400050002500D3000D Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  11. Relational Modeling Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  12. Relational Model BMW-engines Facts Month cyl fuel B-E B-EB-BM € # Name days Bodies B-B … Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  13. E/R-Model: Star-Schema BMW-Engines BMW-bodies Facts Months  i.e. simple star schema Prof. R. Bayer, DWH, Ch. 3.2, SS2000

  14. E/R-Model: Snowflake-Schema The Snowflake-Schema arises from the Star-Schema by more details plus normalization: Basic body brakes Drive-train Facts BMW-bodies engine trans-mission Months extras This can be continued arbitrarily far Prof. R. Bayer, DWH, Ch. 3.2, SS2000

More Related