Prednosti Pivot by Data Model

Pri gradnji vrtilne tabele v Excelu je v prvem pogovornem oknu, kjer smo pozvani, da nastavimo začetni obseg in izberemo mesto za vstavljanje vrtilne tabele, spodaj neopazno, a zelo pomembno potrditveno polje – Te podatke dodajte v podatkovni model (Dodaj te podatke v podatkovni model) in malo višje stikalo Uporabi podatkovni model te knjige (Uporabi podatkovni model tega delovnega zvezka):

Na žalost mnogi uporabniki, ki že dolgo poznajo vrtilne tabele in jih uspešno uporabljajo pri svojem delu, včasih ne razumejo zares pomena teh možnosti in jih nikoli ne uporabijo. In zaman. Navsezadnje nam ustvarjanje vrtilne tabele za podatkovni model daje več zelo pomembnih prednosti v primerjavi s klasično Excelovo vrtilno tabelo.

Vendar, preden razmislimo o teh "žemljicah" od blizu, najprej razumemo, kaj je pravzaprav ta podatkovni model?

Kaj je podatkovni model

Podatkovni model (skrajšano kot MD ali DM = podatkovni model) je posebno področje znotraj datoteke Excel, kamor lahko shranite tabelarične podatke – eno ali več tabel, ki so po želji povezane med seboj. Pravzaprav je to majhna zbirka podatkov (kocka OLAP), vdelana v Excelov delovni zvezek. V primerjavi s klasičnim shranjevanjem podatkov v obliki običajnih (ali pametnih) tabel na samih Excelovih listih ima podatkovni model več pomembnih prednosti:

• Mize so lahko do 2 milijardi vrstic, Excelov list pa lahko sprejme nekaj več kot 1 milijon.
• Kljub velikanski velikosti se izvaja obdelava takšnih tabel (filtriranje, razvrščanje, izračuni na njih, sestavljanje povzetka itd.) zelo hitro Veliko hitreje kot sam Excel.
• S podatki v modelu lahko izvedete dodatne (po želji zelo zapletene) izračune z uporabo vgrajen jezik DAX.
  
• Vse informacije, naložene v podatkovni model, so zelo močno stisnjena z uporabo posebnega vgrajenega arhivarja in precej zmerno poveča velikost izvirne datoteke Excel.
  

Model upravlja in izračunava poseben dodatek, vgrajen v Microsoft Excel – powerpivoto kateri sem že pisal. Če ga želite omogočiti, na zavihku razvijalec klik dodatki COM (Razvijalec — dodatki COM) in označite ustrezno polje:

Če zavihki razvijalec (Razvijalec)na traku ga ne vidite, lahko ga vklopite skozi Datoteka – Možnosti – Nastavitev traku (Datoteka — Možnosti — Prilagodi trak). Če v oknu, prikazanem zgoraj na seznamu dodatkov COM, nimate Power Pivot, potem ni vključen v vašo različico Microsoft Officea 🙁

Na zavihku Power Pivot, ki se prikaže, bo velik svetlo zelen gumb upravljanje (Upravljanje), s klikom na katerega se odpre okno Power Pivot na vrhu Excela, kjer bomo videli vsebino podatkovnega modela trenutne knjige:

Pomembna opomba ob poti: Excelov delovni zvezek lahko vsebuje samo en podatkovni model.

Naloži tabele v podatkovni model

Za nalaganje podatkov v model tabelo najprej spremenimo v dinamično »pametno« bližnjico na tipkovnici Ctrl+T in mu na zavihku dajte prijazno ime Konstruktor (Oblikovanje). To je obvezen korak.

Nato lahko izberete katero koli od treh metod:

• pritisni gumb Dodaj v model (Dodaj v podatkovni model) tab powerpivot tab Domov (Domov).
  
• Izbira ekip Vstavi – vrtilna tabela (Vstavi — vrtilna tabela) in vklopite potrditveno polje Te podatke dodajte v podatkovni model (Dodaj te podatke v podatkovni model). V tem primeru se glede na podatke, naložene v Model, takoj zgradi tudi vrtilna tabela.
  
• Na zavihku Napredno datum (Datum) kliknite na gumb Iz tabele/razpona (Iz tabele/razpona)da naložimo našo tabelo v urejevalnik Power Query. Ta pot je najdaljša, vendar po želji lahko tukaj izvedete dodatno čiščenje podatkov, urejanje in vse vrste transformacij, v katerih je Power Query zelo močan.

  Nato se česani podatki naložijo v model z ukazom Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na…). V oknu, ki se odpre, izberite možnost Samo ustvarite povezavo (Ustvari samo povezavo) in kar je najpomembneje, označite Te podatke dodajte v podatkovni model (Dodaj te podatke v podatkovni model).
  

