SH

Sascha Hess

xenosystems.de - IT-Consulting & Data Management

www.xenosystems.de

STRATEGISCHE WISSENS-ROADMAP 2026

SQL Server

BI & Governance

Datenarchitektur

Welche Datenhaltungsarchitektur wann — und wie beide zusammenwachsen

1

Entscheidungsmatrix

12 Kriterien — DWH, Data Lake oder Lakehouse für Ihren Anwendungsfall

2

10 Architektur-Fallen

Typische Fehler beim Aufbau beider Architekturen — mit Sofort-Korrekturen

3

DWH-Modellierung in SQL

Star Schema, Slowly Changing Dimensions und Fact-Tabellen — sofort einsetzbar

4

Lakehouse-Muster

Medaillon-Architektur, Delta-Format und dbt auf SQL Server und Azure Fabric

5

30-Tage-Architekturplan

Vom konzeptlosen Reporting zum strukturierten Datenfundament

DER HÄUFIGSTE KMU-STARTFEHLER

■  72 % der KMU-BI-Projekte beginnen ohne explizite Architekturentscheidung — das Werkzeug (Power BI, Azure, SQL Server) wird gewählt, bevor das Konzept steht.

■  Die häufigste Folge: Power BI greift direkt auf Produktivdatenbanken zu — keine Historisierung, keine Versionsierung, kein stabiles Fundament.

■  Die zweit-häufigste Folge: Ein "Data Lake" entsteht als unkuratierter Dateifriedhof — viele Daten, null Nutzbarkeit.

-- Klassische DWH-Schichtenstruktur in SQL Server

CREATE SCHEMA stg;    -- Staging: rohe Quelldaten, temporär

CREATE SCHEMA core;   -- Core: bereinigt, historisiert, 3NF oder Dimensional

CREATE SCHEMA dm;     -- Data Mart: aggregiert, themenspezifisch

CREATE SCHEMA rpt;    -- Reporting: Views für Power BI DirectQuery

-- Metadaten-Spalten: jede Core-Tabelle bekommt sie

-- _src_system:    aus welchem Quellsystem

-- _load_date:     wann geladen

-- _valid_from:    gültig ab (für SCD Typ 2)

-- _valid_to:      gültig bis (NULL = aktuell)

-- _is_current:    schneller Filter auf aktuellen Datensatz

-- _row_hash:      Änderungs-Erkennung ohne Feldvergleich

Schema

On Write (starr)

On Read (flexibel)

On Write (flexibel anpassbar)

ACID-Transaktionen

Ja

Nein

Ja (Delta/Iceberg)

Dateiformat

Proprietär / Columnar

Beliebig

Offen (Parquet + Delta)

Skalierung Compute

Vertikal (teuer)

Horizontal

Horizontal (Cloud-native)

Query Performance

Sehr hoch

Mittel

Hoch (mit Optimierung)

Rohdaten verfügbar?

Nein

Ja

Ja

BI-Tool-Kompatibilität

Sehr hoch

Mittel

Sehr hoch (DirectLake)

Einstiegskosten KMU

Mittel

Niedrig

Mittel

Betriebskomplexität

Mittel

Hoch

Mittel–Hoch

1

Reporting-Anforderungen sind stabil und gut definiert

3

0

1

2

Datenquellen sind strukturiert (ERP, CRM, SQL)

3

0

2

3

Team hat starke SQL-Kompetenz, keine Spark/Python-Erfahrung

3

0

1

4

Regulatorische Anforderungen erfordern strikte Datenhistorie und Audit

3

0

2

5

Datenvolumen < 100 GB pro Tag, kein starkes Wachstum erwartet

3

1

1

6

Unstrukturierte Daten (Logs, JSON, Bilder, Sensoren) sind relevant

0

3

2

7

Maschinelles Lernen und Data Science auf Rohdaten erforderlich

0

3

2

8

Datenvolumen > 1 TB, starkes Wachstum erwartet

0

2

3

9

Schema ändert sich häufig — neue Quellen, neue Attribute

0

3

2

10

Microsoft Fabric oder Power BI Premium bereits lizenziert

1

0

3

11

Cloud-Budget vorhanden, On-Premise-Investitionen abgeschrieben

0

1

3

12

Explorative Analysen und Ad-hoc-Abfragen dominieren

0

2

3

AUSWERTUNG UND EMPFEHLUNG

■  DWH-Score deutlich höher (> 8 Punkte Abstand): Klassisches DWH auf SQL Server — solide, bewährt, für Ihr Team beherrschbar.

■  Data Lake-Score deutlich höher: Prüfen Sie ob ein Lakehouse nicht sinnvoller ist — ein reiner Data Lake ohne Governance wird zum Swamp.

■  Lakehouse-Score führt oder Scores liegen nah beieinander: Lakehouse-Ansatz (Medaillon-Architektur) — heute der pragmatischste Einstieg für KMU mit Wachstumsambitionen.

■  Alle Scores unter 10: Architekturanforderungen sind noch nicht klar genug — vor der Technologiewahl steht die Anforderungsanalyse.

