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

Selvstudiet har følgende afsnit:

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

• Udvid 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. Udvid 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. Du kan få en vejledning i, hvordan du aktiverer Power Pivot, ved at klikke her.

Tilføje en relation med diagramvisning i Power Pivot

I dette afsnit skal du bruge tilføjelsesprogrammet Microsoft Office Power Pivot 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 det Power Pivot tilføjelsesprogram er aktiveret.

Bemærk! Tilføjelsesprogrammet Power Pivot i Microsoft Excel 2013 er en del af Office Professional Plus. Se Start Power Pivot i Microsoft Excel 2013-tilføjelsesprogram for at få flere oplysninger.

Føj Power Pivot til båndet i Excel 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 FILER > Indstillinger > tilføjelsesprogrammer.

2. I feltet Administrer nederst skal du klikke på COM-tilføjelsesprogrammer> Gå.

3. Markér afkrydsningsfeltet Microsoft Office Power Pivot i Microsoft Excel 2013, og klik derefter på OK.

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

Tilføje en relation med diagramvisning i Power Pivot

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

1. I Excel skal du klikke på fanen Værter for at gøre det til det aktive ark.

2. På båndet skal du vælge POWER PIVOT > Tabeller > Føj til datamodel. Dette trin føjer tabellen Hosts til datamodellen. Det åbner også det Power Pivot tilføjelsesprogram, som du bruger til at udføre de resterende trin i denne opgave.

3. Bemærk, at vinduet Power Pivot 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, også selvom de ikke vises i regneark i Excel, f.eks. dataene Discipliner, Begivenheder og Medaljer nedenfor, samt S_Teams,W_Teamsog Sport.
   
   

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

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

6. Du bemærker, at både tabellen Medals og tabellen Events har et felt med navnet DisciplineEvent. Efter yderligere inspektion afgør du, at feltet DisciplineEvent i tabellen Hændelser består af entydige værdier, der ikke gentages.

1. Opret en relation mellem tabellen Medals og tabellen Hændelser . Mens du er i diagramvisning, skal du trække feltet DisciplineEvent fra tabellen Events til feltet DisciplineEvent i Medals. Der vises en linje mellem dem, der angiver, at der er oprettet en relation.

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

3. For at forbinde værter til datamodellen skal vi bruge et felt med værdier, der 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. Hvis du kigger i diagramvisning, kan vi ikke gøre dette. Skift tilbage til Datavisning, når Værter er markeret.

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 rart, når dataene i din datamodel har alle de felter, der er nødvendige for at oprette relationer, og mikse data for at visualisere 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.

Udvid datamodellen ved hjælp af beregnede kolonner

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

Da tabellen Overværter ikke har et sådant felt, skal du oprette det. Hvis du vil bevare datamodellens integritet, kan du ikke bruge Power Pivot til at redigere eller slette eksisterende data. Du kan dog oprette nye kolonner ved hjælp af beregnede felter baseret på de eksisterende data.

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

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

Opret beregnede kolonner ved hjælp af DAX

Lad os starte med tabellen Hosts . Målet er at oprette en beregnet kolonne i tabellen Hosts og derefter i tabellen Medals , som kan bruges til at etablere en relation mellem dem.

I Power Pivotkan du bruge DAX (Data Analysis Expressions) til at oprette beregninger. DAX er et formelsprog til Power Pivot og pivottabeller, der er udviklet til relationelle data og kontekstafhængige analyser, der er tilgængelige i Power Pivot. Du kan oprette DAX-formler i en ny Power Pivot kolonne og i beregningsområdet i Power Pivot.

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

2. Vælg tabellen Hosts i Power Pivot. Ved siden af de eksisterende kolonner er en tom kolonne med titlen Tilføj kolonne. Power Pivot indeholder 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, hvoraf den ene er at vælge 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 de fulde navne på kolonner og tabeller og viser de funktioner, der er tilgængelige. Brug fanen til at vælge Autofuldførelsesforslag. Du kan også bare klikke på kolonnen, mens du skriver formlen, og Power Pivot indsætter kolonnenavnet i formlen.
   
   =CONCATENATE([Edition],[Season])

