Selvstudium: Udvide datamodelrelationer ved hjælp af Excel, Power Pivot og DAX

Resumé: Dette er det andet selvstudium i en serie. I det første selvstudium, Importere data til og oprette en datamodel, blev der oprettet en Excel-projektmappe ved hjælp af data, der er importeret fra flere kilder.

I dette selvstudium kommer du til at bruger Power Pivot til at udvide datamodellen, oprette hierarkier og opbygge beregnede felter ud fra eksisterende data til oprettelse af nye relationer mellem tabeller.

Selvstudiet har følgende afsnit:

• Tilføje en relation ved hjælp af diagramvisning i Power Pivot
• Udvide datamodellen ved hjælp af beregnede kolonner
• Oprette et hierarki
• Brug hierarkier i pivottabeller
• Kontrolpunkt og quiz

I slutningen af selvstudiet er der en quiz, du kan tage for at teste, hvad du har lært.

Serien bruger data, der beskriver olympiske medaljer, værtsnationer og forskellige olympiske sportsbegivenheder. Følgende selvstudier indgår i serien:

1. Importér data til Excel, og opret en datamodel
2. Udvide datamodelrelationer ved hjælp af Excel, Power Pivot og DAX
3. Oprette kortbaserede Power View-rapporter
4. Inkorporere internetdata, og Konfigurere standardindstillinger for Power View-rapporter
5. Hjælp til Power Pivot
6. Oprette imponerende Power View-rapporter - Del 2

Vi anbefaler, at du gennemfører selvstudierne i rækkefølge.

Disse selvstudier bruger Excel 2013 med Power Pivot aktiveret. Klik her for at få flere oplysninger om Excel 2013. Klik her for at få en vejledning i aktivering af Power Pivot.

Tilføje en relation ved hjælp af diagramvisning i Power Pivot

I dette afsnit skal du bruge tilføjelsesprogrammet Microsoft Office PowerPivot i Excel 2013 til at udvide modellen. Brug af diagramvisning i Microsoft SQL Server Power Pivot til Excel gør det nemt at oprette relationer. Først skal du sikre dig, at tilføjelsesprogrammet Power Pivot er aktiveret.

Bemærk! Tilføjelsesprogrammet Power Pivot i Microsoft Excel 2013 er en del af Office Professional Plus. Se Start Power Pivot i tilføjelsesprogrammet Microsoft Excel 2013, hvis du vil have mere at vide.

Føj Power Pivot til Excel-båndet ved at aktivere tilføjelsesprogrammet Power Pivot

Når Power Pivot er aktiveret, kan du se en båndfane i Excel 2013 kaldet POWER PIVOT. Følg disse trin for at aktivere Power Pivot.

1. Gå til Tilføjelsesprogrammer med FILINDSTILLINGER >>.
2. I feltet Administrer nær bunden skal du klikke på COM Add-ins> Go.
3. Markér afkrydsningsfeltet Microsoft Office PowerPivot i Microsoft Excel 2013, og klik derefter på OK.

Excel-båndet har nu fanen POWER PIVOT .

Tilføje en relation ved hjælp af diagramvisning i Power Pivot

Excel-projektmappen indeholder en tabel med navnet Værter. Vi importerede Hosts ved at kopiere dem og indsætte dem i Excel og derefter formatere dataene som en tabel. For at tilføje tabellen Hosts til datamodellen skal vi etablere en relation. Lad os bruge Power Pivot til visuelt at repræsentere relationerne i datamodellen og derefter oprette relationen.

1. Klik på fanen Værter i Excel for at gøre den til det aktive ark.

2. Markér POWER PIVOT-tabeller >> som føjes til datamodel på båndet. I dette trin føjes tabellen Hosts til datamodellen. Det åbner også tilføjelsesprogrammet Power Pivot, som du bruger til at udføre de øvrige trin i denne opgave.

3. Bemærk, at Power Pivot-vinduet viser alle tabellerne i modellen, herunder værter. Klik gennem et par af tabellerne. I Power Pivot kan du få vist alle de data, som modellen indeholder, selvom de ikke vises i nogen regneark i Excel, f.eks. dataene for discipliner, begivenheder og medaljer nedenfor samt S_Teams, W_Teams og sportsgrene.

   

