Modelowanie wymiarowe w usłudze Microsoft Fabric Warehouse: tabele wymiarów

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

Uwaga

Ten artykuł stanowi część serii artykułów dotyczących modelowania wymiarowego. Ta seria koncentruje się na wskazówkach i najlepszych rozwiązaniach projektowych związanych z modelowaniem wymiarowym w usłudze Microsoft Fabric Warehouse.

Ten artykuł zawiera wskazówki i najlepsze rozwiązania dotyczące projektowania tabel wymiaró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.

Uwaga

W tym artykule termin magazyn danych odnosi się do magazynu danych przedsiębiorstwa, który zapewnia kompleksową integrację krytycznych danych w całej organizacji. Z kolei autonomiczny magazyn terminów odnosi się do magazynu sieci szkieletowej, który jest oprogramowaniem jako usługą (SaaS) ofertą relacyjnej bazy danych, której można użyć do zaimplementowania magazynu danych. Aby uzyskać jasność, w tym artykule ten ostatni jest wymieniony jako Magazyn sieci szkieletowej.

Napiwek

Jeśli nie masz doświadczenia z modelowaniem wymiarowym, rozważ tę serię artykułów, które są pierwszym krokiem. Nie jest ona przeznaczona do pełnej dyskusji na temat projektowania modelowania wymiarowego. Aby uzyskać więcej informacji, zapoznaj się bezpośrednio z powszechnie opublikowaną zawartością, na przykład The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (trzeci wydanie, 2013) autorstwa Ralph Kimball i innych.

W modelu wymiarowym tabela wymiarów opisuje jednostkę odpowiednią dla wymagań biznesowych i analitycznych. Ogólnie rzecz biorąc, tabele wymiarów reprezentują elementy , które modelujesz. Mogą to być produkty, osoby, miejsca lub inne pojęcia, w tym data i godzina. Aby łatwo zidentyfikować tabele wymiarów, zazwyczaj prefiksuj ich nazwy za pomocą d_ polecenia lub Dim_.

Struktura tabeli wymiarów

Aby opisać strukturę tabeli wymiarów, rozważmy następujący przykład tabeli wymiarów sprzedawcy o nazwie d_Salesperson. W tym przykładzie stosuje się dobre rozwiązania projektowe. Każda z grup kolumn jest opisana w poniższych sekcjach.

