Scénáře použití dočasných tabulek

Platí na: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Dočasné tabulky s systémovou verzí jsou užitečné ve scénářích, které vyžadují sledování historie změn dat. Doporučujeme zvážit dočasné tabulky v následujících případech použití, abyste měli větší výhody produktivity.

Audit dat

Dočasné verze systému můžete použít u tabulek, které ukládají důležité informace, sledovat, co se změnilo a kdy, a provádět forenzní data v jakémkoli okamžiku.

Používejte časové tabulky k plánování scénářů auditu dat v raných fázích vývojového cyklu. Auditování dat můžete přidat do existujících aplikací nebo řešení, když ho potřebujete.

Následující diagram znázorňuje tabulku Employee s ukázkou dat včetně aktuální (označené modrou barvou) a historických verzí řádků (označených šedou barvou).

Pravá část diagramu vizualizuje verze řádků na časové ose a řádky, které vybíráte, s různými typy dotazů na časové tabulce, s klauzulí nebo bez ní SYSTEM_TIME .

diagram znázorňující první scénář dočasného použití

Povolení správy systémových verzí v nové tabulce pro audit dat

Pokud identifikujete informace, které potřebují auditování dat, vytvořte databázové tabulky jako dočasné tabulky se systémovou verzí. Následující příklad ilustruje scénář s tabulkou volanou Employee v hypotetické HR databázi:

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

Různé možnosti vytvoření systémově verzované časové tabulky jsou popsány v článku Vytvoření systémově verzované časové tabulky.

Povolení správy systémových verzí u existující tabulky pro audit dat

Pokud potřebujete provést audit dat v existujících databázích, použijte ALTER TABLE k rozšíření ne dočasných tabulek, aby se staly systémovou verzí. Aby změny ve vaší aplikaci nebyly narušeny, přidejte sloupce periodických jako HIDDEN, jak je vysvětleno v článku Vytvořte systémově verzovanou časovou tabulku.

Následující příklad ukazuje povolení správy systémových verzí u existující tabulky Employee v hypotetické databázi personálního oddělení. Umožňuje správu verzí systému v tabulce Employee ve dvou krocích. Nejprve se nové sloupce období přidají ve formátu HIDDEN. Pak vytvoří výchozí tabulku historie.

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

Přesnost datového typu datetime2 musí být stejná ve zdrojové tabulce jako v tabulce historie systémových verzí.

Po spuštění předchozího skriptu tabulka historie transparentně sbírá všechny změny dat. V typickém scénáři auditu dat se dotazujete na všechny změny dat aplikované na jednotlivý řádek v časovém období, které vás zajímá. Výchozí tabulka historie se vytvoří s clusterovaným řádkově uloženým B-stromem, který efektivně řeší tento případ použití.

Note

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

Provedení analýzy dat

Jakmile povolíte správu verzí systému pomocí některého z předchozích přístupů, k auditování dat stačí jeden jediný dotaz. Následující dotaz vyhledá verze řádků pro záznamy v tabulce Employee s EmployeeID = 1000, které byly aktivní alespoň pro část období mezi 1. lednem 2021 a 1. lednem 2022 (včetně horní hranice):

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;

Nahraďte FOR SYSTEM_TIME BETWEEN...ANDFOR SYSTEM_TIME ALL k analýze celé historie změn dat pro daného zaměstnance:

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

Chcete-li vyhledat verze řádků, které byly aktivní pouze v období (a ne mimo ni), použijte CONTAINED IN. Tento dotaz je efektivní, protože se dotazuje pouze na tabulku historie:

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;

Nakonec, v některých auditních scénářích byste mohli chtít vidět, jak celá tabulka vypadala v jakémkoli okamžiku v minulosti:

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

Dočasné tabulky založené na systémové verzi ukládají hodnoty pro sloupce období v časovém pásmu UTC, ale může být pohodlnější pracovat v místním časovém pásmu, a to jak pro filtrování dat, tak zobrazení výsledků. Následující ukázka kódu ukazuje, jak aplikovat filtrační podmínku, která je specifikována v lokálním časovém pásmu a poté převedena na UTC pomocí 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;