4. Klik på Diagramvisning i sektionen Vis i Power Pivot-vinduet.

5. Brug skyderen til at ændre diagrammets størrelse, så du kan se alle objekter i diagrammet. Omarranger tabellerne ved at trække i deres titellinje, så de er synlige og placeret ved siden af hinanden. Bemærk, at fire tabeller ikke har relation til resten af tabellerne: Værter, Begivenheder, W_Teams og S_Teams.

   

6. Du har bemærket, at både tabellen Medaljer og tabellen Begivenheder har et felt med navnet DisciplinBegivenhed. Ved nærmere inspektion finder du ud af, at feltet DisciplineEvent i tabellen Events består af entydige værdier, der ikke gentages.

1. Oprette en relation mellem tabellen Medals og tabellen Begivenheder . Træk feltet DisciplineEvent fra tabellen Events til feltet DisciplineEvent i Medals, når du er i diagramvisning. Der vises en streg mellem dem, som angiver, at der er etableret en relation.

2. Klik på den linje, der forbinder Begivenheder og Medaljer. De fremhævede felter definerer relationen, som vist på følgende skærmbillede.

   

3. Hvis vi skal forbinde Hosts til datamodellen, skal vi bruge et felt med værdier, som entydigt identificerer hver række i tabellen Hosts . Derefter kan vi søge i vores datamodel for at se, om de samme data findes i en anden tabel. Ved at kigge i diagramvisning kan vi ikke gøre dette. Skift tilbage til Datavisning, mens Værtsværdier er valgt.

4. Efter at have undersøgt kolonnerne indser vi, at Værter ikke har en kolonne med entydige værdier. Vi er nødt til at oprette den ved hjælp af en beregnet kolonne og DAX (Data Analysis Expressions).

Det er godt, når dataene i din datamodel indeholder alle de felter, der er nødvendige for at oprette relationer og indsamle data, der skal visualiseres i Power View eller pivottabeller. Men tabeller er ikke altid så samarbejdsvillige, så i næste afsnit beskrives det, hvordan du opretter en ny kolonne ved hjælp af DAX, der kan bruges til at oprette en relation mellem tabeller.

Udvide datamodellen ved hjælp af beregnede kolonner

Hvis du vil etablere en relation mellem tabellen Hosts og datamodellen og dermed udvide vores datamodel til at omfatte tabellen Hosts , skal Hosts have et felt, der entydigt identificerer hver række. Desuden skal dette felt svare til et felt i datamodellen. Disse tilsvarende felter, ét i hver tabel, er det, der gør det muligt at tilknytte tabellernes data.

Da tabellen Hosts ikke indeholder et sådant felt, skal du oprette det. For at bevare datamodellens integritet kan du ikke bruge Power Pivot til at redigere eller slette eksisterende data. Du kan dog oprette nye kolonner ved at bruge beregnede felter, der er baseret på de eksisterende data.

Ved at kigge i tabellen Hosts og derefter se på andre tabeller i datamodeller, finder vi en god kandidat til et entydigt felt, som vi kan oprette i Hosts og derefter tilknytte det til en tabel i datamodellen. Begge tabeller kræver en ny, beregnet kolonne for at opfylde de nødvendige krav til at etablere en relation.

Under Værter kan vi oprette en entydig beregnet kolonne ved at kombinere feltet Udgave (året for OL) og feltet Sæson (sommer eller vinter). I tabellen Medals er der også felterne Edition og Season, så hvis vi opretter en beregnet kolonne i hver af disse tabeller, der kombinerer felterne Edition og Season, kan vi etablere en relation mellem Hosts og Medals. Følgende skærmbillede viser tabellen Hosts med felterne Edition og Season markeret

        

Oprette beregnede kolonner ved hjælp af DAX

Lad os starte med tabellen Værtsbyer . Målet er at oprette en beregnet kolonne i tabellen Værtsnationer og derefter i tabellen Medaljer , som kan bruges til at etablere en relation mellem dem.

