Relace mezi tabulkami v datovém modelu

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.
Video: Relace v Power View a PowerPivotu

Vytvoříte-li relace mezi daty různých tabulek, budete moci provádět komplexnější analýzu dat. Relace je propojení mezi dvěma tabulkami dat založené na jednom sloupci v každé tabulce. 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 následující strukturu:

CustomerID

Name

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

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)

[CustomerID]

Name

Email

1

Ashton

chris.ashton@contoso.com

2

Jaworski

michal.jaworski@contoso.com

CustomerDiscounts (Slevy zákazníků)

[CustomerID]

DiscountRate

1

0,05

2

0,10

Orders (Objednávky)

[CustomerID]

OrderID

OrderDate

Product

Quantity

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 jsou relace, které explicitně vytvoříte nebo které za vás vytvoří Excel při současném importu víc tabulek najednou. 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 pro import tabulek ze stejné databáze použijete 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.

Začátek stránky

V tomto článku

Sloupce a klíče

Typy relací Typy relací

Relace a výkon

Více relací mezi tabulkami

Požadavky na relace tabulek

Nepodporováno v relaci tabulky

Složené klíče a vyhledávací sloupce

Relace M:N

Spojení sama na sebe a cykly

Automatické rozpoznávání a odvozování relací v doplňku PowerPivot

Automatické rozpoznávání pojmenovaných sad

Odvozování relací

Sloupce a klíče

Relace jsou založeny na sloupcích jednotlivých tabulek, které obsahují stejná data. Například lze vytvořit relace mezi tabulkami Customers (Zákazníci) a Orders (Objednávky), protože obě tabulky obsahují 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íčů, což jsou obvykle vlastně jen sloupce 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.

Pro naše potřeby jsou nejzajímavější následující klíče:

  • 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ý také obsahuje jedinečná data. 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, v našem případě 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 relace definována 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 tabulkou Customers a Orders je relace typu 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šími typy relací jsou 1:1 a M:N. Tabulka CustomerDiscounts, která definuje jednu sazbu slev pro každého zákazníka, má relaci typu 1:1 k tabulce Customers.

Následující tabulka obsahuje přehled relací mezi uvedenými třemi tabulkami:

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ší informace 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 provádění 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. Ostatní relace jsou neaktivní, lze je však zadat do vzorců a dotazů. 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.

Začátek stránky

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 naleznete v tématu Datové typy podporované v datových modelech.

Nepodporováno v relaci tabulky

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íč vzniká složením víc sloupců. Složené klíče nejdou použít v datových modelech. Tabulka musí mít vždycky k dispozici právě jeden sloupec, který jedinečně identifikuje každý řádek tabulky. Pokud se pokusíte importovat tabulky, které obsahují relaci založenou na složeném klíči, bude Průvodce importem tabulek v doplňku Power Pivot tuto relaci ignorovat, protože v modelu ji nejde 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.

Začátek stránky

Automatické rozpoznávání a odvozování relací v doplňku Power Pivot

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ý předtím vytvořil v Excelu.

Při importu víc tabulek rozpozná Power Pivot automaticky všechny stávající relace mezi tabulkami. Power Pivot provede analýzu dat v tabulkách taky při vytvoření kontingenční tabulky. 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 relace mohla být rozpoznána, musí být počet jedinečných klíčů ve vyhledávacím sloupci větší než počet hodnot v tabulce na straně N relace. Jinými slovy klíčový sloupec na straně „N“ relace nesmí obsahovat hodnoty, které se nenacházejí v klíčovém sloupci vyhledávací tabulky. Předpokládejme například, že máte tabulku obsahující seznam produktů s jejich ID (vyhledávací tabulka) a tabulku, ve které jsou uvedeny prodeje jednotlivých produktů (strana „N“ relace). Pokud záznamy o prodejích obsahují ID, ke kterým neexistují odpovídající ID v tabulce Products, nemůže být relace vytvořena automaticky. Relaci však můžete vytvořit ručně. Chcete-li, aby aplikace Excel relaci rozpoznala, je třeba nejprve aktualizovat vyhledávací tabulku Product a doplnit do ní 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 apod. 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.

Začátek stránky

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×