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 från diagramvyn i Power Pivot

• Utöka datamodellen med beräknade kolumner

• Skapa en hierarki

• Använd 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.

De här självstudiekurserna använda Excel 2013 med Power Pivot aktiverad. Mer information om Excel 2013 klickar du här. Råd om 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 Microsoft Office Power Pivot i Excel 2013 för att utöka modellen. Det är enkelt att skapa relationer med diagramvyn i Microsoft SQL Server Power Pivot for Excel. Först måste du se till att du har tilläggsprogrammet Power Pivot aktiverat.

Obs: Tillägget Power Pivot i Microsoft Excel 2013 är en del av Office Professional Plus. Mer information finns i Starta Power Pivot in Microsoft Excel 2013-tilläggsprogrammet .

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

När Power Pivot är aktiverat visas en flik på menyfliksområdet i Excel 2013 med namnet POWER PIVOT. Aktivera Power Pivot genom att följa stegen nedan.

1. Gå till Arkiv > Alternativ > Tilläggsprogram.

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

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

Nu visas fliken POWER PIVOT på menyfliksområdet i Excel.

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

Excel-arbetsboken innehåller en tabell med namnet Värdar. Vi importerade Värdar genom att kopiera den och klistra in den i Excel. Sedan formaterade vi data som en tabell. För att lägga till tabellen Värdar i datamodellen måste vi upprätta en relation. Vi kan använda Power Pivot till att visuellt presentera 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. På menyfliksområdet väljer du POWER PIVOT > Tabeller > Lägg till i datamodell. Nu läggs tabellen Värdar till i datamodellen. Dessutom öppnas tilläggsprogrammet Power Pivot som du använder till att utföra resterande steg i den här uppgiften.

3. Lägg märke till att fönstret Power Pivot visas alla tabeller i modellen, inklusive värdar. Klicka dig igenom några tabeller. I Power Pivot kan du visa alla data som modellen innehåller, även om de inte visas i ett kalkylblad i Excel, till exempel grenar, händelseroch medaljer data under, samt S_Teams,W_ Team, och Sport.
   
   

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

5. Använd skjutreglaget om du vill ändra storlek på diagrammet så att du kan se alla objekt i diagrammet. Ordna om tabellerna genom att dra deras namnlisten så att de blir synliga och placerade intill varandra. Lägg märke till att fyra tabeller är kopplade till resten av tabellerna: värdar, händelser, W_Teamsoch S_Teams.
   
   

6. Du märker att både tabellen Medaljer och Evenemang har ett fält som heter GrenEvenemang. Vid en närmare undersökning ser du att fältet GrenEvenemang i tabellen Evenemang består av unika värden som inte upprepas.

1. Skapa en relation mellan tabellen Medaljer och tabellen Evenemang. I diagramvyn drar du fältet GrenEvenemang från tabellen Evenemang till fältet GrenEvenemang i Medaljer. En linje visas mellan dem, vilket indikerar att en relation har skapats.

2. Klicka på den linje som kopplar samman händelser och medaljer. De markerade fälten definiera relationen, enligt 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. Detta kan inte göras i diagramvyn. Markera Värdar och växla tillbaka till datavyn.

4. När du har granskat kolumnerna inser att värdar inte har en kolumn med unika värden. Vi måste du skapa den med hjälp av en beräknad kolumn och Data Analysis Expressions (DAX).

Det är trevligt när data i datamodellen har alla fält som behövs för att skapa relationer och kombinera data för visualisering i Power View eller i pivottabeller. Men så är inte fallet med alla tabeller så nästa avsnitt beskriver hur du skapar en ny kolumn med DAX som kan användas till 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 datamodellen till att inkludera tabellen Värdar, måste Värdar ha ett fält som unikt identifierar varje rad. Dessutom måste det fältet motsvara ett fält i datamodellen. De här motsvarande fälten, ett i varje tabell, gör att man kan sammankoppla tabellernas data.

Eftersom tabellen värdar saknar ett fält, måste du skapa den. Du kan inte använda Power Pivot om du vill bevara integritet datamodellen, redigera eller ta bort befintliga data. Du kan skapa nya kolumner med hjälp av beräknade fält utifrån befintliga data.

