- Finansijski Snowboard - http://www.mcb.rs/blog -

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:

4Maajtemolimvas

5Tataneverujemi

1Jesamvoziomilione

 

2negledakudvozi

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

Bandera

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

8pazimilionredova

 

3mojmiskojegenije

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.

320miliona

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:

7samobezpanike

10mastapricate

11cutibrebudalo

12neverujetemi

6jelvidisista

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.

Prevod

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.

Relacija

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.

PP300

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.

pivotizvisetabela

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).

Mjera

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.

MjeraUpivotu

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.

13necesproci

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:

14busihahaha

I tako…

15dajtepare

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

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

16cuti

A šefovi po pravilu izgovaraju antologijsku rečenicu:

17itatabisine

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.