Relacje między tabelami w modelu danych

Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Przeglądarka nie obsługuje klipu wideo. Instalowanie dodatku Microsoft Silverlight, Adobe Flash Player lub programu Internet Explorer 9.

Dodając relacje amogn różne tabele, możesz dodać więcej energii do analizy danych. Relacja to połączenie między dwiema tabelami, które zawierają dane: jedna kolumna w każdej tabeli stanowi 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żesz śledzić wszystkie dane w jednej tabeli, które mają taką strukturę:

CustomerID

Nazwa

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 — utworzonym wprost lub w programie Excel automatycznie tworzonym w Twoim imieniu podczas 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. Na przykład tabelę klienci można powiązać z tabelą zamówienia , jeśli każda z nich zawiera kolumnę, w której jest przechowywany Identyfikator 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 jest zazwyczaj kolumną z właściwościami specjalnymi. 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 dla naszego celu w tym celu:

  • Klucz podstawowy: unikatowa identyfikacja wiersza w tabeli, takiego jak IDKlienta w tabeli klienci .

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

  • Klucz obcy: kolumna odwołująca się do unikatowej kolumny w innej tabeli, takiej jak IDKlienta w tabeli Orders , która odwołuje się do elementu CustomerID w tabeli Customers.

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 relacja zostanie zdefiniowana między IDKlienta w tabeli Orders (kolumny) i IDKlienta w tabeli Customers (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 jest relacją typu jedno-do-wielu. Każdy klient może mieć wiele zamówień, ale zamówienie może dotyczyć tylko jednego klienta. Inna ważna relacja między tabelami to jeden-do-jednego. W naszym przykładzie w tabeli CustomerDiscounts , która definiuje jedną stawkę dyskontową dla każdego klienta, ma relację jeden-do-jednego z tabelą Klienci.

W poniższej tabeli przedstawiono relacje między tymi trzema tabelami (Klienci, CustomerDiscountsi 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

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 relacji program Excel musi zwykle 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 informacji, zobacz ponowne obliczanie formuł.

W modelu danych może istnieć wiele relacji między dwiema tabelami. Aby zbudować dokładne obliczenia, program Excel wymaga pojedynczej ścieżki z jednej tabeli do następnej. Dlatego w danej chwili między każdą parą tabel może istnieć tylko jedna aktywna relacja. Chociaż inne osoby są nieaktywne, możesz określić nieaktywną relację w formułach i kwerendach.

W widoku Diagram aktywna relacja jest linią ciągłą, a nieaktywne są liniami kreskowanymi. Na przykład w AdventureWorksDW2012 tabela tabeli wymiardata zawiera kolumnę, kluczdaty, która jest powiązana z trzema kolumnami w tabeli FactInternetSales: DataZamówienia, wyrażeniui DataWysyłki. 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 zawierać dokładnie jedną kolumnę, która jednoznacznie identyfikuje poszczególne wiersze w tabeli. Jeśli importujesz tabele, które mają istniejącą relację na podstawie klucza złożonego, 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żesz to zrobić przed zaimportowaniem danych lub utworzeniem 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 istnieją odmiany w nazwach kolumn zawierających zasadniczo te same dane: EMP ID, IDPracownika, identyfikator pracownika, emp_iditd. 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.

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?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×