A PowerPivot memóriahasználatának optimalizálása

A 32 bites Excel 2 GB memóriát tud megcímezni, ebből a PowerPivot adatbázis-kezelő kicsit kevesebb mint 1 GB-ot használhat (A PowerPivot az Excelen belül fut)

A PowerPivotnak a be/ki tömörítéshez szüksége van plusz memória területre, ahol létrehozhatja a temp tábláit

E kettő eredőjeként alakul ki az a tapasztalati szám ami szerint 5-700 megabájtos Excel fájlnál nem tud többel dolgozni a 32 bites PowerPivot.

Sok ez az 5-700 megabájt? Excel fájlméretnek soknak tűnhet, de egy pármillió soros adatbázissal már könnyen elérhetjük ezt a határt. Ilyen esetben két dolgot tehetünk:

1. Váltunk 64-bites Excelre és akkor már kb. 2-3 szor annyi adattal tudunk dolgozni, mint a 32 bites Excel esetén. (Megj: Egy 4 gigás SharePoint adattípus korlát következtében 2 gigásnál nagyobb Excel fájt nem tudunk lementeni)
2. Csökkentjük a memóriába töltött adatok mennyiségét vagy jobban tömöríthető adatbázis hozunk létre. Nézzük ez utóbbit

Csökkentjük az adatbázisba töltött adatok méretét

Ezt háromféleképpen tudjuk elkérni:

1. kevesebb sort olvasunk be. Hááát ez a legritkábban járható út. Ritkán fordul elő, hogy nincs szükségünk az összes sorra. Az viszont gyakrabban fordul elő, hogy nincs szükségünk az összes oszlopra
2. kevesebb oszlopot olvasunk be. Ez már egy sokkal könnyebben járható út, hiszen a táblák nagyon sokszor tartalmaznak az elemzések szempontjából érdektelen oszlopokat. Ezek kihagyásával jelentősen csökkenthetjük a memóriában futó adatbázis méretét. Különösen igaz ez a nehezen tömöríthető (sok különböző elemet tartalmazó) oszlopokra. Pl.: Áfa összege
3. Számított oszlop (Calculated column) helyett használjunk számított mezőt (Calculated measure) A számított oszlop tartalma ugyanis letárolásra kerül míg a számított mező tartalma nem: ez lekérdezés időben fog számolódni.
4. Normalizálás? Sajnos nem segít, hiszen a PowerPivot tömörítetten tárolja az adatokat így mindegy, hogy azok redundánsak e vagy egyediek: Tömörítve ugyanazt az eredményt kapjuk. Erről bővebben itt: PowerPivot és hópihe

Jobban tömöríthető adatokat töltünk az adatbázisba

Hogyan tehetjük jobban tömöríthetővé az adatokat? Például úgy hogy

1. Rendezzük betöltés előtt az adatokat. Az Analysis Services-nél tudott volt, hogy jobban tudja tömöríteni az adatokat, ha azokat eleve rendezetten adjuk oda neki. Ugyanez megfigyelhető a PowerPivot esetében is: Ha betöltés előtt rendezzük az adatokat, akkor kisebb adatbázist, és jobb lekérdezési teljesítményt kapunk, mintha rendezés nélkül töltenénk be az adatokat.
2. csökkentjük a tizedes jegyek számát. A 211,9 sokkal jobban tömöríthető, mint a 211,89765232 hiszen sokkal nagyobb a valószínűsége, hogy az adathalmazban előfordul még egy 211,9-es érték, mint annak, hogy előfordul egy 211,89765232-és érték. Persze ezzel nagyon csínján kell bánni nehogy előforduljon az, hogy a forrásrendszerből számított kedvezmény összege eltérjen a PowerPivotból számított kedvezmény összegétől.
3. csökkentjük a számítások eredményének tizedes jegyeit a Round() függvénnyel . Beszéltünk róla, hogy a számított oszlopok is letárolásra kerülnek, így azoknál is fontos, hogy minél jobban legyenek tömöríthetőek.
4. Szétvágjuk a sok különböző értéket tartalmazó oszlopot több, kevesebb különböző elemet tartalmazó oszlopra:
   1. Idő oszlopok (óra:perc:másodperc) szétbontása Óra, Perc, Másodperc oszlopokra
   2. Értékesítés Ft oszlop helyett a Mennyiség és az egységár oszlopokat olvassuk be.

Összefoglalva: A 32 bites Excel korlátait hamar el fogjuk érni. Épp ezért törekedni kell a minél kisebb adtabázis méretre, amelyet úgy érhetünk el a legegyszerűbben, ha nem olvassuk be az elemzéshez nem szükséges oszlopokat :-)

További infó:

• Memory Considerations about PowerPivot for Excel
• PowerPivot: improve the performances by sorting tables
• How much data can I load into PowerPivot?