Användningsscenarier för tidstabeller

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Systemversionsbaserade temporala tabeller är användbara i scenarier som kräver spårningshistorik för dataändringar. Vi rekommenderar att du överväger temporala tabeller i följande användningsfall för större produktivitetsfördelar.

Datagranskning

Du kan använda temporal systemversionshantering på tabeller som lagrar viktig information, för att hålla reda på vad som har ändrats och när och för att utföra datatekniska uppgifter när som helst.

Använd temporala tabeller för att planera för datagranskningsscenarier i de tidiga stadierna av utvecklingscykeln. Du kan lägga till datagranskning i befintliga applikationer eller lösningar när du behöver det.

Följande diagram visar en Employee tabell med dataexemplet inklusive aktuell (markerad med blå färg) och historiska radversioner (markerade med en grå färg).

Den högra delen av diagrammet visualiserar radversioner på en tidsaxel, och de rader du väljer med olika typer av frågor i en temporal tabell, med eller utan klausulen SYSTEM_TIME .

diagram som visar det första scenariot för tidsanvändning.

Aktivera systemversioner i en ny tabell för datagranskning

Om du identifierar information som behöver datagranskning skapar du databastabeller som systemversionsbaserade tidstabeller. Följande exempel illustrerar ett scenario med en tabell som anropas Employee i en hypotetisk HR-databas:

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));

Olika alternativ för att skapa en tabell med temporär systemversion beskrivs i Create a system-versioned temporal table.

Aktivera systemversionshantering i en befintlig tabell för datagranskning

Om du behöver utföra datagranskning i befintliga databaser använder du ALTER TABLE för att utöka icke-temporala tabeller till att bli systemversionsbaserade. För att undvika att ändra ändringar i din applikation, lägg till periodkolumner som HIDDEN, som förklaras i Skapa en systemversionerad temporal tabell.

I följande exempel visas aktivering av systemversioner på en befintlig Employee tabell i en hypotetisk HR-databas. Det möjliggör systemversionshantering i tabellen Employee i två steg. Först läggs nya periodkolumner till som HIDDEN. Sedan skapas standardhistoriktabellen.

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

Precisionen för datetime2 datatyp måste vara samma i källtabellen som i tabellen med systemversionshistorik.

Efter att du kört det föregående skriptet samlar historiktabellen transparent in alla dataändringar. I ett typiskt datagranskningsscenario frågar du efter alla dataändringar som tillämpas på en enskild rad inom en tidsperiod av intresse. Standardhistoriktabellen skapas med ett grupperat B-träd för radlagring för att effektivt hantera det här användningsfallet.

Anmärkning

I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

Utföra dataanalys

När du har aktiverat systemversionshantering med någon av de tidigare metoderna är datagranskning bara en fråga bort. Följande fråga söker efter radversioner efter poster i tabellen Employee, med EmployeeID = 1000 som var aktiva minst under en del av perioden mellan 1 januari 2021 och 1 januari 2022 (inklusive den övre gränsen):

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;

Ersätt FOR SYSTEM_TIME BETWEEN...AND med FOR SYSTEM_TIME ALL för att analysera hela historiken för dataändringar för den specifika medarbetaren:

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

Om du vill söka efter radversioner som endast var aktiva inom en period (och inte utanför den) använder du CONTAINED IN. Den här frågan är effektiv eftersom den bara frågar historiktabellen:

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;

Slutligen, i vissa revisionsscenarier kan du vilja se hur hela tabellen såg ut vid någon tidpunkt tidigare:

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

Systemversionsbaserade tidstabeller lagrar värden för periodkolumner i UTC-tidszonen, men du kanske tycker att det är enklare att arbeta i din lokala tidszon, både för att filtrera data och visa resultat. Följande kodexempel visar hur man applicerar ett filtreringsvillkor, som anges i den lokala tidszonen och sedan konverteras till UTC med hjälp av 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;

Att använda AT TIME ZONE är användbart i alla andra scenarier där systemversionstabeller används.

Filtreringsvillkor som anges i temporala satser med FOR SYSTEM_TIME är SARGable.

Anmärkning

Termen SARGable i relationsdatabaser refererar till ett Search ARGumentable-predikat som kan använda ett index för att påskynda exekveringen av SQL-frågan. Mer information finns i arkitektur och designguide för SQL Server- och Azure SQL-index.

Om du kör frågor mot historiktabellen direkt bör du se till att filtreringsvillkoret också är SARG-bart genom att ange filter i formen <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Om du använder AT TIME ZONE periodkolumner utför SQL Server en tabell- eller indexgenomsökning, vilket kan vara dyrt. Undvik den här typen av villkor i dina frågor:

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

Mer information finns i Fråga data i en systemversionerad temporär tabell.

