Tema broja – Office 2010 sustav – PowerPivot for Excel

 luka_abrus

Milijuni pod prstima

Excel već odavno drži poziciju glavnog alata za brzu analizu i rad s podacima. Iako se za pravo poslovno obavještavanje koriste specijalizirani alati, svoje mjesto među njima nekako uvijek nađe Excel. Štoviše, Excel je okosnica Microsoftove platforme za poslovno obavještavanje, a s novom verzijom 2010 i dodatkom PowerPivot, započinje revoluciju. PowerPivot donosi Excelu neslućenu dimenziju i 01 Izvjestaj u Exceluotvara potpuno nove scenarije…

Što danas predstavlja tipičan sustav za poslovno obavještavanje (business intelligence, BI)? Gledajući samo arhitekturu, tipične implementacije uključuju skladište podataka i specijaliziranu aplikaciju za izradu i prikazivanje izvještaja. Treba li vam novi izvještaj ili mala promjena nekog postojećeg, najčešće ćete morati kontaktirati IT tim i pričekati određen broj sati ili dana da se izvještaj napravi, testira i objavi. Ako ste pak vi taj IT tim na koji svi računaju, vjerojatno ogroman broj sati provodite za samim alatom izrađujući izvještaje i pripremajući podatke.

Excel datoteka s izvještajima temeljenim na podacima iz PowerPivota –
primijetite horizontalne i vertikalne slicere te pristup podacima i mjerama kroz desni okvir

 

Microsoft se s platformom generacije 2007, koja uključuje SharePoint 2007, Excel 2007 i SQL Server 2005/2008, odlučio na drugačiji pristup i svoju je strategiju nazvao BI za mase. Cilj je, dakako, ponuditi podatke kroz poznata sučelja (Excel ili web preglednik, odnosno SharePoint) svima kojima ti podaci trebaju.

Evolucija Excela

U idealnom scenariju IT tim se brine da svi servisi rade i da su podaci svježi te predstavljaju „jedinstvenu istinu” unutar tvrtke. Krajnji korisnici samo osvježavaju svoje izvještaje kroz Excel ili stišću F5 u pregledniku, a napredni korisnici koriste Excel za izradu izvještaja koji im trebaju te ih potom objavljuju ostatku tima na SharePointu. Excel je tako postao okosnica svakog pristupa podacima, bilo da korisnici imaju neke svoje excelice koje osvježavaju ili se same Excel datoteke prikazuju kao web stranice na portalu. 05 PowerPivot u pregledniku
No, s platformom generacije 2010, koja obuhvaća SharePoint 2010, Excel 2010 i SQL Server 2008 R2, došlo je vrijeme za svojevrsnu evoluciju u obliku PowerPivota. PowerPivot cilja na napredne korisnike Excela kojima analiza podataka, pivot tablice i pristup različitim izvorima podataka nisu bauk. PowerPivot zapravo dolazi u dva pakiranja – verzija za Excel koja je i glavna tema ovog teksta, namijenjena je korištenju kroz Excel, odnosno klijentskoj analizi i obradi podataka. Postoji i PowerPivot for SharePoint koji je odgovoran za osvježavanje PowerPivot Excel datoteka na poslužitelju i njihovo prikazivanje u pregledniku u obliku dinamičkih web stranica (više o tome u zasebnom okviru).
Sâm PowerPivot for Excel je, dakle, dodatak koji se instalira na Excel 2010. Bitno je razumjeti da je PowerPivot tako jedan ribbon u Excelu te da se sâm PowerPivot pokreće u zasebnom prozoru. No, što je zapravo PowerPivot i čemu služi?

  Prikazivanje PowerPivot datoteke na SharePointu u pregledniku –
Excel izvještaj poprima oblik interaktivne aplikacije u kojoj je
moguće podatke sortirati i filtrirati (primijetite slicere s lijeve strane)

Redak po redak

Krenimo s tehničkim sitnicama – PowerPivot Excelu donosi mogućnost rada sa stotinama milijuna zapisa. Da, dobro ste pročitali, radi se o stotinama milijuna. Prisjetimo se malo ne tako davne prošlosti u kojoj je Excel 2003 bio ograničen na 65,536 redaka i 256 stupaca. Excel 2007 donio je ogroman pomak – podršku za nešto više od milijun redaka (1,048,576) i 16,384 stupaca (do krajnjeg XFD stupca). Klasični Excel 2010 može baratati jednakim brojem stupaca i redaka, no njemu PowerPivot otvara mogućnost upravljanja stotinama milijuna redaka.

