Business Intelligence - Appunti completi

Business intelligence

Introduzione lezioni

• Excel tradizionale
• Pacchetto power pivot (sono pivot moderne) per utilizzare Excel come database.
• Power BI.

Lo scopo della BI (business intelligence) è trasformare i dati grezzi in informazioni. I dati diventano informazioni nel momento in cui comunicano qualcosa; i dati di per sé non comunicano nulla se non sono “organizzati” (sistemati).

Lezione 1 - 23/02/2023

Gestionale --> serve per gestire i dati, per interagire con il database.

Info su Excel:

• Non è una buona idea estrarre i dati del database da usare su Excel: i dati non dovrebbero stare qui ma dovrebbe essere utilizzato solo client per fare operazioni.
• Excel non è impostato su tabelle, ma su celle (le tabelle sono solo per i database). Inoltre, Excel non è sicuro a livello di tenuta dati.
• Importante mettere intestazioni univoche (nomi delle celle univoci) e non nulle (non lasciare vuoto), devono per forza avere un nome.
• La prima riga sono metadati, perché contengono il nome della colonna.

Dataset 01:

• Cust no= customer number, indica il numero del cliente.
• Product= prodotto, indica l’oggetto dell’ordine.
• Product plant= magazzino, indica lo stabilimento di provenienza del prodotto.
• Order entry date= data in cui è stato acquistato.
• Scheduled ship date= giorno in cui dovrebbe essere spedito.
• Invoice date= data di fatturazione, è la data in cui avviene effettivamente la transazione.
• Actual ship date= data di reale spedizione.
• Sales AMT (amount)= totale vendite (dato da quantità *prezzo).
• Product series= serie di prodotto, ovvero una serie a cui appartiene un prodotto (sono quindi dati meno particolari, non dà il modello esatto, ma solo la serie a cui appartiene).

TIPS generici:

• Per vedere direttamente il conteggio di una colonna basta selezionarla tutta (da sopra, premi la freccia in giù che appare nel nome della colonna), e poi guardare in basso a destra dove appaiono tutte le info. N.B! in questo caso al conteggio cavare 1 (prima riga non vale, è il nome della colonna. N.B! se però ci sono celle vuote non corrisponde, non conta tutte le righe in questo caso, ovvero le celle vuote NON le conta 1.
• → Per selezionare una tabella fino alla sua fine (solo quella, non tutta la colonna/riga) Click sulla cella poi (Control + shift e freccia direzionale su/giù/destra/sinistra). In questo modo arriva fino alla fine della tabella nella direzione selezionata.
• Nelle funzioni (=…) di Excel il primo numero è necessario, il secondo no (c’è la quadra infatti). Invece il numero in grassetto ci dice dove ci troviamo a livello di posizione.
• → Regolare in modo “fine” fin dove arrivare utilizzo (Shift + freccia direzionale su/giu/destra/sinistra). In questo modo regolo fin dove deve arrivare ad esempio con la somma, variando la selezione di 1 cella.
• Se faccio copia la cella con la funzione somma (o altra funzione) e la incollo in quella di fianco trasporta anche la formula, cambiando da dove prende i dati, quindi traslando i dati, es. se sposto a destra di 3 colonne, anche i dati che prende li cambia di 3 colonne. Stessa cosa se vario di riga.
• → Pulsante F2 sulla cella per vedere la formula.
• Filtro dati: lo utilizzo se voglio dividere i dati da visualizzare. → → → Es. Se voglio sapere i valori della colonna seleziono la colonna poi ordina e filtra poi filtra, e nella spunta che appare trovo la lista dei valori distinti (trovo i valori che non si ripetono, ovvero quelli che sono distinti). Questo però è fattibile solo se sono pochi i risultati, se sono molti diventa incasinato. Utilizzare un filtro permette solo di nascondere i valori che non mi interessano, però ad esempio la formula totale di una somma non cambia (nasconde solo visivamente). Utilizzo “cancella filtro da product series” per cavare i filtri selezionati (nella colonna product series).
• Subtotale: il Subtotale impersona una funzione, ma è sensibile ai filtri. È possibile impostare qualsiasi funzione, ma in questo caso utilizzando i filtri il risultato varierà, poiché prenderà in considerazione o meno i filtri applicati. Come utilizzarlo: selezionare nel primo numero la funzione che deve impersonare, in questo caso 9→ è la somma, e sarà tipo =SUBTOTALE(9;I2:I13308) Il Subtotale, quindi, dà il risultato in base ai valori visibili.
• → → → Incollare solo i valori (non la formula) seleziono i valori interessati li copio poi tasto destro → dove li devo incollare utilizzo (incolla 1,2,3). In questo modo incolla solo i valori e non la formula (questi però non si aggiornano se variano i dati).
• Il totale nelle tabelle pivot NON è mai legato ai subtotali. Il totale in una pivot è SENZA FILTRI. NON è infatti vero che i subtotali sommati danno il totale, ovvero se sommo i subtotali non è detto che danno il totale precedente, per esempio la media non tornerebbe! Non sempre i subtotali sono additivi.

Una pivot vera e propria è però dinamica.

Per fare una Pivot dinamica:

• Selezionare UNA singola cella della tabella (basta selezionare solo UNA cella, poi Excel capisce l’estensione della tabella) --> Inserisci --> tabella pivot --> (sempre consigliato mettere la tabella su → un nuovo foglio) clicco ok. N.B! l’intestazione della colonna deve essere univoca (e non vuota).
• Se voglio vedere le vendite, basta spostare o selezionare sales AMT. Di base Excel fa la somma di quei valori (infatti lo intitola “somma di sales AMT”). In questo caso li mette nella casella “valori” con la somma come funzione. → impostazioni → Cliccando sulla cella all’interno di “valori” poi campo valore posso modificare il calcolo che fa.
• → → Se chiudo l’interfaccia della pivot, posso cliccare sulla pivot in qualunque punto poi tasto dx mostra elenco campi (e l’interfaccia della pivot riappare).
• Se ci sono colonne che non hanno valori numerici, Excel non aggrega, ma fa una lista distinta di elementi. Questo perché sono questi gli elementi fondamentali per i report. Infatti, in questo caso mette product series nel riquadro “righe”.
• Se metto sales amt e product series, capisce che voglio la somma (delle vendite), per ogni diversa serie.
• Mettendo come filtro le serie, poi lo stabilimento, la pivot dà i valori che rispettano questi 2 filtri. Al totale però non ho filtri, ovvero dà il totale reale considerando tutti i valori.

La Pivot risulta utile per fare delle liste e il relativo calcolo insieme. Altrimenti se devo fare solo una cosa alla volta conviene non utilizzare la pivot. Es:

• → Riquadro “Colonne” bisogna mettere manualmente, trascinando il valore (spuntando, come standard mette nelle righe). “Colonne” fa la stessa cosa di “Righe” ma appunto sviluppa i dati su colonne. Semplicemente quindi cambia come vengono ordinate (per righe o per colonne). N.B! Se mettiamo una cosa in Colonne ci deve essere almeno una riga. Inoltre non mettere mai più di una colonna (altrimenti non si capisce nulla). NON mettere neanche più di 1 calcolo se abbiamo una colonna. Infatti, se metto più calcoli nelle colonne non ci capisce nulla, viene troppo complicato. In questo caso meglio mettere su righe i calcoli.
• → Tabella a doppia entrata ovvero vuol dire generare una tabella con righe e colonne (una sola categoria di dati per le colonne però), in modo che sia più leggibile. N.B! Meglio mettere nelle colonne dove ho meno valori (vi saranno quindi meno colonne), ovvero meglio mettere nelle righe dove ho più elementi (le righe sono più sottili, quindi, allungano meno la tabella rispetto a mettere più colonne).

Excel genera un prodotto cartesiano e quindi, se una riga o colonna ha un valore vuoto o nullo, quella riga viene direttamente cancellata poiché non c’è informazione.

• → → Ordinare per numero fare tasto dx sulla selezione ordina (e poi selezionare come ordinare).

La pivot crea una propria copia della tabella originaria, crea una sorta di copia che sta sotto la pivot, di conseguenza nel momento che vengono modificati i dati sulla tabella originaria bisogna fare il refresh della → pivot facendo: tasto dx sulla pivot aggiorna. Il fatto che non aggiorni automaticamente i dati è utile per fare confronti.

La pivot può diventare gli estremi della tabella: o rappresenta solo 1 cella o tutta la tabella. Questo avviene a seconda dei filtri che seleziono per la visualizzazione (se li seleziono tutti vedo tutta la tabella).

Riquadro filtri della Pivot:

• Il Riquadro filtri è utile per tenere i dati in modo gestibile. Ad esempio, se metto la serie nei filtri posso selezionare la serie che voglio, togliendo quelli che non mi servono. Il riquadro filtro permette di evidenziare quello che mi serve! In questo modo restringo la ricerca su quello che mi serve, restando gestibile.
• N.B! I filtri del pannello filtri vanno però a togliere quei dati sul totale del report e quindi il totale è influenzato dai filtri. Ovvero, deselezionando dei dati da questi filtri il risultato viene modificato nei calcoli e quindi è sensibile a questi filtri!
• Posso aggiungere un “elenco visivo” dei dati (o slicer) della Pivot riguardante i filtri, in modo che → siano più veloci da selezionare e graficamente migliori. Basta fare click dx su quello che mi serve → come filtro poi aggiungi come filtro dei dati. In questo modo creo una “DASHBOARD” per ogni tipo di filtro dato.

IMPORTANTISSIMO--> facendo doppio click su un dato della pivot aprirà un'altra pagina con tutti i dati che compongono quel determinato dato ottenuto nella pivot (le mette separatamente copiandole dalla tabella d’origine). Questo si chiama “Drill True”. Permette di vedere come il dato della pivot è composto (da quali dati della tabella originale deriva).

Lezione 2 - 02/03/2023

Riquadri delle pivot (come funzionano):

• Riga: generiamo elenchi distinti (privi di ripetizioni) ogni riga.
• Colonna: generiamo un elenco in orizzontale (privi di ripetizioni, distinti).
• Valori: per fare operazioni (ma se si mettono colonne testuali dà il conteggio delle celle piene).
• Filtri: applichiamo dei filtri, che sono validi anche per il totale, ovvero modificano il totale, agiscono anche su di esso (posso mettere anche filtri visivi per selezionare).

Prima si settano i filtri, poi i calcoli, in modo da individuare quelli sono i calcoli che deve eseguire. Se metto product Plant nella casella “valori” ottengo il conteggio delle caselle piene (quindi NON conta le caselle vuote).

Se forzo una colonna non numerica nel quadrato “valori”, Excel somma le celle piene, quindi non conta le celle vuote, di conseguenza non è un buon modo per contare le celle totali. Inoltre, appunto non va a contare i valori in modo distinto (è un errore se voglio sapere quanti valori distinti ci sono, per esempio per il numero di stabilimenti).

Se invece metto “sales amount” nelle righe, Excel dà un elenco distinto di tutti i valori di sales Amount (il che non ha senso), quindi aggrega i valori che sono uguali. Quindi Excel aggrega testi e aggrega numeri se vengono messi su righe e colonne.

Funzione “Campo Calcolato”

Per fare la media ponderata serve un calcolo prendendo il valore da più di una colonna: se infatti faccio fare la media dei prezzi (inserendo i prezzi nel riquadro “valori”) questa non sarà corretta, poiché non pondera i prezzi, ma fa solamente la media dei valori della colonna. Al contrario per la media ponderata bisogna considera le quantità vendute dei bene, di conseguenza si trova facendo la divisione tra le vendite totali e il numero di ordini.

Allora utilizzo “campo calcolato” che permette di generare un calcolo utilizzando più caselle (prende i dati dalla tabella di base). Cliccare sulla pivot nella zona di calcolo (non sui nomi) --> celle --> inserisci --> inserisci campo calcolato --> inserisci la formula (sales amount/order quantity) [in realtà bisogna fare la somma di sales amount, la somma di order quantity, e poi la divisione, poiché quelle due sono colonne, non numeri di per se] N.B! “campo calcolato” non aggiunge nessuna colonna, è semplicemente per fare un calcolo su più colonne insieme.

Per vedere che calcolo c’è dietro al “campo calcolato” clicca --> celle --> inserisci --> campo calcolato --> selezionare il “nome” interessato, quello che avevi già creato. Ogni cella della pivot è indipendente dalle altre colonne, ogni colonna lavora per sé: ad esempio se metto in “valori” della pivot una colonna, questa fa i calcoli solo su quella colonna (somma, media ecc), al contrario se devono fare calcoli con più di una colonna che devo utilizzare “campo calcolato”.

Join tra tabelle

Se es. voglio vedere una divisione per country devo andare a prendere altri dati da un altro database. [In questo caso country si intende del cliente, è una proprietà del cliente.] Serve quindi fare una join di tabelle (porta i dati da una tabella ad un’altra) tra:

• Pagina “Sales”: è una tabella transazionale o dei fatti. Ogni riga segnala un evento, un qualcosa che è accaduto, segnala tutte le transazioni fatte.
• Pagina “Customer”: è una tabella anagrafica. Da informazione sul cliente, è una tabella di entità di business. Rappresenta in maniera astratta un concetto di business in una tabella, il cliente. Questa tabella dà gli attributi del cliente (sono tutte le info al suo interno).

Per fare l’associazione serve una colonna in comune che permette di matchare il dato ricercato. → Per fare la join devo usare la funzione “cerca.vert”. Crea una nuova colonna, poi nella prima cella = cerca.vert e compila i campi:

1. Valore è il parametro in comune tra le tabelle, in questo caso è customer number (numero identificativo del cliente).
2. Matrice_tabella è dove voglio cercare, ovvero la pagina e la colonna dove Excel deve cercare. Selezionare prima la pagina e poi la tabella dal punto di inizio fino dove mi serve (da sopra seleziona tutte le colonne), dove il primo punto di partenza è la colonna in comune ad entrambe le tabelle.
3. Indice, inserire il numero di colonna del dato che voglio trasportare. N.B! Conta a partire dalla colonna di partenza della selezione (che deve contenere il parametro in comune).
4. Scrivi FALSO, altrimenti di default metterebbe “vero” e non va bene (con vero trova anche risultati simili, con falso trova i risultati esattamente uguali al parametro richiesto).

→ Per estendere il calcolo a tutta la colonna sulla prima tabella quando appare la croce nera nell’angolo (vai col mouse nell’angolo) faccio doppio clic. In questo modo replica il calcolo a tutta la colonna. N.B! la join così però è molto dispendiosa a livello computazionale se vi sono molti dati, deve fare un matching importante. N.B! il valore (parametro) in comune, deve restare sempre a sinistra rispetto al dato che si vuole trasportare, altrimenti se il parametro di ricerca è a destra dà un errore.

Funzione cerca.x

È una funzione similissima a cerca.vert ma migliorata. Il primo dato che selezioni è il valore che vuoi cercare, poi seleziona solo la colonna dove fare la ricerca e poi solo la colonna del dato che vuoi “trasportare”. È NECESSARIO che la colonna da dove prendo i dati (customer), ovvero i dati in comune, DEVE essere primaria, ovvero non deve avere valori duplicati, altrimenti Excel prende il primo valore che trova e usa quello, senza tenere conto degli altri.

Chiave primaria: colonna di una tabella con valori non nulli e non duplicati (univoci).

Verificare se una colonna è una “Chiave Primaria”

Bisogna verificare prima se la colonna da cui prelevo i dati per la join ha dati univoci (in sales invece non serve siano univoci). Per verificare se una tabella è primaria basta fare una Pivot --> metto la colonna che voglio verificare sia nel quadrato “righe” sia in “valori” (con la somma) --> poi ordino dal più grande al più piccolo --> in questo modo ottengo in righe le colonne accorpate e la somma delle celle con quel valore: se la somma è 1 vuol dire che c’è uno solo di quel valore, ovvero è UNIVOCO. Ad esempio, per il product, non è univoco poiché è prodotto in stabilimenti diversi, con dei costi unitari diversi: di conseguenza ci sono più costi unitari.

Concatenamento colonne: creare una “Chiave Primaria”

Posso creare una colonna dove concateno 2 colonne (le unisco) –> utilizzo “&” tra due celle (poi espando a tutta la colonna) es. (=B2&A2). In questo modo creo una colonna primaria tramite l’unione dei valori nelle celle. Fatto questo posso replicare in modo IDENTICO con la funzione cerca.vert questa Primary Key anche nella colonna Sales (dove devo fare il cerca vert). In questo modo si ottiene la colonna da utilizzare come parametro di ricerca per fare la join di tabelle.

Calcolo margine di contribuzione dei prodotti

Margine di contribuzione = vendite – costi variabili (dei prodotti). Se questo margine è negativo vuol dire che non riesco a coprire neanche i costi variabili, di conseguenza neanche quelli fissi. → Se anche il margine di contribuzione è positivo, ma i costi fissi > margine di contribuzione siamo in perdita. N.B! Pct=percentuale. Margine di contribuzione %= margine di contr