Esercitazione: Estendere le relazioni del modello di dati mediante Excel, Power Pivot e DAX

Questa esercitazione include le sezioni seguenti:

• Aggiungere una relazione usando la vista diagramma in Power Pivot

• Estendere il modello di dati usando colonne calcolate

• Creare una gerarchia

• Usare gerarchie nelle tabelle pivot

• Verifica e test

Al termine dell'esercitazione sarà possibile verificare quanto appreso con un test.

In questa serie di esercitazioni vengono usati dati relativi alle medaglie olimpiche, ai paesi ospiti e a vari eventi sportivi legati alle Olimpiadi. Questa serie include le esercitazioni seguenti:

1. Importare dati in Excel e creare un modello di dati

2. Estendere le relazioni del modello di dati con ExcelPower Pivote DAX

3. Creare report di Power View basati su mappe

4. Incorporare dati di Internet e impostare i valori predefiniti per i report di Power View

5. Guida di Power Pivot

6. Creare report di Power View accattivanti - Parte 2

È consigliabile eseguire le esercitazioni nell'ordine in cui sono presentate.

In queste esercitazioni viene usato Excel 2013 con Power Pivot abilitato. Per altre informazioni su Excel 2013, fare clic qui. Per indicazioni su come abilitare Power Pivot, fare clic qui.

Aggiungere una relazione usando la vista diagramma in Power Pivot

In questa sezione verrà usato il componente aggiuntivo Microsoft Office Power Pivot di Excel 2013 per estendere il modello. La vista diagramma in Microsoft SQL Server PowerPivot per Excel consente di semplificare la creazione di relazioni. È innanzitutto necessario verificare di avere abilitato il componente aggiuntivo Power Pivot.

Nota: il componente aggiuntivo PowerPivot in Microsoft Excel 2013 fa parte di Office Professional Plus. Per altre informazioni, vedere Avviare il componente aggiuntivo Power Pivot in Microsoft Excel 2013.

Aggiungere Power Pivot alla barra multifunzione di Excel abilitando il componente aggiuntivo Power Pivot

Quando Power Pivot è abilitato, sulla barra multifunzione di Excel 2013 viene visualizzata una scheda denominata POWER PIVOT. Per abilitare Power Pivot, eseguire i passaggi seguenti.

1. Fare clic su FILE > Opzioni > Componenti aggiuntivi.

2. Nella casella Gestisci disponibile nella parte inferiore fare clic su Componenti aggiuntivi COM> Vai.

3. Selezionare la casella Microsoft Office PowerPivot in Microsoft Excel 2013 e quindi fare clic su OK.

Sulla barra multifunzione di Excel verrà visualizzata la scheda POWER PIVOT.

Aggiungere una relazione tramite la vista diagramma in Power Pivot

La cartella di lavoro di Excel include una scheda denominata Hosts. La scheda Hosts è stata importata copiandola e incollandola in Excel e quindi formattando i dati come tabella. Per aggiungere la tabella Hosts al modello di dati, è necessario stabilire una relazione. A tale scopo, verrà usato Power Pivot per rappresentare visivamente le relazioni nel modello di dati e quindi verrà creata la relazione.

1. In Excel fare clic sulla scheda Hosts per renderla attiva.

2. Sulla barra multifunzione selezionare > tabelle di Power PIVOT > Aggiungi al modello di dati. Questo passaggio aggiunge la tabella hosts al modello di dati. Viene inoltre aperto il componente aggiuntivo Power Pivot, usato per eseguire i passaggi rimanenti di questa attività.

3. Si noti che la finestra Power Pivot Mostra tutte le tabelle del modello, inclusi gli host. Fare clic su una coppia di tabelle. In Power Pivot è possibile visualizzare tutti i dati contenuti nel modello, anche se non vengono visualizzati in fogli di lavoro di Excel, ad esempio i dati discipline, eventie medaglie diseguito, oltre a S_Teams,W_Teamse sport.
   
   

4. Nella sezione Visualizza della finestra di Power Pivot fare clic su Vista diagramma.

