Tutorial: Extinderea relațiilor din Modelul de date folosind Excel, Power Pivot și DAX

Secțiunile acestui tutorial sunt următoarele:

• Adăugarea unei relații utilizând Vizualizarea diagramă în Power Pivot

• Extinderea modelului de date utilizând coloane calculate

• Create a hierarchy

• Utilizarea ierarhiilor în rapoartele PivotTable

• Punct de control și test

La sfârșitul acestui tutorial este un test pe care îl puteți face pentru a vă testa cunoștințele.

Această serie folosește date care descriu medalii olimpice, țări gazdă și diferite evenimente sportive olimpice. Tutorialele din această serie sunt următoarele:

1. Importul datelor în Excel și crearea unui model de date

2. Extindeți relațiile din Modelul de date utilizând Excel, Power Pivot și DAX

3. Crearea de rapoarte Power View bazate pe hărți

4. Includerea datelor de pe internet și setarea valorilor implicite pentru rapoarte Power View

5. Ajutor Power Pivot

6. Crearea de rapoarte Power View uimitoare - Partea a 2-a

Vă sugerăm să le parcurgeți în ordine.

Aceste tutoriale utilizează Excel 2013 cu Power Pivot activat. Pentru mai multe informații despre Excel 2013, faceți clic aici. Pentru instrucțiuni despre activarea Power Pivot, faceți clic aici.

Adăugarea unei relații utilizând Vizualizarea diagramă în Power Pivot

În această secțiune, utilizați programul de completare Microsoft Office Power Pivot în Excel 2013 pentru a extinde modelul. Utilizarea Vizualizării diagramă în Microsoft SQL Server PowerPivot pentru Excel simplifilă crearea relațiilor. Mai întâi, trebuie să vă asigurați că aveți Power Pivot program de completare activat.

Notă: programul de completare Power Pivot în Microsoft Excel 2013 face parte din Office Professional Plus. Consultați Pornirea Power Pivot în Microsoft Excel 2013 program de completare pentru mai multe informații.

Adăugați Power Pivot la panglica Excel activând programul de completare Power Pivot

Atunci când Power Pivot este activată, vedeți o filă de panglică în Excel 2013, numită POWER PIVOT. Pentru a activa Power Pivot, urmați acești pași.

1. Accesați Opțiuni > FIȘIER > Programe de completare.

2. În caseta Gestionare din partea de jos, faceți clic pe Programe de completare COM> Salt.

3. Bifați caseta de Power Pivot în Microsoft Excel 2013 Microsoft Office, apoi faceți clic pe OK.

Panglica Excel are acum o filă POWER PIVOT .

Adăugarea unei relații utilizând Vizualizarea diagramă în Power Pivot

Registrul de lucru Excel include un tabel denumit Gazde. Am importat Hosts prin copierea și lipirea lor în Excel, apoi am formatat datele ca tabel. Pentru a adăuga tabelul Hosts la modelul de date, trebuie să stabilim o relație. Să utilizăm Power Pivot pentru a reprezenta vizual relațiile din modelul de date, apoi să creăm relația.

1. În Excel, faceți clic pe fila Gazde pentru a o transforma în foaie activă.

2. Pe panglică, selectați POWER PIVOT > Tabele > Adăugare la modelul de date. Acest pas adaugă tabelul Hosts la modelul de date. De asemenea, deschide programul de completare Power Pivot, pe care îl utilizați pentru a efectua pașii rămași în această activitate.

3. Observați că fereastra Power Pivot afișează toate tabelele din model, inclusiv gazdele. Faceți clic pe două tabele. În Power Pivot puteți vizualiza toate datele conținute de modelul dvs., chiar dacă nu sunt afișate în nicio foaie de lucru din Excel, cum ar fi datele Discipline, Evenimente și Medals de mai jos, precum și S_Teams,W_Teamsși Sporturi.
   
   

4. În fereastra Power Pivot, în secțiunea Vizualizare , faceți clic pe Vizualizare diagramă.

5. Utilizați bara de diapozitive pentru a redimensiona diagrama, astfel încât să puteți vedea toate obiectele din diagramă. Rearanjați tabelele glisându-le bara de titlu, astfel încât să fie vizibile și poziționate unul lângă altul. Observați că patru tabele nu au legătură cu restul tabelelor: Gazde, Evenimente, W_Teams și S_Teams.
   
   

