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

Selvstudiet har følgende afsnit:

• Tilføje en relation med diagramvisning i Power Pivot

• Udvid datamodellen ved hjælp af beregnede kolonner

• Oprette et hierarki

• Bruge 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. Importere Data til Excel, og oprette 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 brug af Excel 2013 med Power Pivot aktiveret. Flere oplysninger om Excel 2013, ved at klikke her. For at vide om aktivering af Power Pivot, skal du klikke på her.

Tilføje en relation med diagramvisning i Power Pivot

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

Bemærk: Er en del af Office Professional Plus tilføjelsesprogrammet Power Pivot i Microsoft Excel 2013. Du kan få flere oplysninger i Starte Power Pivot i Microsoft Excel 2013-tilføjelsesprogrammet .

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

Når Power Pivot er aktiveret, kan du se et bånd i Excel 2013, der hedder POWER PIVOT. Power Pivot aktiveres ved at følge disse trin.

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.

Båndet i Excel indeholder nu fanen POWER PIVOT.

Tilføje en relation med diagramvisning i Power Pivot

Excel-projektmappen indeholder en tabel, der hedder Hosts. Vi importerede Hosts ved at kopiere og indsætte den i Excel og formaterede derefter dataene som en tabel. Vi er nødt til at etablere en relation for at kunne tilføje tabellen Hosts til datamodellen. Lad os bruge Power Pivot til visuelt at vise relationerne i datamodellen, og derefter oprette relationen.

1. I Excel skal du klikke på fanen Hosts for at gøre den til det aktive ark.

2. Vælg POWER PIVOT > Tabeller > Tilføj til datamodel på båndet. I dette trin tilføjes tabellen Hosts til datamodellen. Power Pivot-tilføjelsesprogrammet åbnes også, og her kan du udføre de øvrige trin i denne opgave.

3. Bemærk, at vinduet Power Pivot indeholder alle tabellerne i modellen, herunder Hosts. Klik dig gennem nogle tabeller. Power Pivot kan du få vist alle de data, der indeholder din model, selvom de ikke vises i alle regneark i Excel, som discipliner, begivenhederog medaljer data nedenfor, samt S_Teams,W_ Teams, og sport.
   
   

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

5. Brug skyderen til at ændre størrelsen på diagrammet, så du kan se alle objekter i diagrammet. Omarrangere tabeller ved at trække deres titellinjen, så de er synlig og placeret ved siden af hinanden. Bemærk, at fire tabeller er ikke-relaterede for resten af tabellerne: Hosts, begivenheder, W_Teamsog S_Teams.
   
   

6. Du kan se, at både Medals-tabellen og Events-tabellen indeholder et felt kaldet DisciplineEvent. Hvis du kigger nærmere, kan de se, at feltet DisciplineEvent i tabellen Events består af entydige værdier, der ikke gentages.

1. Opret en relation mellem tabellen Medals og tabellen Events. Stå i diagramvisning, og træk feltet DisciplineEvent fra tabellen Events til feltet DisciplineEvent i Medals. Der vises en linje mellem dem, som angiver, at der er oprettet en relation.

2. Klik på den linje, der forbinder begivenheder og medaljer. Felterne fremhævede definerer relationen, som vist på følgende skærmbillede.
   
   
   

3. Hvis Hosts skal tilknyttes til datamodellen, skal vi bruge et felt med værdier, der entydigt identificerer de enkelte rækker i tabellen Hosts. Så kan vi søge i vores datamodel for at se, om de samme data findes i en anden tabel. Det kan vi ikke gøre ved at kigge på diagramvisningen. Gå tilbage til datavisning med Hosts markeret.

4. Efter at undersøge kolonnerne, opdager 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 Data Analysis Expressions (DAX).

Det er godt, når dataene i datamodellen har alle de nødvendige felter til at oprette relationer og indsamlingsdata til at visualisere i Power View eller pivottabeller. Men tabeller er ikke altid så samarbejdsvillige. I næste afsnit beskrives det derfor, hvordan man, ved hjælp DAX, opretter en ny kolonne, der kan bruges til at oprette en relation mellem tabeller.

Udvid datamodellen ved hjælp af beregnede kolonner

Feltet Hosts skal have et felt, der entydigt identificerer hver række for at etablere en relation mellem tabellen Hosts og datamodellen og derved udvider datamodellen til at omfatte tabellen Hosts. Derudover skal det pågældende felt svare til et felt i datamodellen. Disse tilsvarende felter, et i hver tabel, er det, der muliggør tilknytning af tabellernes data til hinanden.

