Relacje między tabelami w modelu danych

Relacje są oparte na kolumnach w każdej tabeli, które zawierają te same dane. Na przykład można powiązać tabelę Klienci z tabelą Zamówienia , jeśli każda z nich zawiera kolumnę z identyfikatorem klienta. W tym przykładzie nazwy kolumn są takie same, ale nie jest to wymagane. Jedna kolumna może mieć nazwę ID_klienta, a druga NumerKlienta, o ile wszystkie wiersze w tabeli Zamówienia zawierają identyfikator przechowywany także w tabeli Klienci.

W relacyjnej bazie danych istnieje kilka typów kluczy. Klucz to zazwyczaj kolumna o specjalnych właściwościach. Znajomość przeznaczenia każdego klucza pomoże w zarządzaniu wielotabelowym modelem danych, który dostarcza dane do tabeli przestawnej, wykresu przestawnego lub raportu programu Power View.

Chociaż istnieje wiele typów kluczy, są one najważniejsze w naszym celu tutaj:

• Klucz podstawowy: jednoznacznie identyfikuje wiersz w tabeli, taki jak IdentyfikatorKlienta w tabeli Klienci .

• Klucz alternatywny (lub klucz kandydujący): unikatowa kolumna inna niż klucz podstawowy. Na przykład tabela Pracownicy może zawierać identyfikator pracownika i numer PESEL, a oba te numery są unikatowe.

• Klucz obcy: kolumna odwołujące się do unikatowej kolumny w innej tabeli, na przykład IdentyfikatorKlienta w tabeli Zamówienia , która odwołuje się do pola IdentyfikatorKlienta w tabeli Klienci.

W modelu danych klucz podstawowy lub alternatywny jest nazywany powiązaną kolumną. Jeśli tabela zawiera zarówno klucz podstawowy, jak i alternatywny, jako podstawy relacji tabeli można użyć dowolnego z tych kluczy. Klucz obcy jest również nazywany kolumną źródłową lub po prostu kolumną. W naszym przykładzie można zdefiniować relację między kolumnami ID_klienta w tabeli Zamówienia (kolumna) i ID_klienta w tabeli Klienci (kolumna odnośnika). W przypadku importowania danych z relacyjnej bazy danych program Excel domyślnie wybiera klucz obcy z jednej tabeli i odpowiadający mu klucz podstawowy z innej tabeli. Jednak jako kolumny odnośnika można użyć dowolnej kolumny zawierającej unikatowe wartości.

Relacja między klientem a zamówieniem to relacja jeden-do-wielu. Każdy klient może mieć wiele zamówień, ale zamówienie może dotyczyć tylko jednego klienta. Kolejna ważna relacja pomiędzy tabelami to jeden-do-jednego. W naszym przykładzie tabela CustomerDiscounts , definiująca pojedynczą stopę dyskontową dla każdego klienta, ma relację jeden-do-jednego z tabelą Klienci.

W poniższej tabeli przedstawiono relacje między trzema tabelami (Klienci, CustomerDiscounts i Zamówienia):

Relacja	Typ	Kolumna odnośnika	Kolumna
Klienci-Rabaty_dla_klientów	jeden-do-jednego	Klienci.ID_klienta	Rabaty_dla_klientów.ID_klienta
Klienci-Zamówienia	jeden-do-wielu	Klienci.ID_klienta	Zamówienia.ID_klienta

Po utworzeniu jakiejkolwiek relacji program Excel musi zazwyczaj ponownie obliczyć wszystkie formuły używające kolumn z tabel w nowo utworzonej relacji. Przetwarzanie może trwać jakiś czas, zależnie do ilości danych i złożoności relacji. Aby uzyskać więcej szczegółowych informacji, zobacz Ponowne obliczanie formuł.

W modelu danych może istnieć wiele relacji między dwiema tabelami. Aby utworzyć dokładne obliczenia, program Excel potrzebuje jednej ścieżki od jednej tabeli do następnej. Dlatego w danej chwili między każdą parą tabel może istnieć tylko jedna aktywna relacja. Pozostałe są nieaktywne, ale można określić nieaktywną relację w formułach i zapytaniach.

