Dimensional Modeling

Dimensional Models are for Users

OLTP != OLAP

ProductSubcategory ( ProductCategory (Pro

ProductModel (Produ Product (Production) ProductInventory (Pr

PurchaseOrderDetail

SalesPerson (Sales) PurchaseOrderHead

e

Employee (HumanRe
Store (Sales) s
Person (Person)

SalesReason
Vendor (Purchasing)
(Sales)

SalesOrderHeader (S
Customer (Sales)

SalesOrderHeaderSa
l
SalesTerritory (Sales
SalesOrderDetail (Sa
Dimensional Model/Star Schema

Single central fact

Multiple denormalized dimensions

DimPromotion

DimProduct DimEmployee

FactResellerSales

DimTime DimReseller
The Four Steps

Choose the Business Process

Declare the Grain

Identify the Dimensions

Identify the Facts

 1) Choose the
Business
Process
 A business process is a major operational process in
an organization.
A business process is a set of related activities. Business
processes are classified by the topics of interest to the business.
Examples of business processes are customers, profit, sales,
organizations, and products.
Follow the Value Chain
(In Business Process)

Manufacture Inventory Order Delivery Sale

Common dimensions
Product
Item
Date
 Enterprise Business Architecture

Sales Inventory Orders Delivery Finance

Time     
Employee  
Customer   
Product  
Location  
Promotion 
Store   

Time is imp for all fileds

Employee receives orders and corresponding payments
Product is saled to Customer, delivered to customer and bill is collected
2) Declare the Grain
 The grain of a fact table defines the level of detail
that is stored, and which dimensions are included make
up this grain. 
Grain of Fact table  =  the meaning of one fact  table
row. Determines the maximum level
of detail of the warehouse
Fundamental Grains

Transaction (or line item)

Periodic snapshot e.g. Bank balance
Accumulating snapshot e.g. Order state
3) Identify the

Dimensions
High Level Dimensional Model
Sales Reason Promotion Order Date
Start Date
Reason Type Discount Yea
Reason Percent r Month
C Day
ategory Is Weekend

Sales Territory Internet Ship Date

Country

State
Sales Is Weekend Day Month Year

Is Weekend
Product Name Day
Colour Month
Category Year
SKU Gender
City
Product Full Name Delivery Date
Customer ID

Customer
Attribute Keys and Names

All attributes have both a key and a name

Keys must be unique
Cust ID Full Name Marital City Country
MJ0001 Mary Jones S London UK

Attribute Key Name

Customer ID Cust ID Cust ID
Full Name Cust ID Full Name
Marital Status Marital Marital
City City City
Country Country Country
Attribute Relations
and Hierarchies
Country

Country
State Marital

City Gender Gender

State
Customer Customer Customer

City Gender Marital Age

Customer

Attributes Hierarchies
Slowly Changing Dimensions

Change happens!
 Attributes of a dimension that would undergo
changes over time. It depends on the business
requirement whether particular attribute history of
changes should be preserved in the data warehouse.
 This is called a slowly changing attribute and a
dimension containing such an attribute is called a
slowly changing dimension.
Type I: Overwrite the attribute

Cust Key Cust ID Full Name Marital City Country

1 MJ0001 Mary Jones S London UK

Cust Key Sales Amount

1 £400

In case of change of marital status

Type I: Overwrite the attribute

Cust Key Cust ID Full Name Marital City Country

1 MJ0001 Mary Smith M London UK

Cust Key Sales Amount

1 £400
Type I: Overwrite the attribute

Cust Key Cust ID Full Name Marital City Country

1 MJ0001 Mary Smith M London UK

Cust Key Sales Amount

1 £400

1 £200

1 £150

In case of More sales

Type II: Add a new dimension row

Cust Key Cust ID Full Name Marital City Country Valid From Valid To

1 MJ0001 Mary Jones S London UK 14/12/2000 NULL

Cust Key Sales Amount

1 £400
Type II: Add a new dimension row

Cust Key Cust ID Full Name Marital City Country Valid From Valid To

1 MJ0001 Mary Jones S London UK 14/12/2000 01/02/2009

2 MJ0001 Mary Jones S Leeds UK 01/02/2009 NULL

Cust Key Sales Amount

1 £400
Type II: Add a new dimension row

Cust Key Cust ID Full Name Marital City Country Valid From Valid To

1 MJ0001 Mary Jones S London UK 14/12/2000 01/02/2009

2 MJ0001 Mary Jones S Leeds UK 01/02/2009 NULL

Cust Key Sales Amount

1 £400

2 £200

3 £150
Type III: Add a new attribute

Cust Key Cust ID Full Name Marital City Country

1 MJ0001 Mary Jones S London UK

Cust Key Sales Amount

1 £400

In case of change country

Type III: Add a new attribute

Cust Key Cust ID Full Name Marital City Country Previous Country

1 MJ0001 Mary Jones S London USA UK

Cust Key Sales Amount

1 £400
Type III: Add a new attribute

Cust Key Cust ID Full Name Marital City Country Previous Country

1 MJ0001 Mary Jones S London USA UK

Cust Key Sales Amount

1 £400

1 £200

1 £150
Date and Time

Create your own date dimension

Attribute keys must be unique
“January” vs. “January 2009”
Time of day – do you really need it?
Time zones – do you really need them?
Pre-calculate durations into fact rows

Use calculated surrogate dimension

keys
E.g. Key = 10000 x year + 100 x month +
day
Role Playing Dimensions
Parent-Child Dimensions

How do you model composite

or hierarchical structures?
E.g. Bill of Materials or
Employee/Manager
Mini-Dimensions

How do you manage high frequency, low

cardinality change in a dimension?
Country

State

City Gender Marital Age Current Location

Employee
Employee Location
Degenerate Dimensions

How do you deal with the high

cardinality dimensions like
“transaction number”?
Junk Dimensions

How do you deal with the low

cardinality, non-related
dimensions like gender or
other booleans?
4) Identify the Facts
What makes a good fact?

Numeric

Additive
Yes you can
have strings
Yes you can
have non-
Fact-less Facts

How do you record an event that has no

measures?

A factless fact table is fact table that does not contain fact. They

contain only dimensional keys and it captures events that
happen only at information level but not included in the
calculations level. just an information about an event that
happen over a period
Semi-additive Measures and
Custom Rollups
Semi-additive in Time
Configurable by Account
Currency and Units of Measure

How do you enable currency conversions?

How do you represent amounts in different
units?
e.g. 400 bikes == 2 pallets == 10 shipping
crates

Put the conversion factors in the fact table

Expose the calculated results in the cube
OR
Use a separate conversion measure group
See Exchange Rates in AdventureWorks
High Level Dimensional Model
Sales Reason Promotion Order Date
Start Date
Reason Type Discount Yea
Reason Percent r Month
C Day
ategory Is Weekend

Sales Territory Internet Ship Date

Country

State
Sales Is Weekend Day Month Year

Is Weekend
Product Name Day
Colour Month
Category Year
SKU Gender
City
Product Full Name Delivery Date
Customer ID

Customer
Sample: Exercise High Level Dimensional Model

Telecom Company
Bank
Bahria University
DW Tips: 10 Essential Dim Mod
Rules
 Store report lables and  Load detailed atomic data into
filter domain values in dimensional structures
dimension tables
 Structure dimensional models
 Dimension tables should around business processes
use
 Ensure every fact table has a
surrogate keys
date dimension table
 Create conformed
 Ensure all facts in a Fact table
dimensions to integrate
are the same grain
data across the enterprise
 Resolve many-to-many
 Continuously balance
relationships in fact tables
requirements and realities to
deliver a DW/BI solution  Resolve many to one
that’s accepted by business relationships in dimension
users and that supports tables
their decision making
DW Tips: Dimensional Modeling

 Dimensional data warehouses  A good way to narrow the

are appropriate for summary scope and manage risk is to
level data only focus on delivering the report
 Dimensional models most often requested
presuppose the business  Dimensional models are fully
questions and therefore de-normalized
are inflexible  Ralph Kimball invented the fact
 Dimensional models are and dimension terminology
departmental
 Brining a new data source into
a dimensional data
warehouse breaks existing
schemas and requires new
fact tables