Kurz: Rozšíření relací datového modelu pomocí Excelu, Power Pivotu a jazyka DAX

Tento kurz se skládá z následujících částí:

• Přidání relace pomocí zobrazení diagramu v Power Pivotu

• Rozšíření datového modelu pomocí počítaných sloupců

• Create a hierarchy

• Použití hierarchií v kontingenčních tabulkách

• Kontrolní bod a kvíz

Na konci tohoto kurzu je kvíz, kde si můžete otestovat, co jste se naučili.

V této řadě kurzů používáme data popisující olympijské medaile, hostitelské země a různé olympijské sportovní události. Tato řada obsahuje následující kurzy:

1. Import dat do Excelu a Vytvoření datového modelu

2. Rozšíření relací datového modelu pomocí Excelu Power Pivot a DAX

3. Vytváření sestav Power View založených na mapě

4. Zahrnutí internetových dat a nastavení výchozích možností sestav Power View

5. Nápověda k Power Pivotu

6. Vytváření skvělých sestav Power View – Část 2

Měli byste je absolvovat v tomto pořadí.

Tyto kurzy používají Excel 2013 s povoleným doplňkem Power Pivot. Další informace o Excelu 2013 získáte kliknutím sem. Pokud si chcete přečíst pokyny k povolení doplňku Power Pivot, klikněte sem.

Přidání relace pomocí zobrazení diagramu v Power Pivot

V této části použijete doplněk Microsoft Office Power Pivot v Excelu 2013 k rozšíření modelu. Použití zobrazení diagramu v Microsoft SQL Server PowerPivot pro Excel usnadňuje vytváření relací. Nejprve se ujistěte, že máte povolený doplněk Power Pivot.

Poznámka: Doplněk Power Pivot v Microsoft Excelu 2013 je součástí Office Professional Plus. Další informace najdete v článku Spuštění doplňku Power Pivot v Microsoft Excel 2013.

Přidání Power Pivot na pás karet Excelu povolením doplňku Power Pivot

Když je Power Pivot povolená, zobrazí se v Excelu 2013 karta na pásu karet s názvem POWER PIVOT. Pokud chcete povolit Power Pivot, postupujte takto.

1. Přejděte na soubor > možnosti > doplňky.

2. V poli Spravovat v dolní části klikněte na Doplňky modelu COM> Go.

3. Zaškrtněte políčko Microsoft Office Power Pivot v Microsoft Excelu 2013 a klikněte na OK.

Na pásu karet Excelu je teď karta POWER PIVOT .

Přidání relace pomocí zobrazení diagramu v Power Pivot

Excelový sešit obsahuje tabulku s názvem Hostitelé. Hostitele jsme naimportovali zkopírováním a vložením do Excelu a potom jsme data naformátovali jako tabulku. Abychom mohli přidat tabulku Hosts do datového modelu, musíme vytvořit relaci. Použijeme Power Pivot k vizuálnímu znázornění relací v datovém modelu a následnému vytvoření relace.

1. V Excelu klikněte na kartu Hostitelé a nastavte ji jako aktivní list.

2. Na pásu karet vyberte POWER PIVOT > Tabulky > Přidat do datového modelu. Tento krok přidá tabulku Hosts do datového modelu. Otevře se také doplněk Power Pivot, který použijete k provedení zbývajících kroků v této úloze.

3. Všimněte si, že okno Power Pivot zobrazuje všechny tabulky v modelu, včetně hostitelů. Proklikáte se mezi několika tabulkami. V Power Pivot můžete zobrazit všechna data, která váš model obsahuje, i když se nezobrazují v excelových listech, jako jsou níže uvedená data Disciplíny, Události a Medaile, a také S_Teams,W_Teamsa Sport.
   
   

4. V okně Power Pivot klikněte v části Zobrazení na Zobrazení diagramu.

5. Pomocí panelu snímků změňte velikost diagramu tak, abyste viděli všechny objekty v diagramu. Přetáhněte záhlaví tabulek tak, aby byly viditelné a umístěné vedle sebe. Všimněte si, že čtyři tabulky nesouvisejí se zbývajícími tabulkami: Hosts, Events, W_Teams a S_Teams.
   
   

