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ă din Power Pivot

• Extinderea modelului de date folosind coloane calculate

• Create a hierarchy

• Utilizarea ierarhiilor în rapoarte 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 creați un 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 utilizarea 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 Vizualizare diagramă din Power Pivot

În această secțiune, utilizați programul de completare Microsoft Office Power Pivot din Excel 2013 pentru a extinde modelul. Folosirea opțiunii Vizualizare diagramă din Microsoft SQL Server PowerPivot pentru Excel simplifică crearea de relații. În primul rând, trebuie să vă asigurați că aveți programul de completare Power Pivot activat.

Notă: Power Pivot în Microsoft Excel 2013 program de completare este parte a Office Professional Plus. Consultați secțiunea pornirea Power Pivot în Microsoft Excel 2013 - pentru mai multe informații.

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

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

1. Mergeți la FIȘIER > Opțiuni > 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 selectare Microsoft Office Power Pivot în Microsoft Excel 2013, apoi faceți clic pe OK.

Panglica Excel conține acum o filă POWER PIVOT.

Adăugarea unei relații utilizând vizualizarea diagramă din Power Pivot

Registrul de lucru Excel include un tabel numit Hosts. Am importat Hosts copiindu-l și lipindu-l în Excel, apoi am formatat datele ca un tabel. Pentru a adăuga tabelul Hosts la modelul de date, trebuie să stabilim o relație. Să folosim 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 stabili ca filă activă.

2. Pe panglică, selectați POWER PIVOT > Tabele > 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 veți utiliza pentru a parcurge restul pașilor din această activitate.

3. Observați că fereastra Power Pivot afișează toate tabelele din model, inclusiv Hosts. Faceți clic pe până la două tabele. În Power Pivot puteți să vizualizați toate datele care conține modelul dvs., chiar dacă aceștia nu sunt afișate în foi de lucru în Excel, cum ar fi discipline, evenimenteși datele despre medalii mai jos, precum și S_Teams,W_ Echipele, și sport.
   
   

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

5. Utilizați bara de diapozitiv pentru a redimensiona diagrama, astfel încât să puteți vedea toate obiectele din diagramă. Rearanja tabelele, glisați bara de titlu a lor, astfel încât să fie vizibile și cameră lângă una de alta. Observați că patru tabele nu au legătură cu restul de tabele: Hosts, 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. Mai departe, determinați că acel câmp DisciplineEvent din tabelul Events constă în valori unice, nerepetate.

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

2. Faceți clic pe linia care conectează evenimente și medalii. Câmpurile evidențiat definiți relație, așa cum se arată în următorul ecran.
   
   
   

3. Pentru a conecta Hosts 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 în Modelul de date pentru a vedea dacă aceleași date există într-un alt tabel. Căutarea în Vizualizarea diagramă nu ne permite să facem acest lucru. Cu Hosts selectat, comutați înapoi la vizualizarea de date.

4. După examinarea coloanele, ne dăm seama că Hosts nu are o coloană cu valori unice. Vă va trebui să o creați utilizând o coloană calculată și Data Analysis Expressions (DAX).

Este bine atunci când datele din modelul dvs. de date au toate câmpurile necesare pentru a crea relații și combină date de vizualizat în Power View sau în PivotTable. Dar tabelele nu sunt întotdeauna atât de cooperante, astfel încât următoarea secțiune descrie cum să creați o coloană nouă, utilizând DAX, care poate fi folosită pentru a crea o relație între tabele.

Extinderea modelului de date folosind coloane calculate

Pentru a stabili o relație între tabelul Hosts și modelul de date, în scopul de a extinde modelul de date pentru a include tabelul Hosts, Hosts trebuie să aibă un câmp care identifică în mod unic fiecare rând. În plus, acel câmp trebuie să corespundă unui câmp din modelul de date. Aceste câmpuri corespunzătoare, câte unul în fiecare tabel, sunt ceea ce permite tabelelor de date să fie asociate.

