Tabele sa datumima u programnom programu Power Pivot su od suštinskog značaja za pregledanje i izračunavanje podataka tokom vremena. Ovaj članak pruža detaljno razumevanje tabela sa datumima i načina na koji ih možete kreirati u programu Power Pivot. Konkretno, ovaj članak opisuje:
-
Zašto je tabela sa datumima važna za pregledanje i izračunavanje podataka po datumu i vremenu.
-
Kako da koristite Power Pivot za dodavanje tabele sa datumima u model podataka.
-
Kako da napravite nove kolone sa datumima kao što su "Godina", "Mesec" i "Period" u tabeli sa datumima.
-
Kako da kreirate relacije između tabela sa datumima i činjenica.
-
Kako raditi sa vremenom.
Ovaj članak namenjen je korisnicima koji koriste Power Pivot. Međutim, važno je da već imate dobro razumevanje uvoza podataka, kreiranja relacija i kreiranja izračunatih kolona i mera.
Ovaj članak ne opisuje kako da koristite DAX funkcije Time-Intelligence u formulama mere. Dodatne informacije o tome kako da napravite mere uz DAX funkcije vremenske inteligencije potražite u temi Vremenska inteligencija u programom Power Pivot Excel.
Napomena: U programu Power Pivot imena "mera" i "izračunato polje" su sinonimi. Koristimo meru imena u ovom članku. Dodatne informacije potražite u temi Mere u izvedenom obliku Power Pivot.
Sadržaj
Razumevanje tabela sa datumima
Skoro sva analiza podataka uključuje pregledanje i poređenje podataka tokom datuma i vremena. Na primer, možda ćete želeti da sabirate 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 za mesec za nalog. U svakom od ovih slučajeva, koristite datume kao način za grupisnje i agregaciju 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 će često imati kolone kao što su "Fiskalna godina", "Mesec", "Kvartal" ili "Period" koje možete da izaberete kao polja sa liste polja prilikom sečenja 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 uključile sve datume u okviru svog donjeg opsega, tabela sa datumima mora da ima najmanje jednu kolonu sa kontinualnim skupom datuma. To jesti da 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, a vi izveštavate o kalendarskoj godini, 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 ćete redovno osvežavati podatke novijim podacima, možda ćete želeti da krajnji datum završite za godinu ili dve kako ne biste ažurirali tabelu sa datumima kako vreme prolazi.
Tabela sa datumima sa kontinunim skupom datuma
Ako izveštaj o fiskalnoj godini, možete da kreirate tabelu sa datumima sa kontinunim 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 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 godini 2013.
Ako ćete izveštavati o kalendarskoj godini 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 trinaest kalendarskih perioda od četiri sedmice. Važno je da tabela sa datumima sadrži kontinuitan 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:
-
Uvezite ih iz relacije baze podataka ili drugog izvora podataka.
-
Napravite tabelu sa datumima Excel zatim kopirajte ili povežite u novu tabelu u programu Power Pivot.
-
Uvezite iz Microsoft Azure Marketplace.
Hajde da pogledamo sve ovo još bliže.
Uvoz iz relacionalne baze podataka
Ako uvezete neke ili sve podatke iz skladišta podataka ili drugog tipa relacione baze podataka, postoji šansa da već postoji tabela sa datumima i relacija između njih i ostalih podataka koje uvozite. Datumi i format verovatno će se podudarati sa datumima u vašim činjenicama i datumi verovatno dobro počinju u prošlosti i odu dalje u budućnosti. Tabela sa datumima koji želite da uvezete može da bude veoma velika i sadrži opseg datuma izvan onoga što ćete morati da uključite u model podataka. Možete da koristite napredne funkcije čarobnjaka za uvoz tabela u programu 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će biti potrebno da pravite dodatne kolone kao što su "Fiskalna godina", "Sedmica", "Ime meseca" itd. zato što će već postojati u uvezenim tabelama. Međutim, u nekim slučajevima, kada uvezete tabelu sa datumima u model podataka, možda ćete morati da kreirate dodatne kolone sa datumima, u zavisnosti od određene potrebe izveštavanja. Srećom, ovo se lako radi 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 povezan datum ili tabelu kalendara, obratite se administratoru baze podataka.
Kreiranje tabele sa datumima u programu Excel
Možete da kreirate tabelu sa datumima u programu Excel zatim da je kopirate u novu tabelu u modelu podataka. To je veoma lako uraditi i pruža vam dosta fleksibilnosti.
Kada pravite tabelu sa datumima u programu Excel, počinjete sa jednom kolonom sa kontinuisanim opsegom datuma. Zatim možete kreirati dodatne kolone kao što su Godina, Kvartal, Mesec, Fiskalna godina, Period itd. u Excel radnom listu korišćenjem Excel formula, ili kada kopirate tabelu u model podataka, možete da ih kreirate kao izračunate kolone. Kreiranje dodatnih kolona sa datumima u programnom dodatku Power Pivot je opisano u odeljku Dodavanje kolona sa novim datumima u tabelu sa datumima u kasnijem delu ovog članka.
Kako da: napravite tabelu sa datumima Excel i kopirate je u model podataka
-
U Excel, na praznom radnom listu, u ćeliji A1,otkucajte ime zaglavlja kolone da biste identifikovali opseg datuma. Obično će to biti nešto poput "Date", "DateTime" ili "DateKey".
-
U ćeliji A2otkucajte datum početka. Na primer, 01.01.2010.
-
Kliknite na pokazivač za popunjavanje i prevucite ga nadole do broja reda koji uključuje datum završnog datuma. 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 programu Power Pivot, na kartici Početak izaberite stavku Nalepi.
-
U e->pregledu > tabele otkucajte ime kao što je Datum ili Kalendar. Ostavite stavku Koristi prvi red kao zaglavlja kolona,a zatim kliknite na dugme U redu.
Nova tabela sa datumima (u ovom primeru pod imenom "Kalendar") u programskommenu Power Pivot izgleda ovako:
Napomena: Povezanu tabelu možete da kreirate i pomoću stavke Dodaj u model podataka. Međutim, to čini radnu svesku nepotrebno velikom jer radna sveska ima dve verzije tabele sa datumima; jednu u Excel i jednu u power Pivot..
Napomena: Datum imena je ključna reč u programu Power Pivot. Ako ime tabele koju kreirate u power Pivot datumu imenujete, ime tabele ćete morati da ograničite jednim navodnicima u DAX formulama koje upućuju na njega u argumentu. Sve primere slika i formula u ovom članku odnose se na tabelu sa datumima kreiranu u programskommenu Power Pivot pod imenom Kalendar.
Sada imate tabelu sa datumima u modelu podataka. Možete da dodate nove kolone sa datumima kao što su "Godina", "Mesec" itd. pomoću dax oznake.
Dodavanje novih kolona sa datumima 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 da kreirate relaciju između tabela sa činjenicama i tabele sa datumima. Međutim, ta kolona sa datumima sa 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 prikuite podatke za opseg ili grupu datuma. Na primer, možda ćete želeti da sabelite iznos prodaje po mesecu ili kvartalu ili možete da napravite meru koja izračunava godišnje rast. U svakom od tih slučajeva, tabeli sa datumima su potrebne kolone godine, mesec ili kvartal koje omogućavaju prikupljanje podataka za taj period.
Ako ste uvezli tabelu sa datumima iz izvora relacija podataka, ona možda već sadrži različite tipove kolona sa datumima koje želite. U nekim slučajevima možda ćete želeti da izmenite neke od tih kolona ili da napravite dodatne kolone sa datumima. Ovo posebno važi ako napravite sopstvenu tabelu sa datumima Excel je kopirate u model podataka. Srećom, kreiranje novih kolona sa datumima u funkciji Power Pivot prilično je lako uz funkcije datuma i vremena u DAX-u.
Savet: Ako još uvek niste radili u jeziku DAX, sjajno mesto za početak učenja jeste brzi početak: Naučite DAX osnove za 30 minuta na Kancelarija.com.
DAX funkcije za datum i vreme
Ako ste ikada radili sa funkcijama za datum i vreme u Excel formulama, verovatno ćete biti upoznati sa funkcijama datuma i vremena. Iako su ove funkcije slične njihovim sličanim Excel, postoje neke važne razlike:
-
DAX funkcije za datum i vreme koriste tip podataka datuma ili vremena.
-
Oni mogu da uzimaju vrednosti iz kolone kao argument.
-
One mogu da se koriste za vraćanje vrednosti datuma i/ili manipulisanje njima.
Ove funkcije se često koriste prilikom pravljenja prilagođenih kolona sa datumima u tabeli sa datumima, tako da je važno razumeti ih. Koristićemo nekoliko ovih funkcija da bismo kreirali kolone za kolone "Godina", "Kvartal", "FiskalniMeseč" i tako dalje.
Napomena: Funkcije datuma i vremena u DAX-u nisu iste kao funkcije vremenske inteligencije. Saznajte više o vremenskoj inteligenciji u Excel 2013.
DAX uključuje sledeće funkcije "Date and Time":
Postoji i mnogo drugih DAX funkcija koje možete da koristite u formulama. Na primer, mnoge formule opisane ovde koriste matematičke i trigonometrijske funkcije kao što su MOD i TRUNC,Logičke funkcije kao što su IFi Tekstualne funkcije kao što su 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 koje se koriste za kreiranje dodatnih kolona u tabeli sa datumima pod imenom "Kalendar". Jedna kolona, pod imenom "Datum", već postoji i sadrži kontinuisan 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, kao što je funkcija YEAR, možemo jednostavno da koristimo funkciju MONTH da bismo vratili mesečnu vrednost iz kolone "Datum".
Kvartal
=INT(([Mesec]+2)/3)
U ovoj formuli koristimo funkciju INT da bismo vratili vrednost datuma kao broj. Argument koji navedemo za funkciju INT je vrednost iz kolone Mesec, dodajte 2, a zatim ga podelite sa 3 da biste nabavili kvartal, 1 prag 4.
Ime meseca
=FORMAT([datum],"mmmm")
U ovoj formuli, da bismo dobijali ime meseca, koristimo funkciju FORMAT za konvertovanje numeričke vrednosti iz kolone "Datum" u tekst. Kao prvi argument navodimo kolonu Datum, a zatim format; želimo da ime meseca pokaže sve znakove, pa ćemo koristiti "mmmm". Rezultat izgleda ovako:
Ako želimo da vratimo ime meseca skraćeno na tri slova, koristili bismo "mmm" u argumentu format.
Dan sedmice
=FORMAT([datum],"ddd")
U ovoj formuli koristimo funkciju FORMAT da bismo dobijali ime dana. Pošto želimo samo skraćeno ime dana, u argumentu format navedite "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 "Iznos Prodaje" iz tabele sa činjenicama "Prodaja" u oblasti "VREDNOSTI" i "Godina" i "Kvartal" iz tabele dimenzije "Kalendar" u oblasti "REDOVI". SalesAmount 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 dobijali fiskalnu godinu, prvo koristimo funkciju IF da bismo testirali 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 vratite vrednost iz godine i dodajte 1.
Drugi način da navedete vrednost završnog meseca fiskalne godine jeste da napravite meru koja jednostavno navodi mesec. Na primer, FYE:=6. Zatim možete da ukažete na ime mere na mestu broja meseca. Na primer, =IF([Mesec]<=[FYE],[Godina],[Godina]+1). To pruža veću fleksibilnost prilikom upućivanja na mesec završetaka fiskalne godine u nekoliko različitihformula.
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(([FiskalniMont]+2)/3)
Formula koju koristimo za fiskalniquarter je veoma ista kao za kvartal u kalendarskoj godini. Jedina razlika je u tome što navodimo [FiskalniMesečni] 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 poseban datum. Na primer, možda ćete želeti da sabelite ukupne vrednosti prodaje za Novi dan tako što ćete dodati polje "Praznik" u izvedenu tabelu, kao segmentator ili filter. U drugim slučajevima ćete možda želeti da isključite te datume iz drugih kolona datuma ili iz mere.
Uključivanje praznika ili specijalnih dana je prilično jednostavno. U programu Excel koja sadrži datume koje želite da uključite. Zatim možete da kopirate ili koristite stavku Dodaj u model podataka da biste je dodali u model podataka kao povezanu tabelu. U većini slučajeva nije neophodno da kreirate relaciju između tabele i tabele kalendara. Sve formule koje upućuju na tu funkciju mogu da koriste funkciju LOOKUPVALUE kako bi vratile vrednosti.
Ispod je primer tabele kreirane u Excel koja uključuje praznike koji se dodaju u tabelu sa datumima:
|
Datum |
Praznik |
|---|---|
|
1/1/2010 |
Nove godine |
|
11/25/2010 |
Hvala vam |
|
12/25/2010 |
Božić |
|
01.01.11. |
Nove godine |
|
11/24/2011 |
Hvala vam |
|
12/25/2011 |
Božić |
|
01.01.2012. |
Nove godine |
|
22.11.2012. |
Hvala vam |
|
12/25/2012 |
Božić |
|
1/1/2013 |
Nove godine |
|
11/28/2013 |
Hvala vam |
|
12/25/2013 |
Božić |
|
11/27/2014 |
Hvala vam |
|
12/25/2014 |
Božić |
|
1.1.2014. |
Nove godine |
|
11/27/2014 |
Hvala vam |
|
12/25/2014 |
Božić |
|
1/1/2015 |
Nove godine |
|
11/26/2014 |
Hvala vam |
|
12/25/2015 |
Božić |
|
01.01.2016. |
Nove godine |
|
11/24/2016 |
Hvala vam |
|
12/25/2016 |
Božić |
U tabeli sa datumima kreiramo kolonu pod imenom "Praznik" i koristimo formulu kao što je ova:
=LOOKUPVALUE(Praznici[Prazniki],Praznici[datum],Kalendar[datum])
Hajde da pažljivije pogledamo ovu formulu.
Funkciju LOOKUPVALUE koristimo za pronalaženje vrednosti iz kolone "Praznik" u tabeli "Praznici". U prvom argumentu navodimo kolonu u kojoj će biti vrednost rezultata. U tabeli Praznici navodimo kolonu Praznik zato što je to vrednost koju želimo da vratimo.
=LOOKUPVALUE(Praznici[Prazniki],Praznici[datum],Kalendar[datum])
Zatim navedemo drugi argument, kolonu za pretragu koja ima datume koje želimo da pretražimo. Kolonu "Datum" navodimo u tabeli "Praznici", na ovaj tekst:
=LOOKUPVALUE(Praznici[Prazniki],Praznici[datum],Kalendar[datum])
Na kraju smo naveli kolonu u tabeli "Kalendar" koja sadrži datume koje želimo da pretražimo u tabeli "Praznik". To je naravno kolona "Datum" u tabeli "Kalendar".
=LOOKUPVALUE(Praznici[Prazniki],Praznici[datum],Kalendar[datum])
Kolona Praznik daje ime praznika za svaki red sa vrednošću datuma koja se podudara sa datumom u tabeli "Praznici".
Prilagođeni kalendar – trinaest četvoronedeljnih perioda
Neke organizacije, kao što su maločajne ili prehrambene službe, često prijavljujeju o različitim periodima, kao što je trinaest perioda od četiri sedmice. Sa trinaest kalendarskih perioda od četiri sedmice, svaki period je 28 dana; stoga svaki period sadrži četiri ponedeljka, četiri utorak, četiri srede i tako dalje. Svaki period sadrži isti broj dana i praznici će obično biti u istom periodu svake godine. Možete da odaberete da započnete period bilo kog dana u sedmici. Kao i sa datumima u kalendaru ili fiskalnoj godini, DAX možete da koristite za pravljenje dodatnih kolona sa prilagođenim datumima.
U primerima ispod, prvi pun period počinje prvom nedeljom fiskalne godine. U ovom slučaju, fiskalna godina počinje 1.7.
Sedmica
Ova vrednost nam daje broj sedmice koja 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 04.07.2010. i nastavlja kroz poslednju punu sedmicu u tabeli "Kalendar". Iako sama ova vrednost nije sve što je korisno u analizi, neophodno je izračunati za korišćenje u drugim formulama perioda od 28 dana.
=INT([datum]-40356)/7)
Hajde da pažljivije pogledamo ovu formulu.
Prvo kreiramo formulu koja vraća vrednosti iz kolone "Datum" kao broj, na ovaj način:
=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 stvarnog broja za 27.6.2010., poslednju nedelju iz prethodne fiskalne godine) od te vrednosti da biste dobijali broj dana od početka dana u tabeli "Kalendar", na ovaj mestu:
=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 počinje nedeljom. Ova kolona daje broj perioda koji počinje prvom nedeljom u prvoj fiskalnoj godini.
=INT(([Sedmica]+3)/4)
Hajde da pažljivije pogledamo ovu formulu.
Prvo kreiramo formulu koja vraća vrednost iz kolone Sedmica kao broj, na ovaj način:
=INT([Sedmica])
Zatim dodajte 3 u tu vrednost, kao što je ovo:
=INT([Sedmica]+3)
Zatim podelite rezultat sa 4, na ovaj ili ovaj:
=INT(([Sedmica]+3)/4)
Rezultat izgleda ovako:
Fiskalna godina za period
Ova vrednost daje fiskalnu godinu za period.
=INT(([Period]+12)/13)+2008
Hajde da pažljivije pogledamo ovu formulu.
Prvo kreiramo formulu koja vraća vrednost iz "Tačka" i sabvamo 12:
= ([Tačka]+12)
Rezultat se deli sa 13 zato što u fiskalnoj godini ima trinaest perioda od 28 dana:
=(([Tačka]+12)/13)
Dodajemo 2010 zato što je to prva godina u tabeli:
=(([Tačka]+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 ovaj 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 od prvog punog perioda (počev od nedelje) 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 je bolje razumemo. Ova formula navodi da vrednost iz [Period] delite sa 13 da biste u godini dobijeli broj perioda (1-13). Ako je taj broj 0, daje 13.
Prvo kreiramo formulu koja vraća ostatak vrednosti iz perioda do 13. Možemo da koristimo MOD (matematičke i trigonometrijske funkcije) na ovaj ili:
=MOD([Tačka],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 "Datum kalendara". Ovo možemo da radimo na tome uz funkciju 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
Slika u nastavku prikazuje izvedenu tabelu sa poljem "IznosProdaje" iz tabele sa činjenicama "Prodaja" u poljima "VREDNOSTI", "PeriodFiscalYear" i "PeriodInFiscalYear" iz tabele sa dimenzijama datuma kalendara u oblasti "REDOVI". SalesAmount 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, kao i da biste prikupeli podatke na osnovu kolona u tabeli dimenzije datuma, morate da napravite relaciju između tabele sa činjenicama sa podacima transakcija i tabelom sa datumima.
Pošto treba da kreirate relaciju zasnovanu na datumima, trebalo bi da proverite da li ste kreirali tu relaciju između kolona čije su vrednosti tipa podataka "datum/vreme" ("Datum").
Za svaku vrednost datuma u tabeli sa činjenicama, srodna kolona za pronalaženje u tabeli sa datumima mora da sadrži vrednosti koje se podudaraju. Na primer, red (zapis transakcije) u tabeli sa činjenicama "Prodaja" sa vrednošću od 08.15.2012. u 12:00 u koloni DateKey mora da ima odgovarajuću vrednost u povezanoj koloni "Datum" u tabeli "datum" (pod imenom "Kalendar"). To je jedan od najvažnijih razloga zbog kojih želite da kolona sa datumima u tabeli sa datumima sadrži kontinuisan opseg datuma koji uključuje bilo koji mogući datum u tabeli sa činjenicama.
Napomena: Iako kolona sa datumima u svakoj tabeli mora biti istog tipa podataka (Datum), oblikovanje svake kolone nije važno.
Napomena: Ako Vam Power Pivot ne dozvoli da kreirate relacije između dve tabele, polja za datum možda neće uskladištiti datum i vreme na isti nivo preciznosti. U zavisnosti od oblikovanja kolona, vrednosti mogu izgledati isto, ali će biti uskladištene drugačije. Pročitajte više o radu sa vremenom.
Napomena: Izbegavajte korišćenje više brojeva zamene tastera u relacijama. Kada uvozite podatke iz izvora relacija podataka, često su kolone datuma i vremena predstavljene surgate ključem, koji je kolona sa svim brojevima koja se koristi za predstavljanje jedinstvenog datuma. U programom Power Pivot trebalo bi da izbegnete kreiranje relacija pomoću tastera za više brojeva datum/vreme i umesto toga koristite kolone koje sadrže jedinstvene vrednosti sa tipom podataka datuma. Iako se korišćenje surrogatnih ključeva smatra najboljom praksom u tradicionalnim skladištima podataka, nisu potrebni u programskim prečicama Power Pivot i može da oteža grupisanje vrednosti u izvedenim tabelama po različitim datumskim periodima.
Ako dobijete grešku Tip greške o nedudaranju prilikom pokušaja kreiranja relacije, to je verovatno zbog toga što kolona u tabeli činjenice nije tipa podataka "Datum". To se može dogoditi 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 sa činjenicama, ali podatke ćete morati da konvertujete pomoću DAX formule u novoj izračunatoj koloni. Pogledajte konvertovanje tekstualnih datuma u tip podataka datuma kasnije u programu appendix.
Više relacija
U nekim slučajevima, možda je neophodno da kreirate više relacija ili da napravite više tabela sa datumima. Na primer, ako u tabeli sa činjenicama "Prodaja" postoji više polja sa datumima, kao što su "DateKey", "ShipDate" i "ReturnDate", sva polja mogu da imaju relacije sa poljem "Datum" u tabeli "Datum kalendara", 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, ovo bi najbolje služilo kao aktivna relacija. Druge osobe imaju neaktivne relacije.
Sledeća izvedena tabela izračunava ukupnu prodaju po fiskalnoj godini i fiskalnom kvartalu. Mera pod imenom "Ukupna prodaja", sa formulom Ukupna prodaja:=SUM([Iznos Prodaje]), postavljena je u polja "VREDNOSTI", a polja "Fiskalna godina" i "FiskalniQuarter" iz tabele "Datum kalendara" postavljena su u polja "REDOVI".
Ova izvedena tabela neispravno funkcioniše ispravno jer želimo da sakupimo ukupnu prodaju po datumu transakcijeu funkciji DateKey. Mera "Ukupna prodaja" koristi datume u polju DateKey i sabira se fiskalnom godinom i fiskalnim kvartalima jer postoji relacija između kolone "DateKey" u tabeli "Prodaja" i kolone "Datum" u tabeli "Datum kalendara".
Neaktivne relacije
Ali, šta da bismo želeli da sakuimo ukupnu prodaju ne do datuma transakcije, već po datumu isporuke? Potrebna nam je relacija između kolone "DatumIs isporuke" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar". Ako ne napravimo tu relaciju, naše agregatne informacije su uvek zasnovane na datumu transakcije. Međutim, možemo da imamo više relacija, iako samo jedna može da bude aktivna i pošto je datum transakcije najvažniji, on dobija aktivnu relaciju sa tabelom kalendara.
U ovom slučaju, "DatumIsvebe" ima neaktivnu relaciju, 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 "DatumIstupa" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar", možemo da napravimo meru koja sabira ukupnu prodaju po datumu isporuke. Formulu kao što je ova koristimo da bismo naveli relaciju koja će se koristiti:
Ukupna prodaja po datumu isporuke:=CALCULATE(SUM(Prodaja[IznosNaPlate]), USERELATIONSHIP(Prodaja[DatumIsveće], Kalendar[Datum]))
Ova formula jednostavno navodi: Izračunaj zbir za iznosProdaje, ali filtriraj koristeći relaciju između kolone "DatumIsprodaje" u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar".
Ako napravimo izvedenu tabelu i unesite meru Ukupna prodaja po datumu isporuke u polja "VREDNOSTI", "Fiskalna godina" i "Fiskalni kvartal" u redovima, videćemo isti ukupni zbir, ali svi ostali zbirovi za fiskalnu godinu i fiskalni kvartal se razlikuju jer su zasnovani na datumu isporuke, a ne na datumu transakcije.
Korišćenje neaktivnih relacija vam omogućava da koristite samo jednu tabelu sa datumima, ali ona zahteva da sve mere (kao što je Ukupna prodaja po datumu isporuke) upućuju na neaktivnu relaciju u formuli. Postoji druga alternativa da koristite 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 je da kreirate više tabela sa datumima i kreirate zasebne aktivne relacije između njih. Hajde ponovo da pogledamo primer tabele "Prodaja". Imamo tri kolone sa datumima na koje bismo želeli da prikuimo podatke:
-
DateKey sa datumom prodaje za svaku transakciju.
-
Datum isporuke – sa datumom i vremenom kada su prodate stavke isporučene klijentu.
-
Datum Povraćaja – sa datumom i vremenom kada je primljena jedna ili više stavki.
Ne zaboravite, najvažnije je polje DateKey sa datumom transakcije. Većinu agregacija ćemo uraditi na osnovu tih datuma, tako da ćemo svakako želeti relaciju između njega i kolone "Datum" u tabeli "Kalendar". Ako ne želimo da kreiramo neaktivne relacije između tabela "DatumIspošte" i "DatumIspošte" i polja "Datum" u tabeli "Kalendar", zbog toga smo želeli formule posebne mere, možemo da napravimo dodatne tabele sa datumom isporuke i datumom isporuke. Zatim možemo da kreiramo aktivne relacije između njih.
U ovom primeru napravili 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 različitoj tabeli sa datumima, želimo da ih imenimo na način koji ih razlikuje od istih kolona u tabeli "Kalendar". Na primer, napravili smo kolone pod imenom "Godina Isporuke", "ShipMonth", "ShipQuarter" i tako dalje.
Ako napravimo izvedenu tabelu i stavimo meru "Ukupna prodaja" u oblast "VREDNOSTI", "Razmeštena GodinaIsporuke" i "KoličinaIsporuke" u oblast REDOVI, videćemo iste rezultate koje smo videli kada smo kreirali neaktivnu relaciju 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 prelaze neaktivne relacije pomoću funkcije USERELATIONSHIP. S druge strane, pravljenje više tabela sa datumima može da bude zbunjujuće u listi polja, a pošto imate više tabela u modelu podataka, to zahteva više memorije. Eksperimentišete sa onima koji vam više dobro funkcionišu.
Svojstvo "Tabela sa datumima"
Svojstvo Tabela sa datumima postavlja metapodatake neophodne za Time-Intelligence funkcije kao što su TOTALYTD, PREVIOUSMONTH i DATESBETWEEN da bi ispravno funkcionisale. Kada se izračunavanje pokrene pomoću jedne od ovih funkcija, mašina za formule programnog programa Power Pivot zna gde treba da ode kako bi nabavila potrebne datume.
Upozorenje: Ako ovo svojstvo nije postavljeno, mere koje koriste DAX Time-Intelligence možda neće vratiti tačne rezultate.
Kada podesite svojstvo Tabela sa datumima, navodite tabelu sa datumima i kolonu sa datumima tipa podataka "Datum (datum/vreme) u samoj tabeli.
Kako da: postavite svojstvo "Tabela sa datumima"
-
U prozoru PowerPivot 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" Excel ili 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 i svaki red je ponoć. Na primer, ako polje DateTimeKey u tabeli sa činjenicama "Prodaja" ima vrednosti kao što su 19.10.2010. u 12:00:00, to znači da su vrednosti do nivoa 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 nivou preciznosti u minutu. Vrednosti mogu da budu i preciznost nivoa časa ili čak sekunde preciznosti. Vrednost preciznosti u vremenu imaće značajan uticaj na način kreiranja tabele sa datumima i odnosa između tabele sa činjenicama.
Morate da utvrdite da li ćete preciznost podataka skupiti na dnevni nivo preciznosti ili na vremenski nivo preciznosti. Drugim rečima, možda ćete želeti da koristite kolone u tabeli sa datumima kao što su "Morning", "Afternoon" ili "Hour" kao polja za datum vremena u oblastima "Red", "Kolona" ili "Filter" izvedene tabele.
Napomena: Dani su najmanja jedinica vremena sa njom funkcije DAX vremenske inteligencije. Ako nema potrebe da radite sa vrednostima vremena, trebalo bi da smanjite preciznost podataka kako biste koristili dane kao minimalnu jedinicu.
Ako nameravate da skupite podatke na nivo vremena, tabeli sa datumima će biti potrebna kolona sa datumima sa uključenim vremenom. Zapravo, biće vam potrebna kolona sa datumima sa po jednim redom za svaki čas, a možda i svaki minut, svakog dana, za svaku godinu u opsegu datuma. To je zato što morate da imate podudarne vrednosti da biste kreirali relaciju između kolone "DateTimeKey" u tabeli sa činjenicama i kolone sa datumima. Kao što možete zamisliti, ako uključite mnogo godina, ovo može da napravi veoma veliku tabelu za datume.
Međutim, u većini slučajeva želite da skupite 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 "Filter" izvedene tabele. U ovom slučaju, kolona sa datumima u tabeli sa datumima treba da sadrži samo jedan red za svaki dan u godini, kao što smo već opisali.
Ako kolona sa datumima sadrži nivo preciznosti vremena, ali ćete samo jedan nivo dana sažeti kako biste napravili relaciju između tabele sa činjenicama i tabele sa datumima, možda ćete morati da izmenite tabelu sa činjenicama tako što ćete napraviti novu kolonu koja skraćuje vrednosti u koloni sa datumom na vrednost dana. Drugim rečima, konvertuj vrednost kao što je 19.10.2010. u 8:44:00 do 19.10.2010. Zatim možete da napravite relaciju između ove nove kolone i kolone sa datumima u tabeli sa datumima jer se vrednosti podudaraju.
Hajde da pogledamo primer. Ova slika prikazuje kolonu "DateTimeKey" u tabeli sa činjenicama "Prodaja". Sve agregatne funkcije za podatke u ovoj tabeli potrebne su 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, već samo stvarni datum.
Pošto nije potrebno 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 čas i svaki minut svakog dana u svakoj godini. Kolona Datum u tabeli sa datumima izgleda ovako:
Da bismo kreirali relaciju između kolone DateTimeKey u tabeli "Prodaja" i kolone "Datum" u tabeli "Kalendar", možemo da napravimo novu izračunatu kolonu u tabeli sa činjenicama o prodaji i da koristimo funkciju TRUNC da bismo skratili 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 (imenovali smo DateKey) sa datumom iz kolone DateTimeKey i vremenom 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 napravimo izračunatu kolonu u tabeli "Prodaja" koja smanjuje preciznost vremena u koloni DateTimeKey na nivo preciznosti od sat vremena. U ovom slučaju funkcija TRUNC neće raditi, ali i dalje možemo da koristimo druge DAX funkcije za datum i vreme da bismo izdvojili i ponovo spojili novu vrednost na časni nivo preciznosti. Formulu kao što je ova možemo koristiti:
= 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.
Pravljenje datuma koji su od veće važnijih
Mnoge kolone sa datumima koje kreirate u tabeli sa datumima neophodne su za druga polja, ali zapravo nisu sve to korisne u analizi. Na primer, polje DateKey u tabeli "Prodaja" na koje smo uputili i prikazano u ovom članku važno je zato što se za svaku transakciju ta transakcija beleži kao da se javlja određenog datuma i vremena. Ali sa tačke analize i izveštavanja nije sve to korisno jer ne možemo da ih 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 u stvari, ali ne možete da je koristite kao dimenziju u izvedenoj tabeli.
Da biste zadržli tabele i kolone u njima što je moguće korisnije, kao i da biste olakšali upotrebu lista polja izvedene tabele ili Power View izveštaja, važno je da sakrijete nepotrebne kolone od klijentski alatki. Možda ćete želeti i da sakrijete određene tabele. Tabela Praznici koja je 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 "Praznik" u tabeli "Praznici" kao polja u izvedenoj tabeli. Ovde ponovo možete da sakrijete celu tabelu "Praznici" da biste olakšali okno "Liste polja".
Drugi važan aspekt rada sa datumima jeste konvencije imenvanja. Tabelama i kolonama možete dati ime u power Pivot šta god želite. Međutim, imajte na umu, naročito ako ćete radnu svesku deliti sa drugim korisnicima, dobra konvencija imenljenja olakšava identifikovanje tabela i datuma, ne samo u listama polja, već i u Programskom programu Power Pivot i U DAX formulama.
Kada napravite tabelu sa datumima u modelu podataka, možete da počnete da pravite mere koje će vam pomoći da na najviše mestu dobijete podatke. Neke su možda jednostavne kao sabiranje ukupnih vrednosti prodaje za tekuću godinu, a druge mogu biti složenije, gde morate da filtrirate po određenom opsegu jedinstvenih datuma. Saznajte više u temi Mere u funkcijama Power Pivot i vremenske inteligencije.
Dodatak
Konvertovanje tekstualnih tipova podataka u tip podataka datuma
U nekim slučajevima, tabela sa činjenicama sa podacima transakcije može da sadrži tekstualni tip podataka. To jest, datum koji se pojavljuje kao 12.12.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 sa datumima, obe kolone moraju da imaju tip podataka "Datum".
Obično, kada pokušate da promenite tip podataka za kolonu datuma 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 konverziju tipa podataka, dobijate grešku o nedudaranju 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 da biste raščlanili godinu, mesec, dan, vreme itd. iz tekstualnih niski, a zatim je ponovo spojite na način na koji Power Pivot može da čita kao pravi datum.
U ovom primeru, uvezli smo tabelu činjenica pod imenom "Prodaja" u Power Pivot. Ona sadrži kolonu pod imenom "DateTime". Vrednosti izgledaju ovako:
Ako u grupi Oblikovanje pogledamo Tekst na kartici Početak grupe Power Pivot, videćemo da je to tekstualni tip podataka.
Ne možemo da kreiramo relaciju između kolone "DateTime" i kolone "Datum" u tabeli sa datumima jer se tipovi podataka ne podudaraju. Ako pokušamo da promenimo tip podataka u "Datum",dobijamo grešku o nedudarnosti tipa:
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 napravimo novu kolonu koja raščlani tekst i ponovo ga kreira u vrednost koju Power Pivot može da napravi kao tip podataka "Datum".
Ne zaboravite, u odeljku Rad sa vremenom u prethodnom delu ovog članka; Osim ako nije neophodno da analiza bude na nivou preciznosti za vreme, trebalo bi da konvertujete datume u tabeli činjenica u nivo preciznosti za jedan dan. Imajući to na umu, želimo da vrednosti u novoj koloni budu na nivou preciznosti dana (ne računajući vreme). Možemo da konvertmo vrednosti u koloni "DateTime" u tip podataka datuma i uklonimo nivo preciznosti za vreme sa sledećom formulom:
=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 su datumi i automatski postavlja tip podataka na vrednost "Datum".
Ako želimo da očuvamo vremenski nivo preciznosti, jednostavno proširujemo 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 napravimo relaciju između njega i kolone sa datumom u datumu.
Dodatni resursi
Datumi u programskom dodatku Power Pivot
Izračunavanja u izvedenom grafikonu Power Pivot
Brzi početak: Naučite DAX osnove za 30 minuta
