Prednosti Pivot by Data Model

Kada pravimo zaokretnu tabelu u Excel-u, u prvom dijaloškom okviru, gde se od nas traži da postavimo početni opseg i odaberemo mesto za umetanje pivot tabele, nalazi se neupadljiv, ali veoma važan kvadratić ispod – Dodajte ove podatke u model podataka (Dodajte ove podatke na model podataka) i, malo više, prekidač Koristite model podataka ove knjige (Koristite model podataka ove radne sveske):

Nažalost, mnogi korisnici koji su već duže vrijeme upoznati sa pivot tablicama i uspješno ih koriste u svom radu ponekad ne razumiju značenje ovih opcija i nikada ih ne koriste. I uzalud. Na kraju krajeva, kreiranje pivot tablice za model podataka daje nam nekoliko vrlo važnih prednosti u odnosu na klasičnu Excelovu zaokretnu tablicu.

Međutim, prije nego što izbliza razmotrimo ove „zele“, hajde da prvo shvatimo šta je, zapravo, ovaj model podataka?

Šta je model podataka

Model podataka (skraćeno kao MD ili DM = Model podataka) je posebno područje unutar Excel datoteke gdje možete pohraniti tabelarne podatke – jednu ili više tabela povezanih, ako želite, jedna s drugom. U stvari, ovo je mala baza podataka (OLAP kocka) ugrađena u Excel radnu svesku. U poređenju sa klasičnim skladištenjem podataka u obliku regularnih (ili pametnih) tabela na listovima samog Excela, model podataka ima nekoliko značajnih prednosti:

• Stolovi mogu biti do 2 milijarde linija, a Excel list može stati nešto više od milion.
• Unatoč gigantskoj veličini, obrada ovakvih tablica (filtriranje, sortiranje, proračuni na njima, sažetak zgrade itd.) se obavlja vrlo brzo Mnogo brže od samog Excela.
• Sa podacima u Modelu možete izvršiti dodatne (po želji, vrlo složene) proračune koristeći ugrađeni DAX jezik.
  
• Sve informacije učitane u model podataka su vrlo jako komprimovano koristeći poseban ugrađeni arhivator i prilično umjereno povećava veličinu originalne Excel datoteke.
  

Modelom upravlja i izračunava poseban dodatak ugrađen u Microsoft Excel – powerpivoto čemu sam već pisao. Da biste to omogućili, na kartici Developer klik COM dodaci (Programer — COM dodaci) i označite odgovarajući kvadratić:

Ako tabs Developer (programer)ne možete ga vidjeti na vrpci, možete ga uključiti kroz Datoteka – Opcije – Podešavanje trake (Datoteka — Opcije — Prilagodi traku). Ako u prozoru prikazanom iznad na listi COM dodataka nemate Power Pivot, onda on nije uključen u vašu verziju Microsoft Officea 🙁

Na kartici Power Pivot koja se pojavi nalazit će se veliko svijetlozeleno dugme upravljanje (Upravljaj), klikom na koji će se otvoriti prozor Power Pivot na vrhu Excela, gdje ćemo vidjeti sadržaj modela podataka trenutne knjige:

Važna napomena na putu: Excel radna knjiga može sadržavati samo jedan model podataka.

Učitajte tabele u model podataka

Da bismo učitali podatke u model, prvo pretvorimo tabelu u dinamičku „pametnu“ prečicu na tastaturi Ctrl+T i dajte mu prijateljski naziv na kartici konstruktor (Dizajn). Ovo je obavezan korak.

Zatim možete koristiti bilo koju od tri metode za odabir:

• Pritisnite dugme Dodaj modelu (Dodaj u model podataka) tabulator powerpivot tabulator Početna (Dom).
  
• Odabir timova Umetanje – zaokretna tabela (Insert — Pivot Table) i uključite potvrdni okvir Dodajte ove podatke u model podataka (Dodajte ove podatke u model podataka). U ovom slučaju, prema podacima učitanim u Model, odmah se gradi i pivot tabela.
  
• Na kartici Napredno podaci (Datum) kliknite na dugme Iz tabele/opseg (Iz tabele/dometa)da učitamo našu tabelu u Power Query editor. Ovaj put je najduži, ali, po želji, ovdje možete izvršiti dodatno čišćenje, uređivanje i sve vrste transformacija, u čemu je Power Query vrlo jak.

  Zatim se pročešljani podaci učitavaju u Model pomoću naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…). U prozoru koji se otvori odaberite opciju Samo stvorite vezu (Samo kreirajte vezu) i, što je najvažnije, stavite kvačicu Dodajte ove podatke u model podataka (Dodajte ove podatke u model podataka).
  

Izrađujemo sažetak modela podataka

