Jeżeli ktoś ma do czynienia z hurtowniami danych, prędzej czy później nadzieje się na termin SCD, czyli Slowly Changing Dimension.
SCD to wymiar, który zmienia się rzadko, ale jednak się zmienia.
Przykład: stopy podatkowe. Możemy mieć wymiar zawierający, dajmy na to, procentowe wartości podatku VAT na różne rzeczy (produkty, usługi itd). Podatek ten zasadniczo nie zmienia się częściej niż raz do roku, należy jednak jego zmianę przewidzieć w systemie, a także przechowywać wartości historyczne wraz z informacją, w jakich latach obowiązywały.
SCD można zaimplementować na kilka sposobów. Mamy SCD typu 1, 2, 3, 4 oraz kilka hybryd (zainteresowanych odsyłam do Wikipedii: https://en.wikipedia.org/wiki/Slowly_changing_dimension). Moim ulubionym SCD jest typ 2. Dodajemy do naszego wymiaru dwie kolumny: data_od oraz data_do, a także ustalamy specjalną datę 31 grudnia 9999 roku, oznaczającą bieżącą wersję rekordu.
Żeby nie kombinować z podatkami, pokażę dziś jak obsłużyć takie SCD na przykładzie kolorów.
Załóżmy, że każdy człowiek ma jakiś swój ulubiony kolor. Czasem zdarza się, że kolor ten się zmienia, i to będzie podstawą do utworzenia naszego SCD.
Będziemy przechowywać następujące informacje: kto, kolor, od kiedy i do kiedy.
Jeżeli komuś się zmieni ulubiony kolor, chcemy tę informację zaktualizować, jednocześnie zachowując dane o poprzednim kolorze oraz odpowiedni zakres dat.
Zaczniemy od utworzenia tabelki z ulubionymi kolorami:
SELECT *
INTO #t1
FROM
(
VALUES
(N'Jan Kowalski', N'Żółty', '10-Jan-2017'),
(N'Adam Malinowski', N'Niebieski', '20-Feb-2017'),
(N'Zofia Kszyk', N'Biały', '30-Mar-2017')
) AS x (kto, kolor, kiedy)
SELECT *
FROM #t1
Widzimy, że Jan Kowalski lubi kolor żółty, Adam Malinowski - niebieski, a Zofia Kszyk - biały.
Utworzymy teraz wymiar, który te informacje przechowuje:
CREATE TABLE #t2
(
kto NVARCHAR(100),
kolor NVARCHAR(100),
od_kiedy DATE,
do_kiedy DATE
)
INSERT INTO #t2
SELECT t.kto,
t.kolor,
t.kiedy,
'31-Dec-9999'
FROM #t1 AS t
SELECT *
FROM #t2
To w zasadzie ta sama informacja, co poprzednio, uzupełniona o datę końcową. Ponieważ nie było jeszcze żadnych zmian w ulubionych kolorach, każdy człowiek widnieje tu tylko raz.
Załóżmy teraz, że panu Kowalskiemu się odmieniło i teraz zamiast żółtego wielbi on kolor brązowy. Aktualizujemy jego rekord w pierwszej tabeli:
UPDATE #t1
SET kolor = N'Brązowy',
kiedy = '15 Jan 2017'
WHERE kto = N'Jan Kowalski'
Ponadto pojawił się nowy sympatyk barw, pan Jan Ul, który uwielbia błękit:
INSERT #t1
VALUES
(N'Jan Ul', 'Błękitny', '14-Apr-2017')
Teraz nasza tabela źródłowa wygląda tak:
SELECT *
FROM #t1
No i teraz samo gęste, czyli aktualizujemy wymiar #t2 w taki sposób, żeby uwzględnić zarówno nowego członka grupy koloromanów, jak też uzupełnić informację o nowej pasji Kowalskiego:
INSERT #t2
SELECT kto,
kolor,
od_kiedy,
do_kiedy
FROM
(
MERGE #t2 AS tgt
USING #t1 AS src
ON src.kto = tgt.kto and tgt.do_kiedy = '31-Dec-9999'
WHEN MATCHED AND (src.kolor <> tgt.kolor) THEN
UPDATE SET tgt.do_kiedy = DATEADD(DAY, -1, src.kiedy)
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (src.kto, src.kolor, src.kiedy, '31-Dec-9999')
OUTPUT $action akcja,
src.kto,
src.kolor,
src.kiedy od_kiedy,
'31-Dec-9999' do_kiedy
) m
WHERE m.akcja = 'UPDATE'
SELECT *
FROM #t2
ORDER BY 1, 3
Tu wydarza się mnóstwo interesujących rzeczy:
Operator MERGE to połączenie maksymalnie trzech operacji (INSERT, DELETE, UPDATE) w jednej transakcji. W tym przypadku tylko UPDATE i INSERT (niczego nie kasujemy).
Blok WHEN MATCHED mówi co ma się wydarzyć, jeżeli znajdziemy dopasowanie. Tu - w przypadku zmiany koloru - aktualizujemy datę końcową (ustawiamy ją na 1 dzień przed datą początkową kolejnej wersji tego rekordu).
Blok WHEN NOT MATCHED BY TARGET mówi co ma się wydarzyć, jeżeli w tabeli źródłowej pojawią się rekordy nieistniejące w tabeli docelowej.
Blok OUTPUT zwraca rekordy zmodyfikowane przez operator MERGE, wraz z informacją o rodzaju modyfikacji (dostępna jest tu kolumna specjalna $action, która może przyjąć wartość: 'INSERT', 'UPDATE' lub 'DELETE')
Wreszcie, ponieważ używamy OUTPUT, możemy zamknąć cały blok operatora MERGE w nawiasach i potraktować go jako podzapytanie do operatora INSERT, który dopisze do tabeli #t2 nowe wersje zmodyfikowanych rekordów.
Wynik:
SELECT *
FROM #t2
ORDER BY 1, 3
Jak widać wszystko działa elegancko, może poza kolorem pana Ula (brakuje polskich znaczków, dlaczego?)
Na zakończenie polecam jeszcze dopisać na samym początku skryptu:
IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
GO
Cały skrypt wyglądać więc będzie o, tak:
IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
GO
SELECT *
INTO #t1
FROM
(
VALUES
(N'Jan Kowalski', N'Żółty', '10-Jan-2017'),
(N'Adam Malinowski', N'Niebieski', '20-Feb-2017'),
(N'Zofia Kszyk', N'Biały', '30-Mar-2017')
) AS x (kto, kolor, kiedy)
SELECT *
FROM #t1
CREATE TABLE #t2
(
kto NVARCHAR(100),
kolor NVARCHAR(100),
od_kiedy DATE,
do_kiedy DATE
)
INSERT INTO #t2
SELECT t.kto,
t.kolor,
t.kiedy,
'31-Dec-9999'
FROM #t1 AS t
SELECT *
FROM #t2
UPDATE #t1
SET kolor = N'Brązowy',
kiedy = '15 Jan 2017'
WHERE kto = N'Jan Kowalski'
INSERT #t1
VALUES
(N'Jan Ul', 'Błękitny', '14-Apr-2017')
SELECT *
FROM #t1
INSERT #t2
SELECT kto,
kolor,
od_kiedy,
do_kiedy
FROM
(
MERGE #t2 AS tgt
USING #t1 AS src
ON src.kto = tgt.kto and tgt.do_kiedy = '31-Dec-9999'
WHEN MATCHED AND (src.kolor <> tgt.kolor) THEN
UPDATE SET tgt.do_kiedy = DATEADD(DAY, -1, src.kiedy)
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES
(src.kto, src.kolor, src.kiedy, '31-Dec-9999')
OUTPUT $action akcja,
src.kto,
src.kolor,
src.kiedy od_kiedy,
'31-Dec-9999' do_kiedy
) m
WHERE m.akcja = 'UPDATE'
SELECT *
FROM #t2
ORDER BY 1, 3
Smacznego!
Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]
Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.