6. Všimněte si, že tabulka Medals i tabulka Events mají pole s názvem DisciplineEvent. Při další kontrole zjistíte, že pole DisciplineEvent v tabulce Events se skládá z jedinečných, neopakovaných hodnot.

1. Vytvořte relaci mezi tabulkou Medals a tabulkou Events . V zobrazení diagramu přetáhněte pole DisciplineEvent z tabulky Events do pole DisciplineEvent v části Medals. Zobrazí se mezi nimi čára, která označuje, že byla vytvořena relace.

2. Klikněte na čáru, která spojuje události a medaile. Zvýrazněná pole definují relaci, jak je znázorněno na následující obrazovce.
   
   
   

3. K připojení hostitelů k datovému modelu potřebujeme pole s hodnotami, které jednoznačně identifikují každý řádek v tabulce Hostitelé . Pak můžeme prohledat náš datový model a zjistit, jestli stejná data existují v jiné tabulce. Když se podíváme do zobrazení diagramu, nemůžeme to udělat. S vybranou možností Hostitelé přepněte zpět do zobrazení dat.

4. Po prozkoumání sloupců zjistíme, že hostitelé nemají sloupec jedinečných hodnot. Budeme ho muset vytvořit pomocí počítaný sloupec a jazyka DAX (Data Analysis Expressions).

Je hezké, když data v datovém modelu obsahují všechna pole potřebná k vytváření relací a k vizualizaci dat v Power View nebo kontingenčních tabulkách. Tabulky ale nejsou vždy tak kooperativní, takže další část popisuje, jak vytvořit nový sloupec pomocí jazyka DAX, který se dá použít k vytvoření relace mezi tabulkami.

Rozšíření datového modelu pomocí počítaných sloupců

Aby bylo možné vytvořit relaci mezi tabulkou Hosts a datovým modelem a rozšířit tak datový model tak, aby zahrnoval tabulku Hostitelé , musí mít hostitelé pole, které jednoznačně identifikuje každý řádek. Kromě toho musí toto pole odpovídat poli v datovém modelu. Tato odpovídající pole, jedno v každé tabulce, umožňují přidružit data tabulek.

Vzhledem k tomu, že tabulka Hosts takové pole neobsahuje, musíte ho vytvořit. Pokud chcete zachovat integritu datového modelu, nemůžete použít Power Pivot k úpravě nebo odstranění existujících dat. Nové sloupce ale můžete vytvořit pomocí počítaných polí založených na existujících datech.

Když si projdeme tabulku Hostitelé a pak se podíváme na další tabulky datového modelu, najdeme vhodného kandidáta na jedinečné pole, které bychom mohli vytvořit v hostitelích a pak přidružit k tabulce v datovém modelu. Obě tabulky budou vyžadovat nový počítaný sloupec, aby splňovaly požadavky nezbytné k vytvoření relace.

V části Hostitelé můžeme vytvořit jedinečný počítaný sloupec tak, že zkombinujeme pole Edice (rok konání olympijských her) a pole Season (Léto nebo Zima). V tabulce Medals je také pole Edice a pole Season, takže pokud v každé z těchto tabulek vytvoříme počítaný sloupec, který kombinuje pole Edice a Sezóna, můžeme vytvořit relaci mezi hostiteli a medailemi. Následující obrazovka ukazuje tabulku Hostitelé s vybranými poli Edice a Sezóna.

Vytváření počítaných sloupců pomocí jazyka DAX

Začněme tabulkou Hosts . Cílem je vytvořit počítaný sloupec v tabulce Hosts a potom v tabulce Medals , který se dá použít k vytvoření relace mezi nimi.

V Power Pivot můžete k vytváření výpočtů použít jazyk DAX (Data Analysis Expressions). DAX je jazyk vzorců pro Power Pivot a kontingenční tabulky, který je určený pro relační data a kontextovou analýzu dostupnou v Power Pivot. Vzorce DAX můžete vytvořit v novém Power Pivot sloupci a v oblasti výpočtu v Power Pivot.

1. V Power Pivot vyberte DOMŮ > Zobrazení > zobrazení dat , abyste se ujistili, že je vybrané zobrazení dat a není v zobrazení diagramu.