CREATE TABLE d_Salesperson
(
    --Surrogate key
    Salesperson_SK INT NOT NULL,
    
    --Natural key(s)
    EmployeeID VARCHAR(20) NOT NULL,
    
    --Dimension attributes
    FirstName VARCHAR(20) NOT NULL,
    <…>
    
    --Foreign key(s) to other dimensions
    SalesRegion_FK INT NOT NULL,
    <…>
    
    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditIsInferred BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

Klucz zastępczy

Przykładowa tabela wymiarów ma klucz zastępczy o nazwie Salesperson_SK. Klucz zastępczy to unikatowy identyfikator z jedną kolumną, który jest generowany i przechowywany w tabeli wymiarów. Jest to kolumna klucza podstawowego używana do powiązania z innymi tabelami w modelu wymiarowym.

Klucze zastępcze dążą do odizolowania magazynu danych od zmian w danych źródłowych. Zapewniają one również wiele innych korzyści, dzięki czemu można wykonywać następujące czynności:

• Skonsoliduj wiele źródeł danych (unikając starcia zduplikowanych identyfikatorów).
• Skonsoliduj wielokolumny klucze naturalne w bardziej wydajny, jednokolumny klucz.
• Śledź historię wymiarów z wolno zmieniającym się typem wymiaru (SCD) 2.
• Ogranicz szerokość tabeli faktów na potrzeby optymalizacji magazynu (wybierając najmniejszy możliwy typ danych całkowitych).

Kolumna klucza zastępczego jest zalecaną praktyką, nawet jeśli klucz naturalny (opisany poniżej) wydaje się akceptowalnym kandydatem. Należy również unikać nadawania znaczenia wartościom klucza (z wyjątkiem kluczy wymiarów daty i godziny, zgodnie z opisem w dalszej części).

Klucze naturalne

Przykładowa tabela wymiarów ma również klucz naturalny o nazwie EmployeeID. Klucz naturalny jest kluczem przechowywanym w systemie źródłowym. Umożliwia powiązanie danych wymiarów z systemem źródłowym, który jest zwykle wykonywany przez proces wyodrębniania, ładowania i przekształcania (ETL) w celu załadowania tabeli wymiarów. Czasami klucz naturalny jest nazywany kluczem biznesowym, a jego wartości mogą mieć znaczenie dla użytkowników biznesowych.

Czasami wymiary nie mają klucza naturalnego. Może to być przypadek wymiarów daty lub wymiarów odnośnika albo podczas generowania danych wymiarów przez normalizację pliku prostego.

Atrybuty wymiaru

Przykładowa tabela wymiarów zawiera również atrybuty wymiarów, takie jak kolumna FirstName . Atrybuty wymiaru zapewniają kontekst dla danych liczbowych przechowywanych w powiązanych tabelach faktów. Zazwyczaj są to kolumny tekstowe, które są używane w zapytaniach analitycznych do filtrowania i grupowania (fragmentowanie i kostka), ale nie są agregowane samodzielnie. Niektóre tabele wymiarów zawierają kilka atrybutów, a inne zawierają wiele atrybutów (tyle, ile potrzeba do obsługi wymagań zapytania modelu wymiarowego).

Napiwek

Dobrym sposobem określenia wymiarów i atrybutów, których potrzebujesz, jest znalezienie odpowiednich osób i zadawanie odpowiednich pytań. W szczególności bądź czujny, aby wspomnieć o słowie. Jeśli na przykład ktoś mówi, że musi analizować sprzedaż według sprzedawcy, według miesiąca i kategorii produktów, informuje o tym, że potrzebują wymiarów, które mają te atrybuty.

Jeśli planujesz utworzyć model semantyczny usługi Direct Lake, należy uwzględnić wszystkie możliwe kolumny wymagane do filtrowania i grupowania jako atrybutów wymiarów. Dzieje się tak, ponieważ semantyczne modele usługi Direct Lake nie obsługują kolumn obliczeniowych.

Klucze obce

Przykładowa tabela wymiarów ma również klucz obcy o nazwie SalesRegion_FK. Inne tabele wymiarów mogą odwoływać się do klucza obcego, a ich obecność w tabeli wymiarów jest szczególnym przypadkiem. Wskazuje, że tabela jest powiązana z inną tabelą wymiarów, co oznacza, że może stanowić część wymiaru płatka śniegu lub jest związana z wymiarem tabeli pomocniczej.

Magazyn sieci szkieletowej obsługuje ograniczenia klucza obcego, ale nie można ich wymuszać. Dlatego ważne jest, aby proces ETL testuje integralność między powiązanymi tabelami podczas ładowania danych.

Nadal dobrym pomysłem jest utworzenie kluczy obcych. Jednym z powodów tworzenia niewymuszonych kluczy obcych jest umożliwienie modelowania narzędzi, takich jak program Power BI Desktop, automatycznego wykrywania i tworzenia relacji między tabelami w modelu semantycznym.

Atrybuty śledzenia historycznego

Przykładowa tabela wymiarów ma również różne atrybuty śledzenia historycznego. Atrybuty śledzenia historycznego są opcjonalne w zależności od potrzeb śledzenia określonych zmian w miarę ich występowania w systemie źródłowym. Umożliwiają one przechowywanie wartości w celu obsługi głównej roli magazynu danych, czyli dokładnego opisania przeszłości. W szczególności te atrybuty przechowują kontekst historyczny, ponieważ proces ETL ładuje nowe lub zmienione dane do wymiaru.

Aby uzyskać więcej informacji, zobacz Zarządzanie zmianami historycznymi w dalszej części tego artykułu.

Atrybuty inspekcji

Przykładowa tabela wymiarów zawiera również różne atrybuty inspekcji. Atrybuty inspekcji są opcjonalne, ale zalecane. Umożliwiają one śledzenie, kiedy i jak rekordy wymiarów zostały utworzone lub zmodyfikowane, i mogą zawierać informacje diagnostyczne lub rozwiązywania problemów zgłaszane podczas procesów ETL. Na przykład chcesz śledzić, kto (lub jaki proces) zaktualizował wiersz i kiedy. Atrybuty inspekcji mogą również pomóc zdiagnozować trudny problem, na przykład gdy proces ETL zostanie nieoczekiwanie zatrzymany. Mogą również oznaczać elementy członkowskie wymiaru jako błędy lub wnioskowane elementy członkowskie.

Rozmiar tabeli wymiarów

Często najbardziej przydatne i uniwersalne wymiary w modelu wymiarowym to duże, szerokie wymiary. Są one duże pod względem wierszy (ponad milionów) i szerokie pod względem liczby atrybutów wymiaru (potencjalnie setek). Rozmiar nie jest tak ważny (chociaż należy projektować i optymalizować pod kątem najmniejszego możliwego rozmiaru). Ma to znaczenie, że wymiar obsługuje wymagane filtrowanie, grupowanie i dokładną analizę historyczną danych faktów.

Duże wymiary mogą być pozyskiwane z wielu systemów źródłowych. W takim przypadku przetwarzanie wymiarów musi łączyć, scalać, deduplikować i standaryzować dane; i przypisz klucze zastępcze.

Dla porównania niektóre wymiary są małe. Mogą one reprezentować tabele odnośników zawierające tylko kilka rekordów i atrybutów. Często te małe wymiary przechowują wartości kategorii związane z transakcjami w tabelach faktów i są implementowane jako wymiary z kluczami zastępczymi w celu powiązania z rekordami faktów.

Napiwek

Jeśli masz wiele małych wymiarów, rozważ skonsolidowanie ich w wymiarze śmieci.

Pojęcia dotyczące projektowania wymiarów

W tej sekcji opisano różne pojęcia dotyczące projektowania wymiarów.

Denormalizacja a normalizacja

Prawie zawsze zdarza się, że tabele wymiarów powinny być zdenormalizowane. Podczas gdy normalizacja jest terminem używanym do opisywania danych przechowywanych w sposób, który zmniejsza nadmiarowe dane, denormalizacja jest terminem używanym do definiowania, gdzie istnieją wstępnie skompilowane nadmiarowe dane. Nadmiarowe dane istnieją zazwyczaj ze względu na magazyn hierarchii (omówionych w dalszej części), co oznacza, że hierarchie są spłaszczone. Na przykład wymiar produktu może przechowywać podkategorię (oraz powiązane atrybuty) i kategorię (oraz powiązane atrybuty).

Ponieważ wymiary są zwykle małe (w porównaniu z tabelami faktów), koszt przechowywania nadmiarowych danych jest prawie zawsze przewyższany przez zwiększoną wydajność i użyteczność zapytań.

Wymiary płatka śniegu

Jednym wyjątkiem od denormalizacji jest zaprojektowanie wymiaru płatka śniegu. Wymiar płatka śniegu jest znormalizowany i przechowuje dane wymiarów w kilku powiązanych tabelach.

Na poniższym diagramie przedstawiono wymiar płatka śniegu, który składa się z trzech powiązanych tabel wymiarów: Product, Subcategoryi Category.

Rozważ zaimplementowanie wymiaru płatka śniegu, gdy:

• Wymiar jest bardzo duży, a koszty magazynowania przewyższają potrzebę wysokiej wydajności zapytań. (Należy jednak okresowo ponownie ocenić, że nadal pozostaje tak).
• Potrzebujesz kluczy, aby powiązać wymiar z faktami o wyższym stopniu szczegółowość. Na przykład tabela faktów sprzedaży przechowuje wiersze na poziomie produktu, ale docelowa tabela faktów sprzedaży przechowuje wiersze na poziomie podkategorii.
• Należy śledzić zmiany historyczne na wyższym poziomie szczegółowości.

Uwaga

Należy pamiętać, że hierarchia w modelu semantycznym usługi Power BI może być oparta tylko na kolumnach z pojedynczej tabeli modelu semantycznego. W związku z tym wymiar płatka śniegu powinien dostarczyć zdenormalizowany wynik przy użyciu widoku, który łączy ze sobą tabele płatka śniegu.

Hierarchie

Często kolumny wymiarów tworzą hierarchie. Hierarchie umożliwiają eksplorowanie danych na różnych poziomach podsumowania. Na przykład początkowy widok wizualizacji macierzy może pokazywać roczną sprzedaż, a konsument raportu może wybrać przechodzenie do szczegółów w celu ujawnienia sprzedaży kwartalnej i miesięcznej.

Istnieją trzy sposoby przechowywania hierarchii w wymiarze. Możesz użyć:

• Kolumny z jednego, zdenormalizowanego wymiaru.
• Wymiar płatka śniegu, który składa się z wielu powiązanych tabel.
• Relacja elementu nadrzędnego podrzędnego (odwołującego się do siebie) w wymiarze.

Hierarchie mogą być zrównoważone lub niezrównoważone. Ważne jest również, aby zrozumieć, że niektóre hierarchie są poszarpane.

Zrównoważone hierarchie

Zrównoważone hierarchie są najczęstszym typem hierarchii. Zrównoważona hierarchia ma taką samą liczbę poziomów. Typowym przykładem zrównoważonej hierarchii jest hierarchia kalendarza w wymiarze daty, który obejmuje poziomy dla roku, kwartału, miesiąca i daty.

Na poniższym diagramie przedstawiono zrównoważoną hierarchię regionów sprzedaży. Składa się z dwóch poziomów, które są grupą regionów sprzedaży i regionem sprzedaży.

Poziomy zrównoważonej hierarchii są oparte na kolumnach z jednego, zdenormalizowanego wymiaru lub z tabel, które tworzą wymiar płatka śniegu. W oparciu o pojedynczy, zdenormalizowany wymiar kolumny reprezentujące wyższe poziomy zawierają nadmiarowe dane.

W przypadku zrównoważonych hierarchii fakty zawsze odnoszą się do pojedynczego poziomu hierarchii, który jest zazwyczaj najniższym poziomem. W ten sposób fakty mogą być agregowane (rzutowane) na najwyższy poziom hierarchii. Fakty mogą odnosić się do dowolnego poziomu, który jest określany przez ziarno tabeli faktów. Na przykład tabela faktów sprzedaży może być przechowywana na poziomie daty, podczas gdy tabela faktów docelowych sprzedaży może być przechowywana na poziomie kwartału.

Hierarchie niezrównoważone

Hierarchie niezrównoważone są mniej typowym typem hierarchii. Hierarchia niezrównoważonej ma poziomy oparte na relacji nadrzędny-podrzędny. Z tego powodu liczba poziomów w hierarchii niezrównoważonej jest określana przez wiersze wymiarów, a nie określone kolumny tabeli wymiarów.

Typowym przykładem niezrównoważonej hierarchii jest hierarchia pracowników, w której każdy wiersz w wymiarze pracownika odnosi się do wiersza menedżera raportowania w tej samej tabeli. W takim przypadku każdy pracownik może być menedżerem z pracownikami raportowania. Oczywiście niektóre gałęzie hierarchii będą miały więcej poziomów niż inne.

Na poniższym diagramie przedstawiono niezrównoważone hierarchię. Składa się on z czterech poziomów, a każdy element członkowski w hierarchii jest sprzedawcą. Zwróć uwagę, że sprzedawcy mają inną liczbę przodków w hierarchii zgodnie z tym, do kogo zgłaszają.

Inne typowe przykłady niezrównoważonych hierarchii obejmują rachunek materiałów, modele własności firmy i ogólny rejestr.

W przypadku hierarchii niezrównoważonych fakty zawsze odnoszą się do ziarna wymiaru. Na przykład fakty sprzedaży odnoszą się do różnych sprzedawców, którzy mają różne struktury raportowania. Tabela wymiarów ma klucz zastępczy (o nazwie Salesperson_SK) i kolumnę ReportsTo_Salesperson_FK klucza obcego, która odwołuje się do kolumny klucza podstawowego. Każdy sprzedawca bez nikogo do zarządzania nie musi być na najniższym poziomie żadnej gałęzi hierarchii. Jeśli nie są na najniższym poziomie, sprzedawca może sprzedawać produkty i zgłaszać sprzedawców, którzy również sprzedają produkty. Dlatego zestawienie danych faktów musi uwzględniać poszczególnych sprzedawców i wszystkich ich elementów potomnych.

Wykonywanie zapytań względem hierarchii nadrzędny-podrzędny może być złożone i powolne, szczególnie w przypadku dużych wymiarów. Mimo że system źródłowy może przechowywać relacje jako element nadrzędny-podrzędny, zalecamy naturalizowanie hierarchii. W tym przypadku naturalizowanie oznacza przekształcanie i przechowywanie poziomów hierarchii w wymiarze jako kolumn.

Napiwek

Jeśli nie chcesz naturalizować hierarchii, nadal możesz utworzyć hierarchię na podstawie relacji nadrzędny-podrzędny w modelu semantycznym usługi Power BI. Jednak takie podejście nie jest zalecane w przypadku dużych wymiarów. Aby uzyskać więcej informacji, zobacz Understanding functions for parent-child hierarchies in DAX (Opis funkcji dla hierarchii nadrzędny-podrzędny w języku DAX).

Niewyrównane hierarchie

Czasami hierarchia jest poszarpana , ponieważ element nadrzędny elementu członkowskiego w hierarchii istnieje na poziomie, który nie znajduje się bezpośrednio nad nią. W takich przypadkach brakujące wartości poziomu powtarzają wartość elementu nadrzędnego.

Rozważmy przykład zrównoważonej hierarchii geograficznej. Hierarchia poszarpana istnieje, gdy kraj/region nie ma stanów ani prowincji. Na przykład Nowa Zelandia nie ma ani stanów, ani prowincji. Dlatego podczas wstawiania wiersza Nowa Zelandia należy również przechowywać wartość kraju/regionu w kolumnie StateProvince .

Na poniższym diagramie przedstawiono poszarpaną hierarchię regionów geograficznych.

Zarządzanie zmianami historycznymi

W razie potrzeby można zarządzać zmianami historycznymi przez zaimplementowanie wolno zmieniającego się wymiaru (SCD). ScD utrzymuje kontekst historyczny jako nowe lub zmienione dane są ładowane do niego.

Poniżej przedstawiono najbardziej typowe typy scD.

• Typ 1. Zastąp istniejący element członkowski wymiaru.
• Typ 2. Wstaw nowy element członkowski wymiaru w wersji opartej na czasie.
• Typ 3. Śledzenie ograniczonej historii za pomocą atrybutów.

Możliwe, że wymiar może obsługiwać zmiany typu SCD 1 i SCD typu 2.

Typ SCD 3 nie jest często używany, częściowo ze względu na fakt, że trudno jest go użyć w modelu semantycznym. Należy dokładnie rozważyć, czy podejście typu SCD 2 byłoby lepsze.

Napiwek

Jeśli przewidujesz szybko zmieniający się wymiar, który jest wymiarem, który często się zmienia, rozważ dodanie tego atrybutu do tabeli faktów. Jeśli atrybut jest liczbowy, taki jak cena produktu, możesz dodać go jako miarę w tabeli faktów. Jeśli atrybut jest wartością tekstową, można utworzyć wymiar na podstawie wszystkich wartości tekstowych i dodać jego klucz wymiaru do tabeli faktów.

Typ SCD 1

Zmiany typu SCD 1 zastępują istniejący wiersz wymiaru, ponieważ nie ma potrzeby śledzenia zmian. Ten typ SCD może również służyć do poprawiania błędów. Jest to typowy typ protokołu SCD i powinien być używany do większości zmieniających się atrybutów, takich jak nazwa klienta, adres e-mail i inne.

Na poniższym diagramie przedstawiono stan przed i po elemencie członkowskim wymiaru sprzedawcy, w którym zmienił się ich numer telefonu.

Ten typ scD nie zachowuje perspektywy historycznej, ponieważ istniejący wiersz jest aktualizowany. Oznacza to, że zmiany typu SCD typu 1 mogą powodować różne agregacje wyższego poziomu. Jeśli na przykład sprzedawca jest przypisany do innego regionu sprzedaży, zmiana typu SCD 1 spowoduje zastąpienie wiersza wymiaru. Pakiet zbiorczy sprzedawców historycznych wyników sprzedaży w regionie wygenerowałby inny wynik, ponieważ teraz używa nowego bieżącego regionu sprzedaży. Tak, jakby sprzedawca był zawsze przypisywany do nowego regionu sprzedaży.

Typ SCD 2

Zmiana typu SCD 2 powoduje, że nowe wiersze reprezentują wersję czasową elementu członkowskiego wymiaru. Zawsze istnieje bieżący wiersz wersji i odzwierciedla stan elementu członkowskiego wymiaru w systemie źródłowym. Historyczne atrybuty śledzenia w tabeli wymiarów przechowują wartości, które umożliwiają identyfikację bieżącej wersji (bieżąca flaga to TRUE) i jej okres ważności. Klucz zastępczy jest wymagany, ponieważ podczas przechowywania wielu wersji będą zduplikowane klucze naturalne.

Jest to typowy typ scD, ale powinien być zarezerwowany dla atrybutów, które muszą zachować perspektywę historyczną.

Jeśli na przykład sprzedawca jest przypisany do innego regionu sprzedaży, zmiana typu SCD 2 obejmuje operację aktualizacji i operację wstawiania.

1. Operacja aktualizacji zastępuje bieżącą wersję, aby ustawić atrybuty śledzenia historycznego. W szczególności kolumna zakończenia ważności jest ustawiona na datę przetwarzania ETL (lub odpowiedni znacznik czasu w systemie źródłowym), a bieżąca flaga ma wartość FALSE.
2. Operacja wstawiania dodaje nową, bieżącą wersję, ustawiając kolumnę ważności rozpoczęcia na wartość kolumny zakończenia ważności (używaną do aktualizacji poprzedniej wersji) i bieżącą flagę na TRUE.

Ważne jest, aby zrozumieć, że stopień szczegółowości powiązanych tabel faktów nie jest na poziomie sprzedawcy, ale raczej na poziomie wersji sprzedawcy. Zestawienie historycznych wyników sprzedaży w regionie spowoduje wygenerowanie poprawnych wyników, ale będą dostępne dwie (lub więcej) wersji elementów członkowskich do przeanalizowania.

Na poniższym diagramie przedstawiono stan przed i po elemencie członkowskim wymiaru sprzedawcy, w którym zmienił się ich region sprzedaży. Ponieważ organizacja chce analizować nakład pracy sprzedawców według regionu, do którego są przypisani, wyzwala zmianę typu SCD 2.

Napiwek

Jeśli tabela wymiarów obsługuje zmiany typu SCD 2, należy dołączyć atrybut etykiety opisujący element członkowski i wersję. Rozważmy przykład, gdy sprzedawca Lynn Tsoflias z Adventure Works zmienia przypisanie z regionu sprzedaży Australii do regionu sprzedaży Zjednoczonego Królestwa. Atrybut etykiety dla pierwszej wersji może odczytać "Lynn Tsoflias (Australia)" i atrybut etykiety dla nowej, bieżącej wersji może odczytać "Lynn Tsoflias (Wielka Brytania)." Jeśli jest to przydatne, możesz również uwzględnić daty ważności w etykiecie.

Należy zrównoważyć potrzebę historycznej dokładności w porównaniu z użytecznością i wydajnością. Spróbuj uniknąć zbyt wielu zmian typu SCD w tabeli wymiarów, ponieważ może to spowodować przytłaczającą liczbę wersji, które mogą utrudnić analitykom zrozumienie.

Ponadto zbyt wiele wersji może wskazywać, że zmiana atrybutu może być lepiej przechowywana w tabeli faktów. Rozszerzenie wcześniejszego przykładu, jeśli zmiany w regionie sprzedaży były częste, region sprzedaży może być przechowywany jako klucz wymiaru w tabeli faktów zamiast implementowania typu SCD 2.

Rozważ następujące atrybuty śledzenia historycznego typu SCD 2.

CREATE TABLE d_Salesperson
(
    <…>

    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,

    <…>
);

Oto cele atrybutów śledzenia historycznego.

• Kolumna RecChangeDate_FK przechowuje datę wprowadzenia zmiany. Umożliwia wykonywanie zapytań w przypadku wprowadzenia zmian.
• Kolumny RecValidFromKey i RecValidToKey przechowują daty ważności dla wiersza. Rozważ zapisanie najwcześniejszej daty znalezionej w wymiarze daty, aby RecValidFromKey reprezentować wersję początkową i zapisać 01/01/9999 dla RecValidToKey bieżących wersji.
• Kolumna jest opcjonalna RecReason . Umożliwia dokumentowanie przyczyny wstawienia wersji. Może kodować, które atrybuty uległy zmianie, lub może to być kod z systemu źródłowego, który określa konkretną przyczynę biznesową.
• Kolumna RecIsCurrent umożliwia pobranie tylko bieżących wersji. Jest używany, gdy proces ETL wyszukuje klucze wymiarów podczas ładowania tabel faktów.

Uwaga

Niektóre systemy źródłowe nie przechowują zmian historycznych, dlatego ważne jest, aby wymiar był przetwarzany regularnie w celu wykrywania zmian i implementowania nowych wersji. Dzięki temu można wykrywać zmiany wkrótce po ich wystąpieniu, a ich daty ważności będą dokładne.

Typ SCD 3

Typ SCD 3 zmiany śledzą ograniczoną historię z atrybutami. Takie podejście może być przydatne w przypadku konieczności zarejestrowania ostatniej zmiany lub kilku najnowszych zmian.

Ten typ SCD zachowuje ograniczoną perspektywę historyczną. Może to być przydatne, gdy powinny być przechowywane tylko początkowe i bieżące wartości. W tym przypadku tymczasowe zmiany nie byłyby wymagane.

Jeśli na przykład sprzedawca jest przypisany do innego regionu sprzedaży, zmiana typu SCD 3 zastępuje wiersz wymiaru. Kolumna, która w szczególności przechowuje poprzedni region sprzedaży, jest ustawiona jako poprzedni region sprzedaży, a nowy region sprzedaży jest ustawiony jako bieżący region sprzedaży.

Na poniższym diagramie przedstawiono stan przed i po elemencie członkowskim wymiaru sprzedawcy, w którym zmienił się ich region sprzedaży. Ponieważ organizacja chce określić dowolne poprzednie przypisanie regionu sprzedaży, wyzwala zmianę typu SCD 3.

Specjalne elementy członkowskie wymiaru

Możesz wstawić wiersze do wymiaru reprezentującego brakujące, nieznane, N/A lub stany błędu. Możesz na przykład użyć następujących wartości klucza zastępczego.

Wartość klucza	Przeznaczenie
0	Brak (niedostępne w systemie źródłowym)
-1	Nieznany (błąd wyszukiwania podczas ładowania tabeli faktów)
-2	Nie dotyczy (nie dotyczy)
-3	Błąd

Kalendarz i godzina

Niemal bez wyjątku tabele faktów przechowują miary w określonych punktach w czasie. Aby obsługiwać analizę według daty (i ewentualnie godziny), muszą istnieć wymiary kalendarza (daty i godziny).

Rzadko zdarza się, że system źródłowy będzie miał dane wymiarów kalendarza, więc musi zostać wygenerowany w magazynie danych. Zazwyczaj jest generowany raz, a jeśli jest to wymiar kalendarza, jest on rozszerzony o przyszłe daty w razie potrzeby.

Wymiar daty

Wymiar daty (lub kalendarza) jest najczęstszym wymiarem używanym do analizy. Przechowuje jeden wiersz na datę i obsługuje typowe wymaganie filtrowania lub grupowania według określonych okresów dat, takich jak lata, kwartały lub miesiące.

Ważne

Wymiar daty nie powinien zawierać ziarna, które rozciąga się na godzinę dnia. Jeśli wymagana jest analiza godzin dnia, należy mieć wymiar daty i wymiar godziny (opisany w dalszej części). Tabele faktów przechowujące czas dnia fakty powinny mieć dwa klucze obce, jeden do każdego z tych wymiarów.

Klucz naturalny wymiaru daty powinien używać typu danych daty . Klucz zastępczy powinien przechowywać datę przy użyciu YYYYMMDD formatu i typu danych int . Ta zaakceptowana praktyka powinna być jedynym wyjątkiem (obok wymiaru czasu), gdy wartość klucza zastępczego ma znaczenie i jest czytelna dla człowieka. Przechowywanie YYYYMMDD jako typ danych int jest nie tylko wydajne i sortowane numerycznie, ale także jest zgodne z jednoznacznym formatem daty Międzynarodowej Organizacji Standardów (ISO) 8601.

Poniżej przedstawiono niektóre typowe atrybuty do uwzględnienia w wymiarze daty.

• Year, , Quarter, , MonthDay
• QuarterNumberInYear, MonthNumberInYear — które mogą być wymagane do sortowania etykiet tekstowych.
• FiscalYear, FiscalQuarter — niektóre harmonogramy księgowości korporacyjnej zaczynają się w połowie roku, tak aby początek/koniec roku kalendarzowego i rok obrachunkowy był inny.
• FiscalQuarterNumberInYear, FiscalMonthNumberInYear — które mogą być wymagane do sortowania etykiet tekstowych.
• WeekOfYear — istnieje wiele sposobów etykietowania tygodnia roku, w tym standard ISO, który ma 52 lub 53 tygodnie.
• IsHoliday, HolidayText — jeśli organizacja działa w wielu lokalizacjach geograficznych, należy zachować wiele zestawów list świątecznych, które każda lokalizacja geograficzna obserwuje jako oddzielny wymiar lub naturalizowany w wielu atrybutach w wymiarze daty. Dodanie atrybutu może pomóc w zidentyfikowaniu HolidayText dni wolnych na potrzeby raportowania.
• IsWeekday – podobnie w niektórych lokalizacjach geograficznych standardowy tydzień roboczy nie jest od poniedziałku do piątku. Na przykład tydzień roboczy to niedziela do czwartku w wielu regionach Bliskiego Wschodu, podczas gdy inne regiony zatrudniają czterodniowy lub sześciodniowy tydzień roboczy.
• LastDayOfMonth
• RelativeYearOffset, , RelativeQuarterOffset, RelativeDayOffset RelativeMonthOffset— które mogą być wymagane do obsługi filtrowania dat względnych (na przykład poprzedniego miesiąca). Bieżące okresy używają przesunięcia zera (0); poprzednie okresy przechowują przesunięcia -1, -2, -3...; przyszłe okresy przechowywania przesunięcia 1, 2, 3....

Podobnie jak w przypadku każdego wymiaru, ważne jest, aby zawierać atrybuty, które obsługują znane wymagania dotyczące filtrowania, grupowania i hierarchii. Mogą również istnieć atrybuty, które przechowują tłumaczenia etykiet w innych językach.

Gdy wymiar jest używany do powiązania z faktami o wyższym stopniu szczegółowości, tabela faktów może użyć pierwszej daty okresu daty. Na przykład tabela faktów docelowych sprzedaży, która przechowuje kwartalne cele sprzedawców, przechowuje pierwszą datę kwartału w wymiarze daty. Alternatywną metodą jest utworzenie kolumn kluczy w tabeli dat. Na przykład klucz kwartału może przechowywać klucz kwartału przy użyciu YYYYQ formatu i małego typu danych.

Wymiar powinien zostać wypełniony znanym zakresem dat używanych przez wszystkie tabele faktów. Powinna również zawierać przyszłe daty, gdy magazyn danych przechowuje fakty dotyczące celów, budżetów lub prognoz. Podobnie jak w przypadku innych wymiarów, możesz uwzględnić wiersze reprezentujące brakujące, nieznane, N/A lub sytuacje błędów.

Napiwek

Wyszukaj w Internecie ciąg "generator wymiarów dat", aby znaleźć skrypty i arkusze kalkulacyjne, które generują dane daty.

Zazwyczaj na początku następnego roku proces ETL powinien rozszerzyć wiersze wymiarów daty na określoną liczbę lat. Gdy wymiar 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).