02 PowerPivot ribbon

PowerPivot se instalira kao novi ribbon u Excelu s kojeg se može povezati
s PowerPivot podacima i otvoriti PowerPivotov prozor

 

PowerPivot je, u stvari, in-memory sustav upravljanja ogromnim količinama podataka. Ti se podaci komprimiraju korištenjem internog sustava Vertipaq te analiziraju kroz Excel. Da se najpreciznije izrazimo, ti milijuni zapisa spremljeni su u memoriji, a moguće im je pristupati kroz PowerPivotov prozor. U PowerPivotovom prozoru moguće su neke od klasičnih funkcionalnosti Excela poput filtriranja i sortiranja, koja se izvršavaju u djeliću sekunde kao da se radi o tisućama, a ne milijunima redaka. Excel pak može analizirati te podatke kroz pivot tablice i grafikone, no oni ipak nisu dostupni u standardnim sheetovima u Excelu.

I dok se danas Excelom možemo spajati na različite vanjske izvore podataka, kao što su OLAP sustavi na SQL Serveru, iz njih izvlačiti podatke i mjere te stvarati izvještaje, PowerPivot nam nudi drugačiji scenarij – Excelom se spajamo na podatke koji se drže u memoriji unutar Excela, odnosno unutar PowerPivota. Takve Excel datoteke, koje sadržavaju klasične Excel sheetove s podacima i pivot tablicama te ogromne setove podataka spremljene u PowerPivot dijelu, mogu se pohraniti na disk i pritom će zauzimati desetke pa i stotine megabajta. No, kompresija podataka je značajna i omogućava da se na računalima sa 1 ili 2 GB radne memorije upravlja desecima i stotinama milijuna zapisa, što danas kroz klasičan Excel nije izvedivo.

Kako do podataka?

PowerPivot, dakle, donosi bazu podataka u Excel. Podaci koji se obrađuju kroz Excel više nisu spremljeni na nekom udaljenom OLAP poslužitelju, već unutar samog Excela. To nas dovodi do jednostavnog pitanja – zašto bismo to uopće htjeli? Razloga ima više, stoga krenimo redom.
Prvi, i možda najvažniji, jest mogućnost dohvaćanja podataka u PowerPivot iz različitih izvora, što pak za sobom povlači njihovo međusobno kombiniranje i slaganje relacija. To znači da se podaci mogu dovlačiti iz svakojakih izvora, od baza (SQL Server, Oracle, Teradata, Sybase, Informix, IBM DB2 ili bilo kojeg OLEDB/ODBC izvora), preko izvještajnih sustava (Reporting Services, Atom feedovi) i Microsoftove Analysis Services platforme pa sve do SQL Azure izvora, Access baza te drugih Excel ili tekstualnih datoteka.
Pri dohvatu podataka iz baza, PowerPivot će dohvatiti i veze između tablica te stvoriti relacijsku shemu. Dohvaćanjem dodatnih podataka iz drugih izvora (primjerice, možda iz vlastitih Excel datoteka, drugih izvještaja ili neke treće baze) možete proširiti relacijsku shemu i definirati nove odnose između podataka. Svaka dohvaćena tablica bit će spremljena na drugom sheetu u PowerPivot prozoru pa je iskustvo kretanja kroz podatke gotovo identično klasičnom Excelu. Naravno, možete dodavati nove stupce u tablice, a u njima možete koristiti i posebne formule, Data Analysis Expressions (DAX), o čemu više u posebnom okviru. Zaustavimo se nakratko i pogledajmo još jednom situaciju – Excel se s podržavanjem relacija na neki način približio Accessu jer omogućava stvaranje i održavanje relacijskih baza, a dobio je i dimenziju OLAP alata za analizu (čak će se pri radu s pivot tablicama koje dohvaćaju podatke iz PowerPivota u Excelu na dnu ispisati „Running OLAP query”). Dodatno, sve podatke dohvaćene u PowerPivot iz različitih izvora moguće je osvježiti klikom i tako dohvatiti aktualne informacije i nad njima vršiti analizu.
Prednost je ovakvog pristupa što bilo tko može postati OLAP analitičar, a da toga ne bude svjestan. Da bi se određeni scenariji, poput integracije podataka iz različitih izvora, ispunili danas, nužno je te podatke uvesti u skladište, što je složen i možda nepotreban proces. Napredniji korisnici Excela itekako dobro poznaju rad s pivot tablicama i povezivanjem na izvore podataka i baš će njima PowerPivot biti kao produljena ruka.

