TRIKOVI U EXCELU #204: Ko to tamo PowerPivotira? Ili: Gde su stvarne granice PowerPivota?

Gledam nedavno po 389. put „Ko to tamo peva“ i scenu kada Miško vozi vezanih očiju. To me podsjetilo na situaciju kada sam sa kolegom Emirom četvrti ili peti put u firmi tražio prelazak na Excel verzije 2010 jer podržava PowerPivot. Ranije su nas svaki put odbili zbog „nedostatka licenci“, čitaj: novca a najviše zbog nemogućnosti da povjeruju da Excel može da skladišti i obradi toliku količinu podataka.

Razgovor je išao otprilike ovako:

• Kažem vam, importovao sam preko 100 miliona redova u Excel 2010 fajl.

• Ma, daj, pa znaš da Excel ima ograničenje od milion redova po radnom listu, šta pričaš? Opet nisi uzeo lijekove jutros, je li tako?

• Pa vi meni ne vjerujete! Hajde da se kladimo. Importovaću 100 miliona redova iz baze podataka firme sa mreže u Excel.

• Gledajte sada…

• Pa on nije normalan, pašće nam mreža, serveri, baza podataka…  sve!

U međuvremenu je PowerPivot počeo da importuje podatke iz baze brzinom od oko 70 hiljada redova u sekundi.

Kolege su gledale Excel kao da ga prvi put vide. A onda…

• Pazi, milion redova!

• Saaaamo bez panike. Moj PowerPivot je genije za ove stvari.

I Excel je nastavio da importuje podatke: dva miliona , pet, deset, dvadeset miliona…

Nakon 30 miliona rekli smo zapanjenim kolegama da navrate malo kasnije kada se import završi. Sto miliona, dvije stotine…  Zaustavio se na 319.068.202 reda.

• Jeste li vidjeli?
• Jesmo, u redu, ubjedili ste nas, tražićemo da nam odobre instalaciju nove verzije Excela. A koliko to zadovoljstvo košta?
• Ništa, u pitanju je besplatan dodatak. A verzije od 2013 i novije imaju PowerPivot kao sastavni dio Excela.

I baš kada su počeli da se vraćaju svojim poslovima komentarišući ono što su upravo vidjeli, dobacio sam, onako, nonšalantno:

• .. Heh, a prije dvije godine PowerPivot je, isto za opkladu, na sto miliona redova upario podatke iz druge tabele za sekundu, i to bez VLOOKUP-a i INDEX/MATCH-a!

• Bez VLOOKUP-a?! Sto miliona redova?! Pa šta pričate?

• Ćuti, budalo! Hoćeš li da nam obori kompletan sistem firme?

• Šta, ne vjerujete mi?

• Vjerujemo, vjerujemo…
• Čak i ja vjerujem.
• E, sad ćete da vidite.

• Jel’ima VLOOKUP-a, a? Jel’ vidiš INDEX/MATCH igdje? Vidiš li?…
• Ne vidim, pa šalio sam se.
• Nemoj, Almire, molim te.
• Ti ćeš moj PowerPivot da nazivaš lažovom…

U tabeli iz primjera nazivi artikala su na engleskom a za izvještaj nam je trebao prevod. U tu svrhu smo napravili drugu tabelu sa prevodima artikala.

Inače bi se ovo radilo pomoću VLOOKUP-a ili kombinacije INDEX/MATCH. Ali na 320 miliona redova…

U PowerPivot smo importovali tabelu sa prevodom a zatim tabele relaciono povezali tako da naziv na engleskom u velikoj tabeli odgovara jednom redu u drugoj tabeli koji je u drugoj koloni sadržavao prevod. Ko je ikada pravio bazu podataka bar u Accessu, zna o čemu govorim.

Na ovaj način nije bilo potrebe da se u velikoj tabeli pravi pomoćna kolona u koju bi se donijele vrijednosti iz tabele sa prevodima. Doduše, isprobali smo u tu svrhu funkciju RELATED (ekvivalent VLOOKUP-a i INDEX/MATCH-a). Rezultate za 319 miliona redova smo dobili za nešto više od minut i pol.

