Razumevanje in ustvarjanje datumskih tabel v dodatku Power Pivot v programu Excel

Datumske tabele v dodatku Power Pivot so bistvenega pomena za brskanje in izračunavanje podatkov v daljšem časovnem obdobju. V tem članku je podrobno opisano datumske tabele in kako jih ustvarite v dodatku Power Pivot. Ta članek opisuje zlasti:

• Zakaj je datumska tabela pomembna za brskanje in izračun podatkov po datumih in uri.
• Uporaba dodatka Power Pivot za dodajanje datumske tabele v podatkovni model.
• Ustvarjanje novih datumskih stolpcev, kot so Leto, Mesec in Obdobje v datumski tabeli.
• Kako ustvariti relacije med datumskimi tabelami in tabelami dejstev.
• Kako delati s časom.

Ta članek je namenjen uporabnikom, ki so novi uporabniki dodatka Power Pivot. Vendar pa je pomembno, da že dobro razumete uvoz podatkov, ustvarjanje odnosov ter ustvarjanje izračunanih stolpcev in mer.

V tem članku ni opisano, kako uporabljate funkcije DAX Time-Intelligence v formulah mer. Če želite več informacij o ustvarjanju mer s funkcijami časovnega obveščanja DAX, glejte Časovno obveščanje v dodatku Power Pivot v Excelu.

Vsebina

• Razumevanje datumskih tabel

• Dodajanje datumske tabele v podatkovni model

  • Uvoz iz relacijske zbirke podatkov

  • Ustvarite datumsko tabelo v Excelu

    • Nasveti: Ustvarite datumsko tabelo v Excelu in jo kopirajte v podatkovni model

• Dodajanje novih stolpcev z datumi v datumsko tabelo

  • Funkcije datuma in časa DAX

  • Primeri formul za koledarsko leto

    • Leto
    • Mesec
    • Četrtletje
    • Ime meseca

  • Primeri formul za poslovno leto

    • Poslovno leto
    • Poslovni mesec
    • Poslovno četrtletje

• Prazniki ali posebni datumi

• Koledar po meri - trinajst štiritedenskih obdobij

  • Teden
  • Pika
  • Obdobje Poslovno leto
  • Obdobje v fiskalnem letu

• Relacije

  • Več relacij
  • Neaktivni odnosi
  • Več datumskih tabel

• Lastnost datumske tabele

• Delo s časom

• Izboljšanje uporabnosti datumov

• Dodatek

  • Pretvarjanje datumov besedilnega podatkovnega tipa v datumski podatkovni tip
  • Dodatni viri

Razumevanje datumskih tabel

Skoraj vsa analiza podatkov vključuje brskanje in primerjavo podatkov po datumih in času. Morda boste na primer želeli sešteti zneske prodaje za preteklo poslovno četrtletje in nato primerjati te vsote z drugimi četrtletji ali pa boste morda želeli izračunati končno stanje za račun ob koncu meseca. V vsakem od teh primerov uporabljate datume kot način za združevanje in združevanje prodajnih transakcij ali stanj za določeno časovno obdobje.

Poročilo funkcije Power View

Datumska tabela lahko vsebuje veliko različnih predstavitev datumov in časov. Datumska tabela bo na primer pogosto vsebovala stolpce, kot so Poslovno leto, Mesec, Četrtletje ali Obdobje, ki jih lahko izberete kot polja na seznamu polj med rezanjem in filtriranjem podatkov v vrtilnih tabelah ali poročilih Power View.

Seznam polj »Power View«

Za datumske stolpce, kot so Leto, Mesec in Četrtletje, ki vključujejo vse datume v ustreznem obsegu, mora imeti datumska tabela vsaj en stolpec s sosednjim naborom datumov. To pomeni, da mora imeti ta stolpec eno vrstico za vsak dan za vsako leto, ki je vključeno v datumsko tabelo.

Če so na primer podatki, po katerih želite brskati, datumi od 1. februarja 2010 do 30. novembra 2012 in poročate o koledarskem letu, boste potrebovali datumsko tabelo z vsaj časovnim obdobjem od 1. januarja 2010 do 31. decembra 2012. Vsako leto v tabeli datumov mora vsebovati vse dneve za vsako leto. Če boste podatke redno osveževali z novejšimi podatki, boste morda želeli zagnati končni datum za leto ali dve, tako da vam ni treba posodabljati datumske tabele, ko bo čas minil.

Datumska tabela s sosednjim naborom datumov

Če poročate o poslovnem letu, lahko ustvarite tabelo datumov s sosednjim naborom datumov za vsako poslovno leto. Če se na primer poslovno leto začne 1. marca in imate podatke za poslovna leta 2010 do trenutnega datuma (na primer v poslovnem letu 2013), lahko ustvarite tabelo datumov, ki se začne 1. 3. 2009 in vključuje vsaj vsak dan v vsakem poslovnem letu do zadnjega datuma v proračunskem letu 2013.

Če boste poročali o koledarskem letu in poslovnem letu, vam ni treba ustvariti ločenih tabel datumov. Tabela z enim datumom lahko vključuje stolpce za koledarsko leto, poslovno leto in celo koledar trinajstih štiritedenskih obdobij. Pomembno je, da vaša datumska tabela vsebuje sosednji nabor datumov za vsa vključena leta.

Dodajanje datumske tabele v podatkovni model

Datumsko tabelo lahko dodate v podatkovni model na več načinov:

• Uvoz iz relacijske zbirke podatkov ali drugega vira podatkov.
• Ustvarite datumsko tabelo v Excelu in nato kopirajte ali povežite novo tabelo v dodatku Power Pivot.
• Uvoz iz Tržnice Microsoft Azure.

Oglejmo si vsakega od njih podrobneje.