Analys vid en specifik tidpunkt (tidsbaserad analys)

Istället för att fokusera på förändringar i enskilda poster visar tidsresescenarier hur hela dataset förändras över tid. Ibland inkluderar tidsresor flera relaterade tidstabeller, som var och en ändras i en oberoende takt, för vilka du vill analysera:

  • Trender för viktiga indikatorer i historiska och aktuella data
  • Exakt ögonblicksbild av hela data "från och med" någon tidpunkt tidigare (igår, för en månad sedan osv.)
  • Skillnader mellan två räntepunkter (för en månad sedan jämfört med för tre månader sedan, till exempel)

Många verkliga scenarier kräver tidsreseanalys. För att illustrera detta användningsscenario, låt oss titta på online-transaktionshantering (OLTP) med autogenererad historik.

OLTP med automatiskt genererad datahistorik

I transaktionsbearbetningssystem kan du analysera hur viktiga mått ändras över tid. Idealiskt sett bör analys av historik inte äventyra OLTP-applikationens prestanda där åtkomst till det senaste dataläget måste ske med minimal latens och datalåsning. Du kan använda systemversionsbaserade temporala tabeller för att transparent behålla den fullständiga historiken för ändringar för senare analys, separat från aktuella data, med minimal påverkan på huvud-OLTP-arbetsbelastningen.

För högtransaktionella arbetsbelastningar i SQL Server och Azure SQL Managed Instance rekommenderar vi att du använder systemversionerade temporala tabeller med minnesoptimerade tabeller, vilket låter dig lagra aktuell data i minnet och fullständig historik över ändringar på disken på ett kostnadseffektivt sätt.

För historiktabellen rekommenderar vi att du använder ett grupperat columnstore-index av följande skäl:

  • Typisk trendanalys drar nytta av frågeprestanda som tillhandahålls av ett grupperat columnstore-index.

  • Dataspolningsaktiviteten med minnesoptimerade tabeller presterar bäst under tung OLTP-arbetsbelastning när historiktabellen har ett grupperat kolumnlagringsindex.

  • Ett grupperat columnstore-index ger utmärkt komprimering, särskilt i scenarier där inte alla kolumner ändras samtidigt.

Att använda temporala tabeller med OLTP i minnet minskar behovet av att ha hela datamängden i minnet och gör det enkelt att skilja mellan varm och kall data.

Exempel på verkliga scenarier som passar bra in i den här kategorin är bland annat lagerhantering eller valutahandel.

Följande diagram visar en förenklad datamodell som används för lagerhantering:

diagram som visar en förenklad datamodell som används för inventeringshantering.

Följande kodexempel skapar ProductInventory som en minnesintern temporal tabell med systemversionshantering, med ett klustrat kolumnlagringsindex på historiktabellen (som ersätter det radlagringsindex som skapas som standard):

Anmärkning

Kontrollera att databasen tillåter skapande av minnesoptimerade tabeller. Se Skapa en Memory-Optimized-tabell och en inbyggt kompilerad lagrad procedur.

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 den tidigare modellen är det så här proceduren för att underhålla inventeringen kan se ut:

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;

Den spUpdateInventory lagrade proceduren infogar antingen en ny produkt i lagret eller uppdaterar produktkvantiteten för den specifika platsen. Affärslogiken är enkel och fokuserad på att hålla det senaste tillståndet korrekt hela tiden genom att öka eller minska Quantity fältet genom tabelluppdatering, medan systemversionerade tabeller transparent lägger till en historikdimension till datan, som visas i följande diagram.

Diagram som visar tidsmässig användning med aktuell användning In-Memory och historisk användning i ett grupperat kolumnarkiv.

Nu kan du effektivt fråga det senaste tillståndet från den nativt kompilerade modulen:

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];

Det blir enkelt att analysera dataändringar över tid med FOR SYSTEM_TIME ALL-satsen, som du ser i följande exempel:

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;

Följande diagram visar datahistoriken för en produkt som enkelt kan renderas vid import av den tidigare vyn i Power Query, Power BI eller liknande business intelligence-verktyg:

diagram som visar datahistoriken för en produkt.

Du kan använda temporala tabeller i detta scenario för att utföra andra typer av tidsreseanalyser, såsom att rekonstruera lagrets AS OF tillstånd vid någon tidpunkt i det förflutna eller jämföra ögonblick som tillhör olika tidpunkter.

För detta användningsscenario kan du också utvidga Product och-tabellerna Location till att bli temporala tabeller för att möjliggöra senare analys av historiken för förändringar i UnitPrice och NumberOfEmployee.

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));