Brzo? Ne! Za mjerila PowerPivota to je jaaaako sporo. Mnogo bolji pristup je pravljenje pivot tabele od kolona iz više relaciono povezanih tabela.

Zahvaljujući uspostavljenoj relaciji koja Excelu „objašnjava“ da je veza između dvije tabele preko zajedničke kolone (baš kao kod VLOOKUP-a) pivot tabela nastala iz PowerPivota je u stanju da koristi kolone iz više tabela. Ovo je još jedna velika prednost PowerPivota u odnosu na klasične pivot tabele.

U ovom primjeru kreirana je tabela sa brojčanim pokazateljima iz velike tabele i oznakama proizvoda iz tabele sa prevodima. Tačnost je osigurana kreiranjem relacije između tabela.

Najvažnije od svega je da su uparivanje i kalkulacija izvršeni momentalno.

Također, izvedene brojčane vrijednosti je mnogo bolje kreirati kao tzv. mjere u pivot tabeli nego kao kalkulisane kolone u prozoru PowerPivota. Razlog je u veličini fajla i naročito u brzini kalkulacije.

Ukoliko bismo u gornjem primjeru pokušali dobiti prosječnu (tačnije, ponderisanu) cijenu proizvoda prostim djeljenjem prihoda sa količinom, sporiji način bi bio da se u prozoru PowerPivota kreira kalkulisana kolona na 320 miliona redova, što bi trajalo oko jedne minute.

Mnogo efikasniji način po pitanju brzine i veličine fajla je kreiranje mjera u pivot tabeli. Mjere se kalkulišu momentalno jer se izvršavaju samo na podacima koji se prikazuju u pivot tabeli a ne na svim podacima.

Da biste kreirali mjeru, na traci PowerPivota odaberite New Measure, dajte joj ime i unesite formulu (u ovom slučaju količnik ukupnog broja prodanih komada i ukupnog prihoda).

Nakon toga dodajte mjeru kao i svaku drugu kolonu u pivot tabelu. Et voilà! Završeno bez VLOOUP-a, bez INDEX/MATCH-a i, što je najvažnije, u tren oka.

Inače, da ne bih kvario tok priče, nisam odmah napomenuo da je jako dobro prije osvježavanja podataka u PowerPivotu isprazniti TEMP folder kako bi PowerPivot imao što više prostora.

Lokacija TEMP foldera je: %USERPROFILE%\AppData\Local\Temp.

Kada sam prvi put pokušao ovu akrobatiku sa stotinama miliona redova, dobio sam grešku jer PowerPivot nije imao dovoljno memorijskog prostora.

• Nećeš proći dok ne isprazniš TEMP folder. Nećeš proći dok ne isprazniš TEMP folder.  Nećeš proći dok ne isprazniš TEMP folder.

Tvrdoglav po prirodi, pokušavao sam da prođem bez pražnjenja TEMP foldera jer mi je to išlo na živce. Rezultat je uvijek bio isti:

• Nema dovoljno memorije, hahaha. Nema dovoljno memorije, hahaha. Nema dovoljno memorije, hahaha.

I tako…

• Hajde, briši sve iz TEMP foldera, sve!

Nakon pražnjenja TEMP foldera sve je bilo u redu.

I tako… Kada danas vide PowerPivot na djelu, ostale kolege govore svojim šefovima:

• Tata, hoću i ja onako.

A šefovi po pravilu izgovaraju antologijsku rečenicu:

• I tata bi, sine.

P. S.

Inače sam strastveni kockar, tj. volim da se kladim. A najviše volim da se kladim sam sa sobom jer tako pobjeđujem u svakom slučaju. U međuvremenu smo se ja i ja opkladili da će PowerPivot importovati milijardu redova. Opkladu sam dobio.

Na stranici www.excelbezbola.com, u gornjem desnom uglu je link za preuzimanje dotičnog fajla sa nešto više od milijardu redova. Ne brinite:  vjerovali ili ne, veličina fajla je samo 1,5 MB.