Prednosti Pivota prema podatkovnom modelu

Prilikom izrade zaokretne tablice u Excelu, u prvom dijaloškom okviru, gdje se od nas traži da postavimo početni raspon i odaberemo mjesto za umetanje zaokretne tablice, nalazi se neupadljiv, ali vrlo važan potvrdni okvir ispod – Dodajte ove podatke u podatkovni model (Dodajte ove podatke na podatkovni model) i, malo više, prekidač Koristite model podataka ove knjige (Koristite podatkovni model ove radne knjige):

Nažalost, mnogi korisnici koji već duže vrijeme poznaju pivot tablice i uspješno ih koriste u svom radu ponekad zapravo ne razumiju značenje ovih opcija i nikada ih ne koriste. I uzalud. Uostalom, izrada pivot tablice za podatkovni model daje nam nekoliko vrlo važnih prednosti u usporedbi s klasičnom Excel pivot tablicom.

Međutim, prije nego što izbliza razmotrimo ove "kiflice", prvo shvatimo što je, zapravo, ovaj podatkovni model?

Što je podatkovni model

Model podataka (skraćeno MD ili DM = Data Model) posebno je područje unutar Excel datoteke u koje možete pohraniti tabularne podatke – jednu ili više tablica povezanih, po želji, jedna s drugom. Zapravo, ovo je mala baza podataka (OLAP kocka) ugrađena u Excel radnu knjigu. U usporedbi s klasičnom pohranom podataka u obliku običnih (ili pametnih) tablica na listovima samog Excela, podatkovni model ima nekoliko značajnih prednosti:

• Stolovi mogu biti do 2 milijarde linija, a Excel list može stati malo više od 1 milijuna.
• Unatoč golemoj veličini, vrši se obrada takvih tablica (filtriranje, sortiranje, izračuni na njima, izgradnja sažetka itd.) vrlo brzo Puno brži od samog Excela.
• S podacima u Modelu možete izvršiti dodatne (po želji vrlo složene) izračune pomoću ugrađeni DAX jezik.
  
• Sve informacije učitane u podatkovni model vrlo su jako stisnut pomoću posebnog ugrađenog arhivara i prilično umjereno povećava veličinu izvorne Excel datoteke.
  

Modelom upravlja i izračunava poseban dodatak ugrađen u Microsoft Excel – powerpivoto čemu sam već pisao. Da biste ga omogućili, na kartici razvijač klik COM dodaci (Razvojni programer — COM dodaci) i označite odgovarajuću kućicu:

Ako su kartice razvijač (Programer)ne možete ga vidjeti na vrpci, možete ga uključiti Datoteka – Opcije – Postavljanje vrpce (Datoteka — Opcije — Prilagodi vrpcu). Ako u gore prikazanom prozoru na popisu COM dodataka nemate Power Pivot, tada on nije uključen u vašu verziju Microsoft Officea 🙁

Na kartici Power Pivot koja se pojavi bit će veliki svijetlozeleni gumb Upravljanje (Upravljati), klikom na koji će se otvoriti Power Pivot prozor na vrhu Excela, gdje ćemo vidjeti sadržaj podatkovnog modela trenutne knjige:

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

Učitajte tablice u podatkovni model

Da bismo učitali podatke u model, prvo tablicu pretvaramo u dinamičnu "pametnu" tipkovničku prečicu Ctrl+T i dajte mu prijateljski naziv na kartici Konstruktor (Oblikovati). Ovo je potreban korak.

Tada možete koristiti bilo koju od tri metode koje možete izabrati:

• pritisni gumb Dodaj u model (Dodaj podatkovnom modelu) kartica powerpivot kartica Naslovna (Dom).
  
• Odabir timova Umetanje – zaokretna tablica (Umetni — zaokretna tablica) i uključite potvrdni okvir Dodajte ove podatke u podatkovni model (Dodajte ove podatke u podatkovni model). U ovom slučaju, prema podacima učitanim u model, također se odmah gradi zaokretna tablica.
  
