Bagikan melalui

Skenario penggunaan tabel temporal

  • Artikel

Berlaku untuk: SQL Server

Tabel temporal versi sistem berguna dalam skenario yang memerlukan riwayat pelacakan perubahan data. Kami menyarankan agar Anda mempertimbangkan tabel temporal dalam kasus penggunaan berikut, untuk manfaat produktivitas utama.

Audit data

Anda dapat menggunakan penerapan versi sistem temporal pada tabel yang menyimpan informasi penting, untuk melacak apa yang berubah dan kapan, dan untuk melakukan forensik data kapan saja.

Tabel 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 Employee tabel 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 baris yang Anda pilih dengan berbagai jenis kueri pada tabel temporal, dengan atau tanpa SYSTEM_TIME klausa.

Diagram memperlihatkan skenario Penggunaan Temporal pertama.

Mengaktifkan penerapan versi sistem pada tabel baru untuk audit data

Jika Anda mengidentifikasi informasi yang memerlukan audit data, buat tabel database sebagai tabel temporal versi sistem. Contoh berikut mengilustrasikan skenario dengan tabel yang disebut Employee 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 versi sistem.

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 HIDDEN, seperti yang dijelaskan dalam Membuat tabel temporal versi sistem.

Contoh berikut mengilustrasikan mengaktifkan penerapan versi sistem pada tabel yang ada Employee dalam database SDM hipotetis. Ini memungkinkan penerapan versi sistem dalam Employee tabel dalam dua langkah. Pertama, kolom periode baru ditambahkan sebagai HIDDEN. Kemudian, membuat tabel riwayat default.

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 tipe data datetime2 harus sama dalam tabel sumber seperti dalam tabel riwayat versi sistem.

Setelah Anda menjalankan skrip sebelumnya, semua perubahan data akan dikumpulkan secara transparan dalam tabel riwayat. Dalam skenario audit data umum, Anda akan meminta semua perubahan data yang diterapkan ke baris individual dalam periode waktu yang diinginkan. Tabel riwayat default dibuat dengan B-tree penyimpanan baris berkluster, untuk mengatasi kasus penggunaan ini secara efisien.

Catatan

Dokumentasi menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Melakukan analisis data

