BI Services

Datumstabelle mit T-SQL: Komplettskript für DWH und Power BI

Der Umsatz steigt – nur ausgerechnet in Kalenderwoche 1 scheint Ihr Unternehmen jedes Jahr ein paar Tage zu verlieren. Die Ursache liegt nicht in der Buchhaltung: Manche Januartage gehören noch zur letzten ISO-Kalenderwoche des Vorjahres. Wer Kalenderjahr und Kalenderwoche unbedacht kombiniert, verschiebt Geschäftszahlen in die falsche Schublade.

Eine zentrale Datumstabelle verhindert solche Überraschungen. Sie funktioniert wie ein gemeinsamer Kalender für das gesamte Data Warehouse: Jede Fact Table, also jede Tabelle mit Geschäftsvorgängen und Kennzahlen, verwendet dieselbe Zeitlogik. Power BI bekommt dadurch eine verlässliche Grundlage für Zeitvergleiche, Filter und Measures.

Anforderungen: lückenlos, eindeutig und passend typisiert

Eine Datumstabelle enthält genau eine Zeile je Kalendertag – auch für Sonntage ohne Umsatz. Lücken erschweren Zeitvergleiche und sind bei einer klassischen, als Datumstabelle markierten Power-BI-Tabelle nicht zulässig.

Drei Entscheidungen gehören an den Anfang:

  • Zeitraum: Decken Sie sämtliche Geschäftsdaten ab, möglichst in vollständigen Jahren und mit Reserve für Planwerte.
  • Datumsschlüssel: Ein int im Format YYYYMMDD lässt sich in Fact Tables leicht verwenden. Er ist jedoch keine Rechengröße: Zwischen 20250131 und 20250201 liegt ein Tag, nicht 70.
  • Datumswert: Eine zusätzliche Spalte vom Typ date speichert das echte Datum ohne Uhrzeit. Diese Spalte benötigt Power BI für die Datumslogik.

Das folgende Skript arbeitet mit SQL Server ab 2012. Es erzeugt bewusst keine unbekannte Ersatzzeile innerhalb der Datumstabelle. Fehlende Datumszuordnungen sollten Sie im Ladeprozess gesondert behandeln, statt den lückenlosen Kalender mit einem künstlichen Datum zu vermischen.

Jahr, Quartal und Monat mit Sortierspalten modellieren

Monatsnamen sind lesbar, aber alphabetisch sortiert landet „April“ vor „Februar“. Deshalb bekommen Beschriftungen numerische Begleiter: Monatsname wird nach Monat sortiert.

Über Jahresgrenzen reichen Monatsnummern nicht aus. JahrMonat, beispielsweise 202503, und JahrQuartal, beispielsweise 20251, liefern eindeutige Sortier- und Gruppierschlüssel. MonatJahr zeigt ergänzend eine lesbare Beschriftung wie 2025-03.

Die deutschen Namen setzt das Skript ausdrücklich fest. Damit bleiben die Ergebnisse gleich, unabhängig davon, welche Sprache für die SQL-Verbindung eingestellt ist.

ISO-Kalenderwoche und ISO-Jahr korrekt berechnen

Die ISO-Woche beginnt montags. Kalenderwoche 1 ist die Woche mit dem ersten Donnerstag des Jahres.

Daraus folgt die entscheidende Regel: Das ISO-Jahr ist das Kalenderjahr des Donnerstags derselben Woche. Der 1. Januar 2021 gehört deshalb zu ISO-Woche 53 des ISO-Jahres 2020.

DATEPART(ISO_WEEK, Datum) liefert die passende Wochennummer. Für das ISO-Jahr verschiebt das Skript das Datum auf den zugehörigen Donnerstag. ISOJahrWoche kombiniert anschließend beide Werte. Verwenden Sie niemals einfach Kalenderjahr plus ISO-Wochennummer.

Deutsche Wochentage ohne Sprachabhängigkeit

DATENAME hängt von der Sitzungssprache ab; DATEPART(WEEKDAY, ...) wird durch SET DATEFIRST beeinflusst.

Stattdessen zählt das Skript die Tage seit dem 1. Januar 1900, einem Montag. Eine normalisierte Restwertberechnung ergibt Montag = 1 bis Sonntag = 7 – auch bei Daten vor diesem Ankerdatum.

Damit lassen sich deutsche Wochentage und das Wochenendkennzeichen unabhängig von Sitzungseinstellungen ableiten.

Geschäftsjahr und regionale Feiertage ergänzen

Der Parameter @GeschaeftsjahrStartmonat bestimmt den Beginn des Geschäftsjahres. Bei Startmonat 7 zählt Juli als Geschäftsmonat 1.

Hier bezeichnet Geschaeftsjahr ausdrücklich das Startjahr: Juli 2025 bis Juni 2026 gehört zu Geschäftsjahr 2025. Wenn Ihr Controlling nach dem Endjahr benennt, müssen Sie diese Konvention entsprechend ändern.