I Power Pivot kan du bruge DAX (Data Analysis Expressions) til at oprette beregninger. DAX er et formelsprog til Power Pivot og pivottabeller. Det er udviklet til relationelle data og kontekstanalyse i Power Pivot. Du kan oprette DAX-formler i en ny Power Pivot-kolonne og i området Beregning i Power Pivot.

1. I Power Pivot skal du vælge HJEM > Vis > datavisning for at sikre, at Datavisning er valgt i stedet for at være i diagramvisning.

2. Vælg tabellen Værtsværdier i Power Pivot. Ved siden af de eksisterende kolonner er der en tom kolonne med titlen Tilføj kolonne. Power Pivot giver den pågældende kolonne som en pladsholder. Der er mange måder at føje en ny kolonne til en tabel i Power Pivot på, herunder blot at markere den tomme kolonne med titlen Tilføj kolonne.

   

3. Indtast følgende DAX-formel på formellinjen. Funktionen SAMMENKÆDE kombinerer to eller flere felter i ét. Mens du skriver, hjælper Autofuldførelse dig med at skrive fuldstændige navne på kolonner og tabeller og viser de tilgængelige funktioner. Brug fanen til at vælge forslag til autofuldførelse. Du kan også bare klikke på kolonnen, mens du skriver formlen, og Power Pivot indsætter kolonnenavnet i formlen.

   =CONCATENATE([Edition],[Season])

4. Tryk på Enter, når du er færdig med at oprette formlen, for at acceptere den.

5. Alle rækkerne i den beregnede kolonne udfyldes med værdier. Hvis du ruller ned i tabellen, kan du se, at hver række er entydig. Derfor har vi oprettet et felt, der entydigt identificerer hver række i tabellen Hosts . Disse felter kaldes en primær nøgle.

6. Lad os omdøbe den beregnede kolonne til Udgave-id. Du kan omdøbe en hvilken som helst kolonne ved at dobbeltklikke på den eller ved at højreklikke på kolonnen og vælge Omdøb kolonne. Når du er færdig, ser tabellen Hosts i Power Pivot ud som på følgende skærmbillede.

   

Tabellen over værtsværdier er klar. Lad os nu oprette en beregnet kolonne i Medals , der matcher formatet for den kolonne EditionID, vi oprettede i Værter, så vi kan oprette en relation mellem dem.

1. Start med at oprette en ny kolonne i tabellen Medaljer , som vi gjorde for Værter. Markér tabellen Medals i Power Pivot, og klik på Tilføj designkolonner >>. Bemærk, at Tilføj kolonne er markeret. Dette har samme effekt som blot at vælge Tilføj kolonne.

2. Kolonnen Udgave i Medals har et andet format end kolonnen Udgave i Værter. Før vi kombinerer, eller sammenkæder, kolonnen Edition med kolonnen Season for at oprette kolonnen EditionID, skal vi oprette et mellemliggende felt, der får Edition i det rigtige format. Skriv følgende DAX-formel på formellinjen over tabellen.

   = YEAR([Edition])
   
   

3. Tryk på Enter, når du er færdig med at oprette formlen. Alle rækkerne i den beregnede kolonne udfyldes med værdier baseret på den formel, du har angivet. Hvis du sammenligner denne kolonne med kolonnen Udgave i Værter, kan du se, at disse kolonner har samme format.

4. Omdøb kolonnen ved at højreklikke på CalculatedColumn1 og vælge Omdøb kolonne. Skriv Year, og tryk derefter på Enter.

5. Da du oprettede en ny kolonne, tilføjede Power Pivot endnu en pladsholderkolonne med navnet Tilføj kolonne. Derefter vil vi oprette den beregnede kolonne EditionID, så vælg Tilføj kolonne. Skriv følgende DAX-formel på formellinjen, og tryk på Enter.

   =CONCATENATE([Year],[Season])

6. Omdøb kolonnen ved at dobbeltklikke på CalculatedColumn1 og skrive EditionID.

7. Sortér kolonnen i stigende rækkefølge. Tabellen Medals i Power Pivot ser nu ud som på følgende skærmbillede.

   

