Star schema's implementeren

Gegevens transformeren en analyseren met Microsoft Fabric

Luis Silva

Solution Architect - Data & AI

Medallion-architectuur

Diagram van de medallion-data-architectuur, met data die uit bronnen wordt ingeladen in de bronzen laag, omgezet naar opgeschoonde data in de zilveren laag en verder getransformeerd naar een ster-schema in de gouden laag, geschikt voor analyses

Ontwerppatroon voor het organiseren van data
Drie lagen met stapsgewijze verfijning van de data

Medallion-architectuur

Diagram van de medallion-data-architectuur, met de bronzen laag benadrukt

Bronzen laag: Data uit de bron in originele, ruwe vorm.

Medallion-architectuur

Diagram van de medallion-data-architectuur, met de zilveren laag benadrukt

Zilveren laag: Opgeschoonde en gestandaardiseerde data.

Medallion-architectuur

Diagram van de medallion-data-architectuur, met de gouden laag benadrukt

Gouden laag: Data in een formaat voor analytics, meestal een ster-schema.

Medallion-architectuur in Fabric

Diagram van de medallion-data-architectuur in Fabric, met datapijplijnen die data uit bronnen inladen in een lakehouse in de bronzen laag, spark-notebooks en dataflows die data transformeren naar een lakehouse in de zilveren laag, en verder getransformeerd naar een ster-schema in een warehouse in de gouden laag.

Bronzen laag: Lakehouse
Zilveren laag: Lakehouse
Gouden laag: Lakehouse of Warehouse

Een ster-schema implementeren

Gebruik T-SQL CREATE TABLE om tabellen te definiëren.
Fabric ondersteunt de meest gebruikte T-SQL-datatypen.

Dimension table structure

Describe business entities (e.g., products).
Usually have the following columns:

Sample dimension table called Dim Product with columns Product ID, Product Key, Product Name and Product Brand

Dimension table structure

Describe business entities (e.g., products).
Usually have the following columns:
- Surrogate Key. Single-column unique identifier for dimension items.

Sample dimension table called Dim Product with columns Product ID, Product Key, Product Name and Product Brand. Product ID is highlighted as the surrogate key

Dimension table structure

Describe business entities (e.g., products).
Usually have the following columns:
- Surrogate Key. Single-column unique identifier for dimension items.
- Natural Key (Business Key). Single-column unique identifier that comes from source systems.

Sample dimension table called Dim Product with columns Product ID, Product Key, Product Name and Product Brand. Product Key is highlighted as the natural key

Dimension table structure

Describe business entities (e.g., products).
Usually have the following columns:
- Surrogate Key. Single-column unique identifier for dimension items.
- Natural Key (Business Key). Single-column unique identifier that comes from source systems.
- Dimension Attributes. One or more columns that are used to filter data and provide context to the data stored in the fact table.

Sample dimension table called Dim Product with columns Product ID, Product Key, Product Name and Product Brand. Product Name and Product Brand are highlighted as the dimension attributes

Building dimension tables

T-SQL script using the CREATE TABLE statement to create the sample dimension table dim_Product

Sample dimension table called Dim Product with columns Product ID, Product Key, Product Name and Product Brand. Product ID is the surrogate key, Product Key is the natural key, and Product Name and Product Brand are the dimension attributes

Fact table structure

Describe business events (e.g., sale of a product).
Columns:

Sample fact table called Fact Sales with columns Date ID, Store ID, Product ID, Customer ID, Payment ID, Packaging Notes, Sale Order Number, Sale Units and Sale Price

Fact table structure

Describe business events (e.g., sale of a product).
Columns:
- Dimension Keys. References to the surrogate keys of the dimension tables.

Sample fact table called Fact Sales with columns Date ID, Store ID, Product ID, Customer ID, Payment ID, Packaging Notes, Sale Order Number, Sale Units and Sale Price. Date ID, Store ID, Product ID, Customer ID, and Payment ID are highlighted as the dimension keys

Fact table structure

Describe business events (e.g., sale of a product).
Columns:
- Dimension Keys. References to the surrogate keys of the dimension tables.
- Attributes. Provide additional information about the fact but are neither a dimension or a numerical measure.

Sample fact table called Fact Sales with columns Date ID, Store ID, Product ID, Customer ID, Payment ID, Packaging Notes, Sale Order Number, Sale Units and Sale Price. Packaging Notes and Sale Order Number are highlighted as the attributes

Fact table structure

Describe business events (e.g., sale of a product).
Columns:
- Dimension Keys. References to the surrogate keys of the dimension tables.
- Attributes. Provide additional information about the fact but are neither a dimension or a numerical measure.
- Measures. Quantify something about the fact; they are numeric and commonly additive, e.g., number of units, dollar amount, etc.

Sample fact table called Fact Sales with columns Date ID, Store ID, Product ID, Customer ID, Payment ID, Packaging Notes, Sale Order Number, Sale Units and Sale Price. Sale Units and Sale Price are highlighted as the measures

Building fact tables

T-SQL script using the CREATE TABLE statement to create the sample fact table fact_Sales

Sample fact table called Fact Sales with columns Date ID, Store ID, Product ID, Customer ID, Payment ID, Packaging Notes, Sale Order Number, Sale Units and Sale Price

Let's practice!

Gegevens transformeren en analyseren met Microsoft Fabric