Modelowanie wymiarowe w usłudze Microsoft Fabric Warehouse: ładowanie tabel

Dotyczy:✅ punkt końcowy usługi analitycznej SQL i magazyn w usłudze Microsoft Fabric

Ten artykuł zawiera wskazówki i najlepsze rozwiązania dotyczące ładowania tabel wymiarów i faktów w modelu wymiarowym. Zawiera praktyczne wskazówki dotyczące magazynu w usłudze Microsoft Fabric, który jest środowiskiem obsługującym wiele funkcji języka T-SQL, takich jak tworzenie tabel i zarządzanie danymi w tabelach. Więc masz pełną kontrolę nad tworzeniem tabel modelu wymiarowego i pobieraniem danych.

Ładowanie modelu wymiarowego

Ładowanie modelu wymiarowego wiąże się z okresowym uruchamianiem procesu Extract, Transform, Load (ETL). Proces ETL koordynuje uruchamianie innych procesów, które zwykle dotyczą przejściowych danych źródłowych, synchronizowania danych wymiarów, wstawiania wierszy do tabel faktów i rejestrowania danych audytowych i błędów.

W przypadku rozwiązania magazynu sieci szkieletowej możesz użyć usługi Data Factory w usłudze Microsoft Fabric do tworzenia i uruchamiania procesu ETL. Proces może etapować, przekształcać i załadować dane źródłowe do tabel modelu wymiarowego.

W szczególności można wykonywać następujące czynności:

• Użyj potoków do budowania przepływów pracy i orkiestracji procesu ETL. Rury mogą wykonywać skrypty SQL, procedury składowane i nie tylko.
• Użyj przepływów danych, aby opracować logikę z małą ilością kodu w celu pozyskiwania danych z setek źródeł danych. Przepływy danych obsługują łączenie danych z wielu źródeł, przekształcanie danych, a następnie ładowanie ich do miejsca docelowego, takiego jak tabela modelu wymiarowego. Przepływy danych są tworzone przy użyciu znanego środowiska Power Query dostępnego obecnie w wielu produktach firmy Microsoft, w tym takich jak Microsoft Excel i Power BI Desktop.

Orkiestracja

Ogólny przepływ pracy procesu ETL to:

1. Opcjonalnie załaduj tabele przejściowe.
2. Przetwarzanie tabel wymiarów.
3. Przetwarzanie tabel faktów.
4. Opcjonalnie wykonaj zadania po przetwarzaniu, takie jak wyzwalanie odświeżania zawartości zależnej sieci szkieletowej (na przykład modelu semantycznego).

Tabele wymiarów należy przetworzyć najpierw, aby upewnić się, że przechowują wszystkich członków wymiarów, w tym tych dodanych do systemów źródłowych od ostatniego procesu ETL. Jeśli istnieją zależności między wymiarami, podobnie jak w przypadku wymiarów belki wspornikowej, tabele wymiarów powinny być przetwarzane zgodnie z kolejnością zależności. Na przykład wymiar geograficzny używany przez wymiar klienta i wymiar dostawcy powinien zostać przetworzony przed dwoma pozostałymi wymiarami.

Tabele faktów można przetwarzać po przetworzeniu wszystkich tabel wymiarów.

Po przetworzeniu wszystkich tabel modelu wymiarowego można wyzwolić odświeżanie zależnych modeli semantycznych. Dobrym pomysłem jest również wysłanie powiadomienia do odpowiednich pracowników w celu poinformowania ich o wyniku procesu ETL.

Dane etapu

Przejściowe dane źródłowe mogą pomóc w obsłudze wymagań dotyczących ładowania i przekształcania danych. Obejmuje wyodrębnianie danych systemu źródłowego i ładowanie ich do tabel przejściowych, które są tworzone w celu obsługi procesu ETL. Zalecamy etapowanie danych źródłowych, ponieważ one mogą:

• Zminimalizuj wpływ na systemy operacyjne.
• Służy do ułatwienia i optymalizowania przetwarzania ETL.
• Zapewnienie możliwości ponownego uruchomienia procesu ETL bez konieczności ponownego ładowania danych z systemów źródłowych.