6. Observați că atât tabelul Medals , cât și tabelul Events au un câmp numit DisciplineEvent. După o inspecție ulterioară, determinați că câmpul DisciplineEvent din tabelul Events constă în valori unice, nere repetată.

1. Creați o relație între tabelul Medals și tabelul Events . În Vizualizarea diagramă, glisați câmpul DisciplineEvent din tabelul Events în câmpul DisciplineEvent din Medals. Între ele apare o linie care indică faptul că s-a stabilit o relație.

2. Faceți clic pe linia care conectează Events și Medals. Câmpurile evidențiate definesc relația, așa cum se arată în următorul ecran.
   
   
   

3. Pentru a conecta gazdele la modelul de date, avem nevoie de un câmp cu valori care identifică în mod unic fiecare rând din tabelul Hosts . Apoi putem căuta modelul nostru de date pentru a vedea dacă există aceleași date în alt tabel. Căutarea în Vizualizarea diagramă nu ne permite să facem acest lucru. Cu Opțiunea Gazde selectată, comutați înapoi la Vizualizare date.

4. După examinarea coloanelor, ne dăm seama că gazdele nu au o coloană de valori unice. Va trebui să-l creăm utilizând o coloană calculată și DAX (Data Analysis Expressions).

Este frumos atunci când datele din modelul dvs. de date au toate câmpurile necesare pentru a crea relații și combinați date pentru a le vizualiza în Power View sau în rapoartele PivotTable. Dar tabelele nu sunt întotdeauna atât de cooperante, așa că secțiunea următoare descrie cum să creați o coloană nouă, utilizând DAX, care poate fi utilizată pentru a crea o relație între tabele.

Extinderea modelului de date utilizând coloane calculate

Pentru a stabili o relație între tabelul Hosts și modelul de date și, astfel, pentru a extinde modelul nostru de date pentru a include tabelul Hosts , hosts trebuie să aibă un câmp care identifică în mod unic fiecare rând. În plus, câmpul respectiv trebuie să corespundă unui câmp din modelul de date. Aceste câmpuri corespondente, câte unul din fiecare tabel, sunt cele care permit ca datele tabelelor să fie asociate.

Deoarece tabelul Hosts nu are un astfel de câmp, trebuie să-l creați. Pentru a păstra integritatea modelului de date, nu puteți utiliza Power Pivot pentru a edita sau a șterge date existente. Totuși, puteți crea coloane noi utilizând câmpuri calculate pe baza datelor existente.

Dacă ne uităm prin tabelul Hosts , apoi ne uităm la alte tabele Model de date, găsim un bun candidat pentru un câmp unic pe care l-am putea crea în Hosts, apoi îl putem asocia cu un tabel din modelul de date. Ambele tabele vor necesita o coloană nouă, calculată, pentru a îndeplini cerințele necesare pentru a stabili o relație.

În Hosts, putem crea o coloană calculată unică prin combinarea câmpului Ediție (anul evenimentului Jocurile Olimpice) și a câmpului Season (Vara sau Iarna). În tabelul Medals există, de asemenea, un câmp Ediție și un câmp Season, așadar, dacă creăm o coloană calculată în fiecare dintre tabelele care combină câmpurile Edition și Season, putem stabili o relație între Hosts și Medals. Următorul ecran arată tabelul Hosts, cu câmpurile Edition și Season selectate

Crearea coloanelor calculate utilizând DAX

Să începem cu tabelul Hosts . Scopul este să creați o coloană calculată în tabelul Hosts , apoi în tabelul Medals , care poate fi utilizată pentru a stabili o relație între ele.

În Power Pivot, puteți utiliza DAX (Data Analysis Expressions) pentru a crea calcule. DAX este un limbaj de formule pentru Power Pivot și PivotTable, proiectat pentru datele relaționale și analiza contextuală disponibile în Power Pivot. Puteți crea formule DAX într-o nouă coloană de Power Pivot și în Zona de calcul din Power Pivot.

