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

Delene i denne opplæringen er som følger:

• 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 opplæringene bruker Excel 2013 med Power Pivot aktivert. Hvis du vil ha mer informasjon om Excel 2013, klikker du her. Hvis du vil ha veiledning om hvordan du aktiverer Power Pivot, kan du klikke her.

Legge til en relasjon ved hjelp av diagramvisning i Power Pivot

I denne delen bruker du Microsoft Office Power Pivot i Excel 2013-tillegget 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 du har Power Pivot tillegget aktivert.

Obs! Tillegget Power Pivot i Microsoft Excel 2013 er en del av Office Professional Plus. Se Start Power Pivot i Microsoft Excel 2013 tillegget hvis du vil ha mer informasjon.

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

Når Power Pivot er aktivert, ser du en båndfane i Excel 2013 kalt POWER PIVOT. Følg disse trinnene for å aktivere Power Pivot.

1. Gå til FIL-> alternativer > tillegg.

2. Klikk COM-tillegg i Behandle-boksen nederst,> Gå til.

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 dem og lime dem inn i Excel og deretter formatere dataene som en tabell. Hvis du vil legge til Verter-tabellen i datamodellen, må vi opprette en relasjon. La oss bruke Power Pivot til å representere relasjonene i datamodellen visuelt, og deretter opprette relasjonen.

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

2. Velg POWER PIVOT > Tabeller > Legg til i datamodell på båndet. Dette trinnet legger til Verter-tabellen i datamodellen. Det åpner også Power Pivot tillegget, som du bruker 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 noen tabeller. I Power Pivot kan du vise alle dataene som modellen inneholder, selv om de ikke vises i regneark i Excel, for eksempel disipliner, hendelser og medaljernedenfor, samt S_Teams,W_Teams og idretter.
   
   

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

5. Bruk glidebryteren til å skalere diagrammet slik at du kan se alle objektene i diagrammet. Omorganisere tabellene ved å dra tittellinjen, slik at de vises og plasseres ved siden av hverandre. Legg merke til at fire tabeller ikke er relatert til resten av tabellene: Verter, hendelser, W_Teams og S_Teams.
   
   

6. Du legger merke til at både Medaljer-tabellen og Hendelser-tabellen har et felt kalt DisciplineEvent. Ved videre inspeksjon finner du ut at DisciplineEvent-feltet i Hendelser-tabellen består av unike, ikke-gjentatte verdier.

1. Opprett en relasjon mellom Medaljer-tabellen og Hendelser-tabellen . Når du er i diagramvisning, drar du DisciplineEvent-feltet fra Hendelser-tabellen til DisciplineEvent-feltet i Medaljer. Det vises en linje mellom dem, som angir at det er opprettet en relasjon.

2. Klikk linjen som kobler sammen hendelser og medaljer. De uthevede feltene definerer relasjonen, som vist i følgende skjermbilde.
   
   
   

3. Hvis du vil koble verter til datamodellen, trenger vi et felt med verdier som unikt identifiserer hver rad i Verter-tabellen . Deretter kan vi søke i datamodellen for å se om de samme dataene finnes i en annen tabell. Hvis vi ser i diagramvisning, kan vi ikke gjøre dette. Når verter er valgt, bytter du tilbake til datavisning.

4. Når vi har undersøkt kolonnene, innser vi at verter ikke har en kolonne med unike verdier. Vi må opprette den ved hjelp av en beregnet kolonne og DAX (Data Analysis Expressions).

Det er fint når dataene i datamodellen har alle feltene som er nødvendige for å opprette relasjoner, og fletter sammen data for å visualisere i Power View eller pivottabeller. Tabeller er imidlertid ikke alltid så samarbeidsvillige, så den neste delen beskriver hvordan du oppretter en ny kolonne ved hjelp av DAX, som kan brukes til å opprette en relasjon mellom tabeller.

Utvide datamodellen ved hjelp av beregnede kolonner

Hvis du vil opprette en relasjon mellom Verter-tabellen og datamodellen, og dermed utvide datamodellen for å inkludere vertstabellen , må verter ha et felt som identifiserer hver rad unikt. I tillegg må dette feltet samsvare med et felt i datamodellen. De tilsvarende feltene, ett i hver tabell, er det som gjør at tabellenes data kan knyttes sammen.

Siden Verter-tabellen ikke har et slikt felt, må du opprette det. Hvis du vil beholde integriteten til datamodellen, kan du ikke bruke Power Pivot til å redigere eller slette eksisterende data. Du kan imidlertid opprette nye kolonner ved hjelp av beregnede felt 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 krever en ny, beregnet kolonne for å oppfylle kravene som er nødvendige for å etablere en relasjon.

