V starších článkoch som písal o obmedzeniach PowerPivotu aj Power BI. Niekedy v minulosti, a oficiálne aj doteraz, platilo, že v žiadnej tabuľke nemohlo byť viac ako cca. 2 miliardy riadkov. Pri jednom z mojich pokusov som omylom tento limit prešvihol, a – nič sa nestalo. Všetko fungovalo ďalej. To znamenalo, že PowerPivot ani Power BI už nemá obmedzenie 2 mld. riadkov na tabuľku. A preto som sa na to pozrel trochu podrobnejšie.

Keď ste niekedy v dávnej minulosti skúsili naimportovať miliardy riadkov do PowerPivotu, či už v Exceli alebo v Power BI Desktope, tak ste narazili na obmedzenie, že v žiadnej tabuľke nesmie byť viac ako 2 mld. riadkov. Ak ste teda predtým nenarazili s veľkosťou RAMky 😀 Veci sa však menia, PowerPivot sa vyvíja, a medzičasom sa stalo to, čo by nikto nečakal – toto obmedzenie potichu zmizlo.

Keď som dnes znova pozeral oficiálnu dokumentáciu k obmedzeniam oboch PowerPivotov, tak je tam napísané stále to isté, čo pred rokmi – že to obmedzenie stále existuje. Ale v skutočnosti už nemá obmedzenie na počet riadkov. A moja prvá domnienka bola, že to museli povoliť niekedy v poslednej dobe. Tak som teda nainštaloval Power BI Desktop z konca roku 2017. A obmedzenie tam nebolo tiež. Ale aj napriek tomu si pamätám, že keď som to skúšal niekedy koncom roka 2015, tak tam ešte bolo.

Celý tento objav začal náhodným experimentom, akých robím pri mojich konzultáciách či projektoch hromady. Začal som generovať väčšiu kombinačnú tabuľku, a neuvedomil som si početnosť hodnôt použitých v stĺpcoch. A vznikla z toho tabuľka s viac ako 10 mld. riadkami. Pozerám na to ako bager na tvrdú hlinu, veď to predsa ani nemalo ísť… Ale fungovalo to. Tak som sa s tým trochu pohral.

Ako to zistiť v Power BI Desktope

Keď si to chcete skúsiť v Power BI Desktope, tak použite náš vzorový súbor Power BI. Po jeho otvorení si vytvorte v dátovom modeli novú vypočítanú tabuľku s týmto vzorcom:

Veľká tabuľka =
CROSSJOIN(SELECTCOLUMNS(‘Objednávky’; “test”; [SalesOrderNumber]); SELECTCOLUMNS(‘Objednávky’; “test2”; [SalesOrderNumber]))

Tento vzorec skombinuje všetky hodnoty zo stĺpca SalesOrderNumber z tabuľky Objednávky, so samými sebou. V tej tabuľke je cca. 60 tisíc riadkov, takže vo výsledku by malo byť 60 000 * 60 000 riadkov. Po pár minútach počítania, ak náhodou nenarazíte na veľkosť RAM (dočasne to použije okolo 20 GB RAM), bude táto tabuľka vyzerať takto:

Všimnite si počet riadkov vľavo dole na obrázku. Je tam krásnych chrumkavých 3,6 miliardy riadkov. Čo je tak trochu viac ako oficiálne deklarované maximum 2 miliardy riadkov. A limit je ešte oveľa ďalej.

Jedna z ďalších hypotéz, ktorá ma pri tom napadla, je to, že tento limit platí iba pre vypočítané tabuľky. A pre “obyčajné” importované možno platiť nebude. Preto som si nageneroval v SQL Serveri 24 miliárd riadkov (databázka veľkosti cca. 300 GB), a skúsil naimportovať len jeden stĺpec z nej. Po asi 2 hodinách nekonečného importu výsledok vyzeral takto:

A screenshot spravený počas toho, ako sa načítavali údaje, keď som od nadšenia nevedel zaspať:

Takže ani bežná importovaná tabuľka už nemá obmedzenie 2 mld. riadkov. Jednoduché zosumovanie stĺpca v reporte trvalo iba cca. 7 sekúnd. Dáta zaberali v pamäti 47 GB. Report prehnaný cez Power BI Analyzer dal takúto štatistiku:

