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

Rezumat: Acesta este al doilea tutorial dintr-o serie. În primul tutorial, Importul datelor și crearea unui model de date, a fost creat un registru de lucru Excel utilizând date importate din mai multe surse.

In acest tutorial, utilizați Power Pivot pentru a extinde modelul de date, a crea ierarhii și a construi câmpuri calculate din datele existente pentru a crea noi relații între tabele.

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. Extinderea relațiilor din Modelul de date folosind 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 Power Pivot pentru Excel facilitează crearea relațiilor. Mai întâi, trebuie să vă asigurați că aveți programul de completare Power Pivot activat.

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

Adăugați Power Pivot în panglica Excel, activând programul de completare Power Pivot

Când este activat Power Pivot, 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 Programele de completare Opțiuni > fișier>.
2. În caseta Gestionare din partea de jos, faceți clic pe Programe de completare> COM Go.
3. Bifați caseta Microsoft Office Power Pivot în Microsoft Excel 2013, 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 gazde copiindu-le și lipindu-le î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 Hosts pentru a o transforma în foaia activă.

2. Pe panglică, selectați Tabele POWER PIVOT > Adăugare > la model 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 restul pașilor din această activitate.

3. Observați că fereastra Power Pivot afișează toate tabelele din model, inclusiv gazdele. Faceți clic în două tabele. În Power Pivot, puteți vizualiza toate datele pe care le conține modelul dvs., chiar dacă acestea nu sunt afișate în nicio foaie de lucru din Excel, cum ar fi datele despre discipline, evenimente și medalii 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 glisare pentru a redimensiona diagrama, astfel încât să puteți vedea toate obiectele din diagramă. Rearanjați tabelele glisând bara lor de titlu, astfel încât să fie vizibile și poziționate unele lângă altele. Observați că patru tabele nu sunt legate de restul tabelelor: Hosts, Events, W_Teams și S_Teams.

   

6. Observați că atât tabelul Medalii , cât și tabelul Evenimente au un câmp denumit EvenimentDisciplina. La o inspectare mai amănunțitată, determinați că câmpul EvenimentDisciplină din tabelul Evenimente constă din valori unice, nerepetate.

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

2. Faceți clic pe linia care conectează evenimentele și medaliile. 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 Gazde . Apoi, putem căuta în modelul nostru de date pentru a vedea dacă aceleași date se află în alt tabel. Vizualizarea diagramă nu ne permite să facem acest lucru. Cu Hosts selectat, comutați înapoi la Vizualizare date.

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

Este plăcut când datele din modelul de date au toate câmpurile necesare pentru a crea relații și a combina date pentru a le vizualiza în Power View sau rapoarte PivotTable. Dar tabelele nu sunt întotdeauna atât de cooperante, așa că următoarea secțiune descrie cum se creează 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, a extinde modelul nostru de date pentru a include tabelul Hosts , Hosts trebuie să aibă un câmp care identifică unic fiecare rând. În plus, câmpul trebuie să corespundă unui câmp din modelul de date. Aceste câmpuri corespunzătoare, câte unul în fiecare tabel, sunt cele care permit asocierea datelor din tabele.

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. Cu toate acestea, puteți crea coloane noi utilizând câmpuri calculate bazate pe datele existente.

Parcurgând tabelul Hosts , apoi analizând alte tabele Model de date, găsim un bun candidat pentru un câmp unic pe care îl putem crea în Hosts și pe care î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 Gazde, putem crea o coloană calculată unică, combinând câmpul Edition (anul evenimentului olimpic) și câmpul Season (summer sau winter). În tabelul Medalii există, de asemenea, un câmp Edition și un câmp Season, astfel încât dacă creăm o coloană calculată în fiecare dintre aceste tabele care combină câmpurile Edition și Season, putem stabili o relație între Hosts și Medals. Ecranul următor afișează tabelul Hosts , cu câmpurile Edition și Season selectate

        

Crearea coloanelor calculate utilizând DAX

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

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

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

