10 spôsobov, ako zlepšiť výkon Power Pivot

Keď zverejňujete zostavy Power Pivot na web, máte v úmysle poskytnúť svojmu publiku tú najlepšiu možnú skúsenosť. Veľká časť tejto skúsenosti je zabezpečenie dobrého výkonu. Slovo výkon (v súvislosti s aplikáciami a prehľadmi) je zvyčajne synonymom pre rýchlosť – alebo ako rýchlo aplikácia vykonáva určité akcie, ako je otváranie v prehliadači, spúšťanie dopytov alebo filtrovanie.

1Obmedzte počet riadkov a stĺpcov v tabuľkách údajových modelov.

Obrovský vplyv na výkon doplnku Power Pivot má počet stĺpcov, ktoré prinesiete alebo importujete do dátového modelu. Každý importovaný stĺpec je ďalšou dimenziou, ktorú musí Power Pivot spracovať pri načítavaní zošita. Neimportujte ďalšie stĺpce „pre každý prípad“ – ak si nie ste istí, že niektoré stĺpce použijete, jednoducho ich nevkladajte. Tieto stĺpce je dosť jednoduché pridať neskôr, ak zistíte, že ich potrebujete.

Viac riadkov znamená viac údajov na načítanie, viac údajov na filtrovanie a viac údajov na výpočet. Ak nemusíte, nevyberajte celú tabuľku. Pomocou dotazu alebo zobrazenia v zdrojovej databáze filtrujte iba riadky, ktoré potrebujete importovať. Napokon, prečo importovať 400 000 riadkov údajov, keď môžete použiť jednoduchú klauzulu WHERE a importovať iba 100 000?

2 Namiesto tabuliek použite zobrazenia.

Keď už hovoríme o zobrazeniach, pre najlepší postup používajte zobrazenia vždy, keď je to možné.

Hoci tabuľky sú transparentnejšie ako zobrazenia – umožňujú vám vidieť všetky nespracované, nefiltrované údaje – dodávajú sa so všetkými dostupnými stĺpcami a riadkami, či už ich potrebujete alebo nie. Aby ste udržali svoj dátový model Power Pivot na spravovateľnej veľkosti, často ste nútení urobiť ďalší krok, a to explicitné odfiltrovanie stĺpcov, ktoré nepotrebujete.

Zobrazenia môžu poskytnúť nielen čistejšie a užívateľsky prívetivejšie údaje, ale tiež pomôžu zefektívniť dátový model Power Pivot obmedzením množstva importovaných údajov.

3Vyhýbajte sa viacúrovňovým vzťahom.

Počet vzťahov aj počet vrstiev vzťahov majú vplyv na výkon zostáv Power Pivot. Pri vytváraní modelu postupujte podľa osvedčených postupov a majte jednu tabuľku faktov obsahujúcu predovšetkým kvantitatívne numerické údaje (fakty) a tabuľky dimenzií, ktoré sa priamo týkajú faktov. Vo svete databázy je táto konfigurácia hviezdicovou schémou, ako je znázornené.

Vyhnite sa vytváraniu modelov, kde tabuľky rozmerov súvisia s inými tabuľkami rozmerov.

4Nechajte back-end databázové servery, aby to spravili.

Väčšina analytikov Excelu, ktorí sú v Power Pivot noví, má tendenciu získavať nespracované údaje priamo z tabuliek na svojich externých databázových serveroch. Keď sú nespracované údaje v doplnku Power Pivot, vytvárajú vypočítané stĺpce a miery na transformáciu a agregáciu údajov podľa potreby. Používatelia napríklad bežne získavajú údaje o výnosoch a nákladoch a potom vytvárajú vypočítaný stĺpec v doplnku Power Pivot na výpočet zisku.

Prečo teda prinútiť Power Pivot vykonať tento výpočet, keď to mohol zvládnuť back-end server? Realita je taká, že back-end databázové systémy, ako je SQL Server, majú schopnosť tvarovať, agregovať, čistiť a transformovať údaje oveľa efektívnejšie ako Power Pivot. Prečo nevyužiť ich výkonné schopnosti na masírovanie a tvarovanie údajov pred ich importovaním do doplnku Power Pivot?

Namiesto sťahovania nespracovaných údajov tabuľky zvážte využitie dotazov, zobrazení a uložených procedúr na vykonanie čo najväčšieho množstva agregácie údajov a drvenia. Toto využitie znižuje množstvo spracovania, ktoré bude musieť vykonať Power Pivot, a prirodzene zvyšuje výkon.

5Dávajte si pozor na stĺpce s nerozlíšenými hodnotami.

