Excel Power Pivot e Business Intelligence

Migliora la performance aziendale con la BI Excel

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 24 Gennaio 2023
Categoria: Microsoft Office Excel Power Pivot

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, ottimizzarli, creare calcoli più sofisticati con le misure DAX e costruire indicatori KPI, il tutto può poi essere analizzato con le Tabelle Pivot di Excel o con Power BI. In tal modo anche una piccola impresa 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. Partiamo con Excel Power Pivot e Business Intelligence!

Sommario Power Pivot

• Installare Power Pivot
• Differenze tra Power Pivot ed Excel
• Tabelle pivot e Power Pivot
• Power Pivot esercizio completo
  • Importare i dati
  • Modellare i dati
  • Creare misure DAX
  • Creare KPI
  • Creare tabelle pivot utilizzando il modello dati 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

• Importare dati da origini diverse (ad esempio grandi database aziendali, fogli di calcolo, file di testo)
  In Excel: Importare tutti i dati da un'origine dati
  In Power Pivot: Filtrare i dati e rinominare le colonne e le tabelle durante l'importazione
• Creare tabelle
  In Excel: Le tabelle possono trovarsi in qualsiasi foglio del file. Nei fogli possono essere presenti più tabelle
  In Power Pivot: Le tabelle sono organizzate in singole pagine a schede nella finestra di Power Pivot
• Modificare i dati in una tabella
  In Excel: È possibile modificare i valori in singole celle di una tabella
  In Power Pivot: Non è possibile modificare le singole celle
• Creare relazioni tra le tabelle
  In Excel: Nella finestra Relazioni
  In Power Pivot: Nella vista diagramma o nella finestra Creare relazioni
• Creare calcoli
  In Excel: Utilizzare formule di Excel
  In Power Pivot: Scrivere formule avanzate con il linguaggio delle espressioni Data Analysis Expressions (DAX)
• Creare indicatori di prestazioni chiave (KPI) e prospettive
  In Excel: non disponibile
  In Power Pivot: le prospettive consentono di limitare il numero di colonne e tabelle visualizzate dagli utenti del file Excel
• Creare tabelle e grafici pivot
  In Excel: tramite icona
  In Power Pivot: tramite icona
• Raggruppare i dati
  In Excel: tramite icona
  In Power Pivot: tramite DAX in colonne calcolate e campi calcolati

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 (un insieme di contenuto da fonti diverse 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 tabelle 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 a usare i dati già aggiunti al file corrente) si presenterà un ribbon simile al seguente:

Banalizzando l’uso di questo potente strumento e supponendo di voler importare solo 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 scegliere Access, 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 Power Pivot importerà automaticamente ulteriori tabelle collegate a quelle scelte; pigiando invece il pulsante Visualizza anteprima e applica filtro (leggi i filtri in Excel) si potranno escludere dall'importazione alcune colonne, ovvero i 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 di avere 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 Recupera 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 carica Chiudi 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 clicca 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:

• La relazione va impostata su un unico campo (non sono ammesse relazioni multi-campo, come ad esempio in Microsoft Access, perché saranno marcate come inattive
• I campi da mettere in relazione devono essere dello stesso tipo, ad esempio Numero con Numero
• La relazione deve essere uno-a-molti: una delle due tabelle deve avere come chiave univoca (cioè non deve avere duplicati) il campo oggetto della relazione, altrimenti verrà restituito un errore

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 della tabella data sui campi correlati delle altre tre tabelle, porta dunque il campo:

• Date della tabella data su Date della tabella Calendario
• Corsi della tabella data su Corsi della tabella Corsi
• Sedi della tabella data su Sedi della tabella Sedi

fino a ottenere l'immagine sottostante.

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

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:

• avere dettaglio giornaliero (una riga per ogni giorno)
• iniziare dal primo giorno degli eventi da analizzare e finire all'ultima data, senza interruzioni: conterrà quindi una riga per ogni data compresa tra i due estremi, a prescindere dal fatto che in quella data ci siano stati o meno degli eventi
• contenere tutte le colonne utili alle nostre analisi, come settimana, mese, trimestre, anno, ecc.

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.

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

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

Creare KPI

Creare un indicatore KPI

1. Nella scheda PowerPivot cliccare l'icona KPI > Nuovo KPI e, dall'elenco a discesa della finestra che si apre, scegliere la misura di interessa (è necessario che ne sia presente almeno una), ad esempio TotAllievi
2. In Definire il valore di destinazione selezionare:
1. Misura e quindi selezionare una misura di destinazione nella casella
   oppure
2. Valore assoluto e quindi digitare un valore numerico (ad esempio 400)
3. In Definisci soglie di stato regolare i valori soglia bassi e alti (lasciare il default)
4. In Seleziona stile icona cliccare su un tipo di immagine
5. Cliccare Descrizioni, immettere eventualmente le descrizioni per KPI, Valore, Stato, Destinazione e cliccare Ok

Per modificare o eliminare un indicatore KPI cliccare l'icona KPI > Gestisci KPI e con il destro del mouse sull’area calcoli.

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. In Data troverai anche un semaforo, è il KPI, cliccalo e seleziona tutte le caselle di controllo (o, al limite, non selezionare Obiettivo).
Tutto il resto è uguale all'uso delle tabelle pivot (se non te le ricordi leggi come utilizzare le tabelle pivot).

Piaciuto Excel Power Pivot e Business Intelligence? Condividilo!

Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: