Opplæring: Utvide datamodellrelasjoner ved hjelp av Excel, Power Pivot og DAX

Denne opplæringen har følgende deler:

• Legge til en relasjon ved hjelp av Diagramvisning i Power Pivot

• Utvide datamodellen ved hjelp av beregnede kolonner

• Opprette et hierarki

• Bruke hierarkier i pivottabeller

• Kontrollpunkt og test

På slutten av denne opplæringen kan du ta en test for å kontrollere hva du har lært.

Denne serien bruker data som beskriver olympiske medaljer, vertsland og ulike OL-øvelser. Opplæringene i denne serien er følgende:

1. Importere Data til Excel, og opprette en datamodell

2. Utvide datamodellrelasjoner ved hjelp av Excel, Power Pivot , og DAX

3. Opprette kartbaserte Power View-rapporter

4. Integrere Internett-data og angi standarder for Power View-rapporter

5. Hjelp for Power Pivot

6. Lag flotte Power View-rapporter – del 2

Vi foreslår at du fullfører dem i denne rekkefølgen.

Disse leksjonene bruke Excel 2013 med Power Pivot aktivert. Hvis du vil ha mer informasjon om Excel 2013, klikker du her. Veiledning om hvordan du aktiverer Power Pivot, klikker du her.

Legge til en relasjon ved hjelp av Diagramvisning i Power Pivot

I denne inndelingen skal du bruke tillegget Microsoft Office Power Pivot i Excel 2013 til å utvide modellen. Bruk av Diagramvisning i Microsoft SQL Server Power Pivot for Excel gjør det enkelt å opprette relasjoner. Først må du kontrollere at Power Pivot-tillegget er aktivert.

Obs: Power Pivot i Microsoft Excel 2013-tillegget er en del av Office Professional Plus. Du finner mer informasjon, kan du se Start Power Pivot in Microsoft Excel 2013-tillegget .

Legge til Power Pivot i Excel-båndet ved å aktivere tillegget Power Pivot

Når Power Pivot er aktivert, vises en båndfane i Excel 2013 kalt POWER PIVOT. Hvis du vil aktivere Power Pivot, følger du fremgangsmåten nedenfor.

1. Gå til Fil > Alternativer > Tillegg.

2. Klikk COM-tillegg> Start i Behandle-boksen nær bunnen.

3. Merk av for Microsoft Office Power Pivot i Microsoft Excel 2013, og klikk deretter OK.

Excel-båndet har nå en POWER PIVOT-fane.

Legge til en relasjon ved hjelp av diagramvisning i Power Pivot

Excel-arbeidsboken inneholder en tabell kalt Verter. Vi importerte Verter ved å kopiere og lime den inn i Excel, og deretter formatere dataene som en tabell. Vi må etablere en relasjon for å kunne legge til Verter-tabellen i datamodellen. La oss bruke Power Pivot til å representere relasjonene i datamodellen visuelt, og deretter opprette relasjonen.

1. Klikk Verter-fanen i Excel for å gjøre den til det aktive arket.

2. Velg POWER PIVOT > Tabeller > Legg til i datamodell på båndet. Ved hjelp av dette trinnet legger vi til Verter-tabellen i datamodellen. Vi åpner også Power Pivot-tillegget, som brukes til å utføre resten av trinnene i denne oppgaven.

3. Legg merke til at Power Pivot-vinduet viser alle tabellene i modellen, inkludert verter. Klikk deg gjennom et par tabeller. I Power Pivot kan du vise alle dataene som modellen inneholder, selv om de ikke vises i alle regneark i Excel, for eksempel disipliner, hendelserog medaljer data under, i tillegg til S_Teams,W_ Grupper, og Sport.
   
   

4. Klikk Diagramvisning i vinduet Power Pivot i visning-delen.

5. Bruk glidebryteren for å endre størrelsen på diagrammet slik at du kan se alle objektene i diagrammet. Ordne tabeller ved å dra tittellinjen, slik at de er synlige og plassert ved siden av hverandre. Legg merke til at fire tabeller er relatert til resten av tabellene: verter, hendelser, W_Teamsog S_Teams.
   
   

