Cas d’utilisation des tables temporelles

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Les tables temporelles avec version du système sont utiles dans les scénarios qui requièrent un suivi de l'historique des modifications des données. Nous vous recommandons d'envisager l'utilisation de tables temporelles dans les cas d'utilisation suivants, afin de bénéficier d'importants avantages en termes de productivité.

Audit des données

Vous pouvez utiliser la version temporelle du système pour les tables qui stockent des informations critiques, afin de garder une trace de ce qui a été modifié et quand, et d'effectuer des analyses de données à tout moment.

Utilisez des tables temporelles pour planifier des scénarios d’audit des données aux premières étapes du cycle de développement. Vous pouvez ajouter l’audit des données aux applications ou solutions existantes quand vous en avez besoin.

Le diagramme suivant montre une table Employee avec un échantillon de données comprenant les versions actuelles des lignes (indiquées en bleu) et les versions historiques des lignes (indiquées en gris).

La partie droite du diagramme visualise les versions des lignes sur un axe temporel, et les lignes que vous sélectionnez avec différents types d’interrogations sur une table temporelle, avec ou sans la SYSTEM_TIME clause.

Diagramme montrant le premier scénario d’utilisation temporelle.

Activer la version du système sur une nouvelle table pour l'audit des données

Si vous identifiez des informations nécessitant un audit des données, créez des tables de base de données en tant que tables temporelles version système. L’exemple suivant illustre un scénario avec une table appelée Employee dans une base de données RH hypothétique :

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

Diverses options pour créer une table temporelle versionnée système sont décrites dans Créer une table temporelle versionnée système.

Activer le versionnement système sur une table existante à des fins d’audit des données

Si vous avez besoin d’effectuer un audit des données dans des bases de données existantes, utilisez ALTER TABLE pour convertir les tables non temporelles en tables avec version système. Pour éviter des modifications incompatibles dans votre application, ajoutez les colonnes de période en tant que HIDDEN, comme expliqué dans Créer une table temporelle à versionnement géré par le système.

L’exemple suivant illustre l’activation de la gestion système des versions sur une table Employee existante appartenant à une base de données de ressources humaines hypothétique. Il active le contrôle de version système dans le tableau Employee en deux étapes. Tout d’abord, les nouvelles colonnes de période sont ajoutées en tant que HIDDEN. Ensuite, il crée la table d’historique par défaut.

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

La précision du type de données datetime2 doit être la même dans la table source que dans la table d’historique à versionnement géré par le système.

Après avoir exécuté le script précédent, la table d’historique collecte de manière transparente toutes les modifications de données. Dans un scénario typique d’audit des données, vous interrogez toutes les modifications de données appliquées à une ligne individuelle dans un délai d’intérêt. La table d’historique par défaut est créée avec un index clusterisé rowstore en arborescence B, afin de répondre efficacement à ce cas d’utilisation.

Note

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

Effectuer une analyse des données

Une fois que vous avez activé le contrôle de version du système à l’aide de l’une des approches précédentes, l’audit des données ne nécessite plus qu’une seule requête. La requête suivante recherche les versions de ligne des enregistrements de la table Employee, avec EmployeeID = 1000, qui étaient actives pendant au moins une partie de la période comprise entre le 1er janvier 2021 et le 1er janvier 2022 (limite supérieure comprise) :

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;

Remplacez FOR SYSTEM_TIME BETWEEN...AND par FOR SYSTEM_TIME ALL pour analyser l’historique complet des changements de données pour cet employé particulier :

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

Pour rechercher les versions de ligne qui étaient actives uniquement pendant une période (et pas en dehors de celle-ci), utilisez CONTAINED IN. Cette requête est efficace, car elle interroge uniquement la table d’historique :

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;

Enfin, dans certains scénarios d’audit, vous pourriez vouloir voir à quoi ressemblait l’ensemble de la table à un moment donné dans le passé :

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

