Pridajte k analýze údajov viac sily vytvorením vzťahov, ktoré znásobia rôzne tabuľky. Vzťah je prepojenie medzi dvoma tabuľkami, ktoré obsahujú údaje: základom vzťahu je jeden stĺpec v každej tabuľke. Ak chcete zistiť, prečo sú vzťahy užitočné, predstavte si, že sledujete údaje zákazníckych objednávok vo svojom podniku. Môžete sledovať všetky údaje v jednej tabuľke, ktoré majú takúto štruktúru:
ID zákazníka |
Name |
|
DiscountRate |
OrderID |
OrderDate |
Product |
Quantity |
---|---|---|---|---|---|---|---|
1 |
Baláž |
martin.balaz@contoso.com |
0,05 |
256 |
7. 1. 2010 |
Digitálny kompakt |
11 |
1 |
Baláž |
martin.balaz@contoso.com |
0,05 |
255 |
3. 1. 2010 |
Zrkadlovka |
15 |
2 |
Lukáč |
tomas.lukac@contoso.com |
0,10 |
254 |
3. 1. 2010 |
Filmový set |
27 |
Tento prístup môže fungovať, ale zahŕňa uloženie množstva nadbytočných údajov, ako je napríklad e-mailová adresa zákazníka pre každú objednávku. Ukladací priestor je lacný, ale v prípade zmeny e-mailovej adresy je nutné zabezpečiť aktualizáciu každého riadka pre daného zákazníka. Jedným z možných riešení tohto problému je rozdelenie údajov do viacerých tabuliek a definovanie vzťahov medzi týmito tabuľkami. Tento prístup sa uplatňuje v relačných databázach, ako sú napríklad databázy programu SQL Server. Importovaná databáza napríklad môže predstavovať údaje objednávky pomocou troch súvisiacich tabuliek:
Customers
[CustomerID] |
Name |
|
---|---|---|
1 |
Baláž |
martin.balaz@contoso.com |
2 |
Lukáč |
tomas.lukac@contoso.com |
CustomerDiscounts
[CustomerID] |
DiscountRate |
---|---|
1 |
0,05 |
2 |
0,10 |
Orders
[CustomerID] |
OrderID |
OrderDate |
Product |
Quantity |
---|---|---|---|---|
1 |
256 |
7. 1. 2010 |
Digitálny kompakt |
11 |
1 |
255 |
3. 1. 2010 |
Zrkadlovka |
15 |
2 |
254 |
3. 1. 2010 |
Filmový set |
27 |
V dátovom modeli existujú vzťahy – vzťah, ktorý explicitne vytvoríte, alebo vzťah, ktorý Excel automaticky vytvorí vo vašom mene pri súčasnom importovaní viacerých tabuliek. Na vytvorenie alebo správu modelu môžete tiež použiť Power Pivot. Podrobné informácie nájdete v téme Vytvorenie dátového modelu v programe Excel.
Ak na import tabuliek z tej istej databázy použijete doplnok Power Pivot, Power Pivot dokáže rozpoznať vzťahy medzi tabuľkami na základe stĺpcov uvedených v [hranatých zátvorkách] a dokáže reprodukovať tieto vzťahy v údajovom modeli, ktorý zostaví na pozadí. Ďalšie informácie nájdete v tomto článku v časti Automatické zisťovanie a určovanie vzťahov. Ak importujete tabuľky z viacerých zdrojov, môžete manuálne vytvoriť vzťahy tak, ako je popísané v téme Vytvorenie vzťahu medzi dvomi tabuľkami.
Vzťahy sú založené na stĺpcoch v jednotlivých tabuľkách, ktoré obsahujú rovnaké údaje. Tabuľku Zákazníci môžete napríklad prepojiť s tabuľkou Objednávky , ak každá obsahuje stĺpec, v ktorom je uložené ID zákazníka. V uvedenom príklade sú názvy stĺpcov rovnaké, ale nie je to nevyhnutné. Jeden zo stĺpcov môže byť stĺpec CustomerID a druhým môže byť stĺpec CustomerNumber, ak všetky riadky v tabuľke Orders obsahujú identifikáciu, ktorá je uložená aj v tabuľke Customers.
V relačnej databáze existuje niekoľko typov kľúčov. Kľúčom je zvyčajne stĺpec so špeciálnymi vlastnosťami. Porozumenie účelu jednotlivých kľúčov vám môže pomôcť pri správe modelu údajov s viacerými tabuľkami, ktorý poskytuje údaje do zostavy kontingenčnej tabuľky, kontingenčného grafu alebo funkcie Power View.
Hoci existuje mnoho typov kľúčov, tieto sú pre náš účel najdôležitejšie:
-
Hlavný kľúč: jedinečne identifikuje riadok v tabuľke, napríklad CustomerID v tabuľke Zákazníci .
-
Alternatívny kľúč (alebo kandidátsky kľúč): iný stĺpec ako primárny kľúč, ktorý je jedinečný. V tabuľke Zamestnanci môže byť napríklad uložené ID zamestnanca a číslo sociálneho zabezpečenia, ktoré sú jedinečné.
-
Cudzí kľúč: stĺpec, ktorý odkazuje na jedinečný stĺpec v inej tabuľke, napríklad CustomerID v tabuľke Objednávky , ktorý odkazuje na stĺpec CustomerID v tabuľke Zákazníci.
V modeli údajov sa na primárny kľúč alebo na alternatívny kľúč odkazuje ako na súvisiaci stĺpec. Ak tabuľka má primárny aj alternatívny kľúč, môžete ktorýkoľvek z nich použiť ako základ vzťahu tabuľky. Na cudzí kľúč sa odkazuje ako na zdrojový stĺpec alebo len ako na stĺpec. V našom príklade by bol vzťah definovaný medzi stĺpcami CustomerID v tabuľke Objednávky (stĺpec) a CustomerID v tabuľke Zákazníci (vyhľadávací stĺpec). Ak importujete údaje z relačnej databázy, program Excel predvolene vyberie cudzí kľúč z jednej tabuľky a zodpovedajúci primárny kľúč z druhej tabuľky. Ako stĺpec vyhľadávania však môžete použiť ľubovoľný stĺpec s jedinečnými hodnotami.
Vzťah medzi zákazníkom a objednávkou je vzťah "one-to-many". Každý zákazník môže mať viacero objednávok, ale objednávka nemôže mať viacero zákazníkov. Ďalším dôležitým vzťahom tabuliek je vzťah "one-to-one". V našom príklade má tabuľka CustomerDiscounts , ktorá definuje jednu diskontnú sadzbu pre každého zákazníka, vzťah "one-to-one" s tabuľkou Zákazníci.
Táto tabuľka zobrazuje vzťahy medzi tromi tabuľkami (Customers, CustomerDiscounts a Orders):
Vzťah |
Typ |
Vyhľadávací stĺpec |
Stĺpec |
---|---|---|---|
Customers-CustomerDiscounts |
one-to-one |
Customers.CustomerID |
CustomerDiscounts.CustomerID |
Customers-Orders |
one-to-many |
Customers.CustomerID |
Orders.CustomerID |
Poznámka: Model údajov nepodporuje vzťahy „many-to-many“. Ako príklad vzťahu „many-to-many“ je možné uviesť priamy vzťah medzi tabuľkami Products a Customers, v ktorom zákazník môže nakúpiť viacero produktov a jeden produkt môže byť kúpený viacerými zákazníkmi.
Po vytvorení vzťahu musí Excel zvyčajne prepočítať všetky vzorce, ktoré používajú stĺpce z tabuliek v novovytvorenom vzťahu. Spracovanie môže určitý čas trvať, čo závisí od množstva údajov a zložitosti vzťahov. Ďalšie podrobnosti nájdete v téme Prepočítanie vzorcov.
V modeli údajov môže existovať viacero vzťahov medzi dvomi tabuľkami. Na vytváranie presných výpočtov potrebuje Excel jednu cestu z jednej tabuľky do druhej. Preto musí byť v určitom čase pre každý pár tabuliek aktívny iba jeden vzťah. Hoci sú ostatné neaktívne, môžete vo vzorcoch a dotazoch zadať neaktívny vzťah.
V zobrazení diagramu je aktívny vzťah plná čiara a neaktívne sú prerušované čiary. Napríklad v databáze AdventureWorksDW2012 tabuľka DimDate obsahuje stĺpec DateKey, ktorý súvisí s tromi rôznymi stĺpcami v tabuľke FactInternetSales: OrderDate, DueDate a ShipDate. Ak je aktívny vzťah medzi stĺpcami DateKey a OrderDate, je to predvolený vzťah vo vzorcoch, kým nezadáte iný vzťah.
Vzťah možno vytvoriť v prípade splnenia týchto požiadaviek:
Kritériá |
Popis |
---|---|
Jedinečný identifikátor pre každú tabuľku |
Každá tabuľka musí mať jeden stĺpec, ktorý jedinečne identifikuje každý riadok v tejto tabuľke. Tento stĺpec sa často označuje ako primárny kľúč. |
Jedinečné stĺpce vyhľadávania |
Údajové hodnoty v stĺpci vyhľadávania musia byť jedinečné. Inými slovami, stĺpec nemôže obsahovať duplicitné hodnoty. Hodnota null a prázdne reťazce sú v dátovom modeli ekvivalentné prázdnej hodnote, čo predstavuje jednoznačnú hodnotu údajov. To znamená, že v stĺpci vyhľadávania sa nemôže nachádzať viacero hodnôt null. |
Kompatibilné typy údajov |
Typy údajov v zdrojovom stĺpci a v stĺpci vyhľadávania musia byť kompatibilné. Ďalšie informácie o typoch údajov nájdete v téme Typy údajov podporované v dátových modeloch. |
V modeli údajov nemôžete vytvoriť vzťah tabuľky, ak je kľúčom zložený kľúč. Tiež nemožno vytvoriť vzťahy typu „one-to-one“ a „one-to-many“. Iné typy vzťahov sa nepodporujú.
Zložené kľúče a stĺpce vyhľadávania
Zložený kľúč sa skladá z viacerých stĺpcov. Dátové modely nemôžu používať zložené kľúče: tabuľka musí mať vždy presne jeden stĺpec, ktorý jedinečne identifikuje každý riadok v tabuľke. Ak importujete tabuľky, ktoré majú existujúci vzťah založený na zloženom kľúči, Sprievodca importom tabuľky v doplnku Power Pivot tento vzťah bude ignorovať, pretože ho nie je možné vytvoriť v modeli.
Ak chcete vytvoriť vzťah medzi dvoma tabuľkami s viacerými stĺpcami, ktoré definujú primárny a cudzí kľúč, najprv skombinujte tieto hodnoty a vytvorte jeden stĺpec kľúča a až potom vytvorte vzťah. Môžete to urobiť pred importovaním údajov alebo vytvorením vypočítaného stĺpca v dátovom modeli pomocou doplnku Power Pivot.
Vzťahy „many-to-many“
Model údajov nemôže mať vzťahy „many-to-many“. Do modelu jednoducho nie je možné pridať tabuľky spojení . Ak však chcete modelovať vzťahy „many-to-many“, môžete na to použiť funkcie DAX.
Vlastné spojenia a slučky
Vlastné spojenia nie sú v modeli údajov povolené. Vlastné spojenie je rekurzívny vzťah medzi tabuľkou a tou istou tabuľkou. Vlastné spojenia sa často používajú na definovanie hierarchií nadradených a podriadených položiek. Môžete napríklad vytvoriť spojenie tabuľky Employees so sebou samou a vytvoriť hierarchiu, ktorá vyjadruje reťazec riadenia v podniku.
Program Excel nepovoľuje vytváranie slučiek medzi vzťahmi v zošite. Inými slovami, nasledujúca množina vzťahov je zakázaná.
Tabuľka 1, stĺpec a, s tabuľkou 2, stĺpcom f
Tabuľka 2, stĺpec f, s tabuľkou 3, stĺpcom n
Tabuľka 3, stĺpec n, s tabuľkou 1, stĺpcom a
Ak sa pokúsite vytvoriť vzťah smerujúci k vytvoreniu slučky, generuje sa chyba.
Jednou z výhod importovania údajov pomocou doplnku Power Pivot je, že Power Pivot dokáže niekedy zistiť vzťahy a vytvoriť nové vzťahy v údajovom modeli, ktorý vytvorí v Exceli.
Pri importe viacerých tabuliek Power Pivot automaticky zisťuje všetky existujúce vzťahy medzi tabuľkami. Aj pri vytváraní kontingenčnej tabuľky Power Pivot analyzuje údaje v tabuľkách. Zisťuje prípadné vzťahy, ktoré neboli definované, a navrhuje zodpovedajúce stĺpce na zahrnutie do týchto vzťahov.
Algoritmus zisťovania používa štatistické údaje o hodnotách a metaúdajoch stĺpcov, aby určil pravdepodobnosť vzťahov.
-
Typy údajov vo všetkých súvisiacich stĺpcoch by mali byť kompatibilné. Pri automatickom zisťovaní sú podporované iba celočíselné a textové typy údajov. Ďalšie informácie o typoch údajov nájdete v článku Typy údajov podporované v modeloch údajov.
-
Na úspešné zistenie vzťahu je nutné, aby počet jedinečných kľúčov v stĺpci vyhľadávania bol vyšší než hodnoty v tabuľke na strane „many“. Inými slovami, stĺpec kľúča na strane „many“ vzťahu nesmie obsahovať žiadne hodnoty, ktoré sa nenachádzajú v stĺpci kľúča tabuľky vyhľadávania. Predpokladajme napríklad, že máte tabuľku obsahujúcu produkty aj s ich identifikáciami (tabuľku vyhľadávania) a tabuľku predaja obsahujúcu údaje o predaji jednotlivých produktov (strana „many“ vzťahu). Ak záznamy o predaji obsahujú identifikáciu produktu, ktorý nemá zodpovedajúcu identifikáciu v tabuľke Products, vzťah sa nedá vytvoriť automaticky, ale môžete ho vytvoriť manuálne. Ak chcete, aby program Excel zistil vzťah, je najprv potrebné aktualizovať tabuľku vyhľadávania Products o identifikácie chýbajúcich produktov.
-
Zabezpečte, aby názov stĺpca kľúča na strane „many“ bol podobný názvu stĺpcu kľúča v tabuľke vyhľadávania. Názvy nemusia byť úplne rovnaké. V podnikovom nastavení máte napríklad často variácie názvov stĺpcov, ktoré obsahujú v podstate rovnaké údaje: Emp ID, EmployeeID, Employee ID, EMP_ID atď. Algoritmus zisťuje podobné názvy a priradí vyššiu pravdepodobnosť k stĺpcom, ktoré majú podobné alebo presne rovnaké názvy. Ak teda chcete zvýšiť pravdepodobnosť vytvorenia vzťahu, môžete skúsiť premenovať stĺpce v importovaných údajoch na názvy podobné názvom stĺpcov v existujúcich tabuľkách. Ak program Excel nájde viacero možných vzťahov, nevytvorí vzťah.
Tieto informácie vám môžu pomôcť pochopiť, prečo sa nezistia všetky vzťahy, alebo ako zmeny v metaúdajoch (ako napríklad názov poľa a typy údajov) môžu zlepšiť výsledky automatického zisťovania vzťahov. Ďalšie informácie nájdete v článku Riešenie problémov so vzťahmi.
Automatické zisťovanie pomenovaných množín
Vzťahy medzi pomenovanými množinami a súvisiacimi poľami v kontingenčnej tabuľke sa nezisťujú automaticky. Tieto vzťahy je možné vytvoriť manuálne. Ak chcete použiť automatické zisťovanie vzťahov, odstráňte všetky pomenované množiny a pridajte jednotlivé polia z pomenovanej množiny priamo do kontingenčnej tabuľky.
Určovanie vzťahov
V niektorých prípadoch sa vzťahy medzi tabuľkami automaticky zreťazia. Ak napríklad vytvoríte vzťah medzi prvými dvoma množinami tabuliek uvedenými nižšie, určí sa existencia vzťahu medzi zvyšnými dvoma tabuľkami a automaticky sa vytvorí určitý vzťah.
Products a Category (vytvorené manuálne)
Category a SubCategory (vytvorené manuálne)
Products a SubCategory (určí sa vzťah)
Na automatické zreťazenie vzťahov je nutné, aby vzťahy mali jeden smer, ako je uvedené vyššie. Ak počiatočné vzťahy boli napríklad medzi tabuľkami Sales a Products alebo Sales a Customers, vzťah sa neurčí. Je to tak preto, že vzťah medzi tabuľkami Products a Customers je vzťah „many-to-many“.