Chapter 3

Data Warehousing
Chapter outline
■ The Multidimensional Model Basic Concepts
■ The Star Schema
■ The Constellation and Snowflake Schemas
■ Summary

2
The Multidimensional Model (MM)
● Is specific for DWs
● Models data for Decisional Analyses and Data Mining
purposes
● Examples of Decision Analyses
● Total of quantities sold by product and by month
● Average amount of sold products by month and by region
● Sum of daily sales (Amount & quantity) by products and by
salesman
● Total of quantities sold by product, client and per city
● …
These examples analyze the data of the SALES activity
according to many criteria Month, Region, Salesman,
Client, City…
How this data should be modelled?
3
 The Multidimensional Model (MM)
● The MM considers a Subject (i.e., an activity to analyze)
as a point in a multidimensional space where data is
organized in order to highlight the Subject (called Fact)
and its n (n≥2) analysis criteria or axes of analysis
called Dimensions.

4
The Multidimensional Model (MM)
■ The MM considers a Subject (i.e., an activity to analyze)
as a point in a multidimensional space where data is
organized in order to highlight the Subject (called Fact)
and its n (n≥2) analysis criteria or axes of analysis called
Dimensions.
■.

5
From Tables and Spreadsheets to Data Cubes
■ A data warehouse is based on a
multidimensional data model which views data in
the form of a data cube
■ A data cube, such as sales, allows data to be
modeled and viewed in multiple dimensions
● Dimension tables, such as item (item_name, brand,
type), or time(day, week, month, quarter, year)
● Fact table contains measures (such as dollars_sold)
and keys to each of the related dimension tables

6
 Multidimensional Data
■ Sales volume as a function of product, month, and region

Dimensions: Product, Location, Time

Hierarchical summarization paths
Regio
n Industry Region Year

Category Country Quarter

Product Product City Month Week

Office Day

Month
7
A Sample Data Cube

Total annual sales

Date of TV in U.S.A.
1Qtr 2Qtr 3Qtr 4Qtr sum
Product TV
PC U.S.A
VCR
sum
Canada
Country
Mexico

sum

8
Data Warehouse & Data Marts

9
Multi-Dimensional Data
■ Measures - numerical data being tracked
■ Dimensions - business parameters that define a
transaction
■ Example: Analyst may want to view sales data
(measure) by region, by time, and by product
(dimensions)
■ Dimensional modeling is a technique for structuring
data around the business concepts
■ ER models describe “entities” and “relationships”
■ Dimensional models describe “measures” and
“dimensions”

10
 The Multi-Dimensional Model
“Sales by product line over the past six months”
“Sales by store between 1990 and 1995”

Store Info Key columns joining fact table

to dimension tables Numerical Measures

Prod Code Time Code Store Code Sales Qty

Fact table for

Product Info
measures

Dimension tables Time Info

...

11
Dimensional Modeling

■ Dimensions are organized into hierarchies

● E.g., Time dimension: days weeks quarters
● E.g., Product dimension: product product line
brand
■ Dimensions have attributes

12
Dimension Hierarchies
Store Dimension Product Dimension

Total Total

Region Manufacturer

District Brand

Stores Products