Les tables temporelles avec versions gérées par le système stockent des valeurs pour les colonnes de période dans le fuseau horaire UTC, même si vous pouvez considérer qu’il est plus pratique de travailler dans votre fuseau horaire local, à la fois pour le filtrage des données et pour l’affichage des résultats. L’exemple de code suivant montre comment appliquer une condition de filtrage, qui est spécifiée dans le fuseau horaire local puis convertie en UTC en utilisant 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;

La clause AT TIME ZONE est utile dans tous les autres scénarios faisant appel à des tables versionnées par le système.

Les conditions de filtrage spécifiées dans les clauses temporelles avec FOR SYSTEM_TIME sont SARGables.

Note

Le terme SARGable dans les bases de données relationnelles fait référence à un prédicat Search ARGumentable, capable d’utiliser un index pour accélérer l’exécution de la requête. Pour plus d’informations, consultez le guide de l’architecture et de la conception d’index pour SQL Server et Azure SQL.

Si vous interrogez directement la table d’historique, assurez-vous que votre condition de filtrage est également SARGable en spécifiant des filtres sous la forme de <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Si vous appliquez AT TIME ZONE aux colonnes de période, SQL Server effectue un balayage de table ou d’index, ce qui peut être coûteux. Évitez ce type de condition dans vos requêtes :

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

Pour plus d’informations, consultez Interroger les données d'une table temporelle version système.

Analyse à un instant donné (voyage dans le temps)

Au lieu de se concentrer sur les modifications apportées à des enregistrements individuels, les scénarios de voyage dans le temps montrent comment des ensembles de données intières évoluent au fil du temps. Parfois, le voyage dans le temps inclut plusieurs tables temporelles apparentées, chacune changeant à un rythme indépendant, pour lesquelles vous souhaitez analyser :

  • Tendances des indicateurs importants dans les données historiques et les données actuelles
  • Instantané exact de l’ensemble des données à n’importe quel moment du passé (hier, il y a un mois, etc.)
  • Différences entre deux points dans le temps dignes d’intérêt (il y a un mois et il y a trois mois, par exemple)

De nombreux scénarios réels nécessitent une analyse du voyage dans le temps. Pour illustrer ce scénario d’utilisation, examinons le traitement des transactions en ligne (OLTP) avec un historique généré automatiquement.

OLTP avec l’historique des données généré automatiquement

Dans les systèmes de traitement transactionnel, vous pouvez analyser l’évolution de métriques importantes dans le temps. Idéalement, l’analyse de l’historique ne devrait pas compromettre les performances de l’application OLTP, où l’accès au dernier état des données doit se faire avec une latence minimale et un verrouillage des données. Vous pouvez utiliser des tables temporelles avec versions gérées par le système pour permettre aux utilisateurs de conserver de façon transparente l’historique complet des modifications en vue d’une analyse ultérieure, séparément des données actuelles, avec un impact minime sur la charge de travail OLTP principale.

Pour des charges de travail de traitement transactionnelles élevées dans SQL Server et Azure SQL Managed Instance, nous recommandons d’utiliser des tables temporelles versionnées System avec des tables optimisées en mémoire, ce qui permet de stocker les données actuelles en mémoire et l’historique complet des modifications sur disque de manière rentable.

Pour la table d’historique, nous vous recommandons d’utiliser un index columnstore en cluster pour les raisons suivantes :

  • L’analyse de tendances bénéficie généralement des performances de requête offertes par un index columnstore en cluster.

  • L’opération de vidage des données pour les tables optimisées en mémoire offre les meilleures performances dans le cadre de charges de travail OLTP intensives lorsque la table d’historique possède un index columnstore clusterisé.

  • Un index columnstore en cluster offre une excellente compression, surtout dans les cas où toutes les colonnes ne sont pas modifiées en même temps.

L’utilisation de tables temporelles avec OLTP en mémoire réduit la nécessité de conserver l’ensemble des jeux de données en mémoire et permet de distinguer facilement les données chaudes des données froides.

Parmi les exemples de scénarios concrets qui rentrent dans cette catégorie, citons la gestion des stocks ou la négociation en devises.

Le schéma suivant montre un modèle de données simplifié utilisé pour la gestion des stocks :

