Transcript No Slide Title

ACCTG 6910
Building Enterprise &
Business Intelligence Systems
(e.bis)
Dimensional Modeling VI
Olivia R. Liu Sheng, Ph.D.
Emma Eccles Jones Presidential Chair of Business
1
Outline
•
•
•
•
Factless Facts (Ch. 2, 12, 15)
Snapshot Facts (Ch. 3)
Slowly Changing Dimensions (Ch. 4)
M-to-M Relationships –
Multi-valued Dimensions (Ch. 9, 13)
2
Factless Facts:
Measuring Occurrences of
Relationships or Events
Faculty
Student
1
m
m
Course
(Ternary) Relationship: One or many students take one or many
courses from one teacher
3
Measure: # of times the whole or a partial relationship occurs
Factless Facts
No attributes other than the DW keys of dimension tables
are in the fact table
Faculty
Student
Dimension
Dimension
Enrollment Fact
Faculty Key
Student Key
Course Key
Time Key
Course
Dimension
Time Dimension
4
Factless Facts
Classroom
1
Faculty
1
m
Semester
m
Course
Relationship: a teacher teaches in a classroom
1 or many courses over one or many semesters
5
Measure: # of times the whole or a partial relationship occurs
Factless Facts
Faculty
Dimension
Room Dimension
Room
Assignment Fact
Room Key
Faculty Key
Course Key
Course
Dimension
Time Key
Time Dimension
6
Snapshot Facts:
The Simplest Inventory Schema
Inventory Fact
Time Dimension
time_key
Product Dimension
product_key
Warehouse Dimension
warehouse_key
quantity_on_hand
Accumulative measure
7
Slowly Changing Dimension
CUSTOMER
TIME
#
*
*
*
*
*
*
*
*
*
*
TIME_KEY
OR DER DATE
DAY_OF_WEEK
DAY_NUMBER _IN _MON TH
DAY_NUMBER _IN _YEAR
WEEK_NU MBER
MONTH
QU ARTER
HOLID AY_FLAG
FISCAL_YEAR
FISCAL_QU ARTER
referenced by
referenced by
SALES
reference
#
#
#
*
*
*
#
*
*
*
*
CU STOMER_KEY
CID
CN AME
STATE
CITY
reference
TIME_KEY
PR OD UC T_KEY
CU STOMER_KEY
PRICE
QU ANTITY
SALES
reference
referenced by
PRODUCT
#
*
*
*
PR OD UC T_KEY
PID
PNAME
PCN AME
8
Slowly Changing Dimension
Values of attributes in dimension tables may evolve over
time. For example, customers moved from one city to
another city.

CID
CName
State
City
101
Jon
Arizona
Tucson
102
Tom
Arizona
Tucson
103
Mark
Arizona
Phoenix
Salt Lake City
Utah
9
Tom moved from Tucson to Salt Lake
Slowly Changing Dimension
There are three ways to handle slowly changing
dimension.
 Method 1: Overwrite old values with new values

CID
CName
State
City
101
Jon
Arizona
Tucson
102
Tom
Arizona
Tucson
103
Mark
Arizona
Phoenix
CName
State
City
101
Jon
Arizona
Tucson
102
Tom
Utah
Salt Lake city
103
Mark
Arizona
Phoenix
CID
10
Slowly Changing Dimension
Drawbacks of method 1:
Historical information is totally lost.
We will never know that customer 102 lived in
Tucson before.
Moreover, when listing sales by city, all the
sales of customer 102 will be counted as part
of Salt Lake City sales, although 102 was in
Tucson before.
11
Slowly Changing Dimension
Method 2: Add a new attribute to record current value
of the changing attribute.

CID
CName
State
City
101
Jon
Arizona
Tucson
102
Tom
Arizona
Tucson
103
Mark
Arizona
Phoenix
CID
CName
State
Original City
Current City
Current
State
101
Jon
Arizona
Tucson
Tucson
Arizona
102
Tom
Arizona
Tucson
Salt Lake City
Utah
103
Mark
Arizona
Phoenix
Phoenix
Arizona
12
Slowly Changing Dimension
Drawbacks of method 2:
Only partial Historical information (original &
current) is kept.
Considering that customer 102 moved from
Tucson to Phoenix then to Salt Lake City, the
customer information of customer 102 only
includes Tucson and Salt Lake City.
13
Slowly Changing Dimension
Method 3: Add a new dimension record whenever
change occurs  keep all the information.
Warehouse
key
Utah
Utah
Salt Lake City
14
Multi-Value Dimension
• Most of the dimension tables have a 1-m relationship
with the fact table
•Product  Sale, Customer  Sale, SalesDate 
Sale…
• What if there is a m-to-m relationship between a
dimension and a fact?
15
Multi-Value Dimension
Patient
Date
Physician
Medication 1 Medication 2
1
12/11/00
James
Aspirin
Tylenol
2
1/2/01
Kathy
Tylenol
Alomide
1
2/1/01
James
Ativan
Lodine
The above table is a visit table from a clinic.
We want a factless fact table and
preserve medication information
16
Multi-Value Dimension
Time
Physician
VISIT
Patient
m to m !!
Med.
A physician may prescribe 1 or many medications at a patient visit
A physician may prescribe the same medication at different visits
17
Multi-Value Dimension
If we have a “grouping” table for medication….
GroupID
Medication
1
Aspirin
1
Tylenol
2
Tylenol
2
Alomide
3
Lodine
3
Ativan
Patient
Date
Physician GroupID
1
12/11/00
James
1
2
1/2/01
Kathy
2
1
2/1/01
James
3
18
Medication Group Bridage is what we call a bridge table
Medication
Group
Medication
1
Dimension
Medication
Aspirin
Tylenol
Alomide
Lodine
Ativan
m
Medication
1
Bridge
GroupID
Medication
1
Aspirin
1
Tylenol
2
Tylenol
2
Alomide
3
Lodine
3
Ativan
m
Group
GroupID
1
2
3
19
Multi-Value Dimension
Medication
Medication
Group
1
m
1
m
Medication Group
Bridge
Physician
VISIT
Patient
Time
20