Zamana bağlı tablo kullanım senaryoları

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL DatabaseAzure SQL Managed InstanceSQL 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.

Zamana bağlı tablolar, geliştirme döngüsünün ilk aşamalarında veri denetimi senaryoları planlamanıza veya ihtiyacınız olduğunda mevcut uygulamalara veya çözümlere veri denetimi eklemenize olanak tanır.

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ında satır sürümleri bir zaman ekseninde, seçtiğiniz satırlar ise SYSTEM_TIME yan tümcesiyle veya SYSTEM_TIME yan tümcesi olmadan zamansal tabloda farklı sorgu türleriyle görselleştirilmiştir.

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

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, İK veritabanında Employee adlı tabloyu 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));

Zaman temelli sistem sürümüne sahip bir tablo oluşturmak için çeşitli seçenekler, sistem sürümüne sahip zaman temelli bir tablo oluşturmabaşlıkları altında 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 neden olan değişiklikleri önlemek için, HIDDENolarak nokta sütunlarını ekleyin, bu sistem sürümlü bir zamana bağlı tablo oluşturma bölümünde açıklandığı gibidir.

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 yürüttkten sonra tüm veri değişiklikleri geçmiş tablosunda saydam olarak toplanır. Tipik veri denetimi senaryosunda, ilgilendiğiniz bir süre içinde tek bir satıra uygulanan tüm veri değişikliklerini sorgularsı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.

Note

Belgelerde genellikle dizinlere başvuruda B ağacı terimi kullanılır. Rowstore dizinlerinde Veritabanı Altyapısı bir B+ ağacı uygular. Bu, columnstore dizinleri veya bellek için optimize edilmiş 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 tablonun geçmişte herhangi bir noktada 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 SQL Server 2016'da (13.x) kullanıma sunulan AT TIME ZONEkullanılarak UTC'ye dönüştürülen bir filtreleme koşulunun 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.

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

Note

İlişkisel veritabanlarında SARGable terimi, sorgunun yürütülmesini hızlandırmak için bir dizin kullanabilen bir Search ARGbağımsız değişken koşulunu 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, <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'biçiminde filtreler belirterek filtreleme koşulunuzun da SARG özellikli 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)

Tek tek kayıtlarda yapılan değişikliklere odaklanmak yerine, zaman yolculuğu senaryoları veri kümelerinin tamamının zaman içinde nasıl değiştiğini gösterir. Bazen zaman yolculuğu, birbiriyle ilgili birden fazla zaman tablosu içerir, her biri bağımsız bir hızda değişir 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)

Zaman yolculuğu analizi gerektiren birçok gerçek dünya senaryosu vardır. Bu kullanım senaryolarını göstermek için, otomatik olarak oluşturulan geçmişe sahip OLTP'ye göz atalı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, verilerin en son durumuna minimum gecikme ve veri kilitleme ile erişim sağlanan 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'ndeki yüksek işlem iş yükleri için, geçerli verileri bellek içinde depolamanıza ve diskteki değişikliklerin tam geçmişini uygun maliyetli bir şekilde depolamanıza olanak tanıyan bellek için iyileştirilmiş tablolarlaSistem sürümüne sahip zamana bağlı 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.

Zamansal tabloların bellek içi OLTP ile kullanılması, veri kümesinin tamamını bellek içinde tutma gereksinimini azaltır ve sık erişimli ve soğuk verileri kolayca ayırt etmenize olanak tanır.

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

Aşağıdaki diyagramda envanter yönetimi için kullanılan basitleştirilmiş veri modeli gösterilmektedir:

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

Aşağıdaki kod örneği, geçmiş tablosunda kümelenmiş columnstore dizinine sahip, bellek içi ve sistem sürümlü bir zamansal tablo olarak ProductInventory'ı oluşturur (bu, aslında varsayılan olarak oluşturulan satır depolama dizininin yerini alır):

Note

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 table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
            SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];
END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    --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üncelleştirmesi aracılığıyla Quantity alanını arttırarak/azaltarak her zaman en son durumu doğru bir şekilde korumaya odaklanır. Sistem sürümlendirilen tablolar ise, aşağıdaki diyagramda gösterildiği gibi, verilere saydam bir şekilde geçmiş boyutu ekler.

Artık en son durumun sorgulanması, yerel olarak derlenmiş modülden verimli bir şekilde gerçekleştirilebilir:

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:

Zamansal tablolar, geçmişteki herhangi bir noktaya ait envanter AS OF durumunu yeniden yapılandırmak veya zaman içinde farklı anlara ait anlık görüntüleri karşılaştırmak gibi diğer zaman yolculuğu analizi türlerini gerçekleştirmek için bu senaryoda kullanılabilir.

Bu kullanım senaryosunda, UnitPrice ve NumberOfEmployeedeğişiklik geçmişini daha sonra analiz etmek için Ürün ve Konum tablolarını zamana bağlı tablolar haline gelecek şekilde genişletebilirsiniz.

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 çok zamansal tablo içerdiğinden, AS OF analizi için en iyi yöntem, ilgili tablolardan gerekli verileri ayıklayan ve görünüme FOR SYSTEM_TIME AS OF uygulayan bir görünüm oluşturmaktır; çünkü bu, veri modelinin tamamının durumunu yeniden oluşturmayı büyük ölçüde kolaylaştırır:

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:

Ürün envanterinin durumunu zaman içinde iki nokta (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 algılama (veya aykırı değerleri algılama), beklenen bir desene veya veri kümesindeki diğer öğelere uymayan öğelerin tanımlanmasıdır. Belirli desenleri hızla bulmak için zamansal sorgulamayı kullanabileceğiniz gibi düzenli aralıklarla veya düzensiz olarak oluşan anomalileri algılamak için sistem sürümüne sahip zamana bağlı tabloları kullanabilirsiniz. Anomalinin ne olduğu, topladığınız verilerin 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

Normal günlerde satın alınan ürün sayısının küçük bir varyansa sahip olduğunu varsayarsak, aşağıdaki sorgu tekil aykırı değerleri tanımlar: yakın komşularına kıyasla fark olan örnekler önemlidir (2x), çevresindeki örnekler önemli ölçüde farklılık göstermez (20%'den az):

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

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. Boyutlarda yapılan değişikliklerin tahmin edilemeyen şekilde ve olgu tabloları için geçerli olan normal güncelleştirme zamanlaması dışında çok daha az sıklıkta gerçekleştiği göz önünde bulundurulduğunda, bu tür boyut tabloları yavaş değişen boyutlar (SCD) olarak adlandırılır.

Değişikliklerin geçmişinin nasıl korunduğuna bağlı olarak yavaş değişen boyutların çeşitli kategorileri vardır:

Boyut türü	Details
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üne sahip zamana bağlı tablolar, veri geçmişi otomatik olarak korunduğu için kodunuzun karmaşıklığını önemli ölçüde azaltmak için kullanılabilir. İ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 tane oluşturabilir veya mevcut bir boyutu sistem sürümüne sahip geçici tablo olacak şekilde değiştirebilirsiniz. Mevcut boyut tablonuz geçmiş verileri içeriyorsa, ayrı bir tablo oluşturun ve geçmiş verileri oraya taşıyın ve geçerli (gerçek) boyut sürümlerini özgün 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 işlemi gösterir ve DimLocation boyut tablosunun ETL işlemi tarafından doldurulan ValidFrom null atanamayan sütunlar olarak zaten ValidTo ve olduğunu varsayar:

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

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

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

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

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

ScD'yi oluşturduktan sonra veri ambarı yükleme işlemi sırasında korumak için ek kod gerekmez.

Aşağıdaki çizimde, iki SCD (DimLocation ve DimProduct) ve bir olgu tablosu içeren basit bir senaryoda geçici tabloları nasıl kullanabileceğiniz gösterilmektedir.

Raporlarda önceki SCD'leri kullanmak için sorguyu 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

Veritabanı işlem süresine göre hesaplanan geçerlilik süresi iş mantığınızla uyumluysa, SCD için sistem sürümüne dayalı zamana bağlı tabloların kullanılması 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.

Zamana bağlı sistem sürümlü tablolar, herhangi bir sütun değişikliğinde satır sürümü oluşturur. Belirli sütun değişikliklerinde yeni sürümlerin gizlenmesini istiyorsanız, bu sınırlamayı ETL mantığına eklemeniz gerekir.

SCD tablolarında önemli sayıda geçmiş satırı bekliyorsanız, geçmiş tablosu için ana depolama seçeneği olarak kümelenmiş columnstore dizini kullanmayı göz önünde bulundurun. 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 işleminin kendisi için denetim izine sahipsiniz, böylece gerekirse daha sonra ne olduğunu analiz edebilirsiniz.

Onarım eylemi, göreli kolaylıkla otomatikleştirilebilir. Sonraki kod örneğinde, veri denetimi senaryosunda kullanılan tablo Employee için veri onarımı gerçekleştiren bir saklı yordam gösterilmektedir.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord (
    @EmployeeID INT,
    @versionNumber INT = 1
)
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID
        ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999
          AND Employee.EmployeeID = @EmployeeID)

/* Update current row by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee 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. Bu yordam, satır durumunu varsayılan olarak geçmişten son sürüme (@versionNumber = 1) geri yükler.

Aşağıdaki resimde, yordam çağrısından önceki ve sonraki satırın durumu gösterilmektedir. Kırmızı dikdörtgen geçerli satır sürümünü yanlış olarak işaretlerken, yeşil dikdörtgen geçmişe ait doğru sürümü işaretler.

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

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. Vurgulanan @asOf parametresi, sağlanan noktada geçerli olan geçmişteki seçili satır ve onarım işleminden sonra geçerli tabloda yeni satır versiyonu:

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

İlgili içerik

• Zamana bağlı tablolar
• Sistem sürümüne sahip zamana bağlı tabloları kullanmaya başlama
• Zamansal tablo sistemi tutarlılık denetimleri
• Zaman tabloları ile Bölümlendirme
• Zaman tablosuyla ilgili önemli noktalar ve sınırlamalar
• Zamansal tablo güvenliği
• Bellek için iyileştirilmiş tablolar sistem sürümüne sahip zamana bağlı tabloları
• Zamansal tablo meta veri görünümleri ve işlevleri