Skenario penggunaan tabel temporal

Berlaku untuk:SQL Server

Tabel Temporal berguna dalam skenario yang memerlukan riwayat pelacakan perubahan data. Kami menyarankan Anda untuk mempertimbangkan Tabel Temporal dalam kasus penggunaan berikut untuk manfaat produktivitas utama.

Audit data

Gunakan penerapan versi sistem temporal pada tabel yang menyimpan informasi penting yang perlu Anda lacak apa yang telah berubah dan kapan, dan untuk melakukan forensik data kapan saja.

Tabel versi sistem temporal memungkinkan Anda merencanakan skenario audit data pada tahap awal siklus pengembangan atau menambahkan audit data ke aplikasi atau solusi yang ada saat Anda membutuhkannya.

Diagram berikut menunjukkan skenario tabel Karyawan dengan sampel data termasuk saat ini (ditandai dengan warna biru) dan versi baris historis (ditandai dengan warna abu-abu). Bagian kanan diagram memvisualisasikan versi baris pada sumbu waktu dan apa saja baris yang Anda pilih dengan berbagai jenis kueri pada tabel temporal dengan atau tanpa klausa SYSTEM_TIME.

Mengaktifkan penerapan versi sistem pada tabel baru untuk audit data

Jika Anda telah mengidentifikasi informasi yang memerlukan audit data, buat tabel database sebagai versi sistem temporal. Contoh sederhana berikut mengilustrasikan skenario dengan informasi Karyawan dalam database SDM hipotetis:

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

Berbagai opsi untuk membuat tabel versi sistem temporal dijelaskan dalam Membuat Tabel Temporal System-Versioned.

Mengaktifkan penerapan versi sistem pada tabel yang sudah ada untuk audit data

Jika Anda perlu melakukan audit data dalam database yang ada, gunakan ALTER TABLE untuk memperluas tabel non-temporal agar menjadi versi sistem. Untuk menghindari perubahan yang melanggar dalam aplikasi Anda, tambahkan kolom titik sebagai TERSEMBUNYI, seperti yang dijelaskan dalam Ubah Tabel Non-Temporal agar System-Versioned Tabel Temporal. Contoh berikut mengilustrasikan mengaktifkan penerapan versi sistem pada tabel Karyawan yang ada dalam database SDM hipotetis:

/*
Turn ON system versioning in Employee table in two steps
(1) add new period columns (HIDDEN)
(2) create default history table
*/
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));

Penting

Presisi jenis data datetime2 sama dalam tabel sumber dan dalam tabel riwayat versi sistem.

Setelah menjalankan skrip di atas, semua perubahan data akan dikumpulkan secara transparan dalam tabel riwayat. Dalam skenario audit data umum, Anda akan mengkueri semua perubahan data yang diterapkan ke baris individual dalam jangka waktu tertentu. Tabel riwayat default dibuat dengan B-Tree penyimpanan baris terkluster untuk mengatasi kasus penggunaan ini secara efisien.

Melakukan analisis data

Setelah mengaktifkan penerapan versi sistem menggunakan salah satu pendekatan di atas, audit data hanyalah satu kueri yang jauh dari Anda. Kueri berikut mencari versi baris untuk rekaman Karyawan dengan EmployeeID = 1000 yang aktif setidaknya untuk sebagian periode antara 1 Januari 2021 dan 1 Januari 2022 (termasuk batas atas):

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;

Ganti FOR SYSTEM_TIME BETWEEN...AND dengan FOR SYSTEM_TIME ALL untuk menganalisis seluruh riwayat perubahan data untuk karyawan tertentu:

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

Untuk mencari versi baris yang hanya aktif dalam periode (dan bukan di luarnya), gunakan CONTAINED IN. Kueri ini efisien karena hanya mengkueri tabel riwayat:

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;

Terakhir, dalam beberapa skenario audit, Anda mungkin ingin melihat bagaimana seluruh tabel terlihat seperti kapan saja di masa lalu:

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

Tabel temporal versi sistem menyimpan nilai untuk kolom periode di zona waktu UTC, tetapi Anda mungkin merasa lebih nyaman untuk bekerja di zona waktu lokal Anda, baik untuk memfilter data maupun menampilkan hasil. Sampel kode berikut menunjukkan cara menerapkan kondisi pemfilteran, yang ditentukan di zona waktu lokal lalu dikonversi ke UTC menggunakan AT TIME ZONE diperkenalkan pada SQL Server 2016 (13.x):

/*Add offset of the local time zone to current time*/
DECLARE @asOf 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;

