Skenario penggunaan tabel temporal

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

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.

Gunakan tabel temporal untuk merencanakan skenario audit data pada tahap awal siklus pengembangan. Anda dapat menambahkan audit data ke aplikasi atau solusi yang sudah 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 melalui berbagai jenis kueri pada tabel temporal, dengan atau tanpa klausa SYSTEM_TIME.

Diagram memperlihatkan skenario Penggunaan Temporal pertama.

Aktifkan pembuatan 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 menggambarkan sebuah skenario dengan tabel yang disebut Employee dalam basis data HR 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 berversi sistem temporal dijelaskan dalam Buat 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 besar pada aplikasi Anda, tambahkan kolom periode sebagai HIDDEN, sebagaimana dijelaskan dalam Membuat tabel temporal berversi sistem.

Contoh berikut mengilustrasikan cara mengaktifkan pembuatan versi sistem pada tabel Employee yang sudah ada dalam basis data 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));

Important

Presisi dari tipe data datetime2 di tabel sumber harus sama seperti di tabel riwayat berversi sistem.

Setelah Anda menjalankan skrip sebelumnya, tabel riwayat secara transparan mengumpulkan semua perubahan data. Dalam skenario audit data yang umum, Anda melakukan query untuk semua perubahan data yang diterapkan pada baris individual dalam jangka waktu yang diminati. Tabel riwayat default dibuat dengan B-tree row-store terkluster untuk menangani skenario penggunaan ini secara efisien.

Note

Dokumentasi biasanya menggunakan istilah pohon B ketika merujuk pada 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 versioning sistem menggunakan salah satu pendekatan sebelumnya, audit data hanya memerlukan 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 aktif hanya dalam suatu periode (dan tidak di luar periode tersebut), 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 seperti apa tampilan seluruh tabel pada waktu sebelumnya:

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. Contoh kode berikut menunjukkan cara menerapkan kondisi penyaringan, yang ditentukan dalam zona waktu lokal dan kemudian dikonversi ke UTC dengan menggunakan AT TIME ZONE:

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, -9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
       [Name],
       Position,
       Department,
       [Address],
       [AnnualSalary],
       ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
       ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

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 adalah SARGable.

Note

Istilah SARGable dalam basis data relasional mengacu pada predikat Search ARGumentable yang dapat menggunakan indeks untuk mempercepat eksekusi kueri. Untuk informasi selengkapnya, lihat Panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Jika Anda membuat kueri langsung ke tabel riwayat, pastikan kondisi penyaringan Anda juga bersifat SARGable dengan menentukan penyaring dalam bentuk <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Jika Anda menerapkan AT TIME ZONE ke kolom periode, SQL Server akan melakukan pemindaian tabel atau indeks, yang dapat memakan banyak biaya. 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 pada titik waktu tertentu (perjalanan waktu)

Alih-alih berfokus pada perubahan pada catatan individual, skenario perjalanan waktu menunjukkan bagaimana seluruh dataset berubah seiring waktu. Kadang-kadang perjalanan waktu mencakup beberapa tabel temporal yang saling berkait, masing-masing berubah dengan kecepatan independen, yang ingin Anda analisis:

  • Tren indikator-indikator penting dalam data historis dan data saat ini
  • Salinan persis dari seluruh data pada titik waktu tertentu mana pun di masa lalu (kemarin, sebulan yang lalu, dll.)
  • Perbedaan antara dua titik waktu yang relevan (sebulan yang lalu vs. tiga bulan yang lalu, misalnya)

Banyak skenario dunia nyata memerlukan analisis perjalanan waktu. Untuk mengilustrasikan skenario penggunaan ini, mari kita lihat pemrosesan transaksi online (OLTP) dengan riwayat yang dihasilkan 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 tidak boleh mengorbankan kinerja aplikasi OLTP di mana akses ke status data terbaru harus dilakukan 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 transaksi tinggi di SQL Server dan Azure SQL Managed Instance, kami merekomendasikan Anda menggunakan tabel temporal versi Sistem dengan tabel yang dioptimalkan untuk memori, yang memungkinkan Anda menyimpan data terkini di memori dan riwayat perubahan lengkap di disk dengan cara yang hemat biaya.

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

  • Analisis tren umumnya mendapat manfaat dari kinerja kueri yang disediakan oleh indeks columnstore berkluster.

  • Tugas pengosongan data pada tabel yang dioptimalkan untuk memori memberikan kinerja terbaik pada beban kerja OLTP yang berat ketika tabel riwayat memiliki indeks columnstore 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 menyimpan seluruh dataset di memori dan memungkinkan Anda 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 untuk manajemen inventaris:

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

Contoh kode berikut membuat ProductInventory sebagai tabel temporal berversi sistem dalam memori, dengan indeks columnstore terklaster pada tabel riwayat (yang menggantikan indeks row-store yang dibuat secara bawaan):

Note

Pastikan database Anda memungkinkan pembuatan tabel yang dioptimalkan memori. Lihat Membuat Tabel yang Dioptimalkan untuk Memori dan Prosedur Tersimpan yang Dikompilasi Secara Native.

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
            SET (SYSTEM_VERSIONING = OFF);
    END

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

END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
    ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

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

        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 bisnisnya sederhana dan berfokus pada menjaga status terbaru tetap akurat sepanjang waktu dengan menambah / mengurangi Quantity field melalui pembaruan tabel, sementara tabel versi sistem secara transparan menambahkan dimensi riwayat ke data, seperti yang ditunjukkan pada diagram berikut.

