Tutorial [PowerPivot] Lavorare con le Power Pivot

Stato
Chiusa ad ulteriori risposte.

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
5.049
213
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
352
Ciao a tutti. In questo tutorial sulle Power Pivot, vedremo passo-passo come crearne una, che differenze ci sono rispetto a una semplice tabella pivot, e come sfruttare le potenzialità dello strumento.

Se non avete familiarità con le tabelle pivot "classiche", prima di proseguire consiglio di acquisire una certa pratica con esse, poiché sono la base per comprendere il funzionamento delle Power Pivot, che ne sono l'evoluzione.
Consiglio inoltre di leggere le FAQ sulla suite "Power", anche per capire se e come si può attivare il componente aggiuntivo Power Pivot.
________________________________________________

INDICE

Parte 1 - Creazione di una Power Pivot
- Aggiungere una tabella al modello di dati​
- Creare una Power Pivot​
Parte 2 - Lavorare con più tabelle
- Relazioni tra due tabelle​
Parte 3 - Cenni di data modeling - lo "Star schema"
- Star schema​
- Fact table​
- Dimension table e "snowflake schema"​
- Lavorare con più fact table​
- Costruire un report basato su uno star schema​
Parte 4 - Lavorare con le date
- Automatic time intelligence​
- La tabella calendario​
- Lavorare con più date​
- Bonus: tabella calendario in Power Query​
 
Ultima modifica:

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
5.049
213
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
352
Parte 1: Creazione di una Power Pivot

Iniziamo col dire che le Power Pivot non si appoggiano direttamente a un intervallo di celle, come le pivot classiche, bensì al modello di dati (data model), ovvero un'area di lavoro dove si possono importare dati da fogli excel, file esterni, database e tante altre sorgenti. Questi dati vengono memorizzati nel modello di dati in tabelle bidimensionali (come i fogli di excel), che possono poi essere messe in relazione tra loro per creare analisi complesse.

Aggiungere una tabella al modello di dati
Ma iniziamo dal caso più semplice, ovvero un modello dati costituito da una sola tabella.

Per aggiungere un intervallo di celle al modello dati, ci sono varie opzioni:
1) Al momento di creare la tabella pivot, spuntare la casella "Aggiungi questi dati al modello di dati"
2) Formattare l'intervallo dati come oggetto tabella, poi dal menu Power Pivot di Excel cliccare "Aggiungi a modello di dati"
3) Successivamente a un'elaborazione in Power Query, scegliere "Chiudi e carica in..." e spuntare la casella "Aggiungi questi dati al modello di dati"


Le tre modalità per aggiungere una tabella al modello di dati (Office 365)

Indipendentemente dal sistema utilizzato, io consiglio comunque di organizzare i dati di partenza in forma di tabella, dal menu Home->Formatta come tabella (Ctrl+T): si potrà dare ad essa un nome significativo (molto utile in seguito), e sarà più facile gestire gli aggiornamenti e le aggiunte di dati.

Creare la Power Pivot
Per creare la nostra prima Power Pivot, non resta ora che inserire una tabella pivot come si è sempre fatto, avendo cura di selezionare come sorgente dati "Usa il modello di dati di questa cartella di lavoro".
È anche possibile, dopo essere entrati nella gestione del modello dati dal menu Power Pivot, cliccare su "Inserisci tabella pivot": in questo caso non verrà chiesta l'origine dati (viene usato il modello dati di default), ma solo la destinazione, cioè il foglio di lavoro


Due modi per creare una Power Pivot (Office 365)

Benissimo: abbiamo creato la nostra prima Power Pivot! In apparenza, è uguale in tutto e per tutto alle tabelle pivot classiche, e in effetti ne condivide l'aspetto e i principi di funzionamento. Ma il fatto di appoggiarsi al modello dati apre la strada a nuove e potentissime opportunità di analisi dei dati, che scopriremo nei prossimi tutorial. Una piccola anticipazione: se trascinate un campo nell'area "Valori", e cercate di modificare la funzione di aggregazione, vedrete che oltre alle solite Somma, Conteggio, Media.... ne è comparsa una nuova: l'utilissima Distinct Count, ovvero il conteggio degli elementi univoci
 
Ultima modifica:
  • Like
Reactions: ges

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
5.049
213
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
352
Parte 2: Lavorare con più tabelle

