Självstudiekurs: Utöka datamodellrelationer med Excel, Power Pivot och DAX

Följande är avsnitten i den här självstudiekursen:

• Lägga till en relation med diagramvyn i Power Pivot

• Utöka datamodellen med beräknade kolumner

• Skapa en hierarki

• Använda hierarkier i pivottabeller

• Kontrollpunkt och test

I slutet av självstudiekursen finns ett test du kan ta för att testa vad du har lärt dig.

I den här serien används data som beskriver olympiska medaljer, värdländer och olika olympiska sporthändelser. Följande är självstudiekurserna i den här serien:

1. Importera data till Excel och skapa en datamodell

2. Utöka datamodellrelationer med Excel Power Pivot och DAX

3. Skapa kartbaserade Power View-rapporter

4. Införliva Internet-data och ange standardinställningar för Power View-rapporter

5. Hjälp för Power Pivot

6. Skapa fantastiska Power View-rapporter - del 2

Vi föreslår att du går igenom dem i tur och ordning.

I de här självstudiekurserna används Excel 2013 med Power Pivot aktiverat. Om du vill ha mer information om Excel 2013 klickar du här. Om du vill ha anvisningar för hur du aktiverar Power Pivot klickar du här.

Lägga till en relation från diagramvyn i Power Pivot

I det här avsnittet använder du Tillägget Microsoft Office Power Pivot i Excel 2013 för att utöka modellen. Genom att använda diagramvyn i Microsoft SQL Server Power Pivot for Excel blir det enkelt att skapa relationer. Först måste du se till att du har Power Pivot-tillägget aktiverat.

Obs! Tillägget Power Pivot i Microsoft Excel 2013 är en del av Office Professional Plus. Mer information finns i Starta Power Pivot i Microsoft Excel 2013 tillägg.

Lägga till Power Pivot i menyfliksområdet i Excel genom att aktivera det Power Pivot tillägget

När Power Pivot är aktiverat visas en menyflik i Excel 2013 med namnet POWER PIVOT. Följ de här stegen om du vill aktivera Power Pivot.

1. Gå till ARKIV > Alternativ > tillägg.

2. I rutan Hantera längst ned klickar du på COM-tillägg> Go.

3. Markera rutan Microsoft Office Power Pivot i Microsoft Excel 2013 och klicka sedan på OK.

Menyfliksområdet i Excel har nu en POWER PIVOT-flik .

Lägga till en relation från diagramvyn i Power Pivot

Excel-arbetsboken innehåller en tabell som heter Värdar. Vi importerade Värdar genom att kopiera och klistra in dem i Excel och sedan formatera data som en tabell. Om du vill lägga till tabellen Värdar i datamodellen måste vi upprätta en relation. Vi använder Power Pivot för att visuellt representera relationerna i datamodellen och sedan skapa relationen.

1. I Excel klickar du på fliken Värdar för att göra den till det aktiva bladet.

2. I menyfliksområdet väljer du POWER PIVOT > Tabeller > Lägg till i datamodell. I det här steget läggs tabellen Värdar till i datamodellen. Dessutom öppnas det Power Pivot tillägget som du använder för att utföra de återstående stegen i den här uppgiften.

3. Observera att alla tabeller i modellen visas i fönstret Power Pivot, inklusive Värdar. Klicka igenom ett par tabeller. I Power Pivot kan du visa alla data som modellen innehåller, även om de inte visas i några kalkylblad i Excel, till exempel data för Grenar, Evenemang och Medaljer nedan, samt S_Teams,W_Teams och Sport.
   
   

4. Klicka på Diagramvy i avsnittet Visa i fönstret Power Pivot.

5. Använd skjutreglaget och ändra storlek på diagrammet så att du ser alla objekt i diagrammet. Ordna om tabellerna genom att dra deras namnlist så att de visas och placeras bredvid varandra. Observera att fyra tabeller inte är relaterade till resten av tabellerna: Värdar, Händelser, W_Teams och S_Teams.
   
   

6. Du märker att både tabellen Medaljer och tabellen Händelser har ett fält som heter GrenHändelse. Vid ytterligare kontroll bestämmer du att fältet DisciplineEvent i tabellen Händelser består av unika, icke-upprepade värden.

1. Skapa en relation mellan tabellen Medaljer och tabellen Händelser . När du är i diagramvyn drar du fältet GrenHändelse från tabellen Händelser till fältet GrenHändelse i Medaljer. En linje visas mellan dem som anger att en relation har upprättats.

2. Klicka på linjen som kopplar samman händelser och medaljer. De markerade fälten definierar relationen, så som visas på bilden nedan.
   
   
   