Dane w tabelach przejściowych nigdy nie powinny być udostępniane użytkownikom biznesowym. Dotyczy to tylko procesu ETL.

Zalecamy utworzenie schematu w magazynie, prawdopodobnie o nazwie staging. Tabele przejściowe powinny przypominać tabele źródłowe tak blisko, jak to możliwe pod względem nazw kolumn i typów danych. Zawartość każdej tabeli powinna zostać usunięta na początku procesu ETL. TRUNCATE TABLE jest obsługiwane w tym celu.

Alternatywy wirtualizacji danych można również rozważyć w ramach strategii przejściowej. Możesz użyć:

• Dublowanie, które jest rozwiązaniem o niskich kosztach i małych opóźnieniach, które umożliwia utworzenie repliki danych w usłudze OneLake. Aby uzyskać więcej informacji, zobacz Dlaczego warto używać mirroringu w Fabric?.
• Skróty OneLake, które wskazują na inne lokalizacje przechowywania mogące zawierać dane źródłowe. Skróty mogą być używane jako tabele w zapytaniach T-SQL.
• Technologia PolyBase w programie SQL Server, która jest funkcją wirtualizacji danych dla programu SQL Server. Technologia PolyBase umożliwia wykonywanie zapytań T-SQL w celu łączenia danych ze źródeł zewnętrznych do tabel relacyjnych w wystąpieniu programu SQL Server.
• Wirtualizacja danych za pomocą usługi Azure SQL Managed Instance, która umożliwia wykonywanie zapytań T-SQL dotyczących plików przechowujących dane w typowych formatach danych w usłudze Azure Data Lake Storage (ADLS) Gen2 lub Azure Blob Storage oraz łączenie ich z lokalnie przechowywanymi danymi relacyjnymi przy użyciu sprzężeń.

Przekształcanie danych

Struktura danych źródłowych może nie przypominać struktur docelowych tabel modelu wymiarowego. Dlatego proces ETL musi ponownie przekształcać dane źródłowe, aby dopasować je do struktury tabel modelu wymiarowego.

Ponadto magazyn danych musi dostarczać oczyszczone i zgodne dane, więc konieczne może być przekształcenie danych źródłowych w celu zapewnienia jakości i spójności.

Oto kilka przekształceń, które może wykonać proces ETL.

• Łączenie danych: dane z różnych źródeł można zintegrować (scalić) na podstawie pasujących kluczy. Na przykład dane produktów są przechowywane w różnych systemach (takich jak produkcja i marketing), ale wszystkie używają wspólnej jednostki magazynowej (SKU). Dane można również dołączać, gdy mają wspólną strukturę. Na przykład dane sprzedaży są przechowywane w wielu systemach. Połączenie sprzedaży z każdego systemu może wygenerować nadzbiór wszystkich danych sprzedaży.
• Konwertowanie typów danych: typy danych można konwertować na te zdefiniowane w tabelach modelu wymiarowego.
• Obliczenia: obliczenia można wykonywać w celu utworzenia wartości dla tabel modelu wymiarowego. Na przykład w przypadku tabeli wymiarów pracownika można połączyć imię i nazwisko, aby utworzyć pełną nazwę. W innym przykładzie w tabeli faktów sprzedaży możesz obliczyć przychód ze sprzedaży brutto, który jest produktem ceny jednostkowej i ilości.
• Wykrywanie zmian historycznych i zarządzanie nimi: zmiany można wykrywać i odpowiednio przechowywać w tabelach wymiarów. Aby uzyskać więcej informacji, zobacz Zarządzanie zmianami historycznymi w dalszej części tego artykułu.
• Agregowanie danych: Agregacja może służyć do zmniejszenia wymiarowości tabeli faktów i/lub zwiększenia szczegółowości faktów. Na przykład tabela faktów sprzedaży nie musi przechowywać numerów zamówień sprzedaży. W związku z tym zagregowany wynik grupujący według wszystkich kluczy wymiarów może służyć do przechowywania danych tabeli faktów.