6. Du vil legge merke til at tabellene Medaljer og Arrangementer begge har et felt kalt DisiplinArrangement. Ved nærmere ettersyn finner du ut at DisiplinArrangement-feltet i Arrangementer-tabellen består av unike, ikke-gjentatte verdier.

1. Opprette en relasjon mellom Medaljer-tabellen og Arrangementer-tabellen. Når du er i Diagramvisning, drar du DisiplinArrangement-feltet fra Arrangementer-tabellen til DisiplinArrangement-feltet i Medaljer. En linje vises mellom dem, som indikerer at en relasjon har blitt etablert.

2. Klikk linjen som forbinder hendelser og medaljer. Feltene uthevet definerer relasjonen, som vist i følgende skjermbilde.
   
   
   

3. Hvis vi vil koble Verter til datamodellen, trenger vi et felt med unike verdier som identifiserer hver rad i Verter-tabellen. Da kan vi søke i datamodellen for å se om de samme dataene finnes i en annen tabell. Dette kan ikke gjøres ved å se i Diagramvisning. Bytt tilbake til Datavisning mens Verter er valgt.

4. Etter å undersøke kolonnene vi er klar over at verter ikke har en kolonne med unike verdier. Vi må du opprette den ved hjelp av en beregnet kolonne og Data Analysis Expressions (DAX).

Det er fint når dataene i datamodellen har alle feltene som er nødvendige for å opprette relasjoner, og blander data for visualisering i Power View eller pivottabeller. Men tabeller er ikke alltid så enkle å jobbe med, derfor beskriver neste inndeling hvordan man oppretter en ny kolonne ved å bruke DAX, som kan brukes til å opprette en relasjon mellom tabeller.

Utvide datamodellen ved hjelp av beregnede kolonner

Hvis vi vil etablere en relasjon mellom Verter-tabellen og datamodellen, og dermed utvide datamodellen til å inkludere Verter-tabellen, må Verter inneholde et felt som unikt identifiserer hver rad. I tillegg må det feltet samsvare med et felt i datamodellen. Disse tilsvarende feltene, én i hver tabell, er det som gjør at tabellenes data kan tilknyttes.

Siden verter -tabellen ikke har et slikt felt, må du opprette den. Du kan ikke bruke Power Pivot for å beskytte av datamodellen, redigere eller slette eksisterende data. Du kan imidlertid opprette nye kolonner ved hjelp av beregnede felt som er basert på eksisterende data.

Ved å se gjennom Verter-tabellen og deretter se på andre datamodelltabeller, finner vi en god kandidat for et unikt felt vi kan opprette i Verter, og deretter knytte til en tabell i datamodellen. Begge tabellene vil kreve en ny, beregnet kolonne for å oppfylle de nødvendige kravene for å etablere en relasjon.

I Verter kan vi opprette en unik beregnet kolonne ved å kombinere Utgave-feltet (året for OL-arrangementet) og Sesong-feltet (Sommer eller Vinter). I Medaljer-tabellen er det også et Utgave-felt og et Sesong-felt, så hvis vi oppretter en beregnet kolonne i hver av disse tabellene som kombinerer Utgave- og Sesong-feltene, kan vi opprette en relasjon mellom Verter og Medaljer. Det følgende skjermbildet viser Verter-tabellen, med feltene Utgave og Sesong merket

Opprette beregnede kolonner ved hjelp av DAX

La oss starte med Verter-tabellen. Målet er å opprette en beregnet kolonne i Verter-tabellen, og deretter i Medaljer-tabellen, som kan brukes til å etablere en relasjon mellom dem.

I Power Pivot kan du bruke DAX (Data Analysis Expressions) til å opprette beregninger. DAX er et formelspråk for Power Pivot og pivottabeller, utformet for relasjonsdata og kontekstavhengige analyse som er tilgjengelig i Power Pivot. Du kan opprette DAX-formler i en ny Power Pivot-kolonne og i beregningsområdet i Power Pivot.

1. I Power Pivot velger du HJEM > Vis > Datavisning for å sikre at datavisning er valgt i stedet for diagramvisning.