-- Schnellster Weg aus dem Direkt-auf-ERP-Szenario:

-- Tägliche Snapshot-Tabellen als einfachstes DWH-Fundament

CREATE TABLE dwh.Snapshot_Umsatz_Taeglich (

    SnapshotDatum   DATE         NOT NULL,

    ArtikelNr       NVARCHAR(20) NOT NULL,

    KundenNr        INT          NOT NULL,

    Region          NVARCHAR(50),

    Umsatz_Netto    DECIMAL(18,2),

    Menge           DECIMAL(18,4),

    _geladen_am     DATETIME2    DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_Snap_Umsatz PRIMARY KEY (SnapshotDatum, ArtikelNr, KundenNr)

);

-- Power BI verbindet sich ausschließlich auf dwh.* — nie auf dbo.* direkt

-- Ergebnis: ERP-Produktivlast geht auf null, Historisierung entsteht automatisch

-- Datei-Inventar auch ohne Azure Purview: einfache Verwaltungstabelle

CREATE TABLE lake.Datei_Inventar (

    DateiID         INT IDENTITY PRIMARY KEY,

    Pfad            NVARCHAR(500)   NOT NULL,   -- z.B. bronze/erp/auftraege/2026/03/

    Dateiname       NVARCHAR(200)   NOT NULL,

    Schicht         NVARCHAR(10)    NOT NULL,   -- BRONZE / SILVER / GOLD

    Quellsystem     NVARCHAR(50),

    Thema           NVARCHAR(100),              -- 'Auftraege', 'Kunden'

    Format          NVARCHAR(20),               -- CSV, JSON, PARQUET, DELTA

    Zeilenanzahl    BIGINT,

    Groesse_MB      DECIMAL(10,2),

    Extraktion_Von  DATETIME2,

    Extraktion_Bis  DATETIME2,

    Erstellt_Am     DATETIME2 DEFAULT SYSUTCDATETIME(),

    Verantwortlich  NVARCHAR(100),

    Qualitaet       NVARCHAR(10) DEFAULT 'UNGEPRUEFT'  -- OK / WARNUNG / FEHLER

);

-- SCD Typ 2: vollständige Historisierung einer Kundendimension

CREATE TABLE dm.Dim_Kunden (

    KundenKey       INT IDENTITY PRIMARY KEY,   -- Surrogate Key

    KundenNr        INT          NOT NULL,       -- Business Key (aus ERP)

    Name            NVARCHAR(200),

    Region          NVARCHAR(50),

    Umsatzklasse    NVARCHAR(20),

    Zahlungsziel    INT,

    -- SCD-Typ-2-Felder

    _valid_from     DATE         NOT NULL,

    _valid_to       DATE         NOT NULL DEFAULT '9999-12-31',

    _is_current     BIT          NOT NULL DEFAULT 1,

    _row_hash       BINARY(32),   -- SHA2_256 über alle inhaltlichen Felder

    _src_system     NVARCHAR(20) DEFAULT 'ERP',

    _load_date      DATETIME2    DEFAULT SYSUTCDATETIME()

);

-- MERGE-basiertes SCD-Typ-2-Update: erkennt Änderungen via Row-Hash

CREATE OR ALTER PROCEDURE dwh.SP_Aktualisiere_Dim_Kunden AS

BEGIN

    SET NOCOUNT ON;

    -- Schritt 1: Geänderte Records identifizieren (Row-Hash-Vergleich)

    UPDATE dm.Dim_Kunden SET

        _valid_to  = CAST(GETDATE() AS DATE),

        _is_current = 0

    WHERE _is_current = 1

      AND EXISTS (

        SELECT 1 FROM stg.Kunden s

        WHERE s.KundenNr = dm.Dim_Kunden.KundenNr

          AND HASHBYTES('SHA2_256',

              CONCAT(s.Name,'|',s.Region,'|',s.Umsatzklasse,'|',

                     CAST(s.Zahlungsziel AS NVARCHAR))

          ) <> dm.Dim_Kunden._row_hash

    );

    -- Schritt 2: Neue Version für geänderte + völlig neue Records einfügen

    INSERT INTO dm.Dim_Kunden

        (KundenNr, Name, Region, Umsatzklasse, Zahlungsziel,

         _valid_from, _row_hash)

    SELECT

        s.KundenNr, s.Name, s.Region, s.Umsatzklasse, s.Zahlungsziel,

        CAST(GETDATE() AS DATE),

        HASHBYTES('SHA2_256',

            CONCAT(s.Name,'|',s.Region,'|',s.Umsatzklasse,'|',

                   CAST(s.Zahlungsziel AS NVARCHAR)))

    FROM stg.Kunden s

    WHERE NOT EXISTS (

        SELECT 1 FROM dm.Dim_Kunden d

        WHERE d.KundenNr = s.KundenNr AND d._is_current = 1

    );

END

-- Junk Dimension: viele kleine Flags zu einer Tabelle zusammengefasst