Nella prima parte di questo tutorial abbiamo visto come creare una Power Pivot con un modello dati costituito da un'unica tabella.

Sebbene anche così le possibilità di analisi si amplino notevolmente rispetto a una tabella pivot classica, è quando andiamo a utilizzare più tabelle in relazione che le Power Pivot mostrano appieno il motivo per cui hanno questo nome.

Una trattazione completa a livello teorico di come si costruisce un modello dati ottimale esula dai fini di questo tutorial (esistono ottimi testi sul tema), perciò mi limiterò qui a definire i requisiti fondamentali, riservandomi di andare più in profondità in seguito.

Relazioni tra due tabelle
Queste sono quindi le regole base per mettere in relazione due tabelle tra loro in un modello dati di Excel (gli stessi concetti valgono per Power BI Desktop):
1) La relazione deve essere impostata su un unico campo (non è possibile fare relazioni multi-campo, come ad esempio in Access). Possono essere definite altre relazioni tra le stesse due tabelle, ma esse saranno marcate come inattive.
2) I campi da mettere in relazione devono essere dello stesso tipo.
3) La relazione deve essere di tipo uno-a-molti: una delle due tabelle deve pertanto avere come chiave univoca (senza duplicati) il campo oggetto della relazione. Se entrambe le tabelle hanno valori duplicati, il motore di gestione del modello dati si rifiuterà di creare la relazione, restituendo un errore.

Naturalmente ci sono modi e tecniche per superare almeno in parte queste restrizioni, ma per ora teniamo come buone queste regole, che possono essere sufficienti nel 95% delle casistiche.

Per creare una relazione, il modo più semplice e intuitivo è quello di aprire la gestione modello dati dal ribbon di Power pivot, e dalla Tab Home cliccare "Vista diagramma". In questa schermata vengono rappresentate le tabelle che abbiamo aggiunto al modello, e come si vede dal seguente video è molto semplice creare le relazioni: basta trascinare uno sull'altro i campi da collegare, e il motore si occupa autonomamente di capire qual è il lato "uno" e quale il lato "molti", creando la corretta relazione.

Come creare le relazioni tra tabelle nel modello dati

Potreste aver notato una disposizione particolare delle tabelle nel mio esempio: 4 più piccole in alto, e una più grande in basso. Non è obbligatorio disporre le tabelle in un certo modo piuttosto che in un altro, ma si tratta di "trucchi" visivi per distinguere le tabelle dimensionali dalle tabelle dei fatti, e per visualizzare a colpo d'occhio la direzione del flusso dei filtri. Riprenderemo questi concetti più avanti.

Dopo aver definito le relazioni, andiamo a creare la nostra tabella pivot, che come vedremo ha qualcosa di diverso: nell'elenco dei campi, infatti, vedremo tutte le tabelle del modello, ed espandendole comparirà l'elenco dei campi di ciascuna. Si apre quindi la possibilità di riassumere dati da più tabelle.

Creazione di una tabella pivot multitabellare
 
Ultima modifica:
  • Like
Reactions: ges

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
5.049
213
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
352
Parte 3: Cenni di data modeling - lo "Star schema"

In questa parte del tutorial si entra un po' di più nella teoria, ma è fondamentale avere almeno i concetti base del data modeling al fine di poter capire i passaggi successivi. So che per qualcuno sarà complesso "digerire" questi concetti tutti insieme, ma non scoraggiatevi se non è tutto chiaro alla prima lettura: piano piano, lavorando su questi strumenti, tutto inizierà ad avere un senso ;-)

Star schema
Lo "star schema" (schema a stella) è il più semplice tra i modelli di dati, ed è anche quello più adatto per la reportistica in ambito business intelligence, con interfacce grafiche come Power Pivot, Power BI etc.
Per ragioni di semplicità nella navigazione del modello e di performance, pertanto, ogni volta che ci è possibile dobbiamo cercare di ricondurre i nostri dati a uno star schema.

Ma cos'è questo star schema? Partiamo dal nome: si chiama così perché può essere rappresentato, appunto, come una stella, con al centro una tabella di fatti (fact table) e intorno ad essa le tabelle dimensionali (dimension tables).


Rappresentazione grafica dello star schema

