Verwendungsszenarien für temporale Tabellen

Gilt für: SQL Server 2016 (13.x) und spätere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL database in Microsoft Fabric

Systemversionierte temporale Tabellen sind für Szenarien nützlich, in denen der Verlauf von Datenänderungen nachverfolgt werden muss. Für maßgebliche Produktivitätsvorteile empfehlen wir Ihnen den Einsatz temporaler Tabellen in den folgenden Anwendungsfällen.

Datenüberwachung

Sie können die temporale Systemversionsverwaltung für Tabellen verwenden, in denen wichtige Informationen gespeichert werden, um nachzuverfolgen, was sich wann geändert hat, und um Datenforensik zu einem beliebigen Zeitpunkt durchzuführen.

Verwenden Sie temporale Tabellen, um Daten-Audit-Szenarien in den frühen Entwicklungsphasen zu planen. Sie können Datenprüfungen zu bestehenden Anwendungen oder Lösungen hinzufügen, wenn Sie es benötigen.

Das folgende Diagramm zeigt eine Employee-Tabelle mit einem Datenbeispiel, das aktuelle (blau markierte) und historische Zeilenversionen (grau markiert) enthält.

Der rechte Teil des Diagramms visualisiert Zeilenversionen auf einer Zeitachse und die ausgewählten Zeilen mit verschiedenen Abfragetypen in einer temporalen Tabelle, mit oder ohne Klausel SYSTEM_TIME .

Diagramm, das das erste Szenario zur temporalen Verwendung zeigt.

Systemversionierung für eine neue Tabelle zur Datenprüfung aktivieren

Wenn Sie Informationen ermitteln, die eine Datenüberwachung erfordern, erstellen Sie Datenbanktabellen als systemversionierte temporale Tabellen. Das folgende Beispiel veranschaulicht ein Szenario mit einer Tabelle namens Employee in einer hypothetischen HR-Datenbank:

