Dela via

Användningsscenarier för tidstabeller

  • Artikel

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.

Med temporala tabeller kan du planera för datagranskningsscenarier i ett tidigt skede av utvecklingscykeln eller lägga till datagranskning i befintliga program 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 som du väljer med olika typer av frågor i temporala tabeller, med eller utan SYSTEM_TIME-satsen.

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. I följande exempel visas ett scenario med en tabell med namnet 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 temporal systemversionstabell beskrivs i Skapa en systemversionsbaserad temporal tabell.

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 brytande ändringar i din applikation, lägg till periodkolumner som HIDDEN, enligt beskrivningen i Skapa en systemversionsbaserad temporär 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));

Viktig

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

När du har kört föregående skript samlas alla dataändringar in transparent i historiktabellen. I ett typiskt datagranskningsscenario frågar du efter alla dataändringar som har tillämpats på en enskild rad inom en viss tidsperiod. Standardhistoriktabellen skapas med ett grupperat B-träd för radlagring för att effektivt hantera det här användningsfallet.

Not

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;

I vissa granskningsscenarier kanske du vill se hur hela tabellen såg ut när som helst 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 du tillämpar ett filtreringsvillkor som anges i den lokala tidszonen och sedan konverteras till UTC med hjälp av AT TIME ZONE, som introducerades i SQL Server 2016 (13.x):

/* Add offset of the local time zone to current time*/
DECLARE @asOf 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 kan optimeras med SARG. SARG står för sökargument, och SARG-kompatibel innebär att SQL Server kan använda det underliggande klustrade indexet för att utföra en sökning i stället för en skanning. Mer information finns i SQL Server-indexens arkitektur och designguide.

Om du gör en sökning i historiktabellen direkt ska du se till att filtreringsvillkoret kan utnyttja SARG-optimering genom att ange filter som <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Om du använder AT TIME ZONE för periodkolumner utför SQL Server en tabell- eller indexgenomsökning, vilket kan vara mycket 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 temporal tabell.

Analys vid en specifik tidpunkt (tidsbaserad analys)

I stället för att fokusera på ändringar i enskilda poster visar tidsresescenarier hur hela datauppsättningar ändras över tid. Ibland innebär tidsresor flera relaterade temporala tabeller, som var och en ändras i oberoende takt och som 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)

Det finns många verkliga scenarier som kräver tidsreseanalys. För att illustrera det här användningsscenariot ska vi titta på OLTP med autogenererad historik.

OLTP med automatiskt genererad datahistorik

I transaktionsbearbetningssystem kan du analysera hur viktiga mått ändras över tid. Helst bör analys av historik inte äventyra prestanda för OLTP-programmet där åtkomst till det senaste datatillståndet måste ske med minimal svarstid 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 arbetsbelastningar med hög transaktionsbearbetning i SQL Server och Azure SQL Managed Instance rekommenderar vi att du använder systemversionsbaserade temporala tabeller med minnesoptimerade tabeller, vilket gör att du kan lagra aktuella data i minnet och fullständig historik över ändringar på disk 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.

Genom att använda temporala tabeller med minnesintern OLTP minskar behovet av att behålla hela datauppsättningen i minnet och gör att du enkelt kan skilja mellan frekventa och kalla 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.

I följande kodexempel skapas ProductInventory som en systemversionsbaserad temporal tabell i minnet, med ett grupperat kolumnlagringsindex i historiktabellen (som faktiskt ersätter det radlagringsindex som skapats som standard):

Not

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 table is system-versioned, SYSTEM_VERSIONING must be set 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,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
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
            SET @quantityIncrement = 0

        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 fokuserar på att korrekt upprätthålla det senaste tillståndet hela tiden genom att öka/minska fältet Quantity med hjälp av tabelluppdateringar, medan systemversionstabeller 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 frågor om det senaste tillståndet utföras effektivt från den inbyggda 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