Feiertage sind dagegen keine universelle Datumseigenschaft. Ein Datum kann in Bayern Feiertag sein und in Hamburg Arbeitstag. Modellieren Sie deshalb bei mehreren Standorten eine zusätzliche Tabelle mit Datum, Region und Feiertagsname. Erst daraus entstehen regionale Arbeitstagskennzeichen. Bewegliche Feiertage und betriebliche Schließtage brauchen ebenfalls gepflegte Regeln oder eine verlässliche Datenquelle.

Vollständiges T-SQL-Skript

Passen Sie die drei Parameter an. Das Skript legt dbo.DimDatum neu an und bricht ab, wenn die Tabelle bereits existiert. So werden vorhandene Beziehungen und Daten nicht versehentlich gelöscht.

Die Befüllung erfolgt mengenbasiert, ohne Schleife und ohne rekursiven CTE. Ein CTE ist ein benannter Zwischenausdruck innerhalb einer SQL-Anweisung.

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @Startdatum date = '20200101';
DECLARE @Enddatum date = '20351231';
DECLARE @GeschaeftsjahrStartmonat int = 7;

IF @Startdatum IS NULL
   OR @Enddatum IS NULL
   OR @Startdatum > @Enddatum
BEGIN
    THROW 50001, N'Ungueltiger Datumsbereich.', 1;
END;

IF @GeschaeftsjahrStartmonat IS NULL
   OR @GeschaeftsjahrStartmonat NOT BETWEEN 1 AND 12
BEGIN
    THROW 50002, N'Der Startmonat muss zwischen 1 und 12 liegen.', 1;
END;

IF OBJECT_ID(N'dbo.DimDatum') IS NOT NULL
BEGIN
    THROW 50003, N'dbo.DimDatum existiert bereits.', 1;
END;

DECLARE @AnzahlTage int =
    DATEDIFF(day, @Startdatum, @Enddatum) + 1;

BEGIN TRY
    BEGIN TRANSACTION;

    CREATE TABLE dbo.DimDatum
    (
        Datumsschluessel int NOT NULL
            CONSTRAINT PK_DimDatum PRIMARY KEY,
        Datum date NOT NULL
            CONSTRAINT UQ_DimDatum_Datum UNIQUE,
        Jahr smallint NOT NULL,
        Quartal tinyint NOT NULL,
        Quartalsname nvarchar(2) NOT NULL,
        JahrQuartal int NOT NULL,
        Monat tinyint NOT NULL,
        Monatsname nvarchar(9) NOT NULL,
        JahrMonat int NOT NULL,
        MonatJahr char(7) NOT NULL,
        ISOJahr smallint NOT NULL,
        ISOKalenderwoche tinyint NOT NULL,
        ISOJahrWoche int NOT NULL,
        ISOWochentag tinyint NOT NULL,
        Wochentag nvarchar(10) NOT NULL,
        IstWochenende bit NOT NULL,
        Geschaeftsjahr smallint NOT NULL,
        Geschaeftsmonat tinyint NOT NULL,
        Geschaeftsquartal tinyint NOT NULL
    );

    ;WITH Ziffer AS
    (
        SELECT n
        FROM (VALUES (0),(1),(2),(3),(4),
                     (5),(6),(7),(8),(9)) AS v(n)
    ),
    Nummern AS
    (
        SELECT TOP (@AnzahlTage)
            CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
                 AS int) AS n
        FROM Ziffer AS a
        CROSS JOIN Ziffer AS b
        CROSS JOIN Ziffer AS c
        CROSS JOIN Ziffer AS d
        CROSS JOIN Ziffer AS e
        CROSS JOIN Ziffer AS f
        CROSS JOIN Ziffer AS g
    ),
    Tage AS
    (
        SELECT DATEADD(day, n, @Startdatum) AS Datum
        FROM Nummern
    ),
    Basis AS
    (
        SELECT
            Datum,
            YEAR(Datum) AS Jahr,
            MONTH(Datum) AS Monat,
            DATEPART(quarter, Datum) AS Quartal,
            DATEPART(ISO_WEEK, Datum) AS ISOKW,
            ((DATEDIFF(day, CONVERT(date, '19000101', 112),
                       Datum) % 7 + 7) % 7) + 1 AS ISOTag
        FROM Tage
    ),
    Attribute AS
    (
        SELECT *,
            YEAR(DATEADD(day, 4 - ISOTag, Datum)) AS ISOJahr,
            Jahr - CASE
                WHEN Monat < @GeschaeftsjahrStartmonat
                THEN 1 ELSE 0 END AS Geschaeftsjahr,
            (Monat - @GeschaeftsjahrStartmonat + 12) % 12 + 1
                AS Geschaeftsmonat
        FROM Basis
    )
    INSERT INTO dbo.DimDatum
    (
        Datumsschluessel, Datum, Jahr,
        Quartal, Quartalsname, JahrQuartal,
        Monat, Monatsname, JahrMonat, MonatJahr,
        ISOJahr, ISOKalenderwoche, ISOJahrWoche,
        ISOWochentag, Wochentag, IstWochenende,
        Geschaeftsjahr, Geschaeftsmonat, Geschaeftsquartal
    )
    SELECT
        Jahr * 10000 + Monat * 100 + DAY(Datum),
        Datum,
        Jahr,
        Quartal,
        CONCAT(N'Q', Quartal),
        Jahr * 10 + Quartal,
        Monat,
        CHOOSE(Monat,
            N'Januar', N'Februar', N'März', N'April',
            N'Mai', N'Juni', N'Juli', N'August',
            N'September', N'Oktober', N'November', N'Dezember'),
        Jahr * 100 + Monat,
        CONVERT(char(7), Datum, 126),
        ISOJahr,
        ISOKW,
        ISOJahr * 100 + ISOKW,
        ISOTag,
        CHOOSE(ISOTag,
            N'Montag', N'Dienstag', N'Mittwoch',
            N'Donnerstag', N'Freitag', N'Samstag', N'Sonntag'),
        CASE WHEN ISOTag >= 6 THEN 1 ELSE 0 END,
        Geschaeftsjahr,
        Geschaeftsmonat,
        (Geschaeftsmonat - 1) / 3 + 1
    FROM Attribute;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Die sieben Ziffernmengen stellen genügend Nummern selbst für den gesamten Wertebereich des Datentyps date bereit. TOP begrenzt die tatsächlich benötigte Folge. Eine physische Zeilenreihenfolge garantiert das nicht: Abfragen benötigen weiterhin ORDER BY.

