Relacje między tabelami w modelu danych

Przeglądarka nie obsługuje klipu wideo.

Tworzenie relacji z różnymi tabelami w celu większej możliwości analizy danych. Relacja to połączenie pomiędzy dwiema tabelami, które zawierają dane: jedna kolumna w każdej tabeli jest podstawą relacji. Aby zrozumieć, dlaczego relacje są użyteczne, można wyobrazić sobie śledzenie danych dotyczących zamówień składanych przez klientów. Można śledzić wszystkie dane w jednej tabeli o strukturze podobnej do poniższej:

CustomerID

Nazwisko

Adres e-mail

Stawka_rabatu

ID_zamówienia

Data_zamówienia

Produkt

Ilość

1

Janiszewski

krzysztof.janiszewski@contoso.com

0,05

256

2010-01-07

Kompaktowy aparat cyfrowy

11

1

Janiszewski

krzysztof.janiszewski@contoso.com

0,05

255

2010-01-03

Lustrzanka jednoobiektywowa

15

2

Jaworski

michal.jaworski@contoso.com

0,10

254

2010-01-03

Budżetowa kamera wideo

27

Takie podejście może się sprawdzić, ale wymaga przechowywania dużej ilości nadmiarowych danych, takich jak adres e-mail klienta, dla każdego zamówienia. Samo przechowywanie nie jest kosztowne, ale w przypadku zmiany adresu e-mail należy pamiętać o aktualizacji każdego wiersza skojarzonego z danym klientem. Jednym z rozwiązań tego problemu jest podzielenie danych na wiele tabel i zdefiniowanie relacji między tymi tabelami. Jest to podejście używane w relacyjnych bazach danych, takich jak bazy danych programu SQL Server. Na przykład importowana baza danych może przedstawiać dane dotyczące zamówień przy użyciu trzech powiązanych tabel:

Klienci

[ID_klienta]

Nazwisko

Adres e-mail

1

Janiszewski

krzysztof.janiszewski@contoso.com

2

Jaworski

michal.jaworski@contoso.com

Rabaty_dla_klientów

[ID_klienta]

Stawka_rabatu

1

0,05

2

0,10

Zamówienia

[ID_klienta]

ID_zamówienia

Data_zamówienia

Produkt

Ilość

1

256

2010-01-07

Kompaktowy aparat cyfrowy

11

1

255

2010-01-03

Lustrzanka jednoobiektywowa

15

2

254

2010-01-03

Budżetowa kamera wideo

27

Relacje istnieją w modelu danych — jawnie tworzonej lub automatycznie tworzonej Excel w Twoim imieniu podczas jednoczesnego importowania wielu tabel. Do tworzenia modelu lub zarządzania nim można także użyć dodatku Power Pivot. Aby uzyskać szczegółowe informacje, zobacz Tworzenie modelu danych w programie Excel.

Jeśli tabele są importowane z jednej bazy danych za pomocą dodatku Power Pivot, dodatek Power Pivot może wykryć relacje między tabelami na podstawie kolumn ujętych w [nawiasy kwadratowe] i odtworzyć te relacje w modelu danych, który jest tworzony w tle. Aby uzyskać więcej informacji, zobacz sekcję Automatyczne wykrywanie i wnioskowanie relacji w tym artykule. W przypadku importowania tabel z wielu źródeł można ręcznie tworzyć relacje, tak jak opisano w temacie Tworzenie relacji między dwiema tabelami.

Relacje są oparte na kolumnach w każdej tabeli, które zawierają te same dane. Można na przykład 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.

Istnieje wiele typów kluczy, ale oto najważniejsze z naszego celu:

  • Klucz podstawowy: jednoznacznie określa wiersz w tabeli, na przykład identyfikator Klienta wtabeli Klienci.

  • Klucz alternatywny (klucz kandydowania): 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, która odwołuje się do unikatowej kolumny w innej tabeli, na przykład KOLUMNY Klienta w tabeli Zamówienia, która odwołuje się do kolumny IDENTYFIKATOR Klienta 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 tym przykładzie należy zdefiniować relację między kolumną CUSTOMERID w tabeli Zamówienia (kolumna) a kolumną CUSTOMERID w tabeli Klienci (kolumnie 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. Inną ważną relacją pomiędzy tabelami jest relacja jeden-do-jednego. W tym przykładzie tabela Rabaty Klienta, w której zdefiniowano jedną stawkę rabatu dla każdego klienta, ma relację jeden-do-jednego z tabelą Klienci.

W poniższej tabeli przedstawiono relacje między trzema tabelami(Customers, CustomerDiscountsi Orders):

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

Uwaga: Model danych nie obsługuje relacji wiele-do-wielu. Przykładem relacji wiele-do-wielu jest bezpośrednia relacja między tabelami Produkty i Klienci, w której klient może kupić wiele produktów, a jeden produkt może zostać kupiony przez wielu klientów.

Po utworzeniu jakiejkolwiek relacji Excel oblicz ponownie wszystkie formuły, w których są używane kolumny 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. Do tworzenia dokładnych obliczeń Excel jednej ścieżki od jednej tabeli do drugiej. Dlatego w danej chwili między każdą parą tabel może istnieć tylko jedna aktywna relacja. Mimo że pozostałe relacje są nieaktywne, możesz określić nieaktywną relację w formułach i zapytaniach.

W widoku diagramu aktywna relacja jest linią ciągłą, a nieaktywne relacje są liniami przerywaymi. 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, DueDatei 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 Temat 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ę jednoznacznie identyfikującą 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 ignoruje 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 przez utworzenie kolumny obliczeniowej 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 przypadku ustawienia biznesowego często są używane odmiany nazw kolumn, które w zasadzie zawierają te same dane: Id pracownika,ID pracownika, EMP_ID itp. 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.

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

×