EXEC [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.

Temporala tabeller kan användas i det här scenariot för att utföra andra typer av tidsreseanalyser, till exempel rekonstruera tillståndet för inventeringen AS OF någon tidpunkt tidigare eller jämföra ögonblicksbilder som tillhör olika tidpunkter.

I det här användningsscenariot kan du också utöka tabellerna Produkt och Plats till att bli temporala tabeller för att möjliggöra senare analys av historiken 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 omfattar flera temporala tabeller är bästa praxis för AS OF analys att skapa en vy som extraherar nödvändiga data från de relaterade tabellerna och tillämpar FOR SYSTEM_TIME AS OF på vyn, eftersom detta avsevärt förenklar rekonstruera tillståndet för hela datamodellen:

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 SQL Server Database Engine hanterar all komplexitet vid hantering av tidsmässiga relationer.

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

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo 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

Avvikelseidentifiering (eller avvikande identifiering) är identifiering av objekt som inte överensstämmer med ett förväntat mönster eller andra objekt i en datauppsättning. Du kan använda systemversionsbaserade temporala tabeller för att identifiera avvikelser som inträffar regelbundet eller oregelbundet eftersom du kan använda temporala frågor för att snabbt hitta specifika mönster. Vilken avvikelse 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;

Notera

Det här exemplet förenklas avsiktligt. I produktionsscenarierna skulle du förmodligen använda 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. Med tanke på att ändringar i dimensioner sker mycket mindre ofta, på ett oförutsägbart sätt och utanför det vanliga 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 baserat på hur ändringars historik 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.

Systemversionsbaserade temporala tabeller kan användas för att avsevärt minska komplexiteten i koden eftersom datahistorik bevaras automatiskt. 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 den vanliga dimensionen till SCD kan du skapa en ny eller ändra en befintlig för att bli en systemversionsbaserad temporal tabell. Om din befintliga dimensionstabell innehåller historiska data skapar du en separat tabell och flyttar historiska data dit och behåller aktuella (faktiska) dimensionsversioner i den ursprungliga dimensionstabellen. Använd sedan ALTER TABLE syntax för att konvertera dimensionstabellen till en systemversionsbaserad temporal tabell med en fördefinierad historiktabell.

I följande exempel visas processen och förutsätter att dimensionstabellen DimLocation redan har ValidFrom och ValidTo som datetime2 kolumner som inte kan nulleras, som fylls i av ETL-processen:

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Ingen extra kod krävs för att underhålla SCD under inläsningsprocessen för informationslagret när du har skapat den.

Följande bild visar hur du kan använda temporala tabeller i ett enkelt scenario med 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 kunna använda tidigare SCD:er i rapporter måste du effektivt justera dina frågor. 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 DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo 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 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;

Överväganden

Användning av systemversionsbaserade tidstabeller för SCD är acceptabelt om giltighetsperioden som beräknas baserat på databasens transaktionstid är bra med din affärslogik. Om du läser in data med betydande fördröjning är transaktionstiden kanske inte acceptabel.

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.

Temporala systemversionstabeller genererar radversion vid alla kolumnändringar. Om du vill förhindra nya versioner av vissa kolumnändringar måste du införliva den begränsningen i ETL-logiken.

Om du förväntar dig ett stort antal historiska rader i SCD-tabeller bör du överväga att använda ett grupperat 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 granskningsloggen för själva reparationsåtgärden, så att du kan analysera vad som hände senare om det behövs.

Reparationsåtgärden kan automatiseras relativt enkelt. I nästa kodexempel visas 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 by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History 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 här proceduren återställer som standard radtillståndet till den senaste versionen från historiken (@versionNumber = 1).

Följande bild visar status för raden före och efter procedurens anrop. Röd rektangel markerar aktuell radversion som är felaktig, medan grön rektangel markerar rätt version från historiken.

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

EXEC 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, vald rad i historiken som var faktisk vid den angivna tidpunkten och ny radversion i den aktuella tabellen efter reparationsåtgärden:

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