Dimensional Modelling 101

1
How to build your ownSuper Model
Dimensional Modelling for Analysis Services
Darren GosbellPrincipal Consultant - James
Monroehttp//geekswithblogs.net/darrengosbell
2
Agenda

• Why build a Dimensional Model?
• What is a Dimensional Model?
• Overview of some modelling techniques.
• What functionality does Analysis Services provide
  to help us?

3
Further Reading

• The Data Warehouse Toolkit by Ralph Kimball
  Margy Ross
• The Data Warehouse Lifecycle Toolkit by Ralph
  Kimball Margy Ross

4
Why Build a Dimensional Model
OLTP System Dimensional Model
Process Oriented Subject Oriented
Transactional Aggregate
Current Historic
5
What is a Dimensional Model?

• A De-normalized database.
• Designed for ease of querying, not for
  transactional updates.
• Built to support aggregate queries
• Modelled around business subject areas.

6
Facts Dimensions

• There are two main types of objects in a
  dimensional model
• Facts are quantitative measures that we wish to
  analyse and report on.
• Dimensions contain textual descriptors of the
  business. They provide context for the facts.

7
A Transactional Database
Countries
CountryID
Description

Addresses
AddressID
StateID
Street
States
StateID
CountryID
Desc
Customers
CustomerID
AddressID
Name
OrderHeader
OrderHeaderID
CustomerID
OrderDate
FreightAmount
Products
ProductID
Description
Size
OrderDetails
OrderHeaderID
ProductID
Amount
8
A Dimensional Model
Customers
CustomerID
Name
Street
State
Country
Time
TimeID
Date
Month
Quarter
Year
FactSales
CustomerID
ProductID
TimeID
SalesAmount
Products
ProductID
Description
Size
Subcategory
Category
9
Star Schema
10
Snowflake Schema
11
Building a Model - Facts

• You have to talk to the business.
• Identify Facts by looking for quantitative values
  that are reported.
• Make sure the granularity is right.

12
Building a Model - Dimensions

• Identify Dimensions by listening for by words.
• Look for related attributes that should be part
  of a single dimension.
• Pay attention to how Dimensions change over
  time and in relation to each other.

13
Slowly Changing Dimensions -Handling Changes
over time
14
If you dont consider changes over time
yourmodel will start out like this
15
but ending up like this!
16
Type 1 Slowly Changing Dimension

• The simplest form
• Only updates existing records
• Overwrites history

17
Type 1 Slowly Changing Dimension
CustomerID Code Name State Gender
1 K001 Miranda Kerr NSW F
CustomerID Code Name State Gender
1 K001 Miranda Kerr VIC F
18
Type 2 Slowly Changing Dimension

• Allows the recording of changes of state over
  time
• Generates a new record each time the state
  changes
• Usually requires the use of effective dates when
  joining to facts.

19
Type 2 Slowly Changing Dimension
CustomerID Code Name State Gender Start End
1 K001 Miranda Kerr NSW F 1/1/09 ltNULLgt
CustomerID Code Name State Gender Start End
1 K001 Miranda Kerr NSW F 1/1/09 23/2/09
2 K001 Miranda Kerr VIC F 24/2/09 ltNULLgt
23/2/09
20
Type 3 Slowly Changing Dimension

• De-normalized change tracking
• Only keeps a limited history
• Stores changes in separate columns

21
Type 3 Slowly Changing Dimension
CustomerID Code Name Current State Gender Prev State
1 K001 Miranda Kerr F ltNULLgt
NSW
VIC
22
Relationships between facts
and dimensions
23
Regular Relationships

• Most Common relationship
• Works like an inner join between the fact and
  dimension

24
DEMO

• Regular Relationships

25
Many to Many Relationships

• Allows for the situation where you want to
  associate more than one member from a dimension
  with a single fact.

26
Scenario

• Bank Account Transactions - each one has an
  Account - Accounts have one or more Customers
  - Each Customer has one or more Accounts

27
Demo

• Many-to-Many Relationships

28
Bank Accounts
Account 1
1,010
Account 2
2,010
Person Account Amount
Albert 1 1,010
Albert 2 2,010
Betty 2 2,010
TOTAL 5,030
3,020
29
Bank Accounts

• The relational schema

30
Referenced Relationships

• Joins a dimension to a fact table through another
  intermediate dimension

31
Demo

• Reference Relationships

32
Reference Relationships
SELECT Measures.Amount ON Columns
Geography.City.1 ON ROWS FROM Balances
Customer
CustomerID
FullName
CityID
33
Materialized Reference Relationships
CustomerID
FullName
CityID
100
Albert
1
TimeID
CustomerID
Amount
200801
100
1000
34
Fact Relationships

• Used when a table plays both the role of a
  dimension and a fact.
• Sometimes also known as a degenerate dimension.

35
DEMO

• Fact Relationships

36
No Relationship

• Used for controlling calculations when you want
  to influence the context of the calculation
  without changing the context of the data.

37
DEMO

• No Relationship

38
Key Take Aways

• Why to build a dimensional model.
• What makes up a dimensional model.
• How implement various modelling techniques in
  Analysis Services (2005 2008).

39
Thank You

• Darren Gosbell
• http//geekswithblogs.net/darrengosbell