I Verter kan vi opprette en unik beregnet kolonne ved å kombinere Utgave-feltet (året for OL-arrangementet) og sesongfeltet (sommer eller vinter). I Medaljer-tabellen er det også et utgavefelt og et sesongfelt, så hvis vi oppretter en beregnet kolonne i hver av disse tabellene som kombinerer feltene Utgave og Sesong, kan vi etablere en relasjon mellom verter og medaljer. Følgende skjermbilde viser Verter-tabellen , med feltene Utgave og Sesong valgt

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 kontekstanalyse 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. Velg HOME > Vis > datavisning i Power Pivot for å sikre at datavisning er valgt, i stedet for å være i diagramvisning.

2. Velg Verter-tabellen i Power Pivot. Ved siden av de eksisterende kolonnene finner du en tom kolonne med tittelen Legg til kolonne. Power Pivot gir kolonnen som en plassholder. Det finnes mange måter å legge til en ny kolonne i en tabell på i Power Pivot, hvorav den ene er å velge den tomme kolonnen som har tittelen Legg til kolonne.
   
   
   

3. Skriv inn følgende DAX-formel på formellinjen. FUNKSJONEN KJEDE.SAMMEN kombinerer to eller flere felter til ett. Mens du skriver, hjelper Autofullfør deg med å skrive inn de fullstendige navnene på kolonner og tabeller, og viser funksjonene som er tilgjengelige. Bruk fanen til å velge Autofullfør-forslag. Du kan også bare klikke kolonnen mens du skriver inn formelen, og Power Pivot setter inn kolonnenavnet 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 – så vi har opprettet et felt som identifiserer hver rad i Verter-tabellen unikt. Slike felt kalles en primærnøkkel.

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

Verter-tabellen er klar. La oss deretter opprette en beregnet kolonne i Medaljer som samsvarer med formatet til EditionID-kolonnen vi opprettet i Verter, slik at vi kan opprette en relasjon mellom dem.

1. Begynn med å opprette en ny kolonne i Medaljer-tabellen , slik vi gjorde for verter. Velg Medaljer-tabellen i Power Pivot, og klikk Utform > kolonner > Legg til. Legg merke til at Legg til kolonne er valgt. Dette har samme effekt som bare å velge Legg til kolonne.

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

   = YEAR([Edition])
   

3. Når du er ferdig med å bygge formelen, trykker du ENTER. Verdier fylles ut for alle radene i den beregnede kolonnen, basert på formelen du skrev inn. Hvis du sammenligner denne kolonnen med Utgave-kolonnen i Verter, ser du at disse kolonnene har samme format.

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

5. Da du opprettet en ny kolonne, la Power Pivot til en annen plassholderkolonne kalt Legg til kolonne. Deretter ønsker vi å opprette den beregnede kolonnen EditionID, så velg Legg til kolonne. Skriv inn følgende DAX-formel på formellinjen, og trykk ENTER.
   
   =CONCATENATE([Year],[Season])

6. Gi nytt navn til kolonnen ved å dobbeltklikke CalculatedColumn1 og skrive inn EditionID.

7. Sorter kolonnen i stigende rekkefølge. Medaljer-tabellen i Power Pivot ser nå ut som følgende skjermbilde.
   
   
   

Legg merke til at mange verdier gjentas i Medals-tabellen EditionID-feltet. Det er greit og forventet, siden under hver utgave av OL (nå representert av EditionID-verdien) ble mange medaljer tildelt. Det som er unikt i Medaljer-tabellen , er hver tildelte medalje. Den unike identifikatoren for hver post i Medaljer-tabellen , og den angitte primærnøkkelen, er MedalKey-feltet.

Det neste trinnet er å opprette en relasjon mellom verter og medaljer.

Opprette en relasjon ved hjelp av beregnede kolonner

La oss deretter bruke de beregnede kolonnene vi opprettet for å etablere en relasjon mellom verter og medaljer.

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

2. Utvid verter slik at du kan vise alle feltene. Vi opprettet EditionID-kolonnen for å fungere som primærnøkkelen for vertstabellen (unikt, ikke gjentatt felt), og opprettet en EditionID-kolonne i Medaljer-tabellen for å muliggjøre oppretting av en relasjon mellom dem. Vi må finne begge, og opprette en relasjon. Power Pivot har en Finn-funksjon på båndet, slik at du kan søke etter tilsvarende felt i datamodellen. Følgende skjermbilde viser søk etter metadata-vinduet , med EditionID angitt i Søk etter-feltet .
   

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

4. Dra EditionID-kolonnen i Medaljer til EditionID-kolonnen i Verter. Power Pivot oppretter en relasjon mellom tabellene basert på EditionID-kolonnen, og tegner en linje mellom de to kolonnene, som angir relasjonen.
   
   