A po pridaní ďalších stĺpcov s podrobnosťami to vyzeralo takto:

Podľa toho to vyzerá, že jediná kompresia, ktorú Power BI Desktop vykonal, je tá, že tie čísla uložil namiesto 4 bajtami iba 2 bajtami na hodnotu. A zaujímavé, že reálne nepoužil žiadnu kompresnú schému, aj keď sa hodnoty veľmi často opakovali (cca. 32 tisíc jedinečných hodnôt na 24 miliárd riadkov). Možno bug, možno feature.

Report mal po uložení iba cca. 4 MB. To kvôli tomu, že reporty Power BI sú okrem powerpivotovej kompresie ešte navyše komprimované kompresiou XPress9 (algoritmus od Microsoft Research). A pred uložením spustil ešte raz processing modelu, takže to trvalo niekoľko minút. Kompresný pomer z 300 GB na 47 GB nie je zlý, ale zotriedením údajov by to išlo ešte stlačiť na oveľa nižšie číslo. Pretože tie hodnoty boli iba nagenerované, a veľmi často sa opakovali z pohľadu celej tabuľky. Len sa mi nechcelo čakať na “SELECT * FROM tabulka ORDER BY id” nad 24 mld. riadkov, pretože to by SQL Server bez indexu rozdýchaval VEĽMI dlho. Plus jedna skrytá nevýhoda – pri ukladaní reportu a aktualizácii údajov v Power BI Desktope potrebujete 2x toľko RAM. Takže na načítanie tohto modelu bolo potrebné 47 GB RAM, ale na jeho uloženie už takmer 100 GB RAM. To je akurát tak na také to domáce žuvanie.

Takže ide to aj oveľa ďalej. A Power BI Desktop už nemá obmedzenie na veľkosť tabuliek. Otázne však je, ako veľmi ďalej. Väčšiu tabuľku som už poruke nemal, a asi máloktorý z klientov by bol ochotný do toho ísť, aby som mu len tak na pár dní naplno vyťažil servery. Pri takom objeme sa už bežne používa relačná databáza + SSAS Tabular alebo Azure Analysis Services. Každopádne, účel to splnilo, a podarilo sa mi vyskúšať, čo som potreboval.

A ide takýto report publikovať do cloudu Power BI?

Veď je predsa v oficiálnej dokumentácii uvedené, že služba Power BI podporuje iba 2 miliardy riadkov v množine údajov! Tak teda stlačíme tlačítko Publikovať, a po pár minútach je report vypublikovaný v cloude aj s tými 3,6 miliardami riadkov. Funguje a počíta všetko správne. Bez jedinej chybičky. Takže ani tu to už nemá obmedzenie.

Jediné, čo nezbehlo, bola aktualizácia dát v cloude. Po pár minútach – kvôli prepočtu tej vypočítanej tabuľky – to nezbehlo s touto chybou:

Je to kvôli tomu, že v štandardnom cloude Power BI máte k dispozícii iba 4 GB RAM pre svoj dátový model. A zároveň je tam nastavený limit 10 GB na nekomprimované dáta (pozor, nemýliť si to s limitom veľkosti úložiska 10 GB v Power BI Pro). Ak chcete viac, potrebujete Power BI Embedded alebo Power BI Premium. Ten limit ide tiež dočasne presiahnuť, ale nie o moc. A aktualizácia tej vypočítanej tabuľky dočasne zožerie v niektorých prípadoch na chvíľu aj 10 GB RAM. Takže ak by bola naimportovaná, a zmestila by sa do tých 4 GB RAM, tak by ani takéto dáta neboli problém. Ale v cloude je súčasne aj ten limit 10 GB na nekomprimované dáta. Čo v prípade tohto modelu – 2 stĺpcov s reťazcami a 3,6 mld. riadkov – je pri nekomprimovaných dátach okolo 86 GB, a vo veľkom to presahuje ten limit 10 GB. A určite to nie je problém tých deklarovaných oficiálnych 2 miliardy riadkov. Takže do cloudu to vypublikujete, ale na automatickú aktualizáciu dát musíte zabudnúť, dokým nemáte Power BI Embedded alebo Power BI Premium.