Efter att ha tittat genom tabellen Värdar och andra datamodelltabeller hittar vi en bra kandidat för ett unikt fält vi kan skapa i tabellen 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 kraven för att upprätta en relation.

I Värdar kan vi skapa en unik beräknad kolumn genom att kombinera fältet Årgång (året då de olympiska spelen hölls) och fältet Årstid (sommar eller vinter). Tabellen Medaljer innehåller också fälten Årgång och ett Årstid, så om vi skapar en beräknad kolumn i var och en av de tabeller som kombinerar fälten Årgång och Årstid kan vi skapa 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 som har utformats för relationsdata och kontextuell analys 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 säkerställa att datavyn är vald.

2. Markera tabellen värdar i Power Pivot. Bredvid de befintliga kolumnerna är en tom kolumn med titeln Lägg till kolumn. Power Pivot innehåller 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 är att markera den tomma kolumnen med rubrik Lägg till kolumn.
   
   
   

3. Skriv in följande DAX-formel i formelfältet. Funktionen sammanfoga kombinerar två eller fler fält till en. Vid inskrivning Komplettera automatiskt kan du ange de fullständigt kvalificerade namnen på kolumner och tabeller och visar de funktioner som är tillgängliga. Använd TABB för att markera Komplettera automatiskt förslag. Du kan också klicka på kolumnen medan skriva formeln och Power Pivot infogar kolumnnamnet i din formel.
   
   =CONCATENATE([Edition],[Season])

4. När du har skrivit klart formeln trycker du på Enter för att godkänna 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. Vi har nu skapat ett fält som unikt identifierar varje rad i tabellen Värdar. Sådana fält kallas för en primärnyckel.

6. Nu ska vi döpa den beräknade kolumnen till ÅrgångsID. Du kan byta namn på en kolumn genom att dubbelklicka på den eller genom att högerklicka på den och sedan välja Byt namn på kolumn. När det är klart ser tabellen Värdar i PowerPivot ut som på bilden nedan.
   
   

Tabellen Värdar är klar. Låt oss nu skapa en beräknad kolumn i Medaljer som matchar formatet för kolumnen ÅrgångsID som du skapade i Värdar så att du kan skapa en relation mellan dem.

1. Börja med att skapa en ny kolumn i tabellen Medaljer som vi gjorde för Värdar. I Power Pivot väljer du tabellen Medaljer och klickar på Design > Kolumner > Lägg till. Lägg märke till att Lägg till kolumn är markerad. Det här är samma sak som att helt enkelt välja Lägg till kolumn.

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

   = YEAR([Edition])
   

3. När du har skrivit formeln trycker du på Enter. Värden fylls i för alla rader i den beräknade kolumnen utifrån formeln du angav. Om du jämför den här kolumnen med kolumnen Årgång i Värdar ser du att de här kolumnerna har samma format.

4. Byt namn på kolumnen genom att högerklicka på BeräknadKolumn1 och välja Byt namn på kolumn. Skriv År och tryck på Enter.

5. När du har skapat en ny kolumn läggs en till platshållarkolumn i Power Pivot med namnet Lägg till kolumn. Vi vill nu skapa den beräknade kolumnen ÅrgångsID. Välj därför Lägg till kolumn. Skriv följande DAX-formel i formelfältet och tryck på Enter.
   
   =CONCATENATE([Year],[Season])

6. Byt namn på kolumnen genom att dubbelklicka på BeräknadKolumn1 och skriva ÅrgångsID.

7. Sortera kolumnen i stigande ordning. Tabellen medaljer i Power Pivot nu ser ut som på skärmen nedan.
   
   
   

Observera att många värden upprepas i fältet ÅrgångsID i tabellen Medaljer. Det är helt i sin ordning eftersom att många medaljer delas ut varje år som de olympiska spelen hålls (det som nu som representeras av ÅrgångsID-värden). Det unika i tabellen Medaljer är varje utdelad medalj. MedaljNyckel är det unika ID:t för varje post i tabellen Medaljer och dess tilldelade primärnyckel.

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

Skapa en relation med beräknade kolumner

Nästa steg är att använda de beräknade kolumner vi skapade för att upprätta en relation mellan Värdar och Medaljer.