3. För att ansluta värdar till datamodellen behöver vi ett fält med värden som unikt identifierar varje rad i tabellen Värdar . Sedan kan vi söka i vår datamodell för att se om samma data finns i en annan tabell. Vi kan inte göra det genom att titta i diagramvyn. När Värdar är markerat växlar du tillbaka till datavyn.

4. När kolumnerna har granskats inser vi att Värdar inte har någon kolumn med unika värden. Vi måste skapa den med hjälp av en beräknad kolumn och dataanalysuttryck (DAX).

Det är trevligt när data i datamodellen har alla fält som behövs för att skapa relationer och sammanställer data för visualisering i Power View eller pivottabeller. Men tabeller är inte alltid så samarbetsinriktade, så i nästa avsnitt beskrivs hur du skapar en ny kolumn med DAX som kan användas för att skapa en relation mellan tabeller.

Utöka datamodellen med beräknade kolumner

För att upprätta en relation mellan tabellen Värdar och datamodellen, och därmed utöka vår datamodell till att omfatta tabellen Värdar , måste Värdar ha ett fält som unikt identifierar varje rad. Det fältet måste dessutom motsvara ett fält i datamodellen. Motsvarande fält, ett i varje tabell, är det som gör att tabellernas data kan associeras.

Eftersom tabellen Värdar inte har ett sådant fält måste du skapa det. Om du vill bevara datamodellens integritet kan du inte använda Power Pivot för att redigera eller ta bort befintliga data. Du kan dock skapa nya kolumner genom att använda beräknade fält baserat på befintliga data.

Genom att titta igenom tabellen Värdar och sedan titta på andra datamodelltabeller hittar vi en bra kandidat för ett unikt fält som vi kan skapa i Värdar och sedan associerar med en tabell i datamodellen. Båda tabellerna kräver en ny, beräknad kolumn för att uppfylla de krav som krävs för att upprätta en relation.

I Värdar kan vi skapa en unik beräknad kolumn genom att kombinera fältet Edition (året för den olympiska händelsen) och fältet Årstid (sommar eller vinter). I tabellen Medaljer finns det också ett fält av typen Utgåva och fältet Årstid, så om vi skapar en beräknad kolumn i var och en av tabellerna som kombinerar fälten Utgåva och Årstid kan vi upprätta en relation mellan Värdar och Medaljer. På följande skärm visas tabellen Värdar med fälten Utgåva och Årstid markerade

Skapa beräknade kolumner med DAX

Vi börjar med tabellen Värdar . Målet är att skapa en beräknad kolumn i tabellen Värdar och sedan i tabellen Medaljer , som kan användas för att upprätta en relation mellan dem.

I Power Pivot kan du använda DAX (Data Analysis Expressions) för att skapa beräkningar. DAX är ett formelspråk för Power Pivot och pivottabeller, som utformats för relationsdata och sammanhangsberoende analyser som finns tillgängliga i Power Pivot. Du kan skapa DAX-formler i en ny Power Pivot kolumn och i beräkningsområdet i Power Pivot.

1. I Power Pivot väljer du START > Visa > datavy för att kontrollera att datavyn är markerad i stället för att vara i diagramvyn.

2. Välj tabellen Värdar i Power Pivot. Intill de befintliga kolumnerna finns en tom kolumn med namnet Lägg till kolumn. Power Pivot anger den kolumnen som platshållare. Det finns många sätt att lägga till en ny kolumn i en tabell i Power Pivot, varav ett är att helt enkelt markera den tomma kolumnen som har rubriken Lägg till kolumn.
   
   
   

3. Skriv följande DAX-formel i formelfältet. Funktionen SAMMANFOGA kombinerar två eller fler fält till ett. När du skriver hjälper Komplettera automatiskt dig att skriva in fullständigt kvalificerade namn på kolumner och tabeller, och visar de funktioner som är tillgängliga. Använd fliken för att välja Komplettera automatiskt-förslag. Du kan också bara klicka på kolumnen medan du skriver formeln och Power Pivot infogar kolumnnamnet i formeln.
   
   =CONCATENATE([Edition],[Season])

4. När du är klar med formeln trycker du på Retur för att acceptera den.

5. Värden fylls i för alla rader i den beräknade kolumnen. Om du bläddrar nedåt i tabellen ser du att varje rad är unik – så vi har skapat ett fält som unikt identifierar varje rad i tabellen Värdar . Sådana fält kallas för en primärnyckel.

6. Vi byter namn på den beräknade kolumnen till EditionID. Du kan byta namn på en kolumn genom att dubbelklicka på den eller genom att högerklicka på kolumnen och välja Byt namn på kolumn. När den är klar ser tabellen Värdar i Power Pivot ut som på följande skärm.
   
   