Da tabellen Hosts ikke har dette felt, skal du oprette den. Hvis du vil bevare integriteten af en datamodel, bruge du ikke Power Pivot til at redigere eller slette eksisterende data. Du kan dog oprette nye kolonner ved hjælp af beregnede felter, der er baseret på de eksisterende data.

Hvis man ser på tabellen Hosts og derefter ser på andre datamodeltabeller, finder vi en velegnet kandidat til et entydigt felt, som vi kan oprette i Hosts, 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 oprette en relation.

I Hosts kan vi oprette en entydig beregnet kolonne ved at kombinere feltet Edition (året for de olympiske lege) og feltet Season (Summer eller Winter). Tabellen Medals indeholder også et felt med Edition og Season. Det betyder, at 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, hvor felterne Edition og Season er markeret

Oprette 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 Pivot kan du bruge DAX (Data Analysis Expressions) til at oprette beregninger. DAX er et formelsprog til Power Pivot og pivottabeller, som er beregnet til de relationelle data og den kontekstafhængige analyse, der findes i Power Pivot. Du kan oprette DAX-formler i en ny Power Pivot-kolonne og i beregningsområdet i Power Pivot.

1. Gå til Power Pivot og vælg HJEM > Vis > Datavisning for at sikre, at datavisning er valgt i stedet for at stå i diagramvisning.

2. Vælg tabellen Hosts i Power Pivot. Støder op til de eksisterende kolonner er en tom kolonne med titlen Tilføj kolonne. Power Pivot indeholder kolonnen som en pladsholder. Der findes mange måder til at tilføje en ny kolonne til en tabel i Power Pivot, hvoraf den ene er blot markere den tomme kolonne, der indeholder titlen Tilføj kolonne.
   
   
   

3. Skriv følgende DAX-formel på formellinjen. Funktionen sammenkædning kombinerer to eller flere felter i en. Mens du skriver, Autofuldførelse kan du skrive fuldstændige navne på kolonner og tabeller og viser en liste over de funktioner, der er tilgængelige. Brug fanen til at vælge forslag til autofuldførelse. Du kan også bare klikke på kolonnen, mens at skrive din formel og Power Pivot indsætter kolonnenavnet i formlen.
   
   =CONCATENATE([Edition],[Season])

4. Når du er færdig med at oprette 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å det er lykkes os at oprette 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 EditionID. Du kan omdøbe en 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 Hosts er klar. Lad os herefter oprette en beregnet kolonne i Medals, der svarer til formatet af den EditionID-kolonne, vi oprettede i Hosts, så vi kan oprette en relation mellem dem.

1. Start med at oprette en ny kolonne i tabellen Medals, sådan som vi gjorde det for Hosts. Gå til Power Pivot, vælg tabellen Medals og klik på Design > Kolonner > Tilføj. Bemærk, at Tilføje kolonne markeres. Det har samme virkning, som hvis du markerer Tilføj kolonne.

2. Kolonnen Udgave i Medals har et andet format end kolonnen Udgave i Hosts. Før vi kombinerer eller sammenkæder kolonnen Udgave og kolonnen Årstid for at oprette kolonne EditionID, skal vi oprette en midlertidigt felt, der omdanner Udgave til det korrekte 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 angivne formel. Hvis du sammenligner denne kolonne med kolonnen Udgave i Hosts, kan du se, at disse kolonner har det samme format.

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

5. Da du oprettede en ny kolonne, tilføjede Power Pivot endnu en pladsholderkolonne med navnet Tilføj kolonne. Herefter skal vi oprette den beregnede EditionID-kolonne, så vælg Tilføj kolonne. Skriv den 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. Sortere kolonnen i stigende rækkefølge. Tabellen Medals i Power Pivot ser nu sådan følgende skærmbillede.
   
   
   

Læg mærke til, at mange værdier gentages i feltet EditionID i tabellen Medals. Det er fint og forventeligt, da der blev uddelt mange medaljer under alle udgaver af OL (som nu er repræsenteret af værdien EditionID). Det unikke ved tabellen Medals er hver enkelt vundne medalje. Det entydige id for hver enkelt post i tabellen Medals, og dens angivne primære nøgle, er feltet MedalKey.

Det næste trin er at oprette en relation mellem Hosts og Medals.

Opret en relation ved hjælp af beregnede kolonner

Lad os nu bruge de beregnede kolonner, som vi oprettede, til at etablere en relation mellem Hosts og Medals.

