Principi di Business Intelligence

Principi di business intelligence: lezione 1

Guardiamo al dataset02. Ogni azienda ha un suo gestionale, tipo Oracle, che l’azienda installa e con cui l’azienda gestisce tutto (contabilità, finanza, risorse umane, ecc). Questi software gestionali sono rigidi, quindi contengono dati, ma appunto essendo rigidi perché devono essere super legali ecc, mi danno dei vincoli. I dati non li vedo come dico io, ma come dice il gestionale. Questo non è troppo buono, perché un’azienda vuole poter vedere i propri dati anche in un altro modo. Quindi si esportano i dati da quei software, e li trasferiscono su Excel (dove si possono gestire a modo proprio, che sia utile). I grandi software sono molto chiari e diretti, sono fatti per essere efficienti nel gestire la vendita ad esempio (parti operative). Ma quando voglio fare un’idea di business e quindi pianificazione, servono dei reporting e quindi bisogna estrarre quei dati. Separare l’ambito della gestione da quello della reportistica (la business intelligence).

Tabella sales e struttura dei dati

Quella che vediamo è una tabella sales (dal gestionale esportiamo le vendite). Non è propriamente una tabella: è formata da colonne. Ogni colonna ha una propria intestazione (anche se Excel mi fa nominare colonne diverse allo stesso modo, quando ci trasferiremo sulle tabelle pivot non potremo farlo, quindi non lo facciamo nemmeno ora) e ogni colonna deve avere una stessa tipologia di dati. Excel lavora su ogni singola cella.

Le colonne rappresentano: prima colonna è il customer number (identificativo del cliente in maniera univoca): da qualche parte ho una tabella che lega quel numero C393... ad un cliente; seconda colonna: prodotto che è stato comprato, tramite un codice che identifica l’oggetto. Terza colonna: stabilimento di produzione, cioè dove è stato fisicamente costruito quel prodotto comprato da quel cliente. Quarta colonna: data in cui l’ordine è stato inserito a sistema; quinta colonna: quando l’azienda ti promette di spedirti. Sesta colonna: quando effettivamente ho spedito. Settima colonna: data di fatturazione, cioè la data in cui la proprietà passa dall’azienda al cliente (quando ci addebitano, cioè quando producono la fattura che dice del passaggio di proprietà). Già confrontando la quinta e la sesta colonna mi accorgo di quanto sono stato puntuale (tra la promessa di spedizione e la spedizione effettiva). Colonna order quantity: 440 unità di quel prodotto sono state acquistate da quel soggetto in quella prima riga. Sales AMT: totale della transazione, quanto costa tutto al cliente. Unit price è il prezzo unitario del prodotto. Product Series è la famiglia del prodotto: ad esempio iPhone è una famiglia di prodotto. Questa colonna mi serve perché cercheremo di fare dei report essendo il più sintetici possibile: cerchiamo di aggregare quelle righe e colonne in modo tale da capire. Le cose dettagliate ce le ho già, ed è la tabella che vediamo.

Ogni riga è invece una transazione: uno scambio tra l’azienda e il soggetto (customer number). Quante transazioni ci sono? = il numero delle righe. Ctrl + tasto in basso per andare all’ultima riga della tabella. Product, customer e calendar sono tabelle di diverso tipo, di dimensioni. La tabella sales è invece una tabella di fatti. Si distinguono due tipi di tabelle: dimensionali e di fatti. Ogni riga è un insieme di campi. Nelle tabelle di fatti, le righe sono fatti, cioè cose successe. Il cliente CA85902 ha comprato 480 pezzi del prodotto PLT09 e così via. Ci sono numeri che voglio identificare, quindi voglio quantificare. Invece le tabelle come “customer number” sono dimensionali e quindi contengono solo descrizioni (del cliente) e quindi contengono entità di business e attributi (ad esempio chi è il cliente, quanti dipendenti ha, dove è situato) e tutta una serie di caratteristiche che descrivono il cliente.

Iniziamo con la tabella dei fatti: la tabella sales

Per fare la somma di una tabella clicchiamo sull’intestazione della tabella e vediamo in fondo la media, il conteggio e la somma.

Se noi guardiamo al prezzo unitario, alle quantità totali e al totale dei ricavi, qualcosa non coincide. Questo perché in ogni riga le quantità sono diverse, quindi non possiamo fare affidamento sulla media aritmetica (che è quello che ci dà Excel) ma dobbiamo usare la media ponderata.