Tabellen Värdar är klar. Nu ska vi skapa en beräknad kolumn i Medaljer som matchar formatet på kolumnen EditionID som vi skapade i Värdar, så att vi kan skapa en relation mellan dem.

1. Börja med att skapa en ny kolumn i tabellen Medaljer , precis som för Värdar. Markera tabellen Medaljer i Power Pivot och klicka på Design > Kolumner > Lägg till. Observera att Lägg till kolumn är markerat. Det här har samma effekt som att välja Lägg till kolumn.

2. Kolumnen Utgåva i Medaljer har ett annat format än kolumnen Utgåva i Värdar. Innan vi kombinerar, eller sammanfogar, kolumnen Edition med kolumnen Årstid för att skapa kolumnen EditionID måste vi skapa ett mellanliggande fält som hämtar Edition till rätt format. Skriv följande DAX-formel i formelfältet ovanför tabellen.

   = YEAR([Edition])
   

3. Tryck på Retur när du är klar med formeln. Värden fylls i för alla rader i den beräknade kolumnen, baserat på formeln du angav. Om du jämför den här kolumnen med kolumnen Utgåva i Värdar ser du att kolumnerna har samma format.

4. Byt namn på kolumnen genom att högerklicka på BeräknadKolumn1 och markera Byt namn på kolumn. Skriv År och tryck sedan på Retur.

5. När du skapade en ny kolumn Power Pivot lagt till en annan platshållarkolumn med namnet Lägg till kolumn. Därefter vill vi skapa den beräknade kolumnen EditionID, så välj Lägg till kolumn. Skriv följande DAX-formel i formelfältet och tryck på Retur.
   
   =CONCATENATE([Year],[Season])

6. Byt namn på kolumnen genom att dubbelklicka på BeräknadKolumn1 och skriva EditionID.

7. Sortera kolumnen i stigande ordning. Tabellen Medaljer i Power Pivot ser nu ut som på följande skärm.
   
   
   

Observera att många värden upprepas i tabellen Medaljer i fältet EditionID. Det är okej och förväntat, eftersom många medaljer tilldelades under varje utgåva av OS (som nu representeras av EditionID-värdet). Det som är unikt i tabellen Medaljer är varje tilldelad medalj. Den unika identifieraren för varje post i tabellen Medaljer och dess angivna primärnyckel är fältet MedalKey.

Nästa steg är att skapa en relation mellan Värdar och Medaljer.

Skapa en relation med beräknade kolumner

Nu ska vi använda de beräknade kolumner som vi har skapat för att upprätta en relation mellan Värdar och Medaljer.

1. I fönstret Power Pivot väljer du Start > Visa > diagramvy i menyfliksområdet. Du kan också växla mellan rutnätsvyn och diagramvyn med hjälp av knapparna längst ned i PowerView-fönstret, som visas på bilden nedan.
   
   

2. Expandera Värdar så att du kan visa alla dess fält. Vi skapade kolumnen EditionID för att fungera som primärnyckel för Tabellen Värdar (unikt, icke-upprepat fält) och skapade en EditionID-kolumn i tabellen Medaljer för att göra det möjligt att upprätta en relation mellan dem. Vi måste hitta dem båda och skapa en relation. Power Pivot innehåller en Sök-funktion i menyfliksområdet, så att du kan söka efter motsvarande fält i datamodellen. På följande skärm visas fönstret Sök efter metadata med EditionID angivet i fältet Sök efter .
   

3. Placera tabellen Värdar så att den är bredvid Medaljer.

4. Dra kolumnen EditionID i Medaljer till kolumnen EditionID i Värdar. Power Pivot skapar en relation mellan tabellerna baserat på EditionID-kolumnen och ritar en linje mellan de två kolumnerna, som anger relationen.
   
   

I det här avsnittet lärde du dig en ny teknik för att lägga till nya kolumner, skapade en beräknad kolumn med DAX och använde kolumnen för att upprätta en ny relation mellan tabeller. Tabellen Värdar är nu integrerad i datamodellen och dess data är tillgängliga för pivottabellen i Blad1. Du kan också använda associerade data för att skapa ytterligare pivottabeller, pivotdiagram, Power View-rapporter och mycket mer.

Skapa en hierarki

De flesta datamodeller innehåller data som är hierarkiska. Vanliga exempel är kalenderdata, geografiska data och produktkategorier. Att skapa hierarkier inom Power Pivot är användbart eftersom du kan dra ett objekt till en rapport – hierarkin – i stället för att behöva sammanställa och ordna samma fält om och om.

