My first Publication Agile-Data-Warehouse-Design-eBook | Page 12

Contents Tracking History: Type 2 Slowly Changing Dimensions........................................................ 147 ! Current Values or Historical Values? Why Not Both? ........................................................... 148 ! Updating the Dimensions ......................................................................................................... 149 ! Adding Surrogate Keys ......................................................................................................... 149 ! ETL and Audit Attributes ....................................................................................................... 150 ! Time Dimensions ..................................................................................................................... 151 ! Modeling Fact Tables............................................................................................................... 152 ! Replace Event Details with Dimension Foreign Keys ........................................................... 152 ! Modeling Degenerate Dimensions ........................................................................................ 153 ! Modeling Facts...................................................................................................................... 153 ! Drawing Enhanced Star Schema Diagrams............................................................................. 154 ! Create a Separate Diagram for Each Fact Table.................................................................. 154 ! Use a Consistent Star Schema Layout ................................................................................. 155 ! Display BEAMāœ² Short Codes on Star Schemas .................................................................. 155 ! Avoid the Snowflake Schema Anti-pattern............................................................................ 156 ! Do Create Rollup Dimensions............................................................................................... 157 ! C REATING P HYSICAL S CHEMAS ......................................................................................................... 157 ! Choose BI-Friendly Naming Conventions ................................................................................ 158 ! Use Data Domains ................................................................................................................... 158 ! P ROTOTYPING THE DW/BI D ESIGN .................................................................................................... 159 ! T HE D ATA W AREHOUSE M ATRIX ........................................................................................................ 160 ! S UMMARY ........................................................................................................................................ 162 ! PART II: DIMENSIONAL DESIGN PATTERNS .................................................................................. 163 ! CHAPTER 6 WHO AND WHAT: DESIGN PATTERNS FOR PEOPLE AND ORGANIZATIONS, PRODUCTS AND SERVICES ........................................................................................................................................... 165 ! C USTOMER D IMENSIONS ................................................................................................................... 166 ! Mini-Dimension Pattern............................................................................................................ 166 ! Sensible Snowflaking Pattern .................................................................................................. 170 ! Swappable Dimension Patterns ............................................................................................... 172 ! Customer Relationships: Embedded Whos ............................................................................. 173 ! Recursive Relationship ......................................................................................................... 174 ! Variable-Depth Hierarchies ................................................................................................... 175 ! Hierarchy Map Pattern ............................................................................................................. 176 ! Hierarchy Maps and Type 2 Slowly Changing Dimensions .................................................. 179 ! Using a Hierarchy Map.......................................................................................................... 179 ! Displaying a Hierarchy .......................................................................................................... 180 ! Hierarchy Sequence.............................................................................................................. 181 ! Drilling Down on Hierarchy Maps.......................................................................................... 182 ! Querying Multiple Parents..................................................................................................... 182 ! Building Hierarchy Maps ....................................................................................................... 183 ! Tracking History for Variable-Depth Hierarchies................................................................... 183 ! Historical Value Recursive Keys ........................................................................................... 184 ! The Recursive Key Ripple Effect .......................................................................................... 184 ! Ripple Effect Benefits............................................................................................................ 185 ! Ripple Effect Problems.......................................................................................................... 185 ! E MPLOYEE D IMENSIONS .................................................................................................................... 186 ! Hybrid SCD View Pattern......................................................................................................... 186 ! XIII