03 PowerPivot podaci
Jedna omanja tablica sa 2,5 milijuna redaka u PowerPivotovom prozoru –
primijetite popis tablica s podacima na dnu te ikonice u nazivu određenih stupaca
koje označavaju da su ti stupci u relaciji s nekom drugom tablicom

Od slova do slike

Nakon što su podaci pripremljeni u PowerPivotu, vrijeme je da se iskoriste za prave stvari – analizu kroz Excel. Kao što je spomenuto, podaci iz PowerPivota u Excelu se rabe u obliku pivot tablica i grafikona. Sve radi na klasičan način – s desne strane nalazi se popis svih dostupnih polja koja je moguće dovlačiti u izvještaj. Novost su u Excelu 2010 sliceri na koje se može gledati kao na napredniju verziju filtra. Dok su se filtri uglavnom postavljali na vrh izvještaja i nudili odabir neke opcije iz padajuće liste, sliceri prikazuju sve mogućnosti filtriranja i omogućuju korisniku da dinamički klika po svim dostupnim opcijama i tako filtrira graf i izvještaje.

Jednom tako napravljena Excel datoteka može se spremiti lokalno ili, mnogo korisnije, pohraniti na SharePoint. PowerPivot na SharePointu pobrinut će se da se Excel datoteke s PowerPivot podacima prikazuju kao dinamičke web stranice. Tako će izvještaj s pivot tablicama koji ste izradili u Excelu postati dostupan kao web stranica, što je idealno za distribuciju izvještaja ostalim kolegama. Naravno, svaku datoteku sa SharePointa moguće je opet otvoriti u Excelu, malo je promijeniti i izraditi novi izvještaj na temelju postojećeg te ga vratiti na poslužitelj drugima na korištenje. Dodaju li se tome standardne mogućnosti SharePointa kao što su verzioniranje, workflow, upravljanje dozvolama i stvaranje timskih siteova, izrada kompletnih rješenja za poslovno obavještavanje nikad nije bila lakša.

U trenutku pisanja ovog teksta PowerPivot se nalazi u verziji RC0, odnosno Release Candidate 0, a njegov službeni izlazak očekuje se zajedno s Excelom 2010, odnosno Officeom 2010. O PowerPivotu će biti mnogo više riječi u narednim mjesecima kad bude i službeno predstavljen na tržištu, no jedno je sigurno – PowerPivot postaje ključan element Microsoftove strategije za poslovno obavještavanje.

 07 PowerPivot konekcije
Veze iz PowerPivota na različite izvore podataka –
podatke je uvijek moguće osvježiti klikom na Refresh

 

PowerPivot za SharePoint

04 PowerPivot GalleryJednom pripremljeni izvještaji u Excelu zasigurno će poslužiti i ostatku tima. Stoga je najlogičnije mjesto za smjestiti ih upravo SharePoint koji time postaje centralno mjesto za sve izvještaje i poslovno obavještavanje. PowerPivot izvještaji se nalaze u PowerPivot Gallery biblioteci sadržaja koja se može prikazati kroz različita sučelja, čime se izvještaji približavaju poslovnim korisnicima. Jedan od primjera prikazan je na pratećoj slici, a njegova je glavna karakteristika pretpregled samih izvještaja uz koji korisnici više nisu osuđeni na traženje izvještaja po nazivu, već po njegovom izgledu i podacima.

Naravno, osim u Excelu, PowerPivot izvještaje moguće je prikazivati u pregledniku kroz SharePoint. Pritom oni izgledaju kao interaktivne aplikacije, a svu pripremu podataka za prikaz (ovisno o filtriranju) obavlja poslužitelj. Da bi pristupio izvještajima, korisnik stoga uopće ne treba pokretati Excel. Budući da se objavljivanjem izvještaja na SharePointu sav posao prebacuje na server, moguće je i definirati osvježavanje podataka pojedinih datoteka. Tako za svaki izvještaj možemo definirati kad će se i koliko često dohvatiti novi podaci putem konekcija prema izvorima iz PowerPivota. Pritom treba imati na umu da će se moći osvježiti samo oni podaci do kojih poslužitelj ima pristup pa tako ako u pripremi uvezemo podatke iz lokalnih datoteka s diska, poslužitelj njima neće imati pristup i oni se neće osvježavati. No, ako se radi o podacima dohvaćenim iz različitih baza ili OLAP sustava, poslužitelj će se brinuti da se oni redovito osvježavaju, primjerice jednom dnevno, i tako osigurati da PowerPivot izvještaj uvijek bude aktualan i s pravim podacima. 

