Power Pivoti kuupäevatabelid on olulised andmete otsimisel ja arvutuste tegemisel aja lõikes. See artikkel sisaldab kuupäevatabelite põhjalikku seletust ja kirjeldab nende loomist Power Pivotis. Selles spikriteemas kirjeldatakse järgmist:
-
miks on kuupäevatabelid olulised andmete otsimisel ja arvutuste tegemisel kuupäeva ja kellaaja järgi;
-
Power Pivoti abil andmemudelisse kuupäevatabeli lisamine;
-
andmetabelis uute kuupäevaveergude (nt Aasta, Kuu ja Periood) loomine;
-
kuupäevatabelite ja faktitabelite vaheliste seoste loomine;
-
ajaga töötamine.
See artikkel on mõeldud Power Pivoti uutele kasutajatele. Kuid kasutaja peaks juba oskama andmeid importida, luua seoseid ning arvutuslikke veerge ja arvutuslikke mõõte.
Selles artiklis ei kirjeldata, kuidas kasutada DAX-i ajateabefunktsioone mõõduvalemites. Lisateavet DAX-i ajateabefunktsioonide abil mõõtude loomise kohta leiate teemast Ajateave Exceli lisandmoodulis Power Pivot.
Märkus.: Power Pivotis on nimetused „mõõt“ ja „arvutuslik väli“ sünonüümid. Kasutame selles artiklis läbivalt nimetust „mõõt“. Lisateavet leiate teemast Mõõdud Power Pivotis.
Sisukord
Kuupäevatabelite ülevaade
Andmete analüüsimine on peaaegu alati seotud andmete otsimise ja võrdlemisega kuupäevade ja kellaaegade lõikes. Näiteks soovite arvutada viimase finantskvartali müügisumma ja seda võrrelda muude kvartalite summadega või arvutada konto kuu lõpu saldo. Mõlemal juhul kasutate kuupäevi, et rühmitada ja koondada määratud ajavahemiku müügitehingud või saldod.
Power View’ aruanne
Kuupäevatabel võib sisaldada mitmel eri kujul kuupäevi ja kellaaegu. Näiteks sisaldavad kuupäevatabelid sageli veerge, nagu Finantsaasta, Kuu, Kvartal või Periood, mida saate PivotTable-liigendtabeli või Power View’ aruannete andmete tükeldamisel ja filtreerimisel väljaloendist väljadena valida.
Power View’ väljaloend
Et kuupäevaveerud, nagu Aasta, Kuu ja Kvartal, kaasaksid kõik vastava vahemiku kuupäevad peab kuupäevatabelis vähemalt üks veerg sisaldama järjestikusi kuupäevi. See tähendab kuupäevatabeli selles veerus peab iga aasta iga päeva kohta olema üks rida.
Näiteks kui andmed, mida soovite sirvida, sisaldavad kuupäevi alates 1. veebruarist 2010 kuni 30. novembrini 2012 ja esitate aruande kalendriaasta kohta, siis soovite kuupäevatabelit, mille kuupäevavahemik on vähemalt 1. jaanuar 2010 kuni 31. detsember 2012. Kuupäevatabeli iga aasta peab sisaldama iga aasta kõiki päevi. Kui värskendate andmeid regulaarselt uuemate andmetega, soovite võib-olla kasutada lõppkuupäeva kuni kahe aasta kaupa, nii et te ei pea oma kuupäevatabelit kellaaja järgi värskendama.
Järjestikuste kuupäevadega kuupäevatabel
Kui esitate finantsaasta kohta aruande, saate luua kuupäevatabeli, mis sisaldab järjestikuste kuupäevade kogumit iga finantsaasta kohta. Näiteks kui finantsaasta algab 1. märtsil ja teil on andmed 2010. aasta finantsaastate kohta kuni praeguse kuupäevani (nt 2013. aasta FY-s), saate luua kuupäevatabeli, mis algab 1.03.2009 ja sisaldab vähemalt iga finantsaasta iga päeva kuni viimase kuupäevani finantsaastas 2013.
Kui koostate nii kalendri- kui ka finantsaasta aruandeid, siis pole vaja luua eraldi kuupäevatabeleid. Üks kuupäevatabel saab sisaldada kalendriaasta, finantsaasta ja isegi kolmeteistkümne neljanädalase perioodiga kalendri veerge. Oluline on, et kuupäevatabel sisaldaks kõikide kaasatud aastate järjestikusi kuupäevi.
Kuupäevatabeli lisamine andmemudelisse
Kuupäevatabeli andmemudelisse lisamiseks on mitu võimalust:
-
relatsioonandmebaasist või muust andmeallikast importimine;
-
kuupäevatabeli Excelis loomine ja Power Pivoti uude tabelisse kopeermine või lingi lisamine;
-
Importimine rakenduse Microsoft Azure turuplatsilt.
Käsitleme iga võimalust lähemalt.
Relatsioonandmebaasist importimine
Kui impordite osa andmetest või kõik andmed andmelaost või muud tüüpi relatsioonandmebaasist, siis tõenäoliselt on kuupäevatabelid ja nende seosed muude imporditavate andmetega juba olemas. Kuupäevad ja nende vorming vastab tõenäoliselt faktiandmete kuupäevadele ja kuupäevad algavad kauges minevikus ja lõpevad kauges tulevikus. Imporditav kuupäevatabel võib olla väga mahukas ja sisaldada pikemat kuupäevavahemiku kui see, mida vajate oma andmemudelis. Power Pivoti tabeli importimisviisardi täpsema filtreerimise suvandite abil saate valida ainult need kuupäevad ja veerud, mida teil tegelikult vaja on. See võib märgatavalt vähendada töövihiku mahtu ja suurendada jõudlust.
Tabeli importimisviisard
Enamikul juhtudest pole vaja luua lisaveerge, nagu Finantsaasta, Nädal, Kuu nimetus jne, kuna need on imporditud tabelis juba olemas. Mõnel juhul on siiski pärast kuupäevatabeli andmemudelisse importimist vaja luua täiendavaid kuupäevaveerge, sõltuvalt koostatavatest aruannetest. Õnneks on seda DAX-i abil lihtne teha. Kuupäevatabeli väljade loomist kirjeldame hiljem. Iga keskkond on erinev. Kui te pole kindel, kas andmeallikatel on seotud kuupäeva- või kalendritabel, siis pöörduge andmebaasi administraatori poole.
Kuupäevatabeli loomine Excelis
Saate kuupäevatabeli luua Excelis ja kopeerida selle seejärel andmemudeli uude tabelisse. Seda on väga lihtne teha ja see pakub suurt paindlikkust.
Excelis kuupäevatabeli loomisel alustate ühe veeruga, mis hõlmab järjestikuste kuupäevade vahemikku. Seejärel saate Exceli töölehel Exceli valemite abil luua täiendavaid veerge (nt Aasta, Kvartal, Kuu, Finantsaasta, Periood jne) või pärast tabeli kopeerimist andmemudelisse luua need arvutatud veergudena. Power Pivotis täiendavate kuupäevaveergude loomist kirjeldatakse selle artikli jaotises Uute kuupäevaveergude lisamine kuupäevatabelisse .
Õpetus: kuupäevatabeli loomine Excelis ja selle kopeerimine andmemudelisse
-
Tippige Exceli tühja töölehe lahtrisse A1 kuupäevavahemikku märkiv veerupäise nimi. Tavaliselt on see näiteks Kuupäev, KuupäevKellaaeg või KuupäevVõti.
-
Tippige lahtrisse A2 alguskuupäev. Näiteks 1.1.2010.
-
Klõpsake täitepidet ja lohistage seda reanumbrini, mis sisaldab lõppkuupäeva. Näiteks 31.12.2016.
-
Valige veeru Kuupäev kõik read (sh lahtris A1 olev päisenimi).
-
Klõpsake jaotises Laadid nuppu Vorminda tabelina ja valige seejärel laad.
-
Klõpsake dialoogiboksis Tabelina vormindamine nuppu OK.
-
Kopeerige kõik read, sh päis.
-
Klõpsake Power Pivoti menüüs Avaleht nuppu Kleebi
-
Tippige dialoogiboksi Kleepimise eelvaade väljale Tabeli nimi näiteks nimi Kuupäev või Kalender. Jätke ruut Kasuta esimest rida veerupäisena märgituks ja seejärel klõpsake nuppu OK.
Power Pivotis luuakse järgmine uus kuupäevatabel (selles näites on nimeks Kalender):
Märkus.: Saate luua ka lingitud tabeli, kasutades nuppu Lisa andmemudelisse. Kuid see muudab töövihiku tarbetult mahukaks, kuna töövihikus on siis kuupäevatabeli kaks versiooni: üks Excelis, teine Power Pivotis.
Märkus.: Nimi date (kuupäev) on Power Pivoti märksõna. Kui panete Power Pivotis loodud tabeli nimeks Date (kuupäev), siis tuleb mistahes DAX-i valemites, mis sisaldavad argumendis tabeli nime, see ümbritseda ülakomadega . Kõik selles näites kasutatavad illustratsioonid ja valemid viitavad Power Pivotis loodud kuupäevatabelile nimega Kalender.
Nüüd on andmemudelis kuupäevatabel. Uusi kuupäevaveerge (nt Aasta, Kuu jne) saate lisada DAX-i abil.
Uute kuupäevaveergude lisamine kuupäevatabelile
Kuupäevatabel, milles on üks kuupäevaveerg, mis sisaldab iga aasta iga päeva jaoks eraldi rida, on oluline kuupäevavahemiku kõigi kuupäevade määramiseks. See on oluline ka faktitabeli ja kuupäevatabeli vahelise seose loomiseks. Kuid sellest ühest kuupäevaveerust, mis sisaldab iga päeva jaoks ühte rida, pole kasu PivotTable-liigendtabelis või Power View’ aruandes kuupäevade lõikes analüüsimisel. Kuupäevatabel peaks sisaldama veerge, mis aitavad andmeid koondada kuupäevavahemiku või kuupäevarühma alusel. Näiteks võite soovida summeerida müügisummad kuu või kvartali järgi või luua mõõdu, mis arvutab iga-aastase kasvu. Mõlemal neist juhtudest on kuupäevatabelis vaja aasta, kuu ja kvartali veerge, et saaksite vastava perioodi andmed koondada.
Kui importisite kuupäevatabeli relatsioonandmeallikast, võib see juba sisaldada soovitud kuupäevaveerge. Mõnel juhul võite soovite osasid veerge muuta või luua täiendavaid kuupäevaveerge. See on eriti tõenäoline, kui lõite ise kuupäevatabeli Excelis ja kopeerisite selle andmemudelisse. Õnneks on Power Pivotis uute kuupäevaveergude loomine DAX-i kuupäeva- ja kellaajafunktsioonide abil üsna lihtne.
Näpunäide.: Kui te pole DAX-i veel kasutanud, siis hea võimalus õppimise alustamiseks on lugeda artiklit Lühijuhend: omandage DAX-i põhiteadmised 30 minutiga, mille leiate veebisaidilt Office.com.
DAX-i kuupäeva- ja kellaajafunktsioonid
Kui olete Exceli valemites kunagi kuupäeva- ja kellaajafunktsioonidega töötanud, siis tõenäoliselt tunnete ka kuupäeva- ja kellaajafunktsioone. Kuigi need funktsioonid sarnanevad Exceli vastavate funktsioonidega, on ka mõned olulised erinevused.
-
DAX-i kuupäeva- ja kellaajafunktsioonid kasutavad kuupäeva ja kellaaja (datetime) andmetüüpi.
-
Need võivad võtta veerust pärit väärtusi argumendina.
-
Neid saab kasutada kuupäevaväärtuste tagastamiseks ja/või käsitsemiseks.
Neid funktsioone kasutatakse sageli kuupäevatabelis kohandatud kuupäevaveergude loomisel, et neid oleks oluline mõista. Kasutame mitmeid neid funktsioone veergude loomiseks aasta, kvartali, finantskuu jne jaoks.
Märkus.: DAX-i kuupäeva- ja kellaajafunktsioonid pole samad mis ajateabefunktsioonid. Lugege lisateavet Exceli Power Pivoti ajateabe kohta.
DAX sisaldab järgmisi kuupäeva- ja kellaajafunktsioone:
Valemites saate kasutada ka muid DAX-i funktsioone. Näiteks kasutavad paljud siin kirjeldatud valemid matemaatika- ja trigonomeetrilisi funktsioone (nt MOD ja TRUNC), loogikafunktsioone (nt IF) ja tekstifunktsioone(nt FORMAT ). Lisateavet muude DAX-i funktsioonide kohta leiate selle artikli jaotisest Lisaressursid .
Kalendriaasta valemite näited
Järgmised näited kirjeldavad valemeid, mida kasutatakse lisaveergude loomiseks kuupäevatabelis Kalender. Üks veerg (Kuupäev) on juba olemas ja see sisaldab järjestikkusi kuupäevi vahemikus 1.1.2010 kuni 31.12.2016.
Aasta
=YEAR([kuupäev])
Selles valemis tagastab funktsioon YEAR veeru Kuupäev väärtusest aasta. Kuna veeru Kuupäev andmetüüp on kuupäev ja kellaaeg, siis teab funktsioon YEAR, kuidas sellest tagastada aasta.
Kuu
=MONTH([kuupäev])
Sarnaselt funktsiooniga YEAR saame selles valemis lihtsalt kasutada funktsiooni MONTH , et tagastada veerust Kuupäev kuu väärtus.
Kvartal
=INT(([kuu]+2)/3)
Selles valemis kasutame funktsiooni INT kuupäevaväärtuse tagastamiseks täisarvuna. Funktsiooni INT jaoks määratud argument on veeru Kuu väärtus, liita 2 ja seejärel jagada see 3-ga, et saada meie kvartal ehk 1 thru 4.
Kuu nimetus
=FORMAT([kuupäev];"mmmm")
Selles valemis teisendame kuu nime saamiseks funktsiooni FORMAT abil arvulise väärtuse veerust Kuupäev tekstiks. Esimese argumendina määrame veeru Kuupäev ja seejärel vormingu; soovime, et meie kuu nimes kuvataks kõik märgid, seega kasutame märki "mmmm". Meie tulem näeb välja selline:
Kui soovime kasutada kuunimetuste lühendeid, siis kasutame vormingu argumendi väärtust „mmm”.
Nädalapäev
=FORMAT([kuupäev];"ddd")
Selles valemis kasutame funktsiooni FORMAT nädalapäeva toomiseks. Kuna soovime nädalapäeva lühendit, siis on vormingu argumendi väärtuseks „ddd”.
PivotTable-liigendtabeli näide
Kui teil on kuupäevade (nt Aasta, Kvartal, Kuu jne) jaoks väljad, saate neid kasutada PivotTable-liigendtabelis või aruandes. Järgmisel illustratsioonil on alale VÄÄRTUSED lisatud faktitabeli Müük väli Müügisumma ja alale READ dimensioonitabeli Kalender veerud Aasta ja Kvartal. Müügisumma on koondatud aasta ja kvartali põhjal.
Finantsaasta valemite näited
Finantsaasta
=IF([kuu]<= 6;[aasta];[aasta]+1)
Selles näites algab finantsaasta 1. juulil.
Kuupäevaväärtusest finantsaasta tuletamiseks pole funktsiooni, kuna finantsaasta algus- ja lõppkuupäevad erinevad sageli kalendriaasta omadest. Finantsaasta saamiseks kasutame esmalt funktsiooni IF , et kontrollida, kas kuu väärtus on väiksem või võrdne 6-ga. Teine argument määrab, et kui kuu väärtus on väiksem või võrdne kuuega, siis tagastatakse veerus Aasta olev väärtus. Kui ei, siis tagastatakse veerus Aasta olev väärtus ja sellele lisatakse 1.
Teine võimalus finantsaasta lõpukuu väärtuse määramiseks on luua mõõt, mis lihtsalt määrab kuu. Näiteks FAL:=6. Siis saate kuu numbri asemel viidata mõõdu nimele. Näiteks =IF([kuu]<=[FAL];[aasta];[aasta]+1). See võimaldab suuremat paindlikkust, kui finantsaasta lõpukuule tuleb viidata mitmes valemis.
Finantskuu
=IF([kuu]<= 6; 6+[kuu]; [kuu]- 6)
Selles valemis on määratud, kui veeru [Kuu] väärtus on väiksem või võrdne kuuega, siis võetakse kuus ja lisatakse veeru Kuu väärtus. Muidu lahutatakse veeru [Kuu] väärtusest kuus.
Finantskvartal
=INT(([finantskuu]+2)/3)
Veeru Finantskvartal jaoks kasutatav valem sarnaneb kalendriaasta kvartali valemiga. Ainsaks erinevuseks on veeru [Kuu] asemel veeru [Finantskuu] kasutamine.
Pühad või erikuupäevad
Võib-olla soovite lisada kuupäevaveeru, mis näitab, et teatud kuupäevad on riigipühad või muul põhjusel olulised. Näiteks võite soovida summeerida uusaastapäeva müügisummad, lisades PivotTable-liigendtabelisse riigipühade välja tükeldi või filtrina. Muul juhul võite soovida need kuupäevad muudest kuupäevaveergudest või mõõtudest välja jätta.
Pühade ja oluliste päevate kaasamine on küllaltki lihtne. Võite luua Excelis tabeli, mis sisaldab kaasatavaid kuupäevi. Seejärel saate tabeli kopeerida või kasutada nuppu Lisa andmemudelisse tabeli andmemudelisse lisamiseks lingitud tabelina. Tavaliselt pole vaja selle tabeli ja tabeli Kalender vahel luua seost. Kõik sellele viitavad valemid saavad väärtuste tagastamiseks kasutada funktsiooni LOOKUPVALUE .
Järgmine Excelis loodud näidistabel sisaldab kuupäevatabelisse lisatavaid pühasid.
|
Kuupäev |
Püha |
|---|---|
|
1.1.2010 |
uusaasta |
|
02.04.2010 |
suur reede |
|
24.12.2010 |
jõululaupäev |
|
1.1.2011 |
uusaasta |
|
22.04.2011 |
suur reede |
|
24.12.2011 |
jõululaupäev |
|
1.01.2012 |
uusaasta |
|
06.04.2012 |
suur reede |
|
24.12.2012 |
jõululaupäev |
|
1.01.2013 |
uusaasta |
|
29.03.2013 |
suur reede |
|
24.12.2013 |
jõululaupäev |
|
18.04.2014 |
suur reede |
|
24.12.2014 |
jõululaupäev |
|
01.01.2014 |
uusaasta |
|
18.04.2014 |
suur reede |
|
24.12.2014 |
jõululaupäev |
|
01.01.2015 |
uusaasta |
|
03.04.2014 |
suur reede |
|
24.12.2015 |
jõululaupäev |
|
1.1.2016 |
uusaasta |
|
25.03.2016 |
suur reede |
|
24.12.2016 |
Jõulud |
Kuupäevatabelis loome veeru Püha ja kasutame järgmist valemit:
=LOOKUPVALUE(Pühad[Püha];Pühad[Kuupäev];Kalender[kuupäev])
Vaatame seda valemit lähemalt.
Kasutame funktsiooni LOOKUPVALUE tabeli Pühad veerust Püha väärtuste toomiseks. Esimeses argumendis määrame veeru, kus on meie tulemiväärtused. Määrame tabeli Pühad veeru Püha, kuna see sisaldab väärtusi, mida soovime tagastada.
=LOOKUPVALUE(Pühad[Püha];Pühad[Kuupäev];Kalender[kuupäev])
Seejärel määrame teise argumendi ehk otsinguveeru, mis sisaldab otsitavaid kuupäevi. Määrame järgmiselt tabeli Pühad veeru Kuupäev:
=LOOKUPVALUE(Pühad[Püha];Pühad[Kuupäev];Kalender[kuupäev])
Lõpuks määrame tabeli Kalender veeru, mis sisaldab kuupäevi, mida soovime otsida tabelist Pühad. See on loomulikult tabeli Kalender veerg Kuupäev.
=LOOKUPVALUE(Pühad[Püha];Pühad[Kuupäev];Kalender[kuupäev])
Veerg Püha tagastab püha nimetuse iga rea jaoks, mis sisaldab kuupäeva väärtust, mis vastab tabelis Pühad olevale kuupäevale.
Kohandatud kalender – kolmteist neljanädalast perioodi
Osad ettevõtted (nt jaekaubandus- või toitlustusettevõtted) kasutavad aruandluseks muid perioode (nt kolmeteistkümmet neljanädalast perioodi). Kolmeteistkümne neljanädalase perioodiga kalendri korral on iga periood 28 päeva, seega iga periood sisaldab nelja esmaspäeva, nelja teisipäeva, nelja kolmapäeva jne. Iga periood sisaldab sama arvu päevi ja tavaliselt satuvad pühad igal aastal samasse perioodi. Perioodi alguseks saab valida mistahes nädalapäeva. Sarnaselt kalendriaasta või finantsaasta kuupäevadega, saate kohandatud kuupäevadega veergude loomiseks kasutada DAX-i.
Alltoodud näidetes algab esimene täisperiood finantsaasta esimesel pühapäeval. Sel juhul algab finantsaasta 1.
Nädal
See väärtus annab meile nädalanumbri alates finantsaasta esimesest täisnädalast. Selles näites algab esimene täisnädal pühapäeval, nii et tabeli Kalender esimese finantsaasta esimene täisnädal algab tegelikult 4.07.2010 ja kestab tabeli Kalender viimase täisnädala. Kuigi see väärtus ise pole analüüsis nii kasulik, on vaja arvutada kasutamiseks teistes 28-päevase perioodi valemites.
=INT(([kuupäev]-40356)/7)
Vaatame seda valemit lähemalt.
Kõigepealt loome valemi, mis tagastab veeru Kuupäev väärtused täisarvuna.
=INT([kuupäev])
Seejärel soovime üles otsida esimese finantsaasta esimese pühapäeva. Näeme, et see on 04.07.2010.
Nüüd lahutame sellest väärtusest arvu 40356 (see on eelmise finantsaasta viimase pühapäeva, 27.06.2010 täisarvuline väärtus), et saada tabeli Kalender algusest möödunud päevade arv:
=INT([kuupäev]-40356)
Seejärel jagame tulemi seitsmega (päevade arv nädalas):
=INT(([kuupäev]-40356)/7)
Tulem on järgmine:
Periood
Selle kohandatud kalendri periood sisaldab 28 päeva ja algab alati pühapäeval. See veerg tagastab perioodi numbri alates esimese finantsaasta esimesest pühapäevast.
=INT(([nädal]+3)/4)
Vaatame seda valemit lähemalt.
Kõigepealt loome valemi, mis tagastab veeru Nädal väärtused täisarvuna:
=INT([Nädal])
Seejärel lisame väärtusele kolme:
=INT([nädal]+3)
Jagame tulemi neljaga:
=INT(([nädal]+3)/4)
Tulem on järgmine:
Perioodi finantsaasta
See väärtus tagastab perioodi finantsaasta.
=INT(([periood]+12)/13)+2010
Vaatame seda valemit lähemalt.
Kõigepealt loome valemi, mis tagastab veeru Periood väärtuse ja lisab arvu 12:
= ([periood]+12)
Jagame tulemi 13-ga, kuna finantsaastas on kolmteist 28-päevast perioodi:
=(([periood]+12)/13)
Lisame 2010, kuna see on tabeli esimene finantsaasta:
=(([periood]+12)/13)+2010
Viimasena kasutame funktsiooni INT, et eemaldada pärast 13-ga jagamist tulemist murdosa ja tagastada täisarv:
=INT(([periood]+12)/13)+2010
Tulem on järgmine:
Finantsaasta periood
See väärtus tagastab perioodinumbri (1–13) alates iga finantsaasta esimesest täisperioodist (algab pühapäeval).
=IF(MOD([periood];13); MOD([periood];13);13)
See valem on veidi keerulisem, seega kirjeldame seda alguses lihtsamas keeles. See valem määrab: aasta perioodinumbri (1–13) saamiseks tuleb veeru [Periood] väärtus jagada 13-ga. Kui number 0, siis tagastatakse 13.
Kõigepealt loome valemi, mis tagastab veeru Periood väärtuse 13-ga jagamise jäägi. MoD-funktsioone (matemaatika- ja trigonomeetrilised funktsioonid) saame kasutada järgmiselt.
=MOD([periood];13)
Tavaliselt annab see soovitud tulemi, v.a kui veerus Periood on väärtus 0, kuna need kuupäevad ei kuulu esimesse finantsaastasse (nt meie kuupäevade näidistabeli Kalender esimesed viis päeva). Lahenduseks saame kasutada funktsiooni IF. Tulemi 0 korral tagastatakse väärtus 13:
=IF(MOD([periood];13);MOD([periood];13);13)
Tulem on järgmine:
PivotTable-liigendtabeli näide
Järgmisel illustratsioonil on PivtoTable-liigendtabeli alale Väärtused lisatud faktitabeli Müük väli Müügisumma ja alale READ kuupäevadimensiooni tabeli Kalender veerud PerioodiFinantsaasta ja FinantsaastaPeriood. Müügisumma on konteksti saamiseks koondatud finantsaasta ja finantsaasta 28-päevaste perioodide põhjal.
Seosed
Kui olete andmemudelis loonud kuupäevatabeli ja hakkate otsima PivotTable-liigendtabelitest ja aruannetest andmeid ning andmeid koondama kuupäevadimensiooni tabeli veergude põhjal, tuleb tehinguandmetega faktitabeli ja kuupäevatabeli vahel luua seos.
Kuna seos tuleb luua kuupäevade põhjal, siis tuleb seos luua kindlasti veergude vahel, mille väärtuste andmetüüp on kuupäev ja kellaaeg (Kuupäev).
Faktitabeli iga kuupäevaväärtuse jaoks peab kuupäevatabeli seotud otsinguveerg sisaldama vastavaid väärtusi. Näiteks faktitabeli Müük reale (tehingukirje), mille väärtus veerus KuupäevVõti on 15.08.2012 00:00, peab olema vastav väärtus kuupäevatabeli (Kalender) seotud veerus Kuupäev. See on üks peamistest põhjustest, miks kuupäevatabeli kuupäevaveerg peab sisaldama järjestikkuste kuupäevade vahemikku, mis sisaldab faktitabeli mistahes kuupäeva.
Märkus.: Kuigi mõlema tabeli kuupäevaveeru andmetüüp peab olema sama (Kuupäev), siis veergude vorming pole oluline.
Märkus.: Kui Power Pivot ei lase kahe tabeli vahel seost luua, siis ei pruugi kuupäevaväljad sisaldada sama täpsusastmega kuupäeva ja kellaaega. Sõltuvalt veeru vormingust võidakse väärtusi kuvada ühesuguselt, kuid olla talletatud erinevalt. Lugege lisaks ajaga töötamise kohta.
Märkus.: Vältige täisarvuliste asendusvõtmete kasutamist seostes. Kui impordite andmed relatsioonilisest andmeallikast, esitatakse kuupäeva ja kellaaja veerud sageli asendusvõtmega, mis on kordumatu kuupäeva tähistamiseks kasutatav täisarvuveerg. Power Pivotis peaks vältima seoste loomist täisarvuliste kuupäeva/kellaaja võtmetega ja kasutama võtmete asemel veerge, mis sisaldavad kordumatuid väärtusi andmetüübiga date. Kuigi asendusvõtmete kasutamist loetakse harilikes andmeladudes heaks tavaks, pole täisarvulisi võtmeid Power Pivotis vaja ja see võib raskendada PivotTable-liigendtabelites väärtuste rühmitamist erinevate kuupäevaperioodide kaupa.
Kui seose loomisel kuvatakse tüübilahknevuse tõrge, siis tõenäoliselt pole faktitabeli veeru andmetüüp kuupäev. See võib juhtuda, kui Power Pivot ei saa automaatselt teisendada mittekuupäeva (tavaliselt andmetüüp tekst) andmetüübiks kuupäev. Faktitabeli veergu saab ikkagi kasutada, kuid andmed tuleb teisendada DAX-i valemiga uues arvutuslikus veerus. Vt lisa Teksti andmetüübiga kuupäevade teisendamine kuupäeva andmetüübiga kuupäevadeks.
Mitu seost
Mõnel juhul võib osutuda vajalikuks luua mitu seost või luua mitu kuupäevatabelit. Näiteks kui faktitabelis Müük on mitu kuupäevavälja (nt KuupäevVõti, Lähetuskuupäev ja Tagastuskuupäev), võib neil kõigil olla seos kuupäevatabeli Kalender väljaga Kuupäev, kuid aktiivseks seoseks saab olla ainult üks väljadest. Kuna dateKey tähistab tehingu kuupäeva ja seega kõige olulisemat kuupäeva, oleks see kõige parem toiming aktiivse seosena. Teistel on passiivsed suhted.
Järgmine PivotTable-liigendtabel arvutab kogumüügi finantsaastate ja finantskvartalite lõikes. Mõõt Kogumüük (valem Kogumüük:=SUM([müügisumma])) paigutatakse alale VÄÄRTUSED ja kuupäevatabeli Kalender väljad Finantsaasta ja Finantskvartal alale READ.
See lihtne PivotTable-liigendtabel töötab õigesti, kuna soovime summeerida kogumüügi veerus KuupäevVõti oleva tehingu kuupäeva järgi. Mõõt Kogumüük kasutab veerus KuupäevVõti olevaid kuupäevi ja summeeritakse finantsaasta ja finantskvartali järgi, kuna tabeli Müük veeru KuupäevVõti ja kuupäevatabeli Kalender veeru Kuupäev vahel on seos.
Passiivsed seosed
Aga mis siis, kui soovime summida oma kogumüügi tehingukuupäeva, vaid tarnekuupäeva järgi? Vajame seost tabeli Müük veeru Tarnekuupäev ja tabeli Kalender veeru Kuupäev vahel. Kui me seda seost ei loo, põhinevad meie liitmised alati tehingukuupäeval. Kuid meil võib olla mitu seost, kuigi ainult üks saab olla aktiivne, ja kuna tehingu kuupäev on kõige olulisem, saab see aktiivse seose tabeliga Kalender.
Praegusel juhul on Tarnekuupäev passiivne seos, nii et iga mõõduvalem, mis luuakse tarnekuupäevadel põhinevate andmete koondamiseks, peab määrama passiivse seose funktsiooni USERELATIONSHIP abil.
Kuna tabeli Müük veeru Tarnekuupäev ja tabeli Kalender veeru Kuupäev vahel on passiivne seos, saame luua mõõdu, mis summeerib kogumüügi tarnekuupäeva alusel. Kasutatava seose määrame järgmise valemiga.
Kogumüük tarnekuupäeva järgi:=CALCULATE(SUM(Müük[Müügisumma]); USERELATIONSHIP(Müük[Tarnekuupäev]; Kalender[Kuupäev]))
See valem määrab järgmise: arvutatakse veeru Müügisumma summa, kuid filtreeritakse tabeli Müük veeru Tarnekuupäev ja tabeli Kalender veeru Kuupäev vahelise seose alusel.
Kui loome nüüd PivotTable-liigendtabeli ja paneme alale VÄÄRTUSED mõõdu „Kogumüük tarnekuupäeva järgi“ ning alale READ veerud Finantsaasta ja Finantskvartal, siis on lõppsumma sama, kuid finantsaasta ja finantskvartali kõik muud summad on erinevad, kuna need põhinevad tarnekuupäeval, mitte tehingukuupäeval.
Passiivsete seoste kasutamisel saate kasutada ainult ühte kuupäevatabelit, kuid mõõtude (nt Kogumüük tarnekuupäeva järgi) valemid peavad viitama passiivsele seosele. Teiseks võimaluseks on kasutada mitut kuupäevatabelit.
Mitu kuupäevatabelit
Teine võimalus faktitabeli mitme kuupäevaveeruga töötamiseks on luua mitu kuupäevatabelit ja luua nende vahel eraldi aktiivsed seosed. Vaatame uuesti tabelit Müük. Meil on kolm veergu kuupäevadega, mille järgi võime soovida andmeid koondada:
-
veerg KuupäevVõti – iga tehingu müügikuupäev;
-
veerg Tarnekuupäev – kuupäev ja kellaaeg, millal müüdud üksused saadeti kliendile välja;
-
Tagastuskuupäev – kuupäev ja kellaaeg, millal üks või mitu tagastatud üksust vastu võeti.
Pidage meeles, et tehingukuupäevaga väli DateKey on kõige olulisem. Enamik koondfunktsioone tehakse nende kuupäevade põhjal, seega soovime kindlasti seost selle ja tabeli Kalender veeru Kuupäev vahel. Kui me ei soovi luua passiivseid seoseid tarnekuupäeva ja tagastuskuupäeva ning tabeli Kalender välja Kuupäev vahel, mis nõuab seega erilisi mõõduvalemeid, saame tarnekuupäeva ja tagastuskuupäeva jaoks luua täiendavaid kuupäevatabeleid. Seejärel saame nende vahel luua aktiivsed seosed.
Selles näites oleme loonud uue kuupäevatabeli tarnekalendri. See tähendab muidugi ka täiendavate kuupäevaveergude loomist ja kuna need kuupäevaveerud asuvad teises kuupäevatabelis, soovime neile panna nime, mis eristab neid tabeli Kalender samadest veergudest. Näiteks oleme loonud veerud Tarneaasta, Tarnekuu, Tarnekvarter jne.
Kui loome PivotTable-liigendtabeli ja paneme alale VÄÄRTUSED mõõdu Kogumüük ning alale READ veerud TarneFinantsaasta ja TarneFinantskvartal, siis näeme samu tulemeid kui siis, kui lõime passiivse seose ja erilise arvutusliku välja „Kogumüük tarnekuupäeva järgi“.
Mõlemat lähenemist tasub hoolikalt kaaluda. Ühe kuupäevatabeli ja mitme seose kasutamisel võib olla vaja luua erilisi mõõte, mis võimaldavad funktsiooni USERELATIONSHIP abil kasutada passiivseid seoseid. Teisalt võib mitme kuupäevatabeli loomine põhjustada segadust väljaloendis ja kui andmemudelis on rohkem tabeleid, siis on vaja ka rohkem mälu. Katsetage ja valige endale sobivaim variant.
Atribuut Kuupäevatabel
Atribuut Kuupäevatabel sisaldab metaandmeid, mida ajateabefunktsioonid (nt TOTALYTD, PREVIOUSMONTH ja DATESBETWEEN) vajavad õigesti töötamiseks. Kui arvutus kasutab mõnda neist funktsioonidest, siis teab Power Pivoti valemimootor, kust hankida vajalikud kuupäevad.
Hoiatus.: Kui see atribuut pole määratud, siis ei pruugi DAX-i ajateabefunktsioone kasutavad mõõdud tagastada õigeid tulemeid.
Kui määrate atribuudi Kuupäevatabel, siis määrate kuupäevatabeli ja selle kuupäevaveeru, mille andmetüüp on kuupäev (kuupäev ja kellaaeg).
Õpetus: atribuudi Kuupäevatabel määramine
-
Valige PowerPivoti aknas tabel Kalender.
-
Klõpsake menüü Kujundus nuppu Märgi kuupäeva tabelina.
-
Valige dialoogiboksis Märgi kuupäeva tabelina kordumatute väärtustega veerg ja andmetüüp kuupäev.
Ajaga töötamine
Kõik Exceli ja SQL Serveri kuupäevaväärtused, mille andmetüüp on kuupäev on tegelikult arvud. Selles arvus on numbrid, mis viitavad kellaajale. Enamikul juhtudest on iga rea kellaaeg kesköö. Kui faktitabeli Müük väljal KuupäevKellaaegVõti on väärtus 19.10.2010 00:00:00, tähendab see, et väärtused on päeva täpsusega. Kui välja KuupäevKellaaegVõti väärtused sisaldavad kellaaega (nt 19.10.2010 8:44:00), tähendab see, et väärtused on minuti täpsusega. Väärtused võivad olla ka tunni täpsusega või isegi sekundi täpsusega. Kellaajaväärtuse täpsusaste mõjutab oluliselt kuupäevatabeli loomist ja selle ning faktitabeli vahelisi seoseid.
Peate otsustama, kas soovite andmeid koondada päeva või kellaaja täpsusega. Teisisõnu võite soovida PivotTable-liigendtabeli aladel Read, Veerud või Filtrid kasutada kuupäevatabeli veerge, nagu Hommik, Pärastlõuna või Tund.
Märkus.: Väikseim ajaühik, mida DAX-i ajateabefunktsioonid saavad kasutada, on päev. Kui kellaajaväärtustega pole vaja töötada, siis tuleks vähendada andmete täpsust päevatasemele.
Kui kavatsete koondada andmeid kellaaja täpsusega, siis peab kuupäevatabelis olema kuupäevaveerg, mis sisaldab kellaaegu. See peab sisaldama kuupäevaveergu, milles on kuupäevavahemiku iga aasta iga päeva iga tunni (või isegi iga minuti) jaoks eraldi rida. Põhjuseks on see, et faktitabeli veeru KuupäevKellaaegVõti ja kuupäevatabeli kuupäevaveeru vahel seose loomiseks peavad need sisaldama samu väärtusi. Nagu võite arvata, siis paljude aastate kasutamisel on tegemist väga mahuka kuupäevatabeliga.
Enamikul juhtudest piisab siiski andmete koondamisest päeva tasemel. Teisisõnu kasutate PivotTable-liigendtabeli aladel Read, Veerud või Filtrid veerge, nagu Aasta, Kuu, Nädal või Nädalapäev. Sel juhul peab kuupäevatabeli kuupäevaveerg sisaldama vaid ühte rida aasta iga päeva kohta (nagu eelpool kirjeldatud).
Kui kuupäevaveerg sisaldab kellaaega, aga koondate andmeid ainult päeva täpsusega, siis võimalik, et faktitabeli ja kuupäevatabeli vahel seose loomiseks tuleb faktitabelit muuta ja luua uus veerg, mis kärbib kuupäevaveeru väärtused päevaväärtuseks. Teisisõnu teisendab väärtuse 19.10.2010 8:44:00 väärtuseks 19.10.2010 00:00:00. Seejärel saate luua seose uue veeru ja kuupäevatabeli kuupäevaveeru vahel, kuna väärtused on samad.
Vaatame näidet. Sellel pildil on faktitabeli Müük veerg KuupäevKaegVõti. Kõik selle tabeli andmete koondamised peavad olema ainult päevatasemel, kasutades kuupäevatabeli Kalender veerge nagu Aasta, Kuu, Kvartal jne. Väärtuses sisalduv kellaaeg pole oluline, vaid ainult tegelik kuupäev.
Kuna andmeid pole vaja analüüsida kellaajatasemel, siis ei pea kuupäevatabeli Kalender kuupäevaveerg sisaldama iga aasta iga päeva iga tunni iga minuti jaoks eraldi rida. Seega on kuupäevatabeli veerg Kuupäev järgmine:
Tabeli Müük veeru KuupäevKellaaegVõti ja tabeli Kalender veeru Kuupäev vahel seose loomiseks saame faktitabelis Müük luua uue arvutusliku veeru ja kasutada funktsiooni TRUNC veeru KuupäevKellaaegVõti kuupäeva- ja kellaajaväärtuse kärpimiseks kuupäevaväärtuseks, mis vastab tabeli Kalender veeru Kuupäev väärtustele. Valem näeb välja selline:
=TRUNC([KuupäevKellaaegVõti];0)
See loob uue veeru (millele panime nime KuupäevVõti), mille igal real on veerust KuupäevKellaaegVõti võetud kuupäev ja kellaaeg 00:00:00.
Nüüd saame luua seose selle uue veeru (KuupäevVõti) ja tabeli Kalender veeru Kuupäev vahel.
Saame tabelis Müük luua sarnaselt arvutusliku veeru, mis vähendab veeru KuupäevKellaaegVõti aja täpsuse tunnitasemele. Sel juhul funktsioon TRUNC ei tööta, kuid saame kasutada muid DAX-i kuupäeva- ja kellaajafunktsioone väärtuse ekstraktimiseks ja uue väärtuse ühendamiseks tunni täpsusega. Saame kasutada järgmist valemit:
= DATE (YEAR([KuupäevKellaaegVõti]); MONTH([KuupäevKellaaegVõti]); DAY([KuupäevKellaaegVõti]) ) + TIME (HOUR([KuupäevKellaaegVõti]); 0; 0)
Uus veerg on järgmine:
Kui kuupäevatabeli veerg Kuupäev sisaldab tunni täpsusega väärtusi, siis saame nende vahel luua seose.
Kuupäevade muutmine lihtsamini kasutatavaks
Paljud kuupäevatabelis loodavad kuupäevaveerud on vajalikud muude väärtuste jaoks, kuid mitte eriti kasulikud analüüsimisel. Näiteks tabeli Müük väli KuupäevVõti, millele oleme kogu selle artikli jooksul viidanud, on oluline, kuna iga tehingu korral on salvestatud tehing toimunuks sellel kuupäeval ja kellaajal. Kuid analüüsi ja aruannete koostamise seisukohast pole see eriti kasulik, kuna me ei saa kasutada seda PivotTable-liigendtabeli ega aruande ridade, veergude ega filtrite alal.
Sarnaselt on meie tabeli Kalender veerg Kuupäev väga kasulik (lausa kriitilise tähtsusega), kuid seda ei saa kasutada PivotTable-liigendtabeli dimensioonina.
Et tabelid ja nende veerud oleksid võimalikult kasulikud ning, et PivotTable-liigendtabeli või Power View’ aruande väljaloendist oleks vajaliku leidmine lihtsam, tuleks klienttööriistade eest peita ebavajalikud veerud. Võimalik, et soovite peita ka osad tabelid. Ülalpool näidatud tabel Pühad sisaldab pühade kuupäevi, mis on olulised tabeli Kalender teatud veergude jaoks, kuid tabeli Pühad veerge Kuupäev ja Püha endid ei saa kasutada PivotTable-liigendtabeli väljadena. Väljaloendite lihtsustamiseks saate siin kohal peita kogu tabeli Pühad.
Kuupäevadega töötamisel tuleks jälgida ka nimetamise põhimõtteid. Power Pivoti tabelitele ja veergudele saate panna mistahes nimesid. Kuid arvestage, et kui annate töövihiku teiste jaoks ühiskasutusse, siis teeb kindlate nimetamise põhimõtete jälgimine tabelite ja kuupäevade tuvastamise lihtsamaks mitte ainult väljaloendites vaid ka Power Pivoti ja DAX-i valemites.
Kui andmemudelis on kuupäevatabel, saate hakata looma mõõte, mis aitavad teil oma andmeid parimal viisil kasutada. Mõni võib olla nii lihtne kui käesoleva aasta müügisummade summeerimine ja teised võivad olla keerukamad, kus peate filtreerima kindla kordumatute kuupäevade vahemiku alusel. Lisateavet leiate teemast Power Pivoti ja ajateabefunktsioonide mõõdud.
Lisa
Teksti andmetüübiga kuupäevade teisendamine kuupäeva andmetüübiga kuupäevadeks
Mõnikord võib tehinguandmetega faktitabel sisaldada kuupäevi, mille andmetüübiks on tekst. See tähendab, et kujul 2012-12-04T11:47:09 kuvatav kuupäev pole tegelikult kuupäev, vähemalt mitte sellist tüüpi kuupäev, mida Power Pivot mõistaks. See on tekst, mis kirjeldab kuupäeva. Faktitabeli kuupäevaveeru ja kuupäevatabeli kuupäevaveeru vahel seose loomiseks peab mõlema veeru andmetüüp olema kuupäev.
Tavaliselt kui proovite teksti andmetüübiga kuupäevi sisaldava veeru andmetüübiks muuta kuupäeva, siis oskab Power Pivot automaatselt kuupäevad tõlgendada ja teisendada tõeliseks kuupäeva andmetüübiks. Kui Power Pivot ei saa andmetüübi teisendatud, kuvatakse tüübilahknevuse tõrge.
Saate siiski kuupäevad teisendada tõelise kuupäeva andmetüübiks. Saate luua uue arvutusliku välja ja kasutada DAX-i valemit tekstistringidest aasta, kuu, päeva ja kellaaja sõelumiseks ning uuesti ühendamiseks kujul, mida Power Pivot saab lugeda tõelise kuupäevana.
Antud juhul oleme importinud Power Pivotisse faktitabeli Müük. See sisaldab veergu KuupäevKellaaeg. Väärtused on järgmisel kujul:
Kui vaatame Power Pivoti menüü Avaleht jaotise Vormindus välja Andmetüüp, siis näeme, et sellel on väärtus Tekst.
Me ei saa luua seost veeru KuupäevKellaaeg ja kuupäevatabeli veeru Kuupäev vahel, kuna andmetüübid on erinevad. Kui proovime andmetüübiks muuta Kuupäev, kuvatakse järgmine tüübilahknevuse tõrge:
Antud juhul ei saanud Power Pivot teisendada andmetüübi tekst andmetüübiks kuupäev. Saame seda veergu ikkagi kasutada, kuid selle teisendamiseks tõelise kuupäeva andmetüübiks, tuleb luua uus veerg, mis sõelub teksti ja loob sellest väärtuse, millest Power Pivot saab teha kuupäeva andmetüübi.
Pidage meeles selle artikli varasema jaotise Ajaga töötamine soovitust: kui analüüs ei pea olema kellaaja täpsusega, tuleks faktitabeli kuupäevad teisendada päeva täpsusega väärtusteks. Seetõttu soovime, et uue veeru väärtused oleks päeva täpsusega (ei sisalda kellaaega). Järgmise valemiga saame teisenda veeru KuupäevKellaaeg väärtused kuupäeva andmetüübiks ja eemaldada kellaaja täpsustaseme:
=DATE(LEFT([KuupäevKellaaeg];4); MID([KuupäevKellaaeg];6;2); MID([KuupäevKellaaeg];9;2))
Sellega luuakse uus veerg (antud juhul veerg Kuupäev). Power Pivot isegi tuvastab, et väärtused on kuupäevad, ja määrab andmetüübiks automaatselt kuupäeva.
Kui soovime säilitada kellaja täpsuse, siis tuleb lihtsalt valemit laiendada hõlmama tunde, minuteid ja sekundeid.
=DATE(LEFT([KuupäevKellaaeg];4); MID([KuupäevKellaaeg];6;2); MID([KuupäevKellaaeg];9;2))+
TIME(MID([KuupäevKellaaeg];12;2); MID([KuupäevKellaaeg];15;2); MID([KuupäevKellaaeg];18;2))
Nüüd kui veeru Kuupäev andmetüübiks on kuupäev, saame luua selle ja kuupäevatabeli kuupäevaveeru vahel seose.