Uvoz iz relacijske zbirke podatkov

Če uvozite nekatere ali vse podatke iz podatkovnega skladišča ali druge vrste relacijske zbirke podatkov, je verjetno, da že obstaja datumska tabela in relacije med njo in ostalimi podatki, ki jih uvažate. Datumi in oblika se bodo verjetno ujemali z datumi v vaših podatkih o dejstvih, datumi pa se verjetno začnejo v preteklosti in segajo daleč v prihodnost. Datumska tabela, ki jo želite uvoziti, je lahko zelo velika in vsebuje obseg datumov, ki presega tiste, ki jih boste morali vključiti v podatkovni model. Z naprednimi funkcijami filtriranja čarovnika za uvoz tabel v Power Pivotu lahko selektivno izberete le datume in določene stolpce, ki jih resnično potrebujete. Tako lahko znatno zmanjšate velikost delovnega zvezka in izboljšate učinkovitost delovanja.

Čarovnik za uvoz tabele

V večini primerov vam ne bo treba ustvariti dodatnih stolpcev, kot so Poslovno leto, Teden, Ime meseca itd., ker bodo že obstajali v uvoženi tabeli. Vendar pa boste v nekaterih primerih po uvozu datumske tabele v podatkovni model morda morali ustvariti dodatne datumske stolpce, odvisno od določene potrebe po poročanju. Na srečo je to enostavno narediti z uporabo jezika DAX. Več o ustvarjanju polj datumske tabele boste izvedeli pozneje. Vsako okolje je drugačno. Če niste prepričani, ali imajo vaši viri podatkov povezano datumsko ali koledarsko tabelo, se obrnite na skrbnika zbirke podatkov.

Ustvarite datumsko tabelo v Excelu

Datumsko tabelo lahko ustvarite v Excelu in jo nato kopirate v novo tabelo v podatkovnem modelu. To je res precej enostavno narediti in vam daje veliko prilagodljivosti.

Ko ustvarite datumsko tabelo v Excelu, začnete z enim stolpcem s sosednjim obsegom datumov. Nato lahko na Excelovem delovnem listu ustvarite dodatne stolpce, kot so Leto, Četrtletje, Mesec, Poslovno leto, Obdobje itd., Z Excelovimi formulami ali pa jih lahko po kopiranju tabele v podatkovni model ustvarite kot izračunane stolpce. Ustvarjanje dodatnih stolpcev z datumi v dodatku Power Pivot je opisano v razdelku Dodajanje novih stolpcev z datumi v tabelo datumov v nadaljevanju tega članka.

Nasveti: Ustvarite datumsko tabelo v Excelu in jo kopirajte v podatkovni model

1. V Excelu na prazen delovni list v celico A1 vnesite ime glave stolpca, da določite obseg datumov. Običajno bo to nekaj podobnega kot Date, DateTime ali DateKey.

2. V celico A2 vnesite začetni datum. Na primer, 1.1.2010.

3. Kliknite ročico za polnjenje in jo povlecite navzdol do številke vrstice, ki vsebuje končni datum. Na primer, 31.12.2016.
   

4. Izberite vse vrstice v stolpcu Datum (vključno z imenom glave v celici A1).

5. V skupini Slogi kliknite Oblikuj kot tabelo in nato izberite slog.

6. V pogovornem oknu Oblikuj kot tabelo kliknite V redu.
   

7. Kopirajte vse vrstice, vključno z glavo.

8. V dodatku Power Pivot na zavihku Osnovno kliknite Prilepi.

9. V polje Prilepiime tabeleza predogled> vnesite ime, na primer Datum ali Koledar. Potrdite polje Uporabi prvo vrstico kot glave stolpcevin kliknite V redu.
   
   Nova tabela datumov (v tem primeru z imenom Koledar) v dodatku Power Pivot je videti tako:
   

   Opomba

   Povezano tabelo lahko ustvarite tudi z dodajanjem v podatkovni model. Vendar pa je zaradi tega delovni zvezek po nepotrebnem velik, ker ima delovni zvezek dve različici datumske tabele; ena v Excelu in ena v dodatku Power Pivot.

Zdaj imate v podatkovnem modelu tabelo datumov. Z jezikom DAX lahko dodate nove stolpce z datumi, kot so Leto, Mesec itd.

Dodajanje novih stolpcev z datumi v datumsko tabelo

Datumska tabela z enim datumskim stolpcem, ki ima eno vrstico za vsak dan za vsako leto, je pomembna za določanje vseh datumov v časovnem obsegu. Prav tako je potreben za ustvarjanje relacije med tabelo dejstev in datumsko tabelo. Vendar ta stolpec z enim datumom z eno vrstico za vsak dan ni uporaben pri analizi po datumih v vrtilni tabeli ali poročilu Power View. Želite, da tabela datumov vključuje stolpce, ki vam pomagajo združiti podatke za obseg ali skupino datumov. Morda boste na primer želeli sešteti zneske prodaje po mesecih ali četrtletjih ali pa ustvarite merilo, ki izračuna medletno rast. V vsakem od teh primerov tabela datumov potrebuje stolpce za leto, mesec ali četrtletje, ki omogočajo združevanje podatkov za to obdobje.

Če ste datumsko tabelo uvozili iz relacijskega vira podatkov, morda že vključuje različne vrste datumskih stolpcev, ki jih želite. V nekaterih primerih boste morda želeli spremeniti nekatere od teh stolpcev ali ustvariti dodatne stolpce z datumi. To še posebej velja, če ustvarite lastno datumsko tabelo v Excelu in jo kopirate v podatkovni model. Na srečo je ustvarjanje novih stolpcev z datumi v dodatku Power Pivot precej enostavno s funkcijami datuma in časa v jeziku DAX.