• Na kartici Napredno Datum (Datum) kliknite na gumb Iz tablice/raspona (Iz tablice/raspona)da učitamo našu tablicu u Power Query editor. Ovaj put je najduži, ali, po želji, ovdje možete izvršiti dodatno čišćenje podataka, uređivanje i sve vrste transformacija, u čemu je Power Query vrlo jak.

  Zatim se češljani podaci naredbom učitavaju u model Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u…). U prozoru koji se otvori odaberite opciju Samo stvorite vezu (Samo stvoriti vezu) i što je najvažnije, stavite kvačicu Dodajte ove podatke u podatkovni model (Dodajte ove podatke u podatkovni model).
  

Izrađujemo sažetak podatkovnog modela

Za izradu sažetog podatkovnog modela možete koristiti bilo koji od tri pristupa:

• pritisni gumb sažetak tablice (zaokretna tablica) u prozoru Power Pivot.
• Odaberite naredbe u Excelu Umetanje – zaokretna tablica i prebacite se u mod Koristite model podataka ove knjige (Umetni — Zaokretna tablica — Koristite podatkovni model ove radne knjige).
• Odabir timova Umetanje – zaokretna tablica (Umetni — zaokretna tablica) i uključite potvrdni okvir Dodajte ove podatke u podatkovni model (Dodajte ove podatke u podatkovni model). Trenutna "pametna" tablica će se učitati u model i tablica sažetka će se izgraditi za cijeli model.

Sada kada smo otkrili kako učitati podatke u podatkovni model i na temelju toga izraditi sažetak, istražimo prednosti i prednosti koje nam to daje.

Prednost 1: Odnosi između tablica bez upotrebe formula

Redoviti sažetak može se izgraditi samo pomoću podataka iz jedne izvorne tablice. Ako ih imate više, npr. prodaju, cjenik, imenik kupaca, registar ugovora i sl., tada ćete najprije morati prikupiti podatke iz svih tablica u jednu pomoću funkcija kao što je VLOOKUP (VLOOKUP), KAZALO (INDEKS), EKSPONIRANIJI (UTAKMICA), SUMMESLIMN (SUMIFS) i slično. Ovo je dugo, zamorno i tjera vaš Excel u "razmišljanje" s velikom količinom podataka.

U slučaju sažetka podatkovnog modela sve je puno jednostavnije. Dovoljno je jednom postaviti odnose između tablica u Power Pivot prozoru – i gotovo je. Da biste to učinili, na kartici powerpivot pritisni gumb Upravljanje (Upravljati) a zatim u prozoru koji se pojavi – gumb Prikaz grafikona (Prikaz dijagrama). Preostaje povući uobičajene (ključne) nazive stupaca (polja) između tablica za stvaranje veza:

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

Prednost 2: Prebrojite jedinstvene vrijednosti

Uobičajena zaokretna tablica daje nam mogućnost odabira jedne od nekoliko ugrađenih funkcija izračuna: zbroj, prosjek, brojanje, minimum, maksimum itd. U sažetku podatkovnog modela ovom standardnom popisu dodana je vrlo korisna funkcija za brojanje broj jedinstvenih (vrijednosti koje se ne ponavljaju). Uz njegovu pomoć, na primjer, možete jednostavno prebrojati broj jedinstvenih artikala (asortimana) koje prodajemo u svakom gradu.

Desni klik na polje – naredba Opcije polja vrijednosti i na kartici operacija Odaberite Broj različitih elemenata (različit broj):

Prednost 3: prilagođene DAX formule

Ponekad morate izvršiti razne dodatne izračune u pivot tablicama. U redovnim sažecima to se radi pomoću izračunatih polja i objekata, dok sažetak podatkovnog modela koristi mjere u posebnom DAX jeziku (DAX = Data Analysis Expressions).

Za izradu mjere odaberite na kartici powerpivot naredba Mjere – Stvorite mjeru (Mjere — Nova mjera) ili samo desnom tipkom miša kliknite tablicu na popisu Pivot Fields i odaberite Dodajte mjeru (Dodaj mjeru) u kontekstnom izborniku:

U prozoru koji se otvori postavite:

• Naziv tablicegdje će se pohraniti kreirana mjera.
• Naziv mjere – bilo koji naziv koji razumijete za novo polje.
• Opis – neobavezno.
• Formula – najvažnija stvar, jer ovdje ili ručno unosimo ili kliknemo na gumb fx i odaberite DAX funkciju s popisa, koja bi trebala izračunati rezultat kada bacimo svoju mjeru u područje vrijednosti.
• U donjem dijelu prozora možete odmah postaviti format broja za mjeru na listi Kategorija.
  