Da biste napravili sažeti model podataka, možete koristiti bilo koji od tri pristupa:

• Pritisnite dugme sumarna tabela (Pivot Table) u prozoru Power Pivot.
• Odaberite komande u programu Excel Umetanje – zaokretna tabela i prebacite na mod Koristite model podataka ove knjige (Insert — Pivot Table — Koristite model podataka ove radne knjige).
• Odabir timova Umetanje – zaokretna tabela (Insert — Pivot Table) i uključite potvrdni okvir Dodajte ove podatke u model podataka (Dodajte ove podatke u model podataka). Trenutna “pametna” tabela će biti učitana u model i sažeta tabela će biti napravljena za ceo model.

Sada kada smo shvatili kako učitati podatke u model podataka i napraviti sažetak na njemu, istražimo prednosti i prednosti koje nam ovo daje.

Prednost 1: Relacije između tabela bez upotrebe formula

Redovni sažetak se može napraviti samo koristeći podatke iz jedne izvorne tabele. Ako ih imate nekoliko, na primjer, prodaja, cjenik, imenik kupaca, registar ugovora itd., tada ćete prvo morati prikupiti podatke iz svih tabela u jednu koristeći funkcije kao što je VLOOKUP (Vlookup), INDEX (INDEX), IZLOŽENIJI (UTAKMICA), SUMMESLIMN (SUMIFS) i slično. Ovo je dugo, zamorno i dovodi vaš Excel u „misao“ sa velikom količinom podataka.

U slučaju sažetka modela podataka, sve je mnogo jednostavnije. Dovoljno je jednom postaviti odnose između tabela u Power Pivot prozoru – i gotovo. Da biste to učinili, na kartici powerpivot pritisnite dugme upravljanje (Upravljaj) a zatim u prozoru koji se pojavi – dugme Prikaz grafikona (Prikaz dijagrama). Ostaje prevući uobičajena (ključna) imena kolona (polja) između tabela kako biste kreirali veze:

Nakon toga, u sažetku za model podataka, možete ubaciti u područje sažetka (redovi, stupci, filteri, vrijednosti) bilo koja polja iz bilo koje povezane tablice – sve će biti povezano i izračunato automatski:

Prednost 2: Brojite jedinstvene vrijednosti

Uobičajena zaokretna tabela daje nam priliku da izaberemo jednu od nekoliko ugrađenih funkcija izračunavanja: zbroj, prosjek, broj, minimum, maksimum, itd. U sažetku modela podataka, ovoj standardnoj listi je dodana vrlo korisna funkcija za brojanje broj jedinstvenih (neponovljivih vrijednosti). Uz njegovu pomoć, na primjer, možete lako prebrojati broj jedinstvenih artikala robe (aranžmana) koje prodajemo u svakom gradu.

Desni klik na polje – komandu Opcije polja vrijednosti i na kartici operacija Izabrati Broj različitih elemenata (različiti broj):

Prednost 3: Prilagođene DAX formule

Ponekad morate izvršiti razne dodatne proračune u pivot tablicama. U redovnim rezimeima, to se radi pomoću izračunatih polja i objekata, dok sažetak modela podataka koristi mjere u posebnom DAX jeziku (DAX = Izrazi analize podataka).

Da biste kreirali mjeru, odaberite na kartici powerpivot naredba Mjere – Kreirajte mjeru (Mjere — Nova mjera) ili samo kliknite desnim tasterom miša na tabelu na listi Pivot Fields i izaberite Dodajte mjeru (Dodaj mjeru) u kontekstualnom meniju:

U prozoru koji se otvori postavite:

• Naziv tabelegdje će kreirana mjera biti pohranjena.
• Naziv mjere – bilo koje ime koje razumijete za novo polje.
• Opis – opciono.
• formula – najvažnije, jer ovdje ili ručno unosimo, ili kliknemo na dugme fx i odaberite DAX funkciju sa liste, koja bi trebala izračunati rezultat kada zatim bacimo našu mjeru u područje vrijednosti.
• U donjem dijelu prozora možete odmah postaviti format broja za mjeru u listi kategorija.
  

Jezik DAX nije uvek lak za razumevanje jer ne operiše sa pojedinačnim vrednostima, već sa celim kolonama i tabelama, odnosno zahteva izvesno restrukturiranje razmišljanja prema klasičnim Excel formulama. Međutim, isplati se, jer je moć njegovih mogućnosti u obradi velikih količina podataka teško precijeniti.

Korist 4: Prilagođene hijerarhije polja

