Zamana bağlı tablo kullanım senaryoları

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL VeritabanıAzure SQL Yönetilen ÖrneğiSQL database in Microsoft Fabric

Sistem sürümüne sahip zamana bağlı tablolar, veri değişikliklerinin izleme geçmişini gerektiren senaryolarda kullanışlıdır. Önemli üretkenlik avantajları için aşağıdaki kullanım örneklerinde zamansal tabloları göz önünde bulundurmanızı öneririz.

Veri denetimi

Kritik bilgileri depolayan tablolarda zamansal sistem sürümü oluşturma özelliğini kullanabilir, nelerin ve ne zaman değiştiğini takip edebilir ve zaman içinde herhangi bir noktada veri adli incelemeleri gerçekleştirebilirsiniz.

Geliştirme döngüsünün erken aşamalarında veri denetimi senaryolarını planlamak için zaman tabloları kullanın. İhtiyacınız olduğunda mevcut uygulamalara veya çözümlere veri denetimi ekleyebilirsiniz.

Aşağıdaki diyagramda, geçerli (mavi renkle işaretlenmiş) ve geçmiş satır sürümleri (gri renkle işaretlenmiş) dahil olmak üzere veri örneğini içeren bir Employee tablosu gösterilmektedir.

Diyagramın sağ tarafı, zaman ekseni üzerindeki satır sürümlerini ve SYSTEM_TIME yan tümcesiyle ya da yan tümcesi olmadan, zamansal bir tabloda farklı sorgu türleri kullanarak seçtiğiniz satırları görselleştirir.

İlk Geçici Kullanım senaryoyu gösteren Diyagramı.

Veri denetimi için yeni bir tabloda sistem sürümü oluşturmayı etkinleştirme

Veri denetimi gerektiren bilgileri belirlerseniz veritabanı tablolarını sistem sürümüne sahip zamana bağlı tablolar olarak oluşturun. Aşağıdaki örnek, varsayımsal bir İK veritabanında Employee adlı bir tablo içeren bir senaryoyu göstermektedir:

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

Zamansal sistem versiyonlu bir tablo oluşturmak için çeşitli seçenekler, Sistem sürümünde zamansal tablo oluşturma bölümünde açıklanmıştır.

Veri denetimi için mevcut bir tabloda sistem sürümü oluşturmayı etkinleştirme

Mevcut veritabanlarında veri denetimi gerçekleştirmeniz gerekiyorsa, zamansal olmayan tabloları sistem sürümüne dönüştürülecek şekilde genişletmek için ALTER TABLE kullanın. Uygulamanızda hataya yol açabilecek değişiklikleri önlemek için, HIDDEN bölümünde açıklandığı gibi dönem sütunlarını olarak ekleyin.

Aşağıdaki örnek, varsayımsal İK veritabanındaki mevcut bir Employee tablosunda sistem sürümleme özelliğini etkinleştirmeyi göstermektedir. İki adımda Employee tablosunda sistem sürümü oluşturma olanağı sağlar. İlk olarak, yeni dönem sütunları HIDDENolarak eklenir. Ardından varsayılan geçmiş tablosunu oluşturur.

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

datetime2 veri türünün duyarlığı, kaynak tablodaki ile sistem sürümlü geçmiş tablosundaki duyarlıkla aynı olmalıdır.

Önceki betiği çalıştırdıktan sonra, geçmiş tablosu tüm veri değişikliklerini şeffaf şekilde toplar. Tipik bir veri denetim senaryosunda, ilgi duyduğunuz bir süre içinde bireysel satıra uygulanan tüm veri değişikliklerini sorgulatırsınız. Varsayılan geçmiş tablosu, bu kullanım örneğini verimli bir şekilde ele almak için kümelenmiş bir satır deposu B ağacıyla oluşturulur.

Uyarı

Belgelerde genellikle dizinlere başvuruda B ağacı terimi kullanılır. Rowstore dizinlerinde Veritabanı Altyapısı bir B+ ağacı uygular. Bu, sütun deposu dizinleri veya bellek için iyileştirilmiş tablolardaki dizinler için geçerli değildir. Daha fazla bilgi için SQL Server ve Azure SQL dizin mimarisi ve tasarım kılavuzuna bakın.