1. În Power Pivot, selectați PORNIRE > Vizualizare > Vizualizare date pentru a vă asigura că este selectată Vizualizarea date, nu în Vizualizarea diagramă.

2. Selectați tabelul Hosts din Power Pivot. Adiacentă coloanelor existente este o coloană goală intitulată Adăugare coloană. Power Pivot furnizează acea coloană ca substituent. Există mai multe modalități de a adăuga o coloană nouă la un tabel din Power Pivot, dintre care unul este să selectați pur și simplu coloana necompletată care are titlul Adăugare coloană.
   
   
   

3. În bara de formule, tastați următoarea formulă DAX. Funcția CONCATENATE combină două sau mai multe câmpuri într-unul singur. Pe măsură ce tastați, Completarea automată vă ajută să tastați numele complet calificate ale coloanelor și tabelelor și listează funcțiile disponibile. Utilizați fila pentru a selecta sugestii de Completare automată. De asemenea, puteți să faceți clic pur și simplu pe coloană în timp ce tastați formula și Power Pivot inserează numele coloanei în formulă.
   
   =CONCATENATE([Edition],[Season])

4. Când terminați de construit formula, apăsați pe Enter pentru a o accepta.

5. Valorile sunt populate pentru toate rândurile din coloana calculată. Dacă defilați în jos prin tabel, vedeți că fiecare rând este unic , așa că am creat cu succes un câmp care identifică în mod unic fiecare rând din tabelul Hosts . Astfel de câmpuri se numesc cheie primară.

6. Să redenumim coloana calculată în EditionID. Puteți redenumi orice coloană făcând dublu clic pe aceasta sau făcând clic dreapta pe coloană și alegând Redenumire coloană. Atunci când este finalizat, tabelul Hosts din Power Pivot arată ca următorul ecran.
   
   

Tabelul Hosts este gata. În continuare, să creăm o coloană calculată în Medals care se potrivește cu formatul coloanei EditionID pe care am creat-o în Hosts, astfel încât să putem crea o relație între ele.

1. Începeți prin a crea o coloană nouă în tabelul Medals , așa cum am făcut pentru Hosts. În Power Pivot selectați tabelul Medals și faceți clic pe Proiectare > Coloane > Adăugare. Observați că este selectată opțiunea Adăugare coloană . Acest lucru are același efect ca selectarea pur și simplu Adăugare coloană.

2. Coloana Ediție din Medals are un format diferit de coloana Ediție din Hosts. Înainte de a combina sau concatena coloana Ediție cu coloana Season pentru a crea coloana EditionID, trebuie să creăm un câmp intermediar care aduce Edition în formatul corect. În bara de formule de deasupra tabelului, tastați următoarea formulă DAX.

   = YEAR([Edition])
   

3. Când terminați de construit formula, apăsați pe Enter. Valorile sunt populate pentru toate rândurile din coloana calculată, pe baza formulei introduse. Dacă comparați această coloană cu coloana Ediție din Hosts, veți vedea că aceste coloane au același format.

4. Redenumiți coloana făcând clic dreapta pe CalculatEdColumn1 și selectând Redenumire coloană. Tastați An, apoi apăsați pe Enter.

5. Când ați creat o coloană nouă, Power Pivot adăugat o altă coloană substituent numită Adăugare coloană. În continuare, dorim să creăm coloana calculată EditionID, așadar, selectați Adăugare coloană. În bara de formule, tastați următoarea formulă DAX și apăsați pe Enter.
   
   =CONCATENATE([Year],[Season])

6. Redenumiți coloana făcând dublu clic pe CalculatedColumn1 și tastând EditionID.

7. Sortați coloana în ordine ascendentă. Tabelul Medals din Power Pivot arată acum ca următorul ecran.
   
   
   

Observați că multe valori se repetă în câmpul EditionID al tabelului Medals . Este în regulă și este de așteptat, deoarece în timpul fiecărei ediții a Jocurilor Olimpice (acum reprezentată de valoarea EditionID) au fost acordate multe medalii. Ceea ce este unic în tabelul Medals este fiecare medalie acordată. Identificatorul unic pentru fiecare înregistrare din tabelul Medals și cheia sa primară desemnată este câmpul MedalKey.

Următorul pas este să creați o relație între Hosts și Medals.