Funkcije datuma in časa DAX

Če ste kdaj delali s funkcijami datuma in časa v Excelovih formulah, boste verjetno seznanjeni s funkcijami datuma in časa. Čeprav so te funkcije podobne svojim ustreznikom v Excelu, obstaja nekaj pomembnih razlik:

• Funkcije datuma in časa DAX uporabljajo podatkovni tip datuma in časa.
• Vrednosti iz stolpca lahko vzamejo kot argument.
• Uporabljajo se lahko za vrnitev in/ali manipuliranje z datumskimi vrednostmi.

Te funkcije se pogosto uporabljajo pri ustvarjanju datumskih stolpcev po meri v datumski tabeli, zato jih je pomembno razumeti. Številne od teh funkcij bomo uporabili za ustvarjanje stolpcev za Leto, Četrtletje, Fiskalni mesec in tako naprej.

DAX vključuje te funkcije datuma in časa:

• DATUM
• DATEVALUE
• NASLEDNJI DAN
• EDATE
• EOMONTH
• URA
• MINUTA
• MESEC
• NOW
• SEKUNDA
• TIME
• TIMEVALUE
• DANES
• WEEKDAY
• WEEKNUM
• LETO
• YEARFRAC

V formulah lahko uporabite tudi številne druge funkcije jezika DAX. Številne formule, opisane tukaj, na primer uporabljajo matematične in trigonometrične funkcije , kot sta MOD in TRUNC, logične funkcije , kot je IF, in besedilne funkcije , kot je FORMAT Če želite več informacij o drugih funkcijah jezika DAX, glejte razdelek Dodatni viri v nadaljevanju tega članka.

Primeri formul za koledarsko leto

V spodnjih primerih so opisane formule, ki se uporabljajo za ustvarjanje dodatnih stolpcev v datumski tabeli z imenom Koledar. En stolpec z imenom Datum že obstaja in vsebuje sosednji razpon datumov od 1. 1. 2010 do 31. 12. 2016.

Leto

=LETO([datum])

V tej formuli funkcija YEAR vrne leto iz vrednosti v stolpcu »Date«. Ker je vrednost v stolpcu »Datum« podatkovnega tipa datetime, funkcija YEAR ve, kako vrne leto iz nje.

Mesec

=MESEC([datum])

V tej formuli, podobno kot pri funkciji YEAR, lahko preprosto uporabimo funkcijo MONTH , da vrnemo vrednost meseca iz stolpca Date.

Četrtletje

=INT(([mesec]+2)/3)

V tej formuli uporabimo funkcijo INT , da vrnemo vrednost datuma kot celo število. Argument, ki ga določimo za funkcijo INT, je vrednost iz stolpca Month, dodajte 2 in jo nato delite s 3, da dobite četrtletje, od 1 do 4.

Ime meseca

=OBLIKA([datum];"mmmm")

V tej formuli, da dobimo ime meseca, uporabimo funkcijo FORMAT za pretvorbo številske vrednosti iz stolpca Datum v besedilo. Stolpec Datum določimo kot prvi argument in nato obliko; Želimo, da naše ime meseca prikazuje vse znake, zato uporabimo "MMMM". Naš rezultat izgleda takole:

Če želimo vrniti ime meseca, skrajšano na tri črke, bi v argumentu za obliko uporabili »mmm«.

Dan v tednu

=OBLIKA([datum];"ddd")

V tej formuli uporabimo funkcijo FORMAT, da dobimo ime dneva. Ker želimo le okrajšano ime dneva, v argumentu »oblika« navedemo »ddd«.

Vzorčna vrtilna tabela

Ko imate polja za datume, kot so leto, četrtletje, mesec itd., jih lahko uporabite v vrtilni tabeli ali poročilu. Na spodnji sliki sta na primer prikazani polje »SalesAmount« iz tabele »Podatki o prodaji« v argumentu »VREDNOSTI« in »Leto« in »Četrtletje« v tabeli dimenzij koledarja v argumentu »VRSTICE«. »ZnesekProdaje« je združen za kontekst leta in četrtletja.

Primeri formul za poslovno leto

Poslovno leto

=IF([mesec]<= 6,[leto],[leto]+1)

V tem primeru se poslovno leto začne 1. julija.

Nobena funkcija ne obstaja, s katero bi lahko iz datumske vrednosti izluščila proračunsko leto, saj se začetni in končni datum poslovnega leta pogosto razlikujeta od datuma koledarskega leta. Če želite pridobiti poslovno leto, najprej s funkcijo IF preverite, ali je vrednost za argument »Month« manjša ali enaka 6. Če je v drugem argumentu vrednost za argument »Mesec« manjša ali enaka 6, vrnite vrednost iz stolpca »Leto«. Če ni, vrnite vrednost iz argumenta »Year« in dodajte 1.

Vrednost končnega meseca poslovnega leta lahko določite tudi tako, da ustvarite mero, ki preprosto določi mesec. Na primer, FYE:=6. Nato se lahko namesto številke meseca sklicujete na ime mere. Na primer =IF([Mesec]<=[FYE],[Leto],[Leto]+1). To omogoča več prilagodljivosti pri sklicevanju na končni mesec poslovnega leta v več različnih formulah.

Poslovni mesec

=IF([Mesec]<= 6, 6+[Mesec], [Mesec]- 6)

V tej formuli določimo, če je vrednost za [mesec] manjša ali enaka 6, nato vzamemo 6 in prištejemo vrednost iz meseca, sicer odštejemo 6 od vrednosti iz [mesec].

Poslovno četrtletje

=INT(([FiscalMonth]+2)/3)