2. Vyberte tabulku Hosts v Power Pivot. Vedle existujících sloupců je prázdný sloupec s názvem Přidat sloupec. Power Pivot tento sloupec poskytuje jako zástupný symbol. Existuje mnoho způsobů, jak přidat nový sloupec do tabulky v Power Pivot. Jedním z nich je jednoduše vybrat prázdný sloupec s názvem Přidat sloupec.
   
   
   

3. Do řádku vzorců zadejte následující vzorec DAX. Funkce CONCATENATE kombinuje dvě nebo více polí do jednoho. Automatické dokončování vám při psaní pomůže zadat plně kvalifikované názvy sloupců a tabulek a vypíše funkce, které jsou k dispozici. Pomocí karty vyberte návrhy automatického dokončování. Můžete také jednoduše kliknout na sloupec při psaní vzorce a Power Pivot vloží název sloupce do vzorce.
   
   =CONCATENATE([Edition],[Season])

4. Po dokončení vytváření vzorce ho přijměte stisknutím klávesy Enter.

5. Hodnoty se vyplní pro všechny řádky v počítaném sloupci. Když se v tabulce posunete dolů, uvidíte, že každý řádek je jedinečný, takže jsme úspěšně vytvořili pole, které jedinečně identifikuje každý řádek v tabulce Hosts . Taková pole se nazývají primární klíč.

6. Přejmenujme počítaný sloupec na EditionID. Libovolný sloupec můžete přejmenovat tak, že na něj pokliknete nebo na něj kliknete pravým tlačítkem a zvolíte Přejmenovat sloupec. Po dokončení bude tabulka Hostitelé v Power Pivotu vypadat jako na následující obrazovce.
   
   

Tabulka Hosts je připravená. Dále vytvoříme počítaný sloupec v Medals , který odpovídá formátu sloupce EditionID, který jsme vytvořili v hostitelích, abychom mezi nimi mohli vytvořit relaci.

1. Začněte vytvořením nového sloupce v tabulce Medals , jako jsme to udělali u hostitelů. V Power Pivot vyberte tabulku Medals a klikněte na Návrh > Sloupce > Přidat. Všimněte si, že je vybraná možnost Přidat sloupec . To má stejný účinek, jako když jednoduše vyberete Přidat sloupec.

2. Sloupec Edice v Medals má jiný formát než sloupec Edice v hostitelích. Než zkombinujeme nebo zřetědíme sloupec Edice se sloupcem Season a vytvoříme sloupec EditionID, musíme vytvořit zprostředkující pole, které získá edici do správného formátu. Do řádku vzorců nad tabulkou zadejte následující vzorec DAX.

   = YEAR([Edition])
   

3. Po vytvoření vzorce stiskněte Enter. Hodnoty se vyplní pro všechny řádky v počítaném sloupci na základě zadaného vzorce. Pokud tento sloupec porovnáte se sloupcem Edice v hostitelích, uvidíte, že tyto sloupce mají stejný formát.

4. Přejmenujte sloupec tak, že kliknete pravým tlačítkem na CalculatedColumn1 a vyberete Přejmenovat sloupec. Zadejte Year a stiskněte Enter.

5. Když jste vytvořili nový sloupec, Power Pivot přidali další zástupný sloupec s názvem Přidat sloupec. Dále chceme vytvořit počítaný sloupec EditionID, takže vyberte Přidat sloupec. Do řádku vzorců zadejte následující vzorec DAX a stiskněte Enter.
   
   =CONCATENATE([Year],[Season])

6. Přejmenujte sloupec tak, že dvakrát kliknete na CalculatedColumn1 a zadáte EditionID.

7. Seřaďte sloupec ve vzestupném pořadí. Tabulka Medals v Power Pivot teď vypadá jako na následující obrazovce.
   
   
   

Všimněte si, že v poli Medals Table EditionID se opakuje mnoho hodnot. To je v pořádku a očekávané, protože během každého ročníku olympiády (nyní reprezentované hodnotou EditionID) bylo uděleno mnoho medailí. V tabulce Medals je jedinečná každá udělená medaile. Jedinečný identifikátor každého záznamu v tabulce Medals a jeho určený primární klíč je pole MedalKey.

Dalším krokem je vytvoření relace mezi hostiteli a medals.

Vytvoření relace pomocí počítaných sloupců

Dále použijeme počítané sloupce, které jsme vytvořili, k vytvoření vztahu mezi hostiteli a medals.