2. Velg verter -tabellen i Power Pivot. Ved siden av de eksisterende kolonnene er en tom kolonne kalt Legg til kolonne. Power Pivot gir den kolonnen som plassholder. Det finnes mange måter å legge til en ny kolonne i en tabell i Power Pivot, der en er ganske enkelt merke tomme kolonnen som inneholder tittelen Legg til kolonne.
   
   
   

3. Skriv inn følgende DAX-formel i formellinjen. KJEDE.sammen-funksjonen kombinerer to eller flere felt i én liste. Når du skriver, Autofullfør lar deg skriver du inn de fullstendige kolonnenavn kolonner og tabeller, og viser funksjonene som er tilgjengelige. Bruk tab for å velge Autofullfør-forslag. Du kan også bare klikker kolonnen mens du skriver inn formelen, og Power Pivot setter inn navnet på kolonnen i formelen.
   
   =CONCATENATE([Edition],[Season])

4. Når du er ferdig med å bygge formelen, trykker du Enter for å godta den.

5. Alle radene i den beregnede kolonnen fylles ut med verdier. Hvis du ruller ned gjennom tabellen, ser du at hver rad er unik – vi har derfor lyktes i å opprette et felt som unikt identifiserer hver rad i Verter-tabellen. Slike felt kalles en primærnøkkel.

6. La oss gi den beregnede kolonnen navnet UtgaveID. Du kan gi en kolonne nytt navn ved å dobbeltklikke den, eller ved å høyreklikke og velge Gi kolonnen nytt navn. Når du er ferdig, ser Verter-tabellen i Power Pivot ut som følgende skjermbilde.
   
   

Verter-tabellen er klar. La oss opprette en beregnet kolonne i Medaljer som samsvarer med formatet på UtgaveID-kolonnen vi opprettet i Verter, så vi kan opprette en relasjon mellom dem.

1. Begynn med å opprette en ny kolonne i Medaljer-tabellen, slik vi gjorde for Verter. Velg Medaljer i Power Pivot-tabellen, og klikk Utforming > Kolonner > Legg til. Legg merke til at Legg til kolonne er valgt. Dette har samme effekt som ved ganske enkelt å velge Legg til kolonne.

2. Utgave-kolonnen i Medaljer har et annet format enn Utgave-kolonnen i Verter. Før vi kombinerer eller kjeder sammen Utgave-kolonnen med Sesong-kolonnen for å opprette UtgaveID-kolonnen, må vi opprette et mellomleddfelt som sørger for at Utgave får riktig format. Skriv inn følgende DAX-formel på formellinjen over tabellen.

   = YEAR([Edition])
   

3. Når du er ferdig å bygge formelen, trykker du Enter. Alle radene i den beregnede kolonnen fylles ut med verdier basert på formelen du skrev inn. Hvis du sammenligner denne kolonnen med Utgave-kolonnen i Verter, vil du se at disse kolonnene har samme format.

4. Gi kolonnen et nytt navn ved å høyreklikke CalculatedColumn1 og velge Gi kolonnen nytt navn. Skriv inn År, og trykk deretter Enter.

5. Når du har opprettet en ny kolonne, la Power Pivot til en annen plassholderkolonne kalt Legg til kolonne. Nå skal vi opprette den beregnede UtgaveID-kolonnen. Velg Legg til kolonne. Skriv inn DAX-formelen nedenfor på formellinjen, og trykk deretter Enter.
   
   =CONCATENATE([Year],[Season])

6. Gi kolonnen et nytt navn ved å dobbeltklikke CalculatedColumn1 og skrive inn UtgaveID.

7. Sortere kolonnen i stigende rekkefølge. Tabellen medaljer i Power Pivot ser nå slik det følgende skjermbildet.
   
   
   

Legg merke til at mange verdier er gjentatt i Medaljer-tabellens UtgaveID-felt. Det er i orden og forventet, da det ble utdelt mange medaljer under hver utgave av OL (nå representert av UtgaveID-verdien). Det som er unikt i Medaljer-tabellen er hver utdelte enkeltmedalje. Den unike identifikatoren for hver oppføring i Medaljer-tabellen, og tilhørende angitt primærnøkkel, er Medaljenøkkel-feltet.