Budući da smo ovime dali dosta novog posla poslužitelju, važno je kvalitetno upravljanje serverom. Kroz upravljačku konzolu na SharePointu moguće je pratiti opterećenje poslužitelja (koji se bavi dohvatom podataka, kao i njihovom obradom i 06 PowerPivot Mgmt konzolaprikazom u pregledniku) te saznati koje su Excel datoteke najkorištenije, koliko ih ljudi koristi i koji su to točno ljudi. Tko kaže da IT ne poznaje biznis? Brzim pogledom na konzolu vidljivo je koji su PowerPivotovi izvještaji bitni marketingu te koji je direktorov omiljeni izvještaj. Naravno, IT će tako možda saznati i da se nikad nije ni pokrenuo izvještaj koji je bio tako hitan prošli tjedan.

PowerPivot za SharePoint zahtijeva SQL Server 2008 R2 i SharePoint Server 2010 i donosi prijeko potrebnu serversku stranu priče PowerPivotu za Excel. Iako klijentska verzija drastično proširuje mogućnosti Excela, tek se sa serverskom podrškom ostvaruju pravi scenariji za poslovno obavještavanje i njenu primjenu unutar cijele tvrtke.

   Konzola za upravljanje PowerPivot izvještajima na poslužitelju
nudi odgovore na pitanja tko, što, koliko i kada te
kakve su performanse samog sustava

Data Analysis Expressions

Data Analysis Expressions (DAX) jezik je za izraze i formule kroz PowerPivot temeljen na sintaksi Excela. Budući da je DAX osmišljen da radi s više tablica podataka, ima funkcije koje implementiraju različite koncepte relacijskih baza. DAX tako omogućava stvaranje kalkuliranih vrijednosti i novih mjera. U PowerPivot prozoru dovoljno je napraviti novo polje u nekoj tablici i u njega upisati DAX formulu. Važno je razumjeti da DAX ne može referirati određena polja, kao što to mogu Excel formule (npr. B10:B50), već podatke referira prema stupcima i tablicama u kojima se nalaze.

DAX za računanje kalkuliranih vrijednosti u nekom stupcu radi kao i standardni Excel – upiše se formula i ona se izračunava za svaku ćeliju u stupcu. Primjerice, imate li stupac [Qty] za količinu i [Price] za cijenu, možda vam treba ukupan iznos koji se izračunava umnoškom tih dviju vrijednosti. Kao i u Excelu, u novi stupac biste napisali “=[Qty]*[Price]”. Kalkulirane vrijednosti mogu se potom u Excelu koristiti u pivot tablicama među vrijednostima, recima i stupcima te slicerima.

Evo i naprednijeg primjera: želite li u tablici s kupcima izračunati ukupnu zaradu po svakom kupcu (a sav promet je spremljen u tablici Revenue i napravljena je relacija između svake transakcije i tablice s kupcima), stvorit ćemo novi stupac i u njega upisati formulu “=SUMX(RELATEDTABLE(Revenue), Revenue[RevenuesInEuro])”. Tipičan način na koji bismo danas to radili jest izrada upita na SQL Serveru ili nekoj drugoj bazi, a sad dovlačenjem podataka u PowerPivot imamo pod prstima potpuno novi set mogućnosti manipulacije i obrade podataka.

Za detaljnu specifikaciju mogućnosti jezika DAX pogledajte MSDN pa vam prepuštamo da sami otkrijete čemu služe funkcije poput CALCULATE, DISTINCT, EARLIEST, MAXX, CLOSINGBALANCEMONTH, PARALLELPERIOD, NEXTYEAR i deseci drugih…

08 DAX
Pisanje DAX formula u PowerPivot prozoru – iskustvo je isto kao i pri pisanju standardnih Excelovih formula, a tome pripomaže i IntelliSense

Napomena: Članak je preuzet iz posebnog izdanja WinDays Mreža 2010, objavljenog u travnju 2010., izdavač BUG d.o.o.