Bemærk, at mange værdier gentages i feltet EditionID-udgave i tabellen Medals . Det er okay og forventet, da der under hver udgave af OL (nu repræsenteret af EditionID-værdien) blev tildelt mange medaljer. Det, der er unikt i tabellen over medaljer , er hver tildelt medalje. Det entydige id for hver post i tabellen Medaljer og den udpegede primære nøgle er feltet Medaljenøgle .

Det næste trin er at oprette en relation mellem Værter og Medaljer.

Oprette en relation ved hjælp af beregnede kolonner

Lad os derefter bruge de beregnede kolonner, vi oprettede, til at etablere en relation mellem Værter og Medaljer.

1. I Power Pivot-vinduet skal du vælge Diagramvisning for startside >> på båndet. Du kan også skifte mellem gittervisning og diagramvisning ved hjælp af knapperne nederst i PowerView-vinduet som vist på følgende skærmbillede.

   

2. Udvid Værter , så du kan få vist alle felterne i den. Vi har oprettet kolonnen EditionID, så den fungerer som primær nøgle for tabellen Hosts (entydigt, ikke-gentaget felt), og vi har oprettet kolonnen EditionID i tabellen Medals for at gøre det muligt at etablere en relation mellem dem. Vi er nødt til at finde dem begge og skabe en relation. PowerPivot indeholder funktionen Søg på båndet, så du kan søge efter tilsvarende felter i datamodellen. Følgende skærmbillede viser vinduet Find metadata med EditionID angivet i feltet Søg efter .
   

3. Placer tabellen Hosts , så den er ud for Medals.

4. Træk kolonnen Udgave-id i Medals til kolonnen Udgave-id i Værter. Power Pivot opretter en relation mellem tabellerne baseret på kolonnen EditionID og trækker en linje mellem de to kolonner, der angiver relationen.

   

I dette afsnit har du lært en ny teknik til at tilføje nye kolonner, oprettet en beregnet kolonne ved hjælp af DAX og brugt denne kolonne til at oprette en ny relation mellem tabeller. Tabellen Hosts er nu integreret i datamodellen, og dens data er tilgængelige for pivottabellen i Ark1. Du kan også bruge de tilknyttede data til at oprette flere pivottabeller, pivotdiagrammer, Power View-rapporter og meget mere.

Oprette et hierarki

De fleste datamodeller indeholder data, der er hierarkiske. Almindelige eksempler kan være kalenderdata, geografiske data og produktkategorier. Det er nyttigt at oprette hierarkier i Power Pivot, fordi du kan trække et element til en rapport – hierarkiet – i stedet for at skulle samle og arrangere de samme felter igen og igen.

Dataene for OL er også hierarkiske. Det er nyttigt at forstå OL-hierarkiet, hvad angår sportsgrene, discipliner og begivenheder. For hver sportsgren er der en eller flere tilknyttede discipliner (nogle gange er der mange). Og for hver disciplin er der en eller flere begivenheder (igen, nogle gange er der mange begivenheder i hver disciplin). Følgende billede illustrerer hierarkiet.

I dette afsnit skal du oprette to hierarkier inden for de olympiske data, du har brugt i dette selvstudium. Du kan derefter bruge disse hierarkier til at se, hvordan hierarkier gør det nemt at organisere data i pivottabeller og, i et efterfølgende selvstudium, i Power View.

Opret et sportshierarki

1. Skift til diagramvisning i Power Pivot. Udvid tabellen Hændelser , så du lettere kan se alle felterne.

2. Tryk på og hold Ctrl nede, mens du klikker på felterne Sports, Disciplin og Begivenhed. Når disse tre felter er markeret, skal du højreklikke og vælge Opret hierarki. En overordnet hierarkinode, Hierarchy 1, oprettes nederst i tabellen, og de markerede kolonner kopieres under hierarkiet som underordnede noder. Kontrollér, at Sports vises først i hierarkiet, derefter Disciplin og derefter Begivenhed.

3. Dobbeltklik på titlen Hierarchy1, og skriv SDE for at omdøbe dit nye hierarki. Du har nu et hierarki, der omfatter Sports, Disciplin og Begivenhed. Din Hændelsestabel ser nu ud som på følgende skærmbillede.

   

Oprette et placeringshierarki