Neste trinn er å opprette en relasjon mellom Verter og Medaljer.

Opprette en relasjon ved hjelp av beregnede kolonner

La oss nå bruke de beregnede kolonnene vi opprettet, til å etablere en relasjon mellom Verter og Medaljer.

1. Velg Hjem > visningen > diagramvisning på båndet i Power Pivot-vinduet. Du kan også bytte mellom rutenettvisning og diagramvisning ved hjelp av knappene nederst i vinduet PowerView, som vist i følgende skjermbilde.
   
   

2. Utvid verter slik at du kan vise alle feltene. Vi opprettet kolonnen Utgaveid skal fungere som verter -tabellen primærnøkkelen (unike, ikke-gjentatte felt), og opprettet en Utgaveid-kolonnen i Medals -tabellen til å aktivere opprettelsen av en relasjon mellom dem. Vi må du finne dem begge, og opprette en relasjon. Power Pivot gir en Søk -funksjonen på båndet, slik at du kan søke i datamodellen for tilsvarende feltene. Det følgende skjermbildet viser vinduet Søk etter Metadata med Utgaveid som er angitt i feltet Søk etter.
   

3. Plasser Verter-tabellen slik at den er ved siden av Medaljer.

4. Dra kolonnen Utgaveid i medaljer til kolonnen Utgaveid i verter. Power Pivot oppretter en relasjon mellom tabeller som er basert på kolonnen Utgaveid og tegner en linje mellom de to kolonnene, som angir relasjonen.
   
   

I denne delen, du har lært en ny metode for å legge til nye kolonner, opprettes en beregnet kolonne som bruker DAX, og brukes denne kolonnen til å opprette en ny relasjon mellom tabeller. Verter -tabellen er nå integrert i datamodellen, og det finnes dataene i pivottabellen i Ark1. Du kan også bruke de tilknyttede dataene til å opprette flere pivottabeller, pivotdiagrammer, Power View-rapporter, og mye mer.

Opprette et hierarki

De fleste datamodeller inneholder data som er arvemessig hierarkiske. Vanlige eksempler omfatter kalenderdata, geografiske data og produktkategorier. Oppretting av hierarkier inne i Power Pivot er nyttig fordi du kan dra ett element til en rapport – hierarkiet – i stedet for å sette sammen og ordne de samme feltene gang på gang.

Olympiske data er også hierarkiske. Det er nyttig å forstå det Olympiske hierarkiet som bestående av sport, disipliner, og hendelser. For hver idrett er det én eller flere tilknyttede disipliner (noen ganger er det flere). Og for hver disiplin er det én eller flere hendelser (her finnes også noen ganger flere hendelser i hver disiplin). Følgende bilde viser hierarkiet.

I denne inndelingen skal du opprette to hierarkier innen de Olympiske dataene du har brukt i denne opplæringen. Disse hierarkiene kan så brukes til å se hvordan hierarkiene gjør dataorganisering enkelt i pivottabeller og, i en etterfølgende opplæring, i Power View.

Opprette et idrettshierarki

1. Bytt til Diagramvisning i Power Pivot. Utvid Arrangementer-tabellen, slik at du enklere kan se alle feltene i den.

2. Trykk og hold nede Ctrl, og klikk Idrett, Disiplin og Arrangement-feltene. Når disse tre feltene er merket, høyreklikker du og velger Opprett hierarki. Det opprettes en overordnet hierarkinode, Hierarchy 1, nederst i tabellen, og de merkede kolonnene kopieres under hierarkiet som underordnede noder. Kontroller at Idrett vises først i hierarkiet, deretter Disiplin etterfulgt av Arrangement.

3. Dobbeltklikk på tittelen Hierarki1, og skriv inn SDE for å gi nytt navn til hierarkiet. Nå har du et hierarki som inneholder Idrett, Disiplin og Arrangement. Arrangementstabellen ser nå ut som på følgende skjermbilde.
   
   

Opprette et Stedshierarki