Diagramme montrant un modèle de données simplifié utilisé pour la gestion de stocks.

L’exemple de code suivant crée ProductInventory en tant que table temporelle à versionnement géré par le système en mémoire, avec un index columnstore clusterisé sur la table d’historique (qui remplace l’index rowstore créé par défaut) :

Note

Vérifiez que votre base de données permet de créer des tables optimisées en mémoire. Consultez Création d’une table mémoire optimisée et d’une procédure stockée compilée en mode natif.

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

Pour le modèle ci-dessus, la procédure de gestion des stocks pourrait ressembler à ceci :

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 procédure stockée spUpdateInventory insère un nouveau produit dans le stock ou met à jour la quantité du produit pour l’emplacement spécifique. La logique métier est simple et vise à maintenir l’état le plus récent en permanence en incrémentant / décrémentant le Quantity champ via la mise à jour de la table, tandis que les tables version système ajoutent de manière transparente une dimension historique aux données, comme illustré sur le diagramme suivant.

Diagramme montrant l’utilisation de données temporelles avec l’utilisation actuelle en mémoire et l’historique d’utilisation dans un cluster columnstore.

Vous pouvez désormais interroger efficacement le dernier état du module compilé nativement :

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’analyse des changements de données au fil du temps devient facile avec la clause FOR SYSTEM_TIME ALL, comme l’illustre l’exemple suivant :

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;

Le diagramme suivant montre l’historique des données pour un produit, que vous pouvez afficher facilement en important la vue ci-dessus dans Power Query, Power BI ou un outil décisionnel similaire :

Diagramme montrant l’historique des données d’un produit.

Vous pouvez utiliser des tables temporelles dans ce scénario pour effectuer d’autres types d’analyses de voyage dans le temps, comme reconstituer l’état de l’inventaire AS OF à un moment donné dans le passé ou comparer des instantanés appartenant à différents moments dans le temps.

Pour ce scénario d’utilisation, vous pouvez également étendre les Product tables et Location pour qu’elles deviennent des tables temporelles afin de permettre une analyse ultérieure de l’historique des changements de UnitPrice et 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));

Puisque le modèle de données implique désormais plusieurs tables temporelles, la meilleure pratique pour AS OF l’analyse est de créer une vue qui extrait les données nécessaires des tables associées et les applique FOR SYSTEM_TIME AS OF à la vue, car cela simplifie grandement la reconstruction de l’état complet du modèle de données :

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

La capture d’écran suivante montre le plan d’exécution généré pour la requête SELECT. Comme vous pouvez le constater, le moteur de base de données SQL Server prend en charge toute la complexité de la gestion des relations temporelles :

Diagramme montrant le plan d’exécution généré pour la requête Sélection, et indiquant que toute la complexité du traitement des relations temporelles est entièrement gérée par le moteur de base de données SQL Server.

Utilisez le code suivant pour comparer l’état des stocks de produits entre deux moments (il y a un jour et il y a un mois) :

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;

Détection des anomalies

La détection d’anomalie, ou détection de valeurs aberrantes, identifie les éléments qui ne correspondent pas à un schéma attendu ou d’autres éléments d’un jeu de données. Vous pouvez utiliser des tables temporelles versionnées système pour détecter des anomalies qui surviennent périodiquement ou de façon irrégulière, en utilisant des requêtes temporelles pour localiser rapidement des motifs spécifiques. Ce qui compte comme une anomalie dépend du type de données que vous collectez et de votre logique métier.

L’exemple suivant illustre une logique simplifiée pour la détection des « pics » dans les chiffres de ventes. Supposons que vous travaillez avec une table temporelle qui collecte l’historique des produits achetés :

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

Le diagramme suivant montre les achats dans le temps :

Diagramme montrant les achats dans le temps.

