Dimensionaal modelleren

Introductie tot datamodellering in Snowflake

Nuno Rocha

Director of Engineering

Introductie van het dimensionale datamodel

Dimensionaal modelleren: Een techniek om data te structureren die metingen (feiten) scheidt van beschrijvende details (dimensies), geoptimaliseerd voor rapportage en analyse.

Dimensionaal model

Introductie van het dimensionale datamodel (1)

Dimensies: Entiteiten met categorische data in een dimensionaal model.
Feiten: Entiteiten die activiteiten binnen de categorieën in de dimensies vastleggen en kwantificeren.

Dimensionaal collegemodel

Schemadiagram van het stermodel

Dimensionaal stermodel

Schemadiagram van het stermodel

Snowflake Data Warehouse: Een cloudgebaseerde opslag- en analysedienst.
Snowflake-schema: Een manier om data in een dimensionaal model te organiseren met subdimensies.

Snowflake, datawarehouse vs dimensionaal model

Schemadiagram van het snowflake-model

Dimensionaal snowflake-model

Dimensies definiëren

Hernoem entiteiten naar dim_EntityName voor duidelijkheid, aansluitend op de dimensies in het model:

ALTER TABLE students RENAME TO dim_students;

ALTER TABLE classes RENAME TO dim_classes;

ALTER TABLE schools RENAME TO dim_schools;

Datumdimensie definiëren

De tabel dim_date maken om vaste sleuteldatums op te slaan die horen bij de inschrijvingen van studenten:

CREATE OR REPLACE TABLE dim_date (
    date_id NUMBER(10,0) PRIMARY KEY,
    year NUMBER(4,0),
    semester VARCHAR(255)
);

Feitentabel voor inschrijvingen definiëren

Maak een feitenentiteit met verwijzingen naar alle dimensies:

CREATE OR REPLACE TABLE fact_enrollments (
    enrollment_id NUMBER(10,0) PRIMARY KEY,
    student_id NUMBER(10,0),
    class_id NUMBER(10,0),
    date_id NUMBER(10,0),
    FOREIGN KEY (student_id) REFERENCES dim_students(student_id),
    FOREIGN KEY (class_id) REFERENCES dim_classes(class_id),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id)
);

Gegevens opvragen uit de dimensies

Dimensionaal datamodel voor college

Gegevens opvragen uit de dimensies (1)

SELECT name, 
    class_name
FROM fact_enrollments
    JOIN dim_students -- Joinen om studentnamen op te halen
    ON fact_enrollments.student_id = dim_students.student_id
    JOIN dim_classes -- Joinen om klassennamen op te halen
    ON fact_enrollments.class_id = dim_classes.class_id
    JOIN dim_schools -- Joinen om te filteren op de 'Science'-school
    ON dim_classes.school_id = dim_schools.school_id
    JOIN dim_date -- Joinen om te beperken tot het jaar 2023
    ON fact_enrollments.date_id = dim_date.date_id
WHERE dim_schools.school_name = 'Science' 
    AND dim_date.year = 2023;

Overzicht terminologie en functies

Dimensionaal modelleren: Een techniek om data te structureren die metingen (feiten) scheidt van beschrijvende details (dimensies), geoptimaliseerd voor rapportage en analyse
Dimensies: Entiteiten met categorische data in een dimensionaal model
Feiten: Entiteiten die activiteiten binnen de categorieën in de dimensies vastleggen en kwantificeren
ALTER TABLE: SQL-opdracht om de structuur van een bestaande entiteit te wijzigen
RENAME TO: SQL-opdracht, gebruikt met ALTER TABLE, om een entiteit te hernoemen
JOIN ON: SQL-clausule om rijen uit tabellen te combineren op basis van een gerelateerde kolom
WHERE: SQL-clausule om records te filteren op een voorwaarde
AND: Logische operator bij WHERE om meerdere voorwaarden te combineren

Functie-overzicht

-- Een entiteit wijzigen
ALTER TABLE table_name 
RENAME TO new_name;

-- Data opvragen uit gekoppelde entiteiten, gefilterd op specifieke voorwaarden
SELECT 
    table_name.column_name,
    other_name.*
FROM table_name
    JOIN other_table 
    ON table_name.FK = other_table.PK
WHERE column_name  condition  value
    AND column_name  condition  value;

Laten we oefenen!

Introductie tot datamodellering in Snowflake