PowerPivot umožňuje využívať tzv. linkované tabuľky z Excelu vo svojom dátovom modeli. Sú to tabuľky, resp. pomenované oblasti, ktoré sa nachádzajú v hárkoch Excelu. A načo sú nám vlastne linkované tabuľky, keď vieme naimportovať dáta do PowerPivotu priamo z Excelu? Majú totiž veľmi široké využitie pri bežných aj pokročilých analýzach v PowerPivote. Preto si na ne teraz trochu posvietime a ukážeme si aj ich praktické využitie.

Linkované tabuľky sú tabuľky, ktoré sú živo prepojené s Excelom. Ich výhodou je to, že keď sa niečo v nich zmení, tak sa táto zmena okamžite premietne aj do PowerPivotu, a odtiaľ do všetkého, čo od neho závisí – najmä kontingenčných tabuliek. Používajú sa na tieto všetky účely:

1. Dotiahnutie vlastných externých číselníkov do PowerPivotu,
2. Parametrizácia PowerPivotu,
3. Náhrada za funkcionalitu chýbajúcu v PowerPivote,
4. Analýza scenárov a dynamická analytika,
5. Preklady dát, vlastné triedenie dát a mnohé iné.

Jednoznačné výhody oproti importu z Excelu sú takéto:

1. nemusíte po každej zmene ukladať importovaný excelovský súbor a potom aktualizovať údaje v PowerPivote,
2. zmeny v štruktúre tabuľky v Exceli sa prenášajú automaticky do PowerPivotu – a vy nemusíte meniť nastavenie importu,
3. dátové typy z Excelu sa vždy správne prenesú aj do PowerPivotu, čo pri importe nie je vždy možné.

Ideme si teda ukázať, ako sa s týmito linkovanými tabuľkami pracuje.

Otvorte si niektorý zo súborov z predchádzajúcich článkov, alebo použite tento vzorový súbor. Do oblasti hodnôt dajte merítko Obrat z tabuľky Objednávky, a do oblasti riadkov dajte hierarchiu Rok-Mesiac-Deň z tabuľky Čas. Keď teraz rozkliknete v kontingenčke napr. rok 2002, všimnite si, že tam máme názvy mesiacov po anglicky:

My by sme ich ale chceli po slovensky. A preto si najprv v Exceli vytvoríme linkovanú tabuľku, ktorá bude zaisťovať tento preklad, potom ju dotiahneme do PowerPivotu, prepojíme s ostatnými tabuľkami v dátovom modeli, a spravíme v ňom potrebné zmeny.

Na linkované tabuľky môžete použiť ľubovoľný hárok Excelu. Jeden z odporúčaných postupov je taký, že by ste mali mať osobitný hárok na tieto tabuľky, ktorý sa bude volať napr. Čiselníky. Pretože časom budete mať vo Vašom dátovom modeli viacero takýchto tabuliek, a v Exceli budete mať viacero hárkov s grafmi a analýzami. A aby ste sa v tom časom nestratili a dobre sa to udržiavalo, tak si na to vytvorte osobitný hárok. Vytvoríme ho teda aj my, a nazveme ho Číselníky:

Potom si v tomto hárku vytvoríme nasledovnú tabuľku:

1. Do prvého riadku dáme názvy stĺpcov tabuľky – “Anglický názov” a “Slovenský názov”,
2. Do ďalších 12 riadkov postupne vypíšte názvy mesiacov – do prvého stĺpca anglické názvy mesiacov (presne tak ako ich máte v PowerPivote), a do druhého stĺpca slovenské názvy (tak ako chcete, aby sa zobrazovali po novom v PowerPivote, resp. v kontingenčke).

Následne označte myšou celú tabuľku vrátane názvu stĺpcov, a stlačte Ctrl+T. Vyskočí na Vás takéto okno:

V ňom zakliknite políčko Tabuľka obsahuje hlavičky, a kliknite na tlačítko OK.

Toto nám vytvorí z označených buniek pomenovanú oblasť, ktorá potom už pôjde pripojiť do PowerPivotu. Dajte si ale ešte pozor na jednu chybu, ktorá sa vyskytuje v Exceli 2013 – kontingenčka totiž neberie ohľad na názov linkovanej tabuľky v PowerPivote, a zobrazuje ju v ponuke názvom pomenovanej oblasti v Exceli. Ktorá sa štandardne volá Tabuľka1. Preto potrebujeme premenovať túto pomenovanú oblasť na názov Mesiace ešte predtým, ako ju pripojíme do PowerPivotu. Choďte teda vľavo hore do menu Excelu, a tam nájdete políčko Názov tabuľky, v ktorom budete mať napísané Tabuľka1 (ak ho tam nevidíte, kliknite na tabuľku, a choďte v menu v Exceli do záložky Návrh). Tento názov zmeňte na Mesiace a stlačte Enter. Výsledok bude vyzerať takto:

Teraz si teda konečne môžeme pripojiť túto tabuľku do PowerPivotu. Choďte v menu Excelu do záložky PowerPivot, a kliknite na ikonku Pridať do modelu údajov:

Následne Vás to prepne do PowerPivotu, vytvorí novú záložku v dátovom modeli pre túto tabuľku, a naimportuje do nej dáta z Excelu. Všimnite si, ako vyzerá záložka pre takúto tabuľku:

V záložke je malá ikonka reťaze, ktorá symbolizuje, že ide o linkovanú tabuľku, a že to prepojenie je živé. To znamená, že keď sa teraz vrátite do Excelu, niečo v tej tabuľke zmeníte, a potom sa vrátite do PowerPivotu, tak PowerPivot si tie dáta automaticky a hneď zaktualizuje. A ak ste zmenili štruktúru tejto tabuľky – napr. ste pridali alebo odobrali stĺpce – aktualizuje si to tiež. A to je bombová funkcionalita, ktorá ušetrí obrovské množstvo času pri zmenách v modeli.

Keď teda máme túto tabuľku pripojenú do dátového modelu, môžeme s ňou ďalej pracovať ako s každou inou tabuľkou v modeli. Najprv si ju teda prepojíme s dátumovou tabuľkou Čas, aby PowerPivot vedel, ako ich má dokopy používať. Choďte do diagramového zobrazenia, a prepojte stĺpec MonthName v tabuľke Čas so stĺpcom “Anglický názov” v tabuľke Mesiace:

V ďalšom kroku si dotiahneme “preložený” mesiac z našej linkovanej tabuľky do tabuľky Čas. Spravíme to cez nový vypočítaný stĺpec, do ktorého dáme tento vzorec:

=RELATED(Mesiace[Slovenský názov])

Tento stĺpec premenujeme na Mesiac, a výsledok bude vyzerať takto:

Tomuto stĺpcu ale ešte musíme nastaviť triedenie pri zobrazovaní, podľa stĺpca MonthNumberOfYear, aby sa nám preložené mesiace zobrazovali podľa ich reálneho poradia v roku, a nie podľa abecedy (tak sme to v tomto článku robili pre stĺpec MonthName). Nastavte teda tomuto stĺpcu, aby sa triedil podľa stĺpca MonthNumberOfYear.

Posledným krokom bude nahradenie stĺpca MonthName stĺpcom Mesiac v hierarchii Rok-Mesiac-Deň. Prejdite teda do diagramového zobrazenia, do tabuľky Čas, a tam:

1. v hierarchii Rok-Mesiac-Deň odstráňte stĺpec Mesiac (MonthName),
2. pridajte do tej istej hierarchie stĺpec Mesiac, a umiestnite ho medzi stĺpce Rok a Deň.

Takto dosiahneme to, že teraz táto hierarchia bude používať preložený stĺpec, dotiahnutý z našej linkovanej tabuľky, a bude mať dokonca ten istý názov ako starý stĺpec v hierarchii. Teraz už máme spravené všetko potrebné, a môžeme sa presunúť naspäť do Excelu. Po prepnutí do Excelu sa nám dáta v kontingenčke automaticky aktualizujú, a odteraz sa v kontingenčke budú zobrazovať slovenské mesiace:

A takto si môžete nielen preložiť hodnoty v tabuľkách, ktoré ste si dotiahli z databáz, ale môžete túto techniku použiť na rýchle dotiahnutie údajov z Excelu na niektorú z techník, ktorú sme spomínali v úvode článku. Dajte si však pozor, ak chcete používať PowerPivot v serverovej verzii pri nasadení na Analysis Services Tabular – tam tieto linkované tabuľky podporované nie sú. Vo všetkých ostatných prípadoch ale podporované sú, a nebojte sa ich smelo používať – fungujú perfektne, a umožňujú robiť s PowerPivotom oveľa viac, ako jeho tvorcovia s nimi plánovali. A keďže ide o veľmi univerzálnu techniku, jej využitiu budeme v budúcnosti venovať ešte niekoľko článkov. Dovtedy – skúšajte a hrajte sa, určite nájdete časom ešte oveľa viac využití 🙂