1. Vælg Startside > visning > diagramvisning i vinduet Power Pivot på båndet. Du kan også skifte mellem gittervisning og diagramvisning ved hjælp af knapperne nederst i vinduet Power View, som vist på følgende skærmbillede.
   
   

2. Udvid Hosts , så du kan få vist alle felterne. Vi oprettet kolonnen EditionID skal fungere som Hosts tabels primære nøgle (entydige, der ikke gentages felt), og en EditionID kolonne i tabellen medaljer til at aktivere oprettelse af en relation mellem dem. Vi har brug at finde dem begge og oprette en relation. Power Pivot indeholder en funktion, finde på båndet, så du kan søge i din datamodel til tilsvarende felter. Følgende skærmbillede viser vinduet Find Metadata med EditionID, der er angivet i feltet Søg efter.
   

3. Flyt tabellen Hosts, så den er ved siden af Medals.

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

I dette afsnit, du har lært en ny metode til at tilføje nye kolonner, oprettes en beregnet kolonne ved hjælp af DAX, og bruges denne kolonne til at oprette en ny relation mellem tabeller. Tabellen Hosts er nu integreret i datamodellen, og dens data findes i 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 i stedet for at skulle samle og arrangere de samme felter igen og igen.

OL-dataene er også hierarkiske. Det er nyttigt til at forstå OL-hierarkiet i forhold til sportsgrene, discipliner og begivenheder. Der er en eller flere tilknyttede discipliner (nogle gange er der mange) for hver sportsgren. Og for hver disciplin er der en eller flere begivenheder (også her kan der være mange begivenheder i hver disciplin). Følgende billede illustrerer hierarkiet.

I dette afsnit opretter du to hierarkier i de olympiske data, som 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.

Oprette et Sportsgren-hierarki

1. I Power Pivot skal du skifte til Diagramvisning. Udvid tabellen Events, så du lettere kan se alle felterne.

2. Tryk på Ctrl og hold knappen nede, og klik på felterne Sport, Discipline og Event Højreklik og vælg Opret hierarki, når de tre felter er markeret. En overordnet hierarkinode, Hierarki 1, oprettes nederst i tabellen, og de markerede kolonner kopieres under hierarkiet som underordnede noder. Se efter, om Sport vises først i hierarkiet, derefter Discipline og til sidst Event.

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

Oprette et Placering-hierarki

1. Vælg tabellen Hosts stadig i diagramvisning i Power Pivot, og klik på knappen Opret hierarki i tabeloverskriften, som vist på følgende skærmbillede.
   
   
   Der vises en tom hierarki overordnede knude i bunden af tabellen.

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

3. Der er mange måder, hvorpå du kan tilføje kolonner til et hierarki. Træk felterne Season, City og NOC_CountryRegion til hierarkinavnet (i dette tilfælde Locations), indtil hierarkinavnet er markeret, og slip dem derefter for at tilføje dem.

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

5. Sørg for, at din underordnede hierarkinoder er i rækkefølge. Fra top til bund rækkefølgen skal være: Season NOC by, og EditionID. Hvis underordnede hierarkinoder er uden for rækkefølgen, skal du blot trække dem til de relevante rækkefølge i hierarkiet. Tabellen bør se som på følgende skærmbillede.
   

Din datamodel indeholder nu hierarkier, der kan bruges i rapporter. I næste afsnit lærer du, hvordan hierarkierne gør det hurtigere at lave rapporter, der er mere ensartede.

Bruge hierarkier i pivottabeller

Nu hvor vi har et hierarki for Sportsgrene og Placeringer, kan vi tilføje dem til pivottabeller eller Power View og hurtigt få resultater, som indeholder praktiske grupperinger af data. Før du oprettede hierarkierne, var du nødt til at tilføje individuelle felter til pivottabellen og arrangere dem i den orden, hvorpå de skal vises.

I dette afsnit bruger du bruge hierarkierne, som du oprettede i det forrige afsnit, til hurtigt at tilpasse din pivottabel. Derefter skal du oprette den samme pivottabelvisning ved hjælp af de enkelte felter i hierarkiet, så du kan sammenligne brugen af hierarkier med brugen 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 (den er nu ganske lille, så du kan vælge celle A1 for at sikre, at din pivottabel er markeret). De eneste tilbageværende felter i felterne i pivottabellen er Medals 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 tabellen begivenheder til området rækker fra området PivotTable Fields. Træk derefter placeringer fra tabellen Hosts til området kolonner . Din pivottabel er lige ved at trække de to nævnte hierarkier, udfyldt med en stor mængde data, som alle er arrangeret i hierarkiet, du har defineret i de forrige trin. Skærmen skal ligne følgende skærmbillede.
   