2. Selectați tabelul Hosts în Power Pivot. Adiacentă coloanelor existente se află o coloană necompletată numită Adăugare coloană. Power Pivot furnizează acea coloană ca substituent. Există numeroase modalități de a adăuga o coloană nouă la un tabel în Power Pivot, dintre care una este pur și simplu selectarea coloanei goale 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, Completare automată vă ajută să tastați nume complet de coloane și tabele și listează funcțiile care sunt disponibile. Utilizați tab pentru a selecta sugestii de Completare automată. De asemenea, puteți face pur și simplu clic pe coloană în timp ce tastați formula, iar Power Pivot inserează numele coloanei în formulă.

   =CONCATENATE([Edition],[Season])

4. După ce terminați de construit formula, apăsați pe Enter pentru a o accepta.

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

6. Să redenumim coloana calculată în EditionID. Puteți redenumi orice coloană, făcând dublu clic pe ea sau făcând clic dreapta pe coloană și alegând Redenumire coloană. 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 să se potrivească cu formatul coloanei ID ediție 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 Medalii , așa cum am făcut pentru Gazde. Î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 simpla selectare a Adăugare coloană.

2. Coloana Edition din Medals are un format diferit de coloana Edition din Hosts. Înainte de a combina sau a concatena coloana Edition 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 completate pentru toate rândurile din coloana calculată, pe baza formulei pe care ați introdus-o. Dacă comparați această coloană cu coloana Edition 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. Atunci când ați creat o coloană nouă, Power Pivot a adăugat 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 Medalii din Power Pivot arată acum ca următorul ecran.

   

Observați că multe valori se repetă în câmpul ID ediție din tabelul Medals . Este de așteptat și în regulă, deoarece în timpul fiecărei ediții a Jocurilor Olimpice (reprezentate acum de valoarea EditionID) au fost acordate multe medalii. Ceea ce este unic în tabelul de medalii este fiecare medalie acordată. Identificatorul unic pentru fiecare înregistrare din tabelul Medals și cheia 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 Vizualizare > reședință > 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 Hosts so you can view all of its fields. Am creat coloana EditionID pentru a acționa ca cheie primară din tabelul Hosts (câmp unic, nerepetat) și am creat o coloană EditionID în tabelul Medals pentru a permite stabilirea unei relații între acestea. Trebuie să-i găsim pe amândoi și să creăm o relație. Power Pivot oferă o caracteristică Găsire în panglică, astfel încât să puteți căuta în modelul de date câmpurile corespunzătoare. Ecranul următor 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 spre 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 coloana respectivă pentru a stabili o relație nouă între tabele. Tabelul Hosts este acum integrat în modelul de date, iar datele sale sunt disponibile pentru raportul PivotTable în Foaie1. De asemenea, puteți utiliza datele asociate pentru a crea rapoarte PivotTable, PivotChart, Power View și multe altele suplimentare.

Create a hierarchy

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

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 sunt mai multe). Și pentru fiecare disciplină, există unul sau mai multe evenimente (din nou, uneori sunt multe evenimente în fiecare disciplină). Următoarea imagine ilustrează ierarhia.

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

Crearea unei ierarhii sportive

1. În Power Pivot, comutați la vizualizarea 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, Disciplină și Eveniment. Cu aceste trei câmpuri selectate, faceți clic cu butonul din dreapta și selectați Creați 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 secundare. Verificați dacă Sport apare primul în ierarhie, apoi Disciplina, apoi Eveniment.

3. Faceți dublu clic pe titlu, Ierarhie1, și tastați SDE pentru a vă 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 Create Hierarchy ( 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 al ierarhiei necompletat.

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

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

4. Faceți clic dreapta pe EditionID și selectați Add to Hierarchy. Alegeți locațiile.

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

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

Utilizarea ierarhiilor în rapoartele PivotTable

Acum că avem o ierarhie de sporturi și o ierarhie de locații, le putem adăuga la rapoarte PivotTable sau Power View și putem obține rapid rezultate care includ grupări utile de date. Înainte de a crea ierarhii, trebuia să adăugați câmpuri individuale în raportul PivotTable și să aranjați câmpurile 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, pentru a putea compara utilizarea ierarhiilor cu utilizarea câmpurilor individuale.

1. Reveniți la Excel.
2. În Foaia1, 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 acum este destul de mic, astfel încât să puteți alege celula A1 pentru a vă asigura că este selectat raportul PivotTable). Singurele câmpuri rămase în câmpurile PivotTable sunt Medal în zona FILTRE și Count of Medal în zona VALORI. Raportul dvs. PivotTable aproape gol ar trebui să arate ca următorul ecran.
   