CREATE TABLE dm.Dim_Auftrags_Flags (

    FlagsKey            INT IDENTITY PRIMARY KEY,

    AuftragsStatus      NVARCHAR(20),   -- NEU / BESTÄTIGT / GELIEFERT / ABGESCHLOSSEN

    Prioritaet          NVARCHAR(10),   -- NORMAL / HOCH / EILAUFTRAG

    Herkunftskanal      NVARCHAR(20),   -- ONLINE / TELEFON / AUSSENDIENST / EDI

    IstReklamation      BIT,

    IstWiederholung     BIT,

    -- Statt 5 separater Dimensionstabellen: eine Junk Dimension

    CONSTRAINT UQ_Flags UNIQUE (AuftragsStatus, Prioritaet,

        Herkunftskanal, IstReklamation, IstWiederholung)

);

-- Fact-Tabelle: kompakt, wenige JOINs

CREATE TABLE dm.Fact_Auftraege (

    AuftragsKey         INT IDENTITY PRIMARY KEY,

    -- Dimensionsreferenzen (Surrogate Keys)

    DatumKey            INT NOT NULL,   -- Dim_Datum

    KundenKey           INT NOT NULL,   -- Dim_Kunden (SCD Typ 2)

    ArtikelKey          INT NOT NULL,   -- Dim_Artikel

    VertreterKey        INT NOT NULL,   -- Dim_Mitarbeiter

    FlagsKey            INT NOT NULL,   -- Dim_Auftrags_Flags (Junk)

    -- Degenerate Dimension: Belegnummer direkt in Fact, kein JOIN

    AuftragsNr          NVARCHAR(20)    NOT NULL,

    -- Metriken (additive und semi-additive)

    Nettobetrag         DECIMAL(18,2),

    Menge               DECIMAL(18,4),

    Deckungsbeitrag     DECIMAL(18,2),

    -- Nicht-additive Metrik: separat behandeln

    Lieferzeit_Tage     INT,            -- niemals summieren!

    -- Metadaten

    _load_date          DATETIME2 DEFAULT SYSUTCDATETIME()

);

-- Vollständige Datumsdimension — einmal generieren, für immer nutzen

DECLARE @Start DATE = '2020-01-01';

DECLARE @Ende  DATE = '2030-12-31';

WITH Kalender AS (

    SELECT @Start AS Datum

    UNION ALL

    SELECT DATEADD(DAY, 1, Datum) FROM Kalender WHERE Datum < @Ende

)

INSERT INTO dm.Dim_Datum (

    DatumKey, Datum, Jahr, Quartal, Monat, MonatName, KW_ISO,

    Tag, Wochentag, WochentagName, IstWochenende,

    IstFeiertag, Feiertagsname,

    FiskaljJahr, FiskalQuartal,   -- anpassen: Fiskaljahr beginnt z.B. April

    IstArbeitstag, Jahrestag, MonatsBeginn, MonatsEnde

)

SELECT

    CAST(FORMAT(Datum,'yyyyMMdd') AS INT)    AS DatumKey,

    Datum,

    YEAR(Datum)                              AS Jahr,

    DATEPART(QUARTER, Datum)                 AS Quartal,

    MONTH(Datum)                             AS Monat,

    FORMAT(Datum,'MMMM','de-DE')             AS MonatName,

    DATEPART(ISO_WEEK, Datum)                AS KW_ISO,

    DAY(Datum)                               AS Tag,

    DATEPART(WEEKDAY, Datum)                 AS Wochentag,

    FORMAT(Datum,'dddd','de-DE')             AS WochentagName,

    CASE WHEN DATEPART(WEEKDAY, Datum) IN (1,7) THEN 1 ELSE 0 END AS IstWochenende,

    0                                        AS IstFeiertag,  -- nachträglich befüllen

    NULL                                     AS Feiertagsname,

    -- Fiskaljahr: Oktober–September (Beispiel)

    CASE WHEN MONTH(Datum) >= 10

         THEN YEAR(Datum) + 1 ELSE YEAR(Datum) END AS FiskalJahr,

    CASE WHEN MONTH(Datum) IN (10,11,12) THEN 1

         WHEN MONTH(Datum) IN (1,2,3)    THEN 2

         WHEN MONTH(Datum) IN (4,5,6)    THEN 3

         ELSE 4 END                          AS FiskalQuartal,

    CASE WHEN DATEPART(WEEKDAY, Datum) IN (1,7) THEN 0 ELSE 1 END AS IstArbeitstag,

    DATEPART(DAYOFYEAR, Datum)               AS Jahrestag,

    DATEFROMPARTS(YEAR(Datum), MONTH(Datum), 1)               AS MonatsBeginn,

    EOMONTH(Datum)                           AS MonatsEnde

FROM Kalender

OPTION (MAXRECURSION 4000);

bronze/

  erp/

    auftraege/

      jahr=2025/

        monat=03/

          tag=01/

            auftraege_20250301_001.parquet

          tag=02/

            auftraege_20250302_001.parquet

silver/

  auftraege_bereinigt/

    jahr=2025/

      monat=03/

        _delta_log/          ← Delta Lake Transaktionslog

        part-00000.parquet