1. Vælg tabellen Værtsgener , mens du stadig er i diagramvisning i Power Pivot, og klik på knappen Opret hierarki i tabeloverskriften, som vist på følgende skærmbillede.
   

   Der vises en tom, overordnet hierarkinode nederst i tabellen.

2. Skriv Placeringer som navnet på det nye hierarki.

3. Der er mange måder at føje kolonner til et hierarki på. Træk felterne Season, By og NOC_CountryRegion til hierarkinavnet (i dette tilfælde Placeringer), indtil hierarkinavnet er fremhævet. Slip derefter for at tilføje dem.

4. Højreklik på EditionID, og vælg Føj til hierarki. Vælg Placeringer.

5. Sørg for, at dine underordnede hierarkinoder er i rækkefølge. Fra top til bund skal rækkefølgen være: Season, NOC, City, EditionID. Hvis dine underordnede noder er ude af rækkefølge, skal du blot trække dem til den relevante rækkefølge i hierarkiet. Tabellen bør se ud som på følgende skærmbillede.
   

Datamodellen har nu hierarkier, der kan bruges i rapporter. I næste afsnit får du at vide, hvordan disse hierarkier kan gøre oprettelse af rapporter hurtigere og mere ensartet.

Brug hierarkier i pivottabeller

Nu, hvor vi har et sportshierarki og et placeringshierarki, kan vi føje dem til pivottabeller eller Power View og hurtigt hente resultater, der indeholder nyttige grupperinger af data. Før du oprettede hierarkier, skulle du føje individuelle felter til pivottabellen og arrangere disse felter, sådan som du ville have dem vist.

I denne sektion bruger du de hierarkier, der er oprettet i forrige afsnit, til hurtigt at finjustere din pivottabel. Derefter opretter du den samme pivottabelvisning ved hjælp af de individuelle felter i hierarkiet, så du kan sammenligne brug af hierarkier med brug af individuelle felter.

1. Gå tilbage til Excel.
2. I Ark1 skal du fjerne felterne fra området RÆKKER i Pivottabelfelter og derefter fjerne alle felterne fra området KOLONNER. Sørg for, at pivottabellen er markeret (som nu er ret lille, så du kan vælge celle A1 for at sikre, at pivottabellen er markeret). De eneste resterende felter i pivottabelfelterne er Medal i området FILTRE og Antal medaljer i området VÆRDIER. Din næsten tomme pivottabel bør se ud som på følgende skærmbillede.
   
3. Træk SDE fra pivottabelfeltområdet til området RÆKKER. Træk derefter Placeringer fra tabellen Hosts til området KOLONNER . Bare ved at trække i disse to hierarkier er din pivottabel udfyldt med en masse data, som alle er arrangeret i det hierarki, du definerede i de forrige trin. Skærmbilledet bør se ud som på følgende skærmbillede.
   
4. Lad os filtrere dataene lidt og kun se de første ti rækker med hændelser. Klik på pilen i Rækkenavne i pivottabellen, klik på (Markér alle) for at fjerne alle markeringer, og klik derefter på felterne ud for de første ti sportsgrene. Din pivottabel ser nu ud som på følgende skærmbillede.
   
5. Du kan udvide enhver af disse sportsgrene i pivottabellen, som er det øverste niveau i SDE-hierarkiet, og se oplysninger på næste niveau nede i hierarkiet (disciplin). Hvis der findes et lavere niveau i hierarkiet for det pågældende, kan du udvide disciplinen for at se dens begivenheder. Du kan gøre det samme for placeringshierarkiet, hvor det øverste niveau er Årstid, hvilket vises som Sommer og Vinter i pivottabellen. Når vi udvider Aquatics-sporten, ser vi alle dens børnedisciplinelementer og deres data. Når vi udvider disciplinen Dykning under Vand, ser vi også dens underordnede begivenheder, som vist på følgende skærmbillede. Vi kan gøre det samme for vandpolo og se, at der kun er én begivenhed.
   

Ved at trække i disse to hierarkier oprettede du hurtigt en pivottabel med interessante og strukturerede data, som du kan analysere i, filtrere og arrangere.

Lad os oprette den samme pivottabel uden fordelen ved hierarkier.