Izdelamo povzetek podatkovnega modela

Če želite zgraditi povzetek podatkovnega modela, lahko uporabite katerega koli od treh pristopov:

• Pritisnite gumb povzetek tabele (Vrteča miza) v oknu Power Pivot.
• Izberite ukaze v Excelu Vstavi – vrtilna tabela in preklopite v način Uporabi podatkovni model te knjige (Vstavi — vrtilna tabela — uporabi podatkovni model tega delovnega zvezka).
• Izbira ekip Vstavi – vrtilna tabela (Vstavi — vrtilna tabela) in vklopite potrditveno polje Te podatke dodajte v podatkovni model (Dodaj te podatke v podatkovni model). Trenutna »pametna« tabela bo naložena v model in izdelana bo tabela s povzetkom za celoten model.

Zdaj, ko smo ugotovili, kako naložiti podatke v podatkovni model in na njih sestaviti povzetek, raziščimo koristi in prednosti, ki nam jih to daje.

Prednost 1: Relacije med tabelami brez uporabe formul

Običajni povzetek je mogoče sestaviti samo z uporabo podatkov iz ene izvorne tabele. Če jih imate več, na primer prodaja, cenik, imenik kupcev, register pogodb itd., potem boste morali najprej zbrati podatke iz vseh tabel v eno s funkcijami, kot je VLOOKUP. (VLOOKUP), KAZALO (KAZALO), BOLJ IZPOSTAVLJEN (TEKMA), SUMMESLIMN (SUMIFS) in podobni. To je dolgo, dolgočasno in požene vaš Excel v »razmislek« z veliko količino podatkov.

V primeru povzetka podatkovnega modela je vse veliko preprostejše. Dovolj je, da enkrat v oknu Power Pivot nastavite relacije med tabelami – in to je opravljeno. Če želite to narediti, na zavihku powerpivot pritisni gumb upravljanje (Upravljanje) in nato v oknu, ki se prikaže – gumb Pogled grafikona (Pogled diagrama). Ostaja še vlečenje skupnih (ključnih) imen stolpcev (polj) med tabelami, da ustvarite povezave:

Nato lahko v povzetku za podatkovni model vržete v območje povzetka (vrstice, stolpce, filtre, vrednosti) poljubna polja iz poljubnih povezanih tabel – vse bo povezano in samodejno izračunano:

Prednost 2: Štetje edinstvenih vrednosti

Običajna vrtilna tabela nam daje možnost, da izberemo eno od več vgrajenih funkcij za izračun: vsota, povprečje, štetje, minimum, maksimum itd. V povzetku podatkovnega modela je temu standardnemu seznamu dodana zelo uporabna funkcija za štetje število edinstvenih (neponavljajočih se vrednosti). Z njegovo pomočjo lahko na primer enostavno preštejete število edinstvenih kosov blaga (razpon), ki jih prodajamo v posameznem mestu.

Desni klik na polje – ukaz Možnosti polja vrednosti in na zavihku operacija Izberite Število različnih elementov (različno število):

Prednost 3: Formule DAX po meri

Včasih morate izvesti različne dodatne izračune v vrtilnih tabelah. Pri običajnih povzetkih se to izvede z uporabo izračunanih polj in objektov, medtem ko povzetek podatkovnega modela uporablja mere v posebnem jeziku DAX (DAX = Data Analysis Expressions).

Če želite ustvariti mero, izberite na zavihku powerpivot Ukaz Mere – Ustvari mero (Ukrepi – Nova mera) ali samo z desno miškino tipko kliknite tabelo na seznamu vrtilnih polj in izberite Dodajte mero (Dodaj mero) v kontekstnem meniju:

V oknu, ki se odpre, nastavite:

• Ime tabelekam bo shranjena ustvarjena mera.
• Ime mere – poljubno ime za novo polje, ki ga razumete.
• Opis - neobvezno.
• Formula – najpomembnejša stvar, saj tukaj ročno vnesemo ali kliknemo na gumb fx in s seznama izberite funkcijo DAX, ki naj izračuna rezultat, ko nato svojo mero vržemo v območje vrednosti.
• V spodnjem delu okna lahko takoj nastavite obliko števila za mero na seznamu Kategorija.
  

Jezik DAX ni vedno enostaven za razumevanje, ker ne operira s posameznimi vrednostmi, temveč s celimi stolpci in tabelami, kar pomeni, da zahteva nekaj prestrukturiranja razmišljanja po klasičnih Excelovih formulah. Vendar je vredno, saj je moč njegovih zmogljivosti pri obdelavi velikih količin podatkov težko preceniti.

Prednost 4: Hierarhije polj po meri