Penggunaan AT TIME ZONE berguna dalam semua skenario lain di mana tabel versi sistem digunakan.

Tip

Kondisi pemfilteran yang ditentukan dalam klausul temporal dengan FOR SYSTEM_TIME adalah SARGable. (SARGable berarti bahwa SQL Server dapat menggunakan indeks berkluster yang mendasar untuk melakukan pencarian alih-alih operasi pemindaian. Untuk informasi selengkapnya, lihat Panduan Arsitektur dan Desain Indeks SQL Server.) Jika Anda mengkueri tabel riwayat secara langsung, pastikan kondisi pemfilteran Anda juga dapat SARG dengan menentukan filter dalam bentuk \<period column> {< | > | =, ...} date_condition AT TIME ZONE 'UTC'. Jika Anda menerapkan AT TIME ZONE ke kolom titik, SQL Server akan melakukan pemindaian tabel/indeks, yang bisa sangat mahal. Hindari jenis kondisi ini dalam kueri Anda: \<period column> AT TIME ZONE '\<your time zone>' > {< | > | =, ...} date_condition.

Lihat juga: Mengkueri Data dalam Tabel Temporal System-Versioned.

Analisis titik waktu (perjalanan waktu)

Tidak seperti audit data, di mana fokus biasanya pada perubahan yang terjadi pada rekaman individual, dalam skenario perjalanan waktu pengguna ingin melihat bagaimana seluruh himpunan data berubah dari waktu ke waktu. Terkadang perjalanan waktu mencakup beberapa tabel temporal terkait, masing-masing berubah dengan kecepatan independen, yang ingin Anda analisis:

• Tren untuk indikator penting dalam data historis dan saat ini
• Rekam jepret yang tepat dari seluruh data "per" setiap titik waktu di masa lalu (kemarin, sebulan yang lalu, dll.)
• Perbedaan di antara dua titik pada waktu yang diminati (sebulan yang lalu vs. tiga bulan yang lalu, misalnya)

Ada banyak skenario dunia nyata yang memerlukan analisis perjalanan waktu. Untuk mengilustrasikan skenario penggunaan ini, mari kita lihat OLTP dengan riwayat yang dihasilkan secara otomatis.

OLTP dengan riwayat data yang dibuat secara otomatis

Dalam sistem pemrosesan transaksi, Anda dapat menganalisis seberapa penting metrik berubah dari waktu ke waktu. Idealnya, menganalisis riwayat seharusnya tidak membahayakan performa aplikasi OLTP di mana akses ke status data terbaru harus terjadi dengan latensi minimal dan penguncian data. Anda dapat menggunakan tabel temporal versi sistem untuk secara transparan menyimpan riwayat lengkap perubahan untuk analisis nanti, secara terpisah dari data saat ini, dengan dampak minimal pada beban kerja OLTP utama.

Untuk beban kerja pemrosesan transaksional yang tinggi, kami sarankan Anda menggunakan Tabel Temporal Versi Sistem dengan tabel Memory-Optimized, yang memungkinkan Anda menyimpan data saat ini dalam memori dan riwayat penuh perubahan pada disk dengan cara yang hemat biaya.

Untuk tabel riwayat, kami sarankan Anda menggunakan indeks penyimpan kolom berkluster karena alasan berikut:

• Manfaat analisis tren umum dari performa kueri yang disediakan oleh indeks penyimpan kolom berkluster.
• Tugas flush data dengan tabel yang dioptimalkan memori berkinerja terbaik di bawah beban kerja OLTP berat ketika tabel riwayat memiliki indeks penyimpan kolom berkluster.
• Indeks penyimpan kolom berkluster memberikan kompresi yang sangat baik, terutama dalam skenario di mana tidak semua kolom diubah pada saat yang sama.

Menggunakan tabel temporal dengan OLTP dalam memori mengurangi kebutuhan untuk menjaga seluruh himpunan data dalam memori dan memungkinkan Anda untuk dengan mudah membedakan antara data panas dan dingin.

Contoh skenario dunia nyata yang cocok dengan kategori ini adalah manajemen inventaris atau perdagangan mata uang, antara lain.

Diagram berikut menunjukkan model data yang disederhanakan yang digunakan untuk manajemen inventori:

Contoh kode berikut membuat ProductInventory sebagai tabel temporal versi sistem dalam memori dengan indeks penyimpan kolom berkluster pada tabel riwayat (yang benar-benar menggantikan indeks penyimpanan baris yang dibuat secara default):

Catatan

