Basi di dati per il supporto alle decisioni Data Warehouse ed OLAP E’ la rovina del business moderno: troppi dati, poche informazioni. (John W. Verity) - BusinessWeek 1997 It's the bane of modern business: too many data, not enough information. Computers are everywhere, accumulating gigabytes galore. Yet it only seems to get harder to find the forest for the trees--to extract significance from the blizzard of numbers, facts, and stats. http://www.businessweek.com/1997/05/b3512127.htm Motivazioni I sistemi informatici permettono di aumentare la produttività delle organizzazioni automatizzandone la gestione operativa quotidiana Questi dati — se opportunamente accumulati e analizzati — possono essere utilizzati per la pianificazione e il supporto alle decisioni Una corretta gestione dei dati storici può essere occasione di un grande vantaggio competitivo … ma quali sono i tipi di dati (e di utilizzo) in azienda … Basi di dati: OLAP 21 December, 2015 - slide 2 Utilizzo aziendale della base di dati Gli utenti di un database aziendale possono essere suddivisi in due grandi categorie: Utenti amministrativi, che giornalmente accedono al database, interessati all’interrogazione, inserimento, aggiornamento e cancellazione di un ristretto numero di record (On Line Transaction Processing). Management, che su base periodica, interroga una notevole quantità di dati, generando diverse tipologie di aggregazioni, onde ottenere report su cui basare le proprie decisioni (On Line Analytical Processing) . Basi di dati: OLAP 21 December, 2015 - slide 3 Sistemi di supporto alle decisioni I sistemi che agevolano il management nelle decisioni aziendali (scelte strategiche ed operative), sono chiamati: DSS – Decision Support System EIS – Executive Information systems Settori tipici di impiego: Marketing (migliorare il targeting delle promozioni e campagne commerciali) Customer Services (individuare i servizi di supporto e recommendation del prodotto) Customer Retention (identificare i pattern che portano alla perdita del cliente) Risk Assessment, Frau Detection (identificare i pattern a maggior rischio e quelli sospetti) Entrambi i sistemi attingono informazioni dal patrimonio di dati e conoscenze (anche eterogenee) accumulate dall’azienda, attraverso un processo di ANALISI dei dati stessi (O.L.A.P.). Basi di dati: OLAP 21 December, 2015 - slide 4 OLAP : On Line Analytical Processing Il temine OLAP fu definito da Codd nel 1993 [Codd, EF 1993. “Providing OLAP (On-line Analytical Processing) to UserAnalysts”] Gli OLAP sono processi orientati alla ANALISI (non dunque alla gestione) delle informazioni. Forniscono supporto al DSS ed allo ESS, offrendo una analisi multidimensionale delle informazioni. Necessitano, per una loro effettiva efficacia, della disponibilità di massicce raccolte dati su cui effettuare le analisi: I Data Warehouse ( letteralmente “deposito/magazzino di dati”). Basi di dati: OLAP 21 December, 2015 - slide 5 Sistemi OLTP e OLAP OLTP Numerose, ma elementari operazioni di inserimento, modifica e cancellazione di singoli record Basi di dati: OLAP OLAP Rare, ma pesanti e complesse interrogazioni su grandi quantità di dati eterogenei 21 December, 2015 - slide 6 Differenze tra processi OLTP ed OLAP Dunque: Gli OLTP (On Line Transaction Processing) sono processi orientati alla gestione (transazioni) delle informazioni (insert, update, delete). Sono detti anche processi o DataBase operazioniali. Gli OLAP (On Line Analytical Precessing) sono processi orientati alla sola analisi (interrogazione) delle informazioni, ed eventualmente al loro accumulo ulteriore. Questo determina profonde differenze nelle caratteristiche che devono offrire, ma anche nei requisiti di risorse che necessitano. Basi di dati: OLAP 21 December, 2015 - slide 7 Differenze tra processi OLTP ed OLAP Progettazione Funzione Frequenza Dati contenuti nel DB Sorgente dei dati Accesso tipico Flessibilità accesso Numero record acceduti Tipo utenti Numero utenti Dimensione DB Basi di dati: OLAP OLAP OLTP Orientata all’analisi e ai report Orientata alle transazioni Supporto alle decisioni Gestione amministrativa Periodica Giornaliera Storici, riassuntivi DB multiple Mutevoli, dettagliati Singola DB Read, (periodicamente append) Linguaggio SQL / proprietario 10E3 - 10E6 Manager Read/write/delete Linguaggio SQL 10 Amministrativi Centinaia 100 GB – 1 TB Migliaia 100 MB – 1 GB 21 December, 2015 - slide 8 Separazione degli ambienti I requisiti sono quindi contrastanti Le applicazioni dei due tipi possono danneggiarsi a vicenda Basi di dati: OLAP OLTP OLAP Base di dati Data Warehouse APPLICAZIONE OLTP APPLICAZIONE OLAP UTENTI FINALI (Transazioni) ANALISTI (Query complesse) 21 December, 2015 - slide 9 Sistemi OLTP RDBMS Basi di dati: OLAP 21 December, 2015 - slide 10 ERP: processi interni Cos’è: ENTERPRISE resource planning Un singolo SW che integra tutti i dipartimenti No planning di risorse L’integrazione, se realizzata correttamente, crea benefici enormi per l’azienda ERP migliora i processi interni ed esterni Quanto dura un progetto ERP Basi di dati: OLAP Di media 2-3 anni, di solito si sbaglia nella pianificazione dei tempi Modifica dei processi ‘pre ERP’ Non importa quanto tempo ma importa imparare ad usarlo correttamente 21 December, 2015 - slide 11 Basi di dati: OLAP 21 December, 2015 - slide 12 Data Warehouse Definizione di Data Warehouse W.H. Immon presenta per primo il termine/concettto di Data Warehouse in “Building the Datawarehouse” (1992) : “il data warehouse è una raccolta di dati: integrata permanente, focalizzata su un argomento variabile nel tempo, che può fornire supporto alle decisioni di gestione.” Grande dimensione ed integrazione dei dati contenuti nel DW, permettono una analisi multidimensionale dei fattori strategici del business aziendale Basi di dati: OLAP 21 December, 2015 - slide 16 DataWarehouse e viste 1 Ad un primo esame, l’analisi (multidimensionale) dei dati tramite DW potrebbe anche essere considerata come una “estensione” del concetto di vista. Infatti: entrambi sono estratti da basi di dati OLTP e focalizzati su uno specifico argomento. entrambi sono costituiti da dati in sola lettura (le view possono in certi casi essere anche aggiornabili...) Tuttavia..i Data Warehouse presentano specifiche differenze: Basi di dati: OLAP 21 December, 2015 - slide 17 DataWarehose e viste 2 ….i Data Warehouse: Richiedono una memorizzazione persistente invece di essere materializzati a richiesta (ottimizzazione della performance). Tipicamente sono multidimensionali, mentre le viste sono relazionali. Sono indicizzati per ottimizzare le prestazioni, mentre le viste non possono esserlo indipendentemente dalle basi di dati sottostanti. Gestiscono grandi quantità di dati temporali, spesso aggregati, più grandi di quanto ne siano contenuti normalmente nella base dati originaria, mentre le viste sono un estratto e quindi un sottoinsieme delle medesime. Forniscono funzionalità specifiche: Rollup (aggregazione), Drilldown (estrazione), Pivot (rotazione) , slice & Dice (ritaglio) Basi di dati: OLAP 21 December, 2015 - slide 18 Caretteristiche dei Data Warehouse (1) E’ possibile evidenziare alcune proprietà caratteristiche dei dati contenuti nei DW. In un Data Warehouse i dati contenuti sono: Basi di dati: OLAP Dati Consolidati e Consistenti. I DW acquisiscono dati da diversi DB potenzialmente eterogenei, e assicurano nel consolidarli una nomenclatura, misura, codifica e semantica consistente Dati Subject Oriented I DW sono orientati al supporto del business (business related function). I DW prelevano dai DB operazionali e consolidano soltanto quelle informazioni indicate come strategiche per il business, tralasciando le altre 21 December, 2015 - slide 19 Caretteristiche dei Data Warehouse (2) Dati Storici I DW archiviano dati storici, che si ripetono, per eseguire confronti, previsioni e per individuare tendenze. I dati riguardano un periodo pluriennale (anche decennale) a differenza delle basi dati transazionali (OLTP) che gravitano invece in ogni momento attorno al valore corrente. L’orizzonte temporale di interesse è dell’ordine degli anni Dati aggregati Gli strumenti decisionali non sono interessati al “chi” ma al “quanto”, al “medio”, al “min” … I DW offrono la possibilità di ottenere aggregazioni a diversi livelli, sia temporali che su altre dimensioni, agevolando così il processo di formazione delle decisioni Basi di dati: OLAP Le operazioni di aggregazione sono quindi fondamentali nel warehousing e nella costruzione/mantenimento di un data warehouse. 21 December, 2015 - slide 20 Caretteristiche dei Data Warehouse (3) Dati Read only Il DW è una base dati “fuori linea” in cui l’accesso ai dati avviene esclusivamente in lettura;l’aggiornamento del DW è periodico (tipicamente notturno riguarda milioni di records, a differenza dei “pochi” record coinvolti nei normali processi OLTP). Dati integrati I dati di interesse provengono da tutte le sorgenti informative — ciascun dato proviene da una o più di esse Il data warehouse rappresenta i dati in modo univoco — riconciliando le eterogeneità dalle diverse rappresentazioni su Basi di dati: OLAP nomi struttura codifica rappresentazione multipla 21 December, 2015 - slide 21 Caretteristiche dei Data Warehouse (4) Manutenzione separata della base dati relazionale transazionale Diversi motivi: non esiste un’unica base di dati operazionale che contiene tutti i dati di interesse la base di dati deve essere integrata non è tecnicamente possibile fare l’integrazione in linea i dati di interesse sarebbero comunque diversi devono essere mantenuti dati storici e aggregati Basi di dati: OLAP l’analisi dei dati richiede per i dati organizzazioni speciali e metodi di accesso specifici degrado generale delle prestazioni senza la separazione 21 December, 2015 - slide 22 Data Warehouse: tipologie e dimensioni In base alla loro dimensione ed impiego, è possibile individuare inoltre: Data warehouse a livello di impresa ( si tratta di grandi infrastrutture che richiedono un massiccio investimento di tempo e di risorse) Data warehouse virtuali , forniscono viste su basi di dati operative che sono materializzate per fornire un accesso efficace Datamart, sono aggregazioni di dati rivolte ad una sotto organizazione aziendale, tipicamente un dipartimento o un reparto. Basi di dati: OLAP 21 December, 2015 - slide 23 Architettura per il data warehousing Metadati Sorgenti esterne Basi di dati operazionali Analisi dimensionale Data Warehouse Data mining Data Mart Sorgenti dei dati Basi di dati: OLAP Strumenti di analisi 21 December, 2015 - slide 25 Sorgenti informative i sistemi operazionali dell’organizzazione sono sistemi transazionali (OLTP) orientati alla gestione dei processi operazionali non mantengono dati storici ogni sistema gestisce uno o più soggetti (ad esempio, prodotti o clienti) sono spesso sistemi “legacy” sorgenti esterne Basi di dati: OLAP ad esempio, dati forniti da società specializzate di analisi 21 December, 2015 - slide 26 Alimentazione del data warehouse Attività necessarie ad alimentare un data warehouse estrazione — accesso ai dati nelle sorgenti pulizia —rilevazione e correzione di errori e inconsistenze nei dati estratti trasformazione —trasformazione di formato, correlazione con oggetti in sorgenti diverse caricamento — con introduzione di informazioni temporali e generazione dei dati aggregati I metadati sono informazioni mantenute a supporto di queste attività: "Dati sui dati": descrizioni logiche e fisiche dei dati (nelle sorgenti e nel DW) corrispondenze e trasformazioni dati quantitativi Spesso sono non dichiarativi e immersi nei programmi Basi di dati: OLAP 21 December, 2015 - slide 27 Data Warehouse Server Sistema dedicato alla gestione warehouse Può basarsi su diverse tecnologie ROLAP i dati sono memorizzati in DBMS relazionali (schemi a stella) MOLAP I dati sono memorizzati in forma multidimensionale tramite speciali strutture dati tipicamente proprietarie Basi di dati: OLAP Misto i produttori di RDBMS stanno iniziando a fornire estensioni OLAP ai loro prodotti 21 December, 2015 - slide 28 Strumenti di analisi Consentono di effettuare analisi dei dati utilizzando il Data Warehouse server e offrono interfacce amichevoli per presentare, in forma adeguata e facilmente comprensibile, i risultati delle analisi Due principali tipologie di analisi (e quindi di strumenti) Basi di dati: OLAP Analisi multidimensionale Data mining 21 December, 2015 - slide 29 I DataMart Un datamart può essere visto come un sottoinsieme del contenuto di un DW, memorizzato in un propria struttura DB. I Data marts contengono informazioni orientate ad uno specifico dipartimento o specifica area di business. Dato che il volume dei dati è inferiore a quello del DW, I dati possono esistere sia a livello di dettaglio che di aggregazione, pur rimanendo i processi di query superiori in velocità rispetto al DW. Basi di dati: OLAP 21 December, 2015 - slide 30 Caratteristiche dei Data Mart Implementazione facile e veloce, rispetto ai DW costo di implementazione inferiore al DW soddisfacimento di esigenze specifiche del dipartimento tempi di accesso e risposta più veloci rispetto al DW Il DW può essere costruito a partire dai vari Data Mart (bottom-up) oppure è possibile ricavare i singoli DataMart dall’unico DW (top-down). un data mart rappresenta un progetto fattibile la realizzazione diretta di un data warehouse completo non è invece solitamente fattibile Nell’approccio top-down si avranno tuttavia ulteriori costi: Basi di dati: OLAP per maggiori risorse hardware & software per popolare singolarmente e regolarmente ciascun datamart per assicurare la consistenza globale tra tutti i datamart Network access se presenti in aree geografiche distinte 21 December, 2015 - slide 31 Variante dell’architettura Monitoraggio & Amministrazione Metadati Sorgenti esterne Analisi dimensionale Basi di dati operazionali Data mining Sorgenti dei dati Basi di dati: OLAP Data Mart Strumenti di analisi 21 December, 2015 - slide 32 Data Warehouse Data Mart Indipendenti Stipendi SDO Pianificazione Personale Acquisti Posti Letto Patrimonio Referti Ricoveri Anagrafica Personale DRG Economato Contabilità Independent Data Marts Dati esterni Basi di dati: OLAP 21 December, 2015 - slide 33 Data Warehouse Data Mart Indipendenti Stipendi SDO Personale Pianificazione Acquisti Posti Letto Referti Patrimonio Ricoveri Anagrafica Personale DRG Enterprise Data Warehouse Economato Contabilità Dati esterni Basi di dati: OLAP Independent Data Marts 21 December, 2015 - slide 34 Data Warehouse Approccio evolutivo Stipendi SDO Pianificazione Personale Acquisti Posti Letto Patrimonio Referti Ricoveri Anagrafica DRG Enterprise Data Warehouse Personale Economato Contabilità Dati esterni Basi di dati: OLAP 21 December, 2015 - slide 35 Operazioni su dati multidimensionali Slice & dice — seleziona e proietta Roll up (o drill up)— aggrega i dati volume di vendita totale dello scorso anno per categoria di prodotto e regione Drill down — disaggrega i dati per una particolare categoria di prodotto e regione, mostra le vendite giornaliere dettagliate per ciascun negozio (Pivot — re-orienta il cubo) Database dimensionali NON esistono operazioni di: inserimento modifica cancellazione NON esiste normalizzazione del database, anzi sono ammesse ridondanze la struttura è ottimizzata per consentire operazioni di ricerca ad alte prestazioni Basi di dati: OLAP 21 December, 2015 - slide 37 Dimensioni e gerarchie di livelli regione anno provincia trimestre categoria marca mese città prodotto negozio Luogo Basi di dati: OLAP Articolo giorno Tempo 21 December, 2015 - slide 38 Database dimensionali tempo_id anno trimestre mese giorno negozio_id nome città stato TEMPO PRODOTTO VENDITA tempo_id prodotto_id negozio_id cliente_id quantità prezzo totale cliente_id nome tipo_cliente NEGOZIO Basi di dati: OLAP prodotto_id nome_prod categoria casa_prod CLIENTE 21 December, 2015 - slide 40 Database dimensionali tempo_id anno trimestre mese giorno DIMENSION TABLE negozio_id nome città stato TEMPO PRODOTTO VENDITA tempo_id prodotto_id negozio_id cliente_id quantità prezzo totale DIMENSION TABLE Basi di dati: OLAP DIMENSION TABLE cliente_id nome tipo_cliente FACT TABLE NEGOZIO prodotto_id nome_prod categoria casa_prod CLIENTE DIMENSION TABLE 21 December, 2015 - slide 41 Data Cube Le dimensioni sono gli attributi attraverso i quali si vogliono compiere le analisi, ed ogni dimensione può comprendere delle gerarchie: Prodotto (Tipo prodotto, categoria), Tempo (anno, mese, giorno) Anno Mercato Prodotto Basi di dati: OLAP 21 December, 2015 - slide 42 Data Cube Anno Mercato Prodotto Basi di dati: OLAP 21 December, 2015 - slide 43 Data Cube Prodotto1 Prodotto2 Prodotto Mercaton Prodotto Mercato2 Mercato1 n Anno Anno 1 Basi di dati: OLAP 2 Mercato Anno Anno n 21 December, 2015 - slide 44 Le celle del cubo contengono i valori da analizzare relativi alle dimensioni che le individuano Data Cube Prodotto1 Prodotto2 Prodotto Mercaton Prodotto Mercato2 Mercato1 n Anno Anno 1 Basi di dati: OLAP 2 Mercato Anno Anno n 21 December, 2015 - slide 45 Data Cube Venduto Numero ordini Fatturato … Prodotto1 Prodotto2 Prodotto Prodotto Mercato k Anno k k Mercaton Prodotto Mercato2 Mercato1 n Anno Anno 1 Basi di dati: OLAP 2 Mercato Anno Anno n 21 December, 2015 - slide 46 Prodotto Mercato k Anno Prodotto k Anno k Mercato k Pivoting Annok Mercato Prodotto k k k k Pivoting (fare perno), per selezionare due dimensioni attraverso le quali aggregare i valori da analizzare Slice and dice (affettare e ritagliare a cubetti), per selezionare e proiettare i dati riducendo le dimensioni Ranking (attribuire una classe di merito), per ordinare i dati secondo diversi criteri Basi di dati: OLAP 21 December, 2015 - slide 47 Esempio di Pivoting Prodotto1 Prodotto2 Prodotto Mercaton Mercato2 Mercato1 Prodotto n Mercato Anno Basi di dati: OLAP 21 December, 2015 - slide 48 Esempio di Pivoting (2) Prodotto1 Prodotto2 Prodotto Mercaton Mercato2 Mercato1 Prodotto n Mercato Anno Basi di dati: OLAP 21 December, 2015 - slide 49 Esempio di Pivoting (3) Prodotto1 Prodotto2 Prodotto Mercaton Mercato2 Mercato1 Prodotto n Mercato Anno Basi di dati: OLAP 21 December, 2015 - slide 50 Rappresentazione multidimensionale dei dati Luogo (negozio) Milano-2 Milano-1 Roma-2 Roma-1 Lettori DVD Televisori Quantità Lettori CD Articolo (prodotto) Videoregistratori 1 trim. 2003 2 trim. 2003 3 trim. 2003 4 trim. 2003 Tempo (trimestre) Basi di dati: OLAP 21 December, 2015 - slide 51 Slice and dice Il manager regionale esamina la vendita dei prodotti in tutti i periodi relativamente ai propri mercati Il manager finanziario esamina la vendita dei prodotti in tutti i mercati relativamente al periodo corrente e quello precedente Luogo Articolo Tempo Il manager di prodotto esamina la vendita di un prodotto in tutti i periodi e in tutti i mercati Basi di dati: OLAP Il manager strategico si concentra su una categoria di prodotti, una area e un orizzonte temporale 21 December, 2015 - slide 52 Risultato di Slice and dice Basi di dati: OLAP 21 December, 2015 - slide 53 Esempio di Roll-up e Drill-Down Prodottok Mercatok Annok Drill-Down Le tipiche operazioni (OLAP) che si eseguono sul Data Cube sono: •Drill down (perforare), per aumentare il livello di dettaglio dei dati •Roll up (accumulare), per aumentare il livello di aggregazione dei dati Roll-Up Prodottok Mercatok Mese1 Mese2 Basi di dati: OLAP Mesen 21 December, 2015 - slide 54 Roll-up Basi di dati: OLAP 21 December, 2015 - slide 55 Altra operazione di roll-up Basi di dati: OLAP 21 December, 2015 - slide 56 Visualizzazione dei dati I dati vengono infine visualizzati in veste grafica, in maniera da essere facilmente comprensibili. Si fa uso di: Basi di dati: OLAP tabelle istogrammi grafici torte superfici 3D bolle … 21 December, 2015 - slide 57 Visualizzazione finale di un’analisi 1000 900 800 700 600 500 400 300 200 4 trim.2003 100 3 trim.2003 0 2 trim.2003 Lettori DVD 1 trim.2003 Televisori Lettori CD Videoregistratori Basi di dati: OLAP 21 December, 2015 - slide 58 Il design di un Data Warehouse Data Warehouse 1) Modello concettuale: fatti e dimensioni 2) Modello logico: database N-dimensionale 1) Analisi Concettuale: fatti e dimensioni I DW servono a focalizzare l’attenzione sui fatti strategici del business aziendale, legati al loro contesto tramite le loro dimesioni. Fatti: sono l’oggetto primario di interesse del business. Sono espressi (quasi) sempre tramite una misura numerica. Esempi: numero di prodotti venduti, valore degli incassi, valore dei costi, numero di abitanti, numero di contatti,… (eccezione numerica: evento si/no) Dimensioni: abbinano ai fatti una precisa dimensione di tempo, spazio, tipologia, categoria, …. Sono espressi tipicamente da un attributo testuale. Basi di dati: OLAP 21 December, 2015 - slide 60 Caratteristiche identificative dei fatti Additività dei fatti Caratteristica dei fatti è quella di poter essere assommati. Tale additività può essere: totale, se sono sommabili in ogni loro dimensione; es: incassi. parziale, se sono sommabili solo rispetto a certe dimensioni.es: voci di bilanci annuali. (tuttavia: somma saldo di più periodi / totale periodi = saldo medio = intensità) Una dimensione può talvolta assumere un aspetto numerico, facendo dubitare della sua natura; tuttavia, quando il suo valore non varia nel tempo è da ritenersi un attributo di dimensione. Basi di dati: OLAP 21 December, 2015 - slide 61 Caratteristiche specifiche delle dimensioni Gerarchia delle dimensioni Caratteristica delle dimensioni è quella di presentare spesso una organizzazione gerarchica (temporale, geografica, tipologica,….). Questo permette di operare delle aggregazioni, assommando i valori dei fatti, secondo le gerarchie oggetto di interesse. (vendite per regione, costi per reparto, consumi per tipologia familiare, …) Basi di dati: OLAP 21 December, 2015 - slide 62 2) DataWarehouse: modello dimensionale Nei database destinati all’OLTP, i modelli logici utilizzati sono spesso il relazionale, il gerarchico, il reticolare,. .. Nei Data Warehouse, il modello utilizzato logico è quello dimensionale. Nei database dimensionali si collegano i fatti con le loro dimensioni. Basi di dati: OLAP 21 December, 2015 - slide 63 Modello logico del db dimensionale I DB dimensionali sono generalmente realizzati secondo due schemi: Lo schema a Stella (Star Schema) Lo schema a Fiocco di Neve (Snowflake Schema) Basi di dati: OLAP 21 December, 2015 - slide 64 DB Dimensionale: Schema a Stella Nello schema a stella, si crea una tabella centrale (Fact Table) per ogni fatto oggetto di interesse del business (vendite, costi, addetti..). Tale tabella è collegata a ciascuna delle sue dimensioni (Dimension tables), realizzando una topologia a stella. La tabella centrale si trova generalmente in 3FN: contiene i valori di “misura” (unità vendute, prezzo unitario,..), più tutte le FK per collegarla alle tabelle dimensionali. Le tabelle periferiche sono invece poste in 2FN: sono “denormalizzate” onde ridurre il numero di join necessari per accedere alle informazioni cercate. Basi di dati: OLAP 21 December, 2015 - slide 65 Esempio di Schema a Stella Basi di dati: OLAP 21 December, 2015 - slide 66 Schema a Fiocco di neve (Snowflake) La tipologia a fiocco di neve è una variante di quella a stella. Ulteriori tabelle dimensionali sono aggiunte, non direttamente alla tabella centrale, ma alle tabelle dimensionali. In questo schema, anche le tabelle dimensionali “interne” sono normalizzate in 3FN, presentando ciascuna delle FK a dimensioni esterne ulteriori. Basi di dati: OLAP 21 December, 2015 - slide 67 Esempio di Schema a fiocco di Neve Basi di dati: OLAP 21 December, 2015 - slide 68 Confronto tra Fact e Dimension Tables Fact Tables: n° righe elevatissimo (10E6-10E9) n° di campi ridotto Dati principalmente numerici Molteplici chiavi esterne (FK) alle tabelle dimensionali Sono tabelle “strette e lunghe” (ridurre al max i campi) Dimension Tables n° di righe contenuto (10E2-10-3) Numero di campi elevato, per gestire le gerarchie Dati testuali o temporali Chiave primaria dimensionale Sono tabelle “larghe e corte” (ridurre al max le righe) Basi di dati: OLAP 21 December, 2015 - slide 69 Considerazioni sugli Schemi a Stella e a Fiocco di neve Lo schema a fiocco di neve è ottimizzato per occupare meno spazio. Ottimale in presenza di DW enormi Fattorizzando maggiormente, impiega più tempo tempo per risolvere i join. Inoltre, impedisce l’utilizzo di indici bitmapped, a discapito delle prestazioni di ricerca. Basi di dati: OLAP 21 December, 2015 - slide 70 Schema a stella Tempo CodiceTempo Giorno Mese Trimestre Anno Luogo CodiceLuogo Negozio Indirizzo Città Provincia Regione Basi di dati: OLAP Vendite CodiceTempo CodiceLuogo CodiceArticolo CodiceCliente Quantità Incasso Articolo CodiceArticolo Descrizione Marca CodiceCategoria Categoria Cliente CodiceCliente Nome Cognome Sesso Età Professione 21 December, 2015 - slide 71 Una possibile istanza Basi di dati: OLAP 21 December, 2015 - slide 72 Caratteristiche di uno schema dimensionale Una tabella dimensione memorizza i membri di una dimensione la chiave primaria è semplice gli altri campi memorizzano i livelli della dimensione tipicamente denormalizzata La tabella fatti memorizza le misure (fatti) di un processo Basi di dati: OLAP la chiave è composta da riferimenti alle chiavi di tabelle dimensione gli altri campi rappresentano le misure è in BCNF 21 December, 2015 - slide 73 Additività dei fatti Un fatto è additivo se ha senso sommarlo rispetto a ogni possibile combinazione delle dimensioni da cui dipende Basi di dati: OLAP l’incasso è additivo perché ha senso calcolare la somma degli incassi per un certo intervallo di tempo, insieme di prodotti e insieme di negozi l’additività è una proprietà importante, perché le applicazioni del data warehouse devono solitamente combinare i fatti descritti da molti record di una tabella fatti 21 December, 2015 - slide 74 Formato delle interrogazioni di roll-up Le interrogazione assumono solitamente il seguente formato standard SELECT D1.L1,.., Dn.Ln, Aggr1(F.M1),.., Aggrk(F.Ml) FROM Fatti as F, Dimensione1 as D1, .., DimensioneN as Dn WHERE Join-predicate(F,D1) and .. and Join-predicate(F,Dn) and selection-predicate GROUP BY D1.L1, ..., Dn.Ln ORDER BY D1.L1, ..., Dn.Ln Basi di dati: OLAP 21 December, 2015 - slide 75 Esempio SELECT A.Categoria, T.trimestre, sum(V.Quantita) FROM Vendite as V, Articolo as A, Tempo as T WHERE V.CodiceArticolo = A.CodiceArticolo and V.CodiceTempo = T.CodiceTempo and T.Anno = 2003 GROUP BY A.Categoria, T.trimestre ORDER BY A.Categoria, T.trimestre Basi di dati: OLAP 21 December, 2015 - slide 76 Data cube SELECT Citta, Categoria, count(Quantita) as VenditeCC FROM Vendite as V, Articolo as A, Luogo as L WHERE V.CodiceArticolo = A.CodiceArticolo and V.CodiceLuogo = L.CodiceLuogo GROUP BY CUBE(Citta, Categoria) Basi di dati: OLAP 21 December, 2015 - slide 77 Possibile risultato del data cube Basi di dati: OLAP 21 December, 2015 - slide 78 Group by roll up SELECT Citta, Categoria, count(Quantita) as VenditeCC FROM Vendite as V, Articolo as A, Luogo as L WHERE V.CodiceArticolo = A.CodiceArticolo and V.CodiceLuogo = L.CodiceLuogo GROUP BY ROLLUP(Citta, Categoria) Basi di dati: OLAP 21 December, 2015 - slide 79 Possibile risultato Basi di dati: OLAP 21 December, 2015 - slide 80 Esempio: Creazione di un Data Warehouse Passaggio da un DB relazionale (OLTP) ad uno Data Warehouse (OLAP) strutturato con tipologia Star Schema Passaggio da un DB relazionale ad uno dimensionale (Data Warehouse) Le fasi necessarie al design di un DW sono: 1- Individuazione dei fatti e delle dimensioni 2- Design delle Fact tables. 3- Design delle Dimension tables. Basi di dati: OLAP 21 December, 2015 - slide 82 Situazione iniziale OLTP Basi di dati: OLAP 21 December, 2015 - slide 83 Individuazione dei Fatti e delle Dimensioni Definizione dei Fatti: Individuare le transazioni che gestiscono i fatti chiavi del business e le relative tabelle. Definizione delle Dimensioni: individuare le entità che descrivono gli aspetti secondo i quali i fatti strategici verranno analizzati (temporali, geografici, tipologi, …..) Basi di dati: OLAP 21 December, 2015 - slide 84 Individuazione: Fact & Dimensions Basi di dati: OLAP 21 December, 2015 - slide 85 DW: Design delle Fact Tables Una volta identificare i fatti del business di interesse strategico, occorre minimizzare l’occupazione on-line (cpumemoria) e off-line (storage) del DW. A tal fine occorrerà: Basi di dati: OLAP Ridurre il numero di colonne della tabella, eliminando ogni campo superfluo ed i campi calcolati (occorre comunque fare un bilancio con il tempo speso a rigenerare in futuro i campi calcolati nelle query) Determinare il data type adatto ad ogni colonna, con la minor occupazione (con 10E6-10E9 record, anche i byte contano) Archiviare i dati storici o con livello di aggregazione diverso in tabelle differenti 21 December, 2015 - slide 86 DW: Design delle Dimension Tables E’ importante decidere quali dimensioni supporteranno le queries, e con quale granularità minima (esempio: vendita di uno specifico prodotto per regione, totale delle vendite per regione) E necessario denormalizzare in 2FN le informazioni dimensionali in singole tabelle, ciascuna contenente una sola dimensione. Si sceglie di percorrere questa strada per minimizzare il numero di joins ed aumentare le performance delle queries di ricerca. Basi di dati: OLAP 21 December, 2015 - slide 87 DW: Design delle Dimension Tables Collegamento a regione indiretto Basi di dati: OLAP 21 December, 2015 - slide 88 DW: Design delle Dimension Tables Collegamento Diretto con Regione e Data Basi di dati: OLAP 21 December, 2015 - slide 89 Popolazione di un DW Per popolare il DW con i dati proveniente da vari DB dell’azienda occorre eseguire: Validazione dei dati; i dati presenti nei vari DB possono essere espressi in varie unità di misura, valuta, tempo. Occorre unificarli prima di consolidarle. Eventuale retroimmissione dei dati corretti (backflushing) Pulizia dei dati (data scrubbing); E’ possibile che nelle diverse basi dati si siano utilizzate diverse denominazioni, sigle, abbreviazioni, o interi nomi, per identificare il medesimo soggetto. E’ possibile inoltre che esistano dati con corretti o incompleti; è necessario correggere queste anomali prima di consolidare i dati. Basi di dati: OLAP 21 December, 2015 - slide 90 Popolazione di un DW Migrazione dei dati. Prima di inserire i record nel DW, questi vanno prelevati da tutti i vari db (coerenza temporale) e copiati in un db temporaneo per essere processati prima dell’inserimento. Operazione eseguita durante l’inattività del db OLTP, onde non rallentare la normale operatività aziendale. Data Trasformation (riformattazione). A seconda delle “dimensioni” prescelte, può essere necessario trasformare/denormalizzare degli attributi. Es: data -> anno,mese,giorno,quadrimestre I,II,II, .. -> 1,2,3 campi -> campi calcolati (Totale ordine, abitanti totali,…) Basi di dati: OLAP 21 December, 2015 - slide 91 OLAP: tipi di storage MOLAP - Multi dimensional OLAP La memorizzazione avviene in forma nativa multidimensionale Alte performace dovute gli indici bitmapped (migliori in query) Necessaria Copia TUTTI I dati valido per piccole-medie dimensioni (propblema di matrici/cubi sparsi) ROLAP - Relational OLAP utilizza le funzionalità di un’engine relazionale Dati sono mantenuti nella locazione originaria Vengono memorizate le aggregazioni (viste materializzate) Valido per DB grandi e sistemi legacy HOLAP - Modello Ibrido Basi di dati: OLAP Combinazione dei due: i (molti) dati originali sono mantenuti in un DB ROLAP, nel mentre i soli dati aggregati sono inseriti in strutture MOLAP. 21 December, 2015 - slide 93 Progetttazione di un DW Progettazione di data warehouse Requisiti dell’analisi Basi di dati aziendali Altre sorgenti informative Analisi Selezione delle sorgenti informative Traduzione in un modello concettuale comune Analisi delle sorgenti informative Integrazione Integrazione di schemi concettuali Progettazione Progettazione concettuale Progettazione logica Progettazione fisica Basi di dati: OLAP 21 December, 2015 - slide 97 Dati in ingresso Le informazioni in ingresso necessarie alla progettazione di un data warehouse Basi di dati: OLAP requisiti — le esigenze aziendali di analisi descrizione delle basi di dati — con una documentazione sufficiente per la loro comprensione descrizione di altre sorgenti informative — l’analisi richiede spesso la correlazione con dati non di proprietà dell’azienda ma comunque da essa accessibili — ad esempio, dati ISTAT o sull’andamento dei concorrenti 21 December, 2015 - slide 98 Analisi delle sorgenti informative Selezione delle sorgenti informative analisi preliminare del patrimonio informativo aziendale correlazione del patrimonio informativo con i requisiti identificazione di priorità tra schemi Traduzione in un modello di riferimento attività preliminare alla correlazione e all’integrazione di schemi — si svolge meglio con riferimento a schemi concettuali Analisi delle sorgenti informative Basi di dati: OLAP identificazione di fatti (concetti su cui basare l’analisi), misure (proprietà atomiche dei fatti) e dimensioni (concetti su cui aggregare le misure) 21 December, 2015 - slide 99 Reverse engineering di schemi relazionali Il reverse engineering è l’attività di comprensione concettuale di uno schema di dati — la rappresentazione di uno schema relazionale in un modello concettuale Uno schema ER è più espressivo di uno schema relazionale Il reverse engineering di schemi relazionali è svolto in modo semiautomatico dagli strumenti di progettazione CASE Basi di dati: OLAP 21 December, 2015 - slide 100 Integrazione di sorgenti informative L’integrazione di sorgenti informative è l’attività di fusione dei dati rappresentati in più sorgenti in un’unica base di dati globale che rappresenta l’intero patrimonio informativo aziendale L’approccio è orientato alla identificazione, analisi e risoluzione di conflitti — terminologici, strutturali, di codifica Basi di dati: OLAP 21 December, 2015 - slide 101 Progettazione del data warehouse L’integrazione delle sorgenti informative ha prodotto una descrizione globale del patrimonio informativo aziendale Progettazione del data warehouse Basi di dati: OLAP concettuale — completare la rappresentazione dei concetti dimensionali necessari per l’analisi — ad esempio, dati storici e geografici logica — identificare il miglior compromesso tra la necessità di aggregare i dati e quella di normalizzarli fisica — individuare la distribuzione dei dati e le relative strutture di accesso 21 December, 2015 - slide 102 Progettazione del DW e di basi di dati multidimensionali Introduzione di elementi dimensionali nella base di dati integrata Attività identificazione di fatti, misure e dimensioni ristrutturazione dello schema concettuale rappresentazione di fatti mediante entità individuazione di nuove dimensioni raffinamento dei livelli di ogni dimensione Basi di dati: OLAP derivazione di un grafo dimensionale progettazione logica e fisica 21 December, 2015 - slide 103 Identificazione di fatti e dimensioni marca categoria codice sesso anno nascita città residenza Cliente (0,1) Articolo codice nome prezzo costo Vendita scontrino data numero pezzi incasso percentuale tempo Occupazione Basi di dati: OLAP nome Negozio nome città 21 December, 2015 - slide 104 Ristrutturazione dello schema categoria Categoria prezzo codice Articolo Marca marca costo Dati articolo nome scontrino nome Vendita Occupazione principale incasso numero pezzi codice Cliente anno nascita Giorno Negozio Mese mese data nome Trimestre trimestre sesso Residenza Città città Regione Anno anno regione E’ lo schema concettuale del data warehouse Basi di dati: OLAP 21 December, 2015 - slide 105 Progettazione fisica ROLAP: star schema per Vendita ARTICOLO CodArticolo Marca Categoria Nome CLIENTE CodCliente Sesso Occupazione Anno nascita Città nascita Provincia nascita Regione nascita VENDITA CodArticolo CodCliente CodTempo CodNegozio Incasso TEMPO CodTempo Giorno Mese Trimestre Anno NEGOZIO CodNegozio Indirizzo Città Provincia Regione Basi di dati: OLAP 21 December, 2015 - slide 106 DATA MINING Introduzione La maggior parte delle aziende dispone di enormi basi di dati Oggigiorno i database possono variare in grandezza fino all’ordine del Terabyte Queste basi di dati costituiscono una potenziale miniera di informazioni utili A questo punto ci viene spontaneo chiedere: come facciamo ad avere una estrazione di dati efficiente da una grande massa di dati. La risposta è: data mining! 1. 2. 3. 4. Basi di dati: OLAP Valorizza i dati presenti in azienda Riduce i costi Aumenta i redditi Rende più efficienti i processi aziendali 21 December, 2015 - slide 108 Data mining: definizione Traduzione data mining: estrazione da una miniera dati… In informatica è l’estrazione di informazione utile, con strumenti automatici o semiautomatici, da grandi quantità di dati al fine di scoprire strutture e regole significative. Basi di dati: OLAP 21 December, 2015 - slide 109 Cos’è il data mining? E’ ricavare informazioni di supporto alle decisioni direttamente dai dati E’ modellare il comportamento dei clienti in modo da poterlo prevedere E applicare consistentemente i risultati per mirare le attività di marketing aumentando la performance Basi di dati: OLAP 21 December, 2015 - slide 110 Data mining: come processo “Il data mining è un processo atto a scoprire correlazioni, relazioni e tendenze nuove e significative settaciando grandi quantità di dati immagazzinati nei repository, usando tecniche di riconoscimento delle relazioni e tecniche statistiche e matematiche.” (Gartner Group) Basi di dati: OLAP 21 December, 2015 - slide 111 Rivoluzione o evoluzione? Il data mining è una disciplina molto più evoluzionaria, che non rivoluzionaria L’ampia disponibilità di databases commerciali portò alla nascita del database marketing, che consentiva campagne di marketing personalizzate, più mirate alle vere esigenze dei consumatori Inoltre venne ideato il termine di “knowledge discovery in database”, ad indicare il processo generale di estrazione di conoscenza dai databases. KDD è oggi diventato sinonimo di data mining. Basi di dati: OLAP 21 December, 2015 - slide 113 Data mining: diagramma 2 I due livelli inferiori rappresentano le fasi di collezione, omogeneizzazione e memorizzazione dei dai Il livello intermedio rappresenta l’analisi Il livello superiore rappresenta la fase di prestazione dei risultati ottenuti Basi di dati: OLAP 21 December, 2015 - slide 116 OLAP vs. Data mining OLAP Basi di dati: OLAP Individua cosa è successo in passato Informazioni ottenute da richieste dirette Dati storici Strumenti reattivi Data mining Individua perché è successo qualcosa Conoscenza basata sulla ricerca di relazioni Modelli predittivi Strumenti proattivi 21 December, 2015 - slide 121 OLAP vs. Data mining OLAP Basi di dati: OLAP Quali sono i clienti che non hanno rispettato i pagamenti dei mutui? Quali sono stati i miei migliori 10 clienti? Qual è stato il tasso di risposta all’ultimo mailing? Quanti prodotti finanziari abbiamo venduto ai nostri clienti l’anno scorso? Quanti clienti hanno venduto i fondi in portafoglio? 21 December, 2015 - slide 122 OLAP vs. Data mining Data mining Basi di dati: OLAP A quali clienti è possibile erogare un mutuo avendo probabilità elevate di restituzione? Quali sono i 10 clienti che offrono il miglior profitto potenziale? Qual è il profilo delle persone che molto probabilmente risponderanno al mailing? Quali dei nostri clienti saranno interessati al nostro nuovo fondo d’investimento? Quanti clienti potrebbero vendere i propri fondi e acquistarne dalla concorrenza? 21 December, 2015 - slide 123 OLAP vs. Data mining Questo è un tipo di risultato che uno strumento di OLAP può fornire. Nel grafico non appaiono linee di tendenza particolari. Adesso guardiamo gli stessi dati ripartiti su tre gruppi di clienti identificati applicando l'analisi dei gruppi. Basi di dati: OLAP 21 December, 2015 - slide 124 OLAP vs. Data mining Il grafico mostra che l'acquisizione sta aumentando fra i clienti di "Breve termine", è sostanzialmente stabile fra i clienti definiti "Generici" e sta calando fra quelli di "Lungo termine". Dal momento che i clienti di "Lungo termine" sono i più interessanti per la banca, questa tendenza rappresenta un problema. Disponendo di questa ripartizione è stato innanzitutto possibile rilevare il problema, e sarà possibile studiare azioni specifiche di marketing dirette a invertire la tendenza. Basi di dati: OLAP 21 December, 2015 - slide 125 OLAP vs. Data mining Inoltre il data mining consente di costruire modelli per prevedere il comportamento futuro dei clienti Basi di dati: OLAP 21 December, 2015 - slide 126 Tecniche di data mining L’uso di tecniche di data mining è necessario per massimizzare l’estrazione di informazione da una quantità sempre crescente di dati. Tali tecniche sono generali e possono essere utilizzato in diversi campi applicativi: Tecniche di classificazione Tecniche di clustering Selezione delle proprietà maggiormente rilevanti Uso di agenti automatici o semi-automatici Tecniche di visualizzazione Basi di dati: OLAP 21 December, 2015 - slide 128 Classificazione/Regressione E’ lo sviluppo di un modello o di una funzione che mappa gli oggetti in classi predefinite (classificazione) o in appropriati valori (regressione) Il modello/funzione è costruito utilizzando un training set formato da elementi già classificati Una volta determinato il modello esso viene utilizzato per eseguire le future classificazioni (supervised learning) Basi di dati: OLAP 21 December, 2015 - slide 129 Clustering In questo caso, al contrario della classificazione, non si sa nulla a priori Inizialmente non ci sono regole o classi, ma esse vengono scoperte per similarità tra gli oggetti analizzati Il processo consiste nel raggruppare gli oggetti in classi con l’obiettivo di massimizzare la similarità degli elementi all’interno di ogni classe e minimizzare la similarità interclasse (unsupervised learning) Basi di dati: OLAP 21 December, 2015 - slide 130 Regole associative Può essere considerato il problema più rappresentativo del data mining come disciplina Esso richiede di estrarre regole del tipo X -> Y da un database, con il significato che oggetti/dati che soddisfano la condizione X probabilmente soddisfano anche Y Basi di dati: OLAP 21 December, 2015 - slide 131 Regole associative: esempio La regola A -> C ha: Un supporto pari al 50%, perché {A,C} compare in 2 transazioni su 4 Una confidenza pari al 66,6% perché su 3 transazioni in cui compare A in due compare anche C Basi di dati: OLAP 21 December, 2015 - slide 132 Pattern sequenziali Riguarda la scoperta di sottosequenze frequenti in una collezione di sequenze, ognuna delle quali rappresenta una serie di eventi verificatisi in successione nel tempo In questo contesto l’ordinamento degli eventi nelle sottosequenze è determinante. Un esempio possono essere sequenze di acquisti che occorrono frequentemente nel tempo: ad esempio si può notare che clienti che acquistano un televisore spesso poi acquistano anche un lettore DVD e questa informazione può venire usata per decisioni di marketing volte a fare in modo che il cliente sia invogliato ad acquistarli entrambi. Basi di dati: OLAP 21 December, 2015 - slide 133 Gli alberi decisionali Un albero decisionale è un diagramma di flusso di domande o punti di dati che in definitiva riportano a una decisione I sistemi ad alberi decisionali cercano di creare percorsi ottimizzati, ordinando le domande in modo da poter prendere una decisione nel minor numero di passi Basi di dati: OLAP 21 December, 2015 - slide 134 Le reti neurali Le reti neurali sono modelli che simulano la struttura del cervello umano, imitandone i meccanismi di apprendimento. In base ai dati di input le reti neurali correggono i parametri del modello per trovare relazioni tra i dati. Basi di dati: OLAP 21 December, 2015 - slide 135 Le reti neurali Le reti neurali sono non lineari per definizione, ma non fanno alcun tipo di ipotesi sulla forma dei dati, a differenza di tecniche come la regressione non lineare Le reti neurali sono utili per analizzare una variabile obiettivo in presenza di forte non linearità e di interazioni Le applicazioni principali sono le previsioni, il merito di credito, i modelli di risposta e le indagini di rischio Basi di dati: OLAP 21 December, 2015 - slide 136 Microsoft SQL Server Analisi delle vendite: I risultati degli articoli venduti possono essere utilizzati per creare un sistema online per la proposta di suggerimenti o per determinare come la combinazione di prodotti possono contribuire sul profitto. Analisi dei clienti insoddisfatti: Possiamo creare dei report per individuare eventuali clienti che possono passare alla concorrenza Analisi di mercato: Possiamo definire segmenti di mercato raggruppando i clienti simili per eseguire analisi delle tendenze o progettare campagne di marketing. Basi di dati: OLAP 21 December, 2015 - slide 139 Microsoft SQL Server Previsioni: Possiamo creare proiezioni delle vendite e delle scorte per scoprire le relazioni. Esplorazione dei dati: Si acquisisce una migliore conoscenza dei clienti esaminando i modelli rilevati dagli algoritmi di data mining. Analisi di siti Web: Possiamo individuare come i visitatori utilizzano il nostro sito web. Analisi delle campagne: Come investire meglio il budget destinato al marketing realizzando iniziative mirate Basi di dati: OLAP 21 December, 2015 - slide 140 Altri strumenti di data mining Clementine - SPSS Inc. Intelligent Miner - IBM S.p.a. Enterprise Miner - SAS Institute Inc. SuperQuery - Azmy Thinkware Inc. Tlearn - freeware R System - freeware Weka - freeware Basi di dati: OLAP 21 December, 2015 - slide 142 Bibliografia Manuale di Ms-SQL Addison Wesley Data Warehouse, Data Mining e OLAP A.Berson, S. J.Smith, McGrawHiull Designing and Building the Database http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/dts_whs_7.htm Inelligent Enterprise Magazine - Ralph Kimball Data Warehouse Designer - Fact Table and Dimension Tables http://www.intelligententerprise.com/030101/602warehouse1_2.jhtml Data Mining: Concepts, Models, Methods, and Algorithms di Mehmed Kantardzic High Performance Multidimensional Analysis and Data Mining di Sanjay Goil and Alok Choudhary Using Data Mining to Support the Construction and Maintenance of Expert Systems di Geoffrey Holmes and Sally Jo Cunningham Discovering Data Mining - From Concept to Implementation di P. Cabena, P. Hadjinian, R. Stadler, J. Verhees, A. Zanasi Introduction to Data Mining di P.N. Tan, M. Steinbach, V. Kumar. Basi di dati: OLAP 21 December, 2015 - slide 151