Stĺpce, ktoré majú vysoký počet jedinečných hodnôt, sú obzvlášť náročné na výkon doplnku Power Pivot. Stĺpce ako ID transakcie, ID objednávky a číslo faktúry sú často zbytočné v zostavách a informačných paneloch Power Pivot na vysokej úrovni. Takže pokiaľ nie sú potrebné na vytvorenie vzťahov s inými tabuľkami, vynechajte ich zo svojho modelu.

6Obmedzte počet výrezov v prehľade.

Slicer je jednou z najlepších nových funkcií Business Intelligence (BI) Excelu za posledné roky. Pomocou rýchlych filtrov môžete svojmu publiku poskytnúť intuitívne rozhranie, ktoré umožňuje interaktívne filtrovanie zostáv a panelov programu Excel.

Jednou z užitočnejších výhod krájača je, že reaguje na iné krájače a poskytuje efekt kaskádového filtra. Obrázok napríklad ilustruje nielen to, že kliknutie na Stredozápad v segmente Región filtruje kontingenčnú tabuľku, ale aj to, že segmentácia trhu tiež odpovedá zvýraznením trhov, ktoré patria do regiónu Stredozápad. Microsoft toto správanie nazýva krížové filtrovanie.

Akokoľvek je rezač užitočný, je, žiaľ, mimoriadne zlý pre výkon Power Pivot. Pri každej zmene filtra musí Power Pivot prepočítať všetky hodnoty a miery v kontingenčnej tabuľke. Na to musí Power Pivot vyhodnotiť každú dlaždicu vo vybranom filtri a na základe výberu spracovať príslušné výpočty.

7 Vytvorte výrezy iba na poliach dimenzií.

Slices naviazané na stĺpce, ktoré obsahujú veľa jedinečných hodnôt, často spôsobia väčší zásah do výkonu ako stĺpce obsahujúce len niekoľko hodnôt. Ak rýchly filter obsahuje veľké množstvo dlaždíc, zvážte použitie rozbaľovacieho zoznamu Filter kontingenčnej tabuľky.

Podobne sa uistite, že máte správnu veľkosť údajových typov stĺpcov. Stĺpec s niekoľkými odlišnými hodnotami je ľahší ako stĺpec s vysokým počtom odlišných hodnôt. Ak ukladáte výsledky výpočtu zo zdrojovej databázy, znížte počet číslic (za desatinnou čiarkou), ktoré sa majú importovať. Tým sa zníži veľkosť slovníka a prípadne aj počet odlišných hodnôt.

8Vypnite správanie krížového filtra pre určité filtre.

Zakázanie správania krížového filtra výrezu v podstate zabráni tomuto výrezu meniť výbery, keď sa klikne na iné výrezy. Tým sa zabráni tomu, aby Power Pivot musel vyhodnocovať nadpisy v zakázanom filtri, čím sa skrátia cykly spracovania. Ak chcete vypnúť správanie krížového filtra filtra, výberom položky Nastavenia filtra otvorte dialógové okno Nastavenia filtra. Potom jednoducho zrušte výber možnosti Vizuálne označiť položky bez údajov.

9 Namiesto vypočítaných stĺpcov použite vypočítané miery.

Ak je to možné, použite namiesto vypočítaných stĺpcov vypočítané miery. Vypočítané stĺpce sa uložia ako importované stĺpce. Keďže vypočítané stĺpce prirodzene interagujú s ostatnými stĺpcami v modeli, vypočítavajú sa vždy, keď sa kontingenčná tabuľka aktualizuje, či už sa používajú alebo nie. Na druhej strane vypočítané miery sa počítajú iba v čase dopytu.

Vypočítané stĺpce sa podobajú bežným stĺpcom v tom, že oba zaberajú miesto v modeli. Naproti tomu vypočítané miery sa počítajú za chodu a nezaberajú priestor.

10Inovujte na 64-bitový Excel.

Ak budete mať naďalej problémy s výkonom v zostavách Power Pivot, vždy si môžete kúpiť lepší počítač – v tomto prípade inováciou na 64-bitový počítač s nainštalovaným 64-bitovým Excelom.

Power Pivot načíta celý dátový model do pamäte RAM vždy, keď s ním pracujete. Čím viac pamäte RAM má váš počítač, tým menej problémov s výkonom uvidíte. 64-bitová verzia Excelu môže získať prístup k väčšej časti pamäte RAM vášho počítača, čím zaistí, že má systémové prostriedky potrebné na prepracovanie väčších dátových modelov. V skutočnosti spoločnosť Microsoft odporúča 64-bitový Excel pre každého, kto pracuje s modelmi vytvorenými z miliónov riadkov.

Ale skôr, než sa rýchlo pustíte do inštalácie 64-bitového Excelu, musíte si zodpovedať tieto otázky:

Máte už nainštalovaný 64-bitový Excel?

Sú vaše dátové modely dostatočne veľké?

Máte v počítači nainštalovaný 64-bitový operačný systém?

Prestanú fungovať vaše ďalšie doplnky?