I denne delen lærte du en ny teknikk for å legge til nye kolonner, opprettet en beregnet kolonne ved hjelp av DAX, og brukte denne kolonnen til å etablere en ny relasjon mellom tabeller. Verter-tabellen er nå integrert i datamodellen, og dataene er tilgjengelige for 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 inkluderer data som er hierarkisk i seg selv. Vanlige eksempler omfatter kalenderdata, geografiske data og produktkategorier. Det er nyttig å opprette hierarkier i Power Pivot fordi du kan dra ett element til en rapport – hierarkiet – i stedet for å måtte sette sammen og ordne de samme feltene om og om igjen.

OL-dataene er også hierarkiske. Det er nyttig å forstå OL-hierarkiet, når det gjelder sport, disipliner og arrangementer. For hver idrett er det én eller flere tilknyttede disipliner (noen ganger er det mange). Og for hver disiplin er det én eller flere hendelser (noen ganger er det mange hendelser i hver disiplin). Bildet nedenfor illustrerer hierarkiet.

I denne delen oppretter du to hierarkier i de olympiske dataene du har brukt i denne opplæringen. Deretter bruker du disse hierarkiene til å se hvordan hierarkier gjør organisering av data enkelt i pivottabeller, og i en senere opplæring i Power View.

Opprette et sporthierarki

1. Bytt til diagramvisning i Power Pivot. Utvid Hendelser-tabellen slik at det blir enklere å se alle feltene.

2. Trykk og hold nede CTRL, og klikk feltene Idrett, Disiplin og Hendelse. Når de tre feltene er valgt, høyreklikker du og velger Opprett hierarki. En overordnet hierarkinode, Hierarki 1, opprettes nederst i tabellen, og de valgte kolonnene kopieres under hierarkiet som underordnede noder. Kontroller at Idrett vises først i hierarkiet, deretter Disiplin og deretter Hendelse.

3. Dobbeltklikk tittelen, Hierarki1, og skriv inn SDE for å gi nytt navn til det nye hierarkiet. Du har nå et hierarki som inkluderer sport, disiplin og arrangement. Hendelser-tabellen ser nå ut som følgende skjermbilde.
   
   

Opprette et plasseringshierarki

1. Når du fortsatt er i diagramvisning i Power Pivot, velger du Verter-tabellen og klikker Opprett hierarki-knappen i tabelloverskriften, som vist i følgende skjermbilde.
   
   
   En tom overordnet hierarkinode vises nederst i tabellen.

2. Skriv inn Plasseringer som navn for det nye hierarkiet.

3. Det finnes mange måter å legge til kolonner i et hierarki på. Dra feltene Season, City og NOC_CountryRegion til hierarkinavnet (i dette tilfellet Plasseringer) til hierarkinavnet er uthevet, og slipp deretter for å legge dem til.

4. Høyreklikk EditionID, og velg Legg til i hierarki. Velg plasseringer.

5. Kontroller at de underordnede hierarkinodene er i orden. Fra topp til bunn skal rekkefølgen være: Season, NOC, City, EditionID. Hvis underordnede noder ikke er i riktig rekkefølge, drar du dem ganske enkelt til riktig rekkefølge i hierarkiet. Tabellen skal se ut som følgende skjermbilde.
   

Datamodellen har nå hierarkier som kan brukes i rapporter. I neste del lærer du hvordan disse hierarkiene kan gjøre rapportopprettingen raskere og mer konsekvent.

Bruke hierarkier i pivottabeller

Nå som vi har et sportshierarki og plasseringshierarki, kan vi legge dem til i pivottabeller eller Power View, og raskt få resultater som inkluderer nyttige grupperinger av data. Før du opprettet hierarkier, måtte du legge til individuelle felt i pivottabellen og ordne disse feltene slik du ville at de skulle vises.

I denne delen bruker du hierarkiene som ble opprettet i forrige del, til raskt å finjustere pivottabellen. Deretter oppretter du den samme pivottabellvisningen ved hjelp av de individuelle feltene i hierarkiet, slik at du kan sammenligne bruk av hierarkier med å bruke individuelle felt.

1. Gå tilbake til Excel.

2. Fjern feltene fra RAD-området i pivottabellfelt i Ark1, og fjern deretter alle feltene fra KOLONNE-området. Kontroller at pivottabellen er valgt (som nå er ganske liten, slik at du kan velge celle A1 for å sikre at pivottabellen er valgt). De eneste gjenværende feltene i pivottabellfeltene er Medalje i FILTERS-området og Antall medaljer i VERDIER-området. Den nesten tomme pivottabellen skal se ut som følgende skjermbilde.

   

