Dimensional Data Warehousing with MySQL: A Tutorial
This book is primarily for data warehouse developers. However, IT managers and other IT professionals, especially those interested in MIS (management reporting) and DSS (decision support application), will find this book useful too.
Dimensional Data Warehousing with MySQL: A Tutorial
IntroductionPart I - Fundamentals
Chapter 1 - Basic Components
Chapter 2 - Dimension History
Chapter 3 - Measure Additivity
Chapter 4 - Dimensional Queries
Part II - Extract, Transform, and Load
Chapter 5 - Source Extraction
Chapter 6 - Populating the Date Dimension
Chapter 7 - Initial Population
Chapter 8 - Regular Population
Chapter 9 - Regular Population Scheduling
Part III - Growth
Chapter 10 - Adding Columns
Chapter 11 - On-Demand Population
Chapter 12 - Subset Dimensions
Chapter 13 - Dimension Role Playing
Chapter 14 - Snapshots
Chapter 15 - Dimension Hierarchies
Chapter 16 - Multi-Path and Ragged Hierarchies
Chapter 17 - Degenerate Dimensions
Chapter 18 - Junk Dimensions
Chapter 19 - Multi-Star Schemas
Part IV - Advanced Techniques
Chapter 20 - Non-straight Sources
Chapter 21 - Factless Facts
Chapter 22 - Late Arrival Facts
Chapter 23 - Dimension Consolidation
Chapter 24 - Accumulated Measures
Chapter 25 - Band Dimensions
Appendix A - Flat File Data Sources
Chapters Overview
This book consists of 25 chapters and one appendix. The chapters are organized into four parts. Part I covers data warehousing basics. Part II explains the moving of data from the source to the data warehouse database. Part III talks about the techniques to handle growth. Part IV deals with some advanced dimensional techniques. The following section provides an overview of each chapter.
Part I: Fundamentals
Part I, covering the fundamentals of dimensional data warehouse, has four chapters.
Chapter 1, “Basic Components” introduces the star schema (a database schema that has a fact table surrounded by dimension tables) and explains the basic components of the schema.
Chapter 2, “Dimension History” discusses the use of surrogate keys for maintaining dimension history.
Chapter 3, “Measure Additivity” covers one of the most fundamental characteristics of a dimensional data warehouse, namely the additivity of measurement stored in the data warehouse fact tables.
Chapter 4, “Dimensional Queries” introduces a type of SQL query that is most suitably applied to a star schema. A dimensional query is a way to prove the two most fundamental design points of a dimensional data warehouse: simplicity and performance.
Part II: Extract, Transform, and Load
All five chapters in Part II deal with data population and the fact and dimension tables.
Chapter 5, “Source Extraction” explains the various types of data extraction.
Chapter 6, “Populating the Date Dimension” covers the three most common techniques for populating the date dimension.
Chapter 7 , “Initial Population” and Chapter 8, “Regular Population” deal with the two types of population techniques: initial and regular.
Chapter 9, “Regular Population Scheduling” concludes Part II by providing step-by-step instructions to schedule regular population using Windows Task Manager.
Part III: Growth
Part III presents the various techniques for resolving problems associated with the growth of a successful dimensional data warehouse. There are ten chapters in Part III.
Chapter 10, “Adding Columns” deals with the techniques for adding columns to tables in the existing dimensional data warehouse.
Chapter 11, “On-Demand Population” covers the on-demand population technique.
Chapter 12, “Subset Dimensions” explains the techniques for helping users with subset dimensions.
Chapter 13, “Dimension Role Playing” is about using a dimension more than once in a fact table.
Chapter 14, “Snapshots” helps you deliver fast performance queries for users that need to work out summarized data.
Chapter 15, “Dimension Hierarchies” and Chapter 16, “Multipath and Ragged Hierarchies” are about single and multipath hierarchical techniques, respectively. These techniques help users with grouping and drilling analysis.
Chapter 17, “Degenerate Dimensions” shows you how to reduce the complexity of a data warehouse schema by applying the dimension degeneration technique.
Chapter 18, “Junk Dimensions” is about the junk dimension, a technique for selecting seemingly unrelated analytical pieces of data often required by users and organizing them dimensionally.
Chapter 19, “Multi-Star Schemas” shows you how to add more starts to your schema.
Part IV: Advanced Techniques
There are six chapters in this part.
Chapter 20, “Non-Straight Sources” explains how to deal with data sources whose structures do not map directly to the target tables in the data warehouse.
Chapter 21, “Factless Facts” helps you build an analytical aid, a factless fact table, for your users on data that does not have measure from its source.
Chapter 22, “Late Arrival Facts” covers a technique that you use when source data, particularly facts, does not come all together at its scheduled population time.
Chapter 23, “External Data Sources and Dimension Consolidation” covers two topics: handling external data sources and the technique for consolidating scattered attributes in multiple dimensions into one dimension.
Chapter 24, “Accumulated Measures” discusses two related topics: computed measures and non-additivity of the accumulated measures.
Chapter 25, “Band Dimensions” explains a technique that helps users with their needs to analyze data on continuously valued attributes.
Appendix
Appendix A, “Flat File Data Sources” presents instructions on how to use the flat file data sources used in the book examples.