Eftersom datamodellen nu involverar flera temporala tabeller är bästa praxis för AS OF analys att skapa en vy som extraherar nödvändig data från de relaterade tabellerna och applicerar FOR SYSTEM_TIME AS OF på vyn, eftersom detta avsevärt förenklar rekonstruktionen av hela datamodellens tillstånd:

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';

Följande skärmbild visar exekveringsplanen som genererats för fråga SELECT. Detta illustrerar att databasmotorn hanterar all komplexitet när det gäller tidsmässiga relationer:

diagram som visar körningsplanen som genererats för SELECT-frågan som illustrerar att Databasmotor för SQL Server hanterar all komplexitet vid hantering av tidsmässiga relationer.

Använd följande kod för att jämföra statusen för produktlagret mellan två tidpunkter (för en dag sedan och för en månad sedan):

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;

Avvikelseidentifiering

Avvikelsedetektion, eller avvikelsedetektering, identifierar objekt som inte följer ett förväntat mönster eller andra objekt i en datamängd. Du kan använda systemversionerade temporala tabeller för att upptäcka avvikelser som uppstår periodiskt eller oregelbundet, genom att använda temporal frågeställning för att snabbt hitta specifika mönster. Vad som räknas som en anomali beror på vilken typ av data du samlar in och din affärslogik.

I följande exempel visas förenklad logik för att identifiera "toppar" i försäljningssiffror. Anta att du arbetar med en tidstabell som samlar in historik för de produkter som köpts:

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
    )
);

Följande diagram visar köpen över tid:

diagram som visar inköpen över tid.