Formula, ki jo uporabljamo za proračunsko četrtletje, je skoraj enaka kot je bila za četrtletje v koledarskem letu. Edina razlika je, da namesto [Month] določimo [FiscalMonth].

Prazniki ali posebni datumi

Morda boste želeli vključiti stolpec z datumi, v katerem so določeni datumi prazniki ali kakšni drugi posebni datumi. Morda boste na primer želeli sešteti skupno prodajo za novo leto tako, da v vrtilno tabelo dodate polje »Praznik« kot razčlenjevalnik ali filter. V drugih primerih boste morda želeli izključiti te datume iz drugih datumskih stolpcev ali mere.

Vključevanje praznikov ali posebnih dni je precej preprosto. V Excelu lahko ustvarite tabelo z datumi, ki jih želite vključiti. Nato lahko kopirate ali uporabite »Dodaj v podatkovni model«, da ga dodate v podatkovni model kot povezano tabelo. V večini primerov ni treba ustvariti relacije med tabelo in tabelo koledarja. Vse formule, ki se sklicujejo nanj, lahko uporabijo funkcijo LOOKUPVALUE za vrnitev vrednosti.

Spodaj je primer tabele, ustvarjene v Excelu, ki vključuje praznike, ki jih želite dodati v datumsko tabelo:

Datum	Praznik
1/1/2010	Novo leto
11/25/2010	zahvalni dan
12/25/2010	božič
1. 1. 2011	Novo leto
11/24/2011	zahvalni dan
12/25/2011	božič
01.01.2012	Novo leto
22. 11. 2012	zahvalni dan
12/25/2012	božič
1/1/2013	Novo leto
11/28/2013	zahvalni dan
12/25/2013	božič
11/27/2014	zahvalni dan
12/25/2014	božič
1. 1. 2014	Novo leto
11/27/2014	zahvalni dan
12/25/2014	božič
1/1/2015	Novo leto
11/26/2014	zahvalni dan
12/25/2015	božič
01. 01. 2016	Novo leto
11/24/2016	zahvalni dan
12/25/2016	božič

V datumski tabeli ustvarimo stolpec z imenom »Praznik« in uporabimo takšno formulo:

=LOOKUPVALUE(prazniki[praznik],prazniki[datum],koledar[datum])

Podrobneje si oglejmo to formulo.

S funkcijo LOOKUPVALUE pridobite vrednosti iz stolpca »Prazniki« v tabeli »Prazniki«. V prvem argumentu določimo stolpec, kjer bo vrednost našega rezultata. V tabeli » Prazniki « določimo stolpec » Prazniki «, ker je to vrednost, ki jo želimo pridobiti.

=LOOKUPVALUE(prazniki[praznik],prazniki[datum],koledar[datum])

Nato določimo drugi argument, stolpec za iskanje, ki vsebuje datume, ki jih želimo poiskati. V tabeli »Prazniki« je stolpec »Datum« določen tako:

=LOOKUPVALUE(prazniki[praznik],prazniki[datum],koledar[datum])

Na koncu določimo stolpec v tabeli koledarja , ki vsebuje datume, ki jih želimo poiskati v tabeli »Prazniki «. To je seveda stolpec » Datum « v tabeli »Koledar «.

=LOOKUPVALUE(prazniki[praznik],prazniki[datum],koledar[datum])

Stolpec »Prazniki« vrne ime praznika za vsako vrstico z datumsko vrednostjo, ki se ujema z datumom v tabeli »Prazniki«.

Koledar po meri – trinajst štiritedenskih obdobij

Nekatere organizacije, kot so maloprodaja ali gostinstvo, pogosto poročajo o različnih obdobjih, na primer trinajstih štiritedenskih obdobjih. S trinajstimi štiritedenskimi koledarji je vsako obdobje 28 dni; zato vsako obdobje vsebuje štiri ponedeljeke, štiri torke, štiri srede in tako naprej. Vsako obdobje vsebuje enako število dni in po navadi prazniki padejo v isto obdobje vsako leto. Obdobje lahko začnete na kateri koli dan v tednu. Tako kot z datumi v koledarju ali proračunskem letu lahko s formulo DAX ustvarite dodatne stolpce z datumi po meri.

V spodnjih primerih se prvo celotno obdobje začne na prvo nedeljo v proračunskem letu. V tem primeru se poslovno leto začne 1. 7.

Teden

Ta vrednost nam da številko tedna, ki se začne s prvim popolnim tednom v proračunskem letu. Prvi popolni teden se v tem primeru začne v nedeljo, kar pomeni, da se prvi popolni teden v prvem proračunskem letu v tabeli »Koledar« dejansko začne 4. 7. 2010 in se nadaljuje do zadnjega popolnega tedna v tabeli koledarja. Čeprav ta vrednost sama po sebi ni tako uporabna v analizi, jo morate izračunati za uporabo v drugih formulah 28-dnevnih obdobij.

=INT([datum]-40356)/7)

Podrobneje si oglejmo to formulo.

Najprej ustvarimo formulo, ki vrne vrednosti iz stolpca »Datum« kot celo število, na primer:

=INT([datum])

Nato želimo poiskati prvo nedeljo v prvem proračunskem letu. Vidimo, da je 7/4/2010.

Odštejte 40356 (kar je celo število za 27.06.2010, zadnjo nedeljo od prejšnjega proračunskega leta) od te vrednosti, da dobite število dni od začetka dni v naši tabeli »Koledar«, na primer:

=INT([datum]-40356)

Nato rezultat delite s 7 (dni v tednu), na primer:

=INT(([datum]-40356)/7)

Rezultat je videti tako:

Pika

Obdobje v tem koledarju po meri vsebuje 28 dni in se vedno začne v nedeljo. Ta stolpec vrne številko obdobja, ki se začne s prvo nedeljo v prvem proračunskem letu.

