Summary
Summary: Data Management
- Course
- Institution
Summary of course Data Management from
[Show more]
Seller
Follow
axel_temmerman
Content preview
Summary: Data Management [HMH25f] – Axel TemmermanData 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
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller axel_temmerman. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $16.60. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
53022 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now