W widoku diagramu aktywna relacja to linia ciągła, a nieaktywne są liniami kreskowanymi. Na przykład w tabeli AdventureWorksDW2012 tabela DimDate zawiera kolumnę DateKey, która jest powiązana z trzema różnymi kolumnami w tabeli FactInternetSales: OrderDate, DueDate i ShipDate. Jeśli aktywna jest relacja między kolumnami DateKey i OrderDate, będzie ona domyślnie używana w formułach, chyba że użytkownik określi inaczej.

Relację można utworzyć, gdy są spełnione następujące wymagania:

Kryterium	Opis
Unikatowy identyfikator każdej tabeli	Każda tabela musi mieć jedną kolumnę jednoznacznie identyfikującą każdy wiersz w tabeli. Ta kolumna jest często nazywana kluczem podstawowym.
Unikatowe kolumny odnośników	Wartości danych w kolumnie odnośnika muszą być unikatowe. Innymi słowy, kolumna nie może zawierać duplikatów. W modelu danych wartości null i puste ciągi są równoważne pustym wartościom, które są traktowane jako odrębne wartości danych. Oznacza to, że kolumna odnośnika nie może zawierać wielu wartości null.
Zgodne typy danych	Typy danych w kolumnie źródłowej i kolumnie odnośnika muszą być zgodne. Aby uzyskać więcej informacji na temat typów danych, zobacz Typy danych obsługiwane w modelach danych.

W modelu danych nie można utworzyć relacji tabeli, jeśli klucz jest kluczem złożonym. Można także tworzyć wyłącznie relacje jeden-do-jednego i jeden-do-wielu. Inne typy relacji są nieobsługiwane.

Klucze złożone i kolumny odnośników

Klucz złożony to klucz składający się z kilku kolumn. W modelach danych nie można używać kluczy złożonych: tabela musi zawsze mieć dokładnie jedną kolumnę, która jednoznacznie identyfikuje każdy wiersz w tabeli. W przypadku importowania tabel, które mają istniejącą relację opartą na kluczu złożonym, Kreator importu tabeli w dodatku Power Pivot zignoruje tę relację, ponieważ nie można jej utworzyć w modelu.

Aby utworzyć relację między dwiema tabelami, które zawierają wiele kolumn określających klucze podstawowe i obce, przed utworzeniem relacji należy połączyć wartości w celu utworzenia jednej kolumny klucza. Można to zrobić przed zaimportowaniem danych lub tworząc kolumnę obliczeniową w modelu danych przy użyciu dodatku Power Pivot.

Relacje wiele-do-wielu

Model danych nie może zawierać relacji wiele-do-wielu. Nie można po prostu dodać tabel skrzyżowań w modelu. Można jednak używać funkcji języka DAX w celu modelowania relacji wiele-do-wielu.

Samosprzężenia i pętle

W przypadku modelu danych samosprzężenia są niedozwolone. Samosprzężenie to relacja cykliczna między tabelą i nią samą. Samosprzężenia są często używane do definiowania hierarchii element nadrzędny-element podrzędny. Na przykład można połączyć tabelę Pracownicy z nią samą, aby utworzyć hierarchię pokazującą łańcuch zarządzania w firmie.

Program Excel nie zezwala na tworzenie pętli między relacjami w skoroszycie. Innymi słowy, zabroniony jest poniższy zestaw relacji.

Tabela 1, kolumna a   do   Tabela 2, kolumna f

Tabela 2, kolumna f   do   Tabela 3, kolumna n

Tabela 3, kolumna n   do   Tabela 1, kolumna a

Przy próbie utworzenia relacji, która doprowadziłaby do utworzenia pętli, jest generowany błąd.

Jedną z zalet importowania danych przy użyciu dodatku Power Pivot jest to, że czasami dodatek Power Pivot może wykrywać relacje i tworzyć nowe relacje w modelu danych, który tworzy w programie Excel.

Podczas importowania wielu tabel dodatek Power Pivot automatycznie wykrywa istniejące relacje między tabelami. Ponadto podczas tworzenia tabeli przestawnej dodatek Power Pivot analizuje dane w tabelach. Wykrywa możliwe relacje, które nie zostały zdefiniowane, i sugeruje odpowiednie kolumny, które powinny zostać uwzględnione w tych relacjach.