Wymiar czasu

Czasami fakty muszą być przechowywane w określonym momencie (tak jak w porze dnia). W takim przypadku utwórz wymiar czasu (lub zegara). Może mieć ziarno minut (24 x 60 = 1440 wierszy), a nawet sekundy (24 x 60 x 60 = 86 400 wierszy). Inne możliwe ziarna obejmują pół godziny lub godzinę.

Klucz naturalny wymiaru czasu powinien używać typu danych czasu . Klucz zastępczy może używać odpowiedniego formatu i przechowywać wartości, które mają znaczenie i są czytelne dla człowieka, na przykład przy użyciu HHMM formatu lub HHMMSS .

Poniżej przedstawiono niektóre typowe atrybuty, które mają być uwzględniane w wymiarze czasu.

• Hour, , HalfHour, , QuarterHourMinute
• Etykiety okresów czasowych (rano, popołudnie, wieczór, noc)
• Nazwy zmian pracy
• Flagi szczytowe lub poza szczytem

Zgodne wymiary

Niektóre wymiary mogą być zgodne z wymiarami. Zgodne wymiary odnoszą się do wielu tabel faktów, dlatego są one współużytkowane przez wiele gwiazd w modelu wymiarowym. Zapewniają one spójność i mogą pomóc w zmniejszeniu ciągłego rozwoju i konserwacji.

