Exercise 3: Analytical data architecture

In this exercise, we cover:

Exercise part	Time (min)
Star schema: Facts and dimensions	15
Star schema (wine retailer)	10
Star schema (restaurant chain)	15
Star schema (Flowers GmbH)	15
OLAP operations	10
Question set	20
Wrap-up	5
Overall	90

How to work on this exercise

This notebook does not involve coding. You can solve all tasks in the PDF document.

Part 1: Star schema: Facts and dimensions

You are working as a data analytics consultant for a bank. The bank plans to build a data warehouse to support management reporting and analytical queries. A common starting point in data warehouse design is to consider the analytical questions that decision-makers want to answer. These questions help determine:

the facts that should be stored in the warehouse (typically derived from transaction data, e.g., transaction amounts or volumes), and
the dimensions that provide perspectives for analyzing these facts (typically derived from master data or attributes of master data, such as customer characteristics or product categories).

Below is a selection of analytical questions that bank managers might ask.

What is the ratio of individual customers to business clients in the bank’s portfolio?
Does this customer distribution correspond to the share of total transaction volume generated by each group?
Do customer attributes (e.g., profession, household status, education level, income group) influence the volume or value of financial transactions?
Which customers generate the highest transaction volumes?
How does transaction volume evolve over time? Are there noticeable shifts when analyzed by customer segments?
Can new customer segments be identified by combining multiple attributes (e.g., income level, profession, and household structure)?
Are there geographic patterns in customer behavior or transaction activity across different regions?
Are certain financial products or service categories particularly prominent in specific regions?
Are there relationships between customer profiles and the financial products they use?
Which financial products or services generate the highest transaction volumes? Which products are rarely used?
Are there seasonal patterns in transaction activity depending on the type of financial product or customer segment?

Your task

Read the questions and highlight the elements referring to facts. Highlight the elements referring to dimensions.
Based on these observations, derive a possible star schema, identifying the fact table and the relevant dimension tables.

Part 2: Star schema (wine retailer)

An online wine retailer plans to design a data warehouse to collect key figures regarding its wine sales. The relevant part of the operational database consists of the following tables:

CUSTOMER (ID, name, address, telephone, birthday, gender)
WINE (ID, name, type, year, bottle_price, class_ID)
CLASS (ID, name, region)
ORDER (customer_ID, wine_ID, timestamp, nr_bottles)

Create the star schema for the data warehouse.

Part 3: Star schema (restaurant chain)

A restaurant chain wants to build a management information system. The application system architecture is to be aligned with the data warehouse concept. An OLAP system is chosen for report generation. The company maintains various restaurants, which can be differentiated by region and by renovation status. The Leonardo-Campus restaurant belongs to the North region and is New from the renovation status. The restaurant Nordblick is also located in the North region, but its renovation status is Old. The Spätzleburg restaurant, on the other hand, is in the South region and has a New renovation status. The menu items of the restaurants are divided into the classes “Starters”, “Main courses”, “Desserts”, “Beverages” and “Other”. Time-based analyses are to enable evaluations by days of the week, weeks, months and years. Simple contribution margin calculations for restaurants, services and days are to be supported. The contribution margin is mathematically calculated from sales minus costs.

Create the logical data warehouse schema!

Part 4: Star schema (Flowers GmbH)

As an employee of the Controlling department, you will be given the task of setting up a data warehouse for sales and headcount analyses. Flowers GmbH has two branches in Hesse, one in Rhineland-Palatinate and three in France. Product categories include cut flowers, garden flowers and bridal jewelry. Within garden flowers, the rose family includes the genera roses and wild roses. Products in the rose genus include thornless roses and roses with thorns. The company distinguishes between corporate and private customers. Data about the place of residence of the customers is available. The DWH should be able to generate daily and weekly analyses as well as monthly, quarterly and annual reports. The key figure required for the sales analyses is the profit, which is calculated from the difference between revenues and costs. Employees have been working in the different branches of the company for different periods of time. The DWH should also enable headcount analyses. The key figure required for this is the headcount.

Create the logical data warehouse schema!

Part 5: OLAP operations

Next, we will translate common OLAP operations into SQL queries using a simple dataset.

We work with the sales_data table:

order_id	date	region	product_category	sales_amount
1	2024-01-15	Europe	Electronics	200
2	2024-01-20	Europe	Furniture	150
3	2024-02-10	Asia	Electronics	300
4	2024-02-12	Europe	Electronics	250
5	2024-03-05	Asia	Furniture	100

Working with dates: DATE_TRUNC

To aggregate data by time periods (e.g., months), we use:

DATE_TRUNC('month', date)

