#Excel: Power Pivot per la Business Intelligence

Migliora la performance aziendale con la BI

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 30 Aprile 2020
Categoria: Microsoft Office Excel Power Pivot

Excel: Power Pivot
#Excel: Power Pivot per la Business Intelligence

Come molti sanno le tabelle pivot rappresentano una funzionalità molto utile di Excel per analizzare velocemente grandi quantità di dati. Le pivot esaminano i suddetti dati e, con pochi clic, li riepilogano sintetizzandoli, Inoltre permettono di creare grafici pivot filtrabili e di interfacciarsi con l'Analisi di simulazione di Excel, in particolare con Gestione Scenari. Il componente Power Pivot è invece un'evoluzione delle tabelle pivot ed è utilizzabile dalla versione Microsoft Excel 2010. Fa parte dei tool power insieme a Power Query, Power Map, Power View e Power BI.

Power Pivot consente di realizzare un elementare sistema di business intelligence, grazie alla capacità di importare dati da più fonti e ottimizzarle per essere analizzate con Excel. In tal modo anche una piccola azienda può avvalersi di un sistema evoluto di reporting utile a controllare la gestione aziendale per migliorare le proprie performance.
Se ci si vuole esercitare con le nozioni successive è possibile scaricare il seguente file compresso trovato in Rete e leggermente modificato.

Indice Power Pivot

Installare Power Pivot

Se già non presente nell’applicativo, Power Pivot va installato come componente aggiuntivo. Per farlo è possibile andare nella scheda File / voce Opzioni di Excel e nella finestra che si apre, cliccare sulla sinistra Componenti aggiuntivi. In basso aprire l’elenco a discesa, scegliere Componenti aggiuntivi COM, cliccare il pulsante Vai…, scegliere Microsoft Power Pivot for Excel e cliccare Ok. In alto a destra, sulla barra delle applicazioni (ribbon) comparirà la scheda Power Pivot.

Differenze tra Power Pivot ed Excel

Tabelle pivot e Power Pivot

PowerPivot incrementa le possibilità offerte dalle tabelle pivot tradizionali in quanto queste ultime analizzano una sola tabella alla volta che non deve perciò essere messa in relazione con altro; PowerPivot può invece analizzare più di una tabella per volta, mettendo in relazione le tabelle stesse.
Power Pivot permette il mashup (indica un insieme di contenuto da fonti diverse che integrati dinamicamente per creare un nuovo tipo di servizio) di grandi mole di dati da diverse origini, di analizzarli velocemente e di condividerli facilmente.
Sia in Excel che in Power Pivot è possibile creare un modello di dati: le Power Pivot non si appoggiano a un intervallo di celle, come le tabell pivot, ma al modello di dati, cioè un'area di lavoro dove importare dati da fogli Excel, file esterni, database e altre sorgenti. Tali dati vengono memorizzati nel data model in tabelle bidimensionali, come i fogli Excel, che possono essere relazionate tra loro per creare analisi complesse (è comunque possibile usare la Power Pivot anche senza stabilire relazioni tra le tabelle), Appoggiarsi al modello dati apre la strada a nuove opportunità di analisi. Le informazioni importate in Excel sono disponibili in Power Pivot e viceversa.
Questa caratteristica riguarda la nozione di modellazione dati che permette appunto l’instradamento nello stesso sistema di informazioni derivanti da diverse fonti da relazionare tra loro.
Cliccando l’icona Gestisci (la prima della scheda Power Pivot che passa a Power Pivot per caricare e preparare i dati o continuare ad usare i dati già aggiunti al file corrente) si presenterà un ribbon simile al seguente:

Tabelle pivot e PowerPivot
Power Pivot Excel