Fact table
La tabella dei fatti è una tabella che contiene i valori numerici che intendiamo aggregare: importi, quantità, conteggi, etc.
Ciascuna riga della fact table avrà delle chiavi esterne, che andranno in relazione con le rispettive tabelle dimensionali, che avranno lo stesso campo come chiave primaria (senza duplicati). Quindi possiamo avere una fact table "Vendite", con una riga per ogni ordine effettuato. Ma ciascun ordine avrà una data, un codice cliente, un codice negozio, un codice prodotto... ciascuno di questi attributi sarà potenzialmente duplicato N volte nella fact table (uno stesso cliente può fare n ordini, in uno stesso giorno ci possono essere n ordini, un prodotto può essere acquistato in n ordini diversi, etc.), e ciascuno di questi attributi andrà in relazione uno-a-molti (dove il lato "molti" è sempre quello della fact table) con una tabella dimensionale.

Dimension table e "snowflake schema"
Ogni tabella dimensionale avrà una chiave primaria che identifica univocamente l'entità che descrive (es. il codice prodotto per la tabella dimensionale "Prodotti"), e una serie di altri attributi che descrivono l'entità stessa. Nel caso della tabella prodotti, ad esempio, potremmo avere marca, colore, categoria, sottocategoria...
Si potrebbe osservare che questi attributi saranno duplicati nella tabella dimensionale, e che quindi potrebbe essere meglio metterli ciascuno nella propria tabellina dedicata, che a loro volta saranno messe in relazione uno-a-molti con la tabella Prodotti, creando una "catena" di tabelle relazionate a cascata.
Questo è vero in termini di dimensioni fisiche del modello e di integrità dei dati, ma vale principalmente per le applicazioni (tipo database transazionale) dove questi parametri sono di massima importanza.
Nella business intelligence, al contrario, si privilegiano le prestazioni e la facilità di lettura e navigazione del modello di dati, quindi alla rappresentazione "atomica" del modello descritta poc'anzi (che prende il nome di "snowflake schema") si tende sempre a preferire lo star schema. Ciò nondimeno, anche uno snowflake schema è perfettamente adatto all'utilizzo in Power Pivot, e se il numero di record che dobbiamo analizzare non è eccessivamente elevato, l'impatto sulle prestazioni sarà minimo e impercettibile.

Lavorare con più fact table
Capita molto di frequente di avere più fact table che descrivono eventi diversi ma che vogliamo includere e confrontare nello stesso report. Ad esempio potremmo avere una tabella di vendite e una di resi, o un'altra con le richieste di assistenza.
Queste tabelle, benché descrivano eventi diversi, avranno molti attributi in comune: ad esempio la data, oppure il codice cliente.
La regola fondamentale da seguire è: MAI mettere in relazione diretta due fact table. Con ogni probabilità il motore di Power Pivot ce lo impedirebbe comunque, perché si tratta quasi sempre relazioni molti-a-molti (che non sono consentite), ma in ogni caso una relazione di questo tipo creerebbe problemi a non finire.
Come possiamo fare quindi a confrontare numeri aggregati da tabelle dei fatti diverse? Semplice: usando le tabelle dimensionali, che possono essere "condivise" tra più fact table, andando in relazione uno-a-molti con ciascuna di esse.

Costruire un report basato su uno star schema
Quando andiamo a creare un report, in modo particolare se include più di una fact table, è fondamentale ricordare che tutti gli attributi usati per filtrare il rapporto (quindi elementi di riga, di colonna, filtro rapporto e slicer) dovranno essere presi dalle tabelle dimensionali, MAI dalle fact table. Per evitare di sbagliarsi, Power Pivot offre un ausilio all'utente: le chiavi esterne delle fact table (che non devono mai essere inserire nelle Pivot) possono essere nascoste agli strumenti client, andando sulla vista diagramma del modello, cliccando i campi da nascondere con il tasto destro, e infine scegliendo "Nascondi a strumenti client". I campi verranno oscurati e non saranno più visibili nell'interfaccia della Power Pivot. Allo stesso modo, se dovesse rendersi necessario, si possono nascondere intere tabelle

Nascondere le chiavi esterne agli strumenti client
 
Ultima modifica:
  • Like
Reactions: ges and Powerwin

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
5.049
213
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
352
Parte 4: Lavorare con le date