Pri ustvarjanju standardnih poročil morate pogosto vrniti iste kombinacije polj v vrtilne tabele v določenem zaporedju, npr. Leto-Četrt-Mesec-Danali Kategorija-izdelekali Država-mesto-stranka itd. V povzetku podatkovnega modela je to težavo enostavno rešiti z ustvarjanjem lastnega hierarhije — nizi polj po meri.

V oknu Power Pivot preklopite v način grafikona z gumbom Pogled grafikona tab Domov (Domov — pogled diagrama), izberite z Ctrl želena polja in jih kliknite z desno tipko miške. Kontekstni meni bo vseboval ukaz Ustvari hierarhijo (Ustvari hierarhijo):

Ustvarjeno hierarhijo lahko preimenujete in vanjo z miško povlečete zahtevana polja, tako da jih pozneje v enem gibu vržete v povzetek:

Prednost 5: Šablone po meri

Če nadaljujemo idejo iz prejšnjega odstavka, lahko v povzetku podatkovnega modela ustvarite tudi lastne nize elementov za vsako polje. Na primer, iz celotnega seznama mest lahko enostavno sestavite nabor samo tistih, ki so v vašem območju odgovornosti. Ali pa zberite samo vaše stranke, svoje blago itd. v poseben komplet.

Če želite to narediti, na zavihku Analiza vrtilne tabele na spustnem seznamu Polja, postavke in nizi obstajajo ustrezni ukazi (Analiziraj - Fields, Items & Sets — ustvarite niz na podlagi elementov vrstice/stolpca):

V oknu, ki se odpre, lahko selektivno odstranite, dodate ali spremenite položaj poljubnih elementov in shranite nastali niz pod novim imenom:

Vsi ustvarjeni nizi bodo prikazani na plošči Polja vrtilne tabele v ločeni mapi, od koder jih je mogoče prosto povleči v območja vrstic in stolpcev katere koli nove vrtilne tabele:

Prednost 6: Selektivno skrivanje tabel in stolpcev

Čeprav je to majhna, a v nekaterih primerih zelo prijetna prednost. Z desnim klikom na ime polja ali na zavihek tabele v oknu Power Pivot lahko izberete ukaz Skrij iz orodja za odjemalce (Skrij iz orodij za odjemalce):

Skriti stolpec ali tabela bo izginila iz podokna Seznam polj vrtilne tabele. Zelo priročno je, če morate pred uporabnikom skriti nekatere pomožne stolpce (na primer izračunane ali stolpce s ključnimi vrednostmi za ustvarjanje odnosov) ali celo celotne tabele.

Prednost 7. Napredno vrtanje navzdol

Če dvokliknete katero koli celico v območju vrednosti v običajni vrtilni tabeli, Excel na ločenem listu prikaže kopijo fragmenta izvornih podatkov, ki je bil vključen v izračun te celice. To je zelo priročna stvar, ki se uradno imenuje Drill-down (ponavadi rečejo "fail").

V povzetku podatkovnega modela to priročno orodje deluje bolj subtilno. Ko stojite na kateri koli celici z rezultatom, ki nas zanima, lahko kliknete ikono s povečevalnim steklom, ki se pojavi poleg nje (imenuje se Express Trendi) in nato izberite katero koli polje, ki vas zanima v kateri koli povezani tabeli:

Po tem bo trenutna vrednost (Model = Explorer) šla v območje filtra, povzetek pa bo sestavljen po uradih:

Seveda lahko tak postopek večkrat ponovimo in se dosledno poglabljamo v svoje podatke v smeri, ki vas zanima.

Prednost 8: Pretvorite vrtilne v kocke

Če izberete katero koli celico v povzetku za podatkovni model in nato izberete na zavihku Analiza vrtilne tabele Ukaz Orodja OLAP – Pretvori v formule (Analiziraj — Orodja OLAP — Pretvori v formule), potem bo celoten povzetek samodejno pretvorjen v formule. Zdaj bodo vrednosti polj v območju vrstice-stolpca in rezultati v območju vrednosti pridobljeni iz podatkovnega modela z uporabo posebnih funkcij kocke: CUBEVALUE in CUBEMEMBER:

Tehnično to pomeni, da zdaj nimamo opravka s povzetkom, ampak z več celicami s formulami, kar pomeni, da lahko z našim poročilom enostavno naredimo vse transformacije, ki niso na voljo v povzetku, na primer v sredino vstavimo nove vrstice ali stolpce. poročila, naredite morebitne dodatne izračune znotraj povzetka, jih poljubno uredite ipd.

Ob tem povezava z izvornimi podatki seveda ostaja in v prihodnje se bodo te formule dopolnjevale ob spremembi virov. Lepota!

• Analiza dejstev načrta v vrtilni tabeli s Power Pivot in Power Query
• Vrtilna tabela z večvrstično glavo
• Ustvarite bazo podatkov v Excelu s Power Pivot