1. Fortsatt i diagramvisning i Power Pivot, velg verter -tabellen, og klikk Opprett hierarki-knappen i tabelloverskriften, som vist i følgende skjermbilde.
   
   
   En tom hierarki overordnet node vises nederst i tabellen.

2. Skriv inn Steder som navn på det nye hierarkiet.

3. Det er flere måter å legge til et arrangement i et hierarki. Dra Sesong, By og NOC_LandOmråde-feltene over i hierarkiets navn (i dette tilfellet Steder) til hierarkiet navn er uthevet, og slipp for å legge dem til.

4. Høyreklikk UtgaveID, og velg Legg til i hierarki. Velg Steder.

5. Kontroller at de underordnede hierarkinoder er i rekkefølge. Sorter må være fra topp til bunn,: sesong, NOC, by, Utgaveid. Hvis de underordnede hierarkinoder er utenfor rekkefølge, må du ganske enkelt dra dem inn i riktig rekkefølge i hierarkiet. Tabellen skal se ut som følgende skjermbilde.
   

Datamodellen har nå hierarkier som kan komme til nytte i rapporter. I neste del skal du lære hvordan disse hierarkiene kan gjøre det raskere og mer konsekvent å opprette rapporter.

Bruke hierarkier i pivottabeller

Nå som vi har hierarkier for Idretter Steder, kan vi legge dem til i pivottabeller eller Power View og hurtig få resultater som inneholder nyttige datagrupperinger. Før du oppretter hierarkier måtte du legge til enkeltfelt i pivottabellen, og ordne disse feltene slik du ønsker de skal vises.

I denne inndelingen bruker du hierarkier opprettet i den forrige delen til raskt å presisere pivottabellen. Deretter oppretter du samme pivottabellvisning ved hjelp av de enkelte feltene i hierarkiet, slik at du kan sammenligne ved å bruke hierarkier til å bruke enkeltfelt.

1. Gå tilbake til Excel.

2. Fjern feltene fra RADER-området av Pivottabellfelt i Ark1, fjern deretter alle feltene fra KOLONNER-området. Kontroller at pivottabellen er valgt (den er nå ganske liten, så du kan velge celle A1 for å sikre at pivottabellen er valgt). De eneste gjenstående feltene i pivottabellfeltene er Medalje i FILTER-området, og Antall medaljer i VERDIER-området. Den nesten tomme pivottabellen skal se ut som i følgende skjermbilde.

   

3. Dra SDE fra arrangementer -tabellen til rader-området fra PivotTable Fields-området. Dra plasseringer fra verter -tabellen til kolonner -området. Ved å dra de to hierarkiene, fylles pivottabellen med store mengder data, som er ordnet i hierarkiet du definert i de forrige trinnene. Skjermen skal se ut som følgende skjermbilde.
   

4. La oss filtrere data en biters, og bare se de ti første radene i hendelser. I pivottabellen, klikker du pilen i Radetiketter, Klikk (Merk alt) Hvis du vil fjerne alle valg og deretter boksene ved siden av de ti første Idrettene. Pivottabellen ser nå slik det følgende skjermbildet.
   

5. Du kan utvide noen av disse Sports i pivottabellen som er det øverste nivået i hierarkiet SDE, og vise informasjon i et høyere nivå ned i hierarkiet (disiplin). Hvis det finnes et lavere nivå i hierarkiet for denne disiplin, kan du utvide disiplin for å se hendelsene. Du kan gjøre det samme for stedshierarki, er det øverste nivået i som sesong, som vises som sommer og vinter i pivottabellen. Når vi utvider Aquatics idrett, ser vi alle underordnede disiplin objekter og tilhørende data. Når vi utvider Diving disiplin under Aquatics, ser vi de underordnede hendelsene også, som vist i følgende skjermbilde. Vi kan gjøre det samme for vann Polo, og se at det er bare én hendelse.
   

Ved å dra de to hierarkiene, ble det raskt opprettet en pivottabell med interessante og strukturerte data som du kan fordype deg i, filtrere og ordne.

La oss nå opprette samme pivottabell, men uten fordelen av hierarkier.

