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

Sammanfattning: Det här är den andra självstudiekursen i en serie. I den första självstudiekursen, Importera data till och skapa en datamodell, skapades en Excel-arbetsbok med hjälp av data som importerats från flera källor.

I den här självstudiekursen använder du Power Pivot till att utöka datamodellen, skapa hierarkier och skapa beräknade fält utifrån befintliga data för att skapa nya relationer mellan tabeller.

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

• Lägga till en relation med diagramvyn i Power Pivot
• Utöka datamodellen med hjälp av 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. Klicka här om du vill ha råd om hur du aktiverar Power Pivot.

Lägga till en relation med diagramvyn i Power Pivot

I det här avsnittet använder du tilläggsprogrammet Microsoft Office Power Pivot i Excel 2013 för att utöka modellen. Använda diagramvyn i Microsoft SQL Server Power Pivot för Excel gör det enkelt att skapa relationer. Kontrollera först att du har aktiverat Power Pivot-tillägget.

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.

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

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

1. Gå till FILALTERNATIV-tillägg >>.
2. I rutan Hantera nästan längst ned klickar du på COM-tillägg> Gå.
3. Markera rutan Microsoft Office Power Pivot i Microsoft Excel 2013 och klicka sedan på OK.

Nu finns fliken POWER PIVOT i menyfliksområdet i Excel.

Lägga till en relation med diagramvyn i Power Pivot

Excel-arbetsboken innehåller en tabell med namnet Värdar. Vi importerade värdar genom att kopiera dem och klistra in dem i Excel, och sedan formaterade vi data som en tabell. För att kunna lägga till tabellen Värdar i datamodellen måste vi upprätta en relation. Nu ska vi använda Power Pivot för att visuellt representera relationerna i datamodellen och sedan skapa relationen.

1. Gör fliken Värdar till det aktiva bladet genom att klicka på den i Excel.

2. I menyfliksområdet väljer du POWER PIVOT-tabeller >> Lägg till i datamodell. Nu läggs tabellen Värdar till i datamodellen. Då öppnas också Power Pivot-tillägget, som du använder för att utföra resten av stegen i den här uppgiften.

3. Observera att alla tabeller i modellen visas i Power Pivot-fönstret, även 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 i data för Grenar, Evenemang och Medaljer nedan, samt S_Teams W_Teams och Sporter.

   

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

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

   

6. Lägg märke till att både tabellen Medaljer och tabellen Evenemang har ett fält med namnet GrenHändelse. Vid närmare kontroll kan du fastställa att fältet DisciplinHändelse i tabellen Händelser består av unika, ej upprepade värden.

1. Skapa en relation mellan tabellerna Medaljer och Evenemang . I diagramvyn drar du fältet GrenEvenemang från tabellen Evenemang 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 evenemang 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 datamodellen för att se om samma data finns i en annan tabell. När vi tittar i diagramvyn kan vi inte göra det. Växla tillbaka till datavyn när Värdar är markerat.

4. När vi har undersökt kolumnerna 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 DAX (Data Analysis Expressions).

Det är snyggt 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å samarbetsvilliga, 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 hjälp av beräknade kolumner

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

Eftersom tabellen Värdar inte har något sådant fält måste du skapa det. För att 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 som baseras 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 skulle kunna skapa i Värdar och sedan associera med en tabell i datamodellen. Båda tabellerna måste ha en ny beräknad kolumn för att uppfylla de nödvändiga kraven för att upprätta en relation.

I Värdar kan vi skapa en unik beräknad kolumn genom att kombinera fältet Upplaga (året för OS-evenemanget) och fältet Säsong (sommar eller vinter). I tabellen Medaljer finns också ett Utgåva-fält och ett Årstid-fält, så om vi skapar en beräknad kolumn i var och en av dessa tabeller som kombinerar fälten Årgång och Årstid kan vi upprätta en relation mellan Värdar och Medaljer. På bilden nedan visas tabellen Värdar med fälten Årgång 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 skapa beräkningar med hjälp av DAX (Data Analysis Expressions). DAX är ett formelspråk för Power Pivot och pivottabeller, utformat för de relationsdata och sammanhangsanalyser som är 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 Datavyn START >> för att kontrollera att datavyn är markerad i stället för att vara i diagramvyn.

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

   

