Omówienie relacji
W tym temacie znajduje się wprowadzenie do relacji, które można definiować między tabelami w programie PowerPivot for Excel. Ten temat zawiera następujące sekcje:
Co to jest relacja?
Wymagania dotyczące relacji
Automatyczne wykrywanie i wnioskowanie relacji
Po przeczytaniu tego tematu użytkownik będzie wiedział, co to jest relacja, jakie wymagania obowiązują podczas definiowania relacji i w jaki sposób program PowerPivot for Excel może automatycznie wykrywać relacje. Jednocześnie użytkownik zapozna się z terminologią, jakiej specjaliści zajmujący się bazami danych używają w celu opisywania relacji.
Co to jest relacja?
Relacja to połączenie między dwiema tabelami danych oparte na co najmniej jednej kolumnie w każdej tabeli (dokładnie jednej kolumnie w każdej tabeli w przypadku programu PowerPivot). 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 |
Nazwa |
AdresEmail |
StawkaRabatu |
ID_zamówienia |
DataZamówienia |
Produkt |
Ilość |
|---|---|---|---|---|---|---|---|
1 |
Zubiewicz |
krzysztof.zubiewicz@contoso.com |
.05 |
256 |
2010-01-07 |
Kompaktowy aparat cyfrowy |
11 |
1 |
Zubiewicz |
krzysztof.zubiewicz@contoso.com |
.05 |
255 |
2010-01-03 |
Lustrzanka jednoobiektywowa |
15 |
2 |
Jaworski |
michal.jaworski@contoso.com |
.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 SQL Server. Na przykład baza danych importowana do programu PowerPivot for Excel może przedstawiać dane dotyczące zamówień przy użyciu trzech powiązanych tabel:
Klienci
[ID_klienta] |
Nazwa |
AdresEmail |
|---|---|---|
1 |
Zubiewicz |
krzysztof.zubiewicz@contoso.com |
2 |
Jaworski |
michal.jaworski@contoso.com |
RabatyDlaKlientów
[ID_klienta] |
StawkaRabatu |
|---|---|
1 |
.05 |
2 |
.10 |
Zamówienia
[ID_klienta] |
ID_zamówienia |
DataZamó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 |
Jeśli te tabele są importowane z jednej bazy danych, program PowerPivot może wykryć relacje między tabelami na podstawie kolumn ujętych w nawias kwadratowy i odtworzyć te relacje w oknie programu PowerPivot. Aby uzyskać więcej informacji, zobacz sekcję Automatyczne wykrywanie i wnioskowanie relacji w tym temacie. 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.
Kolumny i klucze
Relacje są oparte na kolumnach w każdej tabeli, które zawierają te same dane. Na przykład tabele Customers i Orders mogą być powiązane ze sobą, ponieważ obie zawierają kolumnę, w której są przechowywane identyfikatory klientów. W tym przykładzie nazwy kolumn są takie same, ale nie jest to wymagane. Jedna kolumna może mieć nazwę CustomerID, a druga CustomerNumber, o ile wszystkie wiersze w tabeli Orders zawierają identyfikator przechowywany także w tabeli Customers.
W relacyjnej bazie danych istnieje kilka typów kluczy, które zazwyczaj są po prostu kolumnami o specjalnych właściwościach. Z naszego punktu widzenia najbardziej interesujące są następujące cztery typy kluczy:
Klucz podstawowy: jednoznacznie określa wiersz w tabeli, taki jak CustomerID w tabeli Customers.
Klucz alternatywny (klucz kandydacki): unikatowa kolumna inna niż klucz podstawowy. Na przykład tabela Employees 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 CustomerID w tabeli Orders, która odwołuje się do kolumny CustomerID w tabeli Customers.
Klucz złożony: klucz składający się z kilku kolumn. Program PowerPivot for Excel nie obsługuje kluczy złożonych. Aby uzyskać więcej informacji, zobacz sekcję „Klucze złożone i kolumny odnośników” w tym temacie.
W programie PowerPivot for Excel klucz podstawowy lub alternatywny jest nazwany powiązaną kolumną odnośnika albo kolumną odnośnika. Jeśli tabela zawiera zarówno klucz podstawowy, jak i alternatywny, jako kolumny odnośnika 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 relację można zdefiniować między kolumną CustomerID w tabeli Orders (kolumna) i kolumną CustomerID (kolumna odnośnika) w tabeli Customers. W przypadku importowania danych z relacyjnej bazy danych program PowerPivot for 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 Customers i Orders 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 CustomerDiscounts, w której zdefiniowano jedną stawkę rabatu dla każdego klienta, ma relację jeden-do-jednego z tabelą Customers. Przykładem relacji wiele-do-wielu jest bezpośrednia relacja między tabelami Products i Customers, w której klient może kupić wiele produktów, a jeden produkt może zostać kupiony przez wielu klientów. Interfejs użytkownika programu PowerPivot for Excel nie obsługuje relacji wiele-do-wielu. Aby uzyskać więcej informacji, zobacz sekcję „Relacje wiele-do-wielu” w tym temacie.
W poniższej tabeli pokazano relacje między trzema tabelami:
Relacja |
Typ |
Kolumna odnośnika |
Kolumna |
|---|---|---|---|
Klienci-RabatyDlaKlientów |
jeden-do-jednego |
Klienci.ID_klienta |
RabatyDlaKlientów.ID_klienta |
Klienci-Zamówienia |
jeden-do-wielu |
Klienci.ID_klienta |
Zamówienia.ID_klienta |
Relacje a wydajność
Po utworzeniu jakiejkolwiek relacji program PowerPivot for 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. Aby uzyskać więcej informacji, zobacz temat Ponowne obliczanie formuł.
Wymagania dotyczące relacji
Program PowerPivot for Excel ma kilka wymagań, które należy spełnić podczas tworzenia relacji:
Pojedyncza relacja między tabelami
Istnienie wielu relacji mogłoby spowodować powstanie niejednoznacznych zależności między tabelami. Do przeprowadzania dokładnych obliczeń potrzebna jest jedna ścieżka od jednej tabeli do następnej. Dlatego między każdą parą tabel może istnieć tylko jedna relacja. 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. Przy próbie zaimportowania tych tabel pierwsza relacja zostanie utworzona pomyślnie, ale w przypadku kolejnych relacji dotyczących tej samej kolumny pojawi się następujący błąd:
* Relacja: tabela[kolumna 1]-> tabela[kolumna 2] - Stan: błąd - Przyczyna: Nie można utworzyć relacji między dwiema tabelami <tabela 1> i <tabela 2>. Między dwiema tabelami może istnieć tylko jedna relacja bezpośrednia lub pośrednia.
W przypadku istnienia dwóch tabel i wielu relacji między nimi należy zaimportować wiele kopii tabeli zawierającej kolumnę odnośnika i utworzyć jedną relację między każdą parą tabel.
Jedna relacja dla każdej kolumny źródłowej
Kolumna źródłowa może uczestniczyć w wielu relacjach. Jeśli jakaś kolumna jest już używana jako kolumna źródłowa w jednej relacji, a użytkownik chce użyć tej kolumny do połączenia z inną powiązaną z nią kolumną odnośnika w innej tabeli, może utworzyć kopię tej kolumny i użyć jej w nowej relacji.
Kopię kolumny, która ma dokładnie takie same wartości, można łatwo utworzyć za pomocą formuły języka DAX w kolumnie obliczeniowej. Aby uzyskać więcej informacji, zobacz temat Kolumny obliczeniowe.
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 programie PowerPivot for Excel 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 skoroszytach programu PowerPivot.
Klucze złożone i kolumny odnośników
W skoroszycie programu PowerPivot 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. Przy próbie zaimportowania tabel mających istniejącą relację opartą na kluczu złożonym Kreator importu tabeli ignoruje tę relację, ponieważ nie można jej utworzyć w programie PowerPivot.
Jeśli użytkownik chce ręcznie utworzyć relację między dwiema tabelami w programie PowerPivot, a istnieje wiele kolumn określających klucze podstawowe i obce, przed utworzeniem relacji musi połączyć wartości w celu utworzenia jednej kolumny klucza. Można to zrobić przed zaimportowaniem danych albo w programie PowerPivot, tworząc kolumnę obliczeniową.
Relacje wiele-do-wielu
Program PowerPivot for Excel nie obsługuje relacji wiele-do-wielu i nie można po prostu dodać tabel skrzyżowań w programie PowerPivot. Można jednak używać funkcji języka DAX w celu modelowania relacji wiele-do-wielu.
Samosprzężenia i pętle
W przypadku tabel programu PowerPivot 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ę Employees z nią samą, aby utworzyć hierarchię pokazującą łańcuch zarządzania w firmie.
Program PowerPivot for 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.
Automatyczne wykrywanie i wnioskowanie relacji
Podczas importowania danych do okna programu PowerPivot Kreator importu tabeli automatycznie wykrywa istniejące relacje między tabelami. Ponadto podczas tworzenia tabeli przestawnej program PowerPivot for Excel 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 skoroszytach programu PowerPivot.
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 PowerPivot for 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: Emp ID, EmployeeID, Employee ID, 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 PowerPivot for 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 tematy Rozwiązywanie problemów z relacjami i Kulisy automatycznego wykrywania relacji w programie PowerPivot.
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.
Products i Category — relacja utworzona ręcznie
Category i SubCategory — relacja utworzona ręcznie
Products i SubCategory — 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 Sales i Products oraz Sales i Customers, relacja nie jest wnioskowana. Jest to spowodowane tym, że relacja między tabelami Products i Customers jest relacją wiele-do-wielu.
Zobacz także
Koncepcje
Tworzenie relacji między dwiema tabelami