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 sirvimiseks soovitud andmetel on kuupäevad 1. veebruarist 2010 kuni 30. novembrini 2012 ja te teatate kalendriaastast, siis soovite kuupäevatabelit, mille kuupäevavahemik on vähemalt 1. jaanuar 2010 kuni 31. detsember 2012. Iga aasta peab teie kuupäevatabelis sisaldama iga aasta kõiki päevi. Kui värskendate andmeid regulaarselt uuemate andmetega, võiksite lõppkuupäeva käivitada aasta või kahe võrra, nii et te ei pea kuupäevatabelit aja möödudes värskendama.
Järjestikuste kuupäevadega kuupäevatabel
Kui koostate aruande finantsaasta kohta, saate luua kuupäevatabeli, mis sisaldab iga finantsaasta jaoks külgnevaid kuupäevi. Näiteks kui teie finantsaasta algab 1. märtsil ja teil on andmeid finantsaastate 2010 kohta praeguse kuupäeva jooksul (nt 2013. aasta finantsaastal), saate luua kuupäevatabeli, mis algab 1.03.2009 ja sisaldab vähemalt iga finantsaasta iga päev kuni 2013. aasta finantsaasta viimase kuupäevani.
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.
Kui loote kuupäevatabeli Excel, alustate ühest veerust, mille kuupäevade vahemik on külgnev. Seejärel saate Excel-töölehel luua täiendavaid veerge (nt Aasta, Kvartal, Kuu, Finantsaasta, Periood jne) Excel valemite abil või pärast tabeli kopeerimist andmemudelisse saate need luua 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 importisid kuupäevatabelit relationaalsest andmeallikast, 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-is 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 kunagi valemites töötanud kuupäeva- ja kellaajafunktsioonidega Excel on tõenäoline, et olete tuttav funktsioonidega Kuupäev ja kellaaeg. 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, seega on oluline neid mõista. Veergude loomiseks aasta, kvartali, finantskuu jne jaoks kasutame mitmeid neid funktsioone.
Märkus.: DAX-i kuupäeva- ja kellaajafunktsioonid pole samad mis ajateabefunktsioonid. Vt lisateavet teemast Ajateave Excel 2013 lisandmoodulis Power Pivot.
DAX sisaldab järgmisi kuupäeva- ja kellaajafunktsioone:
Valemites saate kasutada ka paljusid muid DAX-i funktsioone. Näiteks paljud siin kirjeldatud valemid kasutavad matemaatika- ja trigonomeetriafunktsioone(nt MOD ja TRUNC),loogikafunktsioone (nt IF)ja Tekstifunktsioone (nt FORMAT). Muude DAX-i funktsioonide kohta leiate lisateavet 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])
Selles valemis, nagu ka funktsiooni YEAR puhul, saame kasutada funktsiooni MONTH, et tagastada veerust Kuupäev kuu väärtus.
Kvartal
=INT(([kuu]+2)/3)
Selles valemis kasutame funktsiooni INT, et tagastada kuupäevaväärtus täisarvuna. Funktsiooni INT jaoks määratud argument on veeru Kuu väärtus, lisage 2 ja seejärel jagage see kolmega, et saada meie kvartal, 1 läbi 4.
Kuu nimetus
=FORMAT([kuupäev];"mmmm")
Selles valemis kasutame kuu nime toomiseks funktsiooni FORMAT, et teisendada arvväärtus veerust Kuupäev tekstiks. Määrame veeru Kuupäev esimese argumendina ja seejärel vormingu; soovime, et meie kuu nimi näitaks kõiki märke, seega kasutame sõna "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 toomiseks kasutame esmalt funktsiooni IF, et testida, kas kuu väärtus on väiksem kui 6 või sellega võrdne. 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.07.
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, seega algab tabeli Kalender esimese finantsaasta esimene täisnädal tegelikult 04.07.2010 ja jätkub tabeli Kalender viimase täisnädala jooksul. Kuigi see väärtus ise pole analüüsimisel kasulik, on vaja arvutada kasutamiseks muudes 28-päevastes perioodivalemites.
=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. Modi (matemaatika- ja trigonomeetriafunktsioonid) saab kasutada nii:
=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 olla vaja luua mitu seost või luua mitu kuupäevatabelit. Näiteks kui tabelis Müügi faktid on mitu kuupäevavälja (nt DateKey, ShipDate ja ReturnDate), võivad neil kõigil olla seosed tabeli Kalendri kuupäev väljaga Kuupäev, kuid ainult üks neist võib olla aktiivne seos. Sel juhul, kuna DateKey tähistab tehingu kuupäeva ja seega kõige olulisemat kuupäeva, on see kõige parem aktiivne seos. Teistel on passiivsed seosed.
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 kogumüügi liita mitte tehingukuupäeva, vaid lähetuskuupäeva järgi? Vajame seost tabeli Müük veeru "Lähetuskuupä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 tehingukuupäev on kõige olulisem, saab see aktiivse seose tabeliga Kalender.
Sel juhul on lähetuskuupäeval passiivne seos, seega peab lähetuskuupäevade põhjal andmete liitmiseks loodud mõõtvalem 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;
-
Tagastamiskuupäev – kuupäev ja kellaaeg, millal üks või mitu üksust tagastati.
Pidage meeles, et tehingukuupäevaga väli DateKey on kõige olulisem. Enamiku liitmised põhinevad nendel kuupäevadel, seega soovime kindlasti selle ja tabeli Kalender veeru Kuupäev vahelist seost. Kui me ei soovi luua passiivseid seoseid tabeli Kalender väljade "Lähetuskuupäev" ja "Tagastuskuupäev" ja "Kuupäev" vahel, mistõttu on vaja erimõõtevalemeid, saame luua lähetuskuupäeva ja tagastuskuupäeva jaoks täiendavaid kuupäevatabeleid. Seejärel saame nende vahel luua aktiivseid seoseid.
Selles näites oleme loonud teise kuupäevatabeli nimega ShipCalendar. See tähendab muidugi ka täiendavate kuupäevaveergude loomist ja kuna need kuupäevaveerud asuvad teises kuupäevatabelis, soovime neile nimesid eristada tabeli Kalender samadest veergudest. Näiteks oleme loonud veerud nimega ShipYear, ShipMonth, ShipQuarter 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 kuvatakse tabeli Müügi faktuuring veerg DateTimeKey. Kõik selle tabeli andmete liitmised peavad olema ainult päevatasemel, kasutades tabeli Kalendri kuupäev veerge (nt Aasta, Kuu, Kvartal jne). Väärtusesse kaasatud aeg 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 DateTimeKey ja tabeli Kalender veeru Kuupäev vahelise seose loomiseks saame luua tabeli Müük faktid uue arvutusliku veeru ja kasutada funktsiooni TRUNC, et kärpida kuupäeva- ja kellaajaväärtus veerus DateTimeKey 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.
Pärast kuupäevatabeli loomist andmemudelis saate alustada meetmetega, mis aitavad teil oma andmeid kõige rohkem ära kasutada. Mõned võivad olla sama lihtsad kui praeguse aasta müügisummade summeerimine ja teised võivad olla keerukamad, kus peate filtreerima kindlal kordumatute kuupäevade vahemikul. Lisateavet leiate artikliTest Power Pivot jaAjateabe funktsioonid.
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.