Použití AT TIME ZONE je užitečné ve všech ostatních scénářích, ve kterých se používají tabulky se systémovou verzí.

Podmínky filtrování zadané v časových klauzulích s FOR SYSTEM_TIME jsou SARGable.

Note

Termín SARGable v relačních databázích odkazuje na predikát typu Search ARGumentable, který může použít index ke zrychlení provádění dotazu. Další informace najdete v článku Průvodce architekturou a návrhem indexů v SQL Serveru a Azure SQL.

Pokud dotazujete přímo do tabulky historie, ujistěte se, že vaše filtrační podmínka je také SARGabilní, a to zadáním filtrů ve tvaru <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Pokud použijete AT TIME ZONE na sloupce období, SQL Server provede úplné prohledání tabulky nebo indexu, což může být nákladné. Vyhněte se tomuto typu podmínky v dotazech:

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

Další informace najdete v tématu Dotazování dat v časové tabulce s verzováním podle systému.

Analýza k určitému bodu v čase (časová cesta)

Místo zaměření na změny jednotlivých záznamů scénáře cestování časem ukazují, jak se celé datové sady v průběhu času mění. Někdy cestování časem zahrnuje několik souvisejících časových tabulek, z nichž každá se mění nezávislým tempem, a které chcete analyzovat:

  • Trendy důležitých ukazatelů v historických a aktuálních datech
  • Přesný okamžitý přehled veškerých dat k libovolnému okamžiku v čase v minulosti (včera, před měsícem atd.)
  • Rozdíly mezi dvěma body v čase zájmu (před měsícem vs. před třemi měsíci, například)

Mnoho reálných scénářů vyžaduje analýzu cestování časem. Pro ilustraci tohoto scénáře použití se podívejme na online zpracování transakcí (OLTP) s automaticky generovanou historií.

OLTP s automaticky vygenerovanou historií dat

V systémech zpracování transakcí můžete analyzovat, jak se v průběhu času mění důležité metriky. Ideálně by analýza historie neměla ohrozovat výkon OLTP aplikace, kde musí přístup k nejnovějším datům probíhat s minimální latencí a uzamčením. Dočasné tabulky s systémovou verzí můžete použít k transparentnímu zachování úplné historie změn pro pozdější analýzu odděleně od aktuálních dat s minimálním dopadem na hlavní úlohu OLTP.

Pro náročné transakční zpracování v SQL Server a Azure SQL Managed Instance doporučujeme používat System-versioned temporal tables s tabulkami optimalizovanými pro paměť, které umožňují ukládat aktuální data do paměti a plnou historii změn na disk nákladově efektivním způsobem.

Pro tabulku historie doporučujeme použít clusterovaný index columnstore z následujících důvodů:

  • Typická analýza trendu přináší výhody z výkonu dotazů poskytovaných clusterovaným indexem columnstore.

  • Úloha pro vyprázdnění dat s tabulkami optimalizovanými pro paměť dosahuje nejlepšího výkonu při silné zátěži OLTP, pokud má tabulka historie clusterovaný index columnstore.

  • Clusterovaný index columnstore poskytuje vynikající kompresi, zejména ve scénářích, kdy se ve stejnou dobu nezmění všechny sloupce.

Použití temporalních tabulek s OLTP v paměti snižuje potřebu uchovávat celou datovou sadu v paměti a umožňuje snadno rozlišit mezi horkými a studenými daty.

Příklady scénářů z reálného světa, které se dobře hodí do této kategorie, jsou mimo jiné správa zásob nebo obchodování s měnou.

Následující diagram ukazuje zjednodušený datový model používaný pro správu zásob:

diagram znázorňující zjednodušený datový model používaný ke správě inventáře