1. Fjern placeringer fra området KOLONNER i området Pivottabelfelter. Fjern derefter SDE fra området RÆKKER. Du har nu fået en grundlæggende pivottabel igen.
2. Fra tabellen Hosts skal du trække Season, City, NOC_CountryRegion og EditionID til området KOLONNER og arrangere dem i den rækkefølge fra top til bund.
3. Fra tabellen Begivenheder skal du trække Sports, Disciplin og Begivenhed til området RÆKKER og arrangere dem i den rækkefølge fra top til bund.
4. I pivottabellen skal du filtrere rækkenavne til de ti mest populære sportsgrene.
5. Skjul alle rækker og kolonner, og udvid derefter Vandsport, derefter Dykning og vandpolo . Projektmappen ser ud som på følgende skærmbillede.
   

Skærmbilledet ligner hinanden, bortset fra at du har trukket syv individuelle felter til områderne Pivottabelfelter i stedet for blot at trække to hierarkier. Hvis du er den eneste person, der opretter pivottabeller eller Power View-rapporter baseret på disse data, kan oprettelse af hierarkier virke praktisk. Men når mange personer opretter rapporter og skal finde den rigtige rækkefølge af felter for at få de korrekte visninger, bliver hierarkier hurtigt til en produktivitetsforbedring og sikrer ensartethed.

I et andet selvstudium lærer du at bruge hierarkier og andre felter i visuelt flotte rapporter, der er oprettet ved hjælp af Power View.

Kontrolpunkt og quiz

Gennemgå det, du har lært

Excel-projektmappen har nu en datamodel, der indeholder data fra flere kilder, relaterede ved hjælp af eksisterende felter og beregnede kolonner. Du har også hierarkier, der afspejler strukturen af data i tabellerne, hvilket gør det hurtigt, ensartet og nemt at oprette overbevisende rapporter.

Du har lært, at når du opretter hierarkier, kan du angive den iboende struktur i dine data og hurtigt bruge hierarkiske data i dine rapporter.

I det næste selvstudium i serien opretter du visuelt overbevisende rapporter om olympiske medaljer ved hjælp af Power View. Du kan også foretage flere beregninger, optimere data, så du hurtigt kan oprette rapporter, og du importerer yderligere data for at gøre rapporterne endnu mere interessante. Her er en link:

Selvstudium 3: Oprette kortbaserede Power View-rapporter

QUIZ

Vil du se, hvor godt du husker det, du har lært? Nu har du chancen. Følgende quiz fremhæver de funktioner, muligheder og krav, du lærte om i selvstudiet. Du finder svarene nederst på siden. Held og lykke!

Spørgsmål 1: Hvilke af de følgende visninger giver dig mulighed for at oprette relationer mellem to tabeller?

Sv: Du opretter relationer mellem tabeller i Power View.

B: Du opretter relationer mellem tabeller ved hjælp af Designvisning i Power Pivot.

C: Du opretter relationer mellem tabeller med gittervisningen i Power Pivot

D: Alle ovenstående

Spørgsmål 2: SAND eller FALSK: Du kan oprette relationer mellem tabeller baseret på et entydigt id, der oprettes ved hjælp af DAX-formler.

Sv: SAND

B: FALSK

Spørgsmål 3: I hvilket af følgende kan du oprette en DAX-formel?

A: i beregningsområdet i Power Pivot.

B: I en ny kolonne i Power Pivotf.

C: I en vilkårlig celle i Excel 2013.

D: Både A og B.

Spørgsmål 4: Hvilket af følgende er sandt om hierarkier?

Sv: Når du opretter et hierarki, er de inkluderede felter ikke længere tilgængelige enkeltvis.

B: Når du opretter et hierarki, kan de inkluderede felter, herunder deres hierarki, bruges i klientværktøjer ved blot at trække hierarkiet til et Power View- eller pivottabelområde.

C: Når du opretter et hierarki, kombineres de underliggende data i datamodellen i ét felt.

D: Man kan ikke oprette hierarkier i Power Pivot.

Quiz-svar

1. Korrekt svar: D
2. Korrekt svar: A
3. Korrekt svar: D
4. Korrekt svar: B