Scenari di utilizzo delle tabelle temporali

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure AzureSQL Managed InstanceSQL database in Microsoft Fabric

Le tabelle temporali con versione temporale gestita dal sistema sono utili negli scenari in cui è necessario tenere traccia dello storico delle modifiche ai dati. Si consiglia di prendere in considerazione le tabelle temporali nei casi d'uso seguenti per ottenere vantaggi significativi in termini di produttività.

Controllo dei dati

Usare il controllo delle versioni di sistema temporale nelle tabelle che archiviano informazioni critiche per tenere traccia di cosa è stato modificato e quando ed eseguire analisi scientifiche dei dati in qualsiasi momento.

Utilizzare tabelle temporali per pianificare scenari di audit dati nelle prime fasi del ciclo di sviluppo. Puoi aggiungere l'audit dei dati alle applicazioni o soluzioni esistenti quando ne hai bisogno.

Il diagramma seguente mostra luna tabella Employee con il campione di dati che include versioni di riga correnti, contrassegnate dal colore blu, e versioni di riga cronologiche, contrassegnate dal colore grigio.

La parte destra del diagramma visualizza le versioni delle righe su un asse temporale e le righe selezionate con diversi tipi di query su una tabella temporale, con o senza la SYSTEM_TIME clausola.

Abilitare il controllo delle versioni di sistema in una nuova tabella per il controllo dei dati

Se si identificano informazioni che richiedono attività di auditing dei dati, crea tabelle del database come tabelle temporali con versione gestita dal sistema. Il seguente esempio illustra uno scenario con una tabella chiamata Employee in un database ipotetico delle risorse umane:

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

Varie opzioni per creare una tabella versionata al sistema temporale sono descritte in Creare una tabella temporale versionata al sistema.

Abilitare il controllo delle versioni di sistema in una tabella esistente per il controllo dei dati

Se è necessario eseguire la verifica dei dati nei database esistenti, usa ALTER TABLE per convertire le tabelle non temporali in tabelle con controllo delle versioni gestito dal sistema. Per evitare modifiche interrompenti nella tua applicazione, aggiungi colonne di periodo come HIDDEN, come spiegato in Crea una tabella temporale versionata al sistema.

Il seguente esempio illustra l'abilitazione del controllo delle versioni di sistema in una tabella Employee esistente in un ipotetico database delle risorse umane. Abilita il controllo delle versioni di sistema nella tabella Employee in due passaggi. Prima di tutto, le nuove colonne periodo vengono aggiunte come HIDDEN. Poi, viene creata la tabella di cronologia predefinita.

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

Dopo aver eseguito lo script precedente, la tabella storica raccoglie trasparentmente tutte le modifiche ai dati. In uno scenario tipico di audit dei dati, si interrogano tutte le modifiche applicate a una singola riga entro un periodo di tempo di interesse. La tabella di cronologia predefinita viene creata con un B-tree rowstore clusterizzato, per gestire in modo efficiente questo caso d'uso.

Eseguire l'analisi dei dati

Dopo aver abilitato il controllo delle versioni di sistema con uno dei metodi precedenti, per il controllo dei dati è sufficiente eseguire una query. La query seguente cerca le versioni di riga dei record nella tabella Employee, con EmployeeID = 1000 attivi almeno per una parte del periodo tra il 1° gennaio 2021 e il 1° gennaio 2022 (estremo superiore incluso):

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;

Sostituire FOR SYSTEM_TIME BETWEEN...AND con FOR SYSTEM_TIME ALL per analizzare l'intera cronologia delle modifiche dei dati del dipendente specifico:

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

Per cercare le versioni delle righe attive solo all'interno di un periodo, e non al di fuori, usare CONTAINED IN. Questa query è efficiente perché viene eseguita solo sulla tabella di cronologia:

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;

Infine, in alcuni scenari di audit, potresti voler vedere come appariva l'intera tabella in qualsiasi momento del passato:

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

Le tabelle temporali con controllo delle versioni gestito dal sistema memorizzano i valori delle colonne del periodo nel fuso orario UTC, ma potresti trovare più comodo lavorare nel tuo fuso orario locale, sia per filtrare i dati sia per visualizzare i risultati. Il seguente esempio di codice mostra come applicare una condizione di filtraggio, che viene specificata nel fuso orario locale e poi convertita in UTC usando 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;

L'utilizzo di AT TIME ZONE è utile in tutti gli altri scenari in cui vengono usate tabelle con controllo delle versioni di sistema.

Le condizioni di filtro specificate nelle clausole temporali con FOR SYSTEM_TIME sono SARGable.