Förutsatt att antalet köpta produkter under de vanliga dagarna har en liten varians identifierar följande fråga enskilda avvikande värden: stickprov vars skillnad jämfört med deras närmaste grannar är betydande (2x), medan omgivande stickprov inte skiljer sig avsevärt (mindre än 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;

Anmärkning

Det här exemplet förenklas avsiktligt. I produktionsscenarierna använder du förmodligen avancerade statistiska metoder för att identifiera exempel som inte följer det vanliga mönstret.

Långsamt föränderliga dimensioner

Dimensioner i datalager innehåller vanligtvis relativt statiska data om entiteter som geografiska platser, kunder eller produkter. Vissa scenarier kräver dock att du även spårar dataändringar i dimensionstabeller. Eftersom förändringar i dimensioner sker mycket mer sällan, på ett oförutsägbart sätt och utanför det regelbundna uppdateringsschemat som gäller för faktatabeller, kallas dessa typer av dimensionstabeller för långsamt föränderliga dimensioner (SCD).

Det finns flera kategorier av långsamt föränderliga dimensioner baserade på hur förändringarnas historia bevaras:

Dimensionstyp Detaljer
Typ 0 Historik bevaras inte. Dimensionsattribut återspeglar ursprungliga värden.
Typ 1 Dimensionsattribut återspeglar de senaste värdena (tidigare värden skrivs över)
Typ 2 Varje version av dimensionsmedlemmen representeras med en separat rad i tabellen, vanligtvis med kolumner som representerar giltighetsperioden
Typ 3 Behålla begränsad historik för valda attribut med hjälp av extra kolumner på samma rad
Typ 4 Behålla historiken i den separata tabellen medan den ursprungliga dimensionstabellen behåller de senaste (aktuella) dimensionsmedlemsversionerna

När du väljer en SCD-strategi är det ETL-lagrets ansvar (ExtraheringTransform-Load) att hålla dimensionstabellerna korrekta, vilket vanligtvis kräver mer komplex kod och extra underhåll.

Du kan använda systemversionerade temporala tabeller för att dramatiskt sänka kodens komplexitet, eftersom datahistoriken automatiskt bevaras. Med tanke på implementeringen med hjälp av två tabeller är tidstabeller närmast typ 4 SCD. Men eftersom temporala frågor gör att du endast kan referera till den aktuella tabellen kan du även överväga temporala tabeller i miljöer där du planerar att använda scd av typ 2.

För att konvertera din vanliga dimension till SCD kan du skapa en ny eller ändra en befintlig för att bli en systemversionerad temporal tabell. Om din befintliga dimensionstabell innehåller historisk data, skapa en separat tabell och flytta historisk data dit och behåll nuvarande (faktiska) dimensionsversioner i din ursprungliga dimensionstabell. Använd sedan ALTER TABLE syntax för att konvertera dimensionstabellen till en systemversionsbaserad temporal tabell med en fördefinierad historiktabell.

Följande exempel illustrerar processen och antar att DimLocation dimensionstabellen redan har ValidFrom och ValidTo som datetime2 icke-nullbara kolumner, vilka ETL-processen fyller i:

  • Flytta stängda radversioner till den nya historiktabellen:

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

  • Skapa ett klustrat kolumnlagringsindex, ett bra val i datalager-scenarier:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Ta bort tidigare versioner från DimLocation, vilket blir den aktuella tabellen i den temporala systemversioneringskonfigurationen:

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

  • Lägg till definitionen av perioden:

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

  • Aktivera systemversionshantering och bind historiktabellen till DimLocation:

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

Du behöver ingen extra kod för att underhålla en SCD under data warehouse-laddningsprocessen efter att du skapat den.

Följande illustration visar hur du kan använda temporala tabeller i ett grundläggande scenario som involverar två SCD (DimLocation och DimProduct) och en faktatabell.

diagram som visar hur du kan använda temporala tabeller i ett enkelt scenario med 2 SCD (DimLocation och DimProduct) och en faktatabell.

För att använda tidigare SCD:er i rapporter behöver du effektivt justera förfrågningar. Du kanske till exempel vill beräkna det totala försäljningsbeloppet och det genomsnittliga antalet sålda produkter per capita under de senaste sex månaderna. Båda måtten kräver korrelation av data från faktatabellen och dimensioner som kan ha ändrat deras attribut som är viktiga för analysen (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

Följande fråga beräknar de mått som krävs korrekt:

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

Att använda systemversionerade temporala tabeller för SCD är acceptabelt om giltighetsperioden som beräknas baserat på databasens transaktionstid fungerar för din affärslogik. Om du laddar data med betydande fördröjning kan transaktionstiden vara oacceptabel.

Som standard tillåter systemversionsbaserade temporala tabeller inte ändring av historiska data efter inläsning (du kan ändra historiken när du har angett SYSTEM_VERSIONING till OFF). Detta kan vara en begränsning i fall där ändring av historiska data sker regelbundet.

Tabeller med tidsmässiga systemversioner genererar en radversion vid varje kolumnändring. Om du vill undertrycka nya versioner vid en viss kolumnändring måste du införliva den begränsningen i ETL-logiken.

Om du förväntar dig ett betydande antal historiska rader i SCD-tabeller, överväg att använda ett klustrat kolumnlagringsindex som huvudlagringsalternativ för historiktabellen. Användning av ett kolumnlagringsindex minskar fotavtrycket för historiktabellen och snabbar på dina analysfrågor.

Reparera skadade data på radnivå

Du kan förlita dig på historiska data i systemversionsbaserade temporala tabeller för att snabbt reparera enskilda rader till något av de tidigare insamlade tillstånden. Den här egenskapen för temporala tabeller är användbar när du kan hitta berörda rader och/eller när du vet tidpunkten för oönstrade dataändringar. Med den här kunskapen kan du utföra reparation effektivt utan att hantera säkerhetskopior.

Den här metoden har flera fördelar:

  • Du kan styra reparationens omfattning exakt. Poster som inte är påverkade måste behålla det senaste tillståndet, vilket ofta är ett kritiskt krav.

  • Driften är effektiv och databasen förblir online för alla arbetsbelastningar som använder datan.

  • Själva reparationsåtgärden är versionshanterad. Du har en revisionslogg över reparationsarbetet, så att du vid behov kan analysera vad som hände i efterhand.

Du kan automatisera reparationsåtgärden relativt enkelt. Följande kodexempel visar en lagrad procedur som utför datareparation för tabellen Employee som används i ett datagranskningsscenario.

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;

Den här lagrade proceduren tar @EmployeeID och @versionNumber som indataparametrar. Den återställer radtillståndet till den senaste versionen från historiken (@versionNumber = 1) som standard.

Följande bild visar radens tillstånd före och efter procedureinkallelsen. Den röda rektangeln markerar den aktuella radversionen som är felaktig, medan den gröna rektangeln markerar den korrekta versionen från historiken.

Skärmbild som visar status för raden före och efter procedurens anrop. 

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

Skärmbild som visar den korrigerade raden.

Den här lagrade reparationsproceduren kan definieras för att acceptera en exakt tidsstämpel i stället för radversion. Den återställer raden till någon version som var aktiv vid den angivna tidpunkten (det vill säga, vid tidpunkt AS OF).

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;

För samma dataexempel illustrerar följande bild ett reparationsscenario med ett tidsvillkor. Markerade är parametern @asOf , den valda raden i historiken som var aktuell vid den angivna tidpunkten, och den nya radversionen i den aktuella tabellen efter reparationsoperationen:

Skärmbild som visar reparationsscenariot med tidsvillkor.

Datakorrigering kan bli en del av automatisk datainläsning i datalager- och rapporteringssystem. Om ett nyligen uppdaterat värde inte är korrekt, är återställning av den tidigare versionen från historiken i många scenarier tillräckligt bra som åtgärd. Följande diagram visar hur den här processen kan automatiseras:

diagram som visar hur processen kan automatiseras.

Relaterat innehåll

  • Last updated on