V minulém článku jsme psali o Power Pivotu, jednom z nejdůležitějších doplňků pro takzvané "Self-service BI" v Excelu. Power Pivot je základním kamenem pro modelování dat v Excelu, ale k dispozici jsou také další doplňky, které výrazně pomáhají při vytváření reportů v Excelu. V tomto článku se hlouběji podíváme na doplňek Power Query (dříve Data Explorer), který byl představen na konci roku 2013.

Power Query

Power Query (dále jen PQ) je primárně zaměřen na import dat do Excelu. Můžete si říct, že to je v Power Pivotu taky možné, tak proč si instalovat další doplňek. Protože PQ neslouží pouze k import dat, díky němu máte možnost data upravovat, transformovat a vybírat právě ta, která vás zajímají. Power Query se tímto stáva mocným ETL nástrojem pro import dat do datového modelu (nebo do listu v sešitě) z různých zdrojů.

Oproti Power Pivotu se zvýšilo množství externích datových zdrojů, nyní je možné jednoduše stahovat data z MySQL/Postgre databáze, Hadoop, Windows Azure HDInsight, SAP BI Universe, Facebooku, nebo z jakékoliv webové stránky. Takže si například můžete najít zajímavá data na wikipedii, například stránku o mistrovství světa v basketbalu, Power Query analyzuje celou stránku a pokud najde nějakou tabulku na stránce nabídne ji jako datový zdroj. Velice jednoduché a rychlé. Nabízí se také možnost využít službu hledání ve veřejně dostupných datech na internetu, která ale prozatím funguje převážně jen pro americká data.

Při výběru jednoho z datových zdrojů se otevře samostatné okno, ve kterém můžete data transformovat a následně je uložit. Při práci s daty v Power Query se všechny vaše akce ukládají jako seznam po sobě jdoucích kroků, takže přehledně vidíte, jaké transformace jste s daty provedli. Tudíž máte přehled co se s daty stalo od chvíle, kdy jste je nahrály do PQ. Možnosti transformací jsou velké, od filtrování řádků, odstranění duplicit, rozdělení sloupců, nahrazení hodnot až po transformování textu a vytváření dalších sloupců. Vše můžete vytvořit pouhým klikáním na položky v liště nebo pomocí nového programovacího jazyka M. Po dokončení všech kroků můžeme vybrat zda chceme výsledek uložit do listu v Excelu nebo do datového modelu. Následně se celý dotaz uloží, takže se k němu můžeme kdykoliv vrátit a změnit nebo přidat věci, které v něm chybí. Ve spojení s Power BI můžeme tyto dotazy sdílet a nechat další spolupracovníky využívat naše vytvořené dotazy. Více to bude rozvedeno v jednom z dalších dílů se zaměřením na Power BI.

Power Query také podporuje takzvaný "query folding", který výrazně zrychluje práci s daty a načítání do datového modelu. Pokud jsme připojeni například k MS SQL Serveru, "query folding" funguje tak, že interpretuje naše kroky přímo do SQL Serveru a ten vrátí jen data, která chceme. Když například importujeme tabulku z SQL Serveru do PQ, poté vyfiltrujeme jen některé řádky, nahradíme určité hodnoty, odstraníme nepotřebné sloupce a pak uložíme do datového modelu, tak při příštím volání stejného dotazu se tyto kroky nebudou odehrávat na našem počítači v PQ, ale automaticky se vytvoří SQL dotaz na zdrojovou databázi, která vrátí požadovaný výstup. Tím je načítání dotazu rychlejší než kdybychom vše řešili až po stažení všech dat do PQ.

Jazyk M

Jazyk M je procedurální a vychází z jakyza C#. Všechny kroky, které provedete můžete zapsat také ručně pomocí tohoto jazyka. Nabízí se vám tedy mocný nástroj na úpravu a přípravu dat pro samotné reporty. Jedním z příkladů může být vytvoření kalendářní tabulky, jen za pomocí Power Query. Stačí použít předdefinované funkce List.Dates, které se předají argumenty a ta vygeneruje seznam kalendářních dat. Tento seznam už můžete pro jednotlivé dny rozšiřovat o další sloupce jako jsou názvy dne v týdnu, názvy měsíce, rok a další informace, které jsou v kalendářní tabulce důležité.

Další možnost použití je naprogramování funkcí, které mohou vypočítat dodatečné hodnoty do tabulky. Tato funkce se chová jako jakýkoliv jiný PQ dotaz, jen čeká na vstupní hodnotu. Můžeme si tak například představit funkci, která po zádání PSČ vrátí kraj ze kterého PSČ je. Tento dotaz můžeme připojit k tabulce kontaktů, kde každý kontakt má uvedenou adresu. Po načtení dat připojíme nový sloupec, který bude volat naši vytvořenou funkci a ta bude vracet název kraje do kterého uvedený kontakt spadá. Vše stále běží jen v Excelu.