Se interroghi direttamente la tabella di cronologia, assicurati che anche la tua condizione di filtraggio sia anch'essa SARGable, specificando i filtri nella forma di <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Se si applicano AT TIME ZONE alle colonne period, SQL Server esegue un'analisi di tabella o indice, che può risultare costosa. Evita questo tipo di condizione nelle tue query:

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

Per ulteriori informazioni, vedere Eseguire query sui dati in una tabella temporale con controllo delle versioni gestito dal sistema.

Analisi a un determinato momento (viaggio nel tempo)

Invece di concentrarsi sulle modifiche ai singoli registri, gli scenari di viaggio nel tempo mostrano come interi dataset cambiano nel tempo. A volte il viaggio nel tempo include diverse tabelle temporali correlate, ognuna delle quali cambia a un ritmo indipendente, per le quali si vuole analizzare:

• Tendenze degli indicatori importanti in dati storici e correnti.
• Snapshot esatto di tutti i dati "a partire da" qualsiasi punto nel tempo nel passato, ad esempio ieri, un mese fa e così via.
• Differenze tra due punti nel tempo di interesse, ad esempio un mese fa rispetto a tre mesi fa.

Molti scenari reali richiedono analisi del viaggio nel tempo. Per illustrare questo scenario d'uso, diamo un'occhiata all'elaborazione delle transazioni online (OLTP) con la cronologia generata automaticamente.

OLTP con la cronologia dei dati generata automaticamente

Nei sistemi di elaborazione delle transazioni è possibile analizzare l'importanza del cambiamento delle metriche nel tempo. Idealmente, analizzare la storia non dovrebbe compromettere le prestazioni dell'applicazione OLTP, dove l'accesso allo stato più recente dei dati deve avvenire con latenza minima e bloccaggio dei dati. È possibile usare le tabelle temporali con controllo delle versioni gestito dal sistema per conservare in modo trasparente l'intera cronologia delle modifiche per analisi future, separata dai dati correnti, con un impatto minimo sul carico di lavoro OLTP primario.

Per carichi di lavoro ad alto livello di elaborazione transazionale in SQL Server e Istanza gestita di SQL di Azure, consigliamo di utilizzare tabelle temporali versionate per il sistema con tabelle ottimizzate per la memoria, che permettono di memorizzare i dati attuali in memoria e la storia completa delle modifiche su disco in modo conveniente.

Per la tabella di cronologia è consigliabile usare un indice columnstore cluster per i motivi seguenti:

• Una tipica analisi delle tendenze trae vantaggio dalle prestazioni delle query fornite da un indice columnstore clusterizzato.

• L'attività di svuotamento dei dati con tabelle ottimizzate per la memoria fornisce prestazioni ottimali con un carico di lavoro OLTP elevato quando la tabella di cronologia dispone di un indice columnstore clusterizzato.

• Un indice columnstore clusterizzato garantisce un'eccellente compressione, soprattutto negli scenari in cui non tutte le colonne vengono modificate contemporaneamente.

L'uso di tabelle temporali con OLTP in memoria riduce la necessità di mantenere l'intero dataset in memoria e permette di distinguere facilmente tra dati a caldo e a freddo.

La gestione dell'inventario o il trading valutario sono, tra gli altri, esempi di scenari reali che rientrano in questa categoria.

Il diagramma seguente mostra un modello di dati semplificato utilizzato per la gestione dell'inventario:

Il seguente esempio di codice crea ProductInventory come una tabella temporale con controllo delle versioni di sistema residente in memoria, con un indice columnstore clusterizzato nella tabella cronologica, che sostituisce l'indice rowstore creato per impostazione predefinita:

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

Per il modello precedente, ecco come potrebbe apparire la procedura per la gestione dell'inventario:

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;

La stored procedure spUpdateInventory inserisce un nuovo prodotto nell'inventario o aggiorna la quantità del prodotto per l'ubicazione specifica. La logica di business è semplice e focalizzata sul mantenere sempre accurata l'ultimo stato incrementando o decrementando il Quantity campo tramite l'aggiornamento delle tabelle, mentre le tabelle versionate al sistema aggiungono trasparentemente una dimensione di storia ai dati, come mostrato nel diagramma seguente.

Ora puoi interrogare in modo efficiente lo stato più recente dal modulo compilato nativamente:

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

L'analisi delle modifiche dei dati nel corso del tempo diventa semplice con la clausola FOR SYSTEM_TIME ALL, come illustrato nell'esempio seguente:

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;

Il seguente diagramma illustra la cronologia dei dati per un prodotto, che può essere riprodotta facilmente importando la vista precedente in Power Query, Power BI o uno strumento di Business Intelligence simile:

In questo scenario puoi usare le tabelle temporali per eseguire altri tipi di analisi dei viaggi nel tempo, come ricostruire lo stato dell'inventario AS OF in qualsiasi momento del passato o confrontare istantanee appartenenti a momenti diversi.