13
ROLAP: Dimensional Modeling Using
Relational DBMS
■ Special schema design: star, snowflake
■ Special indexes: multi-table join(joining large fact
tables with dimension tables)
■ Special tuning: maximize query throughput:
Focus is on fast analysis of large datasets (aggregate
queries, grouping, filtering).
■ Proven technology (relational model, DBMS), tend to
outperform specialized MDDB especially on large
data sets:
)Built on the relational model and standard DBMS
technologies(

14
MOLAP: Dimensional Modeling Using the
Multi Dimensional Model
■ MDDB: a special-purpose data model
■ Facts stored in multi-dimensional arrays
■ Dimensions used to index array
■ Sometimes on top of relational DB

15
 MOLAP: Dimensional Modeling Using the
Multi Dimensional Model
■ Basic Concepts
The MM has two main concepts
● Fact & Dimension
These concepts are useful for designing Multidimensional
schemas as Star schema, Constellation schema, ....

● Golfarelli suggested a simple and expressive

graphical notation (called DFM) for these concepts.
● The DFM enables the designer representing a
Multidimensional Schema as a diagram, easy to
understand by non IT-persons (as is the ER diagram in IS).

16
 The Multidimensional Model: DFM notation
■ Goal of the Dimensional Fact Model (DFM) notation:
● Bring effective support to the Conceptual Design phase
● Create an environment in which user queries can be intuitively
expressed
● Facilitate communication between designers & D-Makers in order
to formalize requirement specifications
● Enable early testing & verification of users' requirements
● Build a stable platform for Logical Design
● Provide clear & expressive design documentation.
These characteristics make the DFM a good candidate for use in real
DW applications.

17
The Multidimensional Model: Basic Concepts
detailed

A fact is a concept relevant to Decision-Making processes, it

typically models a set of events taking place within a
company.

18
 The Multidimensional Model: Basic Concepts
detailed
■ Measure
■ Definition

A Measure is a fact attribute. It is an indicator of the

business activity the fact describes, it is often numeric to be
summarizable (aggregated using Sum, Avg, Min, Max…).

■ Examples of measures
● SALES: Qty sold, Amount of sale,…
● RESULTS of students: Grade, Appreciation

19
 The MM: Basic Concepts detailed

■ Graphical Notation (DFM)

FACT_NAME

Measure1
Measure2
■ Examples Measuren

RESULTS SALES
Grade Qty
Measures Amount
(or indicators)

■ Measure Granularity:
The fact’s Measures are recorded (and later aggregated) according to
axes called Dimensions.

20
The MM: Basic Concepts detailed

■ Dimension
■ Definition
A Dimension is an Axis for analyzing the fact’s measures. It
■ isGraphical
composed of n (n≥1) attributes.
Notation

DIM_NAME
Attribute1
Attribute2
…
Attribute n
■ Examples
● In the Commerce: PRODUCTS, CLIENTS, TIME…
● in the Academia: STUDENT, COURSE, SEMESTER…

21
 The MM: Basic Concepts detailed
PRODUCTS
■ Star Schema: First draft
TIME
ID-P
Name ID-T
■ Advantages Unit-Price Month-No
● Category Month-Name
Simple diagram
Sub-Categ Year
● Subject is highlighted
● Axes of analyses are highlighted SALES
● Easy for use by D-Makers (Not Normalized)
Qty
Amount CLIENTS
ID-C
Fname
■ Disadvantages Lname
● Dimensions do not explicit the City
Country
analyses axes neither their levels

Need to improve the diagram by introducing the Hierarchy concept

22
The MM: Basic Concepts detailed
■ Definition
A Hierarchy is a tree whose nodes are dimensional attributes
and whose arcs model One-to-many associations between
pairs of attributes (parameters).

■ Example 1: Hierarchy of the PRODUCT dimension

Product-ID Sub-Category Category : H_Category
● A Hierarchy semantically organizes a subset of parameters of
a dimension from the finest (e.g. Product-ID) to the highest
granularity (e.g., Category).

■ Question: Organize the TIME dimension attributes (Month, Year,

Semester, Quarter) into a hierarchy
……………. ……………… ……………… ……..……: H_Year
 The MM: Basic Concepts detailed
■ Graphical Notation of the
hierarchy ■ A hierarchy for the PRODUCTS
■ A hierarchy for the TIME Dimension
Dimension

Hierarchy Parameters (levels)

Year
Category
Month-Name
Unit-Price
Month-No Sub-Categ
ID-T Name
ID-P
TIME
PRODUCTS

The length of a hierarchy is the number of its parameters

24
 The MM: Basic Concepts detailed
■ Hierarchy Instance
H_CATEGORY
hierarchy

Category
Unit-Price
Sub-Categ
Name
ID-P
PRODUCTS
■ This H_CATEGORY hierarchy instance has:
● ………….. Products
● ……......... Sub-Categories
● ………….. Categories
 The Keystone Star Schema
■ A complete star schema (DFM Notation)
Category
Unit-Price Year
Sub-Categ Month-Name
Name Month-No
ID-P
PRODUCTS ID-T
TIME
SALES

Qty
Amount
FName
LName
CLIENTS
ID-C City Country

26
 The Keystone Star Schema
■ Star Schema
Data Cube = Schema + Content
Year
Category Month-Name
SALES Unit-Price
Sub-Categ Month-No
PName
[Link] ID-T
ID-P
TIME
PRODUCTS
10 12 9
2001 230 200 350
10 20 14 SALES
2000 250 430 270
C3 Qty
CLIENTS
8 35 12
1999 180 500 250 C2 Amount ID-C
C1 FName
France [Link] City
Libanon Italy LName
Country
[Link]

27
 The Keystone Star Schema

Note that in a star schema:

■ The Fact contains "measures" (usually numeric) to be
aggregated through Dimensions.
■ All dimensions are directly connected to the Fact.
■ There is no direct link between any two …… dimensions
■ A Dimension has attributes organized into levels for
aggregating the fact’s measures.
■ The relationships between any two parameters of a same
hierarchy, in the direction from the Dimension to the Fact,
is …. One-To-Many.

28
 Example
The fact scheme SALE, shown in Figure 1, describes the
sales in a chain store.

In the DFM, a fact scheme is structured as a quasi-tree whose

root is a fact. A fact is represented by a box which reports the
fact name and, typically, one or more measures.

In the sale scheme, quantity sold, revenue and no. of customers

are measures.

Dimension attributes are represented by circles. Each dimension

attribute directly attached to the fact is a dimension.

The dimension pattern of the sale scheme is {date,

product, store, promotion}.

Non-dimension attributes are always terminal within the quasi-

tree, and are represented by lines (for instance, address).

Subtrees rooted in dimensions are hierarchies. The arc

connecting
two attributes represents a -to-one relationship between them (for
instance, there is a many-to-one relationship between city and
county);

Every directed path within one hierarchy necessarily represents a

-to-one relationship between the starting and the ending
attributes.
29
 Example
Consider for instance the hierarchy on
dimension store:
states are partitioned into counties and sale
districts, and no relationship exists between
them; nevertheless, a store belongs to the same
state whichever of the two paths is followed (i.e.,
store determines state).
Consider attribute city on the product
dimension, which represents the city
where a brand is manufactured. In this
case the two city attributes have
different semantics and must be
represented separately; in fact, a
product manufactured in a city can be
sold in stores of other cities.

Optional relationships between pairs

of attributes are represented by
marking with a dash the
corresponding arc. For instance,
attribute diet takes a value only for
food products; for the other
30
products, it will take a conventional
The Star Schema Design Steps
■ Define the Schema Structure
● Identify the Fact
● Identify the Dimensions
■ Define completely the Fact: Identify the Measures
(Name, Description, Extraction formula…)
■ Define each Dimension completely :
● Identify the Parameters (name, type…)
● Specify the Hierarchies
■ A good knowledge of the domain is required
(i.e., geography…)
■ Analyze values from the source (Product
Category, Sub-Categories)

31
 Star Schema Example - 1

In the following Star

Schema example, the
fact table is at the
center which contains
keys to every
dimension table like
Dealer_ID, Model ID,
Date_ID, Product_ID,
Branch_ID & other
attributes like Units
sold and revenue.

32
Characteristics of star schema
■ Every dimension in a star schema is represented with the only one-
dimension table.
■ The dimension table should contain the set of attributes.
■ The dimension table is joined to the fact table using a foreign key
■ The dimension table are not joined to each other
■ Fact table would contain key and measure
■ The Star schema is easy to understand and provides optimal disk
usage.
■ The dimension tables are not normalized. For instance, in the above
figure, Country_ID does not have Country lookup table as an OLTP
design would have.
■ The schema is widely supported by BI Tools

33
Advantages of star schema

34
Star Schema Example

35
 Exercise
■ For the Purchase Fact
Identify dimensions
Identify measures
■ For each dimension
● Identify attributes
● Build hierarchies
● Prepare a star schema according to the DFM notation
● Using schema obtained in 1), write some analytical requirements
as English sentences.
● Give the corresponding Data cube (Hypercube).