Pastikan database Anda memungkinkan pembuatan tabel yang dioptimalkan memori. Lihat Membuat Tabel Memory-Optimized dan Prosedur Tersimpan yang Dikompilasi Secara Asli.

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 ,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),

    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId)
)
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);

Untuk model di atas ini adalah bagaimana prosedur untuk mempertahankan inventaris bisa terlihat seperti:

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 than this is insert of the new product for a given location*/
    IF @@rowcount = 0
        BEGIN
            IF @quantityIncrement < 0
                SET @quantityIncrement = 0
            INSERT INTO [dbo].[ProductInventory]
                (
                    [ProductId]
                    ,[LocationID]
                    ,[Quantity]
                )
                VALUES
                   (
                        @productId
                       ,@locationId
                       ,@quantityIncrement
                   )
        END
END;

Prosedur spUpdateInventory tersimpan menyisipkan produk baru dalam inventaritas atau memperbarui kuantitas produk untuk lokasi tertentu. Logika bisnis sederhana dan berfokus pada mempertahankan status terbaru yang akurat sepanjang waktu dengan menambah/menurunkan bidang Kuantitas melalui pembaruan tabel, sementara tabel versi sistem secara transparan menambahkan dimensi riwayat ke data, seperti yang digambarkan pada diagram di bawah ini.

Sekarang, kueri status terbaru dapat dilakukan secara efisien dari modul yang dikompilasi secara asli:

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
EXEC [dbo].[spQueryInventoryLatestState];

Menganalisis perubahan data dari waktu ke waktu menjadi mudah dengan klausa FOR SYSTEM_TIME ALL, seperti yang ditunjukkan dalam contoh berikut:

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;

Diagram di bawah ini memperlihatkan riwayat data untuk satu produk yang dapat dengan mudah dirender mengimpor tampilan di atas di Power Query, Power BI, atau alat kecerdasan bisnis serupa:

Tabel temporal dapat digunakan dalam skenario ini untuk melakukan jenis analisis perjalanan waktu lainnya, seperti rekonstruksi status inventaris DARI titik waktu mana pun di masa lalu atau membandingkan rekam jepret yang termasuk dalam momen yang berbeda pada waktunya.

Untuk skenario penggunaan ini, Anda juga dapat memperluas tabel Produk dan Lokasi untuk menjadi tabel temporal untuk mengaktifkan analisis selanjutnya tentang riwayat perubahan UnitPrice dan 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));

Karena model data sekarang melibatkan beberapa tabel temporal, praktik terbaik untuk AS OF analisis adalah membuat tampilan yang mengekstrak data yang diperlukan dari tabel terkait dan berlaku FOR SYSTEM_TIME AS OF untuk tampilan karena ini akan sangat menyederhanakan rekonstruksi status seluruh model data:

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
JOIN dbo.Product AS P ON PrInv.ProductId = P.ProductID
JOIN dbo.Location AS L ON PrInv.LocationId = L.LocationID;
GO
SELECT * FROM vw_ProductInventoryDetails
    FOR SYSTEM_TIME AS OF '2022-01-01';

Cuplikan layar berikut menunjukkan rencana eksekusi yang dihasilkan untuk SELECT kueri. Ini menggambarkan bahwa semua kompleksitas berurusan dengan hubungan temporal sepenuhnya ditangani oleh mesin SQL Server:

Gunakan kode berikut untuk membandingkan status inventori produk antara dua titik waktu (sehari yang lalu dan sebulan yang lalu):

DECLARE @dayAgo datetime2 = DATEADD (day, -1, SYSUTCDATETIME());
DECLARE @monthAgo 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
JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Deteksi anomali

Deteksi anomali (atau deteksi outlier) adalah identifikasi item yang tidak sesuai dengan pola yang diharapkan atau item lain dalam himpunan data. Anda dapat menggunakan tabel temporal versi sistem untuk mendeteksi anomali yang terjadi secara berkala atau tidak teratur karena Anda dapat menggunakan kueri temporal untuk menemukan pola tertentu dengan cepat. Anomali apa yang bergantung pada jenis data yang Anda kumpulkan dan logika bisnis Anda.

Contoh berikut menunjukkan logika yang disederhanakan untuk mendeteksi "lonjakan" dalam nomor penjualan. Mari kita asumsikan bahwa Anda bekerja dengan tabel temporal yang mengumpulkan riwayat produk yang dibeli:

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

Diagram berikut menunjukkan pembelian dari waktu ke waktu:

Dengan asumsi bahwa selama hari biasa jumlah produk yang dibeli memiliki varian kecil, kueri berikut mengidentifikasi outlier singleton: sampel yang perbedaannya dibandingkan dengan tetangga langsung mereka signifikan (2x), sementara sampel di sekitarnya tidak berbeda secara signifikan (kurang dari 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;

Catatan

Contoh ini sengaja disederhanakan. Dalam skenario produksi, Anda mungkin akan menggunakan metode statistik tingkat lanjut untuk mengidentifikasi sampel yang tidak mengikuti pola umum.

Dimensi yang berubah secara perlahan

Dimensi dalam pergudangan data biasanya berisi data yang relatif statis tentang entitas seperti lokasi geografis, pelanggan, atau produk. Namun, beberapa skenario mengharuskan Anda untuk melacak perubahan data dalam tabel dimensi juga. Mengingat bahwa modifikasi dalam dimensi terjadi jauh lebih jarang, dengan cara yang tidak dapat diprediksi dan di luar jadwal pembaruan reguler yang berlaku untuk tabel fakta, jenis tabel dimensi ini disebut dimensi yang berubah secara perlahan (SCD).

Ada beberapa kategori dimensi yang berubah secara perlahan berdasarkan bagaimana riwayat perubahan dipertahankan:

• Jenis 0: Riwayat tidak dipertahankan. Atribut dimensi mencerminkan nilai asli.
• Jenis 1: Atribut dimensi mencerminkan nilai terbaru (nilai sebelumnya ditimpa)
• Jenis 2: Setiap versi anggota dimensi diwakili dengan baris terpisah dalam tabel biasanya dengan kolom yang mewakili periode validitas
• Jenis 3: Menyimpan riwayat terbatas untuk atribut yang dipilih menggunakan kolom tambahan di baris yang sama
• Jenis 4: Menyimpan riwayat dalam tabel terpisah sementara tabel dimensi asli menyimpan versi anggota dimensi terbaru (saat ini)

Ketika Anda memilih strategi SCD, lapisan ETL (Extract-Transform-Load) bertanggung jawab untuk menjaga tabel dimensi tetap akurat, yang biasanya memerlukan kode yang lebih kompleks dan pemeliharaan ekstra.

Tabel temporal versi sistem dapat digunakan untuk secara dramatis menurunkan kompleksitas kode Anda karena riwayat data secara otomatis dipertahankan. Mengingat implementasinya menggunakan dua tabel, tabel temporal paling dekat dengan SCD Tipe 4. Namun, karena kueri temporal memungkinkan Anda untuk mereferensikan tabel saat ini saja, Anda juga dapat mempertimbangkan tabel temporal di lingkungan tempat Anda berencana menggunakan SCD Tipe 2.

Untuk mengonversi dimensi reguler Anda ke SCD, Anda dapat membuat yang baru atau mengubah yang sudah ada untuk menjadi tabel temporal versi sistem. Jika tabel dimensi Anda yang sudah ada berisi data historis, buat tabel terpisah dan pindahkan data historis ke sana dan simpan versi dimensi saat ini (aktual) dalam tabel dimensi asli Anda. Kemudian gunakan sintaks ALTER TABLE untuk mengonversi tabel dimensi Anda ke tabel temporal versi sistem dengan tabel riwayat yang telah ditentukan sebelumnya.

Contoh berikut mengilustrasikan proses dan mengasumsikan bahwa tabel dimensi DimLocation sudah memiliki ValidFrom dan ValidTo sebagai kolom datetime2 yang tidak dapat diubah ke null, yang diisi oleh proses ETL:

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

Tidak ada kode tambahan yang diperlukan untuk mempertahankan SCD selama proses pemuatan gudang data setelah Anda membuatnya.

Ilustrasi berikut menunjukkan bagaimana Anda dapat menggunakan Tabel Temporal dalam skenario sederhana yang melibatkan 2 SCD (DimLocation dan DimProduct) dan satu tabel fakta.

Untuk menggunakan SCD di atas dalam laporan, Anda perlu menyesuaikan kueri secara efektif. Misalnya, Anda mungkin ingin menghitung jumlah total penjualan dan jumlah rata-rata produk yang dijual per kapita selama enam bulan terakhir. Kedua metrik memerlukan korelasi data dari tabel fakta dan dimensi yang mungkin telah mengubah atributnya penting untuk analisis (DimLocation.NumOfCustomers, DimProduct.UnitPrice). Kueri berikut menghitung metrik yang diperlukan dengan benar:

DECLARE @now datetime2 = SYSUTCDATETIME()
DECLARE @sixMonthsAgo 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 F
/* find corresponding record in SCD history in last 6 months, based on matching fact */
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 */
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 ;

Pertimbangan:

• Menggunakan tabel temporal versi sistem untuk SCD dapat diterima jika periode validitas yang dihitung berdasarkan waktu transaksi database baik-baik saja dengan logika bisnis Anda. Jika Anda memuat data dengan penundaan yang signifikan, waktu transaksi mungkin tidak dapat diterima.
• Secara default, tabel temporal versi sistem tidak mengizinkan perubahan data historis setelah memuat (Anda dapat mengubah riwayat setelah Anda mengatur SYSTEM_VERSIONING ke NONAKTIF). Ini mungkin batasan dalam kasus di mana perubahan data historis terjadi secara teratur.
• Tabel versi sistem temporal menghasilkan versi baris pada perubahan kolom apa pun. Jika Anda ingin menekan versi baru pada perubahan kolom tertentu, Anda perlu menggabungkan batasan tersebut dalam logika ETL.
• Jika Anda mengharapkan sejumlah besar baris historis dalam tabel SCD, pertimbangkan untuk menggunakan indeks penyimpan kolom berkluster sebagai opsi penyimpanan utama untuk tabel riwayat. Itu akan mengurangi jejak tabel riwayat dan mempercepat kueri analitik Anda.

Memperbaiki kerusakan data tingkat baris

Anda dapat mengandalkan data historis dalam tabel temporal versi sistem untuk memperbaiki baris individual dengan cepat ke salah satu status yang ditangkap sebelumnya. Properti tabel temporal ini berguna ketika Anda dapat menemukan baris yang terpengaruh dan/atau ketika Anda mengetahui waktu perubahan data yang tidak diinginkan sehingga Anda dapat melakukan perbaikan secara efisien tanpa berurusan dengan cadangan.

Pendekatan ini memiliki beberapa keuntungan:

• Anda dapat mengontrol cakupan perbaikan dengan tepat. Catatan yang tidak terpengaruh perlu tetap pada status terbaru, yang sering menjadi persyaratan penting.
• Operasi efisien dan database tetap online untuk semua beban kerja menggunakan data.
• Operasi perbaikan itu sendiri berversi. Anda memiliki jejak audit untuk operasi perbaikan itu sendiri, sehingga Anda dapat menganalisis apa yang terjadi nanti jika perlu.

Tindakan perbaikan dapat diotomatisasi dengan relatif mudah. Contoh kode berikutnya memperlihatkan prosedur tersimpan yang melakukan perbaikan data untuk tabel Employee yang digunakan dalam skenario audit data.

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 E JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
    WHERE E.EmployeeID = @EmployeeID

Prosedur tersimpan ini mengambil @EmployeeID dan @versionNumber sebagai parameter input. Prosedur ini secara default memulihkan status baris ke versi terakhir dari riwayat (@versionNumber = 1).

Gambar berikut ini memperlihatkan status baris sebelum dan sesudah pemanggilan prosedur. Persegi panjang merah menandai versi baris saat ini yang salah, sementara persegi panjang hijau menandai versi yang benar dari riwayat.

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1

Prosedur tersimpan perbaikan ini dapat didefinisikan untuk menerima tanda waktu yang tepat alih-alih versi baris. Ini akan memulihkan baris ke versi apa pun yang aktif untuk titik waktu yang disediakan (yaitu, AS OF point in time).

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 JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History ON E.EmployeeID = History.EmployeeID
    WHERE E.EmployeeID = @EmployeeID

Untuk sampel data yang sama, gambar berikut mengilustrasikan skenario perbaikan dengan kondisi waktu. Disorot adalah @asOf parameter, baris yang dipilih dalam riwayat yang sebenarnya pada titik waktu yang disediakan, dan versi baris baru dalam tabel saat ini setelah operasi perbaikan:

Koreksi data dapat menjadi bagian dari pemuatan data otomatis dalam pergudangan data dan sistem pelaporan. Jika nilai yang baru diperbarui tidak benar, maka dalam banyak skenario, memulihkan versi sebelumnya dari riwayat adalah mitigasi yang cukup baik. Diagram berikut menunjukkan bagaimana proses ini dapat diotomatisasi:

Langkah berikutnya

• Tabel Temporal
• Memulai Tabel Temporal System-Versioned
• Pemeriksaan Konsistensi Sistem Tabel Temporal
• Partisi dengan Tabel Temporal
• Pertimbangan dan Batasan Tabel Temporal
• Keamanan Tabel Temporal
• Tabel Temporal Versi Sistem dengan Tabel Memory-Optimized
• Tampilan dan Fungsi Metadata Tabel Temporal