Deoarece tabelul Hosts nu au un astfel de câmp, trebuie să îl creați. Pentru a păstra integritatea din modelul de date, nu se poate utiliza Power Pivot pentru a edita sau a șterge datele existente. Cu toate acestea, puteți creați coloane noi utilizând câmpuri calculate pe baza datelor existente.

Căutând în tabelul Hosts, apoi în alte tabele din modelul de date, vom găsi un bun candidat pentru un câmp unic pe care l-am putea crea în Hosts și asocia cu un tabel din modelul de date. Ambele tabele vor necesita o coloană calculată nouă, pentru a satisface cerințele necesare stabilirii unei relații.

În Hosts, putem crea o coloană calculată unică prin combinarea câmpurilor Edition (anul evenimentului olimpic) și Season (vara sau iarna). În tabelul Medals există, de asemenea, un câmp Edition și un câmp Season, așa că dacă am crea o coloană calculată în fiecare din acele tabele care combină câmpurile Edition și Season, am putea stabili o relație între Hosts și Medals. Următorul ecran arată tabelul Hosts, cu câmpurile Edition și Season selectate

Crearea de coloane calculate folosind DAX

Să începem cu tabelul Hosts. Scopul este de a crea o coloană calculată în tabelul Hosts, apoi în tabelul Medals, care poate fi folosită 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 pentru formule destinat Power Pivot și PivotTable, conceput pentru date relaționale și analiză contextuală disponibil în Power Pivot. Puteți crea formule DAX într-o nouă coloană Power Pivot și în zona de calcul din Power Pivot.

1. În Power Pivot, selectați PAGINĂ DE PORNIRE > Vizualizare > Vizualizare date pentru a vă asigura că s-a selectat Vizualizarea date în loc de Vizualizarea diagramă.

2. Selectați tabelul Hosts în Power Pivot. Adiacente pe coloanele existente este o coloană goală intitulat Adăugare coloană. Power Pivot oferă acea coloană ca substituent. Există mai multe moduri pentru a adăuga o coloană nouă într-un tabel în Power Pivot, una dintre care este pur și simplu, selectați coloana goală, care conține 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-una singură. Pe măsură ce tastați, completare automată vă ajută să tastați numele complet de coloane și tabele și listează funcțiile care sunt disponibile. Utilizați tab pentru a selecta sugestii de completare automată. Vă puteți, de asemenea, faceți clic pe coloana în timp ce tastarea de formulă și Power Pivot inserează numele de coloană în formulă.
   
   =CONCATENATE([Edition],[Season])

4. Când ați terminat 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, veți vedea 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. Aceste câmpuri sunt numite chei primare.

6. Să redenumim coloana calculată ca 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ă. După finalizare, 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 create în Hosts, pentru a putea 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 atunci când se selectează doar 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 transformă Edition în formatul corect. În bara de formule de deasupra tabelului, tastați următoarea formulă DAX.

   = YEAR([Edition])
   

3. Când ați terminat de construit formula, apăsați pe Enter. Valorile sunt populate pentru toate rândurile din coloana calculată, pe baza formulei introduse. Dacă veți compara 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 Year, apoi apăsați Enter.

5. Când ați creat o coloană nouă, Power Pivot a adăugat o altă coloană substituentă numită Adăugare coloană. În continuare, dorim să creăm coloana calculată EditionID, deci selectați Adăugare coloană. În bara de formule, introduceț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 în Power Pivot acum arată cum ar fi următorul ecran.
   
   
   

Observați că multe valori se repetă în câmpul EditionID al tabelului Medals. Acest lucru este bun și de așteptat, deoarece în fiecare ediție a Jocurilor Olimpice (acum reprezentată de valoarea EditionID) s-au acordat 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 de a crea o relație între Hosts și Medals.

Crearea unei relații folosind coloane calculate

În continuare, să folosim coloanele calculate create pentru a stabili o relație între Hosts și Medals.