Crearea unei relații utilizând coloane calculate

În continuare, să utilizăm coloanele calculate pe care le-am creat pentru a stabili o relație între Hosts și Medals.

1. În fereastra Power Pivot, selectați Pornire > Vizualizare > Vizualizare diagramă din panglică. De asemenea, puteți comuta între vizualizarea Grilă și Vizualizarea diagramă utilizând butoanele din partea de jos a ferestrei PowerView, așa cum se arată în următorul ecran.
   
   

2. Extindeți Gazde, astfel încât să puteți vizualiza toate câmpurile sale. Am creat coloana EditionID pentru a acționa ca cheie primară a tabelului Hosts (câmp unic, nere repetat) și am creat o coloană EditionID în tabelul Medals pentru a permite stabilirea unei relații între ele. Trebuie să le găsim pe amândouă și să creăm o relație. Power Pivot oferă o caracteristică Găsire pe panglică, astfel încât să puteți căuta câmpurile corespunzătoare în Modelul de date. Următorul ecran afișează fereastra Găsire metadate , cu EditionID introdus în câmpul De găsit .
   

3. Poziționați tabelul Hosts astfel încât să fie lângă Medals.

4. Glisați coloana EditionID din Medals în coloana EditionID din Hosts. Power Pivot creează o relație între tabele pe baza coloanei EditionID și trasează o linie între cele două coloane, indicând relația.
   
   

În această secțiune, ați învățat o nouă tehnică pentru adăugarea de coloane noi, ați creat o coloană calculată utilizând DAX și ați utilizat acea coloană pentru a stabili o nouă relație între tabele. Tabelul Hosts este acum integrat în modelul de date, iar datele sale sunt disponibile pentru raportul PivotTable din Foaie1. De asemenea, puteți utiliza datele asociate pentru a crea rapoarte PivotTable, PivotChart, Power View suplimentare și multe altele.

Create a hierarchy

Majoritatea modelelor de date includ date inerent ierarhice. Printre exemplele comune se numără datele din calendar, datele geografice și categoriile de produse. Crearea de ierarhii în cadrul Power Pivot este utilă deoarece aveți posibilitatea să glisați un element într-un raport - ierarhia - în loc să asamblați și să ordonați aceleași câmpuri de fiecare dată.

Datele despre Jocurile Olimpice sunt, de asemenea, ierarhice. Este util să înțelegeți ierarhia Jocurilor Olimpice în ceea ce privește sporturile, disciplinele și evenimentele. Pentru fiecare sport, există una sau mai multe discipline asociate (uneori există multe). Iar pentru fiecare disciplină, există unul sau mai multe evenimente (din nou, uneori, există multe evenimente în fiecare disciplină). Următoarea imagine ilustrează ierarhia.

În această secțiune, creați două ierarhii în datele olimpice pe care le-ați utilizat în acest tutorial. Apoi utilizați aceste ierarhii pentru a vedea cum simplifică organizarea datelor în rapoartele PivotTable și, într-un tutorial ulterior, în Power View.

Crearea unei ierarhii Sport

1. În Power Pivot, comutați la Vizualizare diagramă. Extindeți tabelul Events , astfel încât să puteți vedea mai ușor toate câmpurile sale.

2. Apăsați și țineți apăsată tasta Ctrl și faceți clic pe câmpurile Sport, Disciplina și Eveniment. Cu aceste trei câmpuri selectate, faceți clic dreapta și selectați Creare ierarhie. Un nod ierarhie părinte, Ierarhia 1, este creat în partea de jos a tabelului, iar coloanele selectate sunt copiate sub ierarhie ca noduri fiu. Verificați dacă Sport apare primul în ierarhie, apoi Discipline, apoi Eveniment.

3. Faceți dublu clic pe titlu, Ierarhie1, și tastați SDE pentru a redenumi noua ierarhie. Acum aveți o ierarhie care include Sport, Disciplină și Eveniment. Tabelul Evenimente arată acum ca următorul ecran.
   
   

Crearea unei ierarhii de locație

1. Tot în Vizualizarea diagramă din Power Pivot, selectați tabelul Hosts și faceți clic pe butonul Creare ierarhie din antetul tabelului, așa cum se arată în următorul ecran.
   
   
   În partea de jos a tabelului apare un nod părinte de ierarhie gol.