CREATE TABLE Employee
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR (100) NOT NULL,
    [Position] VARCHAR (100) NOT NULL,
    [Department] VARCHAR (100) NOT NULL,
    [Address] NVARCHAR (1024) NOT NULL,
    [AnnualSalary] DECIMAL (10, 2) NOT NULL,
    [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Verschiedene Optionen zur Erstellung einer zeitlich systemversionierten Tabelle sind in Create a system-versioned temporal table beschrieben.

Systemversionierung für eine vorhandene Tabelle zur Datenauditierung aktivieren

Wenn Sie in bestehenden Datenbanken eine Datenprüfung durchführen müssen, verwenden Sie ALTER TABLE, um nichttemporale Tabellen in systemversionierte Tabellen umzuwandeln. Um änderungsbedingte Probleme in Ihrer Anwendung zu vermeiden, fügen Sie Periodenspalten als HIDDEN hinzu, wie unter Erstellen einer systemversionierten temporalen Tabelle erläutert.

Das folgende Beispiel veranschaulicht die Aktivierung der Systemversionsverwaltung für eine vorhandene Employee-Tabelle in einer hypothetischen Personaldatenbank. Die Systemversionsverwaltung wird in der Employee-Tabelle in zwei Schritten aktiviert. Zuerst werden neue Zeitraumspalten als HIDDEN hinzugefügt. Anschließend wird die Standardverlaufstabelle erstellt.

ALTER TABLE Employee
ADD
    ValidFrom DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Employee
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Important

Die Genauigkeit des Datentyps datetime2 muss in der Quelltabelle dieselbe sein wie in der systemversionierten Verlaufstabelle.

Nachdem Sie das vorherige Skript ausgeführt haben, sammelt die History-Tabelle alle Datenänderungen transparent. In einem typischen Daten-Audit-Szenario fragen Sie nach allen Datenänderungen, die innerhalb eines relevanten Zeitraums auf eine einzelne Zeile angewendet werden. Die Standardverlaufstabelle wird standardmäßig mit einem gruppierten Rowstore-B-Baum erstellt, um diesen Anwendungsfall effizient zu unterstützen.

Note

In der Dokumentation wird der Begriff „B-tree“ im Allgemeinen im Zusammenhang mit Indizes verwendet. In Rowstore-Indizes implementiert die Datenbank-Engine einen B+-Baum. Dies gilt nicht für Columnstore-Indizes oder Indizes auf speicheroptimierten Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Durchführen einer Datenanalyse

Nachdem Sie die Systemversionierung mit einem der beiden vorherigen Ansätze aktiviert haben, ist die Datenprüfung mit nur einer Abfrage möglich. Die folgende Abfrage sucht nach Zeilenversionen für Datensätze in der Employee-Tabelle mit EmployeeID = 1000, die zwischen dem 1. Januar 2021 und dem 1. Januar 2022 (einschließlich der oberen Grenze) mindestens eine Zeit lang aktiv waren:

SELECT *
FROM Employee FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Ersetzen Sie FOR SYSTEM_TIME BETWEEN...AND durch FOR SYSTEM_TIME ALL, um den gesamten Verlauf der Datenänderungen für diesen bestimmten Mitarbeiter zu analysieren:

SELECT *
FROM Employee FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Verwenden Sie CONTAINED IN, um nach Zeilenversionen zu suchen, die nur innerhalb eines bestimmten Zeitraums aktiv waren (und nicht außerhalb). Diese Abfrage ist effizient, da sie nur die Verlaufstabelle abfragt:

SELECT *
FROM Employee FOR SYSTEM_TIME
    CONTAINED IN ('2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000')
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Schließlich möchten Sie in einigen Prüfungsszenarien sehen, wie die gesamte Tabelle zu einem bestimmten Zeitpunkt in der Vergangenheit aussah:

SELECT *
FROM Employee FOR SYSTEM_TIME
    AS OF '2021-01-01 00:00:00.0000000';

Systemversionierte temporale Tabellen speichern Werte für Zeitraumspalten in der UTC-Zeitzone, es kann jedoch praktischer sein, in Ihrer lokalen Zeitzone zu arbeiten, sowohl zum Filtern von Daten als auch zum Anzeigen von Ergebnissen. Das folgende Codebeispiel zeigt, wie eine Filterbedingung angewendet wird, die in der lokalen Zeitzone angegeben und dann in UTC umgewandelt wird, indem verwendet wird AT TIME ZONE:

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, -9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
       [Name],
       Position,
       Department,
       [Address],
       [AnnualSalary],
       ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
       ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

Die Verwendung von AT TIME ZONE ist in allen anderen Szenarien hilfreich, in denen systemversionierte Tabellen verwendet werden.

In temporalen Klauseln angegebene Filterbedingungen mit FOR SYSTEM_TIME sind SARGable.

Note

Der Begriff SARGable in relationalen Datenbanken bezeichnet ein Prädikat, das Search-ARGumentable ist und einen Index verwenden kann, um die Abfrageausführung zu beschleunigen. Weitere Informationen finden Sie in der SQL Server- und Azure SQL-Indexarchitektur und im Entwurfshandbuch.

Wenn Sie die Versionstabelle direkt abfragen, stellen Sie sicher, dass Ihre Filterbedingung ebenfalls SARGable ist, indem Sie Filter in der Form von <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'spezifizieren.

Wenn Sie AT TIME ZONE auf Zeitraumspalten anwenden, führt SQL Server einen Tabellen- oder Indexscan durch, was kostspielig sein kann. Vermeiden Sie folgenden Bedingungstyp in Ihren Abfragen:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

Weitere Informationen finden Sie unter Daten in einer systemversionierten temporalen Tabelle abfragen.

Zeitpunktbezogene Analyse (Zeitreise)

Anstatt sich auf Änderungen einzelner Datensätze zu konzentrieren, zeigen Zeitreiseszenarien, wie sich ganze Datensätze im Laufe der Zeit verändern. Manchmal umfasst die Zeitreise mehrere zusammengehörige temporale Tabellen, die sich jeweils unabhängig voneinander ändern und die Sie analysieren möchten:

  • Trends für wichtige Indikatoren in den Verlaufsdaten und aktuellen Daten
  • Exakte Momentaufnahme sämtlicher Daten zu einem beliebigen Zeitpunkt in der Vergangenheit (gestern, vor einem Monat usw.)
  • Unterschiede zwischen zwei interessanten Zeitpunkten (z. B. vor einem Monat im Vergleich zu vor drei Monaten)

Viele reale Szenarien erfordern eine Zeitreiseanalyse. Um dieses Anwendungsszenario zu veranschaulichen, schauen wir uns die Online-Transaktionsverarbeitung (OLTP) mit automatisch generierter Historie an.

OLTP mit automatisch generiertem Datenverlauf

In Transaktionsverarbeitungssystemen können Sie die Änderung von wichtigen Metriken im Verlauf der Zeit zu analysieren. Idealerweise sollte die Analyse des Verlaufs die Leistung der OLTP-Anwendung nicht beeinträchtigen, wenn der Zugriff auf den neuesten Datenzustand mit minimaler Latenz und Datensperrung erfolgen muss. Sie können systemversionierte temporale Tabellen verwenden, um den vollständigen Änderungsverlauf für spätere Analysen getrennt von den aktuellen Daten automatisch zu speichern, und das bei minimalen Auswirkungen auf die primäre OLTP-Arbeitslast.

Für Workloads mit hoher transaktionaler Verarbeitung in SQL Server und Azure SQL Managed Instance empfehlen wir, systemversionierte temporale Tabellen mit speicheroptimierten Tabellen zu verwenden, die es ermöglichen, aktuelle Daten im Speicher und die vollständige Versionshistorie der Änderungen auf der Festplatte kosteneffizient zu speichern.

Für die Verlaufstabelle empfehlen wir aus den folgenden Gründen die Verwendung eines gruppierten Columnstore-Indexes:

  • Die typische Trendanalyse profitiert von der Abfrageleistung, die von einem gruppierten Columnstore-Index bereitgestellt wird.

  • Die Datenleerungsaufgabe mit speicheroptimierten Tabellen funktioniert am besten bei hoher OLTP-Arbeitsauslastung, wenn die Verlaufstabelle über einen gruppierten Columnstore-Index verfügt.

  • Ein gruppierter Columnstore-Index bietet eine hervorragende Komprimierung, besonders in Szenarien, in denen nicht alle Spalten gleichzeitig geändert werden.

Die Verwendung von temporalen Tabellen mit In-Memory-OLTP reduziert die Notwendigkeit, den gesamten Datensatz im Speicher zu halten, und ermöglicht es Ihnen, leicht zwischen heißen und kalten Daten zu unterscheiden.

Beispiele für reale Szenarien, die gut in diese Kategorie passen, sind u. a. die Bestandsverwaltung oder der Devisenhandel.

Das folgende Diagramm zeigt ein vereinfachtes Datenmodell, das für das Bestandsmanagement verwendet wird:

Diagramm: Vereinfachtes Datenmodell für die Lagerverwaltung

Das folgende Codebeispiel erstellt ProductInventory als eine im Speicher systemversionierte temporale Tabelle mit einem clusterierten Kolumnenspeicherindex in der Versionstabelle (der den standardmäßig erstellten Zeilenspeicherindex ersetzt):

Note

Stellen Sie sicher, dass die Datenbank die Erstellung von speicheroptimierten Tabellen ermöglicht. Weitere Informationen finden Sie unter Erstellen einer speicheroptimierten Tabelle und einer systemintern kompilierten gespeicherten Prozedur.

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
            SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];

