Dimension Tables, Exercises of Design

Download Dimension Tables and more Exercises Design in PDF only on Docsity!

Dimension Tables

Based on Chapter 05 Dimension Tables – the Nouns

of the Data Warehouse in

Object-Oriented Data

j

Warehouse Design: Building a Star Schema

by Wm.

Giovinazzo

Star SchemaStar Schema 

Allows the creation of a multidimensional space within arelational database

Dimension TablesDimension Tables 

Dimension tables hold nouns, the objects

Hold attributes that used to define the set of objects for

l

analysis^ 

These are analytical data elements

Dimension tables are not normalized, typically^ 

There are exceptionsThere are exceptions

Analytical Data ElementsAnalytical Data Elements 

analytical data elements arethe dimensions that theanalyst wants to use toform the cubesform the cubes

For example:^ 

Salesperson

p



Time



Product

Analytical Data Elements - Finite D i Ch t i ti Domain Characteristic 

The domain of the attributes should be finite^ 

That is, there should not be uncountably many possibilities forvalues of dimension attributes

The values for attributes are for the most part numeric fields

The values for attributes are for the most part numeric fieldswith a limited number of possible values or text fields with alimited number of possible values^ 

That is, categorical data or nominal data

Thus, free format text fields are not very useful^ 

difficult to group on

Slowly Changing DimensionsSlowly Changing Dimensions



A fact is a fact^ 

Facts are not volatile  Facts are not volatile 

Objects -- represented in the dimension tables -- may change overtitime^ 

Usually the change over time is slow  If it is not slow, then the object may not be suitable for data miningpurposespurposes 

Problem with dimensions that change^ 

H

d ll h ith t l i^ th hi t  How do we allow change without losing the history 

Author suggests 5 ways to handle slowly changing dimensions

2 Create a New Dimension Record 2

. Create a New Dimension Record



Add a new record and keep the older record also^ 

Maintains history  Maintains history  Accurately reflects current state  Problem: may hide valuable information 

When is this a good choice?

h d l^ d h h b d d  When trends related to the changing attribute are not consideredvaluable information 

When is it not a good choice?

Wh h i^ ib ld id l^ bl l^ i f i  Wh en change in attribute could provide valuable relevant information  E.g., how do buying habits change when a person moves from singleto married to married-with-children status

3. Alter the Structure of the DimensionT blTable  Modify the structure if the dimension table so that it can capture the change inthe value of an attribute^  Create current_status and past_status fields within one record plus date ofchange  Problem: must be done for every attribute where slow change is expected andwhere it is something we want to track – may significantly increase table sizewhere it is something we want to track may significantly increase table size  Problem: how many changes are to be captured? Past to current? What if the value changes again? More fields needed.  When is this a good choice?^  Limited number of slowly changing attributes  Slowly changing attributes with few change points to be recorded  When is it not a good choice?^  Many slowly changing attributes to track  Many change points to be recorded y g p

5 Use Record Linking 5

. Use Record Linking.

Create a new record with a new PK

h

b

d

l^

k h

d

d

f

h



another attribute is used to link the records (i.e. identify themas belonging to the same “individual”



E.g., Use clientID as link

When is this a good choice?

When is it not a good choice?

When is it not a good choice?^ 

Sometimes clientID may not be a consistent value due tobringing data in from disparate systems

Time Dimension TableTime Dimension Table Why have a dimension table for time?

y

Analyze trends over time

Location Dimension TableLocation Dimension Table Why have a dimension table for location?

y

Analyze trends by location

Location HierarchyLocation Hierarchy

Other possibilities denoting something aboutlocation^ 

Zipcode



Zipcode



Time zone



Lattitude/longitude

g

Example – Hierarchy Embedded inS l Di i Salesperson Dimension

Dimension Tables

Based on Chapter 05 Dimension Tables – the Nouns

of the Data Warehouse in

Object-Oriented Data

j

Warehouse Design: Building a Star Schema

by Wm.

Giovinazzo