Ładowanie danych

Tabele można załadować do Fabric Warehouse przy użyciu następujących opcji pozyskiwania danych.

• COPY INTO (T-SQL): Ta opcja jest przydatna, gdy dane źródłowe składają się z plików Parquet lub CSV przechowywanych na zewnętrznym koncie usługi Azure Storage, takim jak ADLS Gen2 lub Azure Blob Storage.
• Potoki: Oprócz organizowania procesu ETL, potoki mogą obejmować działania wykonujące instrukcje T-SQL, wyszukiwanie danych lub kopiowanie danych ze źródła do miejsca docelowego.
• Przepływy danych: Jako alternatywa dla potoków przepływy danych zapewniają środowisko bez kodu do przekształcania i czyszczenia danych.
• Pozyskiwanie między magazynami: gdy dane są przechowywane w tym samym obszarze roboczym, pozyskiwanie między magazynami umożliwia łączenie różnych tabel magazynu lub magazynu typu lakehouse. Obsługuje polecenia języka T-SQL, takie jak INSERT...SELECT, SELECT INTOi CREATE TABLE AS SELECT (CTAS). Te polecenia są szczególnie przydatne, gdy chcesz przekształcić i załadować dane z tabel przejściowych w tym samym obszarze roboczym. Są to również operacje oparte na zestawie, które prawdopodobnie będą najbardziej wydajnym i najszybszym sposobem ładowania tabel modelu wymiarowego.

Rejestrowanie

Procesy ETL zwykle wymagają dedykowanego monitorowania i konserwacji. Z tych powodów zalecamy zarejestrowanie wyników procesu ETL w tabelach modeli niewymiarowych w magazynie. Należy wygenerować unikatowy identyfikator dla każdego procesu ETL i użyć go do rejestrowania szczegółów dotyczących każdej operacji.

Rozważ rejestrowanie:

• Proces ETL:
  • Unikalny identyfikator każdego wykonania ETL
  • Godzina rozpoczęcia i godzina zakończenia
  • Stan (powodzenie lub niepowodzenie)
  • Wszelkie napotkane błędy
• Każda z tabel modelu przejściowego i wymiarowego:
  • Godzina rozpoczęcia i godzina zakończenia
  • Stan (powodzenie lub niepowodzenie)
  • Wstawione, zaktualizowane i usunięte wiersze
  • Końcowa liczba wierszy tabeli
  • Wszelkie napotkane błędy
• Inne operacje:
  • Godzina rozpoczęcia i godzina zakończenia operacji odświeżania modelu semantycznego

Przetwarzanie tabel wymiarów

Przetwarzanie tabeli wymiarów obejmuje synchronizowanie danych magazynu danych z systemami źródłowymi. Dane źródłowe są najpierw przekształcane i przygotowane do załadowania do tabeli wymiarów. Te dane są następnie dopasowywane do istniejących danych tabeli wymiarów poprzez łączenie z kluczami biznesowymi. Następnie można określić, czy dane źródłowe reprezentują nowe, czy zmodyfikowane dane. Gdy tabela wymiarów stosuje wolno zmieniający się typ wymiaru (SCD) 1, zmiany są wprowadzane przez zaktualizowanie istniejących wierszy tabeli wymiarów. Gdy tabela zastosuje zmiany typu SCD 2, istniejąca wersja wygasła i zostanie wstawiona nowa wersja.

Na poniższym diagramie przedstawiono logikę używaną do przetwarzania tabeli wymiarów.

Rozważ proces tabeli wymiarów Product .

• Po dodaniu nowych produktów do systemu źródłowego wiersze są wstawiane do tabeli wymiarów Product .
• Po zmodyfikowaniu produktów istniejące wiersze w tabeli wymiarów zostaną zaktualizowane lub wstawione.
  • W przypadku zastosowania typu SCD 1 aktualizacje są wprowadzane do istniejących wierszy.
  • W przypadku zastosowania typu SCD 2 aktualizacje są wprowadzane w celu wygaśnięcia bieżących wersji wierszy, a nowe wiersze reprezentujące bieżącą wersję zostaną wstawione.
  • W przypadku zastosowania typu SCD 3 proces podobny do typu SCD 1 występuje, aktualizując istniejące wiersze bez wstawiania nowych wierszy.