-- Inkrementelles Laden mit Late-Arriving-Fact-Behandlung

CREATE OR ALTER PROCEDURE dwh.SP_Lade_Fact_Auftraege_Inkrementell AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @LastLoad DATETIME2;

    SELECT @LastLoad = MAX(_load_date) FROM dm.Fact_Auftraege;

    SET @LastLoad = ISNULL(@LastLoad, '2000-01-01');

    -- Neue und geänderte Records seit letztem Load

    MERGE dm.Fact_Auftraege AS ziel

    USING (

        SELECT

            -- Dimensionskeys per Lookup ermitteln

            ISNULL(dd.DatumKey, -1)         AS DatumKey,

            ISNULL(dk.KundenKey, -1)        AS KundenKey,

            ISNULL(da.ArtikelKey, -1)       AS ArtikelKey,

            s.AuftragsNr,

            s.Nettobetrag,

            s.Menge,

            s.GeaendertAm

        FROM stg.Auftraege s

        LEFT JOIN dm.Dim_Datum dd

            ON dd.Datum = CAST(s.Auftragsdatum AS DATE)

        -- SCD Typ 2: Historischen Key zum Zeitpunkt der Buchung ermitteln

        LEFT JOIN dm.Dim_Kunden dk

            ON dk.KundenNr = s.KundenNr

            AND CAST(s.Auftragsdatum AS DATE)

                BETWEEN dk._valid_from AND dk._valid_to

        LEFT JOIN dm.Dim_Artikel da

            ON da.ArtikelNr = s.ArtikelNr AND da._is_current = 1

        WHERE s.GeaendertAm > @LastLoad       -- nur neue/geänderte

           OR s.Auftragsdatum > DATEADD(DAY,-7,GETDATE())  -- Late Arriving: 7 Tage

    ) AS quelle

    ON ziel.AuftragsNr = quelle.AuftragsNr

    WHEN MATCHED AND ziel.Nettobetrag <> quelle.Nettobetrag THEN

        UPDATE SET ziel.Nettobetrag = quelle.Nettobetrag,

                   ziel._load_date  = SYSUTCDATETIME()

    WHEN NOT MATCHED THEN

        INSERT (DatumKey, KundenKey, ArtikelKey, AuftragsNr, Nettobetrag, Menge)

        VALUES (quelle.DatumKey, quelle.KundenKey, quelle.ArtikelKey,

                quelle.AuftragsNr, quelle.Nettobetrag, quelle.Menge);

END

-- Vorberechnete Aggregation: monatliche KPIs je Region und Warengruppe

CREATE TABLE dm.Agg_Umsatz_Monatlich (

    AggKey          INT IDENTITY PRIMARY KEY,

    Jahr            INT,

    Monat           INT,

    FiskalQuartal   INT,

    Region          NVARCHAR(50),

    Warengruppe     NVARCHAR(50),

    -- Additive Metriken — summierbar über alle Dimensionen

    Umsatz_Netto    DECIMAL(18,2),

    Deckungsbeitrag DECIMAL(18,2),

    Auftragsanzahl  INT,

    Kundenanzahl    INT,

    -- Vorberechnete Verhältniszahlen — nicht additiv, nur auf dieser Ebene

    DB_Marge_Pct    DECIMAL(8,4),

    Umsatz_Vorjahr  DECIMAL(18,2),

    Wachstum_YoY_Pct DECIMAL(8,4),

    _aktualisiert   DATETIME2 DEFAULT SYSUTCDATETIME()

);

-- Job: täglich nach ETL-Abschluss aktualisieren (läuft in Sekunden)

CREATE OR ALTER PROCEDURE dwh.SP_Aktualisiere_Agg_Umsatz AS

BEGIN

    TRUNCATE TABLE dm.Agg_Umsatz_Monatlich;

    INSERT INTO dm.Agg_Umsatz_Monatlich

        (Jahr, Monat, FiskalQuartal, Region, Warengruppe,

         Umsatz_Netto, Deckungsbeitrag, Auftragsanzahl, Kundenanzahl,

         DB_Marge_Pct)

    SELECT

        dd.Jahr, dd.Monat, dd.FiskalQuartal,

        dk.Region, da.Warengruppe,

        SUM(f.Nettobetrag),

        SUM(f.Deckungsbeitrag),

        COUNT(DISTINCT f.AuftragsNr),

        COUNT(DISTINCT f.KundenKey),

        ROUND(SUM(f.Deckungsbeitrag) / NULLIF(SUM(f.Nettobetrag),0), 4)

    FROM dm.Fact_Auftraege f

    JOIN dm.Dim_Datum    dd ON f.DatumKey  = dd.DatumKey

    JOIN dm.Dim_Kunden   dk ON f.KundenKey = dk.KundenKey

    JOIN dm.Dim_Artikel  da ON f.ArtikelKey = da.ArtikelKey

    GROUP BY dd.Jahr, dd.Monat, dd.FiskalQuartal,

             dk.Region, da.Warengruppe;

END

-- Vollständiges Star Schema für KMU-Umsatzreporting