Setelah Anda mengaktifkan penerapan versi sistem menggunakan salah satu pendekatan sebelumnya, audit data hanyalah satu kueri. Kueri berikut mencari versi baris untuk rekaman dalam Employee tabel, 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 titik (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 pada titik waktu mana pun 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 dan menampilkan hasil. Sampel kode berikut menunjukkan cara menerapkan kondisi pemfilteran, yang ditentukan di zona waktu lokal lalu dikonversi ke UTC menggunakan AT TIME ZONE, yang diperkenalkan di 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;

Menggunakan AT TIME ZONE sangat membantu dalam semua skenario lain di mana tabel versi sistem digunakan.

Kondisi pemfilteran yang ditentukan dalam klausul temporal dengan FOR SYSTEM_TIME mampu SARG. SARG adalah singkatan dari argumen pencarian, dan SARG-able berarti bahwa SQL Server dapat menggunakan indeks berkluster yang mendasar untuk melakukan pencarian alih-alih operasi pemindaian. Untuk informasi selengkapnya, lihat Arsitektur dan Panduan 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 melakukan pemindaian tabel atau indeks, yang bisa sangat mahal. Hindari jenis kondisi ini dalam kueri Anda:

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

Untuk informasi selengkapnya, lihat Mengkueri data dalam tabel temporal versi sistem.

Analisis titik waktu (perjalanan waktu)

Alih-alih berfokus pada perubahan pada rekaman individual, skenario perjalanan waktu menunjukkan 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 poin 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 dibuat secara otomatis.

OLTP dengan riwayat data yang dibuat secara otomatis

Dalam sistem pemrosesan transaksi, Anda dapat menganalisis bagaimana metrik penting 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 menyimpan riwayat lengkap perubahan secara transparan 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 yang dioptimalkan memori, 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 pemadatan 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 inventaris:

Diagram memperlihatkan model data yang disederhanakan yang digunakan untuk manajemen inventaris.

Contoh kode berikut dibuat 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 yang Dioptimalkan Memori 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 sebelumnya, ini adalah bagaimana prosedur untuk mempertahankan inventori dapat terlihat:

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
            SET @quantityIncrement = 0

        INSERT INTO [dbo].[ProductInventory] (
            [ProductId], [LocationID], [Quantity]
        )
        VALUES (@productId, @locationId, @quantityIncrement)
    END
END;

Prosedur spUpdateInventory tersimpan memasukkan produk baru dalam inventarisi atau memperbarui kuantitas produk untuk lokasi tertentu. Logika bisnis sederhana dan berfokus pada mempertahankan status terbaru yang akurat sepanjang waktu dengan menambah/mengurangi Quantity bidang melalui pembaruan tabel, sementara tabel versi sistem secara transparan menambahkan dimensi riwayat ke data, seperti yang digambarkan pada diagram berikut.

Diagram memperlihatkan penggunaan temporal dengan penggunaan Dalam Memori saat ini dan penggunaan historis di penyimpan kolom berkluster.

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 FOR SYSTEM_TIME ALL waktu menjadi mudah dengan klausul, 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 berikut ini memperlihatkan riwayat data untuk satu produk yang dapat dengan mudah dirender mengimpor tampilan sebelumnya di Power Query, Power BI, atau alat kecerdasan bisnis serupa:

Diagram memperlihatkan riwayat data untuk satu produk.

Tabel temporal dapat digunakan dalam skenario ini untuk melakukan jenis analisis perjalanan waktu lainnya, seperti merekonstruksi status inventaris AS OF kapan saja di masa lalu atau membandingkan rekam jepret yang termasuk dalam momen waktu yang berbeda.

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

Cuplikan layar berikut menunjukkan rencana eksekusi yang dihasilkan untuk SELECT kueri. Ini menggambarkan bahwa Mesin Database menangani semua kompleksitas saat berhadapan dengan hubungan temporal:

Diagram memperlihatkan rencana eksekusi yang dihasilkan untuk kueri 'SELECT' yang menggambarkan bahwa Mesin Database SQL Server menangani semua kompleksitas saat berhadapan dengan hubungan temporal.

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
INNER 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:

Diagram memperlihatkan 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 perlahan (SCD).

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

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

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

Tabel temporal versi sistem dapat digunakan untuk menurunkan kompleksitas kode Anda secara dramatis 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 yang ada berisi data historis, buat tabel terpisah dan pindahkan data historis ke sana dan simpan versi dimensi (aktual) saat ini dalam tabel dimensi asli Anda. Kemudian gunakan ALTER TABLE sintaks untuk mengonversi tabel dimensi Anda menjadi 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 dua SCD (DimLocation dan DimProduct) dan satu tabel fakta.

Diagram memperlihatkan bagaimana Anda dapat menggunakan tabel temporal dalam skenario sederhana yang melibatkan 2 SCD (DimLocation dan DimProduct) dan satu tabel fakta.

Untuk menggunakan SCD sebelumnya 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 atribut 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 */
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;

Pertimbangan

Menggunakan tabel temporal versi sistem untuk SCD dapat diterima jika periode validitas yang dihitung berdasarkan waktu transaksi database tidak masalah 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 OFF). Ini mungkin batasan dalam kasus di mana perubahan data historis terjadi secara teratur.

Tabel versi sistem temporal menghasilkan versi baris pada setiap perubahan kolom. 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. Menggunakan indeks penyimpan kolom 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 saat Anda mengetahui waktu perubahan data yang tidak diinginkan. Pengetahuan ini memungkinkan Anda melakukan perbaikan secara efisien tanpa berurusan dengan cadangan.

Pendekatan ini memiliki beberapa keuntungan:

  • Anda dapat mengontrol cakupan perbaikan dengan tepat. Rekaman 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 diberi versi. 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
INNER 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 menunjukkan 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.

Cuplikan layar memperlihatkan status baris sebelum dan sesudah pemanggilan prosedur. 

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

Cuplikan layar memperlihatkan baris yang dikoreksi.

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

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;

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

Cuplikan layar memperlihatkan skenario perbaikan dengan kondisi waktu.

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:

Diagram memperlihatkan bagaimana proses dapat diotomatisasi.

Konten terkait