Često, kada kreirate standardne izvještaje, morate ubaciti iste kombinacije polja u zaokretne tablice u datom nizu, npr. Godina-Kvart-Mjesec-Dan, ili Kategorija-Proizvod, ili Država-Grad-Klijent itd. U sažetku modela podataka, ovaj problem se lako rješava kreiranjem vlastitog hijerarhije — prilagođeni setovi polja.

U prozoru Power Pivot prebacite se u režim grafikona pomoću dugmeta Prikaz grafikona tabulator Početna (Početna — Prikaz dijagrama), odaberite sa Ctrl željena polja i kliknite desnim tasterom miša na njih. Kontekstni meni će sadržati naredbu Kreirajte hijerarhiju (Kreirajte hijerarhiju):

Stvorena hijerarhija se može preimenovati i mišem uvući u nju potrebna polja, tako da se kasnije u jednom pokretu mogu baciti u sažetak:

Prednost 5: Prilagođene šablone

Nastavljajući ideju iz prethodnog paragrafa, u sažetku modela podataka, možete kreirati i vlastite skupove elemenata za svako polje. Na primjer, iz cijele liste gradova možete lako napraviti skup samo onih koji su u vašoj nadležnosti. Ili sakupite samo svoje kupce, svoju robu itd. u poseban set.

Da biste to učinili, na kartici Analiza zaokretne tabele u padajućoj listi Polja, stavke i skupovi postoje odgovarajuće komande (Analiziraj — Filds, Items & Sets — Kreirajte skup na osnovu stavki reda/kolone):

U prozoru koji se otvori, možete selektivno ukloniti, dodati ili promijeniti položaj bilo kojeg elementa i spremiti rezultirajući skup pod novim imenom:

Svi kreirani skupovi će biti prikazani na panelu Polja zaokretne tabele u zasebnom folderu, odakle se mogu slobodno prevlačiti u oblasti redova i kolona bilo koje nove zaokretne tabele:

Prednost 6: Selektivno sakrijte tabele i kolone

Iako je to mala, ali u nekim slučajevima vrlo ugodna prednost. Desnim klikom na naziv polja ili na karticu tabele u prozoru Power Pivot, možete odabrati naredbu Sakrij iz Client Toolkita (Sakrij od klijentskih alata):

Skrivena kolona ili tabela će nestati iz okna liste polja zaokretne tabele. Vrlo je zgodno ako trebate sakriti od korisnika neke pomoćne stupce (na primjer, izračunate ili stupce s ključnim vrijednostima za kreiranje odnosa) ili čak cijele tablice.

Prednost 7. Napredno drill-down

Ako dvaput kliknete na bilo koju ćeliju u području vrijednosti u običnoj zaokretnoj tablici, Excel će na zasebnom listu prikazati kopiju izvornog fragmenta podataka koji je bio uključen u izračunavanje ove ćelije. Ovo je vrlo zgodna stvar, službeno nazvana Drill-down (u obično se kaže "fail").

U sažetku modela podataka, ovaj zgodan alat radi suptilnije. Stajanjem na bilo koju ćeliju sa rezultatom koji nas zanima, možete kliknuti na ikonu sa povećalom koja iskače pored nje (zove se Express Trends), a zatim odaberite bilo koje polje koje vas zanima u bilo kojoj povezanoj tabeli:

Nakon toga, trenutna vrijednost (Model = Explorer) će otići u područje filtera, a rezime će izraditi uredi:

Naravno, takav postupak se može ponoviti mnogo puta, dosljedno udubljujući svoje podatke u smjeru koji vas zanima.

Korist 8: Pretvorite Pivot u Cube funkcije

Ako odaberete bilo koju ćeliju u sažetku za model podataka, a zatim odaberete na kartici Analiza zaokretne tabele naredba OLAP alati – Pretvorite u formule (Analiza — OLAP alati — Pretvori u formule), tada će se cijeli sažetak automatski pretvoriti u formule. Sada će se vrijednosti polja u području reda i stupca i rezultati u području vrijednosti preuzeti iz modela podataka pomoću posebnih funkcija kocke: CUBEVALUE i CUBEMEMBER:

Tehnički, to znači da sada nemamo posla sa rezimeom, već sa nekoliko ćelija sa formulama, tj. lako možemo napraviti sve transformacije sa našim izvještajem koje nisu dostupne u sažetku, na primjer, ubaciti nove redove ili stupce u sredinu izvještaja, napraviti dodatne kalkulacije unutar rezimea, urediti ih na željeni način itd.

Istovremeno, veza sa izvornim podacima, naravno, ostaje iu budućnosti će se ove formule ažurirati kada se izvori promene. Ljepota!

• Plan-fakt analiza u pivot tabeli sa Power Pivot i Power Queryjem
• Zaokretna tabela sa višerednim zaglavljem
• Kreirajte bazu podataka u Excelu koristeći Power Pivot