-- Dimensions-Tabellen

CREATE TABLE dm.Dim_Datum     (DatumKey INT PRIMARY KEY, /* → Kapitel 4, Punkt 4 */ Datum DATE);

CREATE TABLE dm.Dim_Artikel   (ArtikelKey INT IDENTITY PRIMARY KEY,

    ArtikelNr NVARCHAR(20), Bezeichnung NVARCHAR(200), Warengruppe NVARCHAR(50),

    Hauptgruppe NVARCHAR(50), EKPreis DECIMAL(18,4),

    _is_current BIT DEFAULT 1, _valid_from DATE, _valid_to DATE DEFAULT '9999-12-31');

CREATE TABLE dm.Dim_Mitarbeiter (MitarbeiterKey INT IDENTITY PRIMARY KEY,

    MitarbeiterNr INT, Vorname NVARCHAR(100), Nachname NVARCHAR(100),

    Abteilung NVARCHAR(50), Region NVARCHAR(50), Rolle NVARCHAR(50),

    _is_current BIT DEFAULT 1, _valid_from DATE, _valid_to DATE DEFAULT '9999-12-31');

-- Konformierte Dimensionen: von beiden Fact-Tabellen genutzt

-- Fact-Tabelle 1: Aufträge (transaktional, tagesfein)

-- Fact-Tabelle 2: Lagerbestand (periodisch, Stichtag je Monat)

-- Beziehungsdiagramm im Star Schema:

--

--   Dim_Datum ──────────────┐

--   Dim_Kunden (SCD2) ──────┤

--   Dim_Artikel (SCD2) ─────┤──→ Fact_Auftraege

--   Dim_Mitarbeiter ─────────┤

--   Dim_Auftrags_Flags ──────┘

--

--   Dim_Datum ──────────────┐

--   Dim_Artikel ────────────┤──→ Fact_Lagerbestand (Periodic Snapshot)

--   Dim_Lager ──────────────┘

-- Periodic Snapshot Fact für Lagerbestand

CREATE TABLE dm.Fact_Lagerbestand_Monat (

    SnapshotKey         INT IDENTITY PRIMARY KEY,

    DatumKey            INT NOT NULL,   -- Stichtag: letzter Tag des Monats

    ArtikelKey          INT NOT NULL,

    LagerortKey         INT NOT NULL,

    -- Bestandsmetriken (semi-additiv: summierbar über Artikel, nicht über Zeit!)

    Bestand_Menge       DECIMAL(18,4),

    Bestand_Wert        DECIMAL(18,2),

    Reichweite_Tage     INT,            -- nicht additiv

    UnterMeldebestand   BIT,

    _load_date          DATETIME2 DEFAULT SYSUTCDATETIME()

);

-- Reporting-Views: stabile Schnittstelle für Power BI

-- Schemaänderungen im DWH-Kern bleiben für Power BI unsichtbar

CREATE OR ALTER VIEW rpt.V_Umsatz_Detail AS

SELECT

    dd.Datum,

    dd.Jahr, dd.Quartal, dd.Monat, dd.MonatName,

    dd.KW_ISO, dd.FiskalJahr, dd.FiskalQuartal,

    dk.Name            AS Kunde,

    dk.Region          AS Kundenregion,

    dk.Umsatzklasse,

    da.Bezeichnung     AS Artikel,

    da.Warengruppe,

    da.Hauptgruppe,

    dm2.Vorname + ' ' + dm2.Nachname AS Vertreter,

    dm2.Region         AS Vertreterregion,

    f.AuftragsNr,

    f.Nettobetrag,

    f.Menge,

    f.Deckungsbeitrag,

    -- Berechnete Kennzahl: DB-Marge in % (nicht in Aggregationen summieren!)

    ROUND(f.Deckungsbeitrag / NULLIF(f.Nettobetrag,0) * 100, 2) AS DB_Marge_Pct

FROM dm.Fact_Auftraege f

JOIN dm.Dim_Datum       dd  ON f.DatumKey      = dd.DatumKey

JOIN dm.Dim_Kunden      dk  ON f.KundenKey     = dk.KundenKey

JOIN dm.Dim_Artikel     da  ON f.ArtikelKey    = da.ArtikelKey

JOIN dm.Dim_Mitarbeiter dm2 ON f.VertreterKey  = dm2.MitarbeiterKey;

-- Medaillon-Architektur in SQL Server (ohne Cloud-Lake)

-- Bronze: Rohdaten — unveränderlich, append-only, volle Quelltreue

CREATE SCHEMA bronze;

CREATE TABLE bronze.Auftraege_Raw (

    _row_id         INT IDENTITY PRIMARY KEY,

    _src_system     NVARCHAR(20) DEFAULT 'ERP',

    _loaded_at      DATETIME2    DEFAULT SYSUTCDATETIME(),

    _src_filename   NVARCHAR(200),          -- bei CSV/Datei-Importen

    -- Alle Felder als NVARCHAR: keine Transformation in Bronze

    AuftragsNr      NVARCHAR(100),

    KundenNr        NVARCHAR(100),

    Auftragsdatum   NVARCHAR(100),          -- Rohformat, kein CAST

    Nettobetrag     NVARCHAR(100),

    Status          NVARCHAR(100),

    Rohdaten_JSON   NVARCHAR(MAX)           -- vollständiger Quelldatensatz

);

