Wolno zmieniając typ wymiaru 2
Napiwek
Możesz pobrać plik szablonu dodatku Power Query, który zawiera pełne rozwiązanie wzorca Wolno zmieniającego typ wymiaru 2, aby wykonać czynności opisane w tym samouczku. Aby dowiedzieć się więcej na temat używania pliku szablonu dodatku Power Query, zapoznaj się z artykułem dokumentacji szablonów dodatku Power Query.
Powolne zmienianie typu wymiaru 2 to metoda używana w magazynowaniu danych do zarządzania i śledzenia historycznych zmian w danych wymiarów. Po zmianie wartości atrybutu zostanie utworzony nowy rekord o unikatowym identyfikatorze, a stary rekord zostanie zachowany. Pozwala to na pełny historyczny rekord zmian w czasie, umożliwiając dokładne raportowanie i analizę na podstawie różnych punktów w czasie.
W tym artykule przedstawiono samouczek i przykład sposobu implementacji rozwiązania dla wolno zmieniającej się koncepcji typu wymiaru 2 przy użyciu przepływu danych Gen2 w usłudze Data Factory dla usługi Microsoft Fabric.
Architektura rozwiązania
Podczas implementowania rozwiązania dla wolno zmieniającego się typu wymiaru 2 należy zdefiniować tabelę źródłową i pola z tabeli źródłowej, aby zidentyfikować nowe rekordy.
W całości architektura wymaga co najmniej czterech składników:
- Tabela źródłowa: tabela operacyjna, w której można zmienić wartości zgodnie z potrzebami
- Tabela wymiarów: tabela historyczna wszystkich stanów, które zawiera tabela Źródło. Zdefiniowana przez logikę niestandardową w celu identyfikowania zmian i dat wejścia w życie tych zmian
- Logika identyfikowania zmian: zasadniczo wykonywana przez migawkę bieżącego stanu tabeli Źródłowej i porównująca ją z dostępnymi rekordami w tabeli Wymiar.
- Logika aktualizowania tabeli wymiarów: po zidentyfikowaniu wszystkich zmian w logice w celu zidentyfikowania zmian można użyć tabeli z rekordami do dodania i zaktualizowania w celu zaktualizowania tabeli Wymiar.
Uwaga
Rozwiązanie korzysta z usługi Dataflow Gen2 w usłudze Microsoft Data Factory. Chociaż logika może zostać zmieniona i zmodyfikowana zgodnie z konkretnymi potrzebami, celem samouczka jest przedstawienie prostego sposobu osiągnięcia wolno zmieniającego się wzorca typu wymiaru 2 przy użyciu niskiego kodu i rozwiązania wizualnego, takiego jak Dataflow Gen2.
Tabela źródłowa
Samouczek rozpoczyna się od przykładowej tabeli źródłowej dla pracowników, która zawiera cztery kolumny:
| Identyfikator repsourceID | FirstName | LastName | Region (Region) |
|---|---|---|---|
| 312 | Vance | Deleon | Southwest |
| 331 | Adrian | King | Płn. Zach. |
Dane w tabeli powinny ulec zmianie. Osoby mogą mieć zmiany w ich nazwiskach lub regionie, w którym mogą być przypisane do pracy.
Tabela wymiarów
Tabela wymiarów samouczka analizuje zmiany, które mogą wystąpić w polach FirstName, LastName i Region. Tabela wymiarów i jej dane:
| SalesRepID | Identyfikator repsourceID | FirstName | LastName | Region (Region) | StartDate | EndDate | IsCurrent | Skrót |
|---|---|---|---|---|---|---|---|---|
| 1 | 312 | Vance | Deleon | Southwest | 3/20/2021 | 12/31/9999 | PRAWDA | 3331327c56616e63657c44654c656f6e7c536f75746877657374 |
| 2 | 331 | Adrian | King | Południowo-środkowe | 3/20/2021 | 12/31/9999 | PRAWDA | 3333317c41647269616e7c4b696e677c536f75746863656e7472616c |
| 3 | 334 | Devon | Torres | Panama | 2/14/2024 | 12/31/9999 | PRAWDA | 3333347c446576f6e7c546f727265737c50616e616d61 |
Jest to definicja schematu dla tej tabeli i opis pól:
| Nazwa pola | Typ danych | opis |
|---|---|---|
| SalesRepID | Liczba | Klucz zastępczy używany do unikatowego identyfikowania rekordów w całej tabeli Wymiar |
| Identyfikator repsourceID | Liczba | Klucz naturalny z tabeli źródłowej, który reprezentuje identyfikator pracownika |
| FirstName | Text | Imię pracownika. To pole pochodzi z tabeli Source |
| LastName | Text | Nazwisko pracownika. To pole pochodzi z tabeli Source |
| Region (Region) | Text | Region, w którym pracuje pracownik. To pole pochodzi z tabeli Source |
| StartDate | Data | Sygnatura daty, która określa, kiedy rekord staje się skuteczny |
| EndDate | Data | Sygnatura daty określana do momentu wejścia rekordu w życie |
| IsCurrent | Wartość logiczna | Prosta flaga, aby określić, czy rekord jest bieżący, czy nie. Wartość true oznacza, że rekord jest bieżący |
| Skrót | Text | Kodowanie skrótu pól RepSourceID, FirstName, LastName i Region połączone |
Uwaga
Zdecydowanie zaleca się utworzenie tabeli wymiarów z poprawnym schematem przed ustanowieniem tego procesu. Samouczek uwzględnia, że tabela wymiarów została już utworzona z wyprzedzeniem i ustanowiła już mechanizm tworzenia skrótów lub wyszukiwania, który może być używany w ramach logiki przepływu danych Gen2.
Żądany wynik to zaktualizowana tabela wymiarów zawierająca aktualizacje identyfikatora SalesRepID dwa i trzy oraz nowy rekord cztery. Ta tabela wygląda następująco:
| SalesRepID | Identyfikator repsourceID | FirstName | LastName | Region (Region) | StartDate | EndDate | IsCurrent | Skrót |
|---|---|---|---|---|---|---|---|---|
| 1 | 312 | Vance | Deleon | Southwest | 3/20/2021 | 12/31/9999 | PRAWDA | 3331327c56616e63657c44654c656f6e7c536f75746877657374 |
| 2 | 331 | Adrian | King | Południowo-środkowe | 3/20/2021 | 8/16/2024 | FAŁSZ | 3333317c41647269616e7c4b696e677c536f75746863656e7472616c |
| 3 | 334 | Devon | Torres | Panama | 2/14/2024 | 8/16/2024 | FAŁSZ | 3333347c446576f6e7c546f727265737c50616e616d61 |
| 100 | 331 | Adrian | King | Płn. Zach. | 8/16/2024 | 12/31/9999 | PRAWDA | 3333317c41647269616e7c4b696e677c4e6f72746877657374 |
Logika do identyfikowania zmian
Aby zidentyfikować zmiany, należy najpierw utworzyć migawkę tabeli źródłowej i ustanowić logikę, aby porównać ją z rekordami z tabeli wymiarów. Istnieje wiele sposobów, na które można ustanowić logikę do porównania tych tabel. Niektóre z nich to:
- Scalanie/wzorce JOIN
- Używanie kluczy naturalnych
- Tworzenie pól odnośników przy użyciu technik tworzenia skrótów
- Jawne sprzężenia między tabelami
- Logika niestandardowa używająca dynamicznego dopasowywania rekordów z tabelą Table.SelectRows
W tym samouczku przedstawiono technikę tworzenia skrótów w celu użycia pojedynczej wartości, która może zostać utworzona w obu tabelach dla operacji JOIN, znanej również jako operacja scalania, w celu porównania rekordów z dwóch tabel.
Po załadowaniu tabeli Source do przepływu danych Gen2 możesz wybrać kartę Dodaj kolumnę na wstążce i użyć opcji Dodaj kolumnę niestandardową. W oknie dialogowym Kolumna niestandardowa możesz utworzyć nową kolumnę o nazwie Skrót z typem danych Tekst i przy użyciu formuły:
Binary.ToText( Text.ToBinary( Text.Combine(List.Transform({[RepSourceID],[FirstName],[LastName],[Region]}, each if _ = null then "" else Text.From(_)), "|")), BinaryEncoding.Hex)
Ważne
Chociaż ta przykładowa formuła pokazuje, jak używać tych czterech kolumn, możesz zmienić odwołanie do kolumn na własne kolumny i zdefiniować konkretne pola z tabeli, aby utworzyć skrót.
Teraz z kolumną Skrót w tabeli Source możesz teraz łatwo porównać obie tabele w celu znalezienia dokładnych dopasowań.
Po załadowaniu tabeli Wymiar utwórz odwołanie do tego zapytania, klikając prawym przyciskiem myszy zapytanie w okienku zapytania lub w widoku diagramu i wybierając opcję odwołania. Zmień nazwę tego nowego zapytania na AggregatedDimHash. Liczbę rekordów w tabeli można agregować według pola Skrót. W tym celu przejdź do karty Narzędzia główne na wstążce i wybierz opcję Grupuj według w grupie Przekształć. W oknie dialogowym upewnij się, że pogrupuj według kolumny Skrót i wybierz operację dla nowej kolumny Liczba, aby mieć wartość Liczba wierszy.
Nowe rekordy
Uwaga
Porównanie tabeli źródłowej z tabelą wymiarów zasadniczo daje nowe rekordy, które należy dodać do tabeli wymiarów.
Wybierz zapytanie źródłowe, przejdź do karty Narzędzia główne na wstążce i wybierz opcję Scal zapytania jako nowe w grupie Połącz. Zmień nazwę tego zapytania na Porównaj. W oknie dialogowym Scalanie upewnij się, że na liście rozwijanej "Tabela zagregowanadimHash " wybierz kolumny skrótu z obu tabel, pozostawiając domyślny rodzaj sprzężenia Lewe zewnętrzne.
Po zakończeniu scalania pamiętaj, aby rozwinąć nowo utworzoną kolumnę, wybierając tylko kolumnę Liczba do rozwinięcia.
Przefiltruj tę kolumnę, aby zachować wartości null, które reprezentują wartości, które nie istnieją obecnie w tabeli Wymiar. Wynik daje jeden rekord dla Adrian King w regionie Północno-Zachodnim.
Usuń kolumnę Count (Liczba) i zmień nazwę tego zapytania na CompareStoM.
Następny krok wymaga dodania brakujących pól do rekordu, takich jak StartDate, EndDate, IsCurrent, a nawet SalesRepID. Jednak chociaż pierwsze trzy są łatwe do zdefiniowania przy użyciu prostej formuły, identyfikator SalesRepID wymaga uprzedniego obliczenia tej wartości z istniejących wartości w tabeli Wymiar.
Pobieranie sekwencji identyfikatorów z tabeli Wymiar
Odwołaj się do istniejącej tabeli Wymiar i zmień nazwę zapytania na LastID. Odwołujesz się do tego zapytania w przyszłości.
Przy założeniu, że wartość zapytania jest liczbą całkowitą, która zwiększa się o jeden przy każdym dodaniu nowych rekordów, można zaimplementować logikę, która znajduje maksymalną wartość w identyfikatorze SalesRepID. Wybierz prawym przyciskiem pozycję SalesRepID i wybierz opcję przechodzenia do szczegółów.
Spowoduje to wyświetlenie listy i na wstążce będą dostępne opcje statystyk, w których można wybrać opcję obliczania maksymalnej wartości tej listy:
Dodaj kolejny krok niestandardowy po dodaniu poprzedniego kroku i zastąp formułę dla tego kroku zapytania poniższą formułą, która oblicza maksymalną wartość z identyfikatora SalesRepID i dodaj ją do niego lub ustal wartość jako inicjator dla nowych rekordów w przypadku, gdy tabela nie ma żadnych rekordów try #"Calculated maximum" +1 otherwise 1
Dane wyjściowe zapytania LastID dla tego przykładu to liczba czwarta.
Ważne
#"Calculated maximum" reprezentuje nazwę poprzedniego kroku. Jeśli nie jest to dokładna nazwa zapytania, zmodyfikuj odpowiednio formułę, aby odzwierciedlić nazwę poprzedniego kroku.
Odwołaj się do zapytania CompareStoM , w którym był pojedynczy rekord Adrian King w regionie Północno-Zachodnim, i wywołaj to nowe zapytanie "NewRecords". Dodaj nową kolumnę Indeks za pomocą karty Dodaj kolumnę na wstążce rozpoczynającej się od zera liczbowego.
Sprawdź formułę kroku, który został utworzony, i zastąp ciąg 0 nazwą ostatniego identyfikatora zapytania. Daje to wartość początkową reprezentującą nowe wartości rekordów w tabeli Wymiar.
Zmień nazwę tej kolumny indeksu na SalesRepID.
Dodawanie brakujących pól do nowych rekordów
Nadszedł czas, aby dodać brakujące kolumny przy użyciu kolumny Dodaj kolumnę niestandardową. Poniżej znajduje się tabela zawierająca wszystkie formuły do użycia dla każdej z nowych kolumn
| Nazwa kolumny | Typ danych | Formuła |
|---|---|---|
| StartDate | Data | Date.From(DateTime.LocalNow()) |
| EndDate | Data | #date(9999,12,31) |
| IsCurrent | Prawda/fałsz | prawda |
Wynik jest teraz zgodny ze schematem oczekiwanym przez tabelę Wymiar.
Rekordy do aktualizacji
Uwaga
Porównanie tabeli wymiarów z tabelą źródłową spowoduje zaktualizowanie rekordów w tabeli wymiarów.
Korzystając z oryginalnego zapytania wymiarowego (Wymiar), wykonaj nowe zapytania scalania jako nową operację i wybierz zapytanie tabeli źródłowej jako właściwą tabelę. Wybierz kolumny skrótu z obu tabel i wybierz pozycję Lewy anty jako rodzaj sprzężenia.
Dane wyjściowe to tabela z rekordami, które nie są już używane w tabeli Source. Pamiętaj, aby rozwinąć nowo utworzoną kolumnę z wartościami tabeli i rozwinąć tylko kolumnę Skrót, a następnie usunąć ją później. Zmień nazwę zapytania na RecordsToUpdate.
Teraz należy zaktualizować rekordy z tabeli Wymiar, aby odzwierciedlić tę zmianę w tabeli źródłowej. Zmiany są proste i będą wymagały zaktualizowania wartości w polach EndDate i IsCurrent. W tym celu możesz wybrać prawym przyciskiem pozycję IsCurrent i wybrać opcję Zamień wartości.... W oknie dialogowym Zamienianie wartości można zastąpić wartość TRUE wartością FALSE.
Możesz również wybrać pole EndDate i wybrać pozycję Zamień wartości. Wprowadź wartość 12/31/1999 lub dowolną wybraną datę, ponieważ zastąpisz tę wartość później.
Po zatwierdzeniu okna dialogowego zostanie dodany nowy krok zastępowania wartości. Przejdź do paska formuły kroku i zmień składnik, który ma #date(1999,12,31) przy użyciu formuły z poniższej.
Date.From(DateTime.LocalNow())
Ta nowa formuła dodaje sygnaturę daty tak, jak w przypadku uruchomienia logiki w celu określenia daty zakończenia dla tego konkretnego rekordu.
Wynikiem tego będzie tabela zawierająca dokładnie rekordy, które powinny zostać zaktualizowane przy użyciu odpowiadających im nowych wartości.
Łączenie rekordów w celu dodania i zaktualizowania do pojedynczej tabeli
Możesz dołączyć zapytanie dla rekordów NewRecords przy użyciu rekordów do zaktualizowania (RecordsToUpdate) do pojedynczego zapytania, aby uprościć następujący proces w celu zaktualizowania tabeli wymiarów.
Aby dołączyć zapytania, pamiętaj, aby wybrać zapytanie NewRecords , przejdź do karty głównej wstążki i wewnątrz grupy Połącz znajdziesz opcję Dołącz zapytania jako nowe. W oknie dialogowym Dołączanie upewnij się, że wybrano również zapytanie z rekordami, które mają być aktualizowane jako druga tabela.
Zmień nazwę tego nowego zapytania na StagingTableForUpdates i powinna zawierać 3 wiersze. To zapytanie jest używane w logice do aktualizowania tabeli wymiarów. Możesz przenieść identyfikator SalesRepID lub zmienić kolejność kolumn zgodnie z życzeniem. Dla uproszczenia i pokazu w tym samouczku przedstawiono dane wyjściowe tego zapytania przy użyciu tej samej kolejności pól, co w tabeli Wymiar .
Logika aktualizowania tabeli Wymiar
Rozwiązanie do tej pory udostępnia zapytanie ze wszystkimi rekordami operacji upsert do miejsca docelowego. Od tego momentu można zdefiniować logikę, której chcesz użyć do załadowania danych do tabeli Wymiar, ale zwykle masz dwa:
- Operacja upsert: Można to zrobić dzisiaj, przechowując wyniki zapytania StagingTableForUpdates do tabeli przejściowej w źródle danych, a następnie uruchamiając procedurę składowaną w a aparatu serwera/źródła danych. Możesz również użyć innych mechanizmów, takich jak notes w usłudze Microsoft Fabric. Na koniec możesz ustanowić potok danych, który może wyzwolić notes lub procedurę składowaną po zakończeniu działania przepływu danych Gen2 i zautomatyzować go dla przyszłych operacji i ustawić go zgodnie z harmonogramem.
- Usuń istniejące dane i utwórz ponownie tabelę: możesz to zrobić dzisiaj w usłudze Dataflow Gen2 bez konieczności używania innych narzędzi, ale potencjalnie można użyć innych narzędzi, a także zaimplementować tę logikę. Ten samouczek przedstawia to podejście.
Ładowanie danych do tabeli docelowej Wymiaru przy użyciu usługi Dataflow Gen2
Możesz utworzyć logikę, która używa trzech zapytań do utworzenia zapytania ze wszystkimi rekordami, które powinny istnieć w tabeli Wymiar. Nowe zapytanie umożliwia ładowanie danych za pomocą funkcji miejsc docelowych danych w przepływie danych Gen2.
Rekordy do zachowania z oryginalnej tabeli wymiarów
Pierwsza logika do zaimplementowania to rekordy z oryginalnej tabeli Wymiar do zachowania.
Po wybraniu zapytania Wymiar przejdź do karty Narzędzia główne na wstążce i użyj opcji Scal zapytania jako nowe . W oknie dialogowym Scalanie wybierz zapytanie RecordsToUpdate jako odpowiednią tabelę. Wybierz kolumny SalesRepID z obu kolumn i użyj lewego anty jako rodzaju sprzężenia. Wybierz OK
Pamiętaj, aby rozwinąć pole Skrót z nowo utworzonej kolumny. Po rozwinięciu możesz usunąć kolumnę.
Teraz, gdy już wiesz, jakie rekordy muszą być przechowywane z oryginalnej tabeli Wymiar, możesz dołączyć tabelę StagingTableForUpdates do istniejącego zapytania, aby mieć zapytanie, będzie zawierać wszystkie rekordy, które powinny znajdować się w tabeli Wymiar . W tym celu na karcie Narzędzia główne na wstążce wybierz opcję Dołącz w istniejącym zapytaniu i dołącz zapytanie StagingTableForUpdates .
Tę tabelę można sortować przy użyciu pola SalesRepID w kolejności rosnącej, a dane wyjściowe mogą być używane z funkcją docelową danych w celu załadowania danych do tabeli Wymiar.
Więcej informacji na temat ustawiania miejsca docelowego danych dla zapytania i ładowania danych wyjściowych zapytania do tabeli Dimension można dowiedzieć się z artykułu Dataflow Gen2 data destinations and managed settings (Ustawienia zarządzane i miejsca docelowe danych przepływu danych).
Uwaga
Należy wziąć pod uwagę, że w usłudze Dataflow Gen2 można użyć mechanizmu przejściowego na poziomie zapytania. Dowiedz się więcej o mechanizmie przejściowym w przepływie danych Gen2