4. Når du er færdig med at opbygge formlen, skal du trykke på Enter for at acceptere den.

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

6. Lad os omdøbe den beregnede kolonne til EditionID. 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 Værter er klar. Lad os derefter oprette en beregnet kolonne i Medaljer , der svarer til formatet af kolonnen EditionID, vi har oprettet i Værter, så vi kan oprette en relation mellem dem.

1. Start med at oprette en ny kolonne i tabellen Medals , som vi gjorde for værter. I Power Pivot skal du vælge tabellen Medaljer og klikke på Design > kolonner > Tilføj. Bemærk, at Tilføj kolonne er markeret. Dette har samme effekt som blot at vælge Tilføj kolonne.

2. Kolonnen Udgave i Medaljer har et andet format end kolonnen Udgave i Værter. Før vi kombinerer eller sammenkæder kolonnen Udgave 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 opbygge formlen. Værdier udfyldes for alle rækkerne i den beregnede kolonne 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 en anden pladsholderkolonne kaldet Tilføj kolonne. Derefter vil vi oprette den beregnede EditionID-kolonne, 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 Medaljer i Power Pivot ser nu ud som på følgende skærmbillede.
   
   
   

Bemærk, at mange værdier gentages i feltet EditionID i tabellen Medals . Det er okay og forventet, da der i løbet af hver udgave af OL (nu repræsenteret ved EditionID værdi) mange medaljer blev tildelt. Hvad der er unikt i tabellen Medals er hver tildelt medalje. Det entydige id for hver post i tabellen Medals og den angivne primære nøgle er feltet MedalKey.

Næste trin er at oprette en relation mellem værter og medaljer.

Opret en relation ved hjælp af beregnede kolonner

Lad os derefter bruge de beregnede kolonner, vi har oprettet, til at etablere en relation mellem værter og medaljer.

1. I vinduet Power Pivot skal du vælge Hjem > Vis > Diagramvisning 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 se alle felterne. Vi har oprettet kolonnen EditionID for at fungere som den primære nøgle for tabellen Hosts (entydigt, ikke-gentaget felt) og oprettet en EditionID-kolonne 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. Power Pivot indeholder funktionen Søg på båndet, så du kan søge i datamodellen efter tilsvarende felter. Følgende skærmbillede viser vinduet Find metadata med EditionID angivet i feltet Søg efter .
   

3. Placer tabellen Hosts , så den er ved siden af Medaljer.

4. Træk kolonnen EditionID i Medals til kolonnen EditionID i Hosts. Power Pivot opretter en relation mellem tabellerne baseret på kolonnen EditionID og tegner en streg mellem de to kolonner, der angiver relationen.
   
   

I dette afsnit har du lært en ny metode til at tilføje nye kolonner, oprettet en beregnet kolonne ved hjælp af DAX og brugt den pågældende kolonne til at etablere 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 i sig selv 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 ét element til en rapport – hierarkiet – i stedet for at skulle samle og bestille de samme felter igen og igen.

Ol-dataene er også hierarkiske. Det er nyttigt at forstå ol-hierarkiet med hensyn til sport, discipliner og begivenheder. For hver sport 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 opretter du to hierarkier i 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 nemmere kan se alle dens felter.

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

3. Dobbeltklik på titlen, Hierarki1, og skriv SDE for at omdøbe det nye hierarki. Du har nu et hierarki, der indeholder Sport, Disciplin og Begivenhed. Tabellen Begivenheder ser nu ud som på følgende skærmbillede.
   
   

Opret et placeringshierarki

1. Mens du stadig er i diagramvisning i Power Pivot, skal du vælge tabellen Hosts og klikke 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, City og NOC_CountryRegion over på hierarkinavnet (i dette tilfælde Placeringer), indtil hierarkinavnet er fremhævet, og 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 ordren være: Sæson, 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 skal se ud som på følgende skærmbillede.
   

Din datamodel har nu hierarkier, der kan bruges godt i rapporter. I næste afsnit lærer du, hvordan disse hierarkier kan gøre oprettelsen af rapporten hurtigere og mere ensartet.

Brug hierarkier i pivottabeller

Nu hvor vi har hierarkiet Sports og Placeringer, kan vi føje dem til pivottabeller eller Power View og hurtigt få resultater, der indeholder nyttige grupper af data. Før du opretter hierarkier, skulle du føje individuelle felter til pivottabellen og arrangere disse felter, som du vil have dem vist.

I dette afsnit kan du bruge de hierarkier, der er oprettet i forrige afsnit, til hurtigt at afgrænse din pivottabel. Derefter opretter du den samme pivottabelvisning ved hjælp af de enkelte felter i hierarkiet, så du kan sammenligne ved hjælp af hierarkier til at bruge 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 ganske lille, så du kan vælge celle A1 for at sikre, at pivottabellen er markeret). De eneste resterende felter i pivottabelfelterne er Medalje i området FILTRE og Antal medaljer i området VÆRDIER. Din næsten tomme pivottabel skal se ud som på følgende skærmbillede.

   

3. Fra området Pivottabelfelter skal du trække SDE fra tabellen Hændelser til området RÆKKER. Træk derefter Placeringer fra tabellen Værter til området KOLONNER . Blot ved at trække disse to hierarkier udfyldes din pivottabel med en masse data, som alle er arrangeret i det hierarki, du har defineret i de forrige trin. Skærmen skal se ud som på følgende skærmbillede.
   

4. Lad os filtrere disse data lidt og blot se de første ti rækker af hændelser. Klik på pilen i Rækkenavne i pivottabellen, klik på (Markér alt) 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 en hvilken som helst af disse sportsgrene i pivottabellen, som er det øverste niveau i SDE-hierarkiet, og se oplysninger på det næste niveau ned i hierarkiet (disciplin). Hvis der findes et lavere niveau i hierarkiet for den pågældende disciplin, kan du udvide disciplinen for at se dens hændelser. Du kan gøre det samme for hierarkiet Placering, hvor det øverste niveau er Sæson, som vises som Sommer og Vinter i pivottabellen. Når vi udvider vandsporten, ser vi alle dens børnedisciplinelementer og deres data. Når vi udvider Dykning disciplin under Aquatics, vi ser dets barn begivenheder også, som vist på følgende skærm. Vi kan gøre det samme for Water Polo, og se, at det kun har én begivenhed.
   

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

Lad os nu oprette den samme pivottabel uden at drage fordel af hierarkier.

1. I området Pivottabelfelter skal du fjerne placeringer fra området KOLONNER. Fjern derefter SDE fra området RÆKKER. Du er tilbage til en grundlæggende pivottabel.

2. Træk Season, City, NOC_CountryRegion og EditionID fra tabellen Hosts til området COLUMNS, og arranger dem i den rækkefølge fra top til bund.

3. Træk Sportsgren, Disciplin og Begivenhed fra tabellen Begivenheder til området RÆKKER, og arranger dem i den rækkefølge fra top til bund.

4. I pivottabellen skal du filtrere rækkenavne til de ti øverste sportsgrene.

5. Skjul alle rækker og kolonner, udvid derefter Vand, derefter Dykning og Vandpolo . Projektmappen ser ud som på følgende skærmbillede.
   

Skærmen ser ens ud, 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 det kun virke praktisk at oprette hierarkier. Men når mange personer opretter rapporter og skal finde ud af, hvilken rækkefølge felterne skal have for at få visningerne korrekt, bliver hierarkier hurtigt en produktivitetsforbedring og giver mulighed for ensartethed.

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

Kontrolpunkt og quiz

Gennemgå det, du har lært

Din Excel-projektmappe har nu en datamodel, der indeholder data fra flere kilder, og som er relateret til brug af eksisterende felter og beregnede kolonner. Du har også hierarkier, der afspejler strukturen af data i dine tabeller, 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 denne serie opretter du visuelt overbevisende rapporter om olympiske medaljer ved hjælp af Power View. Du kan også udføre flere beregninger, optimere data til hurtig oprettelse af rapporter og importere yderligere data for at gøre disse rapporter endnu mere interessante. Her er et link:

Selvstudium 3: Oprette kortbaserede Power View-rapporter

QUIZ

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

Spørgsmål 1: Hvilke af 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 ved hjælp af gittervisning 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?

Sv: 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 gælder for 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 til ét felt.

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