Chapter 5 Dimension Tables

1
Chapter 5Dimension Tables the Nouns of the
Data Warehouse
2
Introduction

• Architect must create a data structure that is
  natural to the business strategist
• Warehouse architect structures the data
  dimensionally defines specific relationships
  between objects within business environment
• Dimensionality
• Multidimensional Databases (MDDB)

3
Introduction

• Implementation Model
• Star Schema
• Objects in the analysis are translated to
  dimensions
• Dimensions
• Persons, places, and things that are related to
  the business Nouns of the DW

4
Dimension Table Characteristics

• Business strategist specifies specific instances
  to comprise the analysis
• Specify via analysis-space coordinates
• Select a set of items via attributes of objects
• Analytical Data Elements
• Attributes that can be used to define a set of
  objects for analysis
• Dimension table should primarily contain only
  Analytical Data Elements
• Ex) Cars engine size (O). Cars serial number
  (X)
• Have a finite domain of possible values

5
Dimension Table Characteristics

• Dimension table is primarily composed of
  analytical data elements
• But, nonanalytical data elements may exist in the
  dimension table
• Ex) product name
• Although the number of nonanalytical data
  elements should be kept to a minimum, the
  architect can decide which ones to include

6
Slowly Changing Dimensions

• Warehouse creates a history of activities in the
  fact table nonvolatile.
• Slowly changing dimensions
• All of the objects represented in the dimensions
  of the star schema can easily change with time
• 5 basic solutions for DW to adapt to the
  dimension changes

7
Solution 1

• Simply change the dimension record
• Appropriate when correcting errors
• Permanent changes
• Problem
• Corrupts integrity of the DW
• Both current and past sales to a client will be
  references as purchases of a married woman
• (Assume that the person had a gender change)
• Undetected errors, inaccurate conclusions

8
Solution 2

• Create a new dimension record
• Maintains past history while accurately
  reflecting the current state of the market
• Problem
• Hides valuable market information
• Cannot track how behaviors of a subject changes
  in relation to the changes in a customers
  demographic

9
Solution 3

• Alter the structure of the dimension record to
  contain both new and old values, record the date
  the change took place
• Current and past field, date field
• Problems
• Need to select which of the objects attributes
  are expected to change
• Decide how many times changes are allowed

10
Solution 4

• Create a new record for every change with
  revision number appended to the primary key
• System generated key field represent same
  instance
• Revision number identifies order of change
• Problem
• Complicates the link between dimension tables and
  fact tables

11
Solution 5

• Combine 2 and 4 method. Create a new record with
  a new primary key, and the client ID is carried
  over from the operational system. A flag is
  included to indicate most recent version.
• Optimum but not always available (primary keys
  may be inconsistent or overlap)