1. V okně Power Pivot na pásu karet vyberte Zobrazení > diagramu Domů > Zobrazení . Mezi zobrazením mřížky a zobrazením diagramu můžete také přepínat pomocí tlačítek v dolní části okna PowerView, jak je znázorněno na následující obrazovce.
   
   

2. Rozbalte položku Hostitelé , abyste mohli zobrazit všechna jeho pole. Vytvořili jsme sloupec EditionID, který funguje jako primární klíč tabulky Hosts (jedinečné, neopakované pole), a vytvořili jsme sloupec EditionID v tabulce Medals , abychom mezi nimi mohli vytvořit relaci. Musíme je najít oba a vytvořit relaci. Power Pivot poskytuje funkci Najít na pásu karet, takže v datovém modelu můžete hledat odpovídající pole. Na následující obrazovce se zobrazuje okno Najít metadata se zadaným ID edice do pole Najít .
   

3. Umístěte tabulku Hostitelé tak, aby byla vedle medals.

4. Přetáhněte sloupec EditionID v medals do sloupce EditionID v hostitelích. Power Pivot vytvoří relaci mezi tabulkami na základě sloupce EditionID a nakreslí mezi těmito dvěma sloupci čáru označující relaci.
   
   

V této části jste se naučili novou techniku přidávání nových sloupců, vytvořili jste počítaný sloupec pomocí jazyka DAX a použili jste tento sloupec k vytvoření nové relace mezi tabulkami. Tabulka Hosts je teď integrovaná do datového modelu a její data jsou k dispozici pro kontingenční tabulku v listu List1. Přidružená data můžete také použít k vytváření dalších kontingenčních tabulek, kontingenčních grafů, sestav Power View a mnoha dalších.

Create a hierarchy

Většina datových modelů zahrnuje data, která jsou ze své podstaty hierarchická. Mezi běžné příklady patří data kalendáře, zeměpisná data a kategorie produktů. Vytváření hierarchií v rámci Power Pivot je užitečné, protože můžete přetáhnout jednu položku do sestavy – hierarchii – a nemusíte sestavovat a seřaďte stejná pole pořád dokola.

Data z olympijských her jsou také hierarchická. Je užitečné pochopit hierarchii olympijských her, pokud jde o sport, disciplíny a události. Pro každý sport existuje jedna nebo více přidružených disciplín (někdy jich je mnoho). A pro každou disciplínu existuje jedna nebo více událostí (opět, někdy je v každé disciplíně mnoho událostí). Následující obrázek znázorňuje hierarchii.

V této části vytvoříte dvě hierarchie v rámci olympijských dat, která jste v tomto kurzu používali. Pomocí těchto hierarchií pak zjistíte, jak hierarchie usnadňují uspořádání dat v kontingenčních tabulkách a v dalším kurzu v Nástroji Power View.

Vytvoření hierarchie sportů

1. V Power Pivot přepněte do zobrazení diagramu. Rozbalte tabulku Události , abyste lépe viděli všechna její pole.

2. Stiskněte a podržte klávesu Ctrl a klikněte na pole Sport, Disciplína a Událost. Po výběru těchto tří polí klikněte pravým tlačítkem myši a vyberte Vytvořit hierarchii. V dolní části tabulky se vytvoří uzel nadřazené hierarchie Hierarchy 1 a vybrané sloupce se zkopírují pod hierarchii jako podřízené uzly. Ověřte, že se sport zobrazuje jako první v hierarchii, pak disciplína a pak událost.

3. Poklikejte na název Hierarchie1 a zadáním SDE přejmenujte novou hierarchii. Teď máte hierarchii, která zahrnuje sport, disciplínu a událost. Tabulka Events teď vypadá jako na následující obrazovce.
   
   

Vytvoření hierarchie umístění

1. V zobrazení diagramu v Power Pivot vyberte tabulku Hostitelé a v záhlaví tabulky klikněte na tlačítko Vytvořit hierarchii, jak je znázorněno na následující obrazovce.
   
   
   V dolní části tabulky se zobrazí prázdný nadřazený uzel hierarchie.

2. Jako název nové hierarchie zadejte Umístění .