36
 Exercise 1
Course Evaluation.
A university wants to analyze the results of its students and the quality of its courses over time. For this
purpose they want to create a data warehouse that will store the results of all students.

A student is registered for a program which consists of several courses taught by one or more
lecturers. Every course has a course code, a name, the number of lecturing hours and the number of
hours for exercises and lab sessions.

A lecturer belongs to a department and a department to a faculty.

A student can take one or more exams for a course. In case of multiple exam trials, all results should
be stored.

For a student, his or her name, student identifier, gender, and date of birth are stored.

Furthermore, every course offering is evaluated (anonymously) by the students.

This evaluation results in three scores: one for course delivery, one for course content and one for
overall appreciation. Based on the data in the data warehouse it should be possible to evaluate the
exam results by student, by program, by academic year, by course, by department, etc. Furthermore
courses will be evaluated on the basis of their evaluations by year, by lecturer, by program, etc.

Prepare a star schema according to the DFM notation

37
38
 Data Modeling for Data Warehouses
■ Example of Two- Dimensional vs. Multi-
Dimensional
Snowflake Schema

40
 The Snowflake Schema
■ Definition
A Snowflake is an extension of the Star schema, where
each dimension is normalized and therefore splits into
multiple tables each representing a level in the
dimensional hierarchy.

