Relacje między tabelami w modelu danych

Przeglądarka nie obsługuje klipu wideo. Instalowanie dodatku Microsoft Silverlight, Adobe Flash Player lub programu Internet Explorer 9.
Klip wideo: relacje w programie Power View i dodatku PowerPivot

Utworzenie relacji między danymi z różnych tabel zwiększa możliwości wykonywanej analizy danych. Relacja to połączenie tworzone między dwiema tabelami danych, oparte na jednej kolumnie z każdej tabeli. 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, która ma strukturę podobną do poniższej:

ID_klienta

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 utworzonym przez użytkownika lub utworzonym przez program Excel podczas równoczesnego 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.

Początek strony

W tym artykule

Kolumny i klucze

Typy relacji

Relacje a wydajność

Wiele relacji między tabelami

Wymagania dotyczące relacji tabeli

Elementy nieobsługiwane w relacji tabeli

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

Relacje wiele-do-wielu

Samosprzężenia i pętle

Automatyczne wykrywanie i wnioskowanie relacji w dodatku PowerPivot

Automatyczne wykrywanie w przypadku nazwanych zestawów

Wnioskowanie relacji

Kolumny i klucze

Relacje są oparte na kolumnach w każdej tabeli, które zawierają te same dane. Na przykład tabele Klienci i Zamówienia mogą być powiązane ze sobą, ponieważ obie zawierają kolumnę, w której są przechowywane identyfikatory użytkowników. 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, które zazwyczaj są po prostu kolumnami 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.

Z naszego punktu widzenia najbardziej interesujące są następujące klucze:

  • Klucz podstawowy: jednoznacznie określa wiersz w tabeli, taki jak ID_klienta w tabeli Klienci.

  • Klucz alternatywny (klucz kandydacki): 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ąca się do unikatowej kolumny w innej tabeli, taka jak ID_klienta w tabeli Zamówienia, która odwołuje się do kolumny ID_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 omawianym przykładzie można zdefiniować relację między kolumną ID_klienta w tabeli Zamówienia (kolumna) i kolumną ID_klienta (kolumna odnośnika) w tabeli Klienci. 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.

Typy relacji

Relacja między tabelami Klienci i Zamówienia jest relacją jeden-do-wielu. Każdy klient może mieć wiele zamówień, ale zamówienie może dotyczyć tylko jednego klienta. Inne typy relacji to jeden-do-jednego i wiele-do-wielu. Tabela Rabaty_dla_klientów, w której zdefiniowano jedną stawkę rabatu dla każdego klienta, ma relację jeden-do-jednego z tabelą Klienci.

W poniższej tabeli pokazano relacje między trzema tabelami:

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.

Relacje a wydajność

Po utworzeniu jakiejkolwiek relacji program Excel musi w normalnych warunkach ponownie obliczyć wszystkie formuły, w których są używane kolumny z tabel uwzględnionych w nowo utworzonej relacji. Przetwarzanie może trwać jakiś czas, zależnie do ilości danych i złożoności relacji. Zobacz temat Ponowne obliczanie formuł, aby uzyskać szczegółowe informacje.

Wiele relacji między tabelami

W modelu danych może istnieć wiele relacji między dwiema tabelami. Do przeprowadzania dokładnych obliczeń 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. Inne relacje są nieaktywne, ale można określić nieaktywną relację w formułach i zapytaniach. W widoku diagramu aktywna relacja jest przedstawiona za pomocą linii ciągłej, a nieaktywne relacje za pomocą linii przerywanych. Na przykład w bazie danych 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.

Początek strony

Wymagania dotyczące relacji tabeli

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 ciągi puste są równoważne wartości pustej (blank), która jest odrębną 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 dotyczących typów danych, zobacz temat Typy danych obsługiwane w modelach danych.

Elementy nieobsługiwane w relacji tabeli

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; zawsze musi istnieć dokładnie jedna kolumna, która jednoznacznie określa każdy wiersz w tabeli. Podczas importowania tabel mających 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 albo 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.

Początek strony

Automatyczne wykrywanie i wnioskowanie relacji w dodatku Power Pivot

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 środowisku firmowym często są używane odmiany nazw kolumn, które w istocie zawierają takie same dane: ID pracownika, PracownikID, PRAC_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.

Początek strony

Rozwijaj swoje umiejętności
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.

×