Vytvoříte-li relace mezi různými tabulkami, budete moci provádět komplexnější analýzu dat. Relace je propojení mezi dvěma tabulkami, které obsahují data. Základem relace je jeden sloupec z každé tabulky. Abyste si udělali lepší představu o tom, proč mohou být relace užitečné, představte si, že sledujete data zákaznických objednávek ve své firmě. Data byste mohli sledovat v jediné tabulce, která by měla tuto strukturu:
| CustomerID | Název | DiscountRate | OrderID | DatumObjednávky | Product | Mnozstvi | |
|---|---|---|---|---|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com | 0,05 | 256 | 7. ledna 2010 | Digitální kompakt | 11 |
| 1 | Ashton | chris.ashton@contoso.com | 0,05 | 255 | 3. ledna 2010 | Zrcadlovka | 15 |
| 2 | Jaworski | michal.jaworski@contoso.com | 0,10 | 254 | 3. ledna 2010 | Levný program na zpracování videa | 27 |
Tento přístup může fungovat, ale je s ním spojeno uchovávání velkého množství redundantních dat, například e-mailové adresy zákazníka u každé objednávky. Úložný prostor je sice levný, ale při změně e-mailové adresy zákazníka musíte zajistit aktualizaci všech řádků, které ji obsahují. Jedním z řešení tohoto problému je rozdělit data do více tabulek a definovat mezi nimi relace. Tento přístup používají relační databáze, například databáze systému SQL Sever. Importovaná databáze by například mohla obsahovat data objednávek ve třech tabulkách s definovanými relacemi:
Customers (Zákazníci)
| [IDZákazníka] | Název | |
|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com |
| 2 | Jaworski | michal.jaworski@contoso.com |
CustomerDiscounts (Slevy zákazníků)
| [IDZákazníka] | DiscountRate |
|---|---|
| 1 | 0,05 |
| 2 | 0,10 |
Orders (Objednávky)
| [IDZákazníka] | OrderID | DatumObjednávky | Product | Mnozstvi |
|---|---|---|---|---|
| 1 | 256 | 7. ledna 2010 | Digitální kompakt | 11 |
| 1 | 255 | 3. ledna 2010 | Zrcadlovka | 15 |
| 2 | 254 | 3. ledna 2010 | Levný program na zpracování videa | 27 |
V datovém modelu existují relace, které explicitně vytvoříte nebo které za vás vytvoří Excel automaticky při současném importu několika tabulek. K vytvoření nebo správě modelu můžete použít taky doplněk Power Pivot. Podrobnosti získáte v tématu Vytvoření datového modelu v Excelu.
Pokud k importu tabulek ze stejné databáze použijete doplněk Power Pivot, může Power Pivot rozpoznat vzájemné relace tabulek na základě sloupců v hranatých závorkách a tyto relace reprodukovat v datovém modelu, který vytváří na pozadí. Další informace najdete v tématu Automatické rozpoznávání a odvozování relací v tomto článku. Pokud importujete tabulky z víc zdrojů, můžete relace vytvořit ručně pomocí postupu popsaného v tématu Vytvoření relace mezi dvěma tabulkami.
Sloupce a klíče
Relace jsou založeny na sloupcích jednotlivých tabulek, které obsahují stejná data. Můžete například vytvořit relaci mezi tabulkou Zákazníci a tabulkou Objednávky , pokud každá tabulka obsahuje sloupec, ve kterém je uvedeno ID zákazníka. V tomto příkladu jsou názvy sloupců stejné, ale není to nutné. Sloupec by se mohl v jedné tabulce jmenovat CustomerID (ID zákazníka) a ve druhé CustomerNumber (číslo zákazníka), musí ovšem platit to, že všechny řádky v tabulce Orders (Objednávky) budou obsahovat ID, které je rovněž uloženo v tabulce Customers (Zákazníci).
V relační databázi existuje několik typů klíčů. Klíč je obvykle sloupec se zvláštními vlastnostmi. Pochopení účelu každého klíče vám pomůže spravovat datový model s více tabulkami, který poskytuje data pro kontingenční tabulku, kontingenční graf nebo sestavu nástroje Power View.
Ačkoli existuje mnoho typů klíčů, jsou pro naše účely nejdůležitější:
- Primární klíč: Jedinečný identifikátor řádku v tabulce, např. CustomerID (ID zákazníka ) v tabulce Customers (Zákazníci ).
- Alternativní klíč (nebo kandidátový klíč): Sloupec odlišný od primárního klíče, který je jedinečný. V tabulce zaměstnanců by například mohlo být uloženo ID zaměstnance a číslo sociálního pojištění. V obou případech jde o jedinečné údaje.
- Cizí klíč: Sloupec odkazující na jedinečný sloupec v jiné tabulce, jako je například sloupec CustomerID (ID zákazníka ) v tabulce Orders (Objednávky ), který odkazuje na sloupec CustomerID (ID zákazníka ) v tabulce Customers (Zákazníci).
V datovém modelu je primární nebo alternativní klíč označován jako související sloupec. Pokud má tabulka primární i alternativní klíč, lze kterýkoliv z nich použít jako základ pro relaci tabulky. Cizí klíč se také nazývá zdrojový sloupec nebo pouze sloupec. V našem příkladu by byla definována relace mezi sloupcem CustomerID (sloupec) v tabulce Orders a sloupcem CustomerID (vyhledávací sloupec) v tabulce Customers . Při importu dat z relační databáze vybírá aplikace Excel ve výchozím nastavení cizí klíč z jedné tabulky a odpovídající primární klíč z druhé. Jako vyhledávací sloupec však můžete použít libovolný sloupec s jedinečnými hodnotami.
Typy relací
Relace mezi zákazníkem a objednávkou je relace 1:N. Každý zákazník může provést více objednávek, nicméně jedna objednávka nemůže být provedena více zákazníky. Další důležitou relací mezi tabulkami je relace 1:1. V našem příkladu má tabulka CustomerDiscounts , která definuje jednu sazbu slev pro každého zákazníka, relaci typu 1:1 k tabulce Customers.
Následující tabulka ukazuje relace mezi třemi tabulkami (Zákazníci, ZákazníciSlevy a Objednávky):
| Relace | Typ: | Vyhledávací sloupec | Sloupec |
|---|---|---|---|
| Customers-CustomerDiscounts | 1:1 | Customers.CustomerID | CustomerDiscounts.CustomerID |
| Customers-Orders | 1:N | Customers.CustomerID | Orders.CustomerID |
Poznámka
Datový model nepodporuje relace M:N. Příkladem relace M:N je přímá relace mezi tabulkami Products (Produkty) a Customers (Zákazníci), kde si zákazník může koupit více produktů a stejně tak jeden produkt může být koupen více zákazníky.
Relace a výkon
Po vytvoření každé relace musí aplikace Excel obvykle přepočítat všechny vzorce, které používají sloupce z tabulek v nově vytvořené relaci. Zpracování může v závislosti na množství dat a složitosti relací trvat delší dobu. Další podrobnosti naleznete v tématu Přepočet vzorců.
Více relací mezi tabulkami
Datový model může obsahovat více relací mezi dvěma tabulkami. Aplikace Excel však k vytváření přesných výpočtů potřebuje pracovat s jednoznačnou cestou od jedné tabulky ke druhé. Proto je mezi každou dvojicí tabulek v jednu chvíli aktivní vždy pouze jediná relace. I když ostatní relace nejsou aktivní, můžete zadat neaktivní relaci ve vzorcích a dotazech.
V zobrazení diagramu je aktivní relace zobrazena plnou čarou a neaktivní relace jsou zobrazeny přerušovanými čarami. Tabulka DimDate v databázi AdventureWorksDW2012 například obsahuje sloupec DateKey, který je v relaci se třemi různými sloupci v tabulce FactInternetSales: OrderDate, DueDate a ShipDate. Pokud je relace mezi sloupci DateKey a OrderDate aktivní relací, bude používána jako výchozí relace ve vzorcích, dokud neurčíte jinak.
Požadavky na relace tabulek
Relaci lze vytvořit po splnění následujících požadavků:
| Kritéria: | Popis |
|---|---|
| Jedinečný identifikátor v každé tabulce | Každá tabulka musí obsahovat jeden sloupec, který slouží jako jedinečný identifikátor každého řádku tabulky. Tento sloupec se často nazývá primární klíč. |
| Jedinečné vyhledávací sloupce | Hodnoty dat, které obsahuje vyhledávací sloupec, musejí být jedinečné. Sloupec tedy nesmí obsahovat duplicitní položky. V datovém modelu jsou hodnoty null a prázdné řetězce ekvivalentní prázdné hodnotě, která je samostatnou datovou hodnotou. To znamená, že ve vyhledávacím sloupci může být maximálně jedna hodnota null. |
| Kompatibilní datové typy | Datové typy ve zdrojovém sloupci a ve vyhledávacím sloupci musejí být kompatibilní. Další informace o datových typech najdete v tématu Datové typy podporované v datových modelech. |
Nepodporované funkce databáze v datovém modelu Excelu
V datovém modelu nelze vytvořit relaci tabulky, pokud je klíč složený. Omezení se vztahuje i na vytvoření relací 1:1 nebo 1:N. Další typy relací nejsou podporovány.
Složené klíče a vyhledávací sloupce
Složený klíč se skládá z víc sloupců. Datové modely nemůžou používat složené klíče: tabulka musí vždycky obsahovat právě jeden sloupec, který jedinečně identifikuje každý řádek tabulky. Pokud importujete tabulky, které mají existující relaci založenou na složeném klíči, Průvodce importem tabulky v Power Pivotu bude tuto relaci ignorovat, protože se v modelu nedá vytvořit.
Pokud chcete vytvořit relaci mezi dvěma tabulkami, ve kterých jsou primární a cizí klíče definované pomocí víc sloupců, je před vytvořením relace potřeba nejdřív příslušné hodnoty sloučit do jednoho klíčového sloupce. Můžete to udělat před importem dat nebo vytvořením počítaného sloupce v datovém modelu pomocí doplňku Power Pivot.
Relace M:N
Datový model nesmí obsahovat relace M:N. Spojovací tabulky do modelu přidat nelze. Můžete však pomocí funkcí jazyka DAX relace M:N modelovat.
Spojení sama na sebe a cykly
Spojení sama na sebe nejsou v datovém modelu povolena. Spojení sama na sebe je rekurzivní relace tabulky se sebou samou. Spojení sama na sebe jsou často používána k definování relací typu nadřazený-podřízený. Tímto způsobem byste například mohli spojit tabulku Employees (Zaměstnanci) samu se sebou, čímž byste vytvořili hierarchii řízení ve firmě.
Aplikace Excel nepovoluje vytváření cyklických relací v sešitu. Jinými slovy, následující sada relací není povolena.
Sloupce a tabulky 1 se sloupcem f tabulky 2
Sloupce f tabulky 2 se sloupcem n tabulky 3
Sloupce n tabulky 3 se sloupcem a tabulky 1
Při pokusu o vytvoření relace, která by vedla ke vzniku cyklu, se zobrazí chybová zpráva.
Automatické rozpoznávání a odvozování relací v doplňku PowerPivot
Jednou z výhod importu dat pomocí doplňku Power Pivot je, že Power Pivot může někdy rozpoznat relace a vytvořit nové relace v datovém modelu, který vytvořil v Excelu.
Při importu víc tabulek Power Pivot automaticky zjistí všechny stávající relace mezi tabulkami. Power Pivot analyzuje data v tabulkách taky vždycky, když vytvoříte kontingenční tabulku. Rozpozná možné relace, které ještě nebyly definované, a doporučí vhodné sloupce, které by měly být součástí těchto relací.
Algoritmus rozpoznávání používá statistická data týkající se hodnot a metadata sloupců k vytvoření hypotéz o pravděpodobnosti relací.
- Datové typy ve všech sloupcích v relaci by měly být kompatibilní. Pro účely automatického rozpoznávání jsou podporovány pouze celočíselné a textové datové typy. Další informace o datových typech naleznete v tématu Datové typy podporované v datových modelech.
- Aby bylo možné relaci úspěšně rozpoznat, musí být počet jedinečných klíčů ve vyhledávacím sloupci větší než hodnoty v tabulce na straně N. To znamená, že klíčový sloupec na straně N relace nesmí obsahovat hodnoty, které nejsou v klíčovém sloupci vyhledávací tabulky. Předpokládejme například, že máte tabulku, ve které jsou uvedeny produkty s jejich ID (vyhledávací tabulka) a tabulku prodejů, ve které jsou uvedeny prodeje jednotlivých produktů (strana N relace). Pokud záznamy o prodeji obsahují ID produktu, který nemá odpovídající ID v tabulce Produkty, nejde automaticky vytvořit relaci, ale můžete ji vytvořit ručně. Aby Excel tuto relaci rozpoznal, musíte nejdřív aktualizovat vyhledávací tabulku Produkt o ID chybějících produktů.
- Název klíčového sloupce na straně „N“ by se měl podobat názvu klíčového sloupce ve vyhledávací tabulce. Názvy nemusejí být zcela totožné. Ve firemních nastaveních se například často používají varianty názvů sloupců obsahujících v podstatě stejná data: Emp ID, EmployeeID, Employee ID, EMP_ID atd. Algoritmus rozpozná podobné názvy a přiřadí vyšší pravděpodobnost těm sloupcům, které mají podobné nebo identické názvy. Chcete-li tedy zvýšit pravděpodobnost vytvoření relace, můžete zkusit přejmenovat sloupce importovaných dat tak, aby byly podobné názvům příslušných sloupců ve stávajících tabulkách. Pokud aplikace Excel najde více možných relací, relace nebude vytvořena.
Tyto informace vám mohou pomoci pochopit, proč nejsou zjištěny všechny relace a jak mohou změny metadat (například názvů polí a datových typů) vést ke zlepšení výsledků automatického rozpoznávání relací. Další informace naleznete v tématu Řešení problémů s relacemi.
Automatické rozpoznávání pojmenovaných sad
Relace mezi pojmenovanými sadami a poli kontingenční tabulky nejsou zjištěny automaticky. Tyto relace je možné vytvořit ručně. Chcete-li použít automatické rozpoznávání relací, odeberte všechny pojmenované sady a přidejte jednotlivá pole z pojmenovaných sad přímo do kontingenční tabulky.
Odvozování relací
V některých případech jsou relace mezi tabulkami automaticky zřetězeny. Bude-li tedy například vytvořena relace mezi prvními dvěma níže uvedenými sadami tabulek, mezi dalšími dvěma tabulkami se odvodí a automaticky vytvoří relace.
Products a Category (Produkty a Kategorie) – vytvořeno ručně
Category a SubCategory (Kategorie a Podkategorie) – vytvořeno ručně
Products a SubCategory (Produkty a Podkategorie) – odvozená relace
Má-li dojít k automatickému zřetězení relací, je třeba, aby relace šly jedním směrem (viz výše). Pokud by počáteční relace byly (například) mezi tabulkami Sales a Products a mezi tabulkami Sales a Customers, nedošlo by k odvození relace. Důvodem je fakt, že relace mezi tabulkami Products a Customers je typu M:N.