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

Dotyczy:✅ punkt końcowy analizy 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óre jest środowiskiem obsługującym wiele funkcji języka T-SQL, takich jak tworzenie tabel i zarządzanie danymi w tabelach. W związku z tym masz pełną kontrolę nad tworzeniem tabel modelu wymiarowego i ładowaniem ich przy użyciu danych.

Ładowanie modelu wymiarowego

Ładowanie modelu wymiarowego obejmuje okresowe uruchamianie procesu wyodrębniania, przekształcania i ładowania (ETL). Proces ETL organizuje 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 inspekcji i błędów.

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

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

• Używanie potoków danych do tworzenia przepływów pracy w celu organizowania procesu ETL. Potoki danych 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 Dodatku Power Query dostępnego obecnie w wielu produktach firmy Microsoft, w tym w programie Microsoft Excel i programie Power BI Desktop.

Aranżacja

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 najpierw przetworzyć, aby upewnić się, że przechowują wszystkie elementy członkowskie wymiarów, w tym te dodane 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 pomocniczej, tabele wymiarów powinny być przetwarzane w 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 przygotowanie danych źródłowych, ponieważ 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. Należy jednak pamiętać, że tabele magazynu sieci szkieletowej nie mogą być obcięte. Zamiast tego można usunąć i ponownie utworzyć każdą tabelę przemieszczania przed załadowaniem jej przy użyciu danych.

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ć dublowania w sieci szkieletowej?.
• Skróty OneLake, które wskazują na inne lokalizacje magazynu, które mogą 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 współudzielą wspólną strukturę. Na przykład dane sprzedaży są przechowywane w wielu systemach. Związek 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ć w magazynie sieci szkieletowej 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 danych: oprócz organizowania procesu ETL potoki danych mogą obejmować działania uruchamiające instrukcje języka T-SQL, wyszukiwanie lub kopiowanie danych ze źródła danych do miejsca docelowego.
• Przepływy danych: jako alternatywa dla potoków danych przepływy danych zapewniają środowisko bez programowania w celu 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:
  • Unikatowy identyfikator każdego wykonania ETL
  • Godzina rozpoczęcia i godzina zakończenia
  • Stan (powodzenie lub niepowodzenie)
  • Napotkano wszelkie błędy
• Każda tabela 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
  • Napotkano wszelkie 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 przez dołączenie do kluczy biznesowych. 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 programie SQL Server, które są zwykle wykonywane przez utworzenie kolumny tożsamości, jednak ta funkcja nie jest obsługiwana w magazynie sieci szkieletowej. Zamiast tego należy użyć techniki obejścia, która generuje unikatowe identyfikatory.

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 może obsługiwać zmiany typu SCD 1 i/lub SCD typu 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. Wygaśnięcie bieżącej wersji przez ustawienie kolumny ważności daty zakończenia na datę przetwarzania ETL (lub odpowiedni znacznik czasu w systemie źródłowym) i bieżącą flagę 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 ma flagę bieżącej wersji ustawioną na TRUE.
4. Jeśli tabela zawiera utworzoną datę i utworzoną przez kolumny, ustaw bieżącą datę i proces, który dokonał wstawiania.

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

Należy pamiętać, czy dane źródłowe wskazują, że elementy członkowskie wymiaru zostały usunięte (ponieważ nie są pobierane z systemu źródłowego lub zostały oflagowane jako usunięte). Nie należy synchronizować usuwania z tabelą wymiarów, chyba że elementy członkowskie wymiaru zostały utworzone w błędzie i nie ma żadnych rekordów faktów związanych z nimi.

Odpowiednim sposobem obsługi usuwania źródła jest zarejestrowanie ich jako usunięcia nietrwałego. Usuwanie nietrwałe oznacza element członkowski wymiaru jako nieaktywny lub prawidłowy. Aby zapewnić obsługę tego przypadku, tabela wymiarów powinna zawierać atrybut logiczny z typem danych bitowych, na przykład IsDeleted. Zaktualizuj tę kolumnę dla wszystkich usuniętych elementów członkowskich wymiaru do TRUE (1). Bieżąca, najnowsza wersja elementu członkowskiego wymiaru może być podobnie oznaczona wartością logiczną (bit) w kolumnach IsCurrent lub IsActive . Wszystkie zapytania raportowania i semantyczne modele usługi Power BI powinny filtrować rekordy, które są usuwaniem nietrwałym.

Wymiar daty

Wymiary kalendarza i godziny 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 odnośnik 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 elementu członkowskiego 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 elementu członkowskiego wymiaru (na przykład Nieznany). Takie podejście wymaga późniejszej aktualizacji w celu poprawnego przypisania wartości klucza wymiaru true, jeśli jest znana.

Innym podejściem, odpowiednim, gdy istnieje pewność, że klucz naturalny jest prawidłowy, jest wstawienie nowego elementu członkowskiego wymiaru, a następnie zapisanie jego zastępczej wartości klucza. 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 sprzedaży i numer wiersza zamówienia sprzedaży. 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.

Wywnioskowane elementy członkowskie wymiaru

Gdy proces ładowania faktów wstawia nowy element członkowski wymiaru, jest znany jako wywnioskowany element członkowski. Na przykład po zaewidencjonowaniu gościa hotelowego zostanie wyświetlony monit o dołączenie do sieci hotelowej jako członka lojalności. Numer członkostwa jest wystawiany natychmiast, ale szczegóły gościa mogą nie być zgodne, dopóki dokumenty nie zostaną przesłane przez gościa (jeśli kiedykolwiek).

Wszystko, co wiadomo o elemencie członkowskim wymiaru, jest jego kluczem naturalnym. Proces ładowania faktów musi utworzyć nowy element członkowski wymiaru przy użyciu nieznanych wartości atrybutów. Co ważne, należy ustawić IsInferredMember atrybut audit na TRUE. W ten sposób po źródle późnych danych przychodzących proces ładowania 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 liczby wierszy faktów. Ta strategia używania usuwania nietrwałego zachowuje historię. Usuwanie nietrwałe oznacza wiersz jako nieaktywny lub prawidłowy, a wszystkie zapytania raportowania i modele semantyczne usługi Power BI powinny odfiltrować rekordy, które są usuwaniem nietrwałym.

Podczas przewidywania aktualizacji faktów lub usunięcia należy uwzględnić atrybuty (takie jak numer zamówienia sprzedaży i jego numer wiersza zamówienia sprzedaży) w tabeli faktów, aby ułatwić zidentyfikowanie 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 bieżące dane źródłowe dla takich wierszy faktów i aktualizuje klucze wymiarów do prawidłowych wartości.

Powiązana zawartość

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

• Pozyskiwanie danych do magazynu
• Pozyskiwanie danych do magazynu przy użyciu potoków danych
• Pozyskiwanie danych do magazynu przy użyciu instrukcji COPY
• Pozyskiwanie danych do magazynu przy użyciu języka Transact-SQL
• Samouczek: konfigurowanie bazy danych dla magazynu sieci szkieletowej