3. Existuje mnoho způsobů, jak přidat sloupce do hierarchie. Přetáhněte pole Season, City a NOC_CountryRegion na název hierarchie (v tomto případě Umístění), dokud se název hierarchie zvýrazní. Potom je přidejte uvolněním.

4. Klikněte pravým tlačítkem na EditionID a vyberte Přidat do hierarchie. Zvolte Umístění.

5. Ujistěte se, že podřízené uzly hierarchie jsou v pořadí. Shora dolů by mělo být pořadí: Season, NOC, City, EditionID. Pokud jsou podřízené uzly mimo pořadí, jednoduše je přetáhněte do odpovídajícího pořadí v hierarchii. Tabulka by měla vypadat jako na následující obrazovce.
   

Datový model teď obsahuje hierarchie, které je možné v sestavách dobře využít. V další části se dozvíte, jak tyto hierarchie můžou zrychlit a konzistentněji vytvářet sestavy.

Použití hierarchií v kontingenčních tabulkách

Když teď máme sportovní hierarchii a hierarchii umístění, můžeme je přidat do kontingenčních tabulek nebo nástroje Power View a rychle získat výsledky, které obsahují užitečná seskupení dat. Před vytvořením hierarchií jste museli do kontingenční tabulky přidat jednotlivá pole a uspořádat tato pole tak, jak chcete, aby se zobrazovala.

V této části použijete hierarchie vytvořené v předchozí části k rychlému upřesnění kontingenční tabulky. Pak vytvoříte stejné zobrazení kontingenční tabulky pomocí jednotlivých polí v hierarchii, abyste mohli porovnávat hierarchie s použitím jednotlivých polí.

1. Zpět do Excelu.

2. Na listu List1 odeberte pole z oblasti ŘÁDKY pole kontingenční tabulky a potom odeberte všechna pole z oblasti SLOUPCE. Ujistěte se, že je vybraná kontingenční tabulka (ta je teď poměrně malá, takže můžete vybrat buňku A1 a ujistit se, že je vybraná kontingenční tabulka). Jediná zbývající pole v polích kontingenční tabulky jsou Medal v oblasti FILTRY a Počet medailí v oblasti HODNOTY. Téměř prázdná kontingenční tabulka by měla vypadat jako na následující obrazovce.

   

3. Z oblasti Pole kontingenční tabulky přetáhněte SDE z tabulky Události do oblasti ŘÁDKY. Potom přetáhněte Umístění z tabulky Hostitelé do oblasti SLOUPCE . Když přetáhnete tyto dvě hierarchie, kontingenční tabulka se naplní velkým množstvím dat, která jsou všechna uspořádaná v hierarchii, kterou jste definovali v předchozích krocích. Vaše obrazovka by měla vypadat jako na následující obrazovce.
   

4. Pojďme tato data trochu vyfiltrovat a podívat se jenom na prvních deset řádků událostí. V kontingenční tabulce klikněte na šipku v části Popisky řádků, kliknutím na (Vybrat vše) odeberte všechny výběry a potom klikněte na pole vedle prvních deseti sportů. Vaše kontingenční tabulka teď vypadá jako na následující obrazovce.
   

5. Některé z těchto sportů můžete rozbalit v kontingenční tabulce, což je nejvyšší úroveň hierarchie SDE, a zobrazit informace o další úrovni v hierarchii (disciplíně). Pokud pro danou disciplínu existuje nižší úroveň v hierarchii, můžete disciplínu rozšířit a zobrazit její události. To samé můžete udělat pro hierarchii Umístění, jejíž nejvyšší úrovní je Season (Sezóna), která se v kontingenční tabulce zobrazuje jako Léto a Zima. Když rozšíříme vodní sport, uvidíme všechny jeho prvky dětské disciplíny a jejich data. Když rozšíříme disciplínu potápění pod vodními sporty, uvidíme také jeho dětské události, jak je znázorněno na následující obrazovce. To samé můžeme udělat pro Vodní pólo a uvidíme, že má jenom jednu událost.
   

Přetažením těchto dvou hierarchií jste rychle vytvořili kontingenční tabulku se zajímavými a strukturovanými daty, která můžete procházet, filtrovat a uspořádat.

Teď vytvoříme stejnou kontingenční tabulku bez výhod hierarchií.