5. Usare la barra di scorrimento per ridimensionare il diagramma in modo da poter visualizzare tutti gli oggetti al suo interno. Ridisporre le tabelle trascinando la barra del titolo, in modo che siano visibili e posizionate uno accanto all'altro. Si noti che quattro tabelle non sono correlate alle restanti tabelle: host, eventi, W_Teamse S_Teams.
   
   

6. Si noti che in entrambe le tabelle Medals e Events è presente un campo denominato DisciplineEvent. Un'ulteriore verifica consente di determinare che il campo DisciplineEvent nella tabella Events include valori univoci non ripetuti.

1. Creare una relazione tra la tabella Medals e la tabella Events. Nella vista diagramma trascinare il campo DisciplineEvent dalla tabella Events al campo DisciplineEvent della tabella Medals. Tra le due tabella verrà visualizzata una linea che indica che è stata stabilita una relazione.

2. Fare clic sulla linea che collega Events e Medals. I campi evidenziati definiscono una relazione come illustrato nell'immagine seguente.
   
   
    

3. Per collegare Hosts al modello di dati, è necessario un campo con valori che identifichino univocamente ogni riga della tabella Hosts. È possibile verificare se tali dati esistono in un'altra tabella del modello di dati. La vista diagramma non consente tuttavia di eseguire questa verifica. Con la tabella Hosts selezionata tornare quindi alla vista dati.

4. Dopo aver esaminato le colonne, si noterà che in Hosts non è presente alcuna colonna con valori univoci. Sarà quindi necessario crearla usando una colonna calcolata e Data Analysis Expressions (DAX).

Questa operazione non è necessaria quando i dati del modello di dati includono tutti i campi per creare relazioni e raccogliere i dati da visualizzare in Power View o nelle tabelle pivot. Poiché spesso le tabelle non includono tutti i dati necessari, nella sezione seguente verrà descritto come creare mediante DAX una nuova colonna che consenta di creare una relazione tra tabelle.

Estendere il modello di dati usando colonne calcolate

Per stabilire una relazione tra la tabella Hosts e il modello di dati e quindi estendere il modello di dati in modo che includa la tabella Hosts, nella tabella Hosts deve essere presente un campo che identifichi univocamente ogni riga. Tale campo deve inoltre corrispondere a un campo del modello di dati. Tali campi corrispondenti, uno in ogni tabella, consentono di associare i dati delle tabelle.

Poiché la tabella Hosts non include un campo di questo tipo, è necessario crearlo. Per proteggere l'integrità del modello di dati, non è possibile usare Power Pivot per modificare o eliminare i dati esistenti. È tuttavia possibile creare nuove colonne mediante campi calcolati basati sui dati esistenti.

Esaminare la tabella Hosts e le altre tabelle del modello di dati per individuare un possibile campo univoco da creare in Hosts e quindi da associare a una tabella del modello di dati. Per soddisfare i requisiti necessari per stabilire una relazione, in entrambe le tabelle deve essere presente una nuova colonna calcolata.