De olympiska spelens data är också hierarkiska. Det är bra att förstå hierarkin i OS när det gäller sport, discipliner och evenemang. För varje sport finns det en eller flera associerade discipliner (ibland finns det många). Och för varje gren finns det en eller flera evenemang (återigen, ibland finns det många evenemang i varje disciplin). Följande bild illustrerar hierarkin.

I det här avsnittet skapar du två hierarkier inom de olympiska data som du har använt i den här självstudiekursen. Du kan sedan använda de här hierarkierna för att se hur hierarkier gör det enkelt att organisera data i pivottabeller och, i en senare självstudiekurs, i Power View.

Skapa en sporthierarki

1. Växla till diagramvyn i Power Pivot. Expandera tabellen Händelser så att du lättare kan se alla dess fält.

2. Håll ned Ctrl och klicka på fälten Sport, Gren och Evenemang. När de tre fälten är markerade högerklickar du och väljer Skapa hierarki. En överordnad hierarkinod, Hierarki 1, skapas längst ned i tabellen och de markerade kolumnerna kopieras under hierarkin som underordnade noder. Kontrollera att Sport först visas i hierarkin, sedan på Gren och sedan på Evenemang.

3. Dubbelklicka på rubriken Hierarki1 och skriv SDE för att byta namn på den nya hierarkin. Nu har du en hierarki som inkluderar Sport, Gren och Evenemang. Tabellen Händelser ser nu ut som på skärmen nedan.
   
   

Skapa en platshierarki

1. I diagramvyn i Power Pivot väljer du tabellen Värdar och klickar på knappen Skapa hierarki i tabellrubriken, så som visas på skärmen nedan.
   
   
   En tom överordnad hierarkinod visas längst ned i tabellen.

2. Skriv Platser som namn på den nya hierarkin.

3. Det finns många sätt att lägga till kolumner i en hierarki. Dra fälten Årstid, Ort och NOC_CountryRegion till hierarkinamnet (i det här fallet Platser) tills hierarkinamnet är markerat och släpp sedan för att lägga till dem.

4. Högerklicka på EditionID och välj Lägg till i hierarki. Välj Platser.

5. Kontrollera att hierarkin för underordnade noder är i ordning. Uppifrån och ned ska ordningen vara: Säsong, NOC, Ort, EditionID. Om dina underordnade noder inte är i ordning drar du dem till rätt ordning i hierarkin. Tabellen ska se ut som på följande skärm.
   

Datamodellen har nu hierarkier som kan användas på ett bra sätt i rapporter. I nästa avsnitt får du lära dig hur hierarkierna kan göra det snabbare och mer konsekvent att skapa rapporter.

Använda hierarkier i pivottabeller

Nu när vi har en hierarki för sport och platser kan vi lägga till dem i pivottabeller eller Power View och snabbt få resultat som innehåller användbara grupper av data. Innan du skapade hierarkier var du tvungen att lägga till enskilda fält i pivottabellen och ordna fälten så som du ville att de skulle visas.

I det här avsnittet använder du hierarkierna som skapades i föregående avsnitt för att snabbt förfina pivottabellen. Sedan skapar du samma pivottabellvy med de enskilda fälten i hierarkin, bara så att du kan jämföra hierarkier med att använda enskilda fält.

1. Gå tillbaka till Excel.

2. I Blad1 tar du bort fälten från området RADER i pivottabellfält och tar sedan bort alla fält från området KOLUMNER. Kontrollera att pivottabellen är markerad (som nu är ganska liten, så att du kan välja cell A1 för att kontrollera att pivottabellen är markerad). De enda återstående fälten i pivottabellfälten är Medalj i området FILTER och Antal medaljer i området VÄRDEN. Den nästan tomma pivottabellen ska se ut som på skärmen nedan.

   

3. Dra SDE från området Pivottabellfält från tabellen Händelser till området RADER. Dra sedan Platser från tabellen Värdar till området KOLUMNER . Bara genom att dra dessa två hierarkier fylls pivottabellen med mycket data, som alla är ordnade i hierarkin som du definierade i föregående steg. Skärmen ska se ut som på följande skärm.
   

4. Vi filtrerar dessa data lite och ser bara de första tio raderna med händelser. Klicka på pilen i Radetiketter i pivottabellen, klicka på (Markera alla) om du vill ta bort alla markeringar och klicka sedan på rutorna bredvid de tio första sporterna. Pivottabellen ser nu ut som på skärmen nedan.
   