-- Silver: Bereinigt — Typen korrekt, Nulls behandelt, Duplikate entfernt

CREATE SCHEMA silver;

CREATE OR ALTER VIEW silver.Auftraege AS

SELECT

    _row_id, _src_system, _loaded_at,

    TRY_CAST(AuftragsNr   AS INT)           AS auftrags_nr,

    TRY_CAST(KundenNr     AS INT)           AS kunden_nr,

    TRY_CAST(Auftragsdatum AS DATE)         AS auftragsdatum,

    TRY_CAST(Nettobetrag  AS DECIMAL(18,2)) AS nettobetrag,

    UPPER(TRIM(Status))                     AS status,

    -- Zeilen mit ungültigem Datum oder Betrag herausfiltern

    CASE WHEN TRY_CAST(Auftragsdatum AS DATE) IS NULL THEN 'UNGUELTIG_DATUM'

         WHEN TRY_CAST(Nettobetrag  AS DECIMAL(18,2)) IS NULL THEN 'UNGUELTIG_BETRAG'

         WHEN UPPER(TRIM(Status)) IN ('STORNO','ENTWURF') THEN 'AUSGESCHLOSSEN'

         ELSE 'OK' END                      AS _dq_status

FROM bronze.Auftraege_Raw

WHERE TRY_CAST(Auftragsdatum AS DATE) IS NOT NULL;

-- Gold: Kuratiert — aggregiert, angereichert, business-ready

CREATE SCHEMA gold;

CREATE OR ALTER VIEW gold.Umsatz_Monatlich AS

SELECT

    YEAR(auftragsdatum)     AS jahr,

    MONTH(auftragsdatum)    AS monat,

    SUM(nettobetrag)        AS umsatz_netto,

    COUNT(DISTINCT auftrags_nr) AS auftragsanzahl,

    COUNT(DISTINCT kunden_nr)   AS kundenanzahl

FROM silver.Auftraege

WHERE _dq_status = 'OK'

GROUP BY YEAR(auftragsdatum), MONTH(auftragsdatum);

-- Delta Lake Time Travel: historische Daten abrufen (Fabric / Databricks SQL)

-- Zustand der Tabelle vor 7 Tagen

SELECT * FROM silver.auftraege

TIMESTAMP AS OF DATEADD(DAY, -7, CURRENT_TIMESTAMP);

-- Zustand zu einer bestimmten Version

SELECT * FROM silver.auftraege VERSION AS OF 42;

-- Transaktionshistorie anzeigen

DESCRIBE HISTORY silver.auftraege;

-- Delta Optimize: kleine Dateien zusammenführen (wichtig für Performance)

OPTIMIZE silver.auftraege ZORDER BY (kunden_nr, auftragsdatum);

-- Vacuum: alte Versionen nach Aufbewahrungsfrist löschen (Default: 7 Tage)

VACUUM silver.auftraege RETAIN 168 HOURS;  -- 7 Tage

-- dbt-Modell: models/silver/stg_auftraege.sql

-- Normalisierung und Qualitätsfilterung (Silver-Schicht)

{{ config(materialized='incremental',

          unique_key='auftrags_nr',

          incremental_strategy='merge') }}

SELECT

    TRY_CAST(AuftragsNr  AS INT)            AS auftrags_nr,

    TRY_CAST(KundenNr    AS INT)            AS kunden_nr,

    TRY_CAST(Auftragsdatum AS DATE)         AS auftragsdatum,

    TRY_CAST(Nettobetrag AS DECIMAL(18,2))  AS nettobetrag,

    UPPER(TRIM(Status))                     AS status,

    _loaded_at,

    SYSUTCDATETIME()                        AS _transformiert_am

FROM {{ source('bronze', 'Auftraege_Raw') }}

WHERE TRY_CAST(Auftragsdatum AS DATE) IS NOT NULL

  AND TRY_CAST(Nettobetrag AS DECIMAL(18,2)) IS NOT NULL

  AND UPPER(TRIM(Status)) NOT IN ('STORNO','ENTWURF')

{% if is_incremental() %}

  AND _loaded_at > (SELECT MAX(_loaded_at) FROM {{ this }})

{% endif %}

-- schema.yml — Qualitätsprüfungen als Konfiguration, nicht als Code

-- version: 2

-- models:

--   - name: stg_auftraege

--     description: "Bereinigte Auftragsdaten aus dem ERP-System"

--     columns:

--       - name: auftrags_nr

--         description: "Eindeutige Auftragsnummer"

--         tests:

--           - unique

--           - not_null

--       - name: kunden_nr

--         tests:

--           - not_null

--           - relationships:

--               to: ref('dim_kunden')

--               field: kunden_nr_src

--       - name: nettobetrag

--         tests:

--           - not_null

--           - dbt_utils.accepted_range:

--               min_value: 0

--               max_value: 10000000

-- Eigener Test: Umsatz-Reconciliation DWH vs. ERP

-- tests/assert_umsatz_plausibel.sql

SELECT COUNT(*) AS fehler

FROM (

    SELECT

        YEAR(auftragsdatum)  AS jahr,

        MONTH(auftragsdatum) AS monat,

        SUM(nettobetrag)     AS dwh_umsatz

    FROM {{ ref('stg_auftraege') }}

    GROUP BY YEAR(auftragsdatum), MONTH(auftragsdatum)

) dwh

JOIN (

    SELECT

        YEAR(Auftragsdatum) AS jahr,

        MONTH(Auftragsdatum) AS monat,

        SUM(Nettobetrag) AS erp_umsatz

    FROM {{ source('erp_raw', 'Auftraege') }}

    WHERE Status NOT IN ('STORNO','ENTWURF')

    GROUP BY YEAR(Auftragsdatum), MONTH(Auftragsdatum)

) erp USING (jahr, monat)

WHERE ABS(dwh_umsatz - erp_umsatz) / NULLIF(erp_umsatz, 0) > 0.001

-- Jedes Modell bekommt eine Beschreibung — automatisch als Website generierbar

-- models/marts/mart_umsatz.yml (Auszug)

-- models:

--   - name: mart_umsatz_monatlich

--     description: >

--       Monatliche Umsatzkennzahlen je Region und Warengruppe.

--       Primäre Datenquelle für Power BI Umsatz-Dashboard.

--       Wird täglich um 07:00 Uhr aktualisiert.

--     meta:

--       owner: "controlling@unternehmen.de"

--       sla: "täglich bis 07:30 Uhr"

--       power_bi_dataset: "Umsatz-Dashboard"

--     columns:

--       - name: umsatz_netto

--         description: "Nettoumsatz nach Abzug aller Rabatte, ohne MwSt."

--         meta:

--           kpi_definition: "Gemäß Controlling-Handbuch §4.2, Stand 2025-01"

--       - name: db_marge_pct

--         description: "Deckungsbeitrag I in Prozent des Nettoumsatzes"

--         meta:

--           nicht_additiv: true

--           power_bi_hinweis: "Nicht summieren — als gewichteter Durchschnitt berechnen"

-- Fabric Warehouse: volles T-SQL auf Lakehouse-Daten

-- Dieselbe Abfrage läuft auf Delta-Dateien im Lakehouse

SELECT

    YEAR(auftragsdatum)     AS Jahr,

    dk.Region,

    SUM(f.nettobetrag)      AS Umsatz,

    COUNT(DISTINCT f.kunden_nr) AS Kunden

FROM lakehouse.silver_auftraege f

JOIN lakehouse.dim_kunden dk

    ON f.kunden_nr = dk.kunden_nr AND dk._is_current = 1

WHERE auftragsdatum >= '2025-01-01'

GROUP BY YEAR(auftragsdatum), dk.Region

ORDER BY Umsatz DESC;

-- Fabric optimiert automatisch ob Spark oder SQL Engine günstiger ist

KMU, SQL Server On-Prem, < 100 GB

DWH auf SQL Server + dbt

Kein Cloud-Budget, bewährt, volle Kontrolle

KMU, M365/Power BI Premium vorhanden

Microsoft Fabric

Lizenz inklusive, kein Extra-Budget, unified platform

KMU, Azure bereits genutzt

Azure Synapse oder Fabric

Synapse für mehr Kontrolle, Fabric für weniger Komplexität

KMU, Databricks-Affinität

Databricks Lakehouse + dbt

Wenn Python/Spark im Team vorhanden

KMU, Greenfield ohne Cloud

SQL Server + Medaillon-Schema

Einfachster Einstieg ohne neue Technologie

-- Einfacher Datenkatalog ohne Azure Purview

CREATE TABLE catalog.Objekte (

    ObjektID        INT IDENTITY PRIMARY KEY,

    Schema_Name     NVARCHAR(50),

    Objekt_Name     NVARCHAR(200),

    Objekt_Typ      NVARCHAR(20),    -- TABELLE / VIEW / FUNKTION / PIPELINE

    Schicht         NVARCHAR(10),    -- BRONZE / SILVER / GOLD / RPT

    Beschreibung    NVARCHAR(MAX),

    Owner_Email     NVARCHAR(200),

    SLA             NVARCHAR(100),   -- 'täglich bis 07:30 Uhr'

    Quellsysteme    NVARCHAR(500),   -- 'ERP, CRM'

    Downstream      NVARCHAR(500),   -- 'Power BI Umsatz-Dashboard'

    Aktualisierung  NVARCHAR(50),    -- 'Täglich 06:00 Uhr'

    Letzte_Pruefung DATE,

    Qualitaet       NVARCHAR(10) DEFAULT 'UNGEPRUEFT',

    Erstellt        DATETIME2 DEFAULT SYSUTCDATETIME(),

    GeaendertAm     DATETIME2 DEFAULT SYSUTCDATETIME()

);