Následující příklad kódu vytváří ProductInventory jako paměťově verzovanou časovou tabulku s indexem sloupcového úložiště v tabulce historie (který nahrazuje indexový záznam řádků vytvořený ve výchozím nastavení):

Note

Ujistěte se, že vaše databáze umožňuje vytvářet tabulky optimalizované pro paměť. Viz Vytvoření tabulky Memory-Optimized a nativně zkompilované uložené procedury.

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

U předchozího modelu to znamená, že postup údržby inventáře může vypadat takto:

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;

Uložená procedura spUpdateInventory vloží nový produkt do inventáře nebo aktualizuje množství produktů pro konkrétní umístění. Obchodní logika je jednoduchá a zaměřuje se na to, aby byl nejnovější stav neustále přesný, a to inkrementací/dekrementací pole Quantity prostřednictvím aktualizace tabulky, zatímco systémově verzované tabulky transparentně přidávají k datům historickou dimenzi, jak je znázorněno na následujícím diagramu.

diagram znázorňující dočasné využití s aktuálním využitím In-Memory a historickým využitím v clusterovém sloupcovém úložišti

Nyní můžete efektivně dotazovat nejnovější stav z nativně zkompilovaného modulu:

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

Analýza změn dat v průběhu času se usnadňuje pomocí klauzule FOR SYSTEM_TIME ALL, jak je znázorněno v následujícím příkladu:

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;

Následující diagram znázorňuje historii dat pro jeden produkt, který se dá snadno vykreslit při importu předchozího zobrazení v Power Query, Power BI nebo podobném nástroji business intelligence:

diagram zobrazující historii dat pro jeden produkt

V tomto scénáři můžete použít časové tabulky k provedení jiných typů analýzy cestování časem, například rekonstrukcí stavu inventáře AS OF v jakémkoli okamžiku v minulosti nebo porovnáním snímků patřících různým časovým okamžikům.

Pro tento scénář použití můžete také rozšířit tabulky Product a Location na časové tabulky, které umožní pozdější analýzu historie změn UnitPrice a 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));

Protože datový model nyní zahrnuje více časových tabulek, nejlepší praxí AS OF pro analýzu je vytvořit pohled, který extrahuje potřebná data z příslušných tabulek a aplikuje FOR SYSTEM_TIME AS OF je na daný pohled, což výrazně zjednodušuje rekonstrukci stavu celého datového modelu:

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

Následující snímek obrazovky ukazuje plán provádění vygenerovaný pro dotaz SELECT. To ilustruje, že databázový stroj zpracovává veškerou složitost při práci s dočasnými relacemi:

diagram znázorňující plán provádění vygenerovaný pro dotaz SELECT, který znázorňuje, že databázový stroj SQL Serveru zpracovává veškerou složitost při práci s dočasnými relacemi.

Použijte následující kód k porovnání stavu zásob produktů mezi dvěma časovými body (před dnem a před měsícem):

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;

Detekce anomálií

Detekce anomálií, nebo detekce odlehlých hodnot, identifikuje položky, které neodpovídají očekávanému vzoru nebo jiné položky v datové sadě. Pomocí systémově verzí časových tabulek můžete detekovat anomálie, které se vyskytují periodicky nebo nepravidelně, pomocí časového dotazování k rychlému nalezení konkrétních vzorů. Co se počítá jako anomálie, závisí na typu sběratelských dat a na vaší obchodní logice.

Následující příklad ukazuje zjednodušenou logiku pro detekci "špiček" v prodejních číslech. Předpokládejme, že pracujete s dočasnou tabulkou, která shromažďuje historii zakoupených produktů:

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

Následující diagram znázorňuje nákupy v průběhu času:

diagram znázorňující nákupy v průběhu času