Banalizzando l’uso di questo potente strumento e supponendo di voler soltanto importare delle tabelle, già relazionate tra loro, da un medesimo database di Access, sarà necessario cliccare nel gruppo Recupera dati esterni l’icona Da database e successivamente su Access e su Sfoglia… e Apri per individuare il file in questione (per verificare lo stato della connessione con il database si può cliccare Test Connessione).
Attraverso due pulsanti di opzione verrà chiesto di scegliere la modalità di importazione dei dati, in tal caso opzionare Seleziona da un elenco di tabelle e viste per scegliere i dati da importare (a meno di non conoscere il linguaggio SQL per scrivere una query d’importazione).
Successivamente andranno spuntate le caselle di controllo relative alle tabelle da importare (cliccando Seleziona tabelle correlate PowerPivot importerà automaticamente ulteriori tabelle collegate a quelle scelte; pigiando invece il pulsante Visualizza anteprima e applica filtro si potranno escludere dall'importazione alcune colonne, ovvero campi, inutili).
Cliccando Fine terminerà l’importazione e ciascuna tabella sarà mostrata come se fosse un foglio.

Power Pivot esercizio completo

Supponiamo di gestire una società di formazione e che abbiamo i dati archiviati in 4 file CSV con i campi Date, Corsi, Sedi, Allievi (2016.csv, 2017.csv, 2018.csv, 2019.csv) all’interno della cartella Data, uno per ogni anno di attività. Inoltre abbiamo un file corsi.xlsx con 3 tabelle in due fogli (nel foglio Corsi ci sono la tabella con Corsi e Categoria e la tabella con Sedi e Max, mentre nel foglio Date c’è la tabella con Date, Anno, Mese, NomeMese, GiornoSettimana, NomeGiornoSettimana).
Per unire questi file e creare un report senza Power Pivot, andrebbero copiati i file CSV in Excel, utilizzato CERCA.VERT per portare i dettagli sui corsi e le sedi in nuove colonne della tabella delle attività e creata una tabella pivot: questo è vero se non si supera il limite di Excel che è pari a 1.048.576 righe, altrimenti lo spreadsheet diventa inutile. È allora possibile sfruttare Power Pivot.
Con Power Pivot ci collegheremo a tali fonti (in modo che si aggiornino in futuro) e le collegheremo.
Esistono due modi per accedere a Power Pivot per gestire il modello di dati, creare misure e altro: tramite la scheda Power Pivot o da Dati / Gestisci modello di dati (per avere opzioni maggiori, si può fare anche da Power Query)

Importare i dati

Per importare i 4 file CSV dalla cartella, fare clic sulla scheda Dati / Recupera dati / Da file / Da cartella.
Una volta raggiunta la cartella Data, dai Ok e, nella finestra che si apre, clicca Trasforma dati.
Si apre l'editor Power Query dove potresti filtrare i file che non servono e fare altre trasformazioni, ma dato che il presente articolo riguarda Power Pivot, collegati solo ai file cliccando l'icona Combina File e poi Ok per mettere i 4 CSV in una tabella.
Clicca sulla scheda Home / Chiudi e caricaChiudi e carica in. Seleziona Crea solo connessione e seleziona la casella Aggiungi questi dati al modello di dati e Ok.
Importiamo ora le 3 tabelle del file corsi.xlsx (Corsi, Sedi, Calendario) dalla cartella di lavoro di Excel.
Clicca su Dati / Recupera dati / Da file / Da cartella di lavoro cerca il file corsi.xlsx e clicca Importa.
Si apre una finestra con le tabelle e i fogli di lavoro del file. 
Spunta la casella di controllo Seleziona più elementi e seleziona le caselle Calendario, Corsi1 e Sedi e fai clic su Trasforma dati.
Questi dati non necessitano di modifiche, tranne per il nome della query Corsi1 in Corsi (tasto destro del mouse e Rinomina).
Come prima fai clic Home / Chiudi e carica Chiudi e carica in.
Seleziona Crea solo connessione e seleziona la casella Aggiungi questi dati al modello di dati.
Ora abbiamo la tabella dei dati Data e le tre tabelle di ricerca Calendario, Corsi e Sedi.

Modellare i dati

Al momento abbiamo 4 tabelle indipendenti che non comunicano tra loro, affinché ciò avvenga vanno messe in relazione.
Prima di farlo ecco le regole generali per mettere in relazione due tabelle:

Esistono modi per superare parte di tali restrizioni, ma rispettiamo queste regole, che sono sufficienti nella stragrande maggioranza dei casi e creiamo le relazioni per l'esercizio corrente.
Clicca la scheda Power Pivot e quindi l’icona Gestisci poi clicca l’icona Vista diagramma nella scheda Home.
È possibile trascinare le tabelle nella pagina per organizzarle meglio.
Per creare una relazione, trascina i campi nella tabella Data sui campi correlati delle altre tre tabelle (Date su Date, Corsi su Corsi, Sedi su Sedi) fino ad ottenere l'immagine sottostante.

Power Pivot Relazioni
Relazioni Power Pivot
Hai notato una disposizione particolare delle tabelle, 3 in alto e una in basso?. Non è obbligatorio disporre le tabelle in un determinato modo, ma sono trucchi visivi per distinguere le tabelle dimensionali dalle tabelle dei fatti.
Lo schema a stella (star schema) nonostante sia il più semplice tra i modelli di dati, è quello più adatto per la reportistica di business intelligence. Esso posiziona al centro la tabella di fatti (fact table) e intorno le tabelle dimensionali (dimension tables).
Lo schema a stella (star schema)
Schema a stella

La tabella dei fatti contiene i valori numerici che si desidera aggregare: importi, quantità, conteggi, ecc.
Ciascuna riga della fact table ha chiavi esterne da relazionare con le rispettive dimension tables, che possiederanno lo stesso campo come chiave primaria (senza duplicati). Ad esempio una tabella dei fatti "Vendite", con una riga per ogni ordine effettuato; ogni ordine avrà a sua volta una data, un codice cliente, un codice negozio, un codice prodotto, ecc. Ogni attributo sarà potenzialmente duplicato più volte nella fact table (per esempio il medesimo cliente può fare più ordini oppure in uno stesso giorno verranno effettuati più ordini, ecc.), e andrà in relazione uno a molti (dove il lato molti è quello della fact table) con una tabella dimensionale.
Ogni tabella dimensionale avrà una chiave primaria che identifica univocamente l'entità che descrive (ad esempio il codice prodotto per la tabella dimensionale "Prodotti") e altri attributi che descrivono l'entità stessa (ad esempio marca, colore, categoria, ecc.).
Se si hanno più fact table da includere e confrontare nello stesso report (per esempio una tabella “Vendite” e una “Resi”, la regola fondamentale è di non mettere in relazione due tabelle dei dati perché spesso sono relazioni molti a molti, le quali non sono consentite. Per risolvere il problema basta usare le tabelle dimensionali, che possono andare in relazione uno a molti con ciascuna fact table.
Tra le tabelle dimensionali vi è la tabella calendario che ha tutti i dettagli e le aggregazioni temporali utili per fare le analisi. Questa tabella per funzionare bene deve:

La tabella calendario va messa in relazione uno a molti con la o le tabelle dei fatti, ma dovranno avere lo stesso livello di dettaglio. Se la tabella calendario ha un dettaglio mensile e giornaliero e la tabella dei fatti no, esse non potranno essere relazionate.

Torniamo all'esercizio.
Ordina i record del campo Mese della tabella Calendario. Vai su Home / Vista dati (si trova nella scheda Power Pivot / Gestisci). Clicca sulla scheda Calendario, in basso a sinistra, per passare alla tabella Calendario. Seleziona la colonna NomeMese e clicca Home / Ordina per colonna / Ordina per colonna… Nella finestra che compare, la colonna NomeMese è già nel primo elenco perché la colonna è stata selezionata prima di aprire la finestra. Nel secondo elenco, selezionare la colonna Mese.
Se lo desideri ripeti il processo per la colonna NomeGiornoSettimana da ordinare per GiornoSettimana.
L'ultima modellazione sta nel contrassegnare la tabella Calendario come tabella delle date (importante per un'efficace analisi dell'intelligence nel tempo: comunicando a Power Pivot quale tabella utilizzare per i calcoli della data, si impedisce a Power Pivot di creare la propria tabella delle date ogni volta che si utilizza un campo data nelle tabelle pivot), quindi vai sulla scheda Progetta / Contrassegna come tabella data / Contrassegna come tabella data / Ok

Creare misure DAX

Le misure DAX sono molto più efficienti rispetto ai calcoli che si fanno nelle tabelle pivot, specialmente quando si ha a che fare con set di dati di grandi dimensioni. Dalla scheda Power Pivot clicca l’icona Passa a cartella di lavoro per tornare a Excel.

Misura 1

Cliccare Power Pivot /  Misure / Nuova misura. La prima misura sarà la somma del numero totale dei partecipanti.
Imposta come da foto sottostante.

Power Pivot DAX
DAX nelle Power Pivot

Quando si digita nella casella, è possibile premere il tasto Ctrl e scorrere la rotellina del mouse per ingrandire e ridurre l'area rendendo la formula più facile da leggere.
Fai clic sul pulsante Controlla formula per confermare che non ci sono errori e clicca Ok.

Altre possibili misure

Power Pivot DAX
DAX nelle Power Pivot

Creare tabelle pivot utilizzando il modello dati Power Pivot

Finora abbiamo importato dati da più file, modellato i dati, creato relazioni ed effettuato un paio di calcoli DAX.
La fase finale è creare una normale tabella pivot utilizzando però il modello di dati appena costruito.
Clicca su Inserisci / Tabella pivot.
Excel rileva che hai un modello di dati in questo file e presume tu voglia utilizzarlo per la tabella pivot, quindi clicca Ok.
Compare una una cartella di lavoro vuota (i dati sono caricati sul modello, nulla sui fogli) quindi inserisci la tabella pivot sul foglio esistente.
Utilizza le due misure, presenti in Data, nell'area Valori della tabella pivot e i campi delle altre tabelle nelle aree Righe, Colonne, Filtro,all'occorrenza usa anche Inserisci filtro dati.
Tutto il resto è uguale all'uso delle tabelle pivot (se non te le ricordi leggi come utilizzare le tabelle pivot).


Per saperne di più su Microsoft Excel potrebbero interessarti questi due libri:



Se vuoi approfondire alcuni dei temi trattati, visita la pagina con le mie pubblicazioni cartacee e online.

Se l'articolo ti è piaciuto, condividilo!

Segui l'hashtag #AndreaPacchiarotti