-- DWH-Freshness-Monitoring: wann wurde welches Objekt zuletzt geladen?

CREATE OR ALTER VIEW catalog.V_Freshness_Status AS

SELECT

    c.Schema_Name + '.' + c.Objekt_Name    AS Objekt,

    c.Schicht,

    c.SLA,

    c.Owner_Email,

    -- Letztes Ladedatum aus _load_date Spalte

    (SELECT MAX(_load_date) FROM dm.Fact_Auftraege)  AS Letzte_Ladung,

    DATEDIFF(HOUR,

        (SELECT MAX(_load_date) FROM dm.Fact_Auftraege),

        GETDATE())                          AS Stunden_Zurueck,

    CASE

        WHEN DATEDIFF(HOUR,

            (SELECT MAX(_load_date) FROM dm.Fact_Auftraege),

            GETDATE()) < 25   THEN 'GRUEN'

        WHEN DATEDIFF(HOUR,

            (SELECT MAX(_load_date) FROM dm.Fact_Auftraege),

            GETDATE()) < 49   THEN 'GELB'

        ELSE                       'ROT'

    END                                     AS Freshness_Ampel

FROM catalog.Objekte c

WHERE c.Objekt_Name = 'Fact_Auftraege';

-- Kostenmonitor: Azure-Kosten täglich protokollieren (via Azure Cost Management API)

CREATE TABLE monitor.Azure_Kosten (

    KostenID        INT IDENTITY PRIMARY KEY,

    Datum           DATE,

    Dienst          NVARCHAR(100),   -- 'Azure Synapse', 'ADLS2', 'Fabric'

    Ressource       NVARCHAR(200),

    Kosten_EUR      DECIMAL(10,4),

    Einheit         NVARCHAR(50),    -- 'DWU-Stunden', 'TB', 'vCore-Stunden'

    Budget_EUR      DECIMAL(10,2),   -- monatliches Budget für diesen Dienst

    Budget_Verbrauch_Pct AS

        ROUND(Kosten_EUR / NULLIF(Budget_EUR / 30.0, 0) * 100, 1) PERSISTED

);

-- Alert wenn Tageskosten > 110% des Budget-Tagesdurchschnitts

-- Wird täglich als SQL Agent Job ausgeführt

SELECT

    Dienst,

    SUM(Kosten_EUR)     AS Heute_EUR,

    MAX(Budget_EUR)/30  AS Budget_Tag_EUR,

    CASE WHEN SUM(Kosten_EUR) > MAX(Budget_EUR)/30 * 1.1

         THEN 'BUDGET-ALERT: ' + Dienst + ' überschreitet Tagesbudget!'

         ELSE 'OK' END AS Status

FROM monitor.Azure_Kosten

WHERE Datum = CAST(GETDATE() AS DATE)

GROUP BY Dienst

HAVING SUM(Kosten_EUR) > MAX(Budget_EUR)/30 * 1.1;

VOR DEM START

Wählen Sie einen einzigen Fachbereich als Piloten — Controlling/Umsatz ist fast immer der beste Einstieg. Erstellen Sie ein vollständiges Backup aller betroffenen Quellsysteme. Benennen Sie einen technischen Verantwortlichen (Data Engineer) und einen fachlichen Sponsor (Controller oder CFO). Führen Sie den Entscheidungsmatrix-Check aus Kapitel 3 durch — die Architekturwahl steht vor dem ersten Code.

ERGEBNIS NACH 30 TAGEN

Ihr Ergebnis nach 30 Tagen: Power BI greift nicht mehr direkt auf Produktivdatenbanken zu, eine vollständige Historisierung der Kundendimension ist vorhanden, tägliche Reconciliation sichert Datenqualität automatisch, ein Aggregations-Layer macht Power-BI-Refresh schnell und ein Datenkatalog dokumentiert das neue Fundament. Das ist der solide Ausgangspunkt für alle weiteren BI-Ausbauschritte.

BI-Architektur-Review

Analyse Ihrer bestehenden Datenarchitektur — DWH-Assessment, Architekturempfehlung (DWH vs. Lakehouse), Modernisierungsfahrplan. Scope: 3-5 Tage.

Data Warehouse Aufbau

Implementierung eines produktionsfertigen Star-Schema-DWH auf SQL Server oder Microsoft Fabric — mit Staging, SCD Typ 2, Aggregations-Layer und Power-BI-Anbindung.

Lakehouse Migration

Migration von bestehenden DWH- oder BI-Strukturen in eine Medaillon-Architektur auf Fabric oder Azure Synapse — inkl. dbt-Einführung und Wissenstransfer.

BI-Dashboard-Aufbau

Power BI / DeltaMaster Dashboards inkl. ETL-Strecken und strukturiertem Data-Warehouse-Fundament — keine Direktverbindungen auf Produktivdatenbanken.

Interim IT-Management

Übernahme der IT-Steuerung auf Zeit — Budgetplanung, Dienstleister-Management, strategische IT- und Datenarchitektur-Ausrichtung.