41
 The Snowflake Schema

■ Example YEAR
ID-Year
MONTH
Year
Month-No
ID-Year#
CATEGORY TIME
ID-Ca ID-T
SUB_CATEGORY SALES
Category Month-No#
ID-SCa ID-P#
Sub-Categ PRODUCTS ID-T#
ID-Ca# ID-P ID-C#
Name Qty CLIENT
ID-SCa# Amount ID-C
Fname CITY
Lname ID-CY COUNTRY
Id-CY# City
ID-CT# ID-CT
Country

42
Snowflake Schema Example - 1
■ Refer back Star Schema Example 1

43
Characteristics of snowflake schema

■ The main benefit of the snowflake schema it uses smaller disk space.
■ Easier to implement a dimension is added to the Schema
■ Due to multiple tables query performance is reduced
■ The primary challenge that you will face while using the snowflake
Schema is that you need to perform more maintenance efforts
because of the more lookup tables.

44
Star Schema Vs Snowflake Schema:
Key Differences

45
Snowflake Schema Example - 2
■ Refer back Star Schema Example 2

46
The Snowflake Schema

Advantages:
+ Highlights the analyses levels
+ No redundancy.
Disadvantages:
- Multiple Joins.

47
 Multi-dimensional Schemas
■ Two common multi-dimensional schemas are
■ Star schema:
■ Consists of a fact table with a single table for
each dimension
■ Snowflake Schema:
■ It is a variation of star schema, in which the
dimensional tables from a star schema are
organized into a hierarchy by normalizing them.
Multi-dimensional Schemas
■ Star schema:
■Consists of a fact table with a single table for each
dimension.
Multi-dimensional Schemas
■ Snowflake Schema:
■It is a variation of star schema, in which the dimensional tables
from a star schema are organized into a hierarchy by
normalizing them.
The Constellation

51
 The Constellation Schema
■ Definition
The constellation schema is a group of star schemas
having n (n≥ 2) shared dimensions.

Benefits
● Bring together two related facts; this facilitates the analysis and
interpretation of one fact referring to the other.
● Shared dimensions are stored once: Less ETL efforts and time.
Give trivial examples of shared dimensions
- ………………………
- ………………………

52
 Constellation Schema Example - 1
■ Refer back Star Schema Example 1

There are two facts

table
● Revenue
● Product.
53
Characteristics of constellation schema
■ The dimensions in this schema are separated into separate
dimensions based on the various levels of hierarchy.
■ For example, if geography has four levels of hierarchy like region,
country, state, and city then Galaxy schema should have four
dimensions.
■ Moreover, it is possible to build this type of schema by splitting the one-
star schema into more Star schemes.
■ The dimensions are large in this schema which is needed to build
based on the levels of hierarchy.
■ This schema is helpful for aggregating fact tables for better
understanding.

54
Difference between star schema and
constellation schema

55
 The Constellation Schema
■ Example
Propose a Two-fact Constellation Multidimensional
Schema for the commercial domain.

56
 The Constellation Schema
■ Example
Propose a Two-fact Constellation Multidimensional
Schema for the commercial domain.

The schema contains a fact table for

sales that includes keys to each of the
four dimensions, along with two
measures: Rupee_sold and units_sold.

The shipping table has five dimensions,

or keys: item_key, time_key, shipper_key,
from_location, and to_location, and two
measures: Rupee_cost and
units_shipped.

57
Summary

■ The MM models decisional data while highlighting the

business activity to analyze, and its criteria of analysis
■ The business activity is …called fact, its criteria of
analysis are …dimensions
■ A fact in composed of attributes called …Indicators or …
Measures
■ A Measure quantitatively describes (measures) the …
activity
■ A dimension is an axis according to which measures are
…recorded and then …analyzed

58
Exercise
Each time a student registers for a course, the following
data is recorded:
■ Enrollment ID
■ Student name and ID
■ Course name and code
■ Instructor name
■ Academic program name
■ Department name
■ Credit hours for the course
■ Final grade for the student
■ Date of enrollment (day – month – year)
■ Semester
59
Practical Tips
■ Dimensions and their Fact must have the same granularity: Imagine
that the Fact contains information recorded hourly and the Time
dimension is in minutes, it will not be possible to link the instances of
the Time dimension with the fact instances (multi determination).
■ Each instance of the Fact must be associated with an instance in each
dimension: otherwise, there would be a loss of information or incorrect
analysis.
■ There are never relationships between dimensions. It would be too
complicated to manage! Indeed, the diagram should be easy to
understand and exploit by non-IT persons.

60