1. Välj Start > visa > diagramvyn i menyfliksområdet i fönstret Power Pivot. Du kan också växla mellan diagramvyn och diagramvy med hjälp av knapparna längst ned i fönstret PowerView som visas på bilden nedan.
   
   

2. Expandera värdar så att du kan visa alla dess fält. Vi skapade kolumnen Årgångsid ska fungera som värdar tabell primärnyckeln (unika, ej upprepade resursfält) och skapas en Årgångsid kolumn i tabellen medaljer att upprätta en relation mellan dem. Vi måste du hitta dem båda och skapa en relation. Power Pivot ger en sökning i menyfliksområdet så att du kan söka datamodellen för motsvarande fält. Följande bild visar fönstret Sök Metadata med Årgångsid som anges i fältet Sök efter.
   

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

4. Dra kolumnen Årgångsid i medaljer till kolumnen Årgångsid i värdar. Power Pivot skapar en relation mellan tabellerna baserat på kolumnen Årgångsid och ritar en linje mellan de två kolumnerna, som anger relationen.
   
   

I det här avsnittet du har lärt dig en ny metod för att lägga till nya kolumner, skapas en beräknad kolumn med DAX, och använder den kolumnen du upprättar en ny relation mellan tabeller. Tabellen värdar nu är integrerat i datamodellen och dess data är tillgänglig för pivottabell i Blad1. Du kan också använda den tillhörande 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 att du kan dra ett objekt till en rapport, hierarkin, istä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å hierarkien av olympisk data närt det gäller sporter, grenar och evenemang. För varje sport finns det en eller flera associerade grenar (ibland finns det många). För varje gren finns det ett eller flera evenemang (återigen kan det finnas många evenemang i varje gren). Hierarkin illustreras på följande bild.

I det här avsnittet skapar du två hierarkier av de data du har använt i den här självstudiekursen. Sedan använder du hierarkierna till att se hur hierarkier gör det lätt att ordna data i pivottabeller och, i en senare självstudiekurs, i Power View.

Skapa en Sport-hierarki

1. I Power Pivot kan du växla till Diagramvy. Expandera tabellen Evenemang så att det blir lättare att se alla fälten.

2. Håll Ctrl nedtryckt och klicka på fälten Sport, Gren och Evenemang. Med de tre fälten markerade, högerklicka och välj 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, följt av Gren och 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 Evenemang ser nu ut som på bilden nedan.
   
   

Skapa en platshierarki

1. Markera tabellen värdar fortfarande i diagramvyn i Power Pivot, och klicka på knappen Skapa hierarki i tabellrubriken, som visas på bilden nedan.
   
   
   En tom hierarkin överordnad nod 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_LandRegion till hierarkinamnet (i det här fallet Platser) tills hierarkinamnet är markerat och släpp dem sedan för att lägga till dem.

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

5. Se till att din hierarki underordnade noder i ordning. Uppifrån och ned ska vara: årstid, Nätverksoperationscentralen, ort, Årgångsid. Om underordnade noder i ordning kan helt enkelt dra dem till rätt ordning i hierarkin. Tabellen bör se ut som på skärmen nedan.
   

Datamodellen har nu hierarkier som kan komma väl till pass i rapporter. I nästa avsnitt får du veta hur de här hierarkierna kan göra det snabbare och mer konsekvent att skapa rapporter.

Använd hierarkier i pivottabeller

Nu när vi har hierarkierna Sporter och Platser kan vi lägga till dem i pivottabeller eller i Power View och snabbt få resultat som innehåller användbara datagrupperingar. Innan du skapar hierarkier du måste lägga till enskilda fält i pivottabellen och ordna de fälten som du vill att de ska visas.

I det här avsnittet använder du de hierarkier som skapades i föregående avsnitt till att snabbt förfina din pivottabell. Sedan kan du skapa samma pivottabellvy med hjälp av enskilda fält i hierarkin så att du kan jämföra hur det är att använda hierarkier jämfört med enskilda fält.

1. Gå tillbaka till Excel.

2. I Blad1 tar du bort fälten i området RADER i Pivottabellfält och tar sedan bort alla fält från området KOLUMNER. Kontrollera att pivottabellen är markerad (eftersom tabellen är ganska liten för närvarande så kan du välja cell A1 för att säkerställa att pivottabellen är markerad). De enda återstående fälten i Pivottabellfält är Medalj i området FILTER och antal Medaljer i området VÄRDEN. Den nästan tomma pivottabellen bör nu se ut som på bilden nedan.

   