3. Dra SDE fra Hendelser-tabellen fra Pivottabellfelt-området til RAD-området. Deretter drar du Plasseringer fra Verter-tabellen til KOLONNE-området . Bare ved å dra disse to hierarkiene fylles pivottabellen ut med mye data, som alle er ordnet i hierarkiet du definerte i de forrige trinnene. Skjermen skal se ut som følgende skjerm.
   

4. La oss filtrere dataene litt, og bare se de første ti radene med hendelser. Klikk pilen i radetiketter i pivottabellen, klikk (Merk alle) for å fjerne alle merkede områder, og klikk deretter boksene ved siden av de ti første idrettene. Pivottabellen ser nå ut som følgende skjermbilde.
   

5. Du kan utvide alle disse idrettene i pivottabellen, som er det øverste nivået i SDE-hierarkiet, og se informasjon på neste nivå ned i hierarkiet (). Hvis det finnes et lavere nivå i hierarkiet for denne disiplinen, kan du utvide disiplinen for å se hendelsene. Du kan gjøre det samme for plasseringshierarkiet, der det øverste nivået er Sesong, som vises som sommer og vinter i pivottabellen. Når vi utvider Aquatics sport, ser vi alle sine barn disiplin elementer og deres data. Når vi utvider dykking disiplin under Aquatics, ser vi sine barn hendelser også, som vist i følgende skjerm. Vi kan gjøre det samme for Vannpolo, og se at det bare har én hendelse.
   

Ved å dra disse to hierarkiene opprettet du raskt en pivottabell med interessante og strukturerte data som du kan drille inn i, filtrere og ordne.

La oss nå opprette den samme pivottabellen, uten fordelen av hierarkier.

1. Fjern plasseringer fra KOLONNER-området i pivottabellfeltområdet. Fjern deretter SDE fra RAD-området. Du er tilbake til en grunnleggende pivottabell.

2. Dra Season, City, NOC_CountryRegion og EditionID til COLUMNS-området fra Verter-tabellen , og ordne dem i den rekkefølgen, fra topp til bunn.

3. Dra Sport, Disiplin og Hendelse fra Hendelser-tabellen til RADER-området, og ordne dem i den rekkefølgen, fra topp til bunn.

4. Filtrer radetiketter til de ti beste idrettene i pivottabellen.

5. Skjul alle radene og kolonnene, og utvid deretter Vann- og vannpolo. Arbeidsboken ser ut som følgende skjermbilde.
   

Skjermen ser lik ut, bortsett fra at du har dratt sju individuelle felt til pivottabellfeltområdene , 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 oppretting av hierarkier bare virke praktisk. Men når mange personer oppretter rapporter, og må finne ut riktig rekkefølge av felt for å få visningene riktige, blir hierarkier raskt en produktivitetsforbedring og muliggjør konsekvens.

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

Kontrollpunkt og test

Gå gjennom det du har lært

Excel-arbeidsboken har nå en datamodell som inneholder data fra flere kilder, relatert ved hjelp av eksisterende felt og beregnede kolonner. Du har også hierarkier som gjenspeiler strukturen til dataene i tabellene, noe som gjør det raskt, konsekvent og enkelt å opprette overbevisende rapporter.

Du lærte at ved å opprette hierarkier kan du angi den innebygde strukturen i dataene, og raskt bruke hierarkiske data i rapportene.

I den neste opplæringen i denne serien oppretter du visuelt overbevisende rapporter om olympiske medaljer ved hjelp av Power View. Du utfører også flere beregninger, optimaliserer data for rask oppretting av rapporter og importerer tilleggsdata 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 hva du har lært? Her er din sjanse. Følgende test belyser funksjoner eller krav du har lært om i denne opplæringen. Nederst på siden finner du svarene. Lykke til!

Spørsmål 1: Hvilke av følgende visninger lar deg opprette relasjoner mellom to tabeller?

Svar: Du oppretter relasjoner mellom tabeller i Power View.

B: Du oppretter relasjoner mellom tabeller ved hjelp av utformingsvisning i Power Pivot.

C: Du oppretter relasjoner mellom tabeller ved hjelp av rutenettvisning i Power Pivot

D: Alle svarene ovenfor

Spørsmål 2: SANN eller USANN: Du kan etablere relasjoner mellom tabeller basert på en unik identifikator som opprettes ved hjelp av DAX-formler.

SVAR: SANN

B: USANN

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

Svar: I beregningsområdet i Power Pivot.

B: I en ny kolonne i Power Pivot f.

C: I en hvilken som helst celle i Excel 2013.

D: Både A og B.

Spørsmål 4: Hvilken av følgende er sann om hierarkier?

Svar: Når du oppretter et hierarki, er de inkluderte feltene ikke lenger tilgjengelige individuelt.

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

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

D: Du kan ikke 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