2. Tastați Locații ca nume pentru noua ierarhie.

3. Există multe modalități de a adăuga coloane într-o ierarhie. Glisați câmpurile Season, City și NOC_CountryRegion în numele ierarhiei (în acest caz, Locații) până când este evidențiat numele ierarhiei, apoi eliberați-l pentru a le adăuga.

4. Faceți clic dreapta pe EditionID și selectați Adăugare la ierarhie. Alegeți Locații.

5. Asigurați-vă că nodurile fiu de ierarhie sunt în ordine. De sus în jos, ordinea ar trebui să fie: Season, NOC, City, EditionID. Dacă nodurile copilului dvs. sunt în afara ordinii, glisați-le pur și simplu în ordinea corespunzătoare din ierarhie. Tabelul ar trebui să arate ca următorul ecran.
   

Modelul dvs. de date are acum ierarhii care pot fi utilizate în mod util în rapoarte. În secțiunea următoare, veți afla cum aceste ierarhii pot face crearea de rapoarte mai rapidă și mai unitară.

Utilizarea ierarhiilor în rapoartele PivotTable

Acum, că avem o ierarhie Sport și o ierarhie locații, le putem adăuga în rapoartele PivotTable sau Power View și putem obține rapid rezultate care includ grupări utile de date. Înainte de a crea ierarhii, a trebuit să adăugați câmpuri individuale în raportul PivotTable și să aranjați câmpurile respective așa cum doriți să fie vizualizate.

În această secțiune, utilizați ierarhiile create în secțiunea anterioară pentru a rafina rapid raportul PivotTable. Apoi creați aceeași vizualizare PivotTable utilizând câmpurile individuale din ierarhie, ca să puteți compara utilizând ierarhii cu utilizarea câmpurilor individuale.

1. Reveniți la Excel.

2. În Foaie1, eliminați câmpurile din zona RÂNDURI din Câmpuri PivotTable, apoi eliminați toate câmpurile din zona COLOANE. Asigurați-vă că este selectat raportul PivotTable (care este acum destul de mic, astfel încât să puteți alege celula A1 pentru a vă asigura că pivottable este selectat). Singurele câmpuri rămase din câmpurile PivotTable sunt Medal în zona FILTERS și Count of Medal în zona VALUES. Raportul PivotTable aproape gol ar trebui să arate ca următorul ecran.

   

3. Din zona Câmpuri PivotTable, glisați SDE din tabelul Events în zona RÂNDURI. Apoi glisați Locații din tabelul Gazde în zona COLOANE . Glisând aceste două ierarhii, raportul PivotTable este populat cu o mulțime de date, toate fiind aranjate în ierarhia definită în pașii anteriori. Ecranul ar trebui să arate ca următorul ecran.
   

4. Să filtrăm puțin datele și să vedem primele zece rânduri de evenimente. În raportul PivotTable, faceți clic pe săgeata din Etichete de rânduri, faceți clic pe (Selectare totală) pentru a elimina toate selecțiile, apoi faceți clic pe casetele de lângă primele zece Sporturi. Raportul PivotTable arată acum ca următorul ecran.
   

5. Puteți să extindeți oricare dintre aceste sporturi din PivotTable, care este nivelul superior al ierarhiei SDE, și să vedeți informațiile din nivelul următor în jos în ierarhie (disciplină). Dacă există un nivel inferior în ierarhie pentru acea disciplină, puteți extinde disciplina pentru a vedea evenimentele sale. Puteți face același lucru pentru ierarhia locație, al cărei nivel superior este Season, care se afișează ca Vară și Iarnă în PivotTable. Când extindem sportul Acvatic, vedem toate elementele sale de disciplină pentru copii și datele lor. Când extindem disciplina Diving sub Acvatice, vedem și evenimentele sale pentru copii, așa cum se arată în următorul ecran. Putem face același lucru pentru Polo pe apă, și să vedem că are un singur eveniment.
   

Glisând acele două ierarhii, ați creat rapid un raport PivotTable cu date interesante și structurate pe care le puteți detalia, filtra și aranja.

Acum să creăm același PivotTable, fără avantajul ierarhiilor.