Na przykład tabele faktów przechowują co najmniej jeden klucz wymiaru daty (ponieważ działanie jest prawie zawsze rejestrowane według daty i/lub godziny). Z tego powodu wymiar daty jest wspólnym wymiarem zgodnym. Dlatego należy upewnić się, że wymiar daty zawiera atrybuty istotne dla analizy wszystkich tabel faktów.

Na poniższym diagramie przedstawiono tabelę faktów Sales i tabelę faktów Inventory . Każda tabela faktów odnosi się do Date wymiaru i Product wymiaru, które są zgodne z wymiarami.

W innym przykładzie pracownik i użytkownicy mogą być tym samym zestawem osób. W takim przypadku warto połączyć atrybuty każdej jednostki w celu utworzenia jednego zgodnego wymiaru.

Wymiary związane z pełnieniem ról

Gdy wymiar jest przywoływanych wiele razy w tabeli faktów, jest znany jako wymiar odgrywania roli.

Na przykład gdy tabela faktów sprzedaży zawiera klucze wymiarów daty zamówienia, daty wysyłki i daty dostawy, wymiar daty jest powiązany na trzy sposoby. Każdy sposób reprezentuje odrębną rolę, ale istnieje tylko jeden wymiar daty fizycznej.

Na poniższym diagramie przedstawiono tabelę Flight faktów. Wymiar Airport jest wymiarem odgrywającym rolę, ponieważ jest on powiązany dwa razy z tabelą faktów jako Departure Airport wymiar i Arrival Airport wymiar.