=INT(([Teden]+3)/4)

Podrobneje si oglejmo to formulo.

Najprej ustvarimo formulo, ki vrne vrednost iz stolpca »Teden« kot celo število, na primer:

= INT([Teden])

Nato tej vrednosti dodajte 3, na primer:

=INT([Teden]+3)

Nato delite rezultat s 4:

=INT(([Teden]+3)/4)

Rezultat je videti tako:

Obdobje poslovnega leta

Ta vrednost vrne poslovno leto za obdobje.

=INT(([Obdobje]+12)/13)+2008

Podrobneje si oglejmo to formulo.

Najprej ustvarimo formulo, ki vrne vrednost iz obdobja in sešteje 12:

=([Obdobje]+12)

Rezultat delimo s 13, ker je v poslovnem letu trinajst obdobij od 28 dni:

=(([Obdobje]+12)/13)

Dodamo leto 2010, ker je to prvo leto v tabeli:

=(([Obdobje]+12)/13)+2010

Na koncu s funkcijo INT odstranimo kateri koli del rezultata in vrnemo celo število, če ga delimo s 13, takole:

= INT(([obdobje]+12)/13)+2010

Rezultat je videti tako:

Obdobje v proračunskem letu

Ta vrednost vrne številko obdobja, 1–13, z začetkom s prvim polnim obdobjem (ki se začne v nedeljo) v posameznem proračunskem letu.

=IF(MOD([obdobje]; 13), MOD([obdobje]; 13); 13)

Ta formula je nekoliko bolj zapletena, zato jo bomo najprej opisali v jeziku, ki ga bolje razumemo. Ta formula navaja, deli vrednost iz [obdobje] s 13, da dobi številko obdobja (1-13) v letu. Če je to število 0, potem vrni 13.

Najprej ustvarimo formulo, ki vrne preostanek vrednosti iz obdobja s 13. MOD (matematične in trigonometrične funkcije) lahko uporabimo tako:

= MOD([obdobje],13)

To nam večinoma da želeni rezultat, razen ko je vrednost za obdobje 0, ker ti datumi ne sodijo v prvih pet dni v vzorčni tabeli datumov koledarja. To lahko storimo s funkcijo IF. Če je naš rezultat 0, vrnemo 13, in sicer tako:

= IF(MOD([obdobje]; 13); MOD([obdobje]; 13); 13)

Rezultat je videti tako:

Vzorčna vrtilna tabela

Na spodnji sliki je prikazana vrtilna tabela s poljem »SalesAmount« iz tabele »Podatki o prodaji« v argumentu »VALUES« in polji »PeriodFiscalYear in PeriodInFiscalYear v tabeli dimenzij koledarskega datuma« v funkciji ROWS. »ZnesekProdaje« je združen za kontekst po poslovnem letu in 28-dnevnem obdobju v poslovnem letu.

Relacije

Ko v podatkovnem modelu ustvarite datumsko tabelo, lahko za brskanje po podatkih v vrtilnih tabelah in poročilih ter združevanje podatkov na podlagi stolpcev v tabeli datumskih dimenzij ustvarite relacijo med tabelo dejstev in podatki o transakciji ter datumsko tabelo.

Ker želite ustvariti odnos na osnovi datumov, morate ustvariti relacijo med stolpci z vrednostmi vrste podatkov »Datum/čas« (datum).

Za vsako datumsko vrednost v tabeli dejstev mora povezani stolpec za iskanje v datumski tabeli vsebovati ujemajoče se vrednosti. Na primer, vrstica (zapis transakcije) v tabeli »Podatki o prodaji« z vrednostjo 15.8.2012 12:00 AM v stolpcu »Ključ datuma« mora imeti ustrezno vrednost v povezanem stolpcu z datumi v datumski tabeli (imenovani »Koledar«). To je eden najpomembnejših razlogov, zakaj želite, da stolpec z datumi v datumski tabeli vključuje neprekinjen obseg datumov, ki vključuje vse morebitne datume v tabeli dejstev.

Če se pojavi napaka zaradi neujemanja vrste, ko poskušate ustvariti relacijo, je to verjetno zato, ker stolpec v tabeli z dejstvi ni podatkovnega tipa »Datum«. To se lahko zgodi, ko Power Pivot ne more samodejno pretvoriti podatkovnega tipa brez datuma (po navadi je to besedilni podatkovni tip) v podatkovni tip. Stolpec lahko še vedno uporabljate v tabeli dejstev, vendar boste morali podatke pretvoriti s formulo DAX v novem izračunanem stolpcu. Preberite članek »Pretvorba besedilnega podatkovnega tipa v datumski podatkovni tip« v nadaljevanju dodatka.

Več relacij

V nekaterih primerih bo morda treba ustvariti več relacij ali več datumskih tabel. Če je v tabeli podatkov o prodaji na primer več polj o datumu, na primer DateKey, ShipDate in ReturnDate, so lahko vsa polja v relaciji s poljem »Datum« v tabeli »Datum« koledarja, vendar je lahko le eno od teh polj aktivno relacijo. Ker v tem primeru DateKey predstavlja datum transakcije in s tem najpomembnejši datum, bi to najbolje služilo kot aktivni odnos. Drugi imajo neaktivne odnose.

Naslednja vrtilna tabela izračuna skupno prodajo po poslovnem letu in poslovnem četrtletju. Mera z imenom Skupna prodaja s formulo Skupna prodaja:=SUM([ZnesekProdaje]) je postavljena v VREDNOSTI, polji FiscalYear in FiscalQuarter iz tabele Koledarski datum pa sta postavljeni v VRSTICE.