Algorytm wykrywania używa danych statystycznych dotyczących wartości i metadanych kolumn w celu utworzenia wniosków dotyczących prawdopodobieństwa istnienia relacji.

• Typy danych we wszystkich powiązanych kolumnach muszą być zgodne. Funkcja automatycznego wykrywania obsługuje tylko dane typu liczba całkowita i tekst. Aby uzyskać więcej informacji dotyczących typów danych, zobacz temat Typy danych obsługiwane w modelach danych.

• Aby można było pomyślnie wykryć relację, liczba unikatowych kluczy w kolumnie odnośnika musi być większa niż liczba wartości w tabeli pod stronie „wielu” relacji. Innymi słowy kolumna klucza po stronie „wielu” relacji nie może zawierać żadnych wartości, które nie znajdują się w kolumnie klucza tabeli odnośnika. Na przykład istnieje tabela, w której są wymienione produkty i ich identyfikatory (tabela odnośnika), oraz tabela sprzedaży, w której znajdują się wszystkie transakcje sprzedaży każdego z produktów (strona „wielu” relacji). Jeśli rekordy sprzedaży zawierają identyfikator produktu, któremu nie odpowiada identyfikator w tabeli produktów, nie można automatycznie utworzyć relacji, ale być może będzie można utworzyć ją ręcznie. Aby program Excel mógł wykryć tę relację, należy najpierw zaktualizować tabelę odnośnika (tabelę produktów), tak aby zawierała identyfikatory brakujących produktów.

• Należy upewnić się, że nazwa kolumny klucza po stronie „wielu” relacji jest podobna do nazwy kolumny klucza w tabeli odnośnika. Te nazwy nie muszą być dokładnie takie same. Na przykład w ustawieniach biznesowych często występują odmiany nazw kolumn zawierających zasadniczo te same dane: Identyfikator Emp, Identyfikator pracownika, Identyfikator pracownika, EMP_ID itd. Algorytm wykrywa podobne nazwy i przypisuje wyższe prawdopodobieństwo do kolumn mających podobne lub dokładnie takie same nazwy. Dlatego w celu zwiększenia prawdopodobieństwa utworzenia relacji warto zmienić nazwy kolumn w importowanych danych w taki sposób, aby były podobne do nazw kolumn w istniejących tabelach. Jeśli program Excel znajdzie wiele możliwych relacji, nie utworzy relacji.

Dzięki tym informacjom będzie wiadomo, dlaczego nie wszystkie relacje są wykrywane i w jaki sposób można zmienić metadane, takie jak nazwa pola lub typy danych, aby uzyskać lepsze wyniki automatycznego wykrywania relacji. Aby uzyskać więcej informacji, zobacz temat Rozwiązywanie problemów z relacjami.

Automatyczne wykrywanie w przypadku nazwanych zestawów

Relacje między nazwanymi zestawami a powiązanymi z nimi polami w tabeli przestawnej nie są wykrywane automatycznie. Relacje te można tworzyć ręcznie. Aby korzystać z automatycznego wykrywania relacji, należy usunąć każdy nazwany zestaw i dodać pojedyncze pola z nazwanego zestawu bezpośrednio do tabeli przestawnej.

Wnioskowanie relacji

W niektórych przypadkach relacje między tabelami są automatycznie organizowane w łańcuch. Na przykład w przypadku utworzenia relacji między pierwszymi dwoma zestawami tabel poniżej przyjmowane jest założenie, że istnieje relacja między innymi dwiema tabelami i ta relacja jest automatycznie ustanawiana.

Produkty i Kategoria — relacja utworzona ręcznie

Kategoria i Podkategoria— relacja utworzona ręcznie

Produkty i Podkategoria — relacja wywnioskowana

Aby możliwe było automatyczne łączenie relacji w łańcuch, relacje muszą wskazywać jeden kierunek, tak jak pokazano powyżej. Jeśli początkowo istniały relacje między na przykład tabelami Sprzedaż i Produkty oraz Sprzedaż i Klienci, relacja nie jest wnioskowana. Jest to spowodowane tym, że relacja między tabelami Produkty i Klienci jest relacją wiele-do-wielu.