Veri analizi gerçekleştirme

Önceki yaklaşımlardan birini kullanarak sistem sürümü oluşturmayı etkinleştirdikten sonra, veri denetimi yalnızca bir sorgu uzaktadır. Aşağıdaki sorgu, Employee tablosunda bulunan ve 1 Ocak 2021 ile 1 Ocak 2022 (üst sınır dahil) tarihleri arasında en az bir süre boyunca etkin olan EmployeeID = 1000 kayıtlarının satır sürümlerini arar:

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;

söz konusu çalışan için veri değişiklikleri geçmişinin tamamını analiz etmek için FOR SYSTEM_TIME BETWEEN...AND değerini FOR SYSTEM_TIME ALL ile değiştirin:

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

Yalnızca bir süre içinde (dışında değil) etkin olan satır sürümlerini aramak için CONTAINED INkullanın. Bu sorgu yalnızca geçmiş tablosunu sorguladığı için verimlidir:

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;

Son olarak, bazı denetim senaryolarında, tüm tablonun geçmişte herhangi bir zamanda nasıl göründüğünü görmek isteyebilirsiniz:

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

Sistem sürümüne sahip zamana bağlı tablolar, utc saat dilimindeki dönem sütunlarının değerlerini depolar, ancak hem verileri filtreleme hem de sonuçları görüntüleme amacıyla yerel saat diliminizde çalışmayı daha kullanışlı bulabilirsiniz. Aşağıdaki kod örneği, yerel saat diliminde belirtilen ve ardından UTC'ye AT TIME ZONEdönüştürülen bir filtreleme koşulu nasıl uygulanacağını gösterir:

/* 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;

AT TIME ZONE kullanmak, sistem tarafından sürümlenmiş tabloların kullanıldığı diğer tüm senaryolarda yararlıdır.

FOR SYSTEM_TIME ile zamansal yan tümcelerde belirtilen filtreleme koşulları SARGable'dır.

Uyarı

İlişkisel veritabanlarında SARGable terimi, sorgunun yürütülmesini hızlandırmak için bir dizin kullanabilen Search ARGumentable bir yüklemi ifade eder. Daha fazla bilgi için bkz. SQL Server ve Azure SQL dizin mimarisi ve tasarım kılavuzu.

Geçmiş tablosunu doğrudan sorgularsanız, filtreleme koşulunu <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC' biçiminde belirterek bunun da SARGable olduğundan emin olun.

Dönem sütunlarına uygularsanız AT TIME ZONE , SQL Server pahalı olabilecek bir tablo veya dizin taraması gerçekleştirir. Sorgularınızda bu tür bir koşuldan kaçının:

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

Daha fazla bilgi için bkz. Sistem sürümüne sahip bir zamana bağlı tablodaki verileri sorgulama.

Belirli bir zamanda analiz (zaman yolculuğu)

Bireysel kayıtlardaki değişikliklere odaklanmak yerine, zaman yolculuğu senaryoları tüm veri setlerinin zaman içinde nasıl değiştiğini gösterir. Bazen zaman yolculuğu, her biri bağımsız bir hızda değişen birkaç ilgili zaman tablosu içerir ve bunları analiz etmek istersiniz:

  • Geçmiş ve güncel verilerdeki önemli göstergeler için eğilimler
  • Verilerin tamamının geçmişteki herhangi bir noktadaki "itibarıyla" tam anlık görüntüsü (dün, bir ay önce vb.)
  • İlgi duyulan iki zaman noktası arasındaki farklar (örneğin, bir ay önce veya üç ay önce)

Birçok gerçek dünya senaryosu zaman yolculuğu analizi gerektirir. Bu kullanım senaryosunu açıklamak için, otomatik oluşturulan geçmişe sahip çevrimiçi işlem işleme (OLTP) üzerine bakalım.

Otomatik olarak oluşturulan veri geçmişine sahip OLTP

İşlem işleme sistemlerinde, önemli ölçümlerin zaman içinde nasıl değiştiğini analiz edebilirsiniz. İdeal olarak, geçmişi analiz etmek, en son veri durumuna erişimin minimum gecikme ve veri kilitlenmesi ile sağlanması gereken OLTP uygulamasının performansını tehlikeye atmamalıdır. Sistem sürümlü zamansal tabloları kullanarak, geçerli verilerden ayrı olarak, daha sonra analiz için değişikliklerin tam geçmişini şeffaf bir şekilde tutabilir ve ana OLTP iş yükü üzerinde en az etkiyi sağlayabilirsiniz.

SQL Server ve Azure SQL Yönetilen Örneği'ta yüksek işlemsel işlem yükleri için, mevcut verileri bellekte ve diskteki değişikliklerin tam geçmişini maliyet etkin bir şekilde depolamanızı sağlayan Sistem sürümlü zamanlı tablolar ve bellek optimize edilmiş tablolar kullanmanızı öneririz.

Geçmiş tablosu için aşağıdaki nedenlerle kümelenmiş columnstore dizini kullanmanızı öneririz:

  • Tipik eğilim analizi, kümelenmiş columnstore dizini tarafından sağlanan sorgu performansından yararlanır.

  • Bellek için optimize edilmiş tablolarla veri boşaltma görevi, yoğun OLTP iş yükü altında, geçmiş tablosunda kümelenmiş sütun deposu dizini olduğunda en iyi performansı gösterir.

  • Kümelenmiş columnstore dizini, özellikle tüm sütunların aynı anda değiştirilmediği senaryolarda mükemmel sıkıştırma sağlar.

Bellek içi OLTP ile zamansal tabloların kullanılması, tüm veri setinin hafızada tutulması ihtiyacını azaltır ve sıcak ile soğuk veriyi kolayca ayırt etmenizi sağlar.

Bu kategoriye iyi uyan gerçek dünya senaryolarına örnek olarak envanter yönetimi veya para birimi ticareti verilebilir.

Aşağıdaki diyagram, envanter yönetimi için kullanılan basitleştirilmiş bir veri modelini göstermektedir:

envanter yönetimi için kullanılan basitleştirilmiş veri modelini gösteren Diyagramı.

Aşağıdaki kod örneği, geçmiş tablosunda kümelenmiş bir sütun deposu dizini bulunan (bu dizin, varsayılan olarak oluşturulan satır deposu dizininin yerini alır) bellek içi, sistem sürümlü bir zamansal tablo olarak ProductInventory oluşturur:

Uyarı

Veritabanınızın bellek için iyileştirilmiş tablolar oluşturmaya izin verdiğinden emin olun. bkz. Tablo oluşturma ve Yerel Olarak Derlenmiş Saklı YordamMemory-Optimized.

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

Önceki model için envanteri koruma yordamı şu şekilde görünebilir:

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;

spUpdateInventory saklı yordamı envantere yeni bir ürün ekler veya belirli bir konum için ürün miktarını günceller. İş mantığı basittir ve tablo güncellemesiyle alanı Quantity artırıp küçülterek en güncel durumu sürekli doğruluğunu korumaya odaklanırken, sistem versiyonlu tablolar aşağıdaki diyagramda gösterildiği gibi veriye şeffaf bir geçmiş boyutu ekler.

Geçerli kullanım In-Memory zamansal kullanımı ve kümelenmiş bir columnstore'daki geçmiş kullanımı gösteren diyagram.

Şimdi, yerel derlenen modülden en son durumu verimli şekilde sorgulayabilirsiniz:

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

Aşağıdaki örnekte gösterildiği gibi FOR SYSTEM_TIME ALL yan tümcesiyle zaman içindeki veri değişikliklerini çözümlemek kolaylaşır:

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;

Aşağıdaki diyagramda, Power Query, Power BI veya benzer iş zekası aracında önceki görünümü içeri aktararak kolayca işlenebilen bir ürünün veri geçmişi gösterilmektedir:

Bir ürünün veri geçmişini gösteren diyagram.

Bu senaryoda zaman tablolarını kullanarak geçmişteki herhangi bir zaman noktasında envanterin AS OF durumunu yeniden oluşturmak veya farklı zamana ait anlık görüntüleri karşılaştırmak gibi başka tür zaman yolculuğu analizleri yapabilirsiniz.

Bu kullanım senaryosunda, Location ve Product tablolarını zamansal tablolara genişleterek UnitPrice ve NumberOfEmployee üzerindeki değişikliklerin geçmişinin daha sonra analiz edilmesini de sağlayabilirsiniz.

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

Veri modeli artık birden fazla zamansal tabloyu içerdiğinden, analiz için en iyi uygulama AS OF , ilgili tablolardan gerekli verileri çıkarıp görünüme uygulayan FOR SYSTEM_TIME AS OF bir görünüm oluşturmaktır; bu, tüm veri modelinin durumunu yeniden yapılandırmayı büyük ölçüde basitleştirir:

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

Aşağıdaki ekran görüntüsünde SELECT sorgusu için oluşturulan yürütme planı gösterilmektedir. Bu, Zamansal ilişkilerle ilgilenirken Veritabanı Altyapısı'nın tüm karmaşıklığı işlediğini gösterir:

Sql Server Veritabanı Altyapısı'nın zamana bağlı ilişkilerle ilgilenirken tüm karmaşıklığı işlediğini gösteren 'SELECT' sorgusu için oluşturulan yürütme planını gösteren diyagram.

Ürün envanterinin durumunu iki zaman noktası arasında (bir gün önce ve bir ay önce) karşılaştırmak için aşağıdaki kodu kullanın:

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;

Anomali algılama

Anomali tespiti veya outlier detection, beklenen bir desen veya veri setindeki diğer öğelere uymayan öğeleri belirler. Sistem versiyonlu zaman tablolarını kullanarak periyodik veya düzensiz gerçekleşen anomalileri tespit edebilirsiniz; zamansal sorgulama kullanarak belirli desenleri hızlıca bulabilirsiniz. Anomali olarak sayılan şey, topladığınız veri türüne ve iş mantığınıza bağlıdır.

Aşağıdaki örnekte, satış sayılarındaki "ani artışları" algılamaya yönelik basitleştirilmiş mantık gösterilmektedir. Satın alınan ürünlerin geçmişini toplayan bir zamana bağlı tabloyla çalıştığınızı varsayalım:

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

Aşağıdaki diyagramda zaman içindeki satın alma işlemleri gösterilmektedir:

Zaman içindeki satın almaları gösteren Diyagramı.

Normal günlerde satın alınan ürün sayısındaki varyansın düşük olduğunu varsayarsak, aşağıdaki sorgu tekil aykırı değerleri tespit eder: kendisine en yakın komşularına göre farkı anlamlı düzeyde yüksek olan (2 kat), buna karşılık çevresindeki örneklerin birbirinden anlamlı düzeyde farklılaşmadığı (%20’den az) örnekler.

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;

Uyarı

Bu örnek kasıtlı olarak basitleştirilmiştir. Üretim senaryolarında, ortak deseni izlemeyen örnekleri tanımlamak için büyük olasılıkla gelişmiş istatistiksel yöntemler kullanırsınız.

Yavaşça değişen boyutlar

Veri ambarı içindeki boyutlar genellikle coğrafi konumlar, müşteriler veya ürünler gibi varlıklar hakkında nispeten statik veriler içerir. Ancak bazı senaryolarda boyut tablolarındaki veri değişikliklerini de izlemeniz gerekir. Boyutlardaki değişikliklerin çok daha az sıklıkta, öngörülemez bir şekilde ve olgu tablolarına uygulanan düzenli güncelleme programının dışında gerçekleştiği için, bu tür boyut tablolarına yavaş değişen boyutlar (SCD) denir.

Değişim tarihinin korunma şekline bağlı olarak yavaşça değişen boyutların birkaç kategorisi vardır:

Boyut türü Ayrıntılar
Tür 0 Geçmiş korunmaz. Boyut öznitelikleri özgün değerleri yansıtır.
Tür 1 Boyut öznitelikleri en son değerleri yansıtır (önceki değerlerin üzerine yazılır)
Tür 2 Tablodaki ayrı satırla temsil edilen boyut üyesinin her sürümü genellikle geçerlilik süresini temsil eden sütunlarla gösterilir
Tür 3 Aynı satırda ek sütunlar kullanarak seçili öznitelikler için sınırlı geçmiş tutma
Tür 4 Asıl boyut tablosu en son (geçerli) boyut üyesi sürümlerini tutarken, geçmişi ayrı bir tabloda saklama.

Bir SCD stratejisi seçtiğinizde, boyut tablolarını doğru tutmak ETL katmanının (Ayıkla-Transform-Load) sorumluluğundadır ve bu da genellikle daha karmaşık kod ve ek bakım gerektirir.

Sistem sürümündeki zaman tablolarını kullanarak kodunuzun karmaşıklığını önemli ölçüde azaltabilirsiniz, çünkü veri geçmişi otomatik olarak korunur. İki tablo kullanan uygulaması göz önünde bulundurulduğunda, zamana bağlı tablolar Type 4 SCD'ye en yakın olanlardır. Ancak, zamana bağlı sorgular yalnızca geçerli tabloya başvurmanıza olanak tanıydığından, Tür 2 SCD kullanmayı planladığınız ortamlardaki zamansal tabloları da göz önünde bulundurabilirsiniz.

Normal boyutunuzu SCD'ye dönüştürmek için yeni bir boyut oluşturabilir veya mevcut bir boyutu sistem versiyonlu zamansal tabloya dönüştürebilirsiniz. Mevcut boyut tablonuzda tarihsel veri varsa, ayrı bir tablo oluşturun ve tarihsel verileri oraya taşıyıp güncel (gerçek) boyut sürümlerini orijinal boyut tablonuzda tutun. Ardından ALTER TABLE söz dizimini kullanarak boyut tablonuzu önceden tanımlanmış geçmiş tablosuyla sistem sürümüne sahip bir zamana bağlı tabloya dönüştürün.

Aşağıdaki örnek süreci gösterir ve boyut tablosunda DimLocation zaten ValidFromValidTove datetime2 olarak geçersiz sütunlara sahip olduğunu varsayar; ETL süreci bunları doldurur:

  • Kapalı satır versiyonlarını yeni tarih tablosuna taşıyın:

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

  • Kümelenmiş bir columnstore indeksi oluşturun, veri deposu senaryolarında iyi bir seçimdir:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Önceki sürümleri silin DimLocation, bu da zamansal sistem versiyonlama yapılandırmasında mevcut tablo olur:

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

  • Dönem tanımını ekleyin:

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

  • Sistem sürümünü etkinleştirin ve geçmiş tablosunu şu DimLocationadrese bağlayın:

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

SCD'yi oluşturduktan sonra veri deposu yükleme sürecinde bir SCD'yi sürdürmek için ekstra koda ihtiyacınız yok.

Aşağıdaki illüstrasyon, iki SCD (DimLocation ve DimProduct) ve bir gerçek tablosunu içeren temel bir senaryoda zaman tablolarını nasıl kullanabileceğinizi gösterir.

2 SCD (DimLocation ve DimProduct) ve bir olgu tablosu içeren basit bir senaryoda geçici tabloları nasıl kullanabileceğinizi gösteren diyagram.

Önceki SCD'leri raporlarda kullanmak için sorgulama yapmayı etkili bir şekilde ayarlamanız gerekir. Örneğin, son altı ay için toplam satış tutarını ve kişi başına satılan ürünlerin ortalama sayısını hesaplamak isteyebilirsiniz. Her iki ölçüm de olgu tablosundaki verilerin ve analiz için önemli özniteliklerini değiştirmiş olabilecek boyutların bağıntısını gerektirir (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

Aşağıdaki sorgu gerekli ölçümleri düzgün bir şekilde hesaplar:

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

SCD için sistem sürümünde zamansal tabloların kullanılması, veritabanı işlem süresine göre hesaplanan geçerlilik süresi iş mantığınız için uygunsa kabul edilebilir. Verileri önemli bir gecikmeyle yüklerseniz, işlem süresi kabul edilebilir olmayabilir.

Varsayılan olarak, sistem tarafından sürümlenmiş zamana bağlı tablolar yüklendikten sonra geçmiş verilerinin değiştirilmesine izin vermez (SYSTEM_VERSIONINGOFFolarak ayarladıktan sonra geçmişi değiştirebilirsiniz). Bu, geçmiş verileri değiştirmenin düzenli olarak gerçekleştiği durumlarda bir sınırlama olabilir.

Zamansal sistem versiyonlu tablolar, herhangi bir sütun değişikliğinde satır versiyonu oluşturur. Belirli bir sütun değişikliğinde yeni sürümleri bastırmak istiyorsanız, bu kısıtlamayı ETL mantığına dahil etmeniz gerekir.

SCD tablolarında önemli sayıda tarihsel satır bekliyorsanız, tarih tablosu için ana depolama seçeneği olarak kümelenmiş bir columnstore indeksi kullanmayı düşünün. Columnstore dizini kullanmak, tarihçe tablosunun kapladığı alanı azaltır ve analiz amaçlı sorgularınızı hızlandırır.

Satır düzeyi veri bozulmalarını onarma

Bireysel satırları daha önceki herhangi bir duruma hızla geri yüklemek için sistem versiyonlu zamansal tablolardaki geçmiş verilere güvenebilirsiniz. Zamansal tabloların bu özelliği, etkilenen satırları bulabildiğinizde ve/veya istenmeyen veri değişikliği zamanını bildiğinizde kullanışlıdır. Bu bilgi, yedeklemelerle uğraşmadan onarımı verimli bir şekilde gerçekleştirmenizi sağlar.

Bu yaklaşımın çeşitli avantajları vardır:

  • Onarımın kapsamını tam olarak denetleyebilirsiniz. Etkilenmeyen kayıtların en son durumda kalması gerekir ve bu genellikle kritik bir gereksinimdir.

  • İşlem verimlidir ve verileri kullanan tüm iş yükleri için veritabanı çevrimiçi kalır.

  • Onarım işleminin kendisi versiyonlanmıştır. Onarım operasyonu için bir denetim iziniz var, böylece gerekirse daha sonra ne olduğunu analiz edebilirsiniz.

Onarım işlemini nispeten kolayca otomatikleştirebilirsiniz. Aşağıdaki kod örneği, veri denetim senaryosunda kullanılan tablo Employee için veri onarımı gerçekleştiren bir depolanmış prosedürü göstermektedir.

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;

Bu saklı yordam, giriş parametreleri olarak @EmployeeID ve @versionNumber'i alır. Varsayılan olarak geçmişten (@versionNumber = 1) satır durumunu son sürüme geri getirir.

Aşağıdaki resim, prosedür çağrısından önceki ve sonrakı sıranın durumunu göstermektedir. Kırmızı dikdörtgen, mevcut sırada yanlış olan versiyonu, yeşil dikdörtgen ise tarihten doğru versiyonu işaretler.

Yordam çağrısından önceki ve sonraki satırın durumunu gösteren ekran görüntüsü. 

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

Düzeltilen satırı gösteren ekran görüntüsü.

Bu onarım kayıtlı yordamı, satır sürümü yerine tam bir zaman damgası alabilmek için olarak tanımlanabilir. Belirtilen zaman noktasında (AS OF zaman noktası), etkin olan herhangi bir sürüme satırı geri yükler.

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;

Aynı veri örneği için aşağıdaki resimde zaman koşuluna sahip bir onarım senaryosu gösterilmektedir. Vurgulananlar, @asOf parametresi, tarihte belirtilen anda geçerli olan seçili satır ve onarım işleminden sonra geçerli tablodaki yeni satır sürümüdür:

Zaman koşuluna sahip onarım senaryolarını gösteren ekran görüntüsü.

Veri düzeltme, veri ambarı ve raporlama sistemlerinde otomatik veri yüklemenin bir parçası olabilir. Yeni güncelleştirilen bir değer doğru değilse, birçok senaryoda geçmişe ait önceki sürümü geri yüklemek yeterli risk azaltma işlemidir. Aşağıdaki diyagramda bu işlemin nasıl otomatik hale getirildiği gösterilmektedir:

İşlemin nasıl otomatik hale alınabileceğini gösteren Diyagramı.

İlgili içerik

  • Last updated on