Quando si creano dei report in Power Pivot, una delle attività che capitano più di frequente è quella di fare aggregazioni temporali: vendite aggregate per mese, per anno, o per settimana; confronti tra periodi consecutivi (anno su anno, settimana su settimana), totali parziali, e così via. Le tabelle pivot sono da sempre gli strumenti più idonei in Excel per fare queste analisi, ma il modello di dati ci dà decisamente una marcia in più in termini di flessibilità e potenza.

Automatic time intelligence
Potrà esservi già capitato, lavorando su una tabella pivot, che trascinando un campo data in riga o in colonna, Excel creasse automaticamente una gerarchia temporale, aggiungendo campi quali Anno, Mese, o Trimestre che non esistevano nella matrice dati originale. Questa funzione, che di primo acchito può sembrare molto pratica, in realtà limita molto le possibilità di manovra, pertanto è bene abituarsi fin da subito a rinunciarvi, disabilitando la funzione dalle opzioni di Excel -> Dati, come da immagine, e passando alla tecnica della tabella dimensionale calendario.


Disabilitare la creazione automatica di gerarchie temporali nelle tabelle pivot

La tabella calendario
Nella parte 3 abbiamo dato le definizioni di tabelle dei fatti e tabelle dimensionali. Tra queste ultime, ce n'è una un po' particolare, che è la tabella delle date, o tabella calendario. Si tratta di una tabella contenente tutti i dettagli e le aggregazioni temporali che ci occorrono per fare le nostre analisi, ed è da questa tabella che andremo a inserire i campi relativi alle date nella nostra tabella pivot.

La tabella calendario, per funzionare bene e consentire l'utilizzo corretto delle funzioni di time intelligence delle Power Pivot, deve avere alcune caratteristiche ben precise:
1) Avere dettaglio giornaliero (una riga per ogni giorno)
2) Iniziare dal primo giorno degli eventi che vogliamo analizzare, e finire all'ultima data disponibile per gli stessi, 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. Spesso, per uniformare i report, si sceglie di iniziare dal 1 gennaio del primo anno disponibile, e finire al 31 dicembre dell'ultimo anno disponibile. Va bene anche così, purché si rispetti la regola di avere sempre una riga per ogni giorno, tra i due estremi.
3) Contenere tutte le colonne che possono servire per le nostre analisi (es. mese, anno, settimana, anno fiscale, trimestre....)

Ci sono vari modi per poter creare una tabella calendario: si può ad esempio utilizzare Power Query, oppure, ancora più semplicemente, utilizzare la funzione dedicata di Power Pivot, che in inglese si chiama (intuitivamente) "Date table", mentre in italiano, con un imbarazzante errore di traduzione a oggi mai corretto, porta l'etichetta "Elimina tabella" (!!!).
Questo pulsante, ben lungi dal fare quello che il suo nome suggerirebbe, va al contrario a creare una tabella calendario, con numerose colonne già impostate, e lo fa andando a scansionare tutte le tabelle presenti nel data model e individuando automaticamente, nelle colonne di tipo Data, la data più remota e quella più recente nei nostri dati.


Il pulsante per creare la tabella calendario, con l'incredibile errore nella traduzione italiana dell'etichetta