1. În zona Câmpuri PivotTable, eliminați Locații din zona COLOANE. Apoi eliminați SDE din zona RÂNDURI. Reveniți la un raport PivotTable simplu.

2. Din tabelul Gazde , glisați Season, City, NOC_CountryRegion și EditionID în zona COLOANE și aranjați-le în această ordine, de sus în jos.

3. Din tabelul Evenimente , glisați Sport, Disciplina și Eveniment în zona RÂNDURI și aranjați-le în această ordine, de sus în jos.

4. În raportul PivotTable, filtrați Etichetele de rânduri la primele zece sporturi.

5. Restrângeți toate rândurile și coloanele, apoi extindeți Acvatice, apoi Diving și Polo pe apă . Registrul de lucru arată ca următorul ecran.
   

Ecranul arată asemănător, cu excepția faptului că ați glisat șapte câmpuri individuale în zonele Câmpuri PivotTable , în loc să glisați pur și simplu două ierarhii. Dacă sunteți singura persoană care creează rapoarte PivotTable sau Power View pe baza acestor date, crearea ierarhiilor poate părea convenabilă. Dar atunci când multe persoane creează rapoarte și trebuie să-și dea seama ordinea corespunzătoare a câmpurilor pentru a corecta vizualizările, ierarhiile devin rapid o îmbunătățire a productivității și permit consistența.

Într-un alt tutorial, veți afla cum să utilizați ierarhii și alte câmpuri în rapoartele captivante vizual create utilizând Power View.

Punct de control și test

Revizuiți ce ați învățat

Registrul de lucru Excel are acum un model de date care include date din mai multe surse, asociate utilizând câmpuri existente și coloane calculate. De asemenea, aveți ierarhii care reflectă structura datelor din tabelele dvs., ceea ce face crearea de rapoarte convingătoare rapidă, consistentă și ușoară.

Ați învățat că crearea de ierarhii vă permite să specificați structura inerentă din datele dvs. și să utilizați rapid date ierarhice în rapoartele dvs.

În următorul tutorial din această serie, creați rapoarte convingătoare despre medalii olimpice utilizând Power View. De asemenea, efectuați mai multe calcule, optimizați datele pentru crearea rapidă de rapoarte și importați date suplimentare pentru a face rapoartele și mai interesante. Iată un link:

Tutorial 3: Crearea de rapoarte Power View bazate pe hărți

TEST

Vreți să vedeți cât de bine vă amintiți ce ați învățat? Iată șansa ta. Testul următor evidențiază caracteristicile, capabilitățile sau cerințele despre care ați învățat în acest tutorial. În partea de jos a paginii, veți găsi răspunsurile. Succes!

Întrebarea 1: Care dintre următoarele vizualizări vă permit să creați relații între două tabele?

R: Creați relații între tabele în Power View.

B: Creați relații între tabele utilizând Vizualizarea proiect în Power Pivot.

C: Creați relații între tabele utilizând Vizualizarea grilă în Power Pivot

D: Toate cele de mai sus

Întrebarea 2: TRUE sau FALSE: Puteți stabili relații între tabele pe baza unui identificator unic care este creat utilizând formule DAX.

R: TRUE

B: FALSE

Întrebarea 3: În care dintre următoarele variante puteți crea o formulă DAX?

R: În zona de calcul din Power Pivot.

B: Într-o coloană nouă din Power Pivot f.

C: În orice celulă din Excel 2013.

D: Atât A, cât și B.

Întrebarea 4: Care dintre următoarele lucruri este adevărată despre ierarhii?

R: Atunci când creați o ierarhie, câmpurile incluse nu mai sunt disponibile individual.

B: Atunci când creați o ierarhie, câmpurile incluse, inclusiv ierarhia lor, pot fi utilizate în instrumentele client glisând pur și simplu ierarhia într-o zonă Power View sau PivotTable.

C: Atunci când creați o ierarhie, datele subiacente din modelul de date sunt combinate într-un singur câmp.

D: Nu puteți crea ierarhii în Power Pivot.

Răspunsuri test

1. Răspuns corect: D

2. Răspuns corect: A

3. Răspuns corect: D

4. Răspuns corect: B