1. În fereastra Power Pivot, selectați pornire > vizualizare > vizualizarea 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 astfel încât puteți vizualiza toate câmpurile sale. Am creat coloana EditionID să funcționeze ca Hosts tabel cheii primare (câmp de unice, nerepetate) și creează o coloană EditionID din tabelul medalii pentru a permite crearea unei relații între ele. Trebuie să le găsească ambele și să creați o relație. Power Pivot oferă o caracteristică găsi pe panglică, astfel încât puteți căuta modelului de date pentru câmpurilor 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 în medalii la coloana EditionID în Hosts. Power Pivot creează o relație între tabele bazate pe coloana EditionID și trasează o linie între cele două coloane, care indică faptul că relația.
   
   

În această secțiune, puteți învățat o nouă tehnică pentru a adăuga coloane noi, create unei coloane calculate folosind DAX, și utilizează acea coloană pentru a stabili o noi relații între tabele. Tabelul Hosts este acum integrată în modelul de date și datele sale este disponibilă pentru raportul PivotTable în Sheet1. De asemenea, puteți utiliza date asociate pentru a crea suplimentare PivotTable, rapoarte PivotChart, rapoarte Power View, și multe altele.

Crearea unei ierarhii

Majoritatea modelelor de date conțin date care sunt în mod inerent ierarhice. Printre exemplele obișnuite se numără date calendaristice, date geografice și categorii de produse. Crearea ierarhiilor î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 la nesfârșit aceleași câmpuri.

Datele despre Jocurile Olimpice sunt, de asemenea, ierarhice. Este util de înțeles ierarhia Jocurilor Olimpice, în materie de sport, discipline și evenimente. Pentru fiecare sport, există una sau mai multe discipline asociate (uneori sunt multe). Iar pentru fiecare disciplină, există unul sau mai multe evenimente (din nou, uneori, există multe evenimente în fiecare disciplină). Imaginea următoare ilustrează ierarhia.

În această secțiune veți crea două ierarhii în interiorul datelor olimpice pe care le-ați utilizat în acest tutorial. Apoi utilizați aceste ierarhii pentru a vedea cum ierarhiile fac mai ușoară organizarea datelor în PivotTable și, într-un tutorial ulterior, în Power View.

Crearea unei ierarhii a sporturilor

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 acestuia.

2. Apăsați continuu Ctrl și faceți clic pe câmpurile Sport, Discipline și Event. Cu aceste trei câmpuri selectate, faceți clic dreapta și selectați Creare ierarhie. Un nod de ierarhie părinte, Ierarhie 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 Event.

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, Discipline și Event. Tabelul dvs. Events arată acum ca următorul ecran.
   
   

Crearea unei ierarhii de locații

1. Tot în vizualizarea diagramă în Power Pivot, selectați tabelul Hosts și faceți clic pe butonul Creare ierarhie din titlul de tabel, așa cum se arată în următorul ecran.
   
   
   O ierarhie necompletată părinte nod apare în partea de jos a tabelului.

2. Tastați Locations ca nume pentru noua ierarhie.

3. Există multe modalități de a adăuga coloane la o ierarhie. Glisați câmpurile Season, City și NOC_CountryRegion pe numele ierarhiei (în acest caz, Locations) până când se evidențiază numele ierarhiei, apoi eliberați pentru a le adăuga.

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

5. Asigurați-vă că vă noduri descendente de ierarhie sunt în ordine. Din partea de sus în jos, ar trebui să fie ordinea: Season, NOC, City, EditionID. Dacă vă noduri descendente sunt în ordine, pur și simplu glisați-le în ordonarea corespunzătoare în ierarhie. Tabelul trebuie să arate asemănător cu următorul ecran.
   

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

Utilizarea ierarhiilor în rapoarte PivotTable

Acum, că avem o ierarhie Sports și o ierarhie Locations, le putem adăuga la rapoartele PivotTable sau Power View și obțineți rapid rezultate care includ grupări utile de date. Înainte de crearea ierarhiilor, a trebuit să adăugați câmpuri individuale la raportul PivotTable și să aranjați acele câmpuri 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, doar pentru a putea compara folosirea ierarhiilor cu folosirea câmpurilor individuale.