Diagram yang menunjukkan penggunaan temporal dengan penggunaan In-Memory saat ini dan penggunaan historis dalam columnstore berkluster.

Sekarang, Anda dapat secara efisien melakukan query status terbaru dari modul yang dikompilasi secara native:

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

Menganalisis perubahan data dari waktu ke waktu menjadi mudah dengan klausul 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 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.

Anda dapat menggunakan tabel temporal dalam skenario ini untuk melakukan jenis analisis perjalanan waktu lainnya, seperti merekonstruksi keadaan inventaris AS OF pada titik waktu tertentu di masa lalu atau membandingkan snapshot yang berasal dari momen waktu yang berbeda.

Untuk skenario penggunaan ini, Anda juga dapat memperluas Product tabel dan Location menjadi tabel temporal untuk memungkinkan analisis riwayat perubahan dan UnitPriceNumberOfEmployeedi kemudian hari .

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 diterapkan FOR SYSTEM_TIME AS OF ke view tersebut, 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 inventaris produk antara dua titik waktu (sehari yang lalu dan sebulan yang lalu):

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;

Deteksi anomali

Deteksi anomali, atau deteksi outlier, mengidentifikasi item yang tidak sesuai dengan pola yang diharapkan atau item lain dalam dataset. Anda dapat menggunakan tabel temporal versi sistem untuk mendeteksi anomali yang terjadi secara berkala atau tidak beraturan, dengan menggunakan kueri temporal untuk dengan cepat menemukan pola tertentu. Apa yang dianggap sebagai anomali tergantung 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 pada hari-hari biasa jumlah produk yang dibeli memiliki variasi yang kecil, kueri berikut mengidentifikasi outlier tunggal: sampel yang perbedaannya signifikan (2x) dibandingkan dengan sampel tepat di sebelahnya, sementara sampel di sekitarnya tidak menunjukkan perbedaan yang 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;

Note

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 modifikasi dimensi terjadi jauh lebih jarang, secara tidak terduga, 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 perlahan berdasarkan bagaimana sejarah perubahan dijaga:

Jenis dimensi Rincian
Tipe 0 Riwayat tidak disimpan. 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.

Anda dapat menggunakan tabel temporal versi sistem untuk secara drastis menurunkan kompleksitas kode Anda, karena riwayat data secara otomatis terjaga. 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 dimensi baru atau mengubah yang sudah ada menjadi tabel temporal versi sistem. Jika tabel dimensi Anda yang sudah ada berisi data historis, buat tabel terpisah dan pindahkan data historis ke sana serta pertahankan 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 menunjukkan proses tersebut dan mengasumsikan bahwa tabel dimensi DimLocation telah memiliki ValidFrom dan ValidTo sebagai kolom datetime2 yang tidak boleh bernilai null, yang diisi oleh proses ETL:

  • Pindahkan versi baris tertutup ke dalam tabel riwayat baru:

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

  • Buat indeks columnstore terkluster, pilihan yang tepat untuk skenario gudang data:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Hapus versi sebelumnya dari DimLocation, yang menjadi tabel saat ini dalam konfigurasi temporal versi sistem:

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

  • Tambahkan definisi periode:

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

  • Aktifkan tabel versi sistem dan ikat riwayat ke DimLocation:

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

Anda tidak perlu kode tambahan untuk memelihara SCD selama proses pemuatan data warehouse setelah Anda membuatnya.

Ilustrasi berikut menunjukkan bagaimana Anda dapat menggunakan tabel temporal dalam skenario dasar yang melibatkan dua SCD (DimLocation dan DimProduct) serta 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 di dalam laporan, Anda perlu menyesuaikan proses kueri dengan tepat. 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 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

Menggunakan tabel temporal versi sistem untuk SCD dapat diterima jika periode validitas yang dihitung berdasarkan waktu transaksi basis data sesuai dengan logika bisnis Anda. Jika Anda memuat data dengan penundaan 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 temporal dengan versi sistem membuat versi baris baru setiap kali ada perubahan pada kolom apa pun. Jika Anda ingin menekan versi baru pada perubahan kolom tertentu, Anda perlu memasukkan batasan itu ke dalam logika ETL.

Jika Anda mengharapkan jumlah baris historis yang signifikan dalam tabel SCD, pertimbangkan menggunakan indeks clustered columnstore 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 memanfaatkan data historis dalam tabel temporal berversi sistem untuk dengan cepat memulihkan baris tertentu ke salah satu keadaan yang terekam 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 memiliki versi. Anda memiliki jejak audit untuk operasi perbaikan, sehingga Anda bisa menganalisis apa yang terjadi nanti jika diperlukan.

Anda dapat mengotomatisasi aksi perbaikan dengan relatif mudah. Contoh kode berikut menunjukkan 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 using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

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

Gambar berikut menunjukkan keadaan 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. 

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

Cuplikan layar memperlihatkan baris yang dikoreksi.

Prosedur tersimpan untuk perbaikan ini dapat didefinisikan agar menerima stempel waktu yang persis, alih-alih versi baris. Ini memulihkan baris ke versi mana pun yang aktif pada titik waktu yang ditentukan (yaitu, AS OF titik waktu tersebut).

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 menunjukkan skenario perbaikan berdasarkan kondisi waktu. Yang disorot adalah @asOf parameter, baris yang dipilih dalam riwayat yang aktual pada titik waktu yang diberikan, dan versi baris baru dalam tabel saat ini setelah operasi perbaikan:

Cuplikan layar yang menunjukkan 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

  • Last updated on