In Hosts è possibile creare una colonna calcolata univoca combinando il campo Edition (l'anno delle Olimpiadi) e il campo Season (estate o inverno). Poiché i campi Edition e Season sono presenti anche nella tabella Medals, se in ciascuna di queste tabelle si crea una colonna calcolata che combini i campi Edition e Season, è possibile stabilire una relazione tra Hosts e Medals. L'immagine seguente illustra la tabella Hosts con i campi Edition e Season selezionati.

Creare colonne calcolate mediante DAX

Iniziare con la tabella Hosts. L'obiettivo è creare nella tabella Hosts e quindi nella tabella Medals una colonna calcolata da usare per stabilire una relazione tra le tabelle.

In Power Pivot è possibile usare Data Analysis Expressions (DAX) per creare calcoli. DAX è un linguaggio di formule per Power Pivot e le tabelle pivot, creato per i dati relazionali e l'analisi contestuale disponibile in Power Pivot. È possibile creare formule DAX in una nuova colonna di Power Pivot e nell'area calcoli in Power Pivot.

1. In Power Pivot selezionare HOME > Vista > Vista dati per verificare che sia selezionata la vista dati e non la vista diagramma.

2. Selezionare la tabella Hosts in Power Pivot. Accanto alle colonne esistenti è presente una colonna vuota denominata Aggiungi colonna. In Power Pivot tale colonna rappresenta un segnaposto. È possibile aggiungere una nuova colonna in una tabella di Power Pivot in molti modi, uno dei quali consiste semplicemente nel selezionare la colonna vuota denominata Aggiungi colonna.
   
   
   

3. Nella barra della formula digitare la formula DAX seguente. La funzione CONCATENATE consente di combinare due o più campi in uno. Mentre si digita, la funzionalità Completamento automatico suggerisce i nomi completi di colonne e tabelle ed elenca le funzioni disponibili. Per selezionare i suggerimenti di Completamento automatico, usare il tasto TAB. In alternativa, è sufficiente fare clic sulla colonna mentre si digita la formula per fare in modo che Power Pivot inserisca automaticamente il nome della colonna nella formula.
   
   =CONCATENATE([Edition],[Season])

4. Dopo aver completato la compilazione della formula, premere INVIO per accettarla.

5. I valori vengono popolati per tutte le righe nella colonna calcolata. Se Scorri verso il basso nella tabella, vedrai che ogni riga è univoca, quindi abbiamo creato un campo che identifica in modo univoco ogni riga della tabella hosts . Tali campi sono detti una chiave primaria.

6. Rinominare la colonna calcolata con EditionID. È possibile rinominare qualsiasi colonna facendo doppio clic su di essa oppure facendo clic con il pulsante destro del mouse sulla colonna e scegliendo Rinomina colonna. Al termine, la tabella Hosts in Power Pivot avrà l'aspetto seguente.
   
   

La tabella Hosts è pronta. Creare ora una colonna calcolata in Medals che corrisponda al formato della colonna EditionID creata in Hosts in modo da poter stabilire una relazione tra le due tabelle.

1. Iniziare creando una nuova colonna nella tabella Medals come è stato fatto per Hosts. In Power Pivot selezionare la tabella Medals e fare clic su Progetta > Colonne > Aggiungi. Si noti che Aggiungi colonna è selezionato. Questa operazione equivale alla semplice selezione di Aggiungi colonna.

2. Il formato della colonna Edition in Medals è diverso da quello della colonna Edition in Hosts. Prima di combinare, ovvero concatenare, la colonna Edition con la colonna Season per creare la colonna EditionID, è necessario creare un campo intermediario che trasformi la colonna Edition nel formato corretto. Nella barra della formula sopra la tabella digitare la formula DAX seguente.

   = YEAR([Edition])
   

3. Dopo aver completato la compilazione della formula, premere INVIO. I valori vengono popolati per tutte le righe nella colonna calcolata, in base alla formula immessa. Se si confronta questa colonna con la colonna Edition in Hosts, si noterà che queste colonne hanno lo stesso formato.

4. Rinominare la colonna facendo clic con il pulsante destro del mouse su CalculatedColumn1 e scegliendo Rinomina colonna. Digitare Year e quindi premere INVIO.

5. Quando si crea una nuova colonna, in Power Pivot viene aggiunta un'altra colonna segnaposto denominata Aggiungi colonna. Per creare la colonna calcolata EditionID, selezionare Aggiungi colonna. Nella barra della formula digitare la formula DAX seguente e premere INVIO.
   
   =CONCATENATE([Year],[Season])

6. Rinominare la colonna facendo doppio clic su CalculatedColumn1 e digitando EditionID.

7. Disporre i valori della colonna in ordine crescente. L'aspetto della tabella Medals in Power Pivot è ora quello illustrato nell'immagine seguente.
   
   
    

Si noti che nel campo EditionID della tabella Medals molti valori sono ripetuti. Si tratta comunque di un comportamento previsto poiché durante ciascuna edizione delle Olimpiadi (ora rappresentata dal valore EditionID) sono state assegnate molte medaglie. Ciò che è univoco nella tabella Medals è ogni medaglia assegnata. L'identificatore univoco per ogni record della tabella Medals, nonché la chiave primaria designata, è il campo MedalKey.

Il passaggio successivo consiste nel creare una relazione tra Hosts e Medals.

Creare una relazione mediante colonne calcolate

Usare quindi le colonne calcolate create per stabilire una relazione tra Hosts e Medals.

1. Nella finestra di Power Pivot selezionare Home > Vista > Vista diagramma sulla barra multifunzione. È inoltre possibile passare dalla vista griglia alla vista diagramma usando i pulsanti disponibili nella parte inferiore della finestra di PowerView come illustrato nell'immagine seguente.
   
   

2. Espandere Hosts per visualizzarne tutti i campi. Nella tabella Hosts è stata creata la colonna EditionID come chiave primaria (ovvero come campo univoco, non ripetuto) e nella tabella Medals è stata creata una colonna EditionID per consentire di stabilire una relazione tra le tabelle. È ora necessario individuarle entrambe e creare una relazione. Sulla barra multifunzione in Power Pivot è disponibile la caratteristica Trova che consente di cercare il modello di dati per i campi corrispondenti. Nell'immagine seguente viene illustrata la finestra Trova metadati con il valore EditionID immesso nel campo Trova.
   

3. Posizionare la tabella Hosts accanto a Medals.

4. Trascinare la colonna EditionID in Medals nella colonna EditionID in Hosts. Power Pivot crea una relazione tra le tabelle in base alla colonna EditionID e inserisce una linea tra le due colonne che indica la relazione.
   
   

In questa sezione è stata illustrata una nuova tecnica per l'aggiunta di nuove colonne, è stata creata una colonna calcolata mediante DAX ed è stata usata tale colonna per stabilire una nuova relazione tra le tabelle. La tabella Hosts è ora integrata nel modello di dati e i dati in essa contenuti sono disponibili per la tabella pivot in Sheet1. I dati associati possono inoltre essere usati per creare tabelle pivot, grafici pivot, report Power View aggiuntivi e molto altro ancora.

Creare una gerarchia

La maggior parte dei modelli di dati include dati implicitamente gerarchici. Esempi comuni includono i dati del calendario, i dati geografici e le categorie di prodotti. La creazione di gerarchie in Power Pivot risulta utile poiché consente di trascinare un elemento in un report, ovvero la gerarchia, anziché assemblare e ordinare gli stessi campi in continuazione.

Anche i dati relativi alle Olimpiadi sono gerarchici. È utile comprendere la gerarchia relativa alle Olimpiadi in termini di sport, discipline ed eventi. Ad ogni sport sono associate una o più discipline (a volte molte). Ad ogni disciplina sono associati uno o più eventi (a volte in ogni disciplina sono presenti molti eventi). Nell'immagine seguente viene illustrata la gerarchia.

In questa sezione verranno create due gerarchie all'interno dei dati relativi alle Olimpiadi usati in questa esercitazione. Verrà quindi illustrato l'uso delle gerarchie per semplificare l'organizzazione dei dati nelle tabelle pivot e, nell'esercitazione successiva, in Power View.

Creare una gerarchia Sport

1. In Power Pivot passare alla Vista diagramma. Espandere la tabella Events in modo da poter visualizzare più facilmente tutti i campi.

2. Fare clic sui campi Sport, Discipline ed Event tenendo premuto il tasto CTRL. Con questi tre campi selezionati fare clic con il pulsante destro del mouse e scegliere Crea gerarchia. Nella parte inferiore della tabella verrà creato un nodo di gerarchia padre, Hierarchy 1, e le colonne selezionate verranno copiate nella gerarchia come nodi figlio. Verificare che Sport sia il primo elemento della gerarchia seguito da Discipline ed Event.

3. Fare doppio clic sul titolo Hierarchy1 e digitare SDE per rinominare la nuova gerarchia. È ora disponibile una gerarchia che include Sport, Discipline ed Event. La tabella Events ha ora l'aspetto illustrato nell'immagine seguente.
   
   

Creare una gerarchia Locations

1. Nella vista diagramma di Power Pivot selezionare la tabella Hosts e fare clic sul pulsante Crea gerarchia nell'intestazione della tabella, come illustrato nell'immagine seguente.
   
   
   Nella parte inferiore della tabella viene visualizzato un nodo padre gerarchico vuoto.

2. Digitare Locations come nome per la nuova gerarchia.

3. È possibile aggiungere colonne a una gerarchia in molti modi. Trascinare i campi Season, City e NOC_CountryRegion sul nome della gerarchia (in questo caso Locations) finché il nome della gerarchia non viene evidenziato e quindi rilasciare i campi per aggiungerli.

4. Fare clic con il pulsante destro del mouse su EditionID e scegliere Aggiungi a gerarchia. Scegliere Locations.

5. Verificare che i nodi figlio della gerarchia siano nell'ordine corretto. Dall'alto verso il basso, l'ordine deve essere: stagione, NOC, città, EditionID. Se i nodi figlio non sono ordinati correttamente, è sufficiente trascinarli nell'ordine appropriato nella gerarchia. La tabella deve avere l'aspetto illustrato nell'immagine seguente.
   

Nel modello di dati sono ora disponibili gerarchie che possono essere usate nei report. Nella sezione seguente verrà illustrato come queste gerarchie possono rendere più rapida e coerente la creazione dei report.

Usare gerarchie nelle tabelle pivot

Dopo aver creato le gerarchie Sports e Locations, è possibile aggiungerle alle tabelle pivot oppure a Power View e recuperare rapidamente risultati che includano utili raggruppamenti di dati. Prima di creare le gerarchie, nella tabella pivot sono stati aggiunti campi che sono stati disposti per ottenere la visualizzazione desiderata.

In questa sezione le gerarchie create nella sezione precedente verranno usate per perfezionare rapidamente la tabella pivot. Verrà quindi creata la stessa vista di tabella pivot usando i campi individuali nella gerarchia in modo da poter confrontare l'uso delle gerarchie con l'uso dei campi individuali.

1. Tornare a Excel.

2. In Sheet1rimuovere i campi dall'area righe dei campi tabella pivot, quindi rimuovere tutti i campi dall'area colonne. Verificare che la tabella pivot sia selezionata (ora abbastanza piccola, quindi è possibile scegliere la cella a1 per verificare che la tabella pivot sia selezionata). Gli unici campi rimanenti nei campi della tabella pivot sono Medal nell'area dei filtri e conteggio della medaglia nell'area valori. La tabella pivot quasi vuota dovrebbe essere simile alla schermata seguente.

   

3. Nell'area Campi tabella pivot trascinare SDE dalla tabella Events all'area RIGHE. Trascinare quindi Locations dalla tabella Hosts all'area COLONNE. Trascinando solo queste due gerarchie la tabella pivot viene popolata con molti dati, tutti organizzati nella gerarchia definita nei passaggi precedenti. La schermata visualizzata deve avere l'aspetto di quella illustrata nell'immagine seguente.
   

4. Applicare un filtro ai dati per visualizzare solo le prime dieci righe di eventi. Nella tabella pivot fare clic sulla freccia in Etichette di riga fare clic su (Seleziona tutto) per rimuovere tutte le selezioni e quindi fare clic sulle caselle accanto ai primi dieci sport. L'aspetto della tabella pivot è ora quello illustrato nell'immagine seguente.
   

5. È possibile espandere qualsiasi valore di Sports nella tabella pivot, che rappresenta il livello superiore della gerarchia SDE, e visualizzare informazioni nel livello successivo della gerarchia (valore Discipline). Se nella gerarchia esiste un livello più basso per tale valore Discipline, è possibile espandere il valore per visualizzarne gli eventi. È possibile eseguire la stessa operazione per la gerarchia Location, il cui livello superiore è Season che include i valori Summer e Winter nella tabella pivot. Se si espande lo sport Aquatics, vengono visualizzati tutti gli elementi Discipline figlio e i relativi dati. Se si espande la disciplina Diving in Aquatics, vengono visualizzati anche gli eventi figlio di tale disciplina, come illustrato nell'immagine seguente. È possibile eseguire la stessa operazione per Water Polo in cui è presente un solo evento.
   

Trascinando le due gerarchie è stato possibile creare rapidamente una tabella pivot con dati interessanti e strutturati che possono essere analizzati, filtrati e organizzati.

Creare ora la stessa tabella pivot senza utilizzare le gerarchie.

1. Nell'area Campi tabella pivot rimuovere Locations dall'area COLONNE. Rimuovere quindi SDE dall'area RIGHE. Così facendo si è tornati a una tabella pivot di base.

2. Trascinare Season, City, NOC_CountryRegion e EditionID dalla tabella Hosts all'area COLONNE e disporle in tale ordine dall'alto al basso.

3. Trascinare Sport, Discipline ed Event dalla tabella Events all'area RIGHE e disporle in tale ordine dall'alto al basso.

4. Nella tabella pivot filtrare Etichette di riga in modo da visualizzare i primi dieci valori di Sports.

5. Comprimere tutte le righe e le colonne e quindi espandere Aquatics, Diving e Water Polo. La cartella di lavoro avrà l'aspetto illustrato nell'immagine seguente.
   

La schermata è simile a quella ottenuta utilizzando le gerarchie, ma in questo caso è stato necessario trascinare sette singoli campi nelle aree di Campi tabella pivot anziché trascinare semplicemente due gerarchie. Se si è il solo utente che crea tabelle pivot o report Power View basati su questi dati, la creazione di gerarchie può sembrare solo più pratica. Quando invece i report vengono creati da molti utenti che devono trovare l'ordine appropriato dei campi per ottenere visualizzazioni corrette, l'utilizzo delle gerarchie consente di migliorare la produttività e mantenere la coerenza.

In un'altra esercitazione verrà illustrato come usare le gerarchie e altri campi in report visivamente accattivanti creati mediante Power View.

Verifica e test

Verificare le informazioni acquisite

La cartella di lavoro di Excel dispone ora di un modello di dati che include dati di più origini correlati mediante campi esistenti e colonne calcolate. Sono inoltre presenti gerarchie che riflettono la struttura dei dati delle tabelle e consentono di creare in modo semplice e rapido report coerenti.

Durante l'esercitazione è stato spiegato che la creazione di gerarchie consente di specificare la struttura implicita dei dati, nonché usare rapidamente i dati gerarchici nei report.

Nell'esercitazione successiva di questa serie verranno creati report visivamente accattivanti sulle medaglie olimpiche usando Power View. Verrà inoltre illustrato come eseguire altri calcoli, ottimizzare i dati per la creazione rapida dei report e importare dati aggiuntivi per rendere tali report più interessanti. Ecco il collegamento:

Esercitazione 3: Creare report di Power View basati su mappe

TEST

Vuoi vedere quanto ti ricordi cosa hai imparato? Ecco la tua occasione. Il quiz seguente evidenzia le caratteristiche, le funzionalità o i requisiti appresi in questa esercitazione. Nella parte inferiore della pagina troverai le risposte. Buona Fortuna!

Domanda 1: Quale delle viste seguenti consente di creare relazioni tra due tabelle?

A: Le relazioni tra tabelle vengono create in Power View.

B: Le relazioni tra tabelle vengono create mediante la vista progettazione in Power Pivot.

C: Le relazioni tra tabelle vengono create mediante la vista griglia in Power Pivot

D: Tutte le risposte sopra

Domanda 2: VERO o FALSO: È possibile stabilire relazioni tra tabelle in base a un identificatore univoco creato mediante formule DAX.

A: VERO

B: FALSO

Domanda 3: In quale degli elementi seguenti è possibile creare una formula DAX?

A: Nell'area calcoli di Power Pivot.

B: In una nuova colonna di Power Pivot.

C: In una cella qualsiasi di Excel 2013.

D: Sia A che B.

Domanda 4: Quale delle affermazioni seguenti sulle gerarchie è vera?

A: Quando si crea una gerarchia, i campi inclusi non sono più disponibili individualmente.

B: Quando si crea una gerarchia, i campi inclusi e la gerarchia possono essere usati in strumenti client semplicemente trascinando la gerarchia in Power View o in un'area di una tabella pivot.

C: Quando si crea una gerarchia, i dati sottostanti del modello di dati vengono combinati in un campo.

D: Non è possibile creare gerarchie in Power Pivot.

Risposte del test

1. Risposta corretta: D

2. Risposta corretta: A

3. Risposta corretta: D

4. Risposta corretta: B