3. Skriv följande DAX-formel i formelfältet. Funktionen SAMMANFOGA kombinerar två eller flera fält till ett. Medan du skriver hjälper Komplettera automatiskt dig att skriva in hela kvalificerade namn för kolumner och tabeller och listar funktionerna som finns tillgängliga. Använd tabbtangenten för att välja Komplettera automatiskt-förslag. Du kan också bara klicka på kolumnen medan du skriver formeln så infogas kolumnnamnet i formeln av Power Pivot.

   =CONCATENATE([Edition],[Season])

4. Tryck på Retur för att godkänna formeln när du är klar med den.

5. Värden fylls i för alla rader i den beräknade kolumnen. Om du bläddrar ned 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 primärnycklar.

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å bilden nedan.

   

Tabellen Värdar är klar. Nu ska vi skapa en beräknad kolumn i Medaljer som matchar formatet i 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 vi gjorde för Värdar. Markera tabellen Medaljer i Power Pivot och klicka på > Designkolumner>, lägg till. Lägg märke till att Lägg till kolumn är markerat. Detta har samma effekt som att bara välja Lägg till kolumn.

2. Kolumnen Upplaga i Medaljer har ett annat format än kolumnen Upplaga i Värdar. Innan vi kombinerar, eller sammanfogar, kolumnen Edition med kolumnen Season 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 färdig med formeln. Värden fylls i för alla rader i den beräknade kolumnen, baserat på den formel som du anger. Om du jämför den här kolumnen med kolumnen Edition i Hosts, ser du att dessa kolumner 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 på Retur.

5. När du skapar en ny kolumn läggs ytterligare en platshållarkolumn med namnet Lägg till kolumn i Power Pivot. Nu vill vi skapa den beräknade kolumnen EditionID och väljer 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å bilden nedan.

   

Observera att många värden upprepas i fältet EditionID i tabellen Medaljer . Det är okej och förväntat, eftersom många medaljer delades ut under varje upplaga av de olympiska spelen (som nu representeras av värdet EditionID). Det som är unikt i tabellen Medaljer är varje tilldelad medalj. Den unika identifieraren för varje post i tabellen Medaljer , och dess tilldelade primärnyckel, är fältet MedaljNyckel.

Nästa steg är att skapa en relation mellan värdar och medaljer.

Skapa en relation med hjälp av beräknade kolumner

Nu ska vi använda de beräknade kolumnerna som vi har skapat för att skapa en relation mellan värdar och medaljer.

1. I Power Pivot-fönstret väljer du Hemvy >> , 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, så som visas på bilden nedan.

   

2. Expandera Värdar så att du kan visa alla fälten. Vi har skapat kolumnen EditionID för att fungera som primärnyckel för tabellen Värdar (unikt, ej upprepat fält) och skapat en kolumn av typen EditionID i tabellen Medaljer för att möjliggöra upprättande av en relation mellan dem. Vi måste hitta dem båda och skapa en relation. I Power Pivot finns en sökfunktion i menyfliksområdet, så att du kan söka i datamodellen efter motsvarande fält. På följande skärm visas fönstret Sök 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å kolumnen EditionID 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. Det är praktiskt att skapa hierarkier i Power Pivot 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 igen.

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

I det här avsnittet skapar du två hierarkier i de olympiska data som du har använt i den här självstudiekursen. Du använder sedan dessa hierarkier för att se hur hierarkier gör det enkelt att ordna 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 det blir lättare att se alla fälten.

2. Tryck på och 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 och placeras under hierarkin som underordnade noder. Kontrollera att Sport visas först i hierarkin, sedan Gren och sedan Evenemang.

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

   

Skapa en platshierarki

1. Fortfarande i diagramvyn i Power Pivot markerar du tabellen Värdar och klickar på knappen Skapa hierarki i tabellrubriken, så som visas på bilden nedan.
   

   En tom överordnad nivå 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 markeras och släpp 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 hierarkins underordnade noder är i ordning. Uppifrån och ned ska ordningen vara: Säsong, NOC, Ort, Utgåve-ID. Om dina underordnade noder är i fel ordning drar du dem bara till lämplig ordning i hierarkin. Tabellen bör se ut som på skärmen nedan.
   

Datamodellen har nu hierarkier som kan användas effektivt i rapporter. I nästa avsnitt får du lära dig hur dessa hierarkier kan göra att rapporter skapas snabbare och mer konsekvent.

Använda hierarkier i pivottabeller