Klucze zastępcze

Zalecamy, aby każda tabela wymiarów zawierała klucz zastępczy, który powinien używać najmniejszego możliwego typu danych całkowitych. W środowiskach opartych na SQL Server jest to zazwyczaj wykonywane przez utworzenie kolumny IDENTITY, a w hurtowni danych Fabric kolumny IDENTITY są dostępne z pewnymi ograniczeniami. Aby uzyskać więcej informacji, zobacz kolumny IDENTITY i Używanie kolumn IDENTITY do tworzenia kluczy zastępczych w usłudze Fabric Data Warehouse.

Zarządzanie zmianami historycznymi

Gdy tabela wymiarów musi przechowywać zmiany historyczne, należy zaimplementować wolno zmieniający się wymiar (SCD).

Możliwe, że wymiar obsługuje zmiany typu SCD 1 i/lub typu SCD 2.

Typ SCD 1

Po wykryciu zmian typu SCD 1 użyj następującej logiki.

1. Zaktualizuj wszystkie zmienione atrybuty.
2. Jeśli tabela zawiera datę ostatniej modyfikacji i ostatnio zmodyfikowaną przez kolumny, ustaw bieżącą datę i proces, który dokonał modyfikacji.

Typ SCD 2

Po wykryciu zmian typu SCD 2 użyj następującej logiki.

1. Aby unieważnić bieżącą wersję, ustaw kolumnę ważności daty zakończenia na datę przetwarzania ETL (lub odpowiedni znacznik czasu w systemie źródłowym) i bieżącą wartość flagi na FALSE.
2. Jeśli tabela zawiera datę ostatniej modyfikacji i ostatnio zmodyfikowaną przez kolumny, ustaw bieżącą datę i proces, który dokonał modyfikacji.
3. Wstaw nowe elementy członkowskie, które mają kolumnę ważności daty rozpoczęcia ustawioną na wartość kolumny ważności daty zakończenia (używana do aktualizacji poprzedniej wersji) i mają flagę obecnej wersji ustawioną na TRUE.
4. Jeśli tabela zawiera kolumny data utworzenia i utworzone przez, ustaw bieżącą datę oraz proces, który dokonał wstawień.

Typ SCD 3

Po wykryciu zmian typu SCD 3 zaktualizuj atrybuty przy użyciu podobnej logiki do przetwarzania typu SCD 1.

Usuwanie elementów członkowskich wymiaru

Zachowaj ostrożność, jeśli dane źródłowe wskazują, że członkowie wymiaru zostali usunięci (albo nie są pobierani z systemu źródłowego, albo zostali oznaczeni jako usunięci). Nie należy synchronizować usunięć z tabelą wymiarów, chyba że członkowie wymiaru zostali utworzeni w błędzie i nie ma żadnych rekordów faktu związanych z nimi.

Odpowiednim sposobem obsługi usuwania źródła jest zarejestrowanie ich jako miękkie usunięcie. Miękkie usunięcie oznacza element członkowski wymiaru jako nieaktywny lub nieważny. Aby zapewnić obsługę tego przypadku, tabela wymiarów powinna zawierać atrybut logiczny z typem danych 'bit', na przykład IsDeleted. Zaktualizuj tę kolumnę dla usuniętych członów wymiaru do TRUE (1). Bieżąca wersja elementu członkowskiego wymiaru może być podobnie oznaczona wartością logiczną (bitową) w kolumnach IsCurrent lub IsActive. Wszystkie zapytania raportowania i semantyczne modele Power BI powinny filtrować rekordy oznaczone jako soft delete.

Wymiar daty