3. Din zona Câmpuri PivotTable, glisați SDE din tabelul Evenimente în zona RÂNDURI. Apoi glisați Locațiile din tabelul Hosts în zona COLOANELOR . Doar glisând aceste două ierarhii, raportul PivotTable este populat cu o mulțime de date, toate acestea fiind aranjate în ierarhia definită în pașii anteriori. Ecranul dvs. trebuie să arate ca următorul ecran.
   
4. Să filtrăm puțin datele și să vedem doar primele zece rânduri de evenimente. În raportul PivotTable, faceți clic pe săgeata din Etichete de rând, 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 dvs. PivotTable arată acum ca următorul ecran.
   
5. Puteți extinde oricare dintre aceste sporturi în raportul PivotTable, care este nivelul superior al ierarhiei SDE, și puteți vedea informațiile din următorul nivel din ierarhie (disciplina). Dacă există un nivel inferior în ierarhie pentru acea disciplină, puteți extinde disciplina pentru a-i vedea evenimentele. Puteți face același lucru pentru ierarhia Locații, al cărei nivel superior este Season, care se afișează ca Vară și Iarnă în raportul PivotTable. Când extindem sportul acvatic, vedem toate elementele sale de disciplină a copiilor și datele lor. Când extindem disciplina Diving sub Aquatics, vedem și evenimentele sale copil, 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.
   

Prin glisarea acestor două ierarhii, ați creat rapid un raport PivotTable cu date interesante și structurate în care puteți să le detaliați, să le filtrați și să le aranjați.

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

1. În zona Câmpuri PivotTable, eliminați Locațiile din zona COLOANE. Apoi eliminați SDE din zona RÂNDURI. Ați revenit la un raport PivotTable simplu.
2. Din tabelul Hosts , 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, Discipline și Eveniment în zona RÂNDURI și aranjați-le în această ordine, de sus în jos.
4. În raportul PivotTable, filtrați Etichete de rânduri la primele zece sporturi.
5. Restrângeți toate rândurile și coloanele, apoi extindeți Nautice, apoi Scufundări și polo pe apă. Registrul dvs. 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 de ierarhii poate părea convenabilă. Însă când multe persoane creează rapoarte și trebuie să își dea seama de ordinea corectă a câmpurilor pentru a obține vizualizările corecte, ierarhiile devin rapid o îmbunătățire a productivității și permit coerență.

Într-un alt tutorial, veți învăța cum să utilizați ierarhii și alte câmpuri în rapoarte captivante create vizual cu 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 tabele, ceea ce face crearea de rapoarte convingătoare rapidă, unitară și ușoară.

Ați învățat că crearea de ierarhii vă permite să specificați structura inerentă a datelor și să utilizați rapid date ierarhice în rapoarte.

În următorul tutorial din această serie, veți crea rapoarte convingătoare despre medaliile olimpice utilizând Power View. De asemenea, puteți să faceți mai multe calcule, să optimizați datele pentru crearea rapidă a rapoartelor și să importați date suplimentare pentru a face acele rapoarte ș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 dvs. 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 vizualizările următoare vă permite 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 din Power Pivot.

C: Creați relații între tabele utilizând vizualizarea Grilă din Power Pivot

D: Toate cele de mai sus

Întrebarea 2: ADEVĂRAT sau FALS: Puteți stabili relații între tabele pe baza unui identificator unic creat utilizând formule DAX.

R: TRUE

B: FALSE

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

R: În zona de calcul Power Pivot.

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

C: În orice celulă din Excel 2013.

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

Întrebarea 4: Care din următoarele afirmații este adevărată despre ierarhii?

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

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

C: 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