Nu när vi har en sporthierarki och en platshierarki kan vi lägga till dem i pivottabeller eller Power View och snabbt få resultat som innehåller användbara grupperingar 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. Därefter kan du skapa samma pivottabellvy med hjälp av enskilda fält 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å du kan markera cell A1 och 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 bör se ut som på skärmen nedan.
   
3. Dra SDE från tabellen Händelser från området Pivottabellfält 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 en stor mängd data, som alla ordnas i den hierarki som du definierade i föregående steg. Skärmen bör se ut som på skärmen nedan.
   
4. Nu ska vi filtrera informationen lite och bara se de första tio raderna med händelser. I pivottabellen klickar du på pilen i Radetiketter, klickar på (Markera alla) för att ta bort alla markeringar och klickar sedan på rutorna bredvid de tio första sporterna. Pivottabellen ser nu ut som på skärmen nedan.
   
5. Du kan utöka någon av dessa sporter i pivottabellen, som är den högsta nivån i SDE-hierarkin, och se information på nästa nivå nedåt i hierarkin (gren). Om det finns en lägre nivå i hierarkin för den disciplinen kan du expandera disciplinen för att visa dess händelser. Du kan göra samma sak för platshierarkin, där den översta nivån ä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 expanderar simhoppsdisciplinen under Aquatics ser vi också dess underordnade evenemang, som visas på följande skärm. Vi kan göra samma sak för vattenpolo och se att det bara har en gren.
   

Genom att dra i dessa två hierarkier kan du snabbt skapa en pivottabell med intressanta och strukturerade data som du kan fördjupa dig i, filtrera och ordna.

Nu skapar vi samma pivottabell, utan hierarkier.

1. Ta bort Platser från området KOLUMNER i området Pivottabellfält. Ta sedan bort SDE från området RADER. Nu är du tillbaka till en vanlig pivottabell.
2. Från tabellen Värdar drar du Årstid, Stad NOC_CountryRegion och EditionID till området KOLUMNER och ordnar dem i den ordningen, uppifrån och ned.
3. Från tabellen Evenemang drar du Sport, Gren och Händelse till området RADER och ordnar dem i den ordningen, uppifrån och ned.
4. Filtrera radetiketter till de tio främsta sporterna i pivottabellen.
5. Komprimera alla rader och kolumner, expandera sedan Aquatics, sedan Dykning och vattenpolo . Arbetsboken ser ut som på skärmen nedan.
   

Skärmen ser ungefär likadan ut, förutom att du drog sju enskilda fält till områdena i pivottabellfälten , i stället för att bara dra två hierarkier. Om du är den enda personen som skapar pivottabeller eller Power View-rapporter baserade på dessa data kanske det bara verkar praktiskt att skapa hierarkier. Men när många personer 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 får du lära dig hur du använder hierarkier och andra fält i visuellt tilltalande rapporter som skapats med Power View.

Kontrollpunkt och test

Gå igenom det du lärt dig

Nu har Excel-arbetsboken en datamodell som inkluderar data från flera källor, relaterade med hjälp av befintliga fält och beräknade kolumner. Det finns även hierarkier som återspeglar datastrukturen i tabellerna, vilket gör att du snabbt och konsekvent kan skapa övertygande rapporter.

Du lärde dig hur man skapar hierarkier för att ange den inbyggda strukturen i data och snabbt använda hierarkiska data i rapporter.

I nästa självstudiekurs i den här serien får du skapa visuellt tilltalande rapporter om olympiska medaljer med hjälp av 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 genväg:

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? Nu har du chansen. Följande test tar upp de funktioner, möjligheter och krav du har läst om i den här självstudiekursen. Du hittar svaren längst ned på sidan. Lycka till!

Fråga 1: I vilken av följande vyer kan du skapa relationer mellan två tabeller?

A: Du skapar relationer mellan tabeller i Power View.

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

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

D: Allt ovanstående.

Fråga 2: SANT eller FALSKT: Du kan skapa 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 ämnen kan du skapa en DAX-formel?

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

B: I en ny kolumn i Power Pivotf.

C: I valfri cell i Excel 2013.

D: Både A och B.

Fråga 4: Vilket av följande är sant om hierarkier?

A: När du skapar en hierarki är de inkluderade fälten inte längre tillgängliga individuellt.

B: När du skapar en hierarki kan du använda de inkluderade fälten och deras hierarki i klientverktyg genom att 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