Wymiary czasowe, takie jak kalendarz i czas, są specjalnymi przypadkami, ponieważ zwykle nie mają danych źródłowych. Zamiast tego są generowane przy użyciu stałej logiki.

Należy załadować tabelę wymiarów daty na początku każdego nowego roku, aby rozszerzyć wiersze na określoną liczbę lat. Mogą istnieć inne dane biznesowe, na przykład dane roku obrachunkowego, święta, numery tygodni, które mają być regularnie aktualizowane.

Gdy tabela wymiarów daty zawiera względne atrybuty przesunięcia, proces ETL musi być uruchamiany codziennie, aby zaktualizować wartości atrybutów przesunięcia na podstawie bieżącej daty (dzisiaj).

Zalecamy, aby logika rozszerzania lub aktualizowania tabeli wymiarów daty została zapisana w języku T-SQL i hermetyzowana w procedurze składowanej.

Przetwarzanie tabel faktów

Przetwarzanie tabeli faktów obejmuje synchronizowanie danych magazynu danych z faktami systemu źródłowego. Dane źródłowe są najpierw przekształcane i przygotowane do załadowania ich do tabeli faktów. Następnie dla każdego klucza wymiaru operacja wyszukiwania określa wartość klucza zastępczego do przechowywania w wierszu faktów. Gdy wymiar obsługuje typ SCD 2, należy pobrać klucz zastępczy dla bieżącej wersji członka wymiaru.

Jeśli wyszukiwanie klucza wymiaru nie powiedzie się, może to wskazywać na problem z integralnością systemu źródłowego. W tym przypadku wiersz faktów musi nadal zostać wstawiony do tabeli faktów. Prawidłowy klucz wymiaru musi być nadal przechowywany. Jednym z podejść jest przechowywanie specjalnego członka wymiaru (takiego jak Nieznany). Takie podejście wymaga aktualizacji w późniejszym terminie, aby poprawnie przypisać prawdziwą wartość klucza wymiaru, gdy jest znana.

Innym sposobem podejścia, odpowiednim w sytuacji, gdy mamy pewność, że klucz naturalny jest prawidłowy, jest wstawienie nowego elementu członkowskiego wymiaru, a następnie zapisanie jego wartości klucza zastępczego. Aby uzyskać więcej informacji, zobacz Wywnioskowane elementy członkowskie wymiaru w dalszej części tej sekcji.

Na poniższym diagramie przedstawiono logikę używaną do przetwarzania tabeli faktów.

Jeśli to możliwe, tabela faktów powinna być ładowana przyrostowo, co oznacza, że nowe fakty są wykrywane i wstawiane. Strategia obciążenia przyrostowego jest bardziej skalowalna i zmniejsza obciążenie zarówno systemów źródłowych, jak i systemów docelowych.

Mam nadzieję, że możesz skutecznie wykrywać nowe fakty, opierając się na identyfikatorach systemu źródłowego lub sygnaturach czasowych. Na przykład gdy system źródłowy niezawodnie rejestruje zamówienia sprzedaży, które są w sekwencji, można przechowywać najnowszy numer zamówienia sprzedaży pobrany (znany jako wysoki znak wodny). Następny proces może użyć tego numeru zamówienia sprzedaży, aby pobrać nowo utworzone zamówienia sprzedaży, a następnie zapisać najnowszy numer zamówienia sprzedaży pobrany do użycia przez następny proces. Może być również możliwe, że kolumna daty utworzenia może służyć do niezawodnego wykrywania nowych zamówień.

Jeśli nie możesz polegać na danych systemu źródłowego w celu wydajnego wykrywania nowych faktów, możesz polegać na możliwości systemu źródłowego w celu przeprowadzenia przyrostowego obciążenia. Na przykład programy SQL Server i Azure SQL Managed Instance mają funkcję o nazwie change data capture (CDC), która może śledzić zmiany w każdym wierszu w tabeli. Ponadto program SQL Server, usługa Azure SQL Managed Instance i usługa Azure SQL Database mają funkcję nazywaną śledzeniem zmian, która umożliwia identyfikowanie zmienionych wierszy. Po włączeniu może pomóc w wydajnym wykrywaniu nowych lub zmienionych danych w dowolnej tabeli bazy danych. Możesz również dodać wyzwalacze do tabel relacyjnych, które przechowują klucze wstawionych, zaktualizowanych lub usuniętych rekordów tabeli.