Per questo scenario d'uso, si possono anche estendere le Product tabelle e Location per diventare tabelle temporali e consentire un'analisi successiva della storia dei cambiamenti di UnitPrice e 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));

Poiché il modello dati ora coinvolge più tabelle temporali, la migliore pratica per AS OF l'analisi è creare una vista che estragga i dati necessari dalle tabelle correlate e applicli FOR SYSTEM_TIME AS OF alla vista, poiché questo semplifica notevolmente la ricostruzione dello stato dell'intero modello dati:

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

L'immagine seguente illustra il piano di esecuzione generato per la query SELECT. Questo dimostra che il motore di database gestisce tutta la complessità quando si tratta di relazioni temporali:

Usa il seguente codice per confrontare lo stato dell'inventario di prodotti tra due momenti temporali (un giorno fa e un mese fa):

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;

Rilevamento delle anomalie

La rilevazione delle anomalie, o rilevamento di outlier, identifica elementi che non rispettano uno schema atteso o altri elementi in un dataset. Puoi utilizzare tabelle temporali versionate per il sistema per rilevare anomalie che si verificano periodicamente o in modo irregolare, utilizzando le query temporali per localizzare rapidamente schemi specifici. Cosa si considera un'anomalia dipende dal tipo di dati che raccogli e dalla logica del tuo business.

L'esempio seguente illustra la logica semplificata per rilevare "picchi" nelle cifre relative alle vendite. Si supponga di utilizzare una tabella temporale che raccoglie la cronologia dei prodotti acquistati:

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

Il diagramma illustra gli acquisti nel tempo:

Presupponendo che nei giorni normali il numero di prodotti acquistati presenti una varianza ridotta, la query seguente identifica gli outlier singleton, vale a dire campioni la cui differenza rispetto ai relativi vicini più prossimi è significativa (2x), mentre i campioni circostanti non presentano differenze significative (inferiori al 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;

Dimensioni a modifica lenta

Le dimensioni nel data warehousing in genere contengono dati relativamente statici sulle entità, ad esempio posizioni geografiche, clienti o prodotti. Tuttavia, alcuni scenari richiedono di tenere traccia anche delle modifiche dei dati nelle tabelle delle dimensioni. Dato che le modifiche nelle dimensioni avvengono molto meno frequentemente, in modo imprevedibile e al di fuori del programma regolare di aggiornamenti applicato alle tabelle di fatto, questi tipi di tabelle di dimensioni sono chiamate dimensioni che cambiano lentamente (SCD).

Esistono diverse categorie di dimensioni che cambiano lentamente in base a come viene preservata la storia dei cambiamenti:

Quando si sceglie una strategia SCD, spetta al livello ETL (Extract-Transform-Load) mantenere accurate le tabelle dimensionali, il che di solito richiede codice più complesso e manutenzione aggiuntiva.

Puoi usare tabelle temporali versionate dal sistema per ridurre drasticamente la complessità del tuo codice, perché la cronologia dei dati viene automaticamente preservata. Data la loro implementazione mediante due tabelle, le tabelle temporali sono molto simili alle dimensioni a variazione lenta (SCD) di tipo 4. Tuttavia, poiché le query temporali consentono di fare riferimento solo alla tabella corrente, è possibile valutare anche le tabelle temporali in ambienti in cui si prevede di utilizzare SCD di tipo 2.

Per convertire la tua dimensione regolare in SCD, puoi crearne una nuova o modificare una esistente per trasformarla in una tabella temporale versione sistema. Se la tua tabella delle dimensioni esistente contiene dati storici, crea una tabella separata e sposta lì i dati storici, mantenendo le versioni attuali (reali) delle dimensioni nella tabella originale. Usa quindi la sintassi ALTER TABLE per convertire la tabella di dimensione in una tabella temporale con versionamento gestito dal sistema utilizzando una tabella di cronologia predefinita.

Il seguente esempio illustra il processo e presuppone che la tabella delle dimensioni DimLocation abbia già ValidFrom e ValidTo come colonne datetime2 non annullabili, popolate dal processo ETL:

• Sposta le versioni a righe chiuse nella nuova tabella storica:

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

• Crea un indice clusterizzato di columnstore, una buona scelta in scenari di data warehouse:

  CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
      ON DimLocationHistory;
  

• Eliminare le versioni precedenti da DimLocation, che diventa la tabella corrente nella configurazione temporale di versionamento del sistema:

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

• Aggiungi definizione del periodo:

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

• Abilita il versionamento di sistema e associa la tabella della cronologia a DimLocation:

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

Non serve codice extra per mantenere uno SCD durante il processo di caricamento del data warehouse dopo averlo creato.

L'illustrazione seguente mostra come si possono utilizzare tabelle temporali in uno scenario base che coinvolge due SCD (DimLocation e DimProduct) e una tabella di fatto.

Per utilizzare gli SCD precedenti nei report, è necessario adeguare opportunamente l'interrogazione. Ad esempio, è possibile calcolare l'importo totale delle vendite e il numero medio dei prodotti venduti pro capite per gli ultimi sei mesi. Entrambe le metriche richiedono la correlazione dei dati provenienti dalla tabella dei fatti e dalle dimensioni che potrebbero aver modificato attributi importanti per l'analisi (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

La query seguente calcola correttamente le metriche necessarie:

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;

Considerazioni

L'uso di tabelle temporali versionate al sistema per SCD è accettabile se il periodo di validità calcolato in base al tempo delle transazioni del database funziona per la logica del tuo business. Se carichi i dati con un ritardo significativo, i tempi di transazione potrebbero non essere accettabili.

Per impostazione predefinita, le tabelle temporali con controllo delle versioni di sistema non consentono la modifica dei dati cronologici dopo il caricamento (è possibile modificare la cronologia dopo l'impostazione dell'attributo SYSTEM_VERSIONING su OFF). Potrebbe essere una limitazione nei casi in cui la modifica dei dati cronologici viene eseguita regolarmente.

Le tabelle versionate temporalmente nel sistema generano una versione riga a qualsiasi cambiamento di colonna. Se vuoi sopprimere nuove versioni su una certa modifica di colonna, devi incorporare quella limitazione nella logica ETL.

Se ti aspetti un numero significativo di righe storiche nelle tabelle SCD, considera di usare un indice clustered columnstore come opzione principale di archiviazione per la tabella storica. Utilizzare un indice columnstore riduce il footprint della tabella di cronologia e accelera le query analitiche.

Riparare la corruzione dei dati a livello di riga

È possibile fare affidamento sui dati cronologici archiviati nelle tabelle temporali con controllo delle versioni di sistema per ripristinare rapidamente singole righe in uno qualsiasi degli stati acquisiti in precedenza. Questa proprietà delle tabelle temporali è utile quando è possibile trovare le righe interessate e/o quando si conosce l'ora della modifica indesiderata dei dati. Questa conoscenza consente di eseguire il ripristino in modo efficiente senza utilizzare i backup.

Questo approccio offre diversi vantaggi:

• È possibile controllare con precisione l'ambito del ripristino. I record non interessati dalle modifiche devono rimanere nello stato più recente, che spesso è un requisito critico.

• L'operazione è efficiente e il database rimane online per tutti i carichi di lavoro che usano i dati.

• L'operazione di riparazione stessa è versionata. Hai una traccia di audit per l'operazione di riparazione, così puoi analizzare cosa è successo in seguito se necessario.

Puoi automatizzare l'azione di riparazione con relativa facilità. Il seguente esempio di codice mostra una procedura memorizzata che esegue la riparazione dei dati per la tabella Employee utilizzata in uno scenario di audit dati.

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;

Questa stored procedure accetta @EmployeeID e @versionNumber come parametri di input. Ripristina lo stato della riga all'ultima versione presente nella cronologia (@versionNumber = 1) per impostazione predefinita.

La figura seguente mostra lo stato della riga prima e dopo l'invocazione della procedura. Il rettangolo rosso indica la versione della riga attuale che è errata, mentre il rettangolo verde indica la versione corretta della storia.

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

Questa stored procedure di correzione può essere definita per accettare un timestamp esatto invece della versione di riga. Ripristina la riga a una qualsiasi versione attiva per il punto nel tempo specificato (ovvero AS OF punto nel tempo).

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;

Per lo stesso campione di dati, l'immagine seguente illustra uno scenario di ripristino con una condizione temporale. Sono evidenziati il @asOf parametro, la riga selezionata nella cronologia che era effettiva nel momento fornito e la nuova versione della riga nella tabella corrente dopo l'operazione di riparazione:

La correzione dei dati può diventare una fase automatizzata del caricamento dei dati nei sistemi di data warehousing e reportistica. Se un valore appena aggiornato non è corretto, in molti scenari il ripristino della versione precedente dalla cronologia è una soluzione adeguata. Il diagramma seguente illustra come è possibile automatizzare il processo:

Contenuti correlati

• Tabelle temporali
• Inizia a utilizzare le tabelle temporali versionate dal sistema
• Verifiche di coerenza del sistema delle tabelle temporali
• Partizioni con tabelle temporali
• Considerazioni e limitazioni delle tabelle temporali
• Sicurezza di una tabella temporale
• Tabelle temporali versionate a livello di sistema con tabelle ottimizzate per la memoria
• Funzioni e viste per i metadati delle tabelle temporali