Dimensional Data Modeling
Lecture 4 – Designing Dimensional
Models
The Matrix Method For Design
Assume that we have completed requirements
analysis and now want to start designing our
dimensional model
We have gathered a large number of
information requirements that need to be
translated into facts and dimensions.
Assume also we have done our source data
analysis
2
Matrix Method
Useful to think about a
dimensional models
(book calls them data
marts) as consisting of
a set of related
measures that
represent snapshots
associated with some
business process, for
How does the data mart
example, sales activity: definition we’ve used in
class differ from book
definition?
3
Matrix Approach
Best way to get started with design: build a
matrix that lists all the facts and associated
dimensions, based on the requirements that
have been gathered
At this point, we are not really specific about
the measures associated with the fact. For
example, using the sales activity example, we
would label the fact ‘Sales Activity’ and not
worry about the individual measures (Dollars
Sold, Dollar Cost, etc.
4
Step 1 – list the facts
Start with ‘simple’, or single source facts. Later,
we will consider more complex facts (like the
month end snapshots that combine data from
multiple facts – the ATM monthly usage
example
Example for a retailer:
Sales activity
Shipments
Returns
Vendor Purchase Orders
Customer service transactions
Etc.
5
Step 1 – list the facts
The purpose of the list of facts will be to choose
what gets built and in what order
Typically, we start with facts that are of high
priority for users, but are relatively simple to
construct
Wait until we have a success with first
implementations before tackling more complex
facts
6
Step 2 – list the dimensions
Review the requirements list and identify the
dimensions that stand out
7
Step 3 – create the matrix
Set up the matrix:
The rows are the facts
The columns are the dimensions
Define the intersections
Customer Vendor Ship Method Time Product
Sales
x x x
Returns
x x x
Shipments
x x x x
Customer
Service
x x x
Purchase
Orders
x x x x
8
Designing the Dimensional Model
1. Choose the fact table(s) to be built
2. Declare the grain of each fact table
3. Choose the dimensions
4. Choose the specific measures
9
Fact Design
Date
Day
Pricing
Billing Date
Package Billing Month
Package
Customer
Usage Billing
Customer
Usage Type
Cust. Delivery
Usage Type
Point
Product
Product/Service
10
Fact Design
Default Aggregation Rule
Fact Name Fact Description
Number of Messages Total number of messages that occurred for a given Sum
period.
Minutes of Use Total number of minutes used for a given period. Sum
Base Charge Represents the standard charge for this particular product N/A
or service.
Discount Amount Represents the total amount discounted from the full price Sum
amount.
Surcharge Amount Represents the total surcharge amount that is added to Sum
the total line amount.
Tax Amount Dollar amount of the tax charge allocated to the line item. Sum
Total Line Amount The total charged amount including tax and surcharge.
Invoice # Represents the actual invoice number. N/A
11
Design each Dimension Table
Fiscal Year Calendar Year
Start by designing
the dimensional Fiscal Quarter
Calendar
Quarter
hierarchy(ies)
Calendar
Fiscal Month
Month
Fiscal Week
Calendar
Type of Day
Week
Day of Week Day Holiday
12
Design the Dimension Tables
Attribute Name Attribute Description Sample Values
Day The specific day that an activity took place. 06/04/1998; 06/05/1998
Day of Week The specific name of the day. Monday; Tuesday
Holiday Identifies that this day is a holiday. Easter; Thanksgiving
Type of Day Indicates whether or not this day is a weekday or a Weekend; Weekday
weekend day.
Calendar Week The week ending date, always a Saturday. Note that WE WE 06/06/1998;
denotes week ending. WE 06/13/1998
Calendar Month The calendar month. January,1998; February, 1998
Calendar Quarter The calendar quarter. 1998Q1; 1998Q4
Calendar Year The calendar year. 1998
Fiscal Week The week that represents the corporate calendar. Note that F Week 1 1998;
the F in the data value indicates that this is a fiscal time F Week 46 1998
period.
Fiscal Month The fiscal period comprised of 4 or 5 weeks. Note that the F F January, 1998;
in the data value indicates that this is a fiscal time period. F February, 1998
Fiscal Quarter The grouping of 3 fiscal months. F 1998Q1; F1998Q2
Fiscal Year The grouping of 52 fiscal weeks / 12 fiscal months that F 1998; F 1999
comprise the financial year. 13