This function rounds a date down to the beginning of the specified time unit. Example:

2024-01-15 → 2024-01-01
2024-02-12 → 2024-02-01

This allows grouping data at the month level instead of day level.

Task 1: Roll-up

Roll-up means aggregating data to a higher level of granularity.

In this case:

From day → month
Grouped by region and month

Write an SQL query that calculates total sales:

grouped by region
aggregated at the monthly level (not daily)

Use DATE_TRUNC('month', date).

Also:

include total_sales as a column
sort the result by region and month

Finally, sketch the expected structure of the resulting table.

Task 2: Drill-down

Drill-down means moving to a more detailed level.

In this case:

From month → day
Keep both month and exact date

Write an SQL query that shows total sales:

by region
by month
and by exact date

Use DATE_TRUNC('month', date).

Also:

include total_sales
sort by region, month, and date

Finally, sketch the structure of the result.

Task 3: Slice

Slice means fixing one dimension to a single value.

In this case:

Only look at Europe

Write an SQL query that calculates total sales:

only for region = 'Europe'
grouped by month

Use DATE_TRUNC('month', date).

Also:

include total_sales
sort by month

Sketch the resulting table structure.

Part 6: Question sets

Work on these questions as far as you get during the session. There is no expectation to complete everything — you can continue with the rest after the session.

Question 1

Explain the role of exploratory data analysis in business decision-making.

Address the following points:

Why analysts do not jump directly to modeling
How data preparation and EDA are connected
Which kinds of insights EDA can provide before formal modeling

Question 2

Measurement scales determine which analyses are meaningful. Why?

For each of the following variables, identify the scale (nominal, ordinal, interval, ratio) and name one appropriate descriptive statistic or visualization:

customer_id
customer satisfaction (low / medium / high)
date_of_birth
income
country

Then explain why the mean is appropriate for some variables but not for others.

Question 3

A histogram is useful for some variables, but not for all. Explain why.

Use the examples below:

customer names
age
date_of_birth
country

Question 4

Why is the following dataset not tidy?

Answer the following:

Identify values, variables, observations, and the unit of observation.
Explain why the structure is problematic for analytics tools.
Propose the tidy target structure.
Describe the transformation needed.

Question 5

Different data quality issues require different cleansing actions.

Match each issue with a suitable cleansing action and justify your choice:

missing values:
outdated values:
outliers / implausible values:
inconsistent date formats:
inconsistent units:
duplicate records:
data entry errors:

Question 6

Why can data transformation change which analyses are possible?

Use the example of timestamps stored as strings.

Discuss:

which operations are possible before transformation
which analyses become possible after transforming timestamps
examples such as hour of day, day of week, or numeric timestamps

Question 7

Classify the following EDA methods.

For each method, classify it as:

univariate or multivariate
graphical or non-graphical

Methods:

Histogram:
Boxplot:
Frequency table:
Correlation:
Scatterplot:
Clustering:

Question 8

Explain the logic of the k-means algorithm in your own words.

Your answer should include:

the role of k
initialization
centroids
stopping criterion

Question 9

Why is scaling important before k-means clustering?

Discuss:

what happens if one variable has a much larger scale than another
why k-means is sensitive to scaling
what standardization is intended to achieve

Question 10

How can a dendrogram support the choice of the number of clusters?

In your answer, explain:

what a dendrogram shows
how large vertical gaps can help suggest a reasonable number of clusters
how this supports deciding where to “cut” the tree

Question 11

Why are star schemata preferred over highly normalized schemata in data warehouses?

Discuss:

differences between OLTP and OLAP requirements
query performance and join complexity
usability for analysts
how the structure relates to tidy data principles

Question 12

Explain the difference between a fact table and a dimension table.

In your answer, include:

the type of data stored in each
examples of attributes
how they are used together in analysis
how this relates to tidy data structure

Question 13

What is the role of the time dimension in a data warehouse?

Discuss:

why time is almost always included as a dimension
typical hierarchies (e.g., day → month → year)
how it supports analysis

Question 14

Explain the difference between roll-up and drill-down in OLAP.

Use examples to illustrate:

change in level of detail
impact on number of rows
typical use cases
illustrate both operations using SQL (based on the examples in Part 5)

Wrap-up

🎉🎈 You have completed the notebook - good work! 🎈🎉

In this notebook, we have learned to

Translate business questions into analytical requirements
Distinguish clearly between facts (measures) and dimensions (perspectives)
Derive star schemata from both conceptual questions and operational data sources
Structure data for multidimensional analysis (OLAP)

Session 3 survey

Before you wrap up, please complete the Session 3 survey here: ?meta:surveys.session_03.url. Thank you 🙏