zaimplementuj powoli zmieniający się Wymiar typu 2 W Sql Server Integration Services – Część 1

Problem

chcielibyśmy przechowywać historię w naszej hurtowni danych dla kilku wymiarów.Używamy SQL Server Integration Services (SSIS) do implementacji ETL (ExtractTransform and Load). Próbowaliśmy wbudowanego powoli zmieniającego się Kreatora wymiarów, ale wydajność wydaje się słaba. Jak zaimplementować pożądaną funkcjonalność za pomocą zwykłych komponentów SSIS?

rozwiązanie

Wprowadzenie do powoli zmieniających się wymiarów

powoli zmieniający się wymiar (SCD) śledzi historię swoich poszczególnych elementów., Istnieje kilka metod zaproponowanych przez Ralpha Kimballa w jego książce The DatawarehouseToolkit:

• Type 1 – Zastąp pola, gdy zmieni się wartość. Nie ma historii.
• Typ 2-Utwórz nową linię z nowymi wartościami dla pól. Extracolumny wskazują, kiedy w czasie wiersz był ważny.
• Typ 3-zachowuje starą wartość kolumny w oddzielnej kolumnie.
• jest więcej typów SCD, ale są one w większości hybrydową kombinacją powyższych.

w tej poradzie skupimy się na sytuacji typu 2. Zilustrujmy na przykładzie., Mamy prostą tabelę przechowującą dane klientów.

Kolumna SK_Customer jest kolumną z identyfikatorem, która wygeneruje nową wartość dla każdego wiersza. Chcielibyśmy zachować historię dla atrybutu lokalizacji. Gdy lokalizacja zmienia się z Antwerpii na Brukselę, nie aktualizujemy wiersza, ale wstawiamy nowy rekord:

używając pól ValidFrom i ValidTo, określamy, kiedy rekord był ważny w czasie. Generowany jest nowy klucz zastępczy, ale klucz biznesowy-Nazwa klienta-pozostaje taki sam., Po załadowaniu tabeli faktów zostanie przeprowadzone wyszukiwanie na stole klienta. W zależności od znacznika czasu rekordu fakt, zostanie zwrócony jeden z dwóch wierszy. Na przykład:

wszystkie fakty dotyczą tego samego klienta. Kiedy zapytasz całkowitą kwotę sprzedaży dla Klienta, wynik wynosi 31. Łączna sprzedaż na lokalizację wynosi 12,75 dla Antwerpii i 18,25 dla Brukseli, mimo że dane dotyczą tego samego klienta. Korzystając z SCDType 2, możemy analizować nasze dane z historycznymi atrybutami.,

metody implementacji

istnieje kilka metod ładowania powoli zmieniającego się wymiaru Ina data warehouse. Możesz zdecydować się na czyste podejście T-SQL, albo za pomocą instrukcji multipleT-SQL lub za pomocą instrukcji MERGE. To ostatnie zostało wyjaśnione w instrukcji SQL Server MERGE do przetwarzania typu 2.

dzięki SSIS możesz użyć wbudowanego kreatora powoli zmieniających się wymiarów, który może obsługiwać wiele scenariuszy., Ten kreator jest opisany w wskazówkach ładowanie danych historycznych do hurtowni danych SQL Server i powolne zmienianie wymiarów w usługach integracji SQL Server. Wadą tego kreatora jest wydajność: używa on polecenia OLE DB dla everyupdate, co może skutkować niską wydajnością dla większych zestawów danych. Jeśli wprowadzisz zmiany w przepływie danych, aby rozwiązać te problemy, nie możesz ponownie uruchomić Kreatora, ponieważ stracisz wszystkie zmiany.

ostatnią opcją – poza użyciem komponentów 3rdparty – jest samodzielne zbudowanie logiki SCD typu 2 w przepływie danych,co opiszemy w następnej sekcji.,

implementacja w SSIS

rozwiązanie zaproponowane w tej poradzie działa dla każdej wersji SSIS. Podamy przykład wymiaru klienta, ale dodano dodatkowe pole: atrybut email. Nie przechowujemy historii adresów e-mail, więc anynew wartość nadpisze wszystkie inne wartości.

najpierw odczytujemy dane ze źródła, najprawdopodobniej ze środowiska stagingowego. Używając źródła relacyjnego, możesz użyć komponentu źródłowego OLE DB z zapytaniem SQL do odczytu danych. Wybierz tylko kolumny, których potrzebujesz dla swojego wymiaru. Pole validfrom jest również obliczane tutaj., Ponieważ typowo ładujesz dane z poprzedniego dnia, ValidFrom jest ustawiony na datę wczorajszą. Jeśli nie masz źródła relacyjnego, możesz dodać tę kolumnę za pomocą pochodnej Columntransformation.

w następnym kroku wykonujemy wyszukiwanie wymiarów.Tutaj Sprawdzimy, czy przychodzące wiersze są albo wstawką, albo aktualizacją.Jeśli nie znaleziono dopasowania, wiersz jest nowym elementem wymiaru i musi zostać wstawiony.Jeśli dopasowanie zostanie znalezione, element wymiaru już istnieje i będziemy musieli sprawdzić zmiany SCD typu 2., Jeśli nie masz bardzo dużego wymiaru, możesz użyć pełnego bufora:

Skonfiguruj transformację lookup, aby wysyłać niepasujące wiersze na wyjście no match. W SSIS 2005 ta opcja jeszcze nie istnieje, więc możesz użyć wyjścia błędu lub ustawić transformację tak, aby ignorowała awarie i usuwała wstawki i aktualizacje za pomocą warunkowego podziału.

w okienku połączenia, następujące zapytanie SQL pobiera surrogatekey, klucz biznesowy (CustomerName) i kolumny SCD Type 2., Dla każdego elementu pobierany jest tylko najnowszy wiersz,filtrując w polu ValidTo.

pole lokalizacji zmienia nazwę na Location_OLD dla jasności. W okienku columns dopasuj klucz biznesowy i wybierz wszystkie pozostałe kolumny.

teraz możemy dodać miejsce docelowe OLE DB na kanwie. To miejsce docelowe zapisze wszystkie nowe wiersze do wymiaru. Połącz wyjście Lookup No Match transformacji lookup z miejscem docelowym.,

w panelu mapowanie mapuje kolumny przepływu danych kolumnami tabeli wymiarów.

kolumna SK_Customer nie jest mapowana, ponieważ jest kolumną tożsamości, a jej wartości są generowane przez silnik bazy danych. ValidTo columnis również zostawił puste. Nowe wiersze nie mają wartości dla tej kolumny.

w części 2 tej porady będziemy kontynuować konfigurację przepływu danych,gdzie Sprawdzimy, czy wiersz jest aktualizacją typu 2, czy nie.,

Następne kroki

• Jeśli chcesz dowiedzieć się więcej o implementacji powoli zmieniających się wymiarów insis, możesz zapoznać się z następującymi wskazówkami:
  • używanie instrukcji SQL Server MERGE do przetwarzania typu 2 Powoli zmieniających się wymiarów
  • ładowanie danych historycznych do magazynu danych SQL Server
  • Obsługa powoli zmieniających się wymiarów w usługach integracji SQL Server
• więcej wskazówek dotyczących rozwoju SSIS znajdziesz w tym przeglądzie.,

Ostatnia aktualizacja: 2018-08-09