Tablice datuma u dodatku Power Pivot ključne su za pregledavanje i izračun podataka tijekom vremena. U ovom se članku detaljno razumiju datumski tablice i način na koji ih možete stvoriti u dodatku Power Pivot. U ovom se članku posebno opisuje sljedeće:
-
Zašto je tablica datuma važna za pregledavanje i izračunavanje podataka prema datumima i vremenu.
-
Dodavanje tablice datuma u podatkovni model pomoću dodatka Power Pivot.
-
Stvaranje novih stupaca datuma kao što su Godina, Mjesec i Razdoblje u tablici datuma.
-
Stvaranje odnosa između tablica datuma i tablica činjenica.
-
Kako raditi s vremenom.
Ovaj je članak namijenjen korisnicima koji su novi korisnici dodatka Power Pivot. No važno je već dobro razumjeti uvoz podataka, stvaranje odnosa i stvaranje izračunatih stupaca i mjera.
U ovom se članku ne opisuje korištenje funkcija DAX Time-Intelligence u formulama za mjerenje. Dodatne informacije o stvaranju mjera pomoću funkcija inteligencije vremena za DAX potražite u članku Inteligencija vremena u dodatku Power Pivot u programu Excel.
Napomena: U dodatku Power Pivot nazivi "mjera" i "izračunato polje" sinonimi su. Mjeru naziva upotrebljavamo u cijelom ovom članku. Dodatne informacije potražite u članku Mjere u dodatku Power Pivot.
Sadržaj
Objašnjenje tablica datuma
Gotovo sva analiza podataka obuhvaća pregledavanje i usporedbu podataka tijekom datuma i vremena. Možda ćete, primjerice, htjeti zbrojiti iznose prodaje za prošlo fiskalno tromjesečje, a zatim ih usporediti s drugim tromjesečjima ili pak izračunati završni saldo mjeseca za račun. U svakom od tih slučajeva datume koristite kao način grupiranje i agregacija prodajnih transakcija ili salda za određeno vremensko razdoblje.
Izvješće značajke Power View
Tablica datuma može sadržavati mnogo različitih prikaza datuma i vremena. Tablica datuma, primjerice, često sadrži stupce kao što su Fiskalna godina, Mjesec, Tromjesečje ili Razdoblje koje možete odabrati kao polja s popisa polja prilikom sječa i filtriranja podataka u zaokretnim tablicama ili izvješćima dodatka Power View.
Popis polja značajke Power View
Da bi stupci datuma, kao što su Godina, Mjesec i Tromjesečje, uvrstili sve datume unutar odgovarajućeg raspona, tablica datuma mora imati najmanje jedan stupac s skupom datuma koji se ne nalazi u nastavku. To znači da taj stupac mora imati jedan redak za svaki dan za svaku godinu uključen u tablicu datuma.
Ako, primjerice, podaci koje želite pregledati imaju datume od 1. veljače 2010. do 30. studenog 2012., a vi prijavite kalendarsku godinu, tada ćete htjeti tablicu datuma s rasponom datuma od 1. siječnja 2010. do 31. prosinca 2012. Svake godine u tablici datuma moraju sadržavati sve dane za svaku godinu. Ako ćete redovito osvježavati podatke novijim podacima, možda ćete završni datum htjeti izvesti za godinu ili dvije, pa ne morate ažurirati tablicu datuma kako vrijeme prolazi.
Date table with a contiguous set of dates
Ako izvješće o fiskalnoj godini, možete stvoriti tablicu datuma s skupom susjednih datuma za svaku fiskalnu godinu. Ako, primjerice, fiskalna godina počinje 1. ožujka, a imate podatke za fiskalne godine 2010. do trenutnog datuma (npr. u BJ 2013.), možete stvoriti tablicu datuma koja počinje 1. 3. 2009. i obuhvaća najmanje svaki dan u svakoj fiskalnoj godini do zadnjeg datuma fiskalne godine u fiskalnoj godini 2013.
Ako želite izvješće o kalendarskoj i fiskalnoj godini, ne morate stvarati zasebne tablice datuma. Jedna tablica datuma može sadržavati stupce za kalendarsku godinu, fiskalnu godinu, pa čak i trinaest četverotjesne kalendare. Važno je da tablica datuma sadrži skup neprekinutih datuma za sve godine.
Dodavanje tablice datuma u podatkovni model
Podatkovnom modelu možete dodati tablicu datuma na nekoliko načina:
-
Uvoz iz relacijske baze podataka ili iz drugog izvora podataka.
-
Stvorite tablicu datuma u programu Excel, a zatim kopirajte ili povežite novu tablicu u dodatku Power Pivot.
-
Uvoz iz trgovine Microsoft Azure Marketplace.
Pogledajmo sve ove pobliћe.
Uvoz iz relacijske baze podataka
Ako uvezete neke ili sve podatke iz podatkovnog skladišta ili neke druge vrste relacijske baze podataka, vjerojatno već postoji tablica datuma i odnosi između njega i ostalih podataka koje uvozite. Datumi i oblik vjerojatno će se podudarati s datumima u podacima o činjenicama, a datumi će vjerojatno dobro početi u prošlosti i otići daleko u budućnost. Tablica datuma koju želite uvesti može biti vrlo velika i sadržavati raspon datuma izvan onoga što morate uvrstiti u podatkovni model. Napredne značajke filtra čarobnjaka za uvoz tablica dodatka Power Pivot omogućuju selektivno odabir samo datuma i određenih stupaca koji su vam potrebni. To može znatno smanjiti veličinu radne knjige i poboljšati performanse.
Čarobnjak za uvoz tablica
U većini slučajeva nećete morati stvarati dodatne stupce kao što su Fiskalna godina, Tjedan, Naziv mjeseca itd. jer će već postojati u uvezenoj tablici. No kada u podatkovni model uvezete tablicu datuma, možda ćete morati stvoriti dodatne stupce datuma, ovisno o određenoj potrebi izvješćivanja. Srećom, to je jednostavno učiniti pomoću DAX-a. Kasnije ćete saznati više o stvaranju polja tablice datuma. Svako je okruženje drugačije. Ako niste sigurni imaju li izvori podataka povezani datum ili tablicu kalendara, obratite se administratoru baze podataka.
Stvaranje tablice datuma u programu Excel
U programu Excel možete stvoriti tablicu datuma, a zatim je kopirati u novu tablicu u podatkovnom modelu. To je vrlo jednostavno učiniti i pruža vam mnogo fleksibilnosti.
Kada u programu Excel stvorite tablicu datuma, počinjete jednim stupcem s rasponom datuma koji se ne nalaze u nastavku. Zatim možete stvoriti dodatne stupce kao što su Godina, Tromjesečje, Mjesec, Fiskalna godina, Razdoblje itd. na radnom listu programa Excel pomoću formula programa Excel ili, nakon kopiranja tablice u podatkovni model, možete ih stvoriti kao izračunate stupce. Stvaranje dodatnih stupaca datuma u dodatku Power Pivot opisano je u odjeljku Dodavanje novih stupaca datuma u tablicu datuma u nastavku ovog članka.
Upute: Stvaranje tablice datuma u programu Excel i kopiranje u podatkovni model
-
U programu Excel na praznom radnom listu u ćeliji A1 upišite naziv zaglavlja stupca da biste odredili raspon datuma. Obično će to biti datum, datum i vrijeme ili ključ datuma.
-
U ćeliju A2 upišite datum početka. Primjerice, 1. 1. 2010.
-
Kliknite ručicu za ispunu i povucite je prema dolje do broja retka koji sadrži završni datum. Na primjer, 31. 12. 2016.
-
Odaberite sve retke u stupcu Datum (uključujući naziv zaglavlja u ćeliji A1).
-
U grupi Stilovi kliknite Oblikuj kao tablicu, a zatim odaberite stil.
-
U dijaloškom okviru Oblikovanje kao tablice kliknite U redu.
-
Kopirajte sve retke, uključujući zaglavlje.
-
U dodatku Power Pivot na kartici Polazno kliknite Zalijepi.
-
U pretpregled lijepljenja > naziv tablice upišite naziv kao što su Datum ili Kalendar. Ostavite potvrđen okvir Koristi prvi redak kao zaglavlja stupaca, a zatim kliknite U redu.
Nova tablica datuma (u ovom primjeru s nazivom Kalendar) u dodatku Power Pivot izgleda ovako:
Napomena: Povezanu tablicu možete stvoriti i pomoću mogućnosti Dodaj u podatkovni model. No to nepotrebno velike radne knjige jer radna knjiga sadrži dvije verzije tablice datuma; u programu Excel, a drugi u dodatku Power Pivot.
Napomena: Datum naziva ključna je riječ u dodatku Power Pivot. Ako tablici koju stvorite u datumu dodatka Power Pivot dodijelite naziv tablice jednostrukim navodima u bilo kojoj DAX formuli na koju se pozivate u argumentu. Sve ogledne slike i formule u ovom članku odnose se na tablicu datuma stvorenu u dodatku Power Pivot pod nazivom Kalendar.
Sada imate tablicu datuma u podatkovnom modelu. Možete dodati nove stupce datuma, kao što su Godina, Mjesec itd. pomoću DAX-a.
Dodavanje novih stupaca datuma u tablicu datuma
Tablica datuma s jednim stupcem datuma koji ima jedan redak za svaki dan za svaku godinu važna je za definiranje svih datuma u rasponu datuma. Potreban je i za stvaranje odnosa između tablice činjenica i tablice datuma. No taj stupac s jednim datumom s jednim retkom za svaki dan nije koristan prilikom analize po datumima u izvješću zaokretne tablice ili dodatka Power View. Želite da tablica datuma sadrži stupce koji pojednostavnjuju prikupljanje podataka za raspon ili grupu datuma. Možete, primjerice, zbrojiti iznose prodaje po mjesecu ili tromjesečju ili pak stvoriti mjeru koja izračunava rast iz godine u godinu. U svakom od tih slučajeva tablica datuma treba stupce godine, mjeseca ili tromjesečja koji omogućuju prikupljanje podataka za to razdoblje.
Ako ste uvezli tablicu datuma iz relacijski izvor podataka, ona možda već sadrži različite vrste stupaca datuma koje želite. U nekim ćete slučajevima htjeti izmijeniti neke od tih stupaca ili stvoriti dodatne stupce datuma. To osobito vrijedi ako stvorite vlastitu tablicu datuma u programu Excel i kopirate je u podatkovni model. Srećom, stvaranje novih stupaca datuma u dodatku Power Pivot prilično je jednostavno uz funkcije datuma i vremena u dax-u.
Savjet: Ako još niste radili s DAX-om, sjajno je mjesto za početak učenja uz brzi početak rada: naučite OSNOVE DAX-a za 30 minuta na Office.com.
Funkcije dax datuma i vremena
Ako ste ikada radili s funkcijama datuma i vremena u formulama programa Excel, vjerojatno ćete biti upoznati s funkcijama datuma i vremena. Iako su te funkcije slične njihovim verzijama u programu Excel, postoje neke važne razlike:
-
DaX funkcije datuma i vremena koriste vrstu podataka datetime.
-
Vrijednosti iz stupca mogu preuzeti kao argument.
-
Mogu se koristiti za vraćanje vrijednosti datuma i/ili rukovanje njima.
Te se funkcije često koriste prilikom stvaranja prilagođenih stupaca datuma u tablici datuma, pa ih je važno razumjeti. Pomoću niza tih funkcija stvorit ćemo stupce za godinu, tromjesečje, fiskalni mjesec i tako dalje.
Napomena: Funkcije datuma i vremena u DAX-u nisu iste kao funkcije inteligencije vremena. Saznajte više o inteligenciji vremena u dodatku Power Pivot u programu Excel.
DAX obuhvaća sljedeće funkcije datuma i vremena:
U formulama možete koristiti i mnoge druge DAX funkcije. Mnoge formule opisane ovdje , primjerice, koriste matematičke i trigonometrijske funkcije kao što su MOD i TRUNC, logičke funkcije kao što su IF i Tekstne funkcije kao što su FORMAT Dodatne informacije o drugim DAX funkcijama potražite u odjeljku Dodatni resursi u nastavku ovog članka.
Primjeri formula za kalendarsku godinu
U sljedećim se primjerima opisuju formule koje se koriste za stvaranje dodatnih stupaca u tablici datuma s nazivom Kalendar. Jedan stupac s nazivom Datum već postoji i sadrži neprekinuti raspon datuma od 1. 1. 2010. do 31. 12. 2016.
Godina
=YEAR([datum])
U ovoj formuli funkcija YEAR vraća godinu iz vrijednosti u stupcu Datum. Budući da je vrijednost u stupcu Datum vrste podataka datetime, funkcija YEAR zna kako vratiti godinu iz te vrste.
Mjesec
=MONTH([datum])
U ovoj formuli, slično kao u funkciji YEAR, jednostavno pomoću funkcije MONTH možemo vratiti vrijednost mjeseca iz stupca Datum.
Tromjesečje
=INT(([Mjesec]+2)/3)
U ovoj formuli koristimo funkciju INT da bismo vratili vrijednost datuma kao cijeli broj. Argument koji navedemo za funkciju INT vrijednost je iz stupca Mjesec, dodajte 2, a zatim ga podijelite s 3 da biste dobili naše tromjesečje, od 1 do 4.
Naziv mjeseca
=FORMAT([datum];"mmmm")
U ovoj formuli, da bismo dobili naziv mjeseca, pomoću funkcije FORMAT brojčanu vrijednost pretvaramo iz stupca Datum u tekst. Stupac Datum navodimo kao prvi argument, a zatim oblik; želimo da naziv mjeseca prikazuje sve znakove, pa koristimo "mmmm". Rezultat izgleda ovako:
Ako želimo vratiti skraćeni naziv mjeseca na tri slova, u argumentu oblika koristili bismo "mmm".
Dan u tjednu
=FORMAT([datum];"ddd")
U ovoj formuli koristimo funkciju FORMAT da bismo dobili naziv dana. Budući da samo želimo skraćeni naziv dana, u argumentu oblika naveli smo "ddd".
Primjer zaokretne tablice
Kada imate polja za datume kao što su Godina, Tromjesečje, Mjesec itd., možete ih koristiti u zaokretnoj tablici ili izvješću. Na sljedećoj je slici, primjerice, prikazano polje IznosProdaje iz tablice Podaci o prodaji u vrijednostiMA i Godina i Tromjesečje iz tablice dimenzija Kalendar u recima. IznosProdaje agregiran je za kontekst godine i tromjesečja.
Primjeri formula za fiskalnu godinu
Fiskalna godina
=IF([Mjesec]<= 6;[Godina];[Godina]+1)
U ovom primjeru fiskalna godina počinje 1. srpnja.
Ne postoji funkcija koja može izdvojiti fiskalnu godinu iz vrijednosti datuma jer se datumi početka i završetka fiskalne godine često razlikuju od datuma kalendarske godine. Da bismo dobili fiskalnu godinu, najprije koristimo funkciju IF da bismo provjerili je li vrijednost za Mjesec manja od ili jednaka 6. U drugom argumentu, ako je vrijednost za Mjesec manja od ili jednaka 6, vrati vrijednost iz stupca Godina. Ako nije, vratite vrijednost iz godine i dodajte 1.
Drugi način na koji možete odrediti vrijednost fiskalne godine u završnom mjesecu jest stvaranje mjere koja samo određuje mjesec. Primjerice, BJ:=6. Nakon toga možete referencirati naziv mjere na mjestu broja mjeseca. Na primjer, =IF([Mjesec]<=[BJJ],[Godina],[Godina]+1). Time se pruža veća fleksibilnost prilikom referencivanja na završni mjesec fiskalne godine u nekoliko različitih formula.
Fiskalni mjesec
=IF([Mjesec]<= 6, 6+[Mjesec], [Mjesec]- 6)
U ovoj formuli određujemo je li vrijednost za [Mjesec] manja od ili jednaka 6, a zatim ćemo uzeti 6 i dodati vrijednost iz mjeseca, u suprotnom oduzeti 6 od vrijednosti od [Mjesec].
Fiskalno tromjesečje
=INT(([FiscalMonth]+2)/3)
Formula koju koristimo za FiscalQuarter jednaka je onoj za tromjesečje u kalendarskoj godini. Jedina je razlika to što navedemo [FiscalMonth] umjesto [Month].
Praznici ili posebni datumi
Možda želite uvrstiti stupac datuma koji označava da su određeni datumi praznici ili neki drugi posebni datum. Možda ćete, primjerice, htjeti zbrojiti ukupne iznose prodaje za novi dan dodavanjem polja Praznik u zaokretnu tablicu, kao rezač ili filtar. U drugim slučajevima te datume možda želite izuzeti iz drugih stupaca datuma ili iz mjere.
Uključivanje praznika ili posebnih dana prilično je jednostavno. U programu Excel možete stvoriti tablicu koja sadrži datume koje želite uvrstiti. Zatim možete kopirati ili koristiti mogućnost Dodaj u podatkovni model da biste ga dodali u podatkovni model kao povezanu tablicu. U većini slučajeva nije potrebno stvoriti odnos između tablice i tablice Kalendar. Sve formule koje se pozivaju na njega mogu koristiti funkciju LOOKUPVALUE za vraćanje vrijednosti.
U nastavku je primjer tablice stvorene u programu Excel koja obuhvaća praznike koji će se dodati u tablicu datuma:
|
Datum |
Praznik |
|---|---|
|
1/1/2010 |
Nove godine |
|
11/25/2010 |
Dan zahvalnosti |
|
12/25/2010 |
Božić |
|
1. 1. 2011. |
Nove godine |
|
11/24/2011 |
Dan zahvalnosti |
|
12/25/2011 |
Božić |
|
1.1.2012. |
Nove godine |
|
22.11.12. |
Dan zahvalnosti |
|
12/25/2012 |
Božić |
|
1/1/2013 |
Nove godine |
|
11/28/2013 |
Dan zahvalnosti |
|
12/25/2013 |
Božić |
|
11/27/2014 |
Dan zahvalnosti |
|
12/25/2014 |
Božić |
|
1. 1. 2014. |
Nove godine |
|
11/27/2014 |
Dan zahvalnosti |
|
12/25/2014 |
Božić |
|
1/1/2015 |
Nove godine |
|
11/26/2014 |
Dan zahvalnosti |
|
12/25/2015 |
Božić |
|
01.01.16. |
Nove godine |
|
11/24/2016 |
Dan zahvalnosti |
|
12/25/2016 |
Božić |
U tablici datuma stvaramo stupac s nazivom Praznik i koristimo formulu kao što je ova:
=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])
Pogledajmo ovu formulu pozornije.
Pomoću funkcije LOOKUPVALUE dohvaćamo vrijednosti iz stupca Praznik u tablici Praznici. U prvom argumentu navodimo stupac u kojem će se nalaziti vrijednost rezultata. U tablici Praznici navodimo stupac Praznik jer je to vrijednost koju želimo vratiti.
=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])
Zatim navodimo drugi argument, stupac za pretraživanje koji sadrži datume koje želimo potražiti. U tablici Praznici navodimo stupac Datum , ovako:
=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])
Naposljetku, u tablici Kalendar navodimo stupac koji sadrži datume koje želimo potražiti u tablici Praznik . To je, naravno, stupac Datum u tablici Kalendar.
=LOOKUPVALUE(Praznici[Praznik],Praznici[datum],Kalendar[datum])
Stupac Praznik vratit će naziv praznika za svaki redak koji ima vrijednost datuma koja odgovara datumu u tablici Praznici.
Prilagođeni kalendar – trinaest razdoblja od četiri tjedna
Neke tvrtke ili ustanove, kao što su maloprodaja ili prehrambena usluga, često izvješćuju o različitim razdobljima, kao što su 13 četrnaest tjedana. U kalendaru s 13-tjednim razdobljem svako razdoblje je 28 dana; stoga svako razdoblje sadrži četiri ponedjeljka, četiri utorka, četiri srijede i tako dalje. Svako razdoblje sadrži isti broj dana, a praznici obično ulaze u isto razdoblje svake godine. Možete odabrati početak razdoblja svakog dana u tjednu. Baš kao i s datumima u kalendaru ili fiskalnoj godini, možete koristiti DAX da biste stvorili dodatne stupce s prilagođenim datumima.
U primjerima u nastavku prvo puno razdoblje započinje prvu nedjelju fiskalne godine. U ovom slučaju fiskalna godina počinje 1. 7.
Tjedan
Ta nam vrijednost daje broj tjedna počevši od prvog punog tjedna fiskalne godine. U ovom primjeru prvi puni tjedan počinje u nedjelju, pa prvi puni tjedan u prvoj fiskalnoj godini u tablici Kalendar zapravo počinje 4. 7. 2010. i nastavlja se cijeli tjedan u tablici Kalendar. Iako sama ta vrijednost nije sve što je korisno u analizi, potrebno je izračunati za korištenje u drugim formulama razdoblja od 28 dana.
=INT([datum]-40356)/7)
Pogledajmo ovu formulu pozornije.
Najprije stvaramo formulu koja vraća vrijednosti iz stupca Datum kao cijeli broj, ovako:
=INT([datum])
Tada želimo potražiti prvu nedjelju u prvoj fiskalnoj godini. Vidimo da je 4. 7. 2010.
Sada oduzmite 40356 (što je cijeli broj za 27. 6. 2010., zadnju nedjelju od prethodne fiskalne godine) od te vrijednosti da biste dobili broj dana od početka dana u tablici Kalendar, ovako:
=INT([datum]-40356)
Zatim podijelite rezultat sa 7 (dana u tjednu), ovako:
=INT(([datum]-40356)/7)
Rezultat izgleda ovako:
Točka
Razdoblje u ovom prilagođenom kalendaru sadrži 28 dana i uvijek će početi u nedjelju. Taj će stupac vratiti broj razdoblja počevši od prve nedjelje u prvoj fiskalnoj godini.
=INT(([Tjedan]+3)/4)
Pogledajmo ovu formulu pozornije.
Najprije stvaramo formulu koja vraća vrijednost iz stupca Tjedan kao cijeli broj, ovako:
=INT([Tjedan])
Zatim dodajte 3 na tu vrijednost, ovako:
=INT([Tjedan]+3)
Zatim podijelite rezultat s 4, ovako:
=INT(([Tjedan]+3)/4)
Rezultat izgleda ovako:
Razdoblje fiskalne godine
Ta vrijednost vraća fiskalnu godinu za razdoblje.
=INT(([Period]+12)/13)+2008
Pogledajmo ovu formulu pozornije.
Najprije stvaramo formulu koja vraća vrijednost iz razdoblja i dodaje 12:
= ([Razdoblje]+12)
Rezultat dijelimo s 13 jer fiskalna godina sadrži trinaest 28 dana:
=(([Razdoblje]+12)/13)
Dodali smo 2010. jer je to prva godina u tablici:
=(([Period]+12)/13)+2010
Na kraju koristimo funkciju INT da bismo uklonili bilo koji dio rezultata i vratili cijeli broj, kada smo podijeljeni s 13, ovako:
=INT(([Period]+12)/13)+2010
Rezultat izgleda ovako:
Razdoblje u fiskalnoj godini
Ta vrijednost vraća broj razdoblja od 1 do 13, počevši od prvog punog razdoblja (počevši od nedjelje) u svakoj fiskalnoj godini.
=IF(MOD([Razdoblje];13), MOD([Razdoblje];13);13)
Formula je malo složenija, pa ćemo je najprije opisati na jeziku koji bolje razumijemo. Ova formula stanja, podijelite vrijednost iz [Točka] s 13 da biste dobili broj točke (1-13) u godini. Ako je taj broj 0, vrati 13.
Najprije stvaramo formulu koja vraća ostatak vrijednosti iz razdoblja do 13. Mod ( matematičke i trigonometrijske funkcije) možemo koristiti ovako:
=MOD([Razdoblje];13)
To nam, u većini slučajeva, daje željeni rezultat, osim ako je vrijednost argumenta Razdoblje 0 jer ti datumi ne ulaze u prvu fiskalnu godinu, kao u prvih pet dana ogledne tablice datuma kalendara. To možemo učiniti pomoću funkcije IF. U slučaju da je naš rezultat 0, vratit ćemo 13, ovako:
=IF(MOD([Razdoblje];13);MOD([Razdoblje];13);13)
Rezultat izgleda ovako:
Primjer zaokretne tablice
Na slici u nastavku prikazana je zaokretna tablica s poljem IznosProdaje iz tablice Podaci o prodaji u poljima VALUES i PeriodFiscalYear i PeriodInFiscalYear iz tablice Dimenzija datuma kalendara u recima. IznosProdaje agregiran je za kontekst po fiskalnoj godini i 28-dnevnom razdoblju fiskalne godine.
Odnosi
Kada stvorite tablicu datuma u podatkovnom modelu, da biste počeli pregledavati podatke u zaokretnim tablicama i izvješćima te zbrojiti podatke na temelju stupaca u tablici dimenzija datuma, morate stvoriti odnos između tablice činjenica s podacima o transakcijama i tablice datuma.
Budući da morate stvoriti odnos na temelju datuma, provjerite jeste li stvorili taj odnos između stupaca čija je vrijednost vrste podataka datum i vrijeme (datum).
Za svaku vrijednost datuma u tablici činjenice povezani stupac s vrijednostima u tablici datuma mora sadržavati podudarne vrijednosti. Na primjer, redak (zapis o transakciji) u tablici Činjenica o prodaji s vrijednošću 15.8.2012. 12.00 u stupcu Ključ Datuma mora imati odgovarajuću vrijednost u stupcu Povezani datum u tablici datuma (pod nazivom Kalendar). To je jedan od najvažnijih razloga zbog kojih želite da stupac datuma u tablici datuma sadrži neprekinuti raspon datuma koji obuhvaća moguće datume u tablici činjenica.
Napomena: Iako stupac datuma u svakoj tablici mora biti iste vrste podataka (Datum), oblik svakog stupca nije bitan..
Napomena: Ako vam Power Pivot ne omogućuje stvaranje odnosa između dviju tablica, polja datuma možda neće pohraniti datum i vrijeme na istu razinu preciznosti. Ovisno o oblikovanju stupca, vrijednosti mogu izgledati isto, ali se pohranjuju drugačije. Saznajte više o radu s vremenom.
Napomena: Izbjegavajte korištenje zamjenskih ključeva cijelog broja u odnosima. Kada uvozite podatke iz relacijski izvor podataka, često su stupci datuma i vremena predstavljeni zamjenskim ključem, što je cijeli stupac koji se koristi za predstavljanje jedinstvenog datuma. U dodatku Power Pivot trebali biste izbjegavati stvaranje odnosa pomoću cijelih ključeva datuma/vremena, a umjesto toga koristite stupce koji sadrže jedinstvene vrijednosti s vrstom podataka datuma. Iako se korištenje zamjenskih ključeva smatra najboljim praksama u tradicionalnim podatkovnim skladištima, cijeli ključevi nisu potrebni u dodatku Power Pivot i mogu otežati grupiranje vrijednosti u zaokretnim tablicama prema različitim razdobljima datuma.
Ako vam se prilikom pokušaja stvaranja odnosa prikazuje pogreška nepodudaranje vrste, vjerojatno stupac u tablici činjenica nije vrste podataka Datum. To se može dogoditi kada Power Pivot ne može automatski pretvoriti datum (obično tekstnu vrstu podataka) u vrstu podataka datuma. Stupac u tablici činjenica i dalje možete koristiti, ali ćete podatke morati pretvoriti pomoću DAX formule u novom izračunatom stupcu. Pročitajte članak Pretvorba datuma vrste podataka u tekst u vrstu podataka datuma u nastavku dodatka.
Više odnosa
U nekim slučajevima možda će biti potrebno stvoriti više odnosa ili stvoriti više tablica datuma. Ako, primjerice, u tablici Podaci o prodaji postoji više polja datuma, kao što su KljučDatuma, DatumIsporuke i DatumProdaje, svi mogu imati odnose s poljem Datum u tablici Datum kalendara, ali samo jedan od njih može biti aktivan odnos. U tom slučaju, budući da DateKey predstavlja datum transakcije, a time i najvažniji datum, to bi najbolje funkcioniralo kao aktivni odnos. Ostali imaju neaktivne odnose.
Sljedeća zaokretna tablica izračunava ukupnu prodaju po fiskalnoj godini i fiskalnom tromjesečju. Mjera pod nazivom Ukupna prodaja, s formulom Ukupna prodaja :=SUM([IznosProdaje]), smješta se u polja VRIJEDNOSTI, a polja FiskalnaGodina i Fiskalno Tromjesečje iz tablice Datum kalendara smještaju se u ROWS.
Ta izravna zaokretna tablica funkcionira ispravno jer ukupnu prodaju želimo zbrojiti prema datumu transakcije uključu datuma. Naša mjera Ukupna prodaja koristi datume u argumentu KljučDatoteke i zbraja se po fiskalnoj godini i fiskalnom tromjesečju jer u tablici Prodaja postoji odnos između ključa datuma i stupca Datum u tablici Datum kalendara.
Neaktivni odnosi
No, što ako želimo zbrojiti ukupnu prodaju ne prema datumu transakcije, već po datumu isporuke? Potreban nam je odnos između stupca DatumProdaje u tablici Prodaja i stupca Datum u tablici Kalendar. Ako ne stvorimo taj odnos, naše se agregacije uvijek temelje na datumu transakcije. No možemo imati više odnosa, iako samo jedan može biti aktivan, a budući da je datum transakcije najvažniji, on dobiva aktivan odnos s tablicom Kalendar.
U ovom slučaju DatumIsporudžbe ima neaktivan odnos, pa svaka formula mjere stvorena radi agregacije podataka na temelju datuma isporuke mora navesti neaktivan odnos pomoću funkcije USERELATIONSHIP .
Na primjer, budući da postoji neaktivan odnos između stupca DatumProdaje u tablici Prodaja i stupca Datum u tablici Kalendar, možemo stvoriti mjeru koja zbrajanje ukupne prodaje po datumu otpreme. Koristimo formulu kao što je ova da bismo odredili odnos koji će se koristiti:
Ukupna prodaja po datumu otpreme:=CALCULATE(SUM(Prodaja[IznosProdaje]), USERELATIONSHIP(Prodaja[DatumOtpreme], Kalendar[Datum]))
Ova formula jednostavno kaže: Izračunajte zbroj iznosaProdaje, ali filtrirajte pomoću odnosa između stupca DatumProdaje u tablici Prodaja i stupca Datum u tablici Kalendar.
Sada, ako stvorimo zaokretnu tablicu i mjeru Ukupna prodaja po datumu otpreme smjestimo u VRIJEDNOSTI, a fiskalnu godinu i fiskalno tromjesečje u retke, vidjet ćemo isti ukupni zbroj, ali svi ostali iznosi zbroja za fiskalnu godinu i fiskalno tromjesečje razlikuju se jer se temelje na datumu otpreme, a ne na datumu transakcije.
Korištenje neaktivnih odnosa omogućuje korištenje samo jedne tablice datuma, ali zahtijeva da se sve mjere (kao što je Ukupna prodaja po datumu otpreme) pozivaju na neaktivan odnos u formuli. Postoji još jedna alternativa, a to je korištenje više tablica datuma.
Više tablica datuma
Drugi je način rada s više stupaca datuma u tablici činjenica stvaranje više tablica datuma i stvaranje zasebnih aktivnih odnosa između njih. Pogledajmo ponovno primjer tablice Prodaja. Imamo tri stupca s datumima na koje bismo mogli agregacija podataka:
-
DateKey s datumom prodaje za svaku transakciju.
-
A ShipDate – s datumom i vremenom kada su prodani artikli otpremljeni klijentu.
-
ReturnDate – s datumom i vremenom kada je primljena jedna ili više vraćenih stavki.
Zapamtite da je polje DateKey s datumom transakcije najvažnije. Većinu ćemo agregacija napraviti na temelju tih datuma, pa ćemo zasigurno htjeti odnos između njega i stupca Datum u tablici Kalendar. Ako ne želimo stvoriti neaktivne odnose između datuma otpreme i polja ReturnDate i polja Datum u tablici Kalendar, zbog kojih su potrebne posebne formule mjera, možemo stvoriti dodatne tablice datuma za datum otpreme i datum povrata. Nakon toga možemo stvoriti aktivne odnose između njih.
U ovom smo primjeru stvorili još jednu tablicu datuma pod nazivom ShipCalendar. To, naravno, znači i stvaranje dodatnih stupaca datuma, a budući da se ti stupci datuma nalaze u drugoj tablici datuma, želimo im dodijeliti naziv na način koji ih razlikuje od istih stupaca u tablici Kalendar. Na primjer, stvorili smo stupce pod nazivom ShipYear, ShipMonth, ShipQuarter i tako dalje.
Ako stvorimo zaokretnu tablicu i mjeru Ukupna prodaja stavimo u vrijednosti VALUES i ShipFiscalYear i ShipFiscalQuarter u rows, vidjet ćemo iste rezultate koje smo vidjeli kada smo stvorili neaktivan odnos i posebno izračunato polje Ukupna prodaja po datumu otpreme.
Svaki od tih pristupa zahtijeva pažljivo razmatranje. Kada koristite više odnosa s jednom tablicom datuma, možda ćete morati stvoriti posebne mjere koje prenose neaktivne odnose pomoću funkcije USERELATIONSHIP. S druge strane, stvaranje više tablica datuma može biti zbunjujuće na popisu polja, a budući da u podatkovnom modelu imate više tablica, potrebno je više memorije. Eksperimentirajte s ono što vam najbolje odgovara.
Svojstvo Date Table
Svojstvo Date Table postavlja metapodatke potrebne Time-Intelligence funkcije kao što su TOTALYTD, PREVIOUSMONTH i DATESBETWEEN radi ispravnog rada. Kada se izračun izvodi pomoću neke od tih funkcija, modul za formule dodatka Power Pivot zna kamo treba ići da bi dohvatiti potrebne datume.
Upozorenje: Ako ovo svojstvo nije postavljeno, mjere koje koriste DAX Time-Intelligence možda neće vratiti točne rezultate.
Kada postavite svojstvo Date Table, navedite tablicu datuma i stupac datuma vrste podataka Datum (datum i vrijeme).
Upute: Postavljanje svojstva Date Table
-
U prozoru dodatka PowerPivot odaberite tablicu Kalendar.
-
Na kartici Dizajn kliknite Označi kao tablicu datuma.
-
U dijaloškom okviru Označi kao tablicu datuma odaberite stupac s jedinstvenim vrijednostima i vrstom podataka Datum.
Rad s vremenom
Sve vrijednosti datuma s vrstom podataka Datum u programu Excel ili SQL Server zapravo su broj. Navedeni su u tom broju znamenke koje se odnose na vrijeme. U mnogim slučajevima, to vrijeme za svaki redak je ponoć. Ako, primjerice, polje DateTimeKey u tablici činjenice Prodaja sadrži vrijednosti kao što su 19.10.2010. 12.00.00, to znači da su vrijednosti na razini preciznosti dana. Ako vrijednosti polja DateTimeKey sadrže vrijeme, primjerice 19.10.2010. 8.44.00, to znači da su vrijednosti na razini preciznosti minuta. Vrijednosti mogu biti i do preciznosti razine sata, pa čak i u sekundama. Razina preciznosti u vremenskoj vrijednosti znatno će utjecati na način stvaranja tablice datuma i odnosa između te vrijednosti i tablice činjenica.
Morate odrediti hoćete li zbrojeti podatke na razinu preciznosti dana ili na vremensku razinu preciznosti. Drugim riječima, kao polja datuma zaokretne tablice u područjima redaka, stupca ili filtra zaokretne tablice možete koristiti stupce kao što su Polje prijepodneva, Poslijepodne ili Sat.
Napomena: Dani su najmanja jedinica vremena u kojoj funkcije INTELIGENCIJE VREMENA ZA DAX mogu raditi. Ako ne morate raditi s vrijednostima vremena, smanjite preciznost podataka da biste koristili dane kao minimalnu jedinicu.
Ako podatke namjeravate zbrojili na vremensku razinu, u tablici datuma bit će vam potreban stupac datuma s uključenim vremenom. Zapravo, bit će mu potreban stupac datuma s jednim retkom za svaki sat, ili možda čak i svake minute, svakog dana, za svaku godinu u rasponu datuma. To je zato što, da biste stvorili odnos između stupca DateTimeKey u tablici činjenica i stupca datuma u tablici datuma, morate imati podudarne vrijednosti. Kao što možete zamisliti, ako uvrstite mnogo godina, to može biti vrlo velika tablica datuma.
No u većini slučajeva podatke želite zbroji samo na dan. Drugim riječima, kao polja u područjima Redak, Stupac ili Filtar zaokretne tablice koristit ćete stupce kao što su Godina, Mjesec, Tjedan ili Dan u tjednu. U tom slučaju stupac datuma u tablici datuma mora sadržavati samo jedan redak za svaki dan u godini, kao što smo prethodno opisali.
Ako stupac datuma sadrži razinu preciznosti vremena, no zbrojiti ćete samo na razinu dana da biste stvorili odnos između tablice činjenica i tablice datuma, možda ćete morati izmijeniti tablicu činjenica stvaranjem novog stupca koji skraćuje vrijednosti u stupcu datuma na vrijednost dana. Drugim riječima, pretvorite vrijednost kao što je 19.10.2010. 8.44.00 u 19.10.2010. 12.00:00. Zatim možete stvoriti odnos između ovog novog stupca i stupca datuma u tablici datuma jer se vrijednosti podudaraju.
Pogledajmo primjer. Na ovoj se slici prikazuje stupac DateTimeKey u tablici Činjenice o prodaji. Sve agregacije za podatke u ovoj tablici moraju biti samo na razini dana pomoću stupaca u tablici datuma kalendara, kao što su Godina, Mjesec, Tromjesečje itd. Vrijeme uključeno u vrijednost nije relevantno, već samo stvarni datum.
Budući da ne moramo analizirati te podatke na vremensku razinu, ne treba nam stupac Datum u tablici datum kalendara da bismo uvrstili jedan redak za svaki sat i svaku minutu svakog dana u godini. Stupac Datum u tablici datuma izgleda ovako:
Da biste stvorili odnos između stupca DateTimeKey u tablici Prodaja i stupca Datum u tablici Kalendar, u tablici Podaci o prodaji možemo stvoriti novi izračunati stupac i pomoću funkcije TRUNC stisnuti vrijednost datuma i vremena u stupcu DateTimeKey u vrijednost datuma koja se podudara s vrijednostima u stupcu Datum u tablici Kalendar. Formula izgleda ovako:
=TRUNC([DateTimeKey],0)
To nam daje novi stupac (s nazivom DateKey) s datumom iz stupca DateTimeKey i vremenom od 12:00:00 am za svaki redak:
Sada možemo stvoriti odnos između novog stupca (Ključ Datuma) i stupca Datum u tablici Kalendar.
Slično tome, u tablici Prodaja možemo stvoriti izračunati stupac koji smanjuje preciznost vremena u stupcu DateTimeKey na razinu preciznosti sata. U tom slučaju funkcija TRUNC neće funkcionirati, ali i dalje možemo koristiti druge funkcije DAX date and Time za izdvajanje i ponovno povezivanje nove vrijednosti na razinu preciznosti sata. Možemo koristiti formulu kao što je ova:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Naš novi stupac izgleda ovako:
Pod uvjetom da naš stupac Datum u tablici datuma sadrži vrijednosti do razine preciznosti sata, tada možemo stvoriti odnos između njih.
Stvaranje datuma upotrebljivijim
Mnogi stupci datuma koje stvorite u tablici datuma nužni su za druga polja, ali zapravo nisu svi koji su korisni u analizi. Na primjer, polje DateKey u tablici Prodaja na koju smo se pozivali i koje se prikazuje u ovom članku važno je jer se za svaku transakciju ta transakcija bilježi kao da se odvija na određeni datum i vrijeme. No iz analitičkog i izvještajnog prikaza nije sve to korisno jer ga ne možemo koristiti kao polje retka, stupca ili filtra u zaokretnoj tablici ili izvješću.
Slično tome, u našem je primjeru stupac Datum u tablici Kalendar vrlo koristan i ključan, ali ga ne možete koristiti kao dimenziju u zaokretnoj tablici.
Da bi tablice i stupci u njima bili što korisniji te da biste olakšali kretanje po popisima polja zaokretne tablice ili dodatka Power View, važno je sakriti nepotrebne stupce od klijentskih alata. Možda ćete htjeti i sakriti određene tablice. Prethodno prikazana tablica Praznici sadrži datume praznika koji su važni za određene stupce u tablici Kalendar, ali stupce Datum i Praznik u tablici Praznici ne možete koristiti kao polja u zaokretnoj tablici. Da biste se lakše kretali popisima polja, možete sakriti cijelu tablicu Praznici.
Još jedan važan aspekt rada s datumima jest konvencija imenovanja. Tablicama i stupcima u dodatku Power Pivot možete dodijeliti nazive koliko god želite. Imajte na umu, osobito ako radnu knjigu zajednički koristite s drugim korisnicima, dobra konvencija imenovanja olakšava prepoznavanje tablica i datuma, ne samo u popisima polja, već i u dodatku Power Pivot i u DAX formulama.
Kada u podatkovnom modelu imate tablicu datuma, možete početi stvarati mjere koje će vam pomoći da iskoristite sve svoje podatke. Neki mogu biti jednostavni kao zbrajanje ukupnih zbrojeva prodaje za trenutnu godinu, a drugi mogu biti složeniji, gdje morate filtrirati određeni raspon jedinstvenih datuma. Saznajte više u odjeljku Mjere u dodatku Power Pivot ifunkcijama inteligencije vremena.
Dodatak
Pretvaranje datuma vrste podataka teksta u vrstu podataka datuma
U nekim slučajevima tablica činjenica s podacima o transakcijama može sadržavati datume tekstne vrste podataka. To jest, datum koji se prikazuje kao 2012-12-04T11:47:09 zapravo nije datum ili barem vrsta datuma koju Power Pivot može razumjeti. To je zapravo samo tekst koji čita kao datum. Da biste stvorili odnos između stupca datuma u tablici činjenica i stupca datuma u tablici datuma, oba stupca moraju biti vrste podataka Datum.
Kada pokušate promijeniti vrstu podataka za stupac datuma koji su tekstna vrsta podataka u vrstu podataka datuma, Power Pivot može automatski protumačiti datume i pretvoriti ih u vrstu podataka za pravi datum. Ako Power Pivot ne može napraviti pretvorbu vrste podataka, prikazat će se pogreška nepodudaranje vrsta.
No i dalje možete pretvoriti datume u vrstu podataka pravih datuma. Možete stvoriti novi izračunati stupac i pomoću DAX formule raščlaniti godinu, mjesec, dan, vrijeme itd. iz tekstnih nizova, a zatim ga ponovno povezati na način koji Power Pivot može pročitati kao pravi datum.
U ovom smo primjeru u power Pivot uvezli tablicu s činjenicama pod nazivom Prodaja. Sadrži stupac s nazivom DateTime. Vrijednosti se pojavljuju ovako:
Ako u grupi Oblikovanje u grupi Oblikovanje pogledamo karticu Polazno dodatka Power Pivot, vidjet ćemo da je to tekstna vrsta podataka.
Ne možemo stvoriti odnos između stupca DateTime i stupca Datum u tablici datuma jer se vrste podataka ne podudaraju. Ako vrstu podataka pokušamo promijeniti u Datum, prikazuje se pogreška nepodudaranje vrsta:
U tom slučaju Power Pivot nije uspio pretvoriti vrstu podataka iz teksta u datum. I dalje možemo koristiti taj stupac, ali da bismo ga dobili u vrstu podataka za pravi datum, moramo stvoriti novi stupac koji raščlanjuje tekst i ponovno ga stvara u vrijednost koju Power Pivot može pretvoriti u vrstu podataka Datum.
Zapamtite, iz odjeljka Rad s vremenom ranije u ovom članku; osim ako je potrebno da analiza bude na razini preciznosti u danu, trebali biste pretvoriti datume u tablicu činjenica u razinu preciznosti dana. Imajte na umu da želimo da vrijednosti u našem novom stupcu bude na razini preciznosti dana (osim vremena). Vrijednosti u stupcu DateTime možemo pretvoriti u vrstu podataka datuma i s pomoću sljedeće formule ukloniti razinu preciznosti vremena:
=DATE(LEFT([DateTime];4), MID([DateTime];6;2), MID([DateTime];9;2))
To nam daje novi stupac (u ovom slučaju pod nazivom Datum). Power Pivot čak otkriva vrijednosti koje će biti datumi i automatski postavlja vrstu podataka na Datum.
Ako želimo zadržati vremensku razinu preciznosti, jednostavno ćemo proširiti formulu tako da obuhvaća sate, minute i sekunde.
=DATE(LEFT([DateTime],4), MID([DateTime];6;2), MID([DateTime];9;2)) +
TIME(MID([DateTime];12;2), MID([DateTime];15;2), MID([Datum Vrijeme];18;2))
Sada kada imamo stupac Datum vrste podataka Datum, možemo stvoriti odnos između te vrste i stupca datuma u datumu.
Dodatni resursi
Zbrajanja u dodatku Power Pivot
Brzi početak rada: naučite osnove DAX-a za 30 minuta
