Omówienie relacji
Ten temat stanowi wprowadzenie do relacji zdefiniowanych między tabelami w 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, należy zrozumieć jakie relacji jest, jakie wymagania są do definiowania relacji i jak PowerPivot for Excel można automatycznie wykrywać relacji można.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 jest połączenie między dwiema tabelami danych na podstawie jednego lub więcej kolumn w każdej tabela (dokładnie jedną kolumna w każdej tabela dla 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 |
Ashton |
chris.ashton@contoso.com |
.05 |
256 |
2010-01-07 |
Kompaktowy aparat cyfrowy |
11 |
1 |
Ashton |
chris.ashton@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 stosowane w relacyjne bazy danych jak SQL Server.Na przykład zaimportować do bazy PowerPivot for Excel może reprezentować dane zamówienie za pomocą trzech powiązanych tabel:
Klienci
[ID_klienta] |
Nazwa |
AdresEmail |
|---|---|---|
1 |
Ashton |
chris.ashton@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 importować te tabele z tej samej bazy danych PowerPivot , można wykrywać relacji między tabelami, na podstawie kolumn, które są w nawiasach [kwadratowych] i można odtworzyć te relacje w PowerPivot okna.Aby uzyskać więcej informacji, zobacz automatycznego wykrywania i wnioskowanie relacje w tym temacie.Importowanie tabel z wielu źródeł, można ręcznie utworzyć relacje opisane w 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 Customers i Orders tabele mogą być powiązane ze sobą, ponieważ zawierają kolumna, która przechowuje identyfikator klientaW tym przykładzie nazwy kolumn są takie same, ale nie jest to wymagane.Jeden może być CustomerID i innym CustomerNumber, dopóki wszystkie wiersze w Orders tabela zawiera identyfikator przechowywanego w Customers tabela.
W relacyjnej bazie danych istnieje kilka rodzajów klucze, które są zazwyczaj tylko kolumnami o specjalnych właściwościach.Z naszego punktu widzenia najbardziej interesujące są następujące cztery typy kluczy:
Klucz podstawowy: jednoznacznie identyfikuje wiersz w tabela, takich jak CustomerID w Customers tabela.
Klucz alternatywny (lub klucz kandydujący): kolumna innej niż klucz podstawowy, który jest unikatowy.Na przykład Employees tabela może przechowywać identyfikator pracownika i numer ubezpieczenia społecznego, które są unikatowe.
Klucz obcy: kolumna, która odwołuje się do kolumna unikatowe w innej tabela, takich jak CustomerID w Orders tabela, która odnosi się do CustomerID w Customers tabela.
Klucz złożony: klucz, który składa się z więcej niż jedną kolumna.Kompozytowy klucze nie są obsługiwane w PowerPivot for Excel.Aby uzyskać więcej informacji, zobacz sekcję „Klucze złożone i kolumny odnośników” w tym temacie.
W PowerPivot for Excel, klucz podstawowy lub klucz alternatywny jest określany jako kolumna odnośnika powiązanych, lub po prostu kolumna odnośnika.Jeśli tabela zawiera zarówno klucz podstawowy i alternatywny, można użyć jako kolumna odnośnika. Klucz obcy jest określany jako źródło kolumny lub po prostu kolumny. W naszym przykładzie byłoby zdefiniowane relacje między CustomerID w Orders tabeli (kolumna) i CustomerID (kolumna odnośnika) w Customers tabeli.Jeśli importujesz dane z relacyjnej bazy danych, domyślnie PowerPivot for Excel wybiera klucz obcy z jednej tabela i odpowiedniego klucz podstawowy z innej tabela. Jednak można użyć dowolnej kolumna zawierającej unikatowe wartości dla kolumn odnośników.
Typy relacji
Relacje między Customers i Orders jest relacji jeden do wielu.Każdy klient może mieć wiele zamówień, ale zamówienie może dotyczyć tylko jednego klienta.Typy relacji są jeden i wiele do wielu. CustomerDiscounts tabela, która definiuje pojedynczą stawkę rabatu dla każdego klienta, jest relacją jeden do jednego z Customers tabela.An example of a many-to-many relationship is a direct relationship between Products and Customers, in which a customer can buy many products and the same product can be bought by many customers.PowerPivot for Excel does not support many-to-many relationships in the user interface. For more information, see "Many-to-Many Relationships" in this topic.
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 wszelkie relacje PowerPivot for Excel zazwyczaj należy ponownie obliczyć wszystkie formuły, które używają kolumn z tabel w nowo utworzoną relację.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ł.
Wymagania dotyczące relacji
PowerPivot for Excel zawiera szereg wymagań, których należy przestrzegać 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 AdventureWorks DW 2008, tabela, DimDate, zawiera kolumna, DateKey, który jest związany z trzech różnych kolumn w tabeli FactInternetSales: OrderDate, DueDate, and 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:
* Relacji: Tabela [kolumna 1]-> tabeli [kolumna 2] — Stan: Błąd — Przyczyna: Nie można utworzyć relacji między 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 Tworzenie kolumny obliczeniowej.
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 PowerPivot for Excel, wartości null i puste ciągi znaków są równoważne puste, który jest odrębne dane wartości.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 skoroszytach programu PowerPivot.
Klucze złożone i kolumny odnośników
Nie można użyć złożonego kluczy w PowerPivot skoroszyt; zawsze musi mieć dokładnie jedną kolumna, która jednoznacznie identyfikuje każdy wiersz w tabela.Spróbuj zaimportować tabele, które mają na podstawie istniejącej relacji klucz złożony, Kreator importowania tabeli będzie ignorować tej relacji, ponieważ nie można utworzyć w PowerPivot.
Jeśli chcesz utworzyć relację między dwiema tabelami w PowerPivot, a wiele kolumn, definiowanie kluczy podstawowych i obcych, należy połączyć wartości, aby utworzyć kolumna klucz przed utworzeniem relacji.Można to zrobić przed importowaniem danych lub można to zrobić w PowerPivot przez tworzenie kolumna obliczeniowa.
Relacje wiele-do-wielu
PowerPivot for Excelnie nie obsługi relacji wiele do wielu, a nie po prostu dodać tabel skrzyżowań w PowerPivot.Można jednak używać funkcji języka DAX w celu modelowania relacji wiele-do-wielu.
Samosprzężenia i pętle
Samosprzężeniach nie są dozwolone w PowerPivot tabele.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 łączyć Employees tabela do siebie stworzenia hierarchii, która pokazuje łańcucha zarządzania w działalności.
PowerPivot for Excel nie zezwala na pętle utworzone między relacje skoroszyt.Innymi słowy, zabroniony jest poniższy zestaw relacji.
Tabela 1, kolumna a do Tabela 2, kolumna f
Tabela 2, kolumn f do Tabela 3, kolumna n
Tabela 3, kolumn 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 PowerPivot okno Kreatora importu tabeli automatycznie wykrywa wszystkie istniejące relacje między tabelami.Ponadto podczas tworzenia tabela przestawna, 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 na temat typów danych, zobacz 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 PowerPivot for Excel wykrywać relacji, należy najpierw zaktualizować tabela przeszukiwania produktu z identyfikatorami Brak 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 warunkach biznesowych, często te wahania na nazwy kolumn, które zawierają głównie dane: Emp ID, EmployeeID, Employee ID, EMP_ID, and so forth.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 PowerPivot for Excel znajdzie wiele możliwych relacji, a następnie tworzy relację.
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 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.
Productsi Category --tworzone ręcznie
Categoryi SubCategory --tworzone ręcznie
Productsi SubCategory --wywnioskowane relacji
Aby możliwe było automatyczne łączenie relacji w łańcuch, relacje muszą wskazywać jeden kierunek, tak jak pokazano powyżej.Gdyby początkowej relacje między, na przykład Sales i Products, i Sales i Customers, relacja nie jest wywnioskować.Wynika to z relacji między Products i Customers jest relacja typu wiele do wielu.