Na koniec możesz skorelować dane źródłowe z tabelą faktów przy użyciu atrybutów. Na przykład numer zamówienia handlowego i numer wiersza zamówienia handlowego. Jednak w przypadku dużych tabel faktów może to być bardzo kosztowna operacja wykrywania nowych, zmienionych lub usuniętych faktów. Może to być również problematyczne, gdy system źródłowy zarchiwizuje dane operacyjne.

Wywnioskowani członkowie wymiaru

Gdy proces ładowania faktów wstawia nowy członek wymiaru, nazywa się to wywnioskowanym członkiem. Na przykład, gdy gość hotelowy się melduje, są proszeni o dołączenie do sieci hotelowej jako członek programu lojalnościowego. Numer członkostwa jest wydawany natychmiast, ale szczegóły gościa mogą nie zostać dostarczone, dopóki dokumenty nie zostaną przesłane przez gościa (jeśli w ogóle).

Wszystko, co wiadomo o elemencie członkowskim wymiaru, to jego naturalny klucz. Proces ładowania faktów musi utworzyć nowy człon wymiaru przy użyciu wartości atrybutów Nieznany. Co ważne, należy ustawić IsInferredMemberatrybut kontroli na TRUE. W ten sposób, gdy późno przychodzące szczegóły zostaną pozyskane, proces ładowania danych wymiaru może wprowadzić niezbędne aktualizacje wiersza wymiaru. Aby uzyskać więcej informacji, zobacz Zarządzanie zmianami historycznymi w tym artykule.

Aktualizacje faktów lub usunięcia

Może być wymagane zaktualizowanie lub usunięcie danych faktów. Na przykład po anulowaniu zamówienia sprzedaży lub zmianie ilości zamówienia. Jak opisano wcześniej w przypadku ładowania tabel faktów, należy skutecznie wykrywać zmiany i wykonywać odpowiednie modyfikacje danych faktów. W tym przykładzie dla anulowanego zamówienia stan zamówienia sprzedaży prawdopodobnie zmieni się z Otwórz na Anulowane. Ta zmiana wymagałaby aktualizacji danych faktów, a nie usunięcia wiersza. W przypadku zmiany ilości konieczna byłaby aktualizacja miary ilości w wierszu faktu. Ta strategia używania miękkiego usuwania zachowuje historię. Miękkie usuwanie oznacza wiersz jako nieaktywny lub nieważny, a wszystkie zapytania raportowania i modele semantyczne usługi Power BI powinny odfiltrować rekordy, które są oznaczone w ten sposób.

Gdy przewidujesz aktualizacje lub usunięcia faktów, powinieneś uwzględnić atrybuty (takie jak numer zamówienia sprzedaży oraz jego numer wiersza) w tabeli faktów, aby ułatwić identyfikację wierszy faktów do zmodyfikowania. Pamiętaj, aby indeksować te kolumny w celu obsługi wydajnych operacji modyfikacji.

Na koniec, jeśli dane faktów zostały wstawione przy użyciu specjalnego elementu członkowskiego wymiaru (na przykład Nieznane), należy uruchomić okresowy proces, który pobiera aktualne dane źródłowe dla takich wierszy dotyczących faktów i aktualizuje klucze wymiarów na prawidłowe wartości.

Powiązana zawartość

Aby uzyskać więcej informacji na temat ładowania danych do magazynu Fabric, zobacz:

• Pozyskiwanie danych do magazynu
• Pozyskiwanie danych do magazynu przy użyciu potoków
• Pozyskiwanie danych do magazynu przy użyciu instrukcji COPY
• Pozyskiwanie danych do magazynu przy użyciu języka Transact-SQL
• Samouczek: Konfigurowanie dbt dla magazynu Fabric