Ctrl + shift + freccia su e poi shift + freccia giù è per evidenziare tutta la colonna e il secondo passaggio è per togliere la prima riga (cioè quella dell’intestazione, che ovviamente non vogliamo sommare).

Se vogliamo che Excel valuti quello che stiamo scrivendo (calcolo) si parte con = nella cella. Per vedere ciò che contiene davvero ogni cella clicchiamo f2 per ogni cella.

=SOMMA(primo input è in grassetto perché è obbligatorio. Dal secondo in poi abbiamo la quadra. Clicco sulla cella più vicina della colonna che voglio sommare e poi faccio la mossa di sopra ctrl + shift + freccia su e poi shift freccia giù. Se voglio fare la somma di un’altra colonna, faccio ctrl c della somma che ho appena fatto, e ctrl v in uno spazio diverso di un’altra colonna. Sto quindi copiando la funzione, e mi darà la somma di quell’altra colonna. Quindi ottengo il totale dei ricavi e delle quantità. Poi faccio la seguente divisione: Ricavo/quantità = prezzo medio ponderato.

Però se sono una grande azienda, voglio sapere il prezzo medio per ogni categoria di prodotto (specie se il range del prezzo medio di un prodotto va da 50 a 5000 euro). Quindi attrezzo questa tabellina: prendiamo

Tabelle pivot

Tabelle pivot: le pivot sono alimentate da una sola tabella: da quale tabella voglio prendere i dati. Ci sono anche pivot che prendono i dati da più tabelle. Mi piazzo su una cella, faccio in alto a sinistra inserisci: tabella pivot.

In basso seleziono nuovo foglio di lavoro.

A questo punto mi si apre un nuovo foglio, clicco sulla pivot e mi compaiono i campi della pivot, che corrispondono alle intestazioni delle colonne della mia tabella. Inoltre, cambio il nome della mia pivot e la rinomino come: “pivot sales”, specifico sales perché sto facendo una pivot sulla tabella sales appunto.

Andiamo flagghiamo una colonna non numerica: product series: ovviamente non si può fare la somma di valori non numerici; quindi, ci mostra la lista distinte delle famiglie di prodotto, semplicemente.

P1, P2, P3 sono famiglie di product series (perché è l’intestazione che ho fleggato).

Se invece considero la colonna product, ottengo la stessa cosa. Per capire quanti tipi di prodotto, conto la colonna.

La pivot tende a sintetizzare, quindi ovviamente non posso inserire nella pivot tutte le colonne, altrimenti utilizzerei la tabella originaria. La pivot mi dà sintesi di certe dimensioni della tabella iniziale.

Le colonne si usano solo quando ho qualcosa in riga, per rendere più proporzionato un report. Genero in questo caso, una tabella a doppia entrata.

L’unico valore che non ha filtri è il totale dei totali, cioè il valore in basso a destra. No grafico a torta, robe semplici. No pivot complicate. Non fare pivot con due operazioni sulle colonne. In colonna si mette sempre e solo un solo calcolo. Perché infatti, avendo sulle colonne order quantity e sales AMT non riesco a leggere bene.

In questa tabella ho 4 filtri applicati: la serie, il prodotto, lo stabilimento di produzione e i clienti. I grafici annessi sono: colonne, linee, ma non torta o tridimensionali. Mi dice che la serie è il P03, l’impianto di produzione è il PLT04, il prodotto è il P31887 e i clienti che comprano quel prodotto sono due.

Come faccio ad usare una pivot per comunicare chi lavora sulla singola riga? Faccio doppio click sulla sola cella che mi interessa, mi si apre un nuovo foglio di prodotto in cui mi compaiono i dettagli delle cose che ci interessano.

Copia – incolla sulla prima riga: voglio incollare i valori. Quindi copiamo i nostri 3 valori, ctrl c e poi tasto destro incolla 123. P1, P2 e P3 sono 3 filtri.

Quindi non posso sommare ogni riga di quella colonna. Ognuna di quelle celle è indipendente dalle altre. Quello che chiediamo è di sommare la colonna qta e la colonna ricavi (ma non sommando i 3 numeri a mano), facendo la funzione.

Per fare i totali devo togliere i filtri, copio quindi quei 3 valori (in termini di loro funzioni) e le incollo nei totali della tabella. Abbiamo ottenuto una tabella pivot:

Quanti sono i clienti che abbiamo servito? Se tutte le fatture che emetto sono solo su un cliente, io avrò pur sempre un solo cliente. Quindi ci servono unità distinte.

21.853: ottengo quel valore mettendo il filtro sul valore P1. Il calcolo è sempre lo stesso ma è fatto con valori diversi al loro interno, perché ogni cella si sottopone ad un filtro diverso. Filter context = product series = P2.

Se facciamo inserisci grafico:

Principi di business intelligence: lezione 2

Campo calcolato

Campo calcolato: lavoriamo sulla pivot sales WIP (work in progress): abbiamo tolto tutte le righe (filtri) lasciando solo product series e somma di sales AMT.

Se vogliamo filtrare un elemento, dato che nella vecchia pivot i filtri compaiono sopra e sono scomodi perché se devo aprirli mi coprono la tabella, faccio in questo modo, così mi compare una ulteriore tabella: (slyser --> selettore).

Se voglio guardare solo il plant01, basta che clicco sopra, sullo slyser e mi compare una tabella con il filtro su quello, quindi facendo vedere solo plant02 e plant03.

Torniamo alla pivot: selezionando somma di sales AMT vediamo che compare una colonna di aggregazione ed è giusto così perché quella grandezza è sommabile. Stessa cosa vale per order quantity: per ogni colonna che selezioniamo che tratta una grandezza che è sommabile, ci basterà una sola colonna nelle pivot. Una grandezza è sommabile quando il totale è uguale alla somma delle sue parti. Infatti, il totale delle vendite è uguale alla somma delle singole vendite, stessa cosa con le quantità. Invece, il prezzo totale non è la somma dei prezzi e nemmeno la media dei prezzi.

Le colonne in euro sono tutte aggregabili, il problema quindi ce l’abbiamo quando abbiamo euro x unità (ad esempio il prezzo). Media di unit price, perché quella è una media ponderata; quindi, per fare quel calcolo ci servono almeno due colonne.

Dobbiamo fare un campo calcolato. Ci posizioniamo su una cella numerica della pivot (non su P01, P02 o P03, perché in quel caso ci compare “inserisci elemento calcolato” che non è quello che vogliamo perché fa la somma di P01 + P02, quindi un subtotale). È una formula che si serve di due colonne, non può essere una colonna, anche se compare a destra nei filtri. Quindi è un algoritmo: la formula del prezzo medio è ricavi / quantità: una volta fatto il calcolo, ci esce una colonna “Somma di Prezzo Medio CALC” con dei prezzi medi CALC tipo 0 o 1 euro. Dobbiamo spostare i decimali, e vedremo che ci compariranno 0,8 ecc.… quindi i veri risultati.

Mettiamo sulle righe la production series e lo stabilimento, quindi per ognuno dei due vediamo i ricavi e il prezzo medio.

Come avviene il calcolo evidenziato in verde? Prima applichiamo il filtro alla colonna product series (valore di product series in cui sta quel calcolo è P3), poi si applica un ulteriore filtro sulle production plant (PLANT07). Quel calcolo (evidenziato) è dato dal rapporto tra 952mila e 16 milioni. Se noi togliessimo le colonne di sales e quantity, quel calcolo rimane valido perché in una pivot ogni cella è calcolata indipendentemente da qualunque altra cella.

La formula che abbiamo inserito nel campo calcolato, però, non ha senso: questo perché abbiamo diviso due colonne di numeri (ogni colonna indica il nome degli elementi che contiene, che sono numeri, ma la sua denominazione è appunto una collezione di numeri). La forma della formula è quindi sbagliata, anche se Excel ha comunque calcolato i risultati giusti. Come dovremmo scrivere la formula corretta? Dobbiamo far diventare quella collezione di numeri, un numero solo. Scriviamo la nuova formula: (anche se i risultati di Excel non cambiano).

Per ogni cosa fleggata ho una colonna aggiuntiva. Una sola colonna va bene per una grandezza che è sommabile quando il totale è dato dalla somma delle cose interne alla colonna. Le quantità sono sempre aggregabili. Il problema è euro x unità, ad esempio il prezzo. Il ricavo anche è sommabile. Per cose che richiedono più operazioni, tipo il prezzo medio (terza colonna nella foto), non mi basta una sola colonna.

A quella terza colonna (alla macchina) dovremmo dire di calcolare il ricavo, la quantità, e poi farne la divisione. Si fa così: cliccando su una cella di quella colonna, poi andiamo in alto e abbiamo questo:

Diamo un nome al campo, che è una nuova entità che generiamo, che sembrerà una colonna, ma in realtà è un algoritmo. Mettiamo il nome e impostiamo la formula:

Il calcolo che abbiamo fatto non ha senso. Questo perché abbiamo diviso due colonne di valori. Devo fare la divisione tra due numeri, non tra due colonne di valori. Il software non riesce ad interpretare questo calcolo.

Avrei dovuto scrivere di fare la somma di tutti i valori di sales AMT / la somma di tutti i valori di order quantity.

Cioè questo:

Ottengo questa tabella togliendo i filtri. Ogni cella di una pivot è autonoma. Infatti, se togliamo la colonna sales AMT e order quantity, il prezzo medio sopravvive. Questo perché fa i suoi calcoli autonomamente. Però non volevo la somma del prezzo medio CALC, perché non lo abbiamo chiesto. Per vedere la formula che abbiamo fatto, riclicchiamo su inserisci campo calcolato e clicchiamo sul nome di quello che abbiamo già fatto e vediamo la formula, potendo eventualmente modificarla.

Grafico pivot

Grafico pivot: andiamo sulla pivot, poi “analisi tabella pivot”, e poi “grafico pivot”.

Nel grafico dovrebbero essere rappresentati sia sales che prezzo medio CALC, il problema è che prezzo medio dal grafico non si vede perché è troppo piccolo per la scala che si sta utilizzando. Quindi dobbiamo fare un grafico a due scale: tasto destro sul grafico: cambia tipo di grafico.

A questo punto possiamo mettere un altro ordine di grandezza, identificato da una linea, la cui scala si legge a destra (ed è la scala del prezzo medio). In realtà questa linea non è corretta, perché è continua e i prezzi non sono continui. Il tempo è continuo, ad esempio.

Per ovviare al problema di sopra, possiamo passare ad un indicatore. Basta cliccare sulla linea e cambiare il formato. L’indicatore ci indica il numero esatto del prezzo medio.

Torniamo alle pivot

Torniamo alle pivot: se qualcuno ci dice che vuole la pivot per area di vendita, vediamo che tra i campi della pivot non abbiamo sales area. Una tabella per essere perfetta, oltre a contenere dati utili deve anche contenere chiavi: cioè colonne che mi permettono di andare a recuperare info aggiuntive da altre tabelle. Una chiave potrebbe essere il codice del cliente.

La sales area è presente nella tabella relativa al customer e non in quella relativa alle sales. Questo perché le due tabelle sono diverse: la customer non è una tabella dei fatti, ma è un’anagrafica (dimensione): è un elenco di riferimento. Quella delle sales è una tabella dei fatti che invece registra degli eventi reali (le vendite appunto) e solitamente si modificano molto più velocemente delle prime. La tabella dei fatti di solito ha molte righe ed è stretta in quanto a colonne (colonne = numeri e chiavi per collegarsi alle tabelle dimensione). Le tabelle tipo customer hanno invece poche righe e molte colonne.

Le colonne che non possono avere duplicati (cioè le colonne della tabella customer) si chiamano chiavi primarie. Non possono averli, perché, descrivendo il tipo di cliente, non si può avere lo stesso cliente con info diverse. Non possono nemmeno avere valori nulli (cella vuota). Le chiavi sono colonne che mi permettono di andare a ricavare info aggiuntive da altre tabelle.

Vogliamo vedere le vendite per sales area, come fare? Siccome abbiamo una pivot che prende info solo dalla tabella di sales, se voglio avere una pivot che mi mostri l’area geografica... allora la colonna sales area deve in qualche modo entrare in tabella sales. La logica è che ci interessa trovare la sales area per ogni cliente: siccome nella tabella sales abbiamo i customer number, dobbiamo cercare il customer number di cui ci interessa sapere la sales nella tabella di customer (quindi utilizziamo una colonna che le due tabelle hanno in comune, in questo caso customer no). Questa ricerca si fa tramite la funzione CERCA.VERT. Creiamo una nuova colonna nella tabella sales e scriviamo dentro la cella =CERCA.VERT (il valore del customer no che ci interessa; poi clicchiamo sulla colonna di customer no nella tabella di customer e camminiamo col mouse fino alla colonna sales area; poi digitiamo quanti passi ho dovuto fare per coprire la distanza tra le due colonne, quindi 5; infine FALSO).

Seleziono tutta la cella appena calcolata, doppio click sul quadratino in basso a destra e ho la sales area su tutta la nuova colonna.

Torno sulla pivot e noto che non compare la nuova colonna: questo perché la pivot si lega all’originale tabella sales, che non contiene la nuova colonna. Dobbiamo aggiornare la pivot: tas