Ta neposredna vrtilna tabela deluje pravilno, ker želimo sešteti skupno prodajo po datumu transakcije v DateKey. Merilo »Skupna prodaja« uporablja datume v »DateKey« in se sešteje po poslovnem letu in poslovnem četrtletju, ker obstaja povezava med »DateKey« v tabeli »Sales« in stolpcem »Date« v tabeli »Datum koledarja«.

Neaktivni odnosi

Kaj pa, če bi želeli sešteti našo skupno prodajo ne po datumu transakcije, ampak po datumu odpreme? Potrebujemo relacijo med stolpcem »ShipDate« v tabeli »Sales« in stolpcem »Date« v tabeli »Calendar«. Če tega odnosa ne ustvarimo, naše združevanje vedno temelji na datumu transakcije. Vendar pa imamo lahko več relacij, čeprav je lahko aktiven samo eden, in ker je datum transakcije najpomembnejši, dobi aktivno relacijo s tabelo koledarja.

V tem primeru ima ShipDate neaktivno relacijo, zato mora vsaka formula mere, ustvarjena za združevanje podatkov na podlagi datumov odpreme, določiti neaktivno relacijo s funkcijo USERELATIONSHIP .

Ker je na primer neaktivna relacija med stolpcem »ShipDate« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar«, lahko ustvarimo mero, ki sešteje skupno prodajo po datumu pošiljanja. S takšno formulo določimo relacijo, ki jo je treba uporabiti:

Skupna prodaja po datumu odpreme:=CALCULATE(SUM(Prodaja[ZnesekProdaje]), USERELATIONSHIP(Prodaja[DatumPošiljanja], Koledar[Datum]))

Ta formula preprosto navaja: Izračunajte vsoto za SalesAmount, vendar filtrirajte z relacijo med stolpcem »ShipDate« v tabeli »Sales« in stolpcem »Date« v tabeli »Calendar«.

Če zdaj ustvarimo vrtilno tabelo in v VREDNOSTI vstavimo mero Skupna prodaja po datumu odpreme, v Poslovno leto in Poslovno četrtletje pa v VRSTICE, vidimo enako skupno vsoto, vendar so vsi drugi zneski vsote za poslovno leto in poslovno četrtletje drugačni, ker temeljijo na datumu odpreme in ne na datumu transakcije.

Uporaba neaktivnih relacij vam omogoča, da uporabite le eno datumsko tabelo, vendar zahteva, da se vse mere (na primer Skupna prodaja po datumu pošiljanja) sklicujejo na neaktivno relacijo v svoji formuli. Obstaja še ena možnost, to je, da uporabite več datumskih tabel.

Več datumskih tabel

Drug način za delo z več datumskimi stolpci v tabeli dejstev je, da ustvarite več datumskih tabel in ustvarite ločene aktivne relacije med njimi. Oglejmo si še enkrat primer tabele Prodaja. Imamo tri stolpce z datumi, za katere bomo morda želeli združiti podatke:

• DateKey z datumom prodaje za vsako transakcijo.
• Datum pošiljanja – z datumom in časom, ko so bili prodani izdelki poslani stranki.
• Datum vračila – z datumom in časom, ko je bil prejet en ali več vrnjenih izdelkov.

Ne pozabite, da je polje »DateKey« z datumom transakcije najpomembnejše. Večino naših združevanj bomo naredili na podlagi teh datumov, zato bomo zagotovo želeli relacijo med njim in stolpcem Datum v tabeli Koledar. Če ne želimo ustvariti neaktivnih relacij med »ShipDate« in »ReturnDate« ter poljem »Date« v tabeli »Calendar«, kar zahteva posebne formule mer, lahko ustvarimo dodatne datumske tabele za datum odpreme in datum vrnitve. Nato lahko med njimi ustvarimo aktivne odnose.

V tem primeru smo ustvarili drugo datumsko tabelo z imenom ShipCalendar. To seveda pomeni tudi ustvarjanje dodatnih stolpcev z datumi, in ker so ti stolpci z datumi v drugi datumski tabeli, jih želimo poimenovati tako, da se razlikujejo od istih stolpcev v tabeli koledarja. Ustvarili smo na primer stolpce z imeni ShipYear, ShipMonth, ShipQuarter in tako naprej.

Če ustvarimo vrtilno tabelo in v VREDNOSTI vstavimo mero Skupna prodaja, v VRSTICE pa ShipFiscalYear in ShipFiscalQuarter, vidimo enake rezultate, kot smo jih videli, ko smo ustvarili neaktivno relacijo in posebno izračunano polje »Skupna prodaja po datumu pošiljanja«.

Vsak od teh pristopov zahteva skrben premislek. Če uporabljate več relacij z eno datumsko tabelo, boste morda morali ustvariti posebne mere, ki prenašajo neaktivne relacije s funkcijo USERELATIONSHIP. Po drugi strani pa je lahko ustvarjanje več tabel datumov na seznamu polj zmedeno in ker imate v podatkovnem modelu več tabel, bo potrebno več pomnilnika. Eksperimentirajte s tem, kar vam najbolj ustreza.

Lastnost datumske tabele

Lastnost Tabela datumov nastavi metapodatke, ki so potrebni za pravilno delovanje Time-Intelligence funkcij, kot so TOTALYTD, PREVIOUSMONTH in DATESBETWEEN Ko se izračun zažene z eno od teh funkcij, mehanizem formul dodatka Power Pivot ve, kam naj gre, da dobi datume, ki jih potrebuje.

Ko nastavite lastnost Datumska tabela, določite datumsko tabelo in datumski stolpec podatkovnega tipa Datum (datum in čas).

Nasveti: Nastavitev lastnosti »Datumska tabela«