En supposant que, les jours ordinaires, le nombre de produits achetés varie peu, la requête suivante identifie les valeurs aberrantes isolées : des échantillons dont l’écart par rapport à leurs voisins immédiats est significatif (d’un facteur 2), tandis que les échantillons environnants ne présentent pas d’écart significatif (moins de 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

Cet exemple est intentionnellement simplifié. Dans les scénarios de production, vous utiliseriez probablement des méthodes statistiques avancées pour identifier les exemples qui ne suivent pas le modèle courant.

Dimension à variation lente

En règle générale, les dimensions d’entreposage de données contiennent des données relativement statiques sur les entités telles que des produits, des clients ou des emplacements géographiques. Toutefois, dans certains scénarios, vous devez également tracer les modifications de données dans des tables de dimension. Étant donné que les modifications des dimensions se produisent beaucoup moins fréquemment, de manière imprévisible et en dehors du calendrier de mise à jour régulier applicable aux tables de faits, ce type de tables de dimensions est appelé dimensions à changement lent (SCD).

Il existe plusieurs catégories de dimensions à changement progressif selon la manière dont l’histoire des changements est préservée :

Type de dimension Détails
Type 0 L’historique n’est pas conservé. Les attributs de dimension reflètent les valeurs d’origine.
Type 1 les attributs de dimension reflètent les valeurs les plus récentes (les valeurs précédentes sont remplacées)
Type 2 chaque version de membre de dimension représentée par une ligne distincte dans la table, généralement avec des colonnes qui représentent la période de validité
Type 3 Conservation d’un historique limité pour des attributs sélectionnés en utilisant des colonnes supplémentaires dans la même ligne
Type 4 Conservation de l’historique dans une table distincte, tandis que la table de dimension d’origine conserve les versions les plus récentes (actuelles) des membres de dimension

Quand vous choisissez une stratégie de dimension à variation lente (SCD), il incombe à la couche ETL (extraction, transformation et chargement) de garantir l’exactitude des tables de dimensions, ce qui nécessite un code plus complexe et une maintenance supplémentaire.

Vous pouvez utiliser des tables temporelles version système pour réduire considérablement la complexité de votre code, car l’historique des données est automatiquement préservé. Compte tenu de leur implémentation à l’aide de deux tables, les tables temporelles se rapprochent le plus des SCD de type 4. Cependant, étant donné que les requêtes temporelles vous permettent d’interroger uniquement la table actuelle, vous pouvez également envisager d’utiliser des tables temporelles dans les environnements où vous prévoyez d’utiliser des dimensions à évolution lente de type 2 (SCD de type 2).

Pour convertir votre dimension régulière en SCD, vous pouvez en créer une nouvelle ou modifier une dimension existante pour en faire une table temporelle version système. Si votre table de dimensions existante contient des données historiques, créez une table séparée et déplacez les données historiques là-bas, et conservez les versions actuelles (réelles) des dimensions dans votre table de dimensions d’origine. Ensuite, utilisez la syntaxe ALTER TABLE pour convertir votre table de dimension en table temporelle versionnée par le système avec une table d’historique prédéfinie.

L’exemple suivant illustre le processus et suppose que la table de dimensions DimLocation comporte déjà ValidFrom et ValidTo en tant que colonnes non nulles de type datetime2, que le processus ETL alimente :

  • Déplacez les versions de lignes fermées dans la nouvelle table d’historique :

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

  • Créez un index columnstore clusterisé, un bon choix pour les scénarios d’entrepôt de données :

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Supprimez les versions précédentes de DimLocation, qui devient la table courante dans la configuration de versionnement temporel du système :

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

  • Ajouter la définition de la période :

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

  • Activez le système de versionnement et liez la table d’historique à :DimLocation

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

Vous n’avez pas besoin de code supplémentaire pour maintenir un SCD pendant le processus de chargement de l’entrepôt de données après sa création.

L’illustration suivante montre comment utiliser des tables temporelles dans un scénario de base impliquant deux SCD (DimLocation et DimProduct) et une table de faits.

Diagramme montrant comment utiliser des tables temporelles dans un scénario simple impliquant 2 dimensions à variation lente (DimLocation et DimProduct) et une table de faits.

Pour utiliser les SCD précédents dans les rapports, vous devez adapter les requêtes en conséquence. Par exemple, vous souhaiterez calculer le montant total des ventes et le nombre moyen de produits vendus par habitant au cours des six derniers mois. Les deux métriques nécessitent la corrélation des données à partir de la table de faits et des dimensions dont les attributs importants pour l’analyse ont pu évoluer (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

La requête suivante calcule correctement les métriques requises :

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

L’utilisation de tables temporelles versionnées système pour la SCD est acceptable si la période de validité calculée en fonction du temps de transaction de la base de données correspond à la logique métier. Si vous chargez les données avec un délai important, le délai de transaction peut ne pas être acceptable.

Par défaut, les tables temporelles avec versions gérées par le système n’autorisent pas la modification des données historiques après le chargement (vous pouvez modifier l’historique après avoir défini SYSTEM_VERSIONING sur OFF). Ceci peut constituer une limitation dans les cas où les données historiques sont régulièrement modifiées.

Les tables temporelles versionnées par système génèrent une version de ligne à chaque changement de colonne. Si vous souhaitez supprimer de nouvelles versions lors d’un certain changement de colonne, vous devez intégrer cette limitation dans la logique ETL.

Si vous attendez un nombre significatif de lignes historiques dans les tables SCD, envisagez d’utiliser un index columnstore groupé comme principale option de stockage pour la table d’historique. L’utilisation d’un index columnstore réduit l’empreinte de la table d’historique et accélère vos requêtes analytiques.

Réparez une altération des données au niveau des lignes

Vous pouvez vous baser sur les données historiques des tables temporelles versionnées par le système pour rétablir rapidement des lignes individuelles dans tout état précédemment capturé. Cette propriété des tables temporelles est utile quand vous pouvez localiser les lignes affectées et/ou que vous connaissez l’heure de la modification non souhaitée des données. Cette connaissance vous permet d’effectuer des réparations efficacement sans gérer les sauvegardes.

Cette approche présente plusieurs avantages :

  • Vous pouvez contrôler précisément l’étendue de la réparation. Les enregistrements qui ne sont pas affectés doivent rester au dernier état, ce qui est souvent une exigence critique.

  • L’opération est efficace et la base de données reste en ligne pour toutes les charges de travail utilisant les données.

  • L’opération de réparation elle-même est versionnée. Vous avez une trace d’audit pour l’opération de réparation, donc vous pouvez analyser ce qui s’est passé plus tard si nécessaire.

Vous pouvez automatiser l’action de réparation assez facilement. L’exemple de code suivant montre une procédure stockée qui effectue la réparation des données pour la table Employee utilisée dans un scénario d’audit de données.

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;

Cette procédure stockée accepte @EmployeeID et @versionNumber en tant que paramètres d’entrée. Par défaut, il restaure l’état de la ligne à partir de la dernière version de l’historique (@versionNumber = 1).

L’image suivante montre l’état de la rangée avant et après l’invocation de la procédure. Le rectangle rouge marque la version de la ligne actuelle qui est incorrecte, tandis que le rectangle vert marque la version correcte de l’historique.

Capture d’écran montrant l’état de la ligne avant et après l’appel de la procédure. 

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

Capture d’écran montrant la ligne corrigée.

Cette procédure stockée de réparation peut être définie pour accepter un horodatage exact au lieu de la version de ligne. Elle restaure la ligne vers la version qui était active au AS OF moment indiqué.

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;

Pour le même exemple de données, l’image suivante illustre une scénario de réparation avec une condition de temps. Sont mis en évidence le @asOf paramètre, la ligne sélectionnée dans l’historique qui était réelle au moment donné, ainsi que la nouvelle version de la ligne dans le tableau actuel après l’opération de réparation :

Capture d’écran montrant le scénario de réparation avec une condition de temps.

La correction des données peut être intégrée au processus de chargement automatisé des données dans les systèmes d’entreposage de données et de rapports. Si une valeur qui vient d’être mise à jour n’est pas correcte, dans de nombreux scénarios, restaurer la version précédente à partir de l’historique peut suffire. Le diagramme suivant montre comment ce processus peut être automatisé :

Diagramme montrant comment le processus peut être automatisé.

Contenu connexe

  • Last updated on