END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
    ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

Für das vorangehende Modell kann die Prozedur zum Verwalten des Bestands folgendermaßen aussehen:

CREATE PROCEDURE [dbo].[spUpdateInventory] (
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    UPDATE dbo.ProductInventory
        SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId;

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location
    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
        BEGIN
            SET @quantityIncrement = 0;
        END

        INSERT INTO [dbo].[ProductInventory]
        (
            [ProductId],
            [LocationID],
            [Quantity]
        )
        VALUES (
            @productId,
            @locationId,
            @quantityIncrement
        );
    END
END;

Die gespeicherte Prozedur spUpdateInventory fügt entweder ein neues Produkt im Bestand ein oder aktualisiert die Produktmenge für den jeweiligen Standort. Die Geschäftslogik ist einfach und darauf ausgerichtet, den neuesten Zustand jederzeit korrekt zu halten, indem das Feld Quantity durch eine Tabellenaktualisierung inkrementiert bzw. dekrementiert wird, während systemversionierte Tabellen den Daten transparent eine Historieebene hinzufügen, wie im folgenden Diagramm dargestellt.

Diagramm zur Darstellung der zeitlichen Nutzung mit aktueller In-Memory-Nutzung und historischer Nutzung in einem gruppierten Columnstore-Index.

Jetzt können Sie effizient den neuesten Zustand aus dem nativ kompilierten Modul abfragen:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT ProductId,
           LocationID,
           Quantity,
           ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId;
END;
GO

EXECUTE [dbo].[spQueryInventoryLatestState];

Die Analyse von Datenänderungen im Verlauf der Zeit wird mit der FOR SYSTEM_TIME ALL-Klausel zum Kinderspiel, wie im folgenden Beispiel gezeigt:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory AS
    SELECT ProductId,
           LocationId,
           Quantity,
           ValidFrom,
           ValidTo
    FROM [dbo].[ProductInventory] FOR SYSTEM_TIME ALL;
GO

SELECT *
FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

Das folgende Diagramm zeigt den Datenverlauf für ein Produkt, der problemlos durch den Import der vorangehenden Ansicht in Power Query, Power BI oder ähnlichen Business-Intelligence-Tools gerendert werden kann:

Diagramm: Datenverlauf für ein Produkt

In diesem Szenario können Sie zeitliche Tabellen verwenden, um andere Arten von Zeitreiseanalysen durchzuführen, wie zum Beispiel den Zustand des Inventars AS OF zu einem beliebigen Zeitpunkt in der Vergangenheit zu rekonstruieren oder Schnappschüsse zu vergleichen, die zu verschiedenen Zeitpunkten gehören.

Für dieses Anwendungsszenario kann man auch die Product und-Tabellen Location zu temporalen Tabellen erweitern, um eine spätere Analyse der Änderungshistorie von UnitPrice und NumberOfEmployeezu ermöglichen.

ALTER TABLE Product
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Product
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location]
ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DFValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE [Location]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Da das Datenmodell nun mehrere temporale Tabellen umfasst, besteht die beste Analysemethode darin AS OF , eine Ansicht zu erstellen, die notwendige Daten aus den zugehörigen Tabellen extrahiert und auf die Ansicht anwendet FOR SYSTEM_TIME AS OF , da dies die Rekonstruktion des Zustands des gesamten Datenmodells erheblich vereinfacht:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
       PrInv.LocationId,
       p.ProductName,
       l.LocationName,
       PrInv.Quantity,
       p.UnitPrice,
       l.NumberOfEmployees,
       p.ValidFrom AS ProductStartTime,
       p.ValidTo AS ProductEndTime,
       l.ValidFrom AS LocationStartTime,
       l.ValidTo AS LocationEndTime,
       PrInv.ValidFrom AS InventoryStartTime,
       PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
    INNER JOIN dbo.Product AS p
        ON PrInv.ProductId = p.ProductID
    INNER JOIN dbo.Location AS l
        ON PrInv.LocationId = l.LocationID;