1. V oknu PowerPivot izberite tabelo Koledar .
2. Na zavihku Načrt kliknite Označi kot datumsko tabelo.
3. V pogovornem oknu Označi kot datumsko tabelo izberite stolpec z enoličnimi vrednostmi in podatkovnim tipom Datum.

Delo s časom

Vse datumske vrednosti s podatkovnim tipom »Datum« v Excelu ali strežniku SQL Server so dejansko številka. V to številko so vključene številke, ki se nanašajo na čas. V mnogih primerih je ta čas za vsako vrsto polnoč. Če ima na primer polje »DateTimeKey« v tabeli »Prodaja« vrednosti, kot so 19.10.2010 12:00:00, to pomeni, da so vrednosti natančne na ravni dneva. Če je v vrednosti polja »DateTimeKey« vključen čas, na primer 19.10.2010 8:44:00, to pomeni, da so vrednosti natančne glede na minuto. Vrednosti so lahko tudi natančne na ravni ure ali celo na ravni sekund. Raven natančnosti časovne vrednosti bo pomembno vplivala na to, kako ustvarite datumsko tabelo in razmerja med njo in tabelo dejstev.

Ugotoviti morate, ali boste podatke združili na dnevno raven natančnosti ali na časovno raven natančnosti. Z drugimi besedami, morda boste želeli uporabiti stolpce v datumski tabeli, kot so Jutro, Popoldne ali Ura, kot polja s časom in datumom v območjih Vrstica, Stolpec ali Filter vrtilne tabele.

Če nameravate združiti podatke na časovno raven, bo datumska tabela potrebovala stolpec z datumom z vključenim časom. Pravzaprav bo potreboval stolpec z datumom z eno vrstico za vsako uro ali morda celo vsako minuto vsakega dne, za vsako leto v časovnem obdobju. Če želite ustvariti relacijo med stolpcem »DateTimeKey« v tabeli dejstev in stolpcem z datumom v datumski tabeli, morate imeti ujemajoče se vrednosti. Kot si lahko predstavljate, če vključite veliko let, lahko to naredi zelo veliko tabelo datumov.

V večini primerov pa želite združiti podatke samo na dan. Z drugimi besedami, stolpce, kot so Leto, Mesec, Teden ali Dan v tednu, boste uporabili kot polja v vrstici, stolpcu ali območju filtra vrtilne tabele. V tem primeru mora stolpec z datumom v datumski tabeli vsebovati le eno vrstico za vsak dan v letu, kot smo opisali prej.

Če stolpec z datumom vključuje časovno raven natančnosti, vendar boste združili le na raven dneva, da ustvarite relacijo med tabelo dejstev in datumsko tabelo, boste morda morali spremeniti tabelo dejstev tako, da ustvarite nov stolpec, ki prireže vrednosti v stolpcu z datumom na vrednost dneva. Z drugimi besedami, pretvorite vrednost, kot je 19.10.2010 8:44:00 v 19.10.2010 12:00:00. Nato lahko ustvarite relacijo med tem novim stolpcem in stolpcem z datumom v datumski tabeli, ker se vrednosti ujemajo.

Poglejmo primer. Na tej sliki je prikazan stolpec »DateTimeKey« v tabeli »Prodaja«. Vse združevanje podatkov v tej tabeli mora biti samo na ravni dneva, in sicer z uporabo stolpcev v tabeli Koledarski datum, kot so Leto, Mesec, Četrtletje itd. Čas, vključen v vrednost, ni pomemben, le dejanski datum.

Ker nam teh podatkov ni treba analizirati do časovne ravni, ne potrebujemo, da stolpec »Datum« v tabeli »Datum koledarja« vključuje eno vrstico za vsako uro in vsako minuto vsakega dne v vsakem letu. Torej, stolpec Datum v naši datumski tabeli izgleda takole:

Če želite ustvariti relacijo med stolpcem »DateTimeKey« v tabeli »Prodaja« in stolpcem »Datum« v tabeli »Koledar«, lahko v tabeli »Podatki« »Prodaja« ustvarimo nov izračunani stolpec in s funkcijo TRUNC ter prirežemo vrednost datuma in časa v stolpcu »DateTimeKey« v datumsko vrednost, ki se ujema z vrednostmi v stolpcu »Datum« v tabeli »Koledar«. Naša formula izgleda takole:

=TRUNC([DatumČasKljuč];0)

Tako dobimo nov stolpec (poimenovali smo DateKey) z datumom iz stolpca »DateTimeKey« in časom 12:00:00 za vsako vrstico:

Zdaj lahko ustvarimo relacijo med tem novim stolpcem (DateKey) in stolpcem Date v tabeli Koledar.

Podobno lahko v tabeli »Prodaja« ustvarimo izračunani stolpec, ki zmanjša časovno natančnost v stolpcu »DateTimeKey« na raven natančnosti ure. V tem primeru funkcija TRUNC ne bo delovala, vendar lahko še vedno uporabimo druge funkcije datuma in časa DAX za ekstrahiranje in ponovno združevanje nove vrednosti na raven natančnosti na uro. Uporabimo lahko takšno formulo:

= DATUM (LETO([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) + TIME (HOUR([DateTimeKey]), 0, 0)

Naš novi stolpec izgleda takole:

Če ima stolpec »Datum« v tabeli »Datum« vrednosti do urne ravni natančnosti, lahko nato ustvarimo relacijo med njimi.

Izboljšanje uporabnosti datumov

Številni stolpci z datumi, ki jih ustvarite v datumski tabeli, so potrebni za druga polja, vendar v resnici niso tako uporabni za analizo. Polje »DateKey« v tabeli »Prodaja«, na katero smo se sklicevali in prikazali v tem članku, je na primer pomembno, ker je za vsako transakcijo zabeleženo, da se je ta transakcija zgodila na določen datum in uro. Toda z vidika analize in poročanja ni tako uporaben, ker ga ne moremo uporabiti kot vrstico, stolpec ali polje filtra v vrtilni tabeli ali poročilu.

Podobno je v našem primeru stolpec »Datum« v tabeli »Koledar« zelo uporaben, pravzaprav je kritičen, vendar ga ne morete uporabiti kot dimenzijo v vrtilni tabeli.

Da bodo tabele in stolpci v njih čim bolj uporabni ter da bi bilo krmarjenje po seznamih polj vrtilne tabele ali poročila Power View preprostejše, je pomembno, da skrijete nepotrebne stolpce v odjemalskih orodjih. Morda boste želeli skriti tudi nekatere tabele. V tabeli »Prazniki«, ki smo jo pokazali prej, so datumi praznikov, ki so pomembni za določene stolpce v tabeli »Koledar«, vendar stolpcev »Datum« in »Prazniki« v tabeli »Prazniki« ne morete uporabiti kot polj v vrtilni tabeli. Tudi tukaj lahko zaradi preprostejšega krmarjenja po seznamih polj skrijete celotno tabelo »Prazniki«.

Drug pomemben vidik dela z datumi so pravila poimenovanja. Tabele in stolpce v dodatku Power Pivot lahko poimenujete, kakor koli želite. Če boste delovni zvezek dali v skupno rabo z drugimi uporabniki, upoštevajte, da boste z dobro konvencijo o poimenovanju lažje prepoznali tabele in datume, ne le na seznamu polj, ampak tudi v dodatku Power Pivot in formulah jezika DAX.

Ko imate datumsko tabelo v podatkovnem modelu, lahko začnete ustvarjati ukrepe, s katerimi boste lahko kar najbolje izkoristili svoje podatke. Nekatere funkcije so lahko tako preproste, kot je seštevanje skupnih zneskov prodaje za tekoče leto, druge pa so lahko bolj zapletene, saj jih morate filtrirati po določenem obsegu enoličnih datumov. Več informacij je na voljo v merah v orodju Power Pivot in funkcijah podatkov o času.

Dodatek

Pretvarjanje besedilnega podatkovnega tipa Datumi v datumski podatkovni tip

V nekaterih primerih lahko tabela z dejstvi s podatki o transakciji vsebuje datume besedilnega podatkovnega tipa. To pomeni, da datum, ki je prikazan kot 2012-12-04T11:47:09, v resnici sploh ni datum, ali vsaj ni vrsta datuma, ki ga Power Pivot lahko razume. V resnici je le besedilo, ki se bere kot datum. Če želite ustvariti relacijo med stolpcem z datumom v tabeli z datumi in stolpcem z datumom v tabeli z datumi, morata biti oba stolpca podatkovnega tipa » Datum« .

Ko poskušate spremeniti podatkovni tip za stolpec z datumi, ki so besedilni podatkovni tip, v podatkovni tip »datum«, lahko Power Pivot običajno interpretira datume in ga samodejno pretvori v podatkovni tip »true«. Če Power Pivot ne more izvesti pretvorbe podatkovnega tipa, se prikaže sporočilo o napaki zaradi neujemanja tipa.

Še vedno pa lahko pretvorite datume v podatkovni tip True. Ustvarite lahko nov izračunan stolpec in uporabite formulo DAX, da razčlenite leto, mesec, dan, čas itd. iz besedilnih nizov in jih nato znova spojite tako, da ga Power Pivot lahko prebere kot pravi datum.

V tem primeru smo v Power Pivot uvozili tabelo z vrednostmi z imenom »Prodaja«. Vsebuje stolpec z imenom »DateTime«. Vrednosti so prikazane tako:

Če si v skupini »Oblikovanje« ogledate podatkovni tip, je zavihek »Osnovno« dodatka Power Pivot prikazan, da gre za besedilni podatkovni tip.

V datumski tabeli ni mogoče ustvariti relacije med stolpcema »DateTime« in »Datum«, ker se podatkovni tipi ne ujemajo. Če poskusimo podatkovni tip spremeniti na »Datum«, se prikaže sporočilo o napaki zaradi neujemanja vrste:

V tem primeru Power Pivot ni mogel pretvoriti podatkovnega tipa iz besedila v datum. Ta stolpec lahko še vedno uporabljamo, vendar če ga želimo uvrstiti v podatkovni tip True, moramo ustvariti nov stolpec, ki razčleni besedilo in ga znova ustvari v vrsto podatkov, ki jo Power Pivot določi kot podatkovni tip Date.

Ne pozabite iz razdelka Delo s časom na začetku tega članka; Datume v tabeli dejstev pretvorite v natančnost za dnevni čas, razen če je treba natančno določiti količino analize glede na dan v dnevu. Zato želimo, da so vrednosti v novem stolpcu na ravni natančnosti po dnevih (brez časa). Vrednosti v stolpcu »DateTime« lahko pretvorite v datumske vrste podatkov in odstranite časovno raven natančnosti s to formulo:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

S tem dobite nov stolpec (v tem primeru z imenom »Datum«). Power Pivot zazna celo vrednosti kot datume in samodejno nastavi podatkovni tip na »Datum«.

Če želimo ohraniti časovno raven natančnosti, preprosto razširimo formulo tako, da vključuje ure, minute in 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))

Zdaj, ko imamo stolpec z datumom podatkovnega tipa »Datum«, lahko ustvarimo relacijo med njim in datumskim stolpcem v datumu.

Dodatni viri

Datumi v dodatku Power Pivot

Izračuni v dodatku Power Pivot

Vodnik za hitri začetek: naučite se osnov jezika DAX v 30 minutah

Sklic na izraze za analizo podatkov

Središče virov za jezik DAX