Ergebnisse prüfen und in Power BI einbinden

Kontrollieren Sie zuerst die Vollständigkeit. Bei eindeutigen Datumswerten müssen Zeilenanzahl und erwartete Tage übereinstimmen:

SELECT
    MIN(Datum) AS ErstesDatum,
    MAX(Datum) AS LetztesDatum,
    COUNT(*) AS VorhandeneTage,
    DATEDIFF(day, MIN(Datum), MAX(Datum)) + 1 AS ErwarteteTage
FROM dbo.DimDatum;

Prüfen Sie Jahreswechsel gezielt gegen bekannte Sollwerte. Diese Abfrage meldet auch fehlende Testdaten:

SELECT
    t.Datum,
    t.ISOJahr AS ErwartetesISOJahr,
    t.ISOKW AS ErwarteteISOKW,
    d.ISOJahr,
    d.ISOKalenderwoche,
    CASE
        WHEN d.ISOJahr = t.ISOJahr
         AND d.ISOKalenderwoche = t.ISOKW
        THEN N'OK' ELSE N'Prüfen'
    END AS Ergebnis
FROM (VALUES
    (CONVERT(date, '20210101', 112), 2020, 53),
    (CONVERT(date, '20210104', 112), 2021, 1),
    (CONVERT(date, '20241230', 112), 2025, 1)
) AS t(Datum, ISOJahr, ISOKW)
LEFT JOIN dbo.DimDatum AS d ON d.Datum = t.Datum
ORDER BY t.Datum;

In Power BI gehen Sie anschließend so vor:

  1. Laden Sie dbo.DimDatum und markieren Sie sie als Datumstabelle mit der Spalte Datum.
  2. Verbinden Sie Datumsschluessel über eine 1:n-Beziehung mit dem entsprechenden Schlüssel der Fact Table; normalerweise mit einfacher Filterrichtung zur Fact Table.
  3. Sortieren Sie Monatsname nach Monat, Wochentag nach ISOWochentag und MonatJahr nach JahrMonat.
  4. Verwenden Sie für ISO-Wochenauswertungen ISOJahr statt Jahr.

Ein häufiger Projektfehler: Ein Zeitstempel mit Uhrzeit wird direkt mit Datum verknüpft. Bereiten Sie den passenden Datumsschlüssel bereits beim Laden auf. Bei Bestell-, Liefer- und Rechnungsdatum müssen Sie außerdem bewusst zwischen aktiven beziehungsweise inaktiven Beziehungen oder separaten Rollendimensionen entscheiden.

Fazit

Eine gute Datumstabelle ist der gemeinsame Taktgeber Ihrer Auswertungen. Lückenlose Tage, sprachunabhängige Attribute und korrekt getrennte Kalender- und ISO-Jahre verhindern stille Analysefehler. Mit klar dokumentiertem Geschäftsjahr und regional gepflegten Feiertagen wird daraus eine belastbare Grundlage für DWH und Power BI.

Sie möchten tiefer einsteigen? In unseren Schulungen vermitteln wir SQL Server, Power BI und KI praxisnah – online oder bei Ihnen vor Ort. Fragen? Kontaktieren Sie uns oder buchen Sie ein kostenloses Info-Gespräch.