UNIT II
Dimensional Modeling & OLAP
Overview
Dimensional Modelling
Star Schema
Snowflake Schema
Fact Constellation Schema
OLAP Operations
What is Dimension Modeling?
Dimensional Modelling (DM) is a data structure technique optimized
for data storage in a Data warehouse. The purpose of dimensional
modelling is to optimize the database for faster retrieval of data. The
concept of Dimensional Modelling was developed by Ralph Kimball
and consists of “fact” and “dimension” tables.
Dimensional modeling gets its name from the business
dimensions we need to incorporate into the logical data model.
It is a logical design technique to structure the business
dimensions and the metrics that are analyzed along these
dimensions.
Using dimensional modeling, measurements and relevant
dimensions must be captured and kept in the data warehouse.
For this, information package diagram can be drawn for the
specific subject.
It enables in packaging the data in a symmetric format which
will help in:
a) High Performance for queries and analysis.
b) Captures critical measures
c) Views along dimensions
d) Intuitive to business users
,Principles of Dimensional Modeling
• Dimensional modeling (DM) is the name of a set of techniques
and concepts used in data warehouse design.
• It is considered to be different from entity-relationship
modeling (ER).
• Dimensional Modeling does not necessarily involve a relational
database.
• The same modeling approach, at the logical level, can be used
for any physical form, such as multidimensional database or
even flat files.
• Dimensional Modeling is a design technique for databases
intended to support end-user queries in a data warehouse.
• It is oriented around understandability and performance.
According to him, although transaction-oriented
Dimensional Modeling
In dimension modeling, there are two types of tables: Dimension
Table and Fact Table
Facts are stored in FACT Tables
Dimensions are stored in DIMENSION tables
Dimension tables contain textual descriptors of business
Fact and dimension tables form a Star Schema
“BIG” fact table in center surrounded by “SMALL” dimension
tables
,Elements of Dimensional Data Model
1. Fact
Facts are the measurements/metrics or facts from your business
process. For a Sale business process, a measurement would be
quarterly sales number
2. Dimension
Dimension provides the context surrounding a business process event.
In simple terms, they give who, what, where of a fact. In the Sales
business process, for the fact quarterly sales number, dimensions
would be
Who – Customer Names
Where – Location
What – Product Name
In other words, a dimension is a window to view information in the
facts.
3. Attributes
The Attributes are the various characteristics of the dimension in
dimensional data modelling.
, In the Location dimension, the attributes can be
State
Country
Zip-code etc.
Attributes are used to search, filter, or classify facts. Dimension
Tables contain Attributes
4. Fact Table
A fact table is a primary table in dimension modelling.
A Fact Table contains
1. Measurements/facts
2. Foreign key to dimension table
5. Dimension Table
A dimension table contains dimensions of a fact.
They are joined to fact table via a foreign key.
Dimension tables are de-normalized tables.
The Dimension Attributes are the various columns in a
dimension table
Dimensions offers descriptive characteristics of the facts with
the help of their attributes
No set limit set for given for number of dimensions
The dimension can also contain one or more hierarchical
relationships.
ER Model v/s Dimension Model
ER diagram is a complex diagram, used to represent multiple
processes. A single ER diagram can be broken down into several
DM diagrams.
In DM, we prefer keeping the tables de-normalized, whereas in
a ER diagram, our main aim is to remove redundancy
ER model is designed to express microscopic relationships
between elements. DM captures the business measures
Dimensional Modeling & OLAP
Overview
Dimensional Modelling
Star Schema
Snowflake Schema
Fact Constellation Schema
OLAP Operations
What is Dimension Modeling?
Dimensional Modelling (DM) is a data structure technique optimized
for data storage in a Data warehouse. The purpose of dimensional
modelling is to optimize the database for faster retrieval of data. The
concept of Dimensional Modelling was developed by Ralph Kimball
and consists of “fact” and “dimension” tables.
Dimensional modeling gets its name from the business
dimensions we need to incorporate into the logical data model.
It is a logical design technique to structure the business
dimensions and the metrics that are analyzed along these
dimensions.
Using dimensional modeling, measurements and relevant
dimensions must be captured and kept in the data warehouse.
For this, information package diagram can be drawn for the
specific subject.
It enables in packaging the data in a symmetric format which
will help in:
a) High Performance for queries and analysis.
b) Captures critical measures
c) Views along dimensions
d) Intuitive to business users
,Principles of Dimensional Modeling
• Dimensional modeling (DM) is the name of a set of techniques
and concepts used in data warehouse design.
• It is considered to be different from entity-relationship
modeling (ER).
• Dimensional Modeling does not necessarily involve a relational
database.
• The same modeling approach, at the logical level, can be used
for any physical form, such as multidimensional database or
even flat files.
• Dimensional Modeling is a design technique for databases
intended to support end-user queries in a data warehouse.
• It is oriented around understandability and performance.
According to him, although transaction-oriented
Dimensional Modeling
In dimension modeling, there are two types of tables: Dimension
Table and Fact Table
Facts are stored in FACT Tables
Dimensions are stored in DIMENSION tables
Dimension tables contain textual descriptors of business
Fact and dimension tables form a Star Schema
“BIG” fact table in center surrounded by “SMALL” dimension
tables
,Elements of Dimensional Data Model
1. Fact
Facts are the measurements/metrics or facts from your business
process. For a Sale business process, a measurement would be
quarterly sales number
2. Dimension
Dimension provides the context surrounding a business process event.
In simple terms, they give who, what, where of a fact. In the Sales
business process, for the fact quarterly sales number, dimensions
would be
Who – Customer Names
Where – Location
What – Product Name
In other words, a dimension is a window to view information in the
facts.
3. Attributes
The Attributes are the various characteristics of the dimension in
dimensional data modelling.
, In the Location dimension, the attributes can be
State
Country
Zip-code etc.
Attributes are used to search, filter, or classify facts. Dimension
Tables contain Attributes
4. Fact Table
A fact table is a primary table in dimension modelling.
A Fact Table contains
1. Measurements/facts
2. Foreign key to dimension table
5. Dimension Table
A dimension table contains dimensions of a fact.
They are joined to fact table via a foreign key.
Dimension tables are de-normalized tables.
The Dimension Attributes are the various columns in a
dimension table
Dimensions offers descriptive characteristics of the facts with
the help of their attributes
No set limit set for given for number of dimensions
The dimension can also contain one or more hierarchical
relationships.
ER Model v/s Dimension Model
ER diagram is a complex diagram, used to represent multiple
processes. A single ER diagram can be broken down into several
DM diagrams.
In DM, we prefer keeping the tables de-normalized, whereas in
a ER diagram, our main aim is to remove redundancy
ER model is designed to express microscopic relationships
between elements. DM captures the business measures