Wymiary śmieci

Wymiar wiadomości-śmieci jest przydatny, gdy istnieje wiele niezależnych wymiarów, zwłaszcza gdy składają się one z kilku atrybutów (być może jeden), a gdy te atrybuty mają niską kardynalność (kilka wartości). Celem wymiaru śmieci jest skonsolidowanie wielu małych wymiarów w jeden wymiar. Takie podejście projektowe może zmniejszyć liczbę wymiarów i zmniejszyć liczbę kluczy tabel faktów, a tym samym rozmiar magazynu tabel faktów. Pomagają one również zmniejszyć ilość niepotrzebnych okienek danych, ponieważ udostępniają użytkownikom mniej tabel.

Tabela wymiarów śmieci zwykle przechowuje kartezjański produkt wszystkich wartości atrybutów wymiarów z atrybutem klucza zastępczego.

Dobrzy kandydaci obejmują flagi i wskaźniki, stan zamówienia i stany demograficzne klientów (płeć, grupa wiekowa i inne).

Na poniższym diagramie przedstawiono wymiar wiadomości-śmieci o nazwie Sales Status , który łączy wartości stanu zamówienia i wartości stanu dostawy.

Zdegeneruj wymiary

Wymiar degeneracji może wystąpić, gdy wymiar znajduje się w tym samym ziarnie co powiązane fakty. Typowym przykładem degeneracji wymiaru jest wymiar numeru zamówienia sprzedaży, który odnosi się do tabeli faktów sprzedaży. Zazwyczaj numer faktury jest pojedynczym, nie hierarchicznym atrybutem w tabeli faktów. Jest to więc akceptowana praktyka, aby nie kopiować tych danych w celu utworzenia oddzielnej tabeli wymiarów.