1. Fjern Steder fra KOLONNER-området i området med pivottabellfelt. Fjern deretter SDE fra RADER-området. Vi er nå tilbake til en enkel pivottabell.

2. Dra Sesong, By, NOC_LandOmråde og UtgaveID fra Verter-tabellen til KOLONNER-området, og ordne dem i nevnte rekkefølge, fra topp til bunn.

3. Dra Idrett, Disiplin og Arrangement fra Arrangementer-tabellen til RADER-området, og ordne dem i nevnte rekkefølge, fra topp til bunn.

4. Filtrer radetikettene i pivottabellen til de ti øverste idrettene.

5. Skjule alle rader og kolonner, og deretter utvider Aquatics, og deretter Diving og vann Polo. Arbeidsboken ser ut som følgende skjermbilde.
   

Skjermen ser lik ut, bortsett fra at du dro sju individuelle felt til områder med pivottabellfelt i stedet for bare å dra to hierarkier. Hvis du er den eneste personen som oppretter pivottabeller eller Power View-rapporter basert på disse dataene, kan det hende det bare virker smått praktisk å opprette hierarkier. Men når det er mange personer som oppretter rapporter og må sortere felt riktig for å få ønsket visning, blir hierarkier raskt en produktivitetsforbedring og nødvendig for konsekvens.

I en annen opplæring skal du lære hvordan man bruker hierarkier og andre felt i visuelt engasjerende rapporter opprettet ved hjelp av Power View.

Kontrollpunkt og test

Gå gjennom det du har lært

Excel-arbeidsboken har nå en datamodell som inkluderer data fra flere kilder, beslektet ved hjelp av eksisterende felt og beregnede kolonner. Du har også hierarkier som gjenspeiler datastrukturen i tabellene, noe som gjør oppretting av fengslende rapporter raskt, konsekvent og enkelt.

Du har lært at ved å opprette hierarkier kan du angi iboende struktur i dataene, og raskt bruke hierarkiske data i rapportene.

I neste opplæring i denne serien skal du bruke Power View til å opprette visuelt imponerende rapporter om OL-medaljer. Du kan også gjøre flere beregninger, optimalisere data for rask rapportoppretting, samt importere flere data for å gjøre disse rapportene enda mer interessante. Her er en kobling:

Opplæring 3: Opprette kartbaserte Power View-rapporter

PRØVE

Vil du se hvor godt du husker det du har lært? Her har du muligheten. Følgende test fremhever funksjoner, muligheter eller forutsetninger som du lærte om i denne opplæringen. Nederst på siden finner du svarene. Lykke til!

Spørsmål 1: I hvilke av følgende visninger kan du opprette relasjoner mellom to tabeller?

A: Relasjoner mellom tabeller opprettes i Power View.

B: Relasjoner mellom tabeller opprettes ved å bruke Utformingsvisning i . Power Pivot

C: Relasjoner mellom tabeller opprettes ved å bruke Rutenettvisning i Power Pivot

D: Alle svarene ovenfor

Spørsmål 2: SANN eller USANN: Relasjoner mellom tabeller etableres basert på en unik identifikator som er opprettet ved hjelp av DAX-formler.

An: SANT

B: USANT

Spørsmål 3: I hvilken av følgende kan du opprette en DAX-formel?

A: I beregningsområdet i Power Pivot.

B: I en ny kolonne i Power Pivot.

C: I hvilken som helst celle i Excel 2013.

D: Både A og B.

Spørsmål 4: Hvilket av følgende utsagn er sant om hierarkier?

A: Når du oppretter et hierarki, er inkluderte felt ikke lenger tilgjengelig enkeltvis.

B: Når du oppretter et hierarki, kan inkluderte felt inkludert tilhørende hierarki brukes i klientverktøy ved ganske enkelt å dra hierarkiet til et Power View- eller pivottabellområde.

C: Når du oppretter et hierarki, kombineres underliggende data i datamodellen i ett felt.

D: Det er ikke mulig å opprette hierarkier i Power Pivot.

Svar på prøve

1. Riktig svar: D

2. Riktig svar: A

3. Riktig svar: D

4. Riktig svar: B