Summary: Data Management [HMH25f] – Axel Temmerman
Data Management
Summary (2024 - 2025)
Chapter 1: Introduction & Motivation...................................................................... 2
Chapter 2: Relational Databases..............................................................................7
Part 1 – Entity Relationship Diagram..................................................................... 7
Part 2 – The Relational Model.............................................................................. 11
Part 3 – ER2RM........................................................................................................ 20
Chapter 3: Data Warehousing.................................................................................25
Part 1 – What’s a Data Warehouse & Star Schema.............................................25
Part 2 – Why use a Star or Snowflake schema?..................................................30
Part 3 – What about changes in dimensions?......................................................44
1. Thinking in Dimensions...............................................................................44
2. Slowly Changing Dimensions: Modelling History........................................46
2.1 A History Table per Attribute................................................................47
2.2 A History Table for each Entity Type....................................................48
2.3 A history table to replace a Dimension Table...................................... 49
Summary of the 3 options:.........................................................................49
3. Rapidly Changing Dimensions....................................................................50
4. Date and TIme in a data warehouse...........................................................52
5. Storing “states” of events: Snapshots vs. State Tables...............................52
DWH Design Conclusions.................................................................................... 54
Chapter 4 - OnLine Analytical Processing (OLAP)........................................................... 55
1. What is OLAP?.............................................................................................................55
2. Types of OLAP............................................................................................................. 55
2.1 Relational OLAP................................................................................................... 56
2.2 Multidimensional OLAP........................................................................................ 57
Dimensions & Measures.......................................................................................57
Slicing................................................................................................................... 57
Dicing....................................................................................................................58
Dimensions & Sub-dimensions.............................................................................58
Roll-up and Drill-down.......................................................................................... 58
Nesting................................................................................................................. 59
2.3 Hybrid OLAP........................................................................................................ 60
3. Applications.................................................................................................................. 60
Pivot Tables................................................................................................................ 60
Pivot Tables in MS Excel............................................................................................ 61
1
, Summary: Data Management [HMH25f] – Axel Temmerman
Chapter 1: Introduction & Motivation
Definition of Data Modeling
● Data modelling is a process used to define and analyse data requirements
needed to support the business processes within the scope of corresponding
information systems in organisations
○ It is the process of creating a data model for the data to be stored in a
database. This data model is a conceptual representation of data
objects.
○ The idea is that you start from a set of business processes and build a
relational data model that will support the execution of these processes
○ A relational database is composed of different tables linked through
relationships
○ Database normalisation is the process of restructuring a relational
database in accordance with a series of so-called normal forms in
order to reduce data redundancy and improve data integrity.
Definition of Business Intelligence (BI)
● Business Intelligence (BI) is an umbrella term that includes architectures,
databases, tools, applications and methodologies.
● Content-free term; means different things to different people
○ Objective: enable interactive access to data, its manipulation (data
warehouse design) and provide business managers and analysts the
ability to conduct analyses (business analytics)
○ (Better) decision making based on the analysis of historical and current
data, situations and performances: transformation of data into
information
● Data Warehouses are relational databases but include denormalized tables.
The goal in DW design is to have the simplest structure possible to enable
handling effective queries.
From data to information
2
, Summary: Data Management [HMH25f] – Axel Temmerman
Database Architecture
From Operational Databases to a Data
Warehouse and its Analysis
The data harvested from operational
systems, ERPs, CRMs and Flat Files
needs to be transformed through an ETL
system in order to be stored in the Data
Warehouse.
This is because the data gathered on the
left side of the graph is not structured in the
same way as the Data Warehouse.
Transforming the data turns it into
information, which you can subsequently
use to make Olap Analysis, Reporting or Data Mining.
BI Architecture: A second view
3
, Summary: Data Management [HMH25f] – Axel Temmerman
Operational Database vs. Data Warehouses
The Operational Database is the database used for real-time transactions; it is aimed
to support the business in its everyday operations (buying, invoicing, logistics…)
- Is tailored for the transactional software system
- Is stored on one place in the Database Management system (e.g. Oracle,
SQL Server, …)
- Follows the relational model, normalized architecture
The Data Warehouse is the database for Business Analytics (in other words the data
source used into BI)
- Separate system
- Is loaded from different operational systems (mainly databases)
- Own architecture (star, snowflake diagram)
Reports, OLAP, Data Mining
- Reports: overview of information
● Summary of data
● Computation of sums and means
● User friendly presented
- OLAP: Online Analytical Processing
● Summary of data and computing of sums
● Original information remains available in OLAP
○ “Roll-up”: aggregation of detailed data
○ “Drill-down”: find out how a total sum or average was computed
(through which original data)
● With sophisticated tools (e.g. a dashboard application)
● A pivot table is an example of OLAP
- Data Mining
● Objective: mining the data to (hidden) patterns, trends to find out
● Mining can be used through 2 manners:
○ With conventional statistics (correlation analysis, clustering,
regression)
○ With artificial intelligence: neural networks and fuzzy logic
(module 3)
● With the use of specifically developed tools (module 3)
4
Data Management
Summary (2024 - 2025)
Chapter 1: Introduction & Motivation...................................................................... 2
Chapter 2: Relational Databases..............................................................................7
Part 1 – Entity Relationship Diagram..................................................................... 7
Part 2 – The Relational Model.............................................................................. 11
Part 3 – ER2RM........................................................................................................ 20
Chapter 3: Data Warehousing.................................................................................25
Part 1 – What’s a Data Warehouse & Star Schema.............................................25
Part 2 – Why use a Star or Snowflake schema?..................................................30
Part 3 – What about changes in dimensions?......................................................44
1. Thinking in Dimensions...............................................................................44
2. Slowly Changing Dimensions: Modelling History........................................46
2.1 A History Table per Attribute................................................................47
2.2 A History Table for each Entity Type....................................................48
2.3 A history table to replace a Dimension Table...................................... 49
Summary of the 3 options:.........................................................................49
3. Rapidly Changing Dimensions....................................................................50
4. Date and TIme in a data warehouse...........................................................52
5. Storing “states” of events: Snapshots vs. State Tables...............................52
DWH Design Conclusions.................................................................................... 54
Chapter 4 - OnLine Analytical Processing (OLAP)........................................................... 55
1. What is OLAP?.............................................................................................................55
2. Types of OLAP............................................................................................................. 55
2.1 Relational OLAP................................................................................................... 56
2.2 Multidimensional OLAP........................................................................................ 57
Dimensions & Measures.......................................................................................57
Slicing................................................................................................................... 57
Dicing....................................................................................................................58
Dimensions & Sub-dimensions.............................................................................58
Roll-up and Drill-down.......................................................................................... 58
Nesting................................................................................................................. 59
2.3 Hybrid OLAP........................................................................................................ 60
3. Applications.................................................................................................................. 60
Pivot Tables................................................................................................................ 60
Pivot Tables in MS Excel............................................................................................ 61
1
, Summary: Data Management [HMH25f] – Axel Temmerman
Chapter 1: Introduction & Motivation
Definition of Data Modeling
● Data modelling is a process used to define and analyse data requirements
needed to support the business processes within the scope of corresponding
information systems in organisations
○ It is the process of creating a data model for the data to be stored in a
database. This data model is a conceptual representation of data
objects.
○ The idea is that you start from a set of business processes and build a
relational data model that will support the execution of these processes
○ A relational database is composed of different tables linked through
relationships
○ Database normalisation is the process of restructuring a relational
database in accordance with a series of so-called normal forms in
order to reduce data redundancy and improve data integrity.
Definition of Business Intelligence (BI)
● Business Intelligence (BI) is an umbrella term that includes architectures,
databases, tools, applications and methodologies.
● Content-free term; means different things to different people
○ Objective: enable interactive access to data, its manipulation (data
warehouse design) and provide business managers and analysts the
ability to conduct analyses (business analytics)
○ (Better) decision making based on the analysis of historical and current
data, situations and performances: transformation of data into
information
● Data Warehouses are relational databases but include denormalized tables.
The goal in DW design is to have the simplest structure possible to enable
handling effective queries.
From data to information
2
, Summary: Data Management [HMH25f] – Axel Temmerman
Database Architecture
From Operational Databases to a Data
Warehouse and its Analysis
The data harvested from operational
systems, ERPs, CRMs and Flat Files
needs to be transformed through an ETL
system in order to be stored in the Data
Warehouse.
This is because the data gathered on the
left side of the graph is not structured in the
same way as the Data Warehouse.
Transforming the data turns it into
information, which you can subsequently
use to make Olap Analysis, Reporting or Data Mining.
BI Architecture: A second view
3
, Summary: Data Management [HMH25f] – Axel Temmerman
Operational Database vs. Data Warehouses
The Operational Database is the database used for real-time transactions; it is aimed
to support the business in its everyday operations (buying, invoicing, logistics…)
- Is tailored for the transactional software system
- Is stored on one place in the Database Management system (e.g. Oracle,
SQL Server, …)
- Follows the relational model, normalized architecture
The Data Warehouse is the database for Business Analytics (in other words the data
source used into BI)
- Separate system
- Is loaded from different operational systems (mainly databases)
- Own architecture (star, snowflake diagram)
Reports, OLAP, Data Mining
- Reports: overview of information
● Summary of data
● Computation of sums and means
● User friendly presented
- OLAP: Online Analytical Processing
● Summary of data and computing of sums
● Original information remains available in OLAP
○ “Roll-up”: aggregation of detailed data
○ “Drill-down”: find out how a total sum or average was computed
(through which original data)
● With sophisticated tools (e.g. a dashboard application)
● A pivot table is an example of OLAP
- Data Mining
● Objective: mining the data to (hidden) patterns, trends to find out
● Mining can be used through 2 manners:
○ With conventional statistics (correlation analysis, clustering,
regression)
○ With artificial intelligence: neural networks and fuzzy logic
(module 3)
● With the use of specifically developed tools (module 3)
4