DAX jezik nije uvijek jednostavan za razumijevanje jer ne operira s pojedinačnim vrijednostima, već s cijelim stupcima i tablicama, tj. zahtijeva određeno restrukturiranje razmišljanja nakon klasičnih Excel formula. Međutim, isplati se jer je snagu njegovih mogućnosti u obradi velikih količina podataka teško precijeniti.

Prednost 4: prilagođena hijerarhija polja

Često, kada stvarate standardna izvješća, morate ubaciti iste kombinacije polja u zaokretne tablice u zadanom nizu, na primjer Godina-Kvart-Mjesec-Dan, ili Kategorija-Proizvod, ili Država-Grad-Klijent itd. U sažetku podatkovnog modela ovaj se problem lako rješava stvaranjem vlastitog hijerarhije — prilagođeni skupovi polja.

U prozoru Power Pivot prebacite se u način grafikona pomoću gumba Prikaz grafikona kartica Naslovna (Početna — Prikaz dijagrama), odaberite s Ctrl željena polja i desnom tipkom miša kliknite na njih. Kontekstni izbornik sadržavat će naredbu Stvorite hijerarhiju (Stvori hijerarhiju):

Stvorenu hijerarhiju moguće je preimenovati i u nju mišem povući tražena polja, kako bi se kasnije u jednom pokretu ubacila u sažetak:

Prednost 5: Šablone po narudžbi

Nastavljajući ideju iz prethodnog odlomka, u sažetku podatkovnog modela također možete stvoriti vlastite skupove elemenata za svako polje. Na primjer, iz cijelog popisa gradova možete jednostavno napraviti skup samo onih koji su u vašem području odgovornosti. Ili sakupite samo svoje kupce, svoju robu itd. u poseban set.

Da biste to učinili, na kartici Analiza zaokretne tablice na padajućem popisu Polja, stavke i skupovi postoje odgovarajuće naredbe (Analiziraj — Fields, Items & Sets — Stvorite skup na temelju stavki retka/stupca):

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

Svi stvoreni skupovi bit će prikazani na ploči Polja zaokretne tablice u zasebnoj mapi, odakle se mogu slobodno povući u područja redaka i stupaca bilo koje nove zaokretne tablice:

Prednost 6: Selektivno skrivanje tablica i stupaca

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

Skriveni stupac ili tablica nestat će iz okna Popis polja zaokretne tablice. Vrlo je prikladno ako od korisnika trebate sakriti neke pomoćne stupce (na primjer, izračunate ili stupce s ključnim vrijednostima za stvaranje 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, tada će Excel na zasebnom listu prikazati kopiju fragmenta izvornih podataka koji je bio uključen u izračun ove ćelije. Ovo je vrlo zgodna stvar, službeno nazvana Drill-down (u pravilu kažu "fail").

U sažetku podatkovnog modela ovaj praktični alat radi suptilnije. Stojeći na bilo kojoj ćeliji s rezultatom koji nas zanima, možete kliknuti na ikonu s povećalom koja se pojavljuje pokraj nje (zove se Ekspresni trendovi), a zatim odaberite bilo koje polje koje vas zanima u bilo kojoj povezanoj tablici:

Nakon toga, trenutna vrijednost (Model = Explorer) će ići u područje filtera, a sažetak će biti izgrađen po uredima:

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

Prednost 8: Pretvorite zaokretne u kockaste funkcije

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

Tehnički, to znači da sada nemamo posla sa sažetkom, već s nekoliko ćelija s formulama, tj. s našim izvješćem možemo jednostavno napraviti sve transformacije koje nisu dostupne u sažetku, na primjer, umetnuti nove retke ili stupce u sredinu izvješća, napraviti dodatne izračune unutar sažetka, rasporediti ih na željeni način itd.

Pritom veza s izvornim podacima, naravno, ostaje iu budućnosti će se ove formule ažurirati kada se izvori promijene. Ljepota!

• Analiza činjenica plana u zaokretnoj tablici s Power Pivot i Power Query
• Zaokretna tablica s višelinijskim zaglavljem
• Stvorite bazu podataka u Excelu pomoću Power Pivota