Una volta creata la tabella calendario, essa sarà messa in relazione uno-a-molti con la/le tabelle dei fatti. Attenzione: le tabelle dei fatti e la tabella calendario, per essere messe in relazione, dovranno avere la stessa granularità, ovvero lo stesso livello di dettaglio. Se la tabella calendario è stata costruita come descritto, e la tabella dei fatti ha un dettaglio mensile e non giornaliero, esse non potranno essere messe in relazione: anche scegliendo il campo mese (o mese-anno se lo abbiamo creato) dalla tabella dimensionale, avremo una relazione molti-a-molti o addirittura molti-a-uno, che non fanno al caso nostro. Come risolvere? Creando nella tabella dei fatti una colonna calcolata con il dettaglio giornaliero. Ad esempio, se nella tabella dei fatti abbiamo solo le colonne Anno e Mese, possiamo creare una colonna calcolata con la formula =DATE([Anno];[Mese];1), che assocerà a ciascuna riga la data di inizio mese. Questo campo potrà essere messo in relazione con la colonna data della tabella calendario, e rendere disponibili tutte le analisi di altre aggregazioni temporali (ovviamente, solo a livello mensile o superiore: possiamo fare molto, ma certo non possiamo creare dati che non esistono! ';))

Lavorare con più date
Ma cosa accade se nel nostro modello dati ci sono più date, e vogliamo fare dei report che filtrano i dati per ognuna di esse? Ad esempio potremmo avere la data di vendita e la data di spedizione di un ordine, e vorremmo impostare un report che visualizzi e aggreghi entrambe. Allo stesso tempo, però, sappiamo già che solo una relazione tra le stesse due tabelle può essere attiva in un dato momento. Come fare quindi? Le strade maestre sono due, ciascuna con i suoi pro e contro:
1) Creare "N" tabelle calendario, ognuna dedicata a una specifica colonna del nostro modello. Questa soluzione è buona per il fatto di poter sfruttare le stesse misure (vedremo in seguito cosa sono) per tutte le date che abbiamo, ma il modello rischia di diventare una "costellazione" di tabelle calendario, e sarà più difficile da leggere, oltre che più grande come dimensioni.
2) Lasciare un'unica tabella calendario, impostando relazioni multiple con ciascuna colonna data presente nel modello. Come sappiamo, solo una relazione può essere attiva tra due tabelle, ma possiamo comunque creare altre relazioni (che saranno marcate come inattive), e "attivarle" al bisogno creando una misura in codice DAX che sfrutta la relazione non attiva.
Questa seconda soluzione in genere si fa preferire per la semplicità di navigazione del modello, ma pone alcune limitazioni sul tipo di reportistica che consente di realizzare

Bonus: tabella calendario in Power Query
Segue un esempio di query per generare una tabella calendario con molte colonne utili. Modificare al bisogno i parametri MinDate, MaxDate e WholeYear all'inizio del codice

M (Power Query):
let
    MinDate = #date(2018,1,1),        //adattare data inizio
    MaxDate = #date(2019,12,31),    //adattare data fine
    WholeYear = 1,            //impostare a 0 per considerare le date inizio e fine esatte, senza andare a inizio e fine anno
    Inizio = if WholeYear = 1 then Date.StartOfYear(MinDate) else MinDate,
    Fine = if WholeYear = 1 then Date.EndOfYear(MaxDate) else MaxDate,
    Source = List.Dates(Inizio, Number.From(Fine-Inizio), #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Rinominate colonne" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Data"}}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Rinominate colonne",{{"Data", type date}}),
    #"Inserito anno" = Table.AddColumn(#"Modificato tipo", "Anno", each Date.Year([Data]), Int64.Type),
    #"Inserito inizio dell'anno" = Table.AddColumn(#"Inserito anno", "Inizio dell'anno", each Date.StartOfYear([Data]), type date),
    #"Inserita fine dell'anno" = Table.AddColumn(#"Inserito inizio dell'anno", "Fine dell'anno", each Date.EndOfYear([Data]), type date),
    #"Inserito mese" = Table.AddColumn(#"Inserita fine dell'anno", "Mese", each Date.Month([Data]), Int64.Type),
    #"Inserito inizio del mese" = Table.AddColumn(#"Inserito mese", "Inizio del mese", each Date.StartOfMonth([Data]), type date),
    #"Nome del mese inserito" = Table.AddColumn(#"Inserito inizio del mese", "Nome del mese", each Date.MonthName([Data]), type text),
    #"Riordinate colonne" = Table.ReorderColumns(#"Nome del mese inserito",{"Data", "Anno", "Inizio dell'anno", "Fine dell'anno", "Mese", "Nome del mese", "Inizio del mese"}),
    #"Inserita fine del mese" = Table.AddColumn(#"Riordinate colonne", "Fine del mese", each Date.EndOfMonth([Data]), type date),
    #"Inserita settimana dell'anno" = Table.AddColumn(#"Inserita fine del mese", "Settimana dell'anno", each Date.WeekOfYear([Data]), Int64.Type),
    #"Inserita settimana del mese" = Table.AddColumn(#"Inserita settimana dell'anno", "Settimana del mese", each Date.WeekOfMonth([Data]), Int64.Type),
    #"Nome del giorno inserito" = Table.AddColumn(#"Inserita settimana del mese", "Nome del giorno", each Date.DayOfWeekName([Data]), type text)
in
    #"Nome del giorno inserito"
[/SPOILER]
 
Ultima modifica:
Stato
Chiusa ad ulteriori risposte.

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!