Datotabeller i Power Pivot er avgjørende for å bla gjennom og beregne data over tid. Denne artikkelen gir en grundig forståelse av datotabeller og hvordan du oppretter dem i Power Pivot. Denne artikkelen beskriver spesielt:
- Hvorfor en datotabell er viktig for å bla gjennom og beregne data etter datoer og klokkeslett.
- Slik bruker du Power Pivot til å legge til en datatabell i datamodellen.
- Slik oppretter du nye datokolonner, for eksempel år, måned og periode, i en datotabell.
- Slik oppretter du relasjoner mellom datotabeller og faktatabeller.
- Hvordan arbeide med tid.
Denne artikkelen er ment for brukere som ikke har brukt Power Pivot før. Det er imidlertid viktig å ha en god forståelse av import av data, opprettelse av relasjoner og oppretting av beregnede kolonner og mål.
Denne artikkelen beskriver ikke hvordan du bruker DAX-Time-Intelligence funksjoner i målformler. Hvis du vil ha mer informasjon om hvordan du oppretter mål med funksjoner for tidsintelligens i DAX, kan du se Tidsintelligens i Power Pivot i Excel.
Obs!
I Power Pivot er navnene «mål» og «beregnet felt» synonyme. Vi bruker navnemålet i denne artikkelen. Hvis du vil ha mer informasjon, kan du se Mål i Power Pivot.
Innhold
Forstå datotabeller
De aller fleste dataanalyser innebærer å bla gjennom og sammenligne data over datoer og klokkeslett. Det kan for eksempel hende at du vil summere salgsbeløpene for det siste regnskapskvartalet, og deretter sammenligne totalene med andre kvartaler, eller du vil beregne en sluttsaldo ved slutten av måneden for en konto. I hvert av disse tilfellene bruker du datoer som en metode for å gruppere og aggregere salgstransaksjoner eller saldoer for en bestemt tidsperiode.
Power View-rapport
En datotabell kan inneholde mange ulike representasjoner av datoer og klokkeslett. En datotabell har for eksempel ofte kolonner som Regnskapsår, Måned, Kvartal eller Periode som du kan velge som felt fra en feltliste når du deler opp og filtrerer dataene i pivottabeller eller Power View-rapporter.
Power View-feltliste
For at datokolonner som År, Måned og Kvartal skal inkludere alle datoene innenfor det respektive området, må datotabellen ha minst én kolonne med et sammenhengende sett av datoer. Det vil si at denne kolonnen må ha én rad for hver dag for hvert år som er inkludert i datotabellen.
Hvis for eksempel dataene du vil bla gjennom har datoer fra 1. februar 2010 til 30. november 2012, og du rapporterer for et kalenderår, vil du ha en datotabell med et datointervall fra minst 1. januar 2010 til 31. desember 2012. Hvert år i datotabellen må inneholde alle dagene for hvert år. Hvis du kommer til å oppdatere dataene regelmessig med nyere data, vil du kanskje kjøre sluttdatoen ut med ett år eller to, slik at du ikke trenger å oppdatere datotabellen etter hvert som tiden går.
Datotabell med et sammenhengende sett av datoer
Hvis du rapporterer for et regnskapsår, kan du opprette en datotabell med et sammenhengende sett av datoer for hvert regnskapsår. Hvis for eksempel regnskapsåret begynner 1. mars, og du har data for regnskapsårene 2010 og frem til dagens dato (for eksempel i regnskapsåret 2013), kan du opprette en datotabell som begynner 01.03.2009 og inneholder minst hver dag i hvert regnskapsår til og med den siste datoen i regnskapsåret 2013.
Hvis du vil rapportere for både kalenderår og regnskapsår, trenger du ikke å opprette separate datotabeller. Én enkelt datotabell kan inneholde kolonner for et kalenderår, regnskapsår og til og med en kalender med tretten fire ukers periode. Det viktigste er at datotabellen inneholder et sammenhengende sett med datoer for alle år, inkludert.
Legge til en datotabell i datamodellen
Du kan legge til en datotabell i datamodellen på flere måter:
- Importer fra en relasjonsdatabase eller annen datakilde.
- Opprett en datotabell i Excel, og kopier eller koble deretter til en ny tabell i Power Pivot.
- Importer fra Microsoft Azure Marketplace.
La oss se nærmere på hver av disse.
Importere fra en relasjonsdatabase
Hvis du importerer noen av eller alle dataene fra et datalager eller en annen type relasjonsdatabase, kan det hende at det allerede finnes en datotabell og relasjoner mellom den og resten av dataene du importerer. Datoene og formatet vil sannsynligvis samsvare med datoene i faktadataene, og datoene begynner sannsynligvis langt inn i fremtiden. Datotabellen du vil importere, kan være svært stor og inneholde et datoområde utover det du må ta med i datamodellen. Du kan bruke avanserte filterfunksjoner i tabellimportveiviseren i Power Pivot til å velge bare datoene og kolonnene du virkelig trenger. Dette kan redusere størrelsen på arbeidsboken betydelig og forbedre ytelsen.
Veiviser for tabellimport
I de fleste tilfeller trenger du ikke å opprette noen ekstra kolonner som regnskapsår, uke, månedsnavn og så videre, fordi de allerede finnes i den importerte tabellen. Men i noen tilfeller, etter at du har importert datotabellen til datamodellen, kan det hende at du må opprette flere datokolonner, avhengig av et bestemt rapporteringsbehov. Dette er heldigvis enkelt å gjøre ved hjelp av DAX. Du kommer til å lære mer om oppretting av datotabellfelt senere. Hvert miljø er forskjellig. Hvis du er usikker på om datakildene har en relatert dato- eller kalendertabell, kan du snakke med databaseadministratoren.
Opprette en datotabell i Excel
Du kan opprette en datotabell i Excel og deretter kopiere den til en ny tabell i datamodellen. Dette er egentlig ganske enkelt å gjøre, og det gir deg mye fleksibilitet.
Når du oppretter en datotabell i Excel, starter du med én enkelt kolonne med et sammenhengende datoområde. Du kan deretter opprette flere kolonner, for eksempel år, kvartal, måned, regnskapsår, periode og så videre, i Excel-regnearket ved å bruke Excel-formler, eller når du har kopiert tabellen til datamodellen, kan du opprette dem som beregnede kolonner. Oppretting av flere datokolonner i Power Pivot beskrives i delen Legge til nye datokolonner i datotabellen senere i denne artikkelen.
Fremgangsmåte: Opprette en datotabell i Excel og kopiere den til datamodellen
I et tomt regneark i celle A1 i Excel skriver du inn et kolonneoverskriftsnavn for å identifisere et datoområde. Vanligvis vil dette være noe som dato, dato/klokkeslett eller datonøkkel.
Skriv inn en startdato i celle A2. Eksempel: 01.01.2010.
Klikk fyllhåndtaket, og dra det ned til et radnummer som inneholder en sluttdato. Eksempel: 31.12.2016.
Merk alle radene i Dato-kolonnen (inkludert topptekstnavnet i celle A1).
Klikk Formater som tabell i Stiler-gruppen, og velg deretter en stil.
Klikk OK i dialogboksen Formater som tabell.
Kopier alle rader, inkludert toppteksten.
Klikk Lim inn i PowerPivot på Hjem-fanen.
I Forhåndsvisning> avtabellnavn skriver du inn et navn, for eksempel Date eller Calendar. La Bruk første rad som kolonneoverskriftervære avmerket, og klikk deretter OK.
Den nye datotabellen (kalt Calendar i dette eksemplet) i Power Pivot ser slik ut:
Obs!
Du kan også opprette en koblet tabell ved hjelp av Legg til i datamodell. Dette gjør imidlertid arbeidsboken unødvendig stor fordi arbeidsboken inneholder to versjoner av datotabellen. én i Excel og én i Power Pivot.
Obs!
Navnet på datoen er et nøkkelord i Power Pivot. Hvis du gir tabellen du oppretter i Power Pivot, navnet Date, må du omslutte tabellnavnet med enkle anførselstegn i alle DAX-formler som refererer til den i et argument. Alle eksempelbildene og formlene i denne artikkelen refererer til en datatabell opprettet i PowerPivot kalt Calendar.
Du har nå en datotabell i datamodellen. Du kan legge til nye datokolonner, for eksempel år, måned og så videre, ved å bruke DAX.
Legge til nye datokolonner i datotabellen
En datotabell med én enkelt datokolonne som har én rad for hver dag for hvert år, er viktig for å definere alle datoene i et datoområde. Det er også nødvendig for å opprette en relasjon mellom faktatabellen og datotabellen. Denne ene datokolonnen med én rad for hver dag er imidlertid ikke nyttig når du analyserer etter datoer i en pivottabell eller Power View-rapport. Du vil at datotabellen skal inkludere kolonner som lar deg aggregere dataene for et område eller en gruppe med datoer. Du kan for eksempel summere salgsbeløp etter måned eller kvartal, eller du kan opprette et mål som beregner vekst fra år til år. I hvert av disse tilfellene trenger datotabellen kolonner for år, måned eller kvartal, slik at du kan aggregere dataene for denne perioden.
Hvis du har importert datotabellen fra en relasjonsdatakilde, kan det hende den allerede inneholder de ulike typene datokolonner du vil bruke. I noen tilfeller ønsker du kanskje å endre noen av disse kolonnene eller opprette flere datokolonner. Dette gjelder spesielt hvis du oppretter din egen datotabell i Excel og kopierer den inn i datamodellen. Heldigvis er det ganske enkelt å opprette nye datokolonner i Power Pivot med dato- og klokkeslettfunksjoner i DAX.
Tips
Hvis du ikke har arbeidet med DAX ennå, er Hurtiginnføring et flott sted å starte opplæringen : Lær det grunnleggende om DAX på 30 minutter på Office.com.
Dato- og klokkeslettfunksjoner i DAX
Hvis du noensinne har arbeidet med dato- og klokkeslettfunksjoner i Excel-formler, er du sannsynligvis kjent med dato- og klokkeslettfunksjonene. Selv om disse funksjonene ligner på de tilsvarende funksjonene i Excel, er det noen viktige forskjeller:
- Dato- og klokkeslettfunksjonene i DAX bruker datatypen datetime.
- De kan ta verdier fra en kolonne som argument.
- De kan brukes til å returnere og/eller manipulere datoverdier.
Disse funksjonene brukes ofte når du oppretter egendefinerte datokolonner i en datotabell, så de er viktige å forstå. Vi bruker mange av disse funksjonene til å opprette kolonner for År, Kvartal, Regnskapsmåned og så videre.
Obs!
Dato- og klokkeslettfunksjoner i DAX er ikke det samme som funksjoner for tidsintelligens. Mer informasjon om tidsintelligens i Power Pivot i Excel.
DAX inneholder følgende dato- og klokkeslettfunksjoner:
- DATO
- DATOVERDI
- NESTE DAG
- DAG.ETTER (funksjon)
- EOMONTH
- TIME
- MINUTT
- MÅNED
- NÅ
- SEKUND
- TIME
- TIDSVERDI
- I DAG
- HVERDAG
- WEEKNUM
- ÅR
- ÅRDEL
Det finnes mange andre DAX-funksjoner du kan bruke i formlene også. Mange av formlene som beskrives her, bruker for eksempel matematiske og trigonometriske funksjoner som REST og TRUNC, logiske funksjoner som HVIS, og tekstfunksjoner som FORMAT Hvis du vil ha mer informasjon om andre DAX-funksjoner, kan du se delen Flere ressurser senere i denne artikkelen.
Formeleksempler for et kalenderår
Følgende eksempler beskriver formler som brukes til å opprette ekstra kolonner i en datotabell kalt Calendar. Én kolonne kalt Dato finnes allerede og inneholder et sammenhengende datoområde fra 01.01.2010 til 31.12.2016.
År
=ÅR([dato])
I denne formelen returnerer ÅR-funksjonen året fra verdien i Dato-kolonnen. Fordi verdien i Dato-kolonnen er av datetime-datatypen, vet ÅR-funksjonen hvordan den returnerer året fra den.
Måned
=MÅNED([dato])
I denne formelen, omtrent som med ÅR-funksjonen, kan vi ganske enkelt bruke MÅNED-funksjonen til å returnere en månedsverdi fra Dato-kolonnen.
Kvartal
=INT(([Måned]+2)/3)
I denne formelen bruker vi funksjonen HELTALL til å returnere en datoverdi som et heltall. Argumentet vi angir for funksjonen HELTALL er verdien fra Måned-kolonnen, legg til 2 og del det deretter med 3 for å få kvartalet 1 til 4.
Månedsnavn
=FORMAT([dato],"mmmm")
For å få månedsnavnet i denne formelen bruker vi FORMAT-funksjonen til å konvertere en numerisk verdi fra datokolonnen til tekst for å få månedsnavnet. Vi angir datokolonnen som det første argumentet, og deretter formatet; Vi vil at månedsnavnet skal vise alle tegnene, så vi bruker «mmmm». Vårt resultat ser slik ut:
Hvis vi vil returnere månedsnavnet forkortet til tre bokstaver, bruker vi «mmm» i formatargumentet.
Dag i uke
=FORMAT([dato],"ddd")
I denne formelen bruker vi FORMAT-funksjonen til å få navnet på dagen. Fordi vi bare vil ha et forkortet dagnavn, angir vi «ddd» i formatargumentet.
Eksempel på pivottabell
Når du har felt for datoer som år, kvartal, måned og så videre, kan du bruke dem i en pivottabell eller rapport. Bildet nedenfor viser for eksempel SalesAmount-feltet fra faktatabellen Salg i VERDIER og År og Kvartal fra dimensjonstabellen Calendar i RADER. SalesAmount aggregeres for år og kvartal.
Formeleksempler for et regnskapsår
Regnskapsår
=HVIS([Måned]<= 6,[År],[År]+1)
I dette eksemplet begynner regnskapsåret 1. juli.
Det finnes ingen funksjon som kan trekke ut et regnskapsår fra en datoverdi, fordi start- og sluttdatoene for et regnskapsår ofte er forskjellige fra de i et kalenderår. For å få regnskapsåret bruker vi først en HVIS-funksjon til å teste om verdien for Måned er mindre enn eller lik 6. Hvis verdien for Måned er mindre enn eller lik 6 i det andre argumentet, returneres verdien fra År-kolonnen. Hvis ikke, returnerer du verdien fra Year og legger til 1.
En annen måte å angi en verdi for sluttmåneden for regnskapsåret på er å opprette et mål som ganske enkelt angir måneden. For eksempel FYE:=6. Du kan deretter referere til målnavnet i stedet for månedsnummeret. For eksempel =HVIS([Måned]<=[FYE],[År],[År]+1). Dette gir mer fleksibilitet når du refererer til regnskapsårets sluttmåned i flere forskjellige formler.
Regnskapsmåned
=HVIS([Måned]<= 6, 6+[Måned], [Måned]- 6)
I denne formelen angir vi at hvis verdien for [Måned] er mindre enn eller lik 6, tar du 6 og legger til verdien fra Måned, ellers trekker vi 6 fra verdien fra [Måned].
Regnskapskvartal
=INT(([FiscalMonth]+2)/3)
Formelen vi bruker for Finanskvartal, er stort sett den samme som den var for Kvartal i kalenderåret. Den eneste forskjellen er at vi angir [Regnskapsmåned] i stedet for [Måned].
Helligdager eller spesielle datoer
Du vil kanskje inkludere en datokolonne som angir at bestemte datoer er helligdager eller andre spesielle datoer. Det kan for eksempel hende at du vil summere salgstotaler for nyttårsdag ved å legge til et Helligdag-felt i en pivottabell, som en slicer eller et filter. I andre tilfeller vil du kanskje utelate disse datoene fra andre datokolonner eller i et mål.
Det er ganske enkelt å inkludere helligdager eller spesielle dager. Du kan opprette en tabell i Excel som inneholder datoene du vil inkludere. Deretter kan du kopiere eller bruke Legg til i datamodell for å legge den til i datamodellen som en koblet tabell. I de fleste tilfeller er det ikke nødvendig å opprette en relasjon mellom tabellen og Calendar-tabellen. Alle formler som refererer til den, kan bruke funksjonen SLÅ.OPPVERDI til å returnere verdier.
Nedenfor finner du et eksempel på en tabell opprettet i Excel som inneholder helligdager som skal legges til i datotabellen:
| Dato | Helligdag |
|---|---|
| 1/1/2010 | Nyttår |
| 11/25/2010 | Høsttakkefest |
| 12/25/2010 | Jul |
| 01.01.2011 | Nyttår |
| 11/24/2011 | Høsttakkefest |
| 12/25/2011 | Jul |
| 01.01.2012 | Nyttår |
| 22.11.2012 | Høsttakkefest |
| 12/25/2012 | Jul |
| 1/1/2013 | Nyttår |
| 11/28/2013 | Høsttakkefest |
| 12/25/2013 | Jul |
| 11/27/2014 | Høsttakkefest |
| 12/25/2014 | Jul |
| 01.01.2014 | Nyttår |
| 11/27/2014 | Høsttakkefest |
| 12/25/2014 | Jul |
| 1/1/2015 | Nyttår |
| 11/26/2014 | Høsttakkefest |
| 12/25/2015 | Jul |
| 01.01.2016 | Nyttår |
| 11/24/2016 | Høsttakkefest |
| 12/25/2016 | Jul |
I datotabellen oppretter vi en kolonne kalt Ferie og bruker en formel som dette:
=LOOKUPVALUE(Helligdager[Helligdager],Helligdager[dato],Calendar[dato])
La oss se nærmere på denne formelen.
Vi bruker funksjonen SØKEVERDI til å hente verdier fra kolonnen Helligdager i tabellen Helligdager. I det første argumentet angir vi kolonnen der resultatverdien skal være. Vi angir Helligdager-kolonnen i Helligdager-tabellen fordi det er denne verdien vi vil ha returnert.
=LOOKUPVALUE(Helligdager[Helligdager],Helligdager[dato],Calendar[dato])
Deretter angir vi det andre argumentet, søkekolonnen som har datoene vi vil søke etter. Vi spesifiserer datokolonnen i Helligdager-tabellen , slik:
=LOOKUPVALUE(Helligdager[Helligdager],Helligdager[dato],Calendar[dato])
Til slutt angir vi kolonnen i Calendar-tabellen som inneholder datoene vi vil søke etter i ferietabellen. Dette er selvfølgelig Dato-kolonnen i Calendar-tabellen.
=LOOKUPVALUE(Helligdager[Helligdager],Helligdager[dato],Calendar[dato])
Helligdagskolonnen returnerer helligdagsnavnet for hver rad som har en datoverdi som samsvarer med en dato i tabellen Helligdager.
Egendefinert kalender – tretten fireukersperioder
Noen organisasjoner, for eksempel detaljhandel eller matservering, rapporterer ofte om ulike perioder, for eksempel tretten fireukersperioder. Med en tretten fireukers periodekalender, er hver periode 28 dager; derfor inneholder hver periode fire mandager, fire tirsdager, fire onsdager, og så videre. Hver periode inneholder samme antall dager, og vanligvis faller helligdager innenfor samme periode hvert år. Du kan velge å starte en periode på hvilken som helst dag i uken. På samme måte som med datoer i et kalender- eller regnskapsår, kan du bruke DAX til å opprette ekstra kolonner med egendefinerte datoer.
I eksemplene nedenfor starter den første hele perioden den første søndagen i regnskapsåret. I dette tilfellet begynner regnskapsåret 01.07.
Uke
Denne verdien gir oss ukenummeret og begynner med den første hele uken i regnskapsåret. I dette eksemplet begynner den første hele uken på søndag, slik at den første hele uken i det første regnskapsåret i Calendar-tabellen faktisk begynner 04.07.2010 og fortsetter til og med den siste hele uken i Calendar-tabellen. Selv om denne verdien i seg selv ikke er så nyttig i analyse, er den nødvendig å beregne for bruk i andre formler med en periode på 28 dager.
=INT([dato]-40356)/7)
La oss se nærmere på denne formelen.
Først oppretter vi en formel som returnerer verdier fra datokolonnen som et heltall, slik som dette:
=INT([dato])
Deretter vil vi se etter den første søndagen i det første regnskapsåret. Vi ser at det er 04.07.2010.
Trekk nå 40356 (som er heltallet for 27.06.2010, siste søndag fra forrige regnskapsår) fra denne verdien for å få antall dager siden starten av dagene i Calendar-tabellen, slik som dette:
=INT([dato]-40356)
Del deretter resultatet med 7 (dager i uken), slik:
=INT(([dato]-40356)/7)
Resultatet ser slik ut:
Punktum
Perioden i denne egendefinerte kalenderen inneholder 28 dager, og den begynner alltid på en søndag. Denne kolonnen returnerer nummeret for perioden som begynner med den første søndagen i det første regnskapsåret.
=INT(([Uke]+3)/4)
La oss se nærmere på denne formelen.
Først oppretter vi en formel som returnerer en verdi fra Uke-kolonnen som et heltall, slik som dette:
= INT([Uke])
Deretter legger du til 3 i denne verdien, på følgende måte:
=INT([Uke]+3)
Del deretter resultatet på 4, slik:
=INT(([Uke]+3)/4)
Resultatet ser slik ut:
Regnskapsår for periode
Denne verdien returnerer regnskapsåret for en periode.
=INT(([Period]+12)/13)+2008
La oss se nærmere på denne formelen.
Først lager vi en formel som returnerer en verdi fra periode og legger sammen 12:
=([Periode]+12)
Vi deler resultatet på 13, fordi det er tretten 28-dagers perioder i regnskapsåret:
=(([Periode]+12)/13)
Vi legger til 2010, fordi det er det første året i tabellen:
=(([Periode]+12)/13)+2010
Til slutt bruker vi HELTALL til å fjerne en brøkdel av resultatet, og returnere et helt tall når det blir dividert med 13, slik som dette:
= INT(([Period]+12)/13)+2010
Resultatet ser slik ut:
Periode i regnskapsår
Denne verdien returnerer periodenummeret, 1–13, fra og med den første hele perioden (begynner på søndag) i hvert regnskapsår.
=HVIS(REST([Periode],13), REST([Periode],13),13)
Denne formelen er litt mer kompleks, så vi vil beskrive den først på et språk vi forstår bedre. Denne formelen viser at du må dele verdien fra [Periode] med 13 for å få et periodenummer (1-13) i året. Hvis det tallet er 0, returneres 13.
Først lager vi en formel som returnerer resten av verdien fra punktum med 13. Vi kan bruke MOD (matematiske og trigonometriske funksjoner) slik:
= REST([Period];13)
Dette gir oss for det meste resultatet vi ønsker, bortsett fra der hvor verdien for Periode er 0, fordi disse datoene ikke faller innenfor det første regnskapsåret, som i de første fem dagene i eksempeltabellen vår for Calendar-dato. Vi kan ta oss av dette med en HVIS-funksjon. I tilfelle resultatet er 0, returnerer vi 13, slik:
= HVIS(REST([Periode],13),REST([Periode],13),13)
Resultatet ser slik ut:
Eksempel på pivottabell
Bildet nedenfor viser en pivottabell med SalesAmount-feltet fra faktatabellen Salg i VERDIER, og feltene PeriodFiscalYear og PeriodInFiscalYear fra dimensjonstabellen Calendar date i ROWS. SalesAmount aggregeres for konteksten etter regnskapsår og 28-dagers periode i regnskapsåret.
Relasjoner
Når du har opprettet en datotabell i datamodellen, kan du begynne å bla gjennom dataene i pivottabeller og rapporter, og hvis du vil aggregere data basert på kolonnene i tabellen i datodimensjonen, må du opprette en relasjon mellom faktatabellen med transaksjonsdataene og datotabellen.
Siden du må opprette en relasjon basert på datoer, må du sørge for at du oppretter denne relasjonen mellom kolonner som har verdiene av datatypen datetime (dato).
For hver datoverdi i faktatabellen må den relaterte oppslagskolonnen i datotabellen inneholde samsvarende verdier. En rad (transaksjonspost) i faktatabellen Salg med en verdi på 15.08.2012 12:00 i DateKey-kolonnen må for eksempel ha en tilsvarende verdi i den relaterte Dato-kolonnen i datotabellen (kalt Calendar). Dette er en av de viktigste grunnene til at du vil at datokolonnen i datotabellen skal inneholde et sammenhengende datoområde som inkluderer eventuelle datoer i faktatabellen.
Obs!
Selv om datokolonnen i hver tabell må være av samme datatype (dato), spiller ikke formatet på hver kolonne noen rolle.
Obs!
Hvis Power Pivot ikke lar deg opprette relasjoner mellom de to tabellene, er det ikke sikkert datofeltene lagrer datoen og klokkeslettet med samme presisjonsnivå. Avhengig av kolonneformateringen kan verdiene se like ut, men lagres forskjellig. Les mer om å arbeide med tid.
Obs!
Unngå å bruke surrogatnøkler for heltall i relasjoner. Når du importerer data fra en relasjonsdatakilde, representeres ofte dato- og klokkeslettkolonner av en surrogatnøkkel, som er en heltallskolonne som brukes til å representere en unik dato. I Power Pivot bør du unngå å opprette relasjoner ved hjelp av dato-/klokkeslettnøkler som heltall, og i stedet bruke kolonner som inneholder unike verdier med en datatype for dato. Selv om bruk av surrogatnøkler regnes som beste fremgangsmåte i tradisjonelle datavarehus, trengs ikke heltallsnøklene i Power Pivot, og det kan gjøre det vanskelig å gruppere verdier i pivottabeller etter ulike datoperioder.
Hvis du får en Typekonflikt-feil når du prøver å opprette en relasjon, er det sannsynligvis fordi kolonnen i faktatabellen ikke er av datatypen Dato. Dette kan skje når Power Pivot ikke automatisk kan konvertere en ikke-dato (vanligvis en tekstdatatype) til en datatype for dato. Du kan fortsatt bruke kolonnen i faktatabellen, men du må konvertere dataene med en DAX-formel i en ny beregnet kolonne. Se Konvertere datoer i datatypen Tekst til en datatype for dato senere i tillegget.
Flere relasjoner
I noen tilfeller kan det være nødvendig å opprette flere relasjoner eller flere datotabeller. Hvis det for eksempel er flere datofelt i faktatabellen Salg, for eksempel DateKey, ShipDate og ReturnDate, kan alle ha relasjoner til datofeltet i Calendar-datotabellen, men bare ett av disse kan være en aktiv relasjon. I dette tilfellet, fordi DateKey representerer datoen for transaksjonen, og dermed den viktigste datoen, vil dette fungere best som den aktive relasjonen. De andre har inaktive relasjoner.
Følgende pivottabell beregner totalt salg etter regnskapsår og regnskapskvartal. Et mål kalt Totalt salg, med formelen Totalt salg:=SUM([SalesAmount]), plasseres i VERDIER, og feltene Regnskapsår og Regnskapskvartal fra datotabellen Calendar plasseres i RADER.
Denne enkle pivottabellen fungerer riktig fordi vi ønsker å summere vårt totale salg etter transaksjonsdatoen i DateKey. Totalt salg-målet vårt bruker datoene i DateKey og summeres etter regnskapsår og regnskapskvartal fordi det er en relasjon mellom DateKey i Salg-tabellen og Dato-kolonnen i Calendar-datotabellen.
Inaktive relasjoner
Men hva om vi ønsket å summere vårt totale salg, ikke etter transaksjonsdato, men etter forsendelsesdato? Vi trenger en relasjon mellom Forsendelsesdato-kolonnen i Salg-tabellen og Dato-kolonnen i Calendar-tabellen. Hvis vi ikke oppretter denne relasjonen, er aggregasjonene våre alltid basert på transaksjonsdatoen. Vi kan imidlertid ha flere relasjoner, selv om bare én kan være aktiv, og fordi transaksjonsdatoen er den viktigste, får den den aktive relasjonen med Calendar-tabellen.
I dette tilfellet har ShipDate en inaktiv relasjon, slik at alle målformler som opprettes for å aggregere data basert på forsendelsesdatoer, må angi den inaktive relasjonen ved hjelp av USERELATIONSHIP-funksjonen .
Fordi det for eksempel er en inaktiv relasjon mellom Forsendelsesdato-kolonnen i Salg-tabellen og Dato-kolonnen i Calendar-tabellen, kan vi opprette et mål som summerer totalt salg etter forsendelsesdato. Vi bruker en formel som dette for å spesifisere relasjonen som skal brukes:
Totalt salg etter forsendelsesdato:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Denne formelen sier ganske enkelt: Beregne en sum for SalesAmount, men filtrer ved å bruke relasjonen mellom ShipDate-kolonnen i Sales-tabellen og Date-kolonnen i Calendar-tabellen.
Hvis vi nå oppretter en pivottabell og plasserer mål for totalt salg etter forsendelsesdato i VERDIER, og regnskapsår og regnskapskvartal på RADER, ser vi den samme totalsummen, men alle andre totalbeløp for regnskapsår og regnskapskvartal er forskjellige fordi de er basert på forsendelsesdatoen og ikke transaksjonsdatoen.
Hvis du bruker inaktive relasjoner, kan du bruke bare én datotabell, men det krever at alle mål (for eksempel totalt salg etter forsendelsesdato) refererer til den inaktive relasjonen i formelen. Det finnes et annet alternativ, nemlig å bruke flere datotabeller.
Flere datotabeller
En annen måte å arbeide med flere datokolonner i faktatabellen på, er å opprette flere datotabeller og opprette separate, aktive relasjoner mellom dem. La oss se på eksemplet med tabellen Salg på nytt. Vi har tre kolonner med datoer vi kanskje vil aggregere data om:
- En DateKey med salgsdatoen for hver transaksjon.
- En Forsendelsesdato – med datoen og klokkeslettet da de solgte varene ble sendt til kunden.
- En Returdato – med datoen og klokkeslettet da en eller flere returnerte varer ble mottatt.
Husk at DateKey-feltet med transaksjonsdatoen er viktigst. Vi vil gjøre de fleste aggregasjonene basert på disse datoene, så vi vil absolutt ha en relasjon mellom den og datokolonnen i Calendar-tabellen. Hvis vi ikke vil opprette inaktive relasjoner mellom Forsendelsesdato og Returdato og Dato-feltet i Calendar-tabellen, og derfor krever spesielle målformler, kan vi opprette flere datotabeller for forsendelsesdato og returdato. Vi kan deretter opprette aktive relasjoner mellom dem.
I dette eksemplet har vi opprettet en annen datotabell kalt ShipCalendar. Dette betyr selvfølgelig også å opprette flere datokolonner, og siden disse datokolonnene er i en annen datotabell, ønsker vi å gi dem navn på en måte som skiller dem fra de samme kolonnene i Calendar-tabellen. Vi har for eksempel opprettet kolonner kalt ShipYear, ShipMonth, ShipQuarter og så videre.
Hvis vi oppretter pivottabellen og setter Totalt salg-målet i VERDIER, og ShipFiscalYear og ShipFiscalQuarter på RADER, ser vi de samme resultatene som vi så da vi opprettet en inaktiv relasjon og et spesielt beregnet felt for totalt salg etter forsendelsesdato.
Hver av disse tilnærmingene krever nøye vurdering. Når du bruker flere relasjoner med én enkelt datotabell, må du kanskje opprette spesielle mål som overfører inaktive relasjoner ved hjelp av BRUKRELASJON-funksjonen. På den annen side kan det være forvirrende å opprette flere datotabeller i en feltliste, og siden du har flere tabeller i datamodellen, vil det kreve mer minne. Eksperimenter med det som fungerer best for deg.
Datotabell, egenskap
Datotabellegenskapen angir at metadata som er nødvendige for at Time-Intelligence funksjoner som TOTALYTD, PREVIOUSMONTH og DATESBETWEEN skal fungere riktig. Når en beregning kjøres ved hjelp av en av disse funksjonene, vet Power Pivots formelmotor hvor den skal gå for å få datoene den trenger.
Advarsel
Hvis denne egenskapen ikke er angitt, kan mål som bruker DAX-Time-Intelligence funksjoner ikke returnere riktige resultater.
Når du angir egenskapen Datotabell, angir du en datotabell og en datokolonne av datatypen Dato (dato/klokkeslett).
Fremgangsmåte: Angi egenskapen for datotabell
- Velg Calendar-tabellen i PowerPivot-vinduet.
- Klikk Merk som datotabell på Utforming-fanen.
- Velg en kolonne med unike verdier og datatypen dato i dialogboksen Merk som datotabell.
Arbeide med tid
Alle datoverdier med datatypen Dato i Excel eller SQL Server er faktisk et tall. Inkludert i dette tallet er sifre som refererer til et klokkeslett. I mange tilfeller er den tiden for hver eneste rad midnatt. Hvis for eksempel et DateTimeKey-felt i en salgsfaktatabell har verdier som 19.10.2010 12:00:00 AM, betyr dette at verdiene er på dagspresisjonsnivå. Hvis verdiene i DateTimeKey-feltet har et klokkeslett inkludert, for eksempel 19.10.2010 08:44:00, betyr dette at verdiene er på minuttpresisjonsnivå. Verdier kan også være på timenivåpresisjon eller til og med sekundpresisjon. Presisjonsnivået i tidsverdien har stor innvirkning på hvordan du oppretter datotabellen, og relasjonene mellom den og faktatabellen.
Du må avgjøre om du vil aggregere dataene til et dagspresisjonsnivå eller et tidspresisjonsnivå. Med andre ord vil du kanskje bruke kolonner i datotabellen, for eksempel morgen, ettermiddag eller time, som klokkeslett, datofelt i rad-, kolonne- eller filterområdene i en pivottabell.
Obs!
Dager er den minste tidsenheten som DAX-funksjoner for tidsintelligens kan arbeide med. Hvis du ikke trenger å arbeide med tidsverdier, bør du redusere presisjonen til dataene slik at du kan bruke dager som minimumsenheten.
Hvis du har tenkt å aggregere dataene til tidsnivået, trenger datotabellen en datokolonne med klokkeslettet inkludert. Det trengs faktisk en datokolonne med én rad for hver time, eller kanskje til og med hvert minutt, av hver dag, for hvert år i datointervallet. Årsaken til dette er at du må ha samsvarende verdier for å opprette en relasjon mellom DateTimeKey-kolonnen i faktatabellen og datokolonnen i datotabellen. Som du sikkert kan forestille deg, hvis du inkluderer mange år, kan dette gi en veldig stor datotabell.
I de fleste tilfeller vil du imidlertid aggregere dataene bare for den dagen. Med andre ord bruker du kolonner som år, måned, uke eller ukedag som felt i rad-, kolonne- eller filterområdene i en pivottabell. I dette tilfellet trenger datokolonnen i datotabellen bare å inneholde én rad for hver dag i et år, slik vi beskrev tidligere.
Hvis datokolonnen inneholder et tidspresisjonsnivå, men du aggregerer bare til et dagnivå, for å opprette relasjonen mellom faktatabellen og datotabellen, må du kanskje endre faktatabellen ved å opprette en ny kolonne som avkorter verdiene i datokolonnen til en dagsverdi. Med andre ord, konverter en verdi som 19.10.2010 08:44:00 til 19.10.2010 12:00:00 AM. Du kan deretter opprette relasjonen mellom den nye kolonnen og datokolonnen i datotabellen fordi verdiene samsvarer.
La oss se på et eksempel. Dette bildet viser en DateTimeKey-kolonne i faktatabellen Salg. Alle aggregasjonene for dataene i denne tabellen trenger bare å være til dagnivå, ved hjelp av kolonner i Calendar-datotabellen, som År, Måned, Kvartal og så videre. Klokkeslettet som er inkludert i verdien er ikke relevant, bare den faktiske datoen.
Siden vi ikke trenger å analysere disse dataene ned til et tidsnivå, trenger vi ikke at datokolonnen i Calendar-datotabellen inneholder én rad for hver time og hvert minutt per dag i hvert år. Datokolonnen i datotabellen vår ser slik ut:
Hvis du vil opprette en relasjon mellom DateTimeKey-kolonnen i Salg-tabellen og Dato-kolonnen i Calendar-tabellen, kan vi opprette en ny beregnet kolonne i Salgsfakta-tabellen og bruke TRUNC-funksjonen til å avkorte dato- og klokkeslettverdien i DateTimeKey-kolonnen til en datoverdi som samsvarer med verdiene i Dato-kolonnen i Calendar-tabellen. Formelen vår ser slik ut:
=AVKORT([DateTimeKey],0)
Dette gir oss en ny kolonne (vi kalte DateKey) med datoen fra DateTimeKey-kolonnen og klokkeslettet 12:00:00 for hver rad:
Nå kan vi opprette en relasjon mellom denne nye (DateKey) kolonnen og datokolonnen i Calendar-tabellen.
På samme måte kan vi opprette en beregnet kolonne i Salg-tabellen som reduserer tidspresisjonen i DateTimeKey-kolonnen til timepresisjonsnivå. I dette tilfellet vil ikke TRUNC-funksjonen fungere, men vi kan fortsatt bruke andre DAX-funksjoner for dato og klokkeslett til å trekke ut og på nytt kjede en ny verdi til et timepresisjonsnivå. Vi kan bruke en formel som dette:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Vår nye kolonne ser slik ut:
Forutsatt at datokolonnen i datotabellen har verdier opptil timepresisjon, kan vi opprette en relasjon mellom dem.
Gjøre datoer mer brukervennlige
Mange av datokolonnene du oppretter i datotabellen, er nødvendige for andre felt, men er egentlig ikke så nyttige i analyse. DateKey-feltet i Salg-tabellen som vi har referert til og vist i denne artikkelen, er for eksempel viktig fordi for hver transaksjon registreres transaksjonen som forekommende på en bestemt dato og et bestemt klokkeslett. Men fra et analyse- og rapporteringssynspunkt er det ikke så nyttig, fordi vi ikke kan bruke det som en rad, kolonne eller filterfelt i en pivottabell eller -rapport.
På samme måte er datokolonnen i Calendar-tabellen i vårt eksempel veldig nyttig, faktisk kritisk, men du kan ikke bruke den som en dimensjon i en pivottabell.
For at tabellene og kolonnene i dem skal være så nyttige som mulig, og for å gjøre det enklere å navigere i feltlister for pivottabeller eller Power View-rapporter, er det viktig å skjule unødvendige kolonner fra klientverktøy. Du kan også skjule enkelte tabeller. Tabellen Helligdager som ble vist tidligere, inneholder helligdager som er viktige for bestemte kolonner i Calendar-tabellen, men du kan ikke bruke selve Dato- og Helligdags-kolonnene i tabellen Helligdager som felt i en pivottabell. Også her kan du skjule hele helligdagstabellen for å gjøre det enklere å navigere i feltlister.
Et annet viktig aspekt ved å arbeide med datoer er navnekonvensjoner. Du kan gi tabeller og kolonner i Power Pivot navn når du vil. Husk imidlertid at en god navnekonvensjon gjør det enklere å identifisere tabeller og datoer, spesielt hvis du skal dele arbeidsboken med andre brukere, ikke bare i feltlister, men også i Power Pivot og DAX-formler.
Når du har en datotabell i datamodellen, kan du begynne å opprette mål som hjelper deg med å få mest mulig ut av dataene. Noen kan være så enkle som å summere salgstotaler for inneværende år, mens andre kan være mer komplekse, der du må filtrere etter et bestemt område av unike datoer. Mer informasjon i Mål i Power Pivot og funksjoner for tidsintelligens.
Vedlegg
Konvertere datoer i datatypen Tekst til datatypen dato
I noen tilfeller kan en faktatabell med transaksjonsdata inneholde datoer for datatypen tekst. Det vil si at en dato som vises som 04.12.2012T11:47:09 faktisk ikke er en dato i det hele tatt, eller i det minste ikke av typen dato som Power Pivot kan forstå. Det er egentlig bare tekst som leses som en dato. Hvis du vil opprette en relasjon mellom en datokolonne i faktatabellen og en datokolonne i en datotabell, må begge kolonnene være av datatypen Dato .
Vanligvis når du prøver å endre datatypen for en kolonne med datoer som er tekstdatatype, til en datodatatype, kan Power Pivot automatisk tolke datoene og konvertere dem til datatypen sann dato. Hvis Power Pivot ikke kan utføre en datatypekonvertering, vil du få en typekonfliktfeil.
Du kan imidlertid fortsatt konvertere datoene til datatypen sann dato. Du kan opprette en ny beregnet kolonne og bruke en DAX-formel til å analysere år, måned, dag, klokkeslett og så videre fra tekststrengene og deretter kjede dem sammen igjen slik at Power Pivot kan lese som en sann dato.
I dette eksemplet har vi importert en faktatabell kalt Salg til Power Pivot. Den inneholder en kolonne kalt DateTime. Verdiene ser slik ut:
Hvis vi ser på Datatype i Formatering-gruppen Hjem-fanen i Power Pivot, ser vi at det er Tekst-datatypen.
Vi kan ikke opprette en relasjon mellom DateTime-kolonnen og Dato-kolonnen i datotabellen fordi datatypene ikke samsvarer. Hvis vi prøver å endre datatypen til Dags dato, får vi en typekonfliktfeil:
I dette tilfellet kunne ikke Power Pivot konvertere datatypen fra tekst til dato. Vi kan fortsatt bruke denne kolonnen, men for å få den til en sann datatype, må vi opprette en ny kolonne som analyserer teksten og gjenoppretter den til en verdi Power Pivot kan angi en datatype for dato.
Husk at fra delen Arbeide med tid tidligere i denne artikkelen; Med mindre det er nødvendig at analysen skal være på et klokkeslettpresisjonsnivå, bør du konvertere datoer i faktatabellen til dagspresisjon. Med dette i tankene vil vi at verdiene i den nye kolonnen skal være på dagpresisjonsnivå (unntatt tid). Vi kan både konvertere verdiene i DateTime-kolonnen til en datodatatype og fjerne tidspresisjonnivået med følgende formel:
=DATO(VENSTRE([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Dette gir oss en ny kolonne (i dette tilfellet kalt Dato). Power Pivot oppdager til og med verdiene som datoer og angir datatypen automatisk til Dato.
Hvis vi ønsker å bevare tidspresisjonsnivået, utvider vi ganske enkelt formelen til å inkludere timer, minutter og sekunder.
=DATO(VENSTRE([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TID(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Nå som vi har en datokolonne med datatypen dato, kan vi opprette en relasjon mellom den og en datokolonne i en dato.
Flere ressurser
Hurtigveiledning: Lær det grunnleggende om DAX på 30 minutter