Na poniższym diagramie przedstawiono Sales Order wymiar, który jest wymiarem degeneracyjnym na SalesOrderNumber podstawie kolumny w tabeli faktów sprzedaży. Ten wymiar jest implementowany jako widok, który pobiera odrębne wartości numeru zamówienia sprzedaży.

Napiwek

Istnieje możliwość utworzenia widoku w magazynie sieci szkieletowej, który przedstawia wymiar degeneracji jako wymiar na potrzeby wykonywania zapytań.

Z perspektywy modelowania semantycznego usługi Power BI można utworzyć zdegenerowany wymiar jako oddzielną tabelę przy użyciu dodatku Power Query. W ten sposób model semantyczny jest zgodny z najlepszymi rozwiązaniami, które pola używane do filtrowania lub grupowania pochodzą z tabel wymiarów, a pola używane do podsumowywania faktów pochodzą z tabel faktów.

Wymiary platformy pomocniczej

Gdy tabela wymiarów odnosi się do innych tabel wymiarów, jest znana jako wymiar tabeli pomocniczej. Wymiar platformy pomocniczej może pomóc w dopasowaniu i ponownym użyciu definicji w modelu wymiarowym.

Można na przykład utworzyć wymiar geograficzny, który przechowuje lokalizacje geograficzne dla każdego kodu pocztowego. Ten wymiar może być następnie przywołyny przez wymiar klienta i wymiar sprzedawcy, który będzie przechowywać klucz zastępczy wymiaru geograficznego. Dzięki temu klienci i sprzedawcy mogą być następnie analizowani przy użyciu spójnych lokalizacji geograficznych.

