Tabele sa datumima u programskom dodatku Power Pivot su od suštinske važnosti za pregledanje i izračunavanje podataka tokom vremena. Ovaj članak pruža detaljno razumevanje tabela sa datumima i načina na koji možete da ih kreirate u programskom dodatku Power Pivot. Ovaj članak posebno opisuje:
-
Zašto je tabela sa datumima važna za pregledanje i izračunavanje podataka po datumima i vremenu.
-
Kako da koristite Power Pivot za dodavanje tabele sa datumima u model podataka.
-
Kako da kreirate nove kolone sa datumima kao što su Godina, Mesec i Period u tabeli datuma.
-
Kako da kreirate relacije između tabela sa datumima i tabela sa činjenicama.
-
Rad sa vremenom.
Ovaj članak je namenjen korisnicima koji su novi u programskom dodatku Power Pivot. Međutim, važno je da već dobro razumete uvoz podataka, kreiranje relacija i kreiranje izračunatih kolona i mera.
Ovaj članak ne opisuje kako da koristite DAX Time-Intelligence u formulama mere. Dodatne informacije o tome kako da napravite mere pomoću funkcija DAX vremenske inteligencije potražite u članku Vremenska inteligencija u programskom dodatku Power Pivot u programu Excel.
Napomena: U programskom dodatku Power Pivot, imena "mera" i "izračunato polje" su sinonimi. Koristimo meru imena u celom ovom članku. Više informacija potražite u članku Mere u programskom dodatku Power Pivot.
Sadržaj
Razumevanje tabela sa datumima
Skoro sve analize podataka obuhvata pregledanje i upoređivanje podataka sa datumima i vremenom. Na primer, možda ćete želeti da saberete iznos prodaje za prethodni fiskalni kvartal, a zatim da uporedite te ukupne vrednosti sa drugim kvartalima ili možda želite da izračunate završni saldo na kraju meseca za konto. U svakom od ovih slučajeva koristite datume kao način za grupisanje i agregiranje prodajnih transakcija ili salda za određeni period u vremenu.
Power View izveštaj
Tabela sa datumima može da sadrži mnogo različitih prikazi datuma i vremena. Na primer, tabela sa datumima često ima kolone kao što su "Fiskalna godina", "Mesec", "Kvartal" ili "Period" koje možete da izaberete kao polja sa liste polja prilikom sečenje i filtriranja podataka u izvedenim tabelama ili Power View izveštajima.
Power View lista polja
Da bi kolone sa datumima kao što su "Godina", "Mesec" i "Kvartal" sadrže sve datume u odgovarajućem opsegu, tabela sa datumima mora da ima najmanje jednu kolonu sa susednim skupom datuma. To jest, ta kolona mora da ima jedan red za svaki dan za svaku godinu uključenu u tabelu sa datumima.
Na primer, ako podaci koje želite da pregledate imaju datume od 1. februara 2010. do 30. novembra 2012. i prijavite kalendarsku godinu, onda ćete želeti tabelu sa datumima sa najmanje opsegom datuma od 1. januara 2010. do 31. decembra 2012. Svaka godina u tabeli sa datumima mora da sadrži sve dane za svaku godinu. Ako redovno osvežavate podatke novijim podacima, možda ćete želeti da datum završetka bude isteklo za godinu ili dve, tako da ne morate da ažurirate tabelu sa datumima kako vreme prolazi.
Tabela sa datumima sa susednim skupom datuma
Ako izveštaj o fiskalnoj godini, možete da napravite tabelu sa datumima sa susednim skupom datuma za svaku fiskalnu godinu. Na primer, ako fiskalna godina počinje 1. marta, a imate podatke za fiskalne godine od 2010. do trenutnog datuma (na primer, u verziji FY 2013), možete da napravite tabelu sa datumima koja počinje 1.3.2009. i uključuje najmanje svaki dan u svakoj fiskalnoj godini do poslednjeg datuma u fiskalnoj 2013. godini.
Ako ćete izveštavati o kalendarsku i fiskalnoj godini, ne morate da pravite zasebne tabele sa datumima. Jedna tabela sa datumima može da sadrži kolone za kalendarsku godinu, fiskalnu godinu i čak 13 kalendarskih perioda od četiri sedmice. Važna stvar je da tabela sa datumima sadrži susedni skup datuma za sve uključene godine.
Dodavanje tabele sa datumima u model podataka
Postoji nekoliko načina na koje možete da dodate tabelu sa datumima u model podataka:
-
Uvoz iz relacione baze podataka ili drugog izvora podataka.
-
Create tabelu sa datumima u programu Excel, a zatim kopirajte ili se povežite sa novom tabelom u programskom dodatku Power Pivot.
-
Uvoz sa lokacije Microsoft Azure Marketplace.
Hajde da pogledamo sve ovo paћije.
Uvoz iz relacione baze podataka
Ako uvezete neke ili sve podatke iz skladišta podataka ili drugog tipa relacione baze podataka, verovatno već postoji tabela sa datumima i relacije između toga i ostalih podataka koje uvozite. Datumi i format verovatno se podudaraju sa datumima u podacima činjenica, a datumi verovatno dobro počinju u prošlosti i idu daleko u budućnost. Tabela sa datumima koju želite da uvezete može biti veoma velika i sadrži opseg datuma izvan onoga što ćete morati da uključite u model podataka. Možete da koristite napredne funkcije filtera Čarobnjaka za uvoz tabela programskog dodatka Power Pivot da biste selektivno odabrali samo datume i određene kolone koje su vam zaista potrebne. To može znatno da smanji veličinu radne sveske i poboljša performanse.
Čarobnjak za uvoz tabele
U većini slučajeva, ne morate da pravite dodatne kolone kao što su "Fiskalna godina", "Sedmica", "Ime meseca" itd. zato što će već postojati u uvezenoj tabeli. Međutim, u nekim slučajevima, kada u model podataka uvezete tabelu sa datumima, možda ćete morati da kreirate dodatne kolone sa datumima, u zavisnosti od određene potrebe izveštavanja. Srećom, ovo možete lako da uradite pomoću DAX-a. Kasnije ćete saznati više o kreiranju polja tabele sa datumima. Svako okruženje je drugačije. Ako niste sigurni da li izvori podataka imaju povezani datum ili tabelu kalendara, obratite se administratoru baze podataka.
Create tabele sa datumima u programu Excel
Možete da kreirate tabelu sa datumima u programu Excel, a zatim da je kopirate u novu tabelu u modelu podataka. To je veoma lako uraditi i daje vam dosta fleksibilnosti.
Kada kreirate tabelu sa datumima u programu Excel, počinjete sa jednom kolonom sa susednim opsegom datuma. Zatim možete da napravite dodatne kolone kao što su Godina, Kvartal, Mesec, Fiskalna godina, Period itd. u Excel radnom listu pomoću Excel formula ili, kada kopirate tabelu u model podataka, možete da ih napravite kao izračunate kolone. Kreiranje dodatnih kolona datuma u programskom dodatku Power Pivot opisano je u odeljku Dodavanje novih kolona datuma u tabelu datuma u nastavku ovog članka.
Kako da: Create tabelu sa datumima u programu Excel i kopirate je u model podataka
-
U programu Excel, na praznom radnom listu, u ćeliji A1 otkucajte ime zaglavlja kolone da biste identifikovali opseg datuma. To će obično biti neštopoput datuma, datuma i vremena ili tastera DateKey.
-
U ćeliji A2 otkucajte datum početka. Na primer, 1.1.2010.
-
Kliknite na pokazivač za popunjavanje i prevucite ga nadole u broj reda koji sadrži datum završetka. Na primer, 31.12.2016.
-
Izaberite sve redove u koloni "Datum " (uključujući ime zaglavlja u ćeliji A1).
-
U grupi Stilovi izaberite stavku Oblikuj kao tabelu, a zatim izaberite stil.
-
U dijalogu Oblikuj kao tabelu kliknite na dugme U redu.
-
Kopirajte sve redove, uključujući zaglavlje.
-
U programskom dodatku Power Pivot, na kartici Početak izaberite stavku Nalepi.
-
U prikazu lepljenja > Ime tabele otkucajte ime kao što je "Datum" ili " Kalendar". Ostavite potvrđen izbor u polju za potvrdu Koristi prvi red kao zaglavlja kolona, a zatim kliknite na dugme U redu.
Nova tabela sa datumima (pod imenom "Kalendar" u ovom primeru) u programskom dodatku Power Pivot izgleda ovako:
Napomena: Povezanu tabelu možete da napravite i pomoću stavke Dodaj u model podataka. Međutim, to čini radnu svesku nepotrebno velikom zato što radna sveska ima dve verzije tabele sa datumima; u programu Excel i jedan u programskom dodatku Power Pivot.
Napomena: Datum imena je ključna reč u programskom dodatku Power Pivot. Ako tabelu koju kreirate u programskom dodatku Power Pivot date, ime tabele morate da stavite pod jednostruke navodnike u bilo kojoj DAX formuli koja upućuje na njega u argumentu. Sve primere slika i formula u ovom članku odnose se na tabelu sa datumima kreiranu u programskom dodatku Power Pivot pod imenom Kalendar.
Sada imate tabelu sa datumima u modelu podataka. Možete da dodate nove kolone datuma kao što su Godina, Mesec itd. pomoću daX-a.
Dodavanje novih kolona datuma u tabelu sa datumima
Tabela sa datumima sa jednom kolonom sa datumima koja ima jedan red za svaki dan za svaku godinu važna je za definisanje svih datuma u opsegu datuma. Takođe je neophodno za kreiranje relacije između tabele sa činjenicama i tabele sa datumima. Međutim, ta kolona sa pojedinačnim datumima sa po jednim redom za svaki dan nije korisna kada analizirate datume u izvedenoj tabeli ili Power View izveštaju. Želite da tabela sa datumima sadrži kolone koje vam pomažu da agregirate podatke za opseg ili grupu datuma. Na primer, možda ćete želeti da saberete iznos prodaje po mesecu ili kvartalu ili možete da napravite meru koja izračunava godišnje rast. U svakom od ovih slučajeva, za tabelu sa datumima su potrebne kolone godine, meseca ili kvartala koje vam omogućavaju da agregirate podatke za taj period.
Ako ste uvezli tabelu sa datumima iz relacionog izvora podataka, ona možda već uključuje različite tipove kolona datuma koje želite. U nekim slučajevima, možda ćete želeti da izmenite neke od tih kolona ili da napravite dodatne kolone sa datumima. To posebno važi ako kreirate sopstvenu tabelu sa datumima u programu Excel i kopirate je u model podataka. Srećom, kreiranje novih kolona datuma u programskom dodatku Power Pivot prilično je lako uz funkcije datuma i vremena u programskom dodatku DAX.
Savet: Ako još uvek niste radili sa DAX-om, sjajno mesto za početak učenja je uz QuickStart: Learn DAX Basics in 30 Minutes on Office.com.
DAX funkcije za datum i vreme
Ako ste ikada radili sa funkcijama datuma i vremena u Excel formulama, verovatno ćete biti upoznati sa funkcijama datuma i vremena. Iako su ove funkcije slične svojim kolegama u programu Excel, postoje neke važne razlike:
-
DAX funkcije za datum i vreme koriste tip podataka "datum/vreme".
-
Oni mogu da preuzmu vrednosti iz kolone kao argument.
-
Mogu da se koriste za vraćanje i/ili manipulisanje vrednostima datuma.
Ove funkcije se često koriste prilikom kreiranja prilagođenih kolona datuma u tabeli datuma, tako da su važne za razumevanje. Koristićemo nekoliko ovih funkcija da bismo kreirali kolone za "Godina", "Kvartal", "FiskalniMont" i tako dalje.
Napomena: Funkcije datuma i vremena u daX-u nisu iste kao funkcije vremenske inteligencije. Saznajte više o vremenskoj inteligenciji u programskom dodatku Power Pivot u programu Excel 2013.
DAX uključuje sledeće funkcije datuma i vremena:
U formulama možete da koristite i mnoge druge DAX funkcije. Na primer, mnoge formule opisane ovde koriste matematičke i trigonometrijčke funkcije kao što su MOD i TRUNC , logičke funkcije kao što su IF i tekstualne funkcije kao što je FORMAT Više informacija o drugim DAX funkcijama potražite u odeljku Dodatni resursi u nastavku ovog članka.
Primeri formule za kalendarsku godinu
Sledeći primeri opisuju formule korišćene za kreiranje dodatnih kolona u tabeli sa datumima pod imenom "Kalendar". Jedna kolona pod imenom "Datum" već postoji i sadrži susedni opseg datuma od 01.01.2010. do 31.12.2016.
Godina
=YEAR([datum])
U ovoj formuli funkcija YEAR daje godinu iz vrednosti u koloni "Datum". Pošto je vrednost u koloni "Datum" tipa podataka "datum/vreme", funkcija YEAR zna kako da vrati godinu iz te godine.
Mesec
=MONTH([datum])
U ovoj formuli, na primer sa funkcijom YEAR, jednostavno možemo da koristimo funkciju MONTH da bismo vratili vrednost meseca iz kolone "Datum".
Kvartal
=INT(([Mesec]+2)/3)
U ovoj formuli koristimo funkciju INT da bismo kao ceo broj vratili vrednost datuma. Argument koji navedemo za funkciju INT je vrednost iz kolone Mesec, dodaj 2 i podeli to sa 3 da biste dobili naš kvartal, 1 do 4.
Ime meseca
=FORMAT([datum],"mmmm")
U ovoj formuli, da bismo dobili ime meseca, koristimo funkciju FORMAT za konvertovanje numeričke vrednosti iz kolone "Datum" u tekst. Navodimo kolonu "Datum" kao prvi argument, a zatim oblik; želimo da ime meseca prikaže sve znakove, tako da koristimo "mmmm". Rezultat izgleda ovako:
Ako želimo da vratimo ime meseca skraćeno na tri slova, koristili bismo "mmm" u argumentu formata.
Dan sedmice
=FORMAT([datum],"ddd")
U ovoj formuli koristimo funkciju FORMAT da bismo dobili ime dana. Pošto želimo skraćeno ime dana, u argumentu formata navodimo "ddd".
Uzorak izvedene tabele
Kada imate polja za datume kao što su Godina, Kvartal, Mesec itd., možete da ih koristite u izvedenoj tabeli ili izveštaju. Na primer, sledeća slika prikazuje polje "IznosProdaje" iz tabele sa činjenicama "Prodaja" u oblasti "VREDNOSTI", a "Godina" i "Kvartal" iz tabele dimenzije "Kalendar" u oblasti "REDOVI". IznosProdaje se agregira za kontekst godine i kvartala.
Primeri formule za fiskalnu godinu
Fiskalna godina
=IF([Mesec]<= 6,[Godina],[Godina]+1)
U ovom primeru fiskalna godina počinje 1. jula.
Ne postoji funkcija koja može da izdvoji fiskalnu godinu iz vrednosti datuma zato što se datumi početka i završetka fiskalne godine često razlikuju od onih za kalendarsku godinu. Da bismo dobili fiskalnu godinu, prvo koristimo funkciju IF za testiranje da li je vrednost za Mesec manja od ili jednaka 6. U drugom argumentu, ako je vrednost za mesec manja od ili jednaka 6, onda daje vrednost iz kolone Godina. Ako nije, onda daje vrednost iz godine i dodaje 1.
Drugi način da navedete vrednost fiskalnog meseca završnog meseca jeste da napravite meru koja jednostavno navodi mesec. Na primer, FYE:=6. Zatim možete da ukažete na ime mere umesto broja meseca. Na primer, =IF([Mesec]<=[FYE],[Godina],[Godina]+1). To pruža veću fleksibilnost prilikom upućivanja na završetak fiskalne godine u nekoliko različitih formula.
Fiskalni mesec
=IF([Mesec]<= 6, 6+[Mesec], [Mesec]- 6)
U ovoj formuli navodimo da li je vrednost za [Mesec] manja ili jednaka 6, onda uzmite 6 i dodajte vrednost iz meseca, u suprotnom oduzmite 6 od vrednosti od [Mesec].
Fiskalni kvartal
=INT(([Fiskalni Meseci]+2)/3)
Formula koju koristimo za fiskalni kvartal je mnogo ista kao za kvartal u kalendarsku godinu. Jedina razlika je u tome što navodimo [FiskalniMont] umesto [Mesec].
Praznici ili specijalni datumi
Možda ćete želeti da uključite kolonu sa datumima koja ukazuje na to da su određeni datumi praznici ili neki drugi specijalni datum. Na primer, možda ćete želeti da saberete ukupne vrednosti prodaje za Dan nove godine tako što ćete dodati polje "Praznik" u izvedenu tabelu, kao modul za sečenje ili filter. U drugim slučajevima ćete možda želeti da izuzmete te datume iz drugih kolona datuma ili iz mere.
Uključujući praznike ili posebne dane je prilično jednostavno. U programu Excel možete da napravite tabelu koja sadrži datume koje želite da uključite. Zatim možete da kopirate ili koristite opciju Dodaj u model podataka da biste ga dodali u model podataka kao povezanu tabelu. U većini slučajeva nije neophodno da kreirate relaciju između tabele i tabele "Kalendar". Sve formule koje upućuju na to mogu da koriste funkciju LOOKUPVALUE da bi dale vrednosti.
Ispod je prikazan primer tabele kreirane u programu Excel koja uključuje praznike koji će biti dodati u tabelu sa datumima:
|
Datum |
Praznik |
|---|---|
|
1/1/2010 |
Nove godine |
|
11/25/2010 |
Praznik |
|
12/25/2010 |
Boћiж |
|
01.01.11. |
Nove godine |
|
11/24/2011 |
Praznik |
|
12/25/2011 |
Boћiж |
|
01.01.2012. |
Nove godine |
|
22.11.2012. |
Praznik |
|
12/25/2012 |
Boћiж |
|
1/1/2013 |
Nove godine |
|
11/28/2013 |
Praznik |
|
12/25/2013 |
Boћiж |
|
11/27/2014 |
Praznik |
|
12/25/2014 |
Boћiж |
|
1.1.2014. |
Nove godine |
|
11/27/2014 |
Praznik |
|
12/25/2014 |
Boћiж |
|
1/1/2015 |
Nove godine |
|
11/26/2014 |
Praznik |
|
12/25/2015 |
Boћiж |
|
01.01.2016. |
Nove godine |
|
11/24/2016 |
Praznik |
|
12/25/2016 |
Boћiж |
U tabeli sa datumima kreiramo kolonu pod imenom "Praznik " i koristimo formulu poput ove:
=LOOKUPVALUE(Praznici[Praznici],Praznici[datum],Kalendar[datum])
Hajde da pažljivije pregledamo ovu formulu.
Funkciju LOOKUPVALUE koristimo za preuzimanje vrednosti iz kolone "Praznik" u tabeli "Praznici". U prvom argumentu navodimo kolonu u kojoj će biti vrednost rezultata. Kolonu " Praznik" navodimo u tabeli "Praznici " zato što je to vrednost koju želimo da prikažemo.
=LOOKUPVALUE(Praznici[Praznici],Praznici[datum],Kalendar[datum])
Zatim navodimo drugi argument, kolonu za pretragu koja sadrži datume koje želimo da pretražimo. U tabeli " Praznici " navodimo kolonu "Datum ", na sledeći primer:
=LOOKUPVALUE(Praznici[Praznici],Praznici[datum],Kalendar[datum])
Na kraju, navodimo kolonu u tabeli "Kalendar " koja sadrži datume koje želimo da pretražimo u tabeli "Praznik ". Ovo je naravno kolona "Datum " u tabeli "Kalendar ".
=LOOKUPVALUE(Praznici[Praznici],Praznici[datum],Kalendar[datum])
Kolona Praznik daje ime praznika za svaki red koji ima vrednost datuma koja se podudara sa datumom u tabeli "Praznici".
Prilagođeni kalendar – trinaest perioda od četiri sedmice
Neke organizacije, kao što su malo preduzeća ili prehrana, često izveštavaju o različitim periodima, kao što je trinaest četvoronedeljnih perioda. Sa trinaest kalendarskih perioda od četiri sedmice, svaki period je 28 dana; prema tome, svaki period sadrži četiri ponedeljka, četiri utorka, četiri srede i tako dalje. Svaki period sadrži isti broj dana i obično će praznici svake godine biti u istom periodu. Možete odabrati da započnete period bilo kog dana u sedmici. Kao i sa datumima u kalendaru ili fiskalnoj godini, možete da koristite DAX da biste napravili dodatne kolone sa prilagođenim datumima.
U dolenavedenom primeru, prvi puni period počinje prve nedelje fiskalne godine. U ovom slučaju, fiskalna godina počinje 1.7.
Sedmica
Ova vrednost nam daje broj sedmice koji počinje sa prvom punom sedmicom u fiskalnoj godini. U ovom primeru prva puna sedmica počinje u nedelju, tako da prva puna sedmica u prvoj fiskalnoj godini u tabeli "Kalendar" zapravo počinje 4.7.2010. i nastavlja se do poslednje cele sedmice u tabeli "Kalendar". Iako sama ova vrednost nije sve što je korisno u analizi, neophodno je izračunati za upotrebu u drugim formulama perioda od 28 dana.
=INT([datum]-40356)/7)
Hajde da pažljivije pregledamo ovu formulu.
Prvo kreiramo formulu koja kao ceo broj vraća vrednosti iz kolone "Datum":
=INT([datum])
Zatim želimo da potražimo prvu nedelju u prvoj fiskalnoj godini. Vidimo da je 04.07.2010.
Sada od te vrednosti oduzmite 40356 (što je ceo broj za 27.6.2010., poslednju nedelju od prethodne fiskalne godine) da biste dobili broj dana od početka dana u tabeli "Kalendar", ovako:
=INT([datum]-40356)
Zatim podelite rezultat sa 7 (dana u sedmici), ovako:
=INT(([datum]-40356)/7)
Rezultat izgleda ovako:
Obračunski period
Period u ovom prilagođenom kalendaru sadrži 28 dana i uvek će početi nedeljom. Ova kolona daje broj perioda koji počinje sa prvom nedeljom u prvoj fiskalnoj godini.
=INT(([Sedmica]+3)/4)
Hajde da pažljivije pregledamo ovu formulu.
Prvo kreiramo formulu koja kao ceo broj vraća vrednost iz kolone Sedmica:
=INT([Sedmica])
Zatim toj vrednosti dodajte 3, na sledeći primer:
=INT([Sedmica]+3)
Zatim podelite rezultat sa 4, ovako:
=INT(([Sedmica]+3)/4)
Rezultat izgleda ovako:
Period fiskalne godine
Ova vrednost daje fiskalnu godinu za određeni period.
=INT(([Period]+12)/13)+2008
Hajde da pažljivije pregledamo ovu formulu.
Prvo kreiramo formulu koja vraća vrednost iz "Tačka" i dodaje 12:
= ([Period]+12)
Rezultat delimo sa 13 zato što u fiskalnoj godini ima trinaest perioda od 28 dana:
=(([Period]+12)/13)
Dodajemo 2010 zato što je to prva godina u tabeli:
=(([Period]+12)/13)+2010
Na kraju koristimo funkciju INT za uklanjanje bilo kog dela rezultata i vraćanje celog broja, kada se podeli sa 13, na sledeći način:
=INT(([Period]+12)/13)+2010
Rezultat izgleda ovako:
Period u fiskalnoj godini
Ova vrednost daje broj perioda, od 1 do 13, počevši od prvog punog perioda (koji počinje u nedelju) u svakoj fiskalnoj godini.
=IF(MOD([Period],13), MOD([Period],13),13)
Ova formula je malo složenija, pa ćemo je prvo opisati na jeziku koji bolje razumemo. Ova formula navodi, podelite vrednost sa [Tačka] sa 13 da biste dobili broj perioda (1-13) u godini. Ako je taj broj 0, daje 13.
Prvo kreiramo formulu koja daje ostatak vrednosti iz perioda do 13. Možemo da koristimo MOD (matematičke i trigonometrijčke funkcije) na sledeći način:
=MOD([Period],13)
To nam uglavnom daje rezultat koji želimo, osim kada je vrednost za period 0 zato što ti datumi ne spadaju u prvu fiskalnu godinu, kao u prvih pet dana primera tabele sa datumima kalendara. Ovo možemo da rešimo pomoću funkcije IF. U slučaju da je rezultat 0, vraćamo 13, ovako:
=IF(MOD([Period],13),MOD([Period],13),13)
Rezultat izgleda ovako:
Uzorak izvedene tabele
Dolenavedena slika prikazuje izvedenu tabelu sa poljem "IznosProdaje" iz tabele sa činjenicama "Prodaja" u poljima "VREDNOSTI", "PeriodFiscalYear" i "PeriodInFiscalYear" iz tabele sa dimenzijama datuma u kalendaru u koloni REDOVI. IznosProdaje se agregira za kontekst fiskalne godine i period od 28 dana u fiskalnoj godini.
Relacije
Kada napravite tabelu sa datumima u modelu podataka, da biste počeli da pregledate podatke u izvedenim tabelama i izveštajima i da biste agregirali podatke na osnovu kolona u tabeli dimenzije datuma, morate da kreirate relaciju između tabele sa činjenicama sa podacima o transakcijama i tabelom sa datumima.
Pošto treba da kreirate relaciju na osnovu datuma, trebalo bi da proverite da li ste napravili tu relaciju između kolona čije su vrednosti tipa podataka "datum/vreme" ("Datum").
Za svaku vrednost datuma u tabeli činjenica, srodna kolona za pronalaženje u tabeli datuma mora da sadrži vrednosti koje se podudaraju. Na primer, red (zapis transakcije) u tabeli sa činjenicama o prodaji sa vrednošću od 15.08.2012. u koloni DateKey mora da ima odgovarajuću vrednost u povezanoj koloni "Datum" u tabeli "Datum" (pod imenom "Kalendar"). Ovo je jedan od najvažnijih razloga zbog kojih želite da kolona sa datumima u tabeli sa datumima sadrži susedni opseg datuma koji uključuje bilo koji mogući datum u tabeli činjenica.
Napomena: Iako kolona sa datumima u svakoj tabeli mora biti istog tipa podataka (Datum), format svake kolone nije bitan..
Napomena: Ako vam Power Pivot ne omogućava da kreirate relacije između dve tabele, polja za datum možda neće uskladištiti datum i vreme na istom nivou preciznosti. U zavisnosti od oblikovanja kolona, vrednosti mogu da izgledaju isto, ali se skladište drugačije. Pročitajte više o radu sa vremenom.
Napomena: Izbegavajte korišćenje surogat ključeva celog broja u relacijama. Kada uvozite podatke iz relacionog izvora podataka, kolone datuma i vremena često su predstavljene surogat ključem, što je celobrojna kolona koja se koristi za predstavljanje jedinstvenog datuma. U programskom dodatku Power Pivot trebalo bi da izbegnete kreiranje relacija pomoću tastera za datum/vreme celog broja i umesto toga koristite kolone koje sadrže jedinstvene vrednosti sa tipom podataka datuma. Iako se korišćenje surogat ključeva smatra najboljom praksom u tradicionalnim skladištima podataka, ključevi celog broja nisu potrebni u programskom dodatku Power Pivot i mogu da otežaju grupisanje vrednosti u izvedenim tabelama po različitim datumskim periodima.
Ako dobijete grešku nepodudaranja tipa prilikom pokušaja kreiranja relacije, to je verovatno zbog toga što kolona u tabeli činjenica ne sadrži tip podataka "Datum". To se može desiti kada Power Pivot ne može automatski da konvertuje ne-datum (obično je to tekstualni tip podataka) u tip podataka datuma. I dalje možete da koristite kolonu u tabeli činjenica, ali ćete morati da konvertujete podatke pomoću DAX formule u novoj izračunatoj koloni. Pročitajte članak Konvertovanje tekstualnih tipova podataka u tip podataka datuma kasnije u dodatku.
Više relacija
U nekim slučajevima, možda će biti potrebno da kreirate više relacija ili kreirate više tabela sa datumima. Na primer, ako u tabeli sa podacima o prodaji postoji više polja sa datumom, kao što su "DateKey", "DatumIsporuke" i "DatumIsporuke", sva polja sa datumom mogu da imaju relacije sa poljem "Datum" u tabeli "Datum" u kalendaru, ali samo jedno od njih može da bude aktivna relacija. U ovom slučaju, pošto DateKey predstavlja datum transakcije, a samim tim i najvažniji datum, to bi najbolje služilo kao aktivna relacija . Druge osobe imaju neaktivne odnose.
Sledeća izvedena tabela izračunava ukupnu prodaju po fiskalnoj godini i fiskalnom kvartalu. Mera pod imenom Ukupna prodaja, sa formulom Ukupna prodaja :=SUM([IznosProdaje]), nalazi se u poljima VREDNOSTI, a polja "Fiskalna godina" i "Fiskalni kvart" iz tabele "Datum kalendara" postavljaju se u redove.
Ova izvedena tabela direktno radi ispravno zato što želimo da saberemo ukupnu prodaju po datumu transakcijeu funkciji DateKey. Mera Ukupna prodaja koristi datume u koloni DateKey i sabrana je fiskalnom godinom i fiskalnim kvartalima zato što postoji relacija između tastera DateKey u tabeli "Prodaja" i kolone "Datum" u tabeli "Datum kalendara".
Neaktivne relacije
Ali, šta ako bismo želeli da saberemo ukupnu prodaju ne po datumu transakcije, već po datumu isporuke? Potrebna nam je relacija između kolone "DatumIsporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar". Ako ne kreiramo tu relaciju, naše agregatne funkcije se uvek zasnivaju na datumu transakcije. Međutim, možemo da imamo više relacija, čak i ako samo jedan može da bude aktivan i pošto je datum transakcije najvažniji, on preuzima aktivnu relaciju sa tabelom "Kalendar".
U ovom slučaju, "DatumIsporuke" ima neaktivan odnos, tako da svaka formula mere kreirana za prikupljanje podataka na osnovu datuma isporuke mora da navede neaktivnu relaciju pomoću funkcije USERELATIONSHIP .
Na primer, pošto postoji neaktivna relacija između kolone "DatumIsporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar", možemo da kreiramo meru koja sabira ukupnu prodaju po datumu isporuke. Koristimo ovakvu formulu da bismo naveli relaciju koja će se koristiti:
Ukupna prodaja po datumu isporuke:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ova formula jednostavno navodi: Izračunaj zbir za IznosProdaje, ali filtriraj koristeći relaciju između kolone "DatumIsporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar".
Sada, ako kreiramo izvedenu tabelu i izmerimo meru Ukupna prodaja po datumu isporuke u vrednosti, fiskalnu godinu i fiskalni kvartal u redove, videćemo isti sveukupni zbir, ali svi ostali iznosi za fiskalnu godinu i fiskalni kvartal se razlikuju zato što su zasnovani na datumu isporuke, a ne datumu transakcije.
Korišćenje neaktivnih relacija omogućava vam da koristite samo jednu tabelu sa datumima, ali zahteva da sve mere (kao što je Ukupna prodaja po datumu isporuke) upućuju na neaktivnu relaciju u okviru formule. Postoji druga alternativa, npr. korišćenje više tabela sa datumima.
Više tabela sa datumima
Drugi način za rad sa više kolona sa datumima u tabeli sa činjenicama jeste da kreirate više tabela sa datumima i kreirate zasebne aktivne relacije između njih. Hajde da ponovo pogledamo primer tabele "Prodaja". Imamo tri kolone sa datumima na kojima bismo želeli da prikupljene podatke:
-
DateKey sa datumom prodaje za svaku transakciju.
-
DatumIsporuke – sa datumom i vremenom kada su prodati artikli isporučeni kupcu.
-
Datum Vraćanja – sa datumom i vremenom kada je primljena jedna ili više vraćenih stavki.
Ne zaboravite, polje DateKey sa datumom transakcije je najvažnije. Većinu agregacija ćemo uraditi na osnovu ovih datuma, tako da ćemo sigurno želeti relaciju između njega i kolone "Datum" u tabeli "Kalendar". Ako ne želimo da kreiramo neaktivne relacije između vrednosti "DatumIsporuke" i "DatumPorudžbine" i polja "Datum" u tabeli "Kalendar", tako da zahtevaju formule specijalne mere, možemo da kreiramo dodatne tabele sa datumom isporuke i datumom vraćanja. Zatim možemo da kreiramo aktivne relacije između njih.
U ovom primeru kreirali smo drugu tabelu sa datumima pod imenom "ShipCalendar". To naravno znači i kreiranje dodatnih kolona sa datumima i pošto su te kolone sa datumima u drugoj tabeli sa datumima, želimo da im damo ime na način koji ih razlikuje od istih kolona u tabeli "Kalendar". Na primer, napravili smo kolone pod imenom "Godine isporuke", "MesecIISporuke", "Količina otpreme" i tako dalje.
Ako kreiramo izvedenu tabelu i unesite meru Ukupna prodaja u vrednosti, a ShipFiscalYear i ShipFiscalQuarter u oblast REDOVI, videćemo iste rezultate koje smo videli kada smo napravili neaktivan odnos i posebno izračunato polje Ukupna prodaja po datumu isporuke.
Svaki od ovih pristupa zahteva pažljivo razmatranje. Kada koristite više relacija sa jednom tabelom sa datumima, možda ćete morati da napravite posebne mere koje prenose neaktivne relacije pomoću funkcije USERELATIONSHIP. S druge strane, kreiranje više tabela sa datumima može da zbuni listu polja, a pošto imate više tabela u modelu podataka, to zahteva više memorije. Eksperimentišite sa onima koji vam najviše odgovaraju.
Svojstvo "Tabela sa datumima"
Svojstvo Tabela datuma postavlja metapodatke neophodne Time-Intelligence funkcije kao što su TOTALYTD, PREVIOUSMONTH i DATESBETWEEN da bi ispravno funkcionisale. Kada se izračunavanje pokrene pomoću neke od ovih funkcija, mašina formule programskog dodatka Power Pivot zna gde treba da ode da bi nabavio potrebne datume.
Upozorenje: Ako ovo svojstvo nije postavljeno, mere koje koriste DAX Time-Intelligence možda neće vratiti ispravne rezultate.
Kada podesite svojstvo Tabela sa datumima, navodite tabelu sa datumima i kolonu sa datumima tipa podataka "Datum(datum/vreme)" u njemu.
Kako da: postavite svojstvo "Tabela sa datumima"
-
U powerPivot prozoru izaberite tabelu Kalendar .
-
Na kartici Dizajn izaberite stavku Označi kao tabelu datuma.
-
U dijalogu Označavanje kao tabele sa datumima izaberite kolonu sa jedinstvenim vrednostima i tipom podataka "Datum".
Rad sa vremenom
Sve vrednosti datuma sa tipom podataka "Datum" u programu Excel SQL Server su zapravo broj. Uključeni u taj broj su cifre koje upućuju na vreme. U mnogim slučajevima, to vreme za svaki red je ponoć. Na primer, ako polje DateTimeKey u tabeli sa činjenicama o prodaji ima vrednosti kao što je 10/19/2010 12:00:00 pre podne, to znači da su vrednosti na nivou preciznosti dana. Ako vrednosti polja DateTimeKey imaju uključeno vreme, na primer 19.10.2010. u 8:44:00, to znači da su vrednosti na minute nivoa preciznosti. Vrednosti mogu biti i do preciznosti na nivou sata ili čak sekundama nivoa preciznosti. Nivo preciznosti u vremenskoj vrednosti imaće značajan uticaj na način na koji kreirate tabelu sa datumima i relacije između te tabele sa činjenicama.
Morate da utvrdite da li ćete agregirali podatke na dnevni nivo preciznosti ili na nivo vremenske preciznosti. Drugim rečima, možda ćete želeti da koristite kolone u tabeli sa datumima kao što su "Jutro", "Popodne" ili "Sat" kao polja datuma vremena u oblastima "Red", "Kolona" ili "Filter" izvedene tabele.
Napomena: Dani su najmanja jedinica vremena sa kojim funkcije DAX vremenske inteligencije mogu da rade. Ako ne morate da radite sa vrednostima vremena, trebalo bi da smanjite preciznost podataka tako da koriste dane kao minimalnu jedinicu.
Ako nameravate da agregirate podatke na nivo vremena, tabeli sa datumima će biti potrebna kolona datuma sa uključenim vremenom. Zapravo, biće potrebna kolona datuma sa po jednim redom za svaki sat, ili možda čak i svaki minut, svakog dana, za svaku godinu u opsegu datuma. To je zbog toga što morate da imate podudarne vrednosti da biste kreirali relaciju između kolone "DateTimeKey" u tabeli sa činjenicama i kolone sa datumima u tabeli sa datumima. Kao što možete da zamislite, ako uključite mnogo godina, to može da napravi veoma veliku tabelu za datume.
Međutim, u većini slučajeva želite da agregirate podatke samo na dan. Drugim rečima, kolone kao što su Godina, Mesec, Sedmica ili Dan sedmice koristite kao polja u oblastima "Red", "Kolona" ili "Filtriranje" izvedene tabele. U ovom slučaju, kolona sa datumima u tabeli datuma treba da sadrži samo jedan red za svaki dan u godini, kao što smo opisali ranije.
Ako kolona sa datumima sadrži nivo preciznosti vremena, ali ćete se prikupljeni samo na nivo dana napraviti relaciju između tabele sa činjenicama i tabele sa datumima, možda ćete morati da izmenite tabelu činjenica tako što ćete napraviti novu kolonu koja skraćuje vrednosti u koloni sa datumima na vrednost dana. Drugim rečima, konvertujte vrednost kao što je 19.10.2010. u 8:44:00 do 19.10.2010. u 12:00:00. Zatim možete da kreirate relaciju između ove nove kolone i kolone sa datumima u tabeli datuma jer se vrednosti podudaraju.
Hajde da pogledamo primer. Ova slika prikazuje kolonu "DateTimeKey" u tabeli sa činjenicama "Prodaja". Sve agregacije za podatke u ovoj tabeli treba da budu samo na nivou dana, korišćenjem kolona u tabeli sa datumima kalendara, kao što su Godina, Mesec, Kvartal itd. Vreme uključeno u vrednost nije važno, samo stvarni datum.
Pošto ne moramo da analiziramo ove podatke na nivou vremena, nije nam potrebna kolona Datum u tabeli sa datumima kalendara da bismo uključili jedan red za svaki sat i svaki minut svakog dana u svakoj godini. Dakle, kolona Datum u tabeli sa datumima izgleda ovako:
Da biste napravili relaciju između kolone "DateTimeKey" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar", možemo da kreiramo novu izračunatu kolonu u tabeli sa činjenicama o prodaji i koristimo funkciju TRUNC da skratimo vrednost datuma i vremena u koloni DateTimeKey u vrednost datuma koja se podudara sa vrednostima u koloni "Datum" u tabeli "Kalendar". Naša formula izgleda ovako:
=TRUNC([DateTimeKey],0)
To nam daje novu kolonu (mi se zovemo DateKey) sa datumom iz kolone DateTimeKey i vreme od 12:00:00 za svaki red:
Sada možemo da kreiramo relaciju između ove nove kolone (DateKey) i kolone "Datum" u tabeli "Kalendar".
Slično tome, možemo da kreiramo izračunatu kolonu u tabeli "Prodaja" koja smanjuje preciznost vremena u koloni DateTimeKey na nivo preciznosti časa. U ovom slučaju, funkcija TRUNC neće raditi, ali i dalje možemo da koristimo druge FUNKCIJE DAX datuma i vremena za izdvajanje i ponovno spajanje nove vrednosti sa časom preciznosti. Formulu kao što je ova možemo da koristimo:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Naša nova kolona izgleda ovako:
Pod uslovom da kolona "Datum" u tabeli sa datumima ima vrednosti na nivou preciznosti časa, onda možemo da kreiramo relaciju između njih.
Omogućavanje korišćenje datuma
Mnoge kolone sa datumima koje kreirate u tabeli sa datumima neophodne su za druga polja, ali zapravo nisu baš toliko korisne u analizi. Na primer, polje DateKey u tabeli "Prodaja" na koje smo uputili i prikazano u celom ovom članku važno je zato što se za svaku transakciju ta transakcija snima u određenom datumu i vremenu. Ali iz prikaza analize i tačke izveštavanja, nije sve to korisno jer ne možemo da ga koristimo kao polje reda, kolone ili filtera u izvedenoj tabeli ili izveštaju.
Slično tome, u našem primeru, kolona Datum u tabeli "Kalendar" je veoma korisna, kritična, ali je ne možete koristiti kao dimenziju u izvedenoj tabeli.
Da bi tabele i kolone u njima što korisnije i da bi se liste polja izvedene tabele ili Power View izveštaja lakše kretale, važno je da sakrijete nepotrebne kolone od klijentskih alatki. Možda ćete želeti da sakrijete i određene tabele. Tabela "Praznici" prikazana ranije sadrži datume praznika koji su važni za određene kolone u tabeli "Kalendar", ali ne možete da koristite kolone "Datum" i "Praznici" u samoj tabeli "Praznici" kao polja u izvedenoj tabeli. Ovde ponovo možete da sakrijete celu tabelu "Praznici" Lists da biste olakšali kretanje kroz polje.
Još jedan važan aspekt rada sa datumima jeste konvencije imenovanja. Tabelama i kolonama možete dati ime u programskom dodatku Power Pivot šta god želite. Međutim, imajte na umu da, posebno ako radnu svesku delite sa drugim korisnicima, dobra konvencija imenovanja olakšava identifikovanje tabela i datuma, ne samo u polju Lists, već i u programskom dodatku Power Pivot i u DAX formulama.
Kada imate tabelu sa datumima u modelu podataka, možete da počnete da pravite mere koje će vam pomoći da na najbolji način iskoristite podatke. Neki mogu biti jednostavni kao sabiranje ukupnih vrednosti prodaje za trenutnu godinu, a druge mogu biti složenije, gde treba da filtrirate po određenom opsegu jedinstvenih datuma. Saznajte više u temama u programskom dodatku Power Pivoti funkcijama vremenske inteligencije.
Dodatak
Konvertovanje tekstualnih tipova podataka u tip podataka datuma
U nekim slučajevima, tabela sa činjenicama sa podacima o transakciji može da sadrži datume tekstualnog tipa podataka. To jest, datum koji se pojavljuje kao 12-04T11:47:09 zapravo nije datum ili barem ne može da razume tip datuma koji Power Pivot može da razume. To je zapravo samo tekst koji se čita kao datum. Da biste kreirali relaciju između kolone sa datumima u tabeli sa činjenicama i kolone sa datumima u tabeli datuma, obe kolone moraju da budu tipa podataka "Datum ".
Obično, kada pokušate da promenite tip podataka za kolonu sa datumima koji su tekstualni tip podataka u tip podataka datuma, Power Pivot može da protumači datume i automatski ih konvertuje u pravi tip podataka datuma. Ako Power Pivot ne može da izvrši konverziju tipa podataka, dobijate grešku nepodudaranja tipa.
Međutim, i dalje možete da konvertujete datume u pravi tip podataka datuma. Možete da napravite novu izračunatu kolonu i koristite DAX formulu za raščlanjivanje godine, meseca, dana, vremena itd. iz tekstualnih niski, a zatim da je ponovo spojite na način koji Power Pivot može da čita kao pravi datum.
U ovom primeru uvezli smo tabelu činjenica pod imenom "Prodaja" u programskom dodatku Power Pivot. Sadrži kolonu pod imenom "Datum i vreme". Vrednosti izgledaju ovako:
Ako pogledamo tip podataka u grupi Oblikovanje na kartici Početak programskog dodatka Power Pivot, videćemo da je to tekstualni tip podataka.
Nije moguće kreirati relaciju između kolone "Datum i datum" u tabeli sa datumima jer se tipovi podataka ne podudaraju. Ako pokušamo da promenimo tip podataka u "Datum", dobijamo grešku nepodudaranja tipova:
U ovom slučaju, Power Pivot nije mogao da konvertuje tip podataka iz teksta u datum. I dalje možemo da koristimo ovu kolonu, ali da bismo je pretvorili u pravi tip podataka datuma, moramo da kreiramo novu kolonu koja raščlanava tekst i ponovo ga kreira u vrednost koju Power Pivot može da napravi kao tip podataka "Datum".
Zapamtite, iz odeljka Rad sa vremenom u prethodnom odeljku ovog članka; Osim ako nije neophodno da vaša analiza bude na dnevnom nivou preciznosti, trebalo bi da konvertujete datume u tabeli činjenica u dnevni nivo preciznosti. Imajući to na umu, želimo da vrednosti u novoj koloni budu na dnevnom nivou preciznosti (ne računajući vreme). Možete da konvertujete vrednosti u koloni "Datum i vreme" u tip podataka datuma i da sa sledećom formulom uklonimo nivo preciznosti vremena:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
To nam daje novu kolonu (u ovom slučaju pod imenom "Datum"). Power Pivot čak otkriva vrednosti koje će biti datumi i automatski postavlja tip podataka na "Datum".
Ako želimo da sačuvamo nivo preciznosti vremena, jednostavno proširimo formulu tako da uključuje časove, 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([DateTime],18,2))
Sada kada imamo kolonu "Datum" tipa podataka "Datum", možemo da kreiramo relaciju između njega i kolone sa datumom u datumu.
Dodatni resursi
Datumi u programskom dodatku Power Pivot
Izračunavanja u programskom dodatku Power Pivot
Brzi početak: Naučite DAX osnove za 30 minuta