GO

SELECT *
FROM vw_ProductInventoryDetails
    FOR SYSTEM_TIME AS OF '2022-01-01';

Der folgende Screenshot zeigt den Ausführungsplan, der für die SELECT-Abfrage generiert wird. Dies veranschaulicht, dass die Datenbank-Engine die gesamte Komplexität im Umgang mit temporalen Beziehungen übernimmt:

Diagramm, das den für die „SELECT“-Abfrage generierten Ausführungsplan zeigt und veranschaulicht, dass die SQL Server-Datenbank-Engine die gesamte Komplexität im Umgang mit temporalen Beziehungen übernimmt.

Verwenden Sie folgenden Code, um den Stand des Produktbestands zwischen zwei Zeitpunkten (vor einem Tag und vor einem Monat) zu vergleichen:

DECLARE @dayAgo AS DATETIME2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo AS DATETIME2 = DATEADD(MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
       inventoryDayAgo.ProductName,
       inventoryDayAgo.LocationName,
       inventoryDayAgo.Quantity AS QuantityDayAgo,
       inventoryMonthAgo.Quantity AS QuantityMonthAgo,
       inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
       inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
     INNER JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
         ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Anomalieerkennung

Anomalieerkennung, oder Ausreißererkennung, identifiziert Elemente, die nicht einem erwarteten Muster entsprechen, oder anderen Elementen in einem Datensatz. Man kann systemversionierte temporale Tabellen verwenden, um Anomalien zu erkennen, die periodisch oder unregelmäßig auftreten, indem man zeitliche Abfragen nutzt, um bestimmte Muster schnell zu finden. Was als Anomalie gilt, hängt von der Art der gesammelten Daten und Ihrer Geschäftslogik ab.

Das folgende Beispiel zeigt die vereinfachte Logik zum Erkennen von „Spitzen“ in Verkaufszahlen. Angenommen, Sie arbeiten mit einer temporalen Tabelle, in der der Verlauf von erworbenen Produkten erfasst wird:

CREATE TABLE [dbo].[Product]
(
    [ProdID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] VARCHAR (100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

Das folgende Diagramm zeigt die Verkaufszahlen im Verlauf der Zeit:

Diagramm: Käufe im Zeitverlauf

Angenommen, dass an normalen Tagen die Anzahl der gekauften Produkte nur gering schwankt, identifiziert die folgende Abfrage Singleton-Ausreißer: Werte, deren Unterschied zu ihren unmittelbaren Nachbarn signifikant ist (2×), während sich die umgebenden Werte nicht signifikant unterscheiden (weniger als 20 %):

WITH CTE (ProdId, PrevValue, CurrentValue, NextValue, ValidFrom, ValidTo)
AS (SELECT ProdId,
           LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
           DailySales,
           LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
           ValidFrom,
           ValidTo
    FROM Product FOR SYSTEM_TIME ALL)
SELECT ProdId,
       PrevValue,
       CurrentValue,
       NextValue,
       ValidFrom,
       ValidTo,
       ABS(PrevValue - NextValue) / CONVERT (FLOAT, (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END)) AS PrevToNextDiff,
       ABS(CurrentValue - PrevValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END)) AS CurrentToPrevDiff,
       ABS(CurrentValue - NextValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END) < 0.2
      AND ABS(CurrentValue - PrevValue) / (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END) > 2
      AND ABS(CurrentValue - NextValue) / (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END) > 2;

Note

Dieses Beispiel wurde absichtlich vereinfacht. In den Produktionsszenarien würden Sie wahrscheinlich erweiterte statistische Methoden verwenden, um Beispiele zu identifizieren, die nicht dem gängigen Muster entsprechen.

Langsam veränderliche Dimensionen

Dimensionen beim Data Warehousing enthalten i. d. R. relativ statische Daten zu Entitäten wie z. B. geografische Standorte, Kunden oder Produkte. Einige Szenarien erfordern jedoch ebenfalls das Nachverfolgen von Datenänderungen in Dimensionstabellen. Da Änderungen der Dimensionen viel seltener, unvorhersehbar und außerhalb des regulären Aktualisierungsplans für Faktentabellen erfolgen, werden diese Arten von Dimensionstabellen als langsam wechselnde Dimensionen (SCD) bezeichnet.

Es gibt mehrere Kategorien langsam verändernder Dimensionen, die darauf basieren, wie die Veränderungsgeschichte erhalten bleibt:

Dimensionstyp Details
Typ 0 Verlauf wird nicht gespeichert. Dimensionsattribute spiegeln die ursprünglichen Werte wider.
Typ 1 Dimensionsattribute reflektieren die aktuellen Werte (die vorherigen Werte werden überschrieben).
Typ 2 Jede Version des Dimensionselements wird mit einer separaten Zeile in der Tabelle dargestellt, in der Regel mit Spalten, die die Gültigkeitsdauer angeben.
Typ 3 Begrenzte Historie für ausgewählte Attribute mithilfe zusätzlicher Spalten in derselben Zeile beibehalten
Typ 4 Speicherung der Historie in einer separaten Tabelle, während die ursprüngliche Dimensionstabelle die neuesten (aktuellen) Versionen der Dimensionselemente enthält.

Wenn Sie sich für eine SCD-Strategie entscheiden, so ist die ETL-Ebene (Extrahieren-Transformieren-Laden) dafür verantwortlich, die Dimensionstabellen korrekt beizubehalten. Dafür ist in der Regel komplexer Code und eine umfangreichere Wartung erforderlich.

Sie können systemversionierte temporale Tabellen verwenden, um die Komplexität Ihres Codes drastisch zu senken, da die Datenhistorie automatisch erhalten bleibt. Angesichts der Implementierung mithilfe von zwei Tabellen, entsprechen temporale Tabellen am ehesten dem Typ 4 SCD. Da temporale Abfragen es Ihnen jedoch nur ermöglichen, auf die aktuelle Tabelle zu verweisen, können Sie den Einsatz von temporalen Tabellen auch in Umgebungen in Betracht ziehen, in denen Sie Typ 2 SCD verwenden möchten.

Um deine reguläre Dimension in SCD umzuwandeln, kannst du eine neue erstellen oder eine bestehende verändern, um eine systemversionierte zeitliche Tabelle zu bilden. Wenn deine bestehende Dimensionstabelle historische Daten enthält, erstelle eine separate Tabelle, verschiebe historische Daten dorthin und behalte aktuelle (tatsächliche) Dimensionsversionen in deiner ursprünglichen Dimensionstabelle. Verwenden Sie anschließend die ALTER TABLE-Syntax, um die Dimensionstabelle in eine temporale Tabelle mit Systemversionsverwaltung und einer vordefinierten Verlaufstabelle umzuwandeln.

Das folgende Beispiel veranschaulicht den Prozess und nimmt an, dass die DimLocation-Dimensionstabelle bereits ValidTo und als ValidFrom-NOT-NULL-Spalten enthält, die der ETL-Prozess befüllt:

  • Verschiebe Versionen geschlossener Zeilen in die neue Historientabelle:

    SELECT *
INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

  • Erstellen Sie einen Clustered Columnstore-Index, eine gute Wahl für Data-Warehouse-Szenarien:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Löschen Sie vorherige Versionen aus DimLocation, wodurch die aktuelle Tabelle in der temporalen Systemversionierungskonfiguration entsteht:

    DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

  • Periodendefinition hinzufügen:

    ALTER TABLE DimLocation
    ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

  • Systemversionsführung aktivieren und die Historiktabelle an die DimLocationbinden:

    ALTER TABLE DimLocation
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Du brauchst keinen zusätzlichen Code, um ein SCD während des Data Warehouse-Ladeprozesses nach der Erstellung zu pflegen.

Die folgende Abbildung zeigt, wie man zeitliche Tabellen in einem Grundszenario mit zwei SCDs (DimLocation und DimProduct) und einer Faktentabelle verwenden kann.

Diagramm, das zeigt, wie temporale Tabellen in einem einfachen Szenario mit zwei SCDs (DimLocation und DimProduct) und einer Faktentabelle verwendet werden können.

Um die vorherigen SCDs in Berichten zu verwenden, musst du die Abfrage effektiv anpassen. Sie können beispielsweise den Gesamtumsatz und die durchschnittliche Anzahl der verkauften Produkte pro Kopf in den letzten sechs Monaten berechnen. Beide Metriken erfordern die Korrelation von Daten aus der Faktentabelle und den Dimensionen, deren für die Analyse wichtige Attribute möglicherweise geändert wurden (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

Mit der folgenden Abfrage werden die erforderlichen Metriken ordnungsgemäß berechnet:

DECLARE @now AS DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo AS DATETIME2;

SET @sixMonthsAgo = DATEADD(month, -12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
       DimLocation_History.LocationId,
       SUM(f.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
       AVG(f.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales AS f
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimLocation FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
         ON DimLocation_History.LocationId = f.LocationId
        AND f.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimProduct FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
         ON DimProduct_History.ProductId = f.ProductId
        AND f.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE f.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Considerations

Die Verwendung von systemversionierten temporalen Tabellen für SCD ist akzeptabel, wenn die auf Basis der Datenbanktransaktionszeit berechnete Gültigkeitsdauer für Ihre Geschäftslogik funktioniert. Wenn Sie Daten mit erheblicher Verzögerung laden, ist die Transaktionszeit möglicherweise nicht akzeptabel.

Standardmäßig ermöglichen temporale Tabellen mit Systemversionsverwaltung nicht die Änderung von Verlaufsdaten nach dem Laden (Sie können den Verlauf ändern, nachdem Sie SYSTEM_VERSIONING auf OFF setzen). Dies kann möglicherweise in Fällen eingeschränkt sein, bei denen die Verlaufsdaten regelmäßig geändert werden.

Temporal systemversionierte Tabellen erzeugen bei jeder Änderung einer Spalte eine Zeilenversion. Wenn du neue Versionen bei einer bestimmten Spaltenänderung unterdrücken willst, musst du diese Einschränkung in die ETL-Logik integrieren.

Wenn Sie eine signifikante Anzahl historischer Zeilen in SCD-Tabellen erwarten, sollten Sie einen clustered columnstore-Index als Hauptspeicheroption für die History-Tabelle verwenden. Die Verwendung eines Columnstore-Index reduziert den Speicherbedarf der Verlaufstabelle und beschleunigt Ihre analytischen Abfragen.

Reparieren von Datenbeschädigungen auf Zeilenebene

Sie können sich auf historische Daten in systemversionierten temporalen Tabellen verlassen, um einzelne Zeilen schnell in einen beliebigen der zuvor erfassten Zustände wiederherzustellen. Diese Eigenschaft von temporalen Tabellen ist nützlich, wenn Sie die betroffenen Zeilen ermitteln können und/oder den Zeitpunkt der unerwünschten Datenänderung kennen. Mit diesem Wissen können Sie die Reparatur effizient durchführen, ohne mit Sicherungen zu arbeiten.

Dieser Ansatz hat mehrere Vorteile:

  • Sie können den Umfang der Reparatur genau steuern. Nicht betroffene Datensätze müssen den aktuellen Zustand beibehalten, wobei es sich oftmals um eine kritische Voraussetzung handelt.

  • Der Vorgang ist effizient, und die Datenbank bleibt für alle Arbeitsauslastungen online, bei denen die Daten verwendet werden.

  • Der Reparaturvorgang selbst ist versionsspezifisch. Sie haben eine Prüfspur für die Reparatur, sodass Sie später analysieren können, was passiert ist, falls nötig.

Du kannst die Reparaturaktion relativ einfach automatisieren. Das folgende Codebeispiel zeigt ein gespeichertes Verfahren, das Datenreparatur für die Tabelle Employee durchführt, die in einem Daten-Audit-Szenario verwendet wird.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord (
    @EmployeeID INT,
    @versionNumber INT = 1
)
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID
        ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999
          AND Employee.EmployeeID = @EmployeeID)

/* Update current row using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

Diese gespeicherte Prozedur nimmt @EmployeeID und @versionNumber als Eingabeparameter. Standardmäßig wird der Zeilenzustand auf die letzte Version aus der Historie (@versionNumber = 1) zurückgestellt.

Das folgende Bild zeigt den Zustand der Reihe vor und nach der Prozedur-Aufrufung. Das rote Rechteck markiert die aktuelle Reihenversion, die falsch ist, während das grüne Rechteck die korrekte Version aus der Geschichte markiert.

Screenshot mit dem Zustand der Zeile vor und nach dem Aufruf der Prozedur. 

EXECUTE sp_RepairEmployeeRecord
    @EmployeeID = 1,
    @versionNumber = 1;

Screenshot: Korrigierte Zeile

Diese gespeicherte Reparaturprozedur kann so definiert werden, dass anstelle einer Zeilenversion ein genauer Zeitstempel akzeptiert wird. Die Zeile wird auf eine beliebige Version wiederhergestellt, die zum angegebenen Zeitpunkt aktiv war (d. h. AS OF-Zeitpunkt).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf (
    @EmployeeID INT,
    @asOf DATETIME2
)
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
    SET [Position] = History.[Position],
        [Department] = History.Department,
        [Address] = History.[Address],
        AnnualSalary = History.AnnualSalary
FROM Employee AS e
     INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
         ON e.EmployeeID = History.EmployeeID
WHERE e.EmployeeID = @EmployeeID;

Beim gleichen Datenbeispiel veranschaulicht das folgende Bild das Reparaturszenario mit einer Zeitbedingung. Hervorgehoben werden der @asOf Parameter, die ausgewählte Zeile in der Historie, die zum angegebenen Zeitpunkt tatsächlich war, und die neue Zeilenversion in der aktuellen Tabelle nach der Reparaturaktion:

Screenshot, der das Reparaturszenario mit Zeitbedingung zeigt.

Datenkorrektur kann Teil des automatischen Datenladevorgangs in Data Warehousing- und Berichterstattungssystemen werden. Wenn ein Wert nach einer Aktualisierung nicht korrekt ist, ist in vielen Szenarien die Wiederherstellung der vorherigen Version aus dem Verlauf eine ausreichende Gegenmaßnahme. Das folgende Diagramm zeigt, wie dieser Vorgang automatisiert werden kann:

Diagramm, das zeigt, wie der Prozess automatisiert werden kann

Verwandte Inhalte

  • Last updated on