1. Mergeți înapoi în 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ă raportul PivotTable este selectat (momentan este destul de mic, așa că 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 PivotTable Fields, glisați SDE din tabelul Events în zona rânduri. Apoi glisa locații din tabelul Hosts în zona coloane . Doar prin glisarea acelor două ierarhii, raportul PivotTable este populată cu numeroase date, care sunt aranjate în ierarhia vă definite în pașii anteriori. Ecranul ar trebui să arate ca următorul ecran.
   

4. Să filtrați ca date un pic, și doar vedea 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 PivotTable arată acum ca următorul ecran.
   

5. Puteți extinde oricare dintre aceste sportive în raportul PivotTable, care este nivelul superior al ierarhiei SDE, și puteți vedea informațiile din următorul nivel în jos în ierarhie (disciplină). Dacă un nivel inferior în ierarhia există pentru că disciplina, puteți extinde disciplina pentru a vedea evenimentele sale. Puteți face același pentru ierarhii de locații, nivelul superior al care este Season, care apare ca vară și iarnă în raportul PivotTable. Atunci când vom extinde Aquatics sport, vom vedea toate elementele sale fiu disciplină și datele lor. Atunci când vom extinde disciplina Diving sub Aquatics, vom vedea evenimentele sale fiu prea, așa cum se arată în următorul ecran. Vă puteți faceți același lucru pentru de Polo și vedea că are doar un singur eveniment.
   

Prin glisarea acelor două ierarhii, ați creat rapid un PivotTable cu date interesante și structurate pe care le puteți detalia, filtra și aranja.

Acum să creăm același PivotTable, fără a beneficia de ierarhii.

1. În zona Câmpuri PivotTable, eliminați Locations din zona COLOANE. Apoi eliminați SDE din zona RÂNDURI. Ați ajuns înapoi la un PivotTable de bază.

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

3. Din tabelul Events, glisați Sport, Discipline și Event în zona RÂNDURI și aranjați-le în acea ordine, de sus în jos.

4. În PivotTable, filtrați etichetele de rând la primele zece sporturi.

5. Restrânge toate rândurile și coloanele, apoi extindeți Aquatics, apoi Diving și de Polo. Registrul de lucru arată cum ar fi următorul ecran.
   

Ecranul arată similar, cu excepția faptului că ați glisat șapte câmpuri individuale în zonele Câmpuri PivotTable, în loc să glisați doar două ierarhii. Dacă sunteți singura persoană care creează rapoarte PivotTable sau Power View pe baza acestor date, crearea de ierarhii ar putea părea doar convenabilă. Dar atunci când multe persoane creează rapoarte și trebuie să înțeleagă ordonarea corespunzătoare a câmpurilor pentru a obține vizualizările corecte, ierarhiile devin rapid o îmbunătățire a productivității și permit consistența.

Într-un alt tutorial, veți învăța să utilizați ierarhiile și alte câmpuri în rapoarte interesante din punct de vedere vizual, create folosind Power View.

Punct de control și test

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

Registrul dvs. de lucru Excel are acum un Model de date, care include date din surse multiple, legate folosind câmpurile existente și coloane calculate. Aveți, de asemenea, ierarhii care reflectă structura datelor din tabele, ceea ce face crearea convingătoare de rapoarte rapidă, consistentă și simplă.

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

În următorul tutorial din această serie, creați rapoarte convingătoare despre medalii olimpice, folosind Power View. De asemenea, faceți mai multe calcule, optimizați date pentru creare rapidă a rapoartelor și importați date suplimentare pentru a face aceste rapoarte chiar 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 următoarele vizualizări vă permit să creați relații între două tabele?

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

B: Creați relații între tabele folosind vizualizarea de proiectare din Power Pivot.

C: Creați relații între tabele folosind 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, care este creat folosind formule DAX.

A: ADEVĂRAT

B: FALS

Întrebarea 3: În care dintre următoarele se poate crea o formulă DAX?

A: În zona de calcul din Power Pivot.

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

C: În orice celulă din Excel 2013.

D: A și B.

Întrebarea 4: Care din următoarele afirmații este adevărată cu privire la 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 folosite în instrumente client glisând pur și simplu ierarhia la 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