1. V oblasti Pole kontingenční tabulky odeberte umístění z oblasti SLOUPCE. Potom odeberte SDE z oblasti ŘÁDKY. Jste zpátky k základní kontingenční tabulce.

2. Z tabulky Hostitelé přetáhněte season, City, NOC_CountryRegion a EditionID do oblasti COLUMNS a uspořádejte je v daném pořadí shora dolů.

3. Z tabulky Události přetáhněte sport, disciplínu a událost do oblasti ŘÁDKY a uspořádejte je v daném pořadí shora dolů.

4. V kontingenční tabulce vyfiltrujte popisky řádků na prvních deset sportů.

5. Sbalte všechny řádky a sloupce, pak rozbalte položku Vodní sporty a pak potápění a vodní pólo . Váš sešit vypadá jako následující obrazovka.
   

Obrazovka vypadá podobně, s tím rozdílem, že jste do oblastí polí kontingenční tabulky přetáhli sedm jednotlivých polí, místo abyste přetáhli dvě hierarchie. Pokud jste jedinou osobou, která vytváří kontingenční tabulky nebo sestavy Power View založené na těchto datech, může se vám vytváření hierarchií zdát jen vhodné. Ale když sestavy vytváří mnoho lidí a musí přijít na správné pořadí polí, aby zobrazení byla správná, hierarchie se rychle stanou vylepšením produktivity a umožní konzistenci.

V dalším kurzu se naučíte používat hierarchie a další pole ve vizuálně poutavých sestavách vytvořených pomocí Nástroje Power View.

Kontrolní bod a kvíz

Shrnutí nových znalostí

Váš excelový sešit teď obsahuje datový model, který obsahuje data z více zdrojů a souvisí s existujícími poli a počítanými sloupci. Máte také hierarchie, které odrážejí strukturu dat v tabulkách, což usnadňuje a usnadňuje vytváření poutavých sestav.

Dozvěděli jste se, že vytváření hierarchií umožňuje určit základní strukturu dat a rychle používat hierarchická data v sestavách.

V dalším kurzu v této sérii vytvoříte vizuálně poutavé sestavy o olympijských medailích pomocí nástroje Power View. Provedete také více výpočtů, optimalizujete data pro rychlé vytváření sestav a importujete další data, aby byly tyto sestavy ještě zajímavější. Tady je odkaz:

Kurz 3: Vytváření sestav Power View založených na mapě

KVÍZ

Chcete zjistit, jak dobře si pamatujete, co jste se dozvěděli? Tady máte šanci. Následující kvíz je zaměřený na funkce, možnosti a požadavky, se kterými jste se v tomto kurzu seznámili. V dolní části stránky najdete odpovědi. Hodně štěstí!

Otázka 1: Které z následujících zobrazení umožňuje vytvořit relace mezi dvěma tabulkami?

O: Relace mezi tabulkami se vytvářejí v Power View.

B: Relace mezi tabulkami vytvoříte pomocí návrhového zobrazení v Power Pivot.

C: Relace mezi tabulkami se vytvářejí pomocí zobrazení mřížky v Power Pivot

D: Všechny výše uvedené možnosti.

Otázka 2: PRAVDA nebo NEPRAVDA: Relace mezi tabulkami můžete vytvořit na základě jedinečného identifikátoru vytvořeného pomocí vzorců jazyka DAX.

A: PRAVDA

B: NEPRAVDA

Otázka 3: Ve které z následujících možností můžete vytvořit vzorec DAX?

A: V oblasti výpočtu Power Pivot.

B: V novém sloupci v Power Pivot f.

C: V libovolné buňce Excelu 2013.

D: A i B.

Otázka 4: Které z následujících tvrzení o hierarchiích platí?

O: Když vytvoříte hierarchii, zahrnutá pole už nebudou k dispozici jednotlivě.

B: Když vytvoříte hierarchii, můžete zahrnutá pole včetně jejich hierarchie použít v klientských nástrojích tak, že hierarchii jednoduše přetáhnete do oblasti Power View nebo kontingenční tabulky.

C: Když vytvoříte hierarchii, podkladová data v datovém modelu se zkombinují do jednoho pole.

D: V Power Pivot nelze vytvářet hierarchie.

Odpovědi kvízu

1. Správná odpověď: D

2. Správná odpověď: A

3. Správná odpověď: D

4. Správná odpověď: B