Za předpokladu, že v pravidelných dnech má počet zakoupených produktů malý rozptyl, následující dotaz identifikuje odlehlé hodnoty: vzorky, které rozdíly ve srovnání s jejich bezprostředními sousedy jsou významné (2x), zatímco okolní vzorky se výrazně neliší (méně než 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

Tento příklad je záměrně zjednodušený. V produkčních scénářích byste pravděpodobně použili pokročilé statistické metody k identifikaci vzorků, které nepoužívají běžný vzor.

Pomalu se měnící dimenze

Dimenze v datových skladech obvykle obsahují relativně statická data o entitách, jako jsou geografická umístění, zákazníci nebo produkty. Některé scénáře však vyžadují sledování změn dat v tabulkách dimenzí. Vzhledem k tomu, že změny dimenzí se vyskytují mnohem méně často, nepředvídatelně a mimo běžný aktualizační plán vztahující se na tabulky faktů, se tyto typy tabulk dimenzí nazývají pomalu se měnící dimenze (SCD).

Existuje několik kategorií pomalu se měnících dimenzí podle toho, jak je historie změn zachována:

Typ dimenze Podrobnosti
Typ 0 Historie se nezachová. Atributy dimenze odrážejí původní hodnoty.
Typ 1 Atributy dimenze odrážejí nejnovější hodnoty (předchozí hodnoty jsou přepsány)
Typ 2 Každá verze členu dimenze reprezentovaná samostatným řádkem v tabulce obvykle se sloupci, které představují období platnosti
Typ 3 Zachování omezené historie pro vybrané atributy pomocí nadbytečných sloupců na stejném řádku
Typ 4 Zachování historie v samostatné tabulce, zatímco původní tabulka dimenzí uchovává nejnovější (aktuální) verze členů dimenze

Když zvolíte strategii SCD, je zodpovědností vrstvy ETL (Extract-Transform-Load) udržovat tabulky dimenzí přesné, což obvykle vyžaduje složitější kód a dodatečnou údržbu.

Můžete použít systémově verzované časové tabulky k dramatickému snížení složitosti vašeho kódu, protože historie dat je automaticky uchovávána. Vzhledem k implementaci pomocí dvou tabulek jsou dočasné tabulky nejblíže scD typu 4. Vzhledem k tomu, že dočasné dotazy umožňují odkazovat pouze na aktuální tabulku, můžete také zvážit dočasné tabulky v prostředích, kde plánujete použít scD typu 2.

Pro převod běžné dimenze na SCD můžete vytvořit novou nebo upravit stávající tak, aby se stala systémově verzovanou časovou tabulkou. Pokud vaše stávající tabulka rozměrů obsahuje historická data, vytvořte samostatnou tabulku a přesuňte tam historická data a aktuální (skutečné) verze rozměrů si zachovejte ve své původní tabulkě rozměrů. Potom pomocí syntaxe ALTER TABLE převeďte tabulku dimenzí na časovou tabulku verziovanou systémem s předdefinovanou tabulkou historie.

Následující příklad ilustruje proces a předpokládá, že tabulka DimLocation dimenzí již obsahuje ValidFrom a ValidTo jako datetime2 sloupce nenulovatelné, které ETL proces vyplní:

  • Přesuňte verze uzavřených řádků do nové tabulky historie:

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

  • Vytvořte clusterovaný index columnstore, což je dobrá volba ve scénářích datového skladu:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Smažte předchozí verze z DimLocation, která se stane aktuální tabulkou v konfiguraci časového systémového verzování:

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

  • Přidejte definici období:

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

  • Povolte systémové verzování a navázejte tabulku historie na :DimLocation

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

Po vytvoření SCD nepotřebujete během procesu načítání datového skladu žádný další kód k jeho zachování.

Následující ilustrace ukazuje, jak můžete použít časové tabulky v základním scénáři zahrnujícím dvě SCD (DimLocation a DimProduct) a jednu faktovou tabulku.

diagram znázorňující, jak můžete používat dočasné tabulky v jednoduchém scénáři zahrnujícím 2 identifikátory SCD (DimLocation a DimProduct) a jednu tabulku faktů.

Pro použití předchozích SCD v reportech je potřeba efektivně upravit dotazování. Můžete například chtít vypočítat celkovou částku prodeje a průměrný počet prodaných produktů na obyvatele za posledních šest měsíců. Obě metriky vyžadují korelaci dat z tabulky faktů a dimenzí, které mohly změnit jejich atributy důležité pro analýzu (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

Následující dotaz správně vypočítá požadované metriky:

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

Použití systémově verzovaných časových tabulek pro SCD je přijatelné, pokud je pro vaši obchodní logiku vyhovující doba platnosti vypočítaná na základě doby transakcí v databázi. Pokud data načítáte s výrazným zpožděním, doba transakce nemusí být přijatelná.

Dočasné tabulky s systémovou verzí ve výchozím nastavení neumožňují po načtení měnit historická data (po nastavení SYSTEM_VERSIONING na OFFmůžete změnit historii). Může se jednat o omezení v případech, kdy se pravidelně mění historická data.

Tabulky verzované temporalním systémem generují řádkovou verzi při každé změně sloupce. Pokud chcete potlačit nové verze při určité změně sloupce, musíte toto omezení začlenit do ETL logiky.

Pokud očekáváte významný počet historických řádků v tabulkách SCD, zvažte použití indexu clustered columnstore jako hlavní možnosti úložiště pro tabulku historie. Použití indexu columnstore snižuje nároky na tabulku historie a zrychluje analytické dotazy.

Oprava poškození dat na úrovni řádků

Můžete se spolehnout na historická data v časových tabulkách se systémovými verzemi a rychle obnovit jednotlivé řádky na libovolný z dříve zachycených stavů. Tato vlastnost dočasných tabulek je užitečná, když můžete najít ovlivněné řádky nebo když znáte čas změny nežádoucích dat. Tyto znalosti umožňují efektivně provádět opravy bez nutnosti provádět zálohování.

Tento přístup má několik výhod:

  • Můžete přesně řídit rozsah opravy. Záznamy, které nejsou ovlivněné, musí zůstat v nejnovějším stavu, což je často kritický požadavek.

  • Operace je efektivní a databáze zůstane online pro všechny úlohy, které data používají.

  • Samotná operace opravy je verzována. Máte auditní stopu opravy, takže můžete později analyzovat, co se stalo, pokud bude potřeba.

Opravu můžete automatizovat poměrně snadno. Následující příklad kódu ukazuje uloženou proceduru, která provádí opravu dat pro tabulku Employee použitou v datovém auditu.

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;

Tato uložená procedura přebírá @EmployeeID a @versionNumber jako vstupní parametry. Ve výchozím nastavení obnoví stav řádku do poslední verze z historie (@versionNumber = 1).

Následující obrázek ukazuje stav řádku před a po volání procedury. Červený obdélník označuje aktuální verzi řádku, která je nesprávná, zatímco zelený obdélník označuje správnou verzi z historie.

Snímek obrazovky znázorňující stav řádku před a za voláním procedury 

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

Snímek obrazovky zobrazující opravený řádek

Tuto uloženou proceduru opravy lze nastavit tak, aby přijímala přesné časové razítko místo verze řádku. Obnoví řádek na libovolnou verzi, která byla aktivní pro poskytnutý bod v čase (to znamená AS OF pro daný bod v čase).

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;

Pro stejnou ukázku dat znázorňuje následující obrázek opravu s časovou podmínkou. Zvýrazněny jsou parametry @asOf , vybraný řádek v historii, který byl aktuální v daném okamžiku, a nová verze řádku v aktuální tabulce po opravné operaci:

Snímek obrazovky zobrazující scénář opravy s časovou podmínkou

Oprava dat se může stát součástí automatizovaného načítání dat v systémech datových skladů a reportingových systémů. Pokud nově aktualizovaná hodnota není správná, pak je v mnoha scénářích obnovení předchozí verze z historie dostatečným opatřením. Následující diagram znázorňuje, jak lze tento proces automatizovat:

diagram znázorňující, jak lze proces automatizovat.

Související obsah

  • Last updated on