Na školeniach PowerPivotu sa často stretávam s dilemou, ktorú majú účastníci, keď uvidia, čo všetko dokáže PowerPivot alebo Power Query. V oboch z nich totižto idú vytvárať vlastné vypočítané stĺpce, a potom poniektorí majú problém, že čo je z toho lepšie. Pozrieme sa teda na to, ako to celé funguje.

Aktualizácia 26.4.2018: Tento článok popisuje, ako to fungovalo v starších verziách PowerPivotu. Aktuálna verzia komprimuje rovnakým spôsobom obyčajné aj vypočítané stĺpce, a až na zopár nepodstatných drobností nie je medzi nimi rozdiel. Zvyšok tohto článku je teda pre aktuálne verzie už irelevantný – okrem výnimky popísanej na konci, v predposlednom odseku.

Veľmi málo ľudí vie, že keď vytvoríte vypočítaný stĺpec v PowerPivote, Power BI alebo SSAS Tabulare, tak sa tento správa mierne odlišne od stĺpcov natiahnutých priamo z dátového zdroja, resp. databázy. Priamo natiahnuté stĺpce sú hneď po naimportovaní do dátového modelu skomprimované, a zaberajú v bežnom prípade cca. 4-5x menej pamäte, ako zaberajú v databáze. V niektorých prípadoch sa tento pomer môže vyšplhať aj na 20x, prípade aj viac, keď sú v danom stĺpci hojne sa opakujúce hodnoty. Aj vďaka tejto kompresii vie potom PowerPivot bleskurýchle robiť výpočty, väčšinou aj bez potreby dekompresie dát počas výpočtov.

Inak sa však správajú vypočítané stĺpce. Tie sú NEkomprimované, a teda zaberajú v pamäti plus-mínus rovnako veľké miesto ako v databázach. To Vás nemusí trápiť pri malých dátových modeloch, ale ak máte desiatky miliónov riadkov, či nebodaj miliardy riadkov, tak ten rozdiel už naozaj pocítite. Na porovnanie – keď máte vypočítaný stĺpec obsahujúci číselnú hodnotu, tak každá jedna z nich zaberá v pamäti 4 až 8 bajtov, podľa vybraného dátového typu. Ak máte milión riadkov v tabuľke, tak takýto stĺpec bude zaberať 4 až 8 MB RAM. Čo sa ešte dá prežiť. Ak však máte už miliardu riadkov v tabuľke, tak takýto stĺpec bude zaberať 4 až 8 GB RAM, a pri pridaní niekoľkých desiatok takýchto stĺpcov Vám veľmi rýchlo dôjde pamäť aj na tom najnadupanejšom servri. A na high-endovom PC-ku už po pridaní zopár stĺpcov… Až začnete riešiť, kdepak ti soudruzi z NDR udělali chybu.

Riešením je daný výpočet spraviť už v dátovom zdroji, ak je to možné. Najmä ak ovládate jazyk SQL a spraviť tam daný výpočet je pre Vás jednoduché. Alebo, ešte lepšie – spravte ho v Power Query, najmä ak takéto vstupné dáta nepotrebujete zdieľať vo viacerých dátových modeloch. Power Query sa totiž správa voči PowerPivotu ako externý dátový zdroj, a tým pádom sa takéto stĺpce uložia v PowerPivote ako komprimované. Výhodou je, že väčšinu bežných vypočítaných stĺpcov spravíte v Power Query na zopár kliknutí. Takže nepotrebujete veľmi vedieť jazyk SQL, a ani meniť štruktúru databázy – k čomu Vás aj tak databázisti asi ani nepustia. A dáta idú z Power Query načítať aj rovno do dátového modelu, bez potreby použiť Excel ako medzikrok – tak ako v Power BI. V otázke vypočítaných stĺpcov v PowerPivot alebo Power Query má Power Query v tomto prípade značne navrch.

Jedinou výnimkou v tomto prípade sú asi stĺpce, ktoré sú počítané z dát z ostatných tabuliek v dátovom modeli, ako napríklad v tomto článku. Takéto stĺpce sú väčšinou počítané v číselníkoch, a vytvoríte ich v DAX-e relatívne jednoducho. A tá úspora pamäte, ktorú by ste získali presunutím výpočtu do databázy, za tú námahu jednoducho nestojí. Prípadne, ak pracujete so státisícmi až miliónmi riadkov, tak je lepšie v niektorých (ale naozaj len niektorých) prípadoch spraviť výpočet už na úrovni databázy, pretože Power Query nevie počítať dáta až tak bleskurýchle ako PowerPivot alebo databáza. Prípadne, v niektorých prípadoch sa ešte dajú vypočítané stĺpce nahradiť aj merítkami, a tým stlačiť pamäťové nároky výpočtu takmer na nulu. Ale o tom snáď niekedy inokedy 😉

A týmto je dilema PowerPivot alebo Power Query vyriešená 🙂 Máte aj Vy takúto skúsenosť s presunom výpočtov do databázy, resp. do Power Query? Napíšte dole do komentárov, ako ste sa s tým vysporiadali.