Power Query stále není ve všech směrech uplně dokonalý, ale Microsoft se ho snaží stále zlepšovat. Přibližně každý měsíc vychází novější verze, do které jsou přidány nové funkce a možnosti, nebo opraveny některé chyby verzí starších. Díky Power Query můžeme nahrávat data do Power Pivot modelu už ošetřená a zlepšit tím výsledný datový model a jeho použití. Pro Power Pivot, ve kterém nebyla možnost data vůbec nijak měnit, je to velice užitečný nástroj, který ušetří spousty práce.

Program Microsft Excel se stává čím dál více populárnější při použití v takzvaném "Self-service BI". Jeho přednostmi jsou rychlost, dostupnost a hlavně jednoduchost, se kterou se dají reporty vytvářet.

Pro podporu BI funkcí v Excelu se používají volně dostupné doplňky. Jsou jimi například Power Pivot, Power View, Power Query a Power Map. Na konci minulého roku dále Microsoft přišel s cloudovou službou Power BI, která rozšiřuje využtí Excelu jako BI nástroje.

V následujícím sérii článků se pokusíme představit všechny doplňky a popsat jejich funkce a využití.

Power Pivot

Power Pivot je nejdůležitějším z výše uvedených doplňků. Stará se o import dat z různých datových zdrojů, vytvoření datového modelu, určení vztahů mezi daty, provádění kalkulací nad daty a spoustu dalších. Jakmile je vytvořen datový model, můžeme za pomoci kontingenčních tabulek a grafů jednotlivé data procházet a vytvářet reporty podle daného zadání.

Jako zdroj pro import dat do Power Pivotu můžeme použít Databázové servery, Microsoft Acces, soubory ve formátu .csv nebo tabulky přímo z Excelu. Jakmile definujeme spojení k datům, tak můžeme importovat tabulky do Power Pivotu.

Jednou z velkých výhod Power Pivotu je, že nemá omezení pro počet řádků v tabulce, takže pohodlně zvládá data, která mají miliony záznamů. To vše díky tomu, že stahovaná data komprimuje v poměru přibližně 15:1. To znamená, že pokud je na disku soubor, který má uložená data pouze v tabulkách v Excelu a má velikost 150 MB, tak přesunutím dat do datového modelu v Power Pivot bude mít výsledný soubor velikost pouhých 10 MB. Ruku v ruce s velikostí výsledného souboru jde i velikost paměti potřebná pro práci s daty, díky tomu je práce výrazně rychlejší.Celý datový model a data jsou uloženy přímo v sešitu Excelu, takže jsou ihned k dispozici pro používání. Pokud je potřeba vidět aktuální data, stačí stiskem tlačítka aktualizovat datové zdroje a data načíst.

Data, která jsou uložena v datovém modelu nejdou změnit ani smazat. Předchází se tak neúplnostem v databázi a nekonzistenci dat.

Jazyk DAX

K zpracování uložených dat a vytváření analytických ukazatelů je v Power Pivotu k dispozici jazyk DAX (Data Analysis Expression). Pomocí tohoto jazyka je možné vytvářet složité kalkulace nad daty a tyto kalkulace pak použít v reportech. Tyto kalkulace jsou velice podobné vzorcům v Excelu, ale narozdíl od něj pracují s tabulkami a sloupci namísto buněk. DAX obsahuje některé z funkcí používaných ve vzorcích aplikace Excel a má další funkce určené pro práci s relačními daty a možnosti provádět dynamické agregace.

Prezentace dat

Pro prezentaci dat se používají kontingenční tabulky, které jsou svázány s datovým modelem. Data jde jednoduše procházet a agregovat. Důležitou výhodou oproti klasické kontingenční tabulce je zobrazování dat v čase a zobrazování agregací v časových úsecích. Na více ukázek se můžete podívat do sekce Ukázky

Jaké verze Excelu podporují Power Pivot?

Pro chod doplňku je nutné mít správnou verzi aplikace Excel, protože ne všechny sady Microsoft Office obsahují Excel s tímto doplňkem. Situace se liší s verzemi Microsoft Office.

Pro Microsoft Office 2010 (Excel 2010) jde doplňek stáhnout zdarma ze stránek Microsoftu a není zde žádné omezení v používání.

Od verze Office 2013 je Power Pivot přímo integrován v Excelu a není možné jej stáhnout. Problém je v tom, že není dostupný ve všech verzích Microsoft Office 2013. Nejjednodušší varianta je pořídit samostatnou verzi aplikace Excel, která obsahuje všechny doplňky (Power Pivot, Power View). Pokud ale potřebujete koupit celý kancelářský balík Office, tak je zapotřebí zakoupit verzi Microsoft Office Professional Plus 2013. Jiné verze v sobě Power Pivot nemají a tudíž ho nemůžete používat. Další možností je zakoupení edice Office 365 ProPlus edice, ve které jsou nástroje také součástí. 

Pro shrnutí, v kterých verzích Excelu 2013 je součástí Power Pivot:

• Samostatná verze Excel 2013

• Microsoft Office Professional Plus 2013

• Office 365 ProPlus

V následujícím článku si představíme další doplňky, které pomáhají při vytváření reportů v Excelu.