Na poniższym diagramie przedstawiono Geography wymiar, który jest wymiarem outrigger. Nie odnosi się bezpośrednio do tabeli faktów Sales . Zamiast tego jest on powiązany pośrednio za pośrednictwem Customer wymiaru i Salesperson wymiaru.

Należy wziąć pod uwagę, że wymiar daty można użyć jako wymiaru pomocniczego, gdy inne atrybuty tabeli wymiarów przechowują daty. Na przykład data urodzenia w wymiarze klienta może być przechowywana przy użyciu klucza zastępczego tabeli wymiarów daty.

Wymiary wielowartościowe

Gdy atrybut wymiaru musi przechowywać wiele wartości, należy zaprojektować wymiar wielowartościowy. Zaimplementujesz wielowartościowy wymiar, tworząc tabelę mostka (czasami nazywaną tabelą sprzężenia). Tabela mostka przechowuje relację wiele do wielu między jednostkami.

Rozważmy na przykład wymiar sprzedawcy i że każdy sprzedawca jest przypisany do co najmniej jednego regionu sprzedaży. W takim przypadku warto utworzyć wymiar regionu sprzedaży. Ten wymiar przechowuje każdy region sprzedaży tylko raz. Oddzielna tabela, znana jako tabela mostka, przechowuje wiersz dla każdej relacji sprzedawcy i regionu sprzedaży. Fizycznie istnieje relacja jeden do wielu z wymiaru sprzedawcy do tabeli mostka, a druga relacja jeden do wielu z wymiaru regionu sprzedaży do tabeli mostka. Logicznie istnieje relacja wiele do wielu między sprzedawcami i regionami sprzedaży.

Na poniższym diagramie Account tabela wymiarów jest powiązana z tabelą faktów Transaction . Ponieważ klienci mogą mieć wiele kont i kont, mogą mieć wielu klientów, Customer tabela wymiarów jest powiązana za pośrednictwem tabeli mostka Customer Account .

Powiązana zawartość

W następnym artykule z tej serii dowiesz się więcej o wskazówkach i projektowaniu najlepszych rozwiązań dotyczących tabel faktów.