3. Dra SDE från området PivotTable Fields i tabellen evenemang till området rader. Dra platser i tabellen värdar till området kolumner . Genom att dra de två hierarkierna fylls din pivottabell med stora mängder data, vilket är ordnad i hierarkin som du har definierat i föregående steg. Skärmen bör se ut som på skärmen nedan.
   

4. Nu ska vi filtrera data per bitar, och bara se de tio första raderna i händelser. Klicka på pilen i Radetiketter, i pivottabellen, klicka på (Markera alla) om du vill ta bort alla markeringar och klicka sedan på kryssrutorna bredvid de tio första Sporterna. Pivottabellen ser nu ut som på skärmen nedan.
   

5. Du kan expandera något av dessa sport i pivottabellen, som är den översta nivån i hierarkin SDE, och visa information i nästa nivå ned i hierarkin (gren). Om det finns en lägre nivå i hierarkin för den gren, kan du expandera gren om du vill visa dess händelser. Du kan göra samma för platshierarki översta nivå är årstid visas som sommar och vinter i pivottabellen. När vi expanderar Aquatics sport ser vi alla dess underordnade gren element och tillhörande data. När vi expanderar Diving gren under Aquatics Se vi dess underordnade händelser, enligt bilden nedan. Vi kan gör samma sak med vatten Polo och ser att den innehåller endast en händelse.
   

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

Låt oss nu skapa samma pivottabell men utan fördelen med hierarkier.

1. Ta bort Platser från området KOLUMNER i området Pivottabellfält. Ta bort SDE från området RADER. Nu visas en enkel pivottabell.

2. I tabellen Värdar drar du Årstid, Ort, NOC_LandRegion och ÅrgångsID till området KOLUMNER och ordnar dem uppifrån och ned.

3. I tabellen Evenemang drar du Sport, Gren och Evenemang till området RADER och ordnar dem uppifrån och ned.

4. I pivottabellen filtrerar du radetiketter till de tio första sporterna.

5. Dölja alla rader och kolumner och sedan Aquatics, och sedan Diving och vatten Polo. Din arbetsbok ser ut som på skärmen nedan.
   

Biden ser nästan likadan ut, förutom att du drog sju enskilda fält till området Pivottabellfält istä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 kan det verka som att hierarkier bara skapas av bekvämlighetsskäl. Men när många personer skapar rapporter och måste ta reda på hur fälten ska ordnas för att få rätt vyer blir hierarkier snabbt en produktivitetshöjande åtgärd som dessutom ger konsekventa resultat.

I en annan självstudiekurs lär du dig hur du använder hierarkier och andra fält i visuellt engagerande rapporter som skapas med hjälp av 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 hjälp av befintliga fält och beräknade kolumner. Du har också hierarkier som avspeglar strukturen av data i tabellerna, vilket gör det snabbt, konsekvent och enkelt att skapa tilltalande rapporter.

Du lärde dig vikten av att skapa hierarkier där du kan ange den inneboende strukturen i data och snabbt använda hierarkiska data i rapporterna.

I nästa självstudiekurs i den här serien kan du skapa visuellt tilltalande rapporter om olympiska medaljer med Power View. Du kan också göra ytterligare beräkningar, optimera data för att skapa rapporter snabbt och importera 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: Med 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 med hjälp av designvyn i Power Pivot.

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

D: Alla ovanstående

Fråga 2: SANT eller FALSKT: Du kan upprätta relationer mellan tabeller utifrån ett unikt ID som skapas med hjälp av DAX-formler.

A: SANT

B: FALSKT

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

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

B: I en ny kolumn i Power Pivot.

C: I en cell i Excel 2013.

D: Både A och B.

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

A: När en hierarki skapas är inkluderade fält inte längre tillgängliga individuellt.

B: När en hierarki skapas kan inkluderade fält och tillhörande hierarki användas i klientverktyg genom att enkelt dra hierarkin till ett Power View- eller pivottabellområde.

C: När du skapar en hierarki kombineras underliggande data i datamodellen till 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