A čo PowerPivot v Exceli?

Potom som dostal nápad vyskúšať to aj na PowerPivote v Exceli. Vypočítané tabuľky síce navonok nepodporuje, ale dalo by sa to vytvoriť cez linkback tabuľku. Predpokladám však, že nikto pri zmysloch nemá odvahu tlačiť do Excelu 4 miliardy riadkov. Aj keď ich vie zobraziť iba 1 milión, a natiahnuť oveľa viac, tak som si povedal, že nie.

Tak som si skúsil natiahnuť polovicu z tých istých 24 miliárd riadkov – čiže 12 miliárd riadkv – do PowerPivotu priamo importom z SQL Servera. Najprv do 64-bitového Excelu 2013 SP1, a potom do Excelu 2016 so všetkými aktualizáciami. Excel 2019 a 365 som neskúšal, pretože je tam identický PowerPivot, ako vo verzii 2016. Predpokladal som, že to po pár minútach vzdá a vráti to starú známu hlášku, ale aj majster kováč sa občas utne:

Čiže nebol problém ani tu, a ani excelovský PowerPivot už nemá obmedzenie 2 mld. riadkov na tabuľku. Kedy však toto obmedzenie vypli, už netuším. Staršie verzie Excelu sa mi už nepodarilo zohnať. A oficiálna dokumentácia stále hovorí iba o 2 mld. limite. Asi len nemajú čas aktualizovať dokumentáciu. Každopádne, výsledok poteší. A funguje to rovnako rýchlo ako v Power BI Desktope. Čiže zožralo to rovnakú veľkosť RAM, aj testovacie sumovanie stĺpca trvalo okolo 7 sekúnd. Jediný problém bol ten, že to nešlo uložiť:

Možno by to však išlo uložiť, a bol to problém iba v maximálnej veľkosti excelovského súboru, alebo v iných nastaveniach systému. Snáď sa k tomu raz dostanem.

Takže v pohode môžete natlačiť aj do excelovského PowerPivotu viac ako 2 mld. riadkov na tabuľku. Len si dajte pozor na kompresný pomer dátového modelu, aby váš excelovský súbor nemal desiatky GB 🙂

Na záver

Takéto údaje však nebudete bežne ťahať do Power BI Desktopu, ani do excelovského PowerPivotu. Lepšie je takýto dátový model premigrovať na SSAS Tabular, alebo do Azure Analysis Services, a potom sa pripojiť s reportom naňho. Alebo nechať dáta v relačnej databáze (SQL Server, Oracle, a pod.), a použiť režim DirectQuery. Okrem rýchlejšej aktualizácie dát, a ďalších možností, ktoré máte iba na týchto serveroch, ako aj kvôli rapídnemu zníženiu veľkosti reportov, sa to určite vyplatí. Reporty vo veľkosti jednotiek až desiatok GB sú totižto viac ako nepraktické – či už na publikovanie, zdieľanie, na vývoj, či na ich správu. A verte mi, že také obrovské reporty nechcete. Napriek tomu, že Power BI Desktop už nemá obmedzenie na počet riadkov v tabuľke. Preto berte tento článok skôr ako demonštráciu technologických možností. Kde sa zistilo, že sa hranice našej najobľúbenejšej technológie posunuli niekam oveľa, oveľa vyššie, ak teda vôbec ešte nejaké sú 🙂

Každopádne, aspoň vieme, že Power BI Desktop sa dá využiť aj na oveľa väčšie tabuľky, pretože už nemá obmedzenie 2 mld. riadkov na tabuľku. A na prvotné overenie, či má zmysel generovať v SQL databáze gigantické tabuľky ešte predtým, ako ich tam reálne vygenerujeme, a či ten prínos bude stáť za to. Pre veľké kombinačné tabuľky je to obrovská výhoda. A pomocou columnstore indexov potom nie je problém takéto na prvý pohľad gigantické dáta uložiť kompaktne aj na SQL Serveri. Ale o tom až v ďalšom článku 🙂