4. Lad os filtrere, en bit, og kun se de første ti rækker af begivenheder. Klik på pilen i Rækkenavne, i pivottabellen, skal du klikke på (Markér alt) for at fjerne alle markeringer, og klik derefter på afkrydsningsfelterne ud for de første ti sportsgrene. Din pivottabel ser nu sådan følgende skærmbillede.
   

5. Du kan udvide en af disse sport i pivottabellen, som er det øverste niveau i hierarkiet SDE, og få vist oplysninger i 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 disciplin for at få vist dets begivenheder. Du kan gøre det samme for hierarkiet placering, det øverste niveau som er Season, som vises som sommer- og vinter i pivottabellen. Når vi udvider Aquatics sport, kan vi se alle de underordnede disciplin elementer og deres data. Når vi udvider vipper disciplin under Aquatics, se vi dens underordnede begivenheder, som vist på følgende skærmbillede. Vi kan gøre det samme for vand Polo, og se, at det er kun én begivenhed.
   

Ved at trække de to nævnte hierarkier oprettede du hurtigt en pivottabel med interessante og strukturerede data, som du kan gå dybere ned i, filtrere og arrangere.

Lad os nu oprette den samme pivottabel, men uden at bruge hierarkier.

1. I området Pivottabelfelter skal du fjerne Placeringer fra området KOLONNER. Fjern herefter SDE fra området RÆKKER. Du har nu en grundlæggende pivottabel.

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

3. Gå til tabellen Events, træk Sport, Discipline og Event til området RÆKKER og arranger dem i den rækkefølge, fra top ti bund.

4. Gå til pivottabellen og filtrer Rækkenavne til de ti øverste sportsgrene.

5. Skjule alle rækker og kolonner, og derefter Udvid Aquatics, derefter vipper og vand Polo. Projektmappen ser ud som følgende skærmbillede.
   

Skærmbilledet ser tilsvarende ud, bortset fra at du trak syv individuelle felter til området Pivottabelfelter i stedet for kun at trække to hierarkier. Hvis du er den eneste person, der laver pivottabeller eller Power View-rapporter på baggrund af disse data, kan det måske kun virke praktisk at oprette hierarkier. Men når mange personer laver rapporter og skal finde ud af den rigtige feltsortering for at få korrekte visninger, viser hierarkier sig hurtigt at være produktivitetsforbedrende og sikrer ensartethed.

I et andet selvstudium lærer du, hvordan man bruger 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 indeholder nu en datamodel med data fra flere kilder, som er relateret til hinanden ved hjælp af eksisterende felter og beregnede kolonner. Du har også hierarkier, der afspejler datastrukturen i dine tabeller, hvilket gør oprettelse af fængslende rapporter hurtigt, ensartet og nem.

Du har lært, at ved oprettelse af hierarkier er det muligt at angive dataenes naturlige struktur og hurtigt anvende hierarkiske data i dine rapporter.

I næste selvstudium i serien lærer du at oprette visuelt fængslende rapporter om olympiske medaljer ved hjælp af Power View. Du skal også lave flere beregninger, optimere data til hurtig oprettelse af rapporter og importere yderligere data for at gøre dine rapporter 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(n) af følgende visninger giver mulighed for at oprette relationer mellem to tabeller?

A: Man opretter relationer mellem tabeller i Power View.

B: Man opretter relationer mellem tabeller ved hjælp af designvisning i Power Pivot.

C: Man opretter relationer mellem tabeller ved hjælp af gittervisning i Power Pivot

D: Alle ovenstående

Spørgsmål 2: SANDT eller FALSK: Man kan etablere relationer mellem tabeller ud fra et entydigt id, der oprettes ved hjælp af DAX-formler.

A: SANDT

B: FALSK

Spørgsmål 3: I hvilke(n) af følgende kan man oprette en DAX-formel?

A: I beregningsområdet i Power Pivot.

B: I en ny kolonne i Power Pivotf.

C: I hvilken som helst celle i Excel 2013.

D: Både A og B.

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

A: Når man opretter et hierarki, er de omfattede felter ikke længere tilgængelige enkeltvis.

B: Når man opretter et hierarki, kan de omfattede felter, inklusive deres hierarki, bruges i klientværktøjer ved blot at trække hierarkiet til et område i Power View eller pivottabellen.

C: Når man opretter et hierarki, kombineres de underliggende data i datamodellen til é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