5. Du kan expandera alla sporter i pivottabellen, som är den översta nivån i SDE-hierarkin, och se information på nästa nivå nedåt i hierarkin (disciplin). Om det finns en lägre nivå i hierarkin för den grenen kan du utöka grenen för att se dess händelser. Du kan göra samma sak för platshierarkin, vars översta nivå är Årstid, som visas som Sommar och Vinter i pivottabellen. När vi utökar vattensporten ser vi alla dess barndisciplinelement och deras data. När vi utökar dykdisciplinen under Aquatics ser vi dess barnhändelser också, som visas på följande skärm. Vi kan göra samma sak för Vattenpolo och se till att det bara har en händelse.
   

Genom att dra dessa två hierarkier skapade du snabbt en pivottabell med intressanta och strukturerade data som du kan granska i, filtrera och ordna.

Nu ska vi skapa samma pivottabell, utan att dra nytta av hierarkier.

1. I området Pivottabellfält tar du bort Platser från området KOLUMNER. Ta sedan bort SDE från området RADER. Du är tillbaka i en enkel pivottabell.

2. Från tabellen Värdar drar du Årstid, Ort, NOC_CountryRegion och EditionID till området KOLUMNER och ordnar dem i den ordningen, uppifrån och ned.

3. Dra Sport, Gren och Händelse från tabellen Händelser till området RADER och ordna dem i den ordningen, uppifrån och ned.

4. I pivottabellen filtrerar du Radetiketter till de tio främsta sporterna.

5. Dölj alla rader och kolumner, expandera sedan Aquatics, sedan Dykning och Vattenpolo . Arbetsboken ser ut som på skärmen nedan.
   

Skärmen ser liknande ut, förutom att du drog sju enskilda fält till fältområdena i pivottabellen , i stället för att helt enkelt dra två hierarkier. Om du är den enda personen som skapar pivottabeller eller Power View-rapporter baserat på dessa data kanske det bara verkar bekvämt att skapa hierarkier. Men när många skapar rapporter och måste ta reda på rätt ordning på fälten för att få vyerna korrekta, blir hierarkier snabbt en produktivitetsförbättring och möjliggör konsekvens.

I en annan självstudiekurs lär du dig hur du använder hierarkier och andra fält i visuellt engagerande rapporter som skapats med Power View.

Kontrollpunkt och test

Gå igenom det du lärt dig

Excel-arbetsboken har nu en datamodell som innehåller data från flera källor, relaterade med befintliga fält och beräknade kolumner. Du har också hierarkier som återspeglar datastrukturen i tabellerna, vilket gör det snabbt, konsekvent och enkelt att skapa övertygande rapporter.

Du lärde dig att skapa hierarkier kan du ange den inneboende strukturen i dina data och snabbt använda hierarkiska data i dina rapporter.

I nästa självstudiekurs i den här serien skapar du visuellt tilltalande rapporter om olympiska medaljer med Power View. Du gör också fler beräkningar, optimerar data för att snabbt skapa rapporter och importerar ytterligare data för att göra rapporterna ännu mer intressanta. Här är en länk:

Självstudiekurs 3: Skapa kartbaserade Power View-rapporter

TEST

Vill du ta reda på hur väl du kommer ihåg det du lärt dig? Här är din chans. Följande test tar upp de funktioner, möjligheter och krav du har läst om i den här självstudiekursen. Längst ned på sidan hittar du svaren. Lycka till!

Fråga 1: Vilken av följande vyer låter dig skapa relationer mellan två tabeller?

S: Du skapar relationer mellan tabeller i Power View.

B: Du skapar relationer mellan tabeller med designvyn i Power Pivot.

C: Du skapar relationer mellan tabeller med rutnätsvyn i Power Pivot

D: Allt ovanstående.

Fråga 2: SANT eller FALSKT: Du kan upprätta relationer mellan tabeller baserat på en unik identifierare som skapas med hjälp av DAX-formler.

S: SANT

B: FALSKT

Fråga 3: I vilket av följande kan du skapa en DAX-formel?

S: I beräkningsområdet i Power Pivot.

B: I en ny kolumn i Power Pivot f.

C: I valfri cell i Excel 2013.

D: Både A och B.

Fråga 4: Vilket av följande gäller för hierarkier?

S: När du skapar en hierarki är de fält som ingår inte längre tillgängliga individuellt.

B: När du skapar en hierarki kan de fält som ingår, inklusive deras hierarki, användas i klientverktygen genom att helt enkelt dra hierarkin till ett Power View- eller pivottabellområde.

C: När du skapar en hierarki kombineras underliggande data i datamodellen i ett fält.

D: Du kan inte skapa hierarkier i Power Pivot.

Testsvar

1. Rätt svar: D

2. Rätt svar: A

3. Rätt svar: D

4. Rätt svar: B