Modellazione dati multidimensionali

(1)

DW: Modelli dei dati, A. Albano

E’ necessario utilizzare una base di dati particolare, detta data warehouse, organizzata per analizzare i dati secondo una logica aziendale.

DAI DATI ALLE DECISIONI

La realizzazione di un data warehouse avviene gradualmente, a diversi livelli di astrazione, con un modello concettuale, un modello logico e un modello fisico.

1

Le aziende per competere devono usare metodi di analisi, con tecniche di Business

Intelligence, dei dati interni, accumulati nel tempo, e di dati esterni, sia per la

formazione delle decisioni sia per rendere i processi efficienti ed efficaci.

Il modello concettuale grafico dei fatti per DW.

MODELLI DEI DATI PER DW

Per definire la struttura di un DW si usano i seguenti formalismi, detti modelli dei dati:

Il modello logico multidimensionale (detto cubo), adatto per illustrare la nozione di analisi interattiva dei fatti.

Il modello logico relazionale, per mostrare come si rappresentano i dati nei sistemi per DW.

2

IL DATA WAREHOUSE DEGLI ORDINI

BASE DI DATI 3 Numero Data Ordine CF Nome Qualifica TelefonoUfficio Impiegato CF Nome Indirizzo Città Telefono Cliente NumeroRiga QuantitàOrdinata RigaOrdine Nome Categoria PrezzoUnitario Quantità Articolo Data Fattura MetodoPagamento Metodo Pagamento Quantità Data Spedizione MetodoSpedizione Metodo Spedizione

Quantità di articoli ordinati,

per articolo, per cliente e per mese Requisiti di analisi

Fatturato per categoria di articolo,

per cliente e per mese

Fatturato verso clienti della provincia di Pisa

per città dei clienti e per anno

COSA SI MODELLA IN UN DW

4

I fatti da analizzare (ad es. le vendite di articoli).

Le misure dei fatti: le proprietà numeriche da analizzare (ad es. la quantità venduta di un articolo).

Le dimensioni: rappresentano le prospettive di analisi dei fatti (ad es. le vendite si analizzano per articolo, per mese). Gli attributi dimensionali: attributi descrittivi delle dimensioni. Le gerarchie fra attributi dimensionali.

Data Articolo Cliente Nome Categoria Articolo Città Provincia Regione Cliente Giorno Mese Trimestre Anno Data IL DATA WAREHOUSE DEGLI ORDINI

BASE DI DATI Giorno Mese Trimestre Anno Data Città Provincia Regione Cliente Nome Categoria Articolo Numero Data Ordine CF Nome Qualifica TelefonoUfficio Impiegato CF Nome Indirizzo Città Telefono Cliente NumeroRiga QuantitàOrdinata RigaOrdine Nome Categoria Prezzo Quantità Articolo Data Fattura MetodoPagamento Metodo Pagamento Quantità Data Spedizione MetodoSpedizione Metodo Spedizione

DATA MART = DW TEMATICO 5

Quantità Prezzo

Ordine

SCHEMA CONCETTUALE DEI FATTI

I componenti di base sono i fatti, le misure e le dimensioni.

6 Misure Fatti Quantità Prezzo Ordini Cliente Data Articolo NOrdine

(2)

SCHEMA CONCETTUALE DEI FATTI: DIMENSIONI CON ATTRIBUTI

7 Quantità Prezzo Ordini Cliente Data Articolo Città Provincia Regione Categoria Nome Giorno Mese Trimestre Anno NOrdine Quantità Prezzo Ordini Cliente Data Articolo NOrdine

SCHEMA CONCETTUALE DEI FATTI: DIMENSIONI CON ATTRIBUTI E GERARCHIE

Senza gerarchie 8 Quantità Prezzo Ordini Cliente Data Articolo Città Provincia Regione Categoria Nome Giorno Mese Trimestre Anno NOrdine Con gerarchie Quantità Prezzo Ordini Cliente Data Articolo Città Provincia Regione Categoria Nome Giorno Mese Trimestre Anno NOrdine

PER INIZIARE IL PROGETTO DI UN DW

9 Identificare i patrocinatori, i dirigenti convinti del valore del progetto. Conoscere il problema aziendale prima di iniziare a discutere gli obiettivi del progetto.

Intervistare gli interessati, per comprendere le loro esigenze informative da tradurre in requisiti del progetto.

Un DW deve essere di supporto alle decisioni e quindi va progettato tenendo conto di come i dirigenti vogliono analizzare un certo processo aziendale e di quali informazioni sintetiche hanno bisogno.

Intervistare gli esperti del sistema informatico operazionale, per sapere quali dati esistono e come vengono gestiti.

METODOLOGIA (SEMPLIFICATA) DI PROGETTAZIONE DI UN DW

Per ogni data mart:

10

Progettazione dell’alimentazione dei dati Progettazione fisica del DW Progettazione logica del DW

(cosa si vuole) (cosa si può ottenere) (cosa si può analizzare)

•

Analisi dei requisiti

•

Progettazione concettuale iniziale

•

Progettazione concettuale dai dati operazionali

•

Progettazione concettuale finale

•

Progettazione logica

•

Progettazione concettuale

PROGETTAZIONE CONCETTUALE (SEMPLIFICATA) DI UN DATA MART

Passo 1 Si raccolgono i requisiti di analisi che il committente vuol fare sugli eventi di

interesse di un certo processo aziendale.

Passo 2 Si analizzano i requisiti di analisi per individuare il fatto da analizzare. Requisito di analisi: Totale dell’importo per venditore e per articolo

11 Dimensioni

Misura

Ad es. il processo di registrazione degli ordini dei clienti fatti ai venditori.

Aggregazione (metrica)

Alternativa: Report di analisi che si vogliono.

PROGETTAZIONE DI UN DATA MART(cont)

Passo 3 Si sceglie la granularità del fatto da analizzare. La grana dei fatti è il significato di un suo elemento. Di solito si preferisce una grana fine.

12 Ordini

Passo 4 Si scelgono le misure interessanti del fatto: grandezze numeriche che ha senso

sommare.

Quantità Importo Ordini

(3)

PROGETTAZIONE DI UN DATA MART(cont)

Passo 5 Si scelgono le dimensioni secondo le quali analizzare le misure dei fatti

(prospettive di analisi, contesto dei fatti)

13

Sono domande che mirano a individuare le variabili determinanti delle misure e le

possibili leve di intervento.

Si cerca una risposta a domande sul fatto tipo:

Chi è coinvolto? Cosa riguarda? Quando è accaduto? Dove è accaduto?

Quantità Importo Ordini Cliente Data Articolo Venditore NOrdine

PROGETTAZIONE DI UN DATA MART (cont)

14 Passo 6 Per rendere le analisi più interessanti è in generale utile prevedere dimensioni

con degli attributi organizzati in gerarchie per consentire aggregazioni delle misure a diversi livelli di generalità.

Regione Provincia Città Anno Trimestre Mese Giorno NomeVenditore ZonaVendita Quantità Importo Ordini Cliente Data Articolo Venditore NOrdine Categoria Nome

DW: Modelli dei dati, A. Albano STUDIO DI UN CASO

Di un paziente interessano il codice fiscale, che lo identifica, il nome e l’indirizzo. Di un medico interessano il codice fiscale, che lo identifica, il nome, il telefono e la specializzazione.

Un paziente può essere ricoverato più volte e ogni volta interessa la data, il medico che ne ha fatto la richiesta, il reparto assegnato, la diagnosi, la durata e l’attesa in giorni del ricovero e la tariffa (ricavo) del ricovero. Di una diagnosi interessa il codice e la descrizione.

Di un reparto interessano il codice, che lo identifica, il nome, il numero dei letti e il telefono.

Di un intervento interessano il codice, che lo identifica, la descrizione, il medico che la effettua, il ricovero del paziente che ha subito l’intervento.

15

Si consideri una BD di una casa di cura che contiene informazioni sugli interventi effettuati sui pazienti ricoverati.

STUDIO DI UN CASO: REQUISITI DI ANALISI

(a) Ricavo totale dei ricoveri per codice (descrizione) della diagnosi e per mese (anno) (b) Numero totale dei ricoveri e ricavo totale per

reparto e per sesso (età, città, regione) del paziente

(c) Ricavo totale, durata e attesa media del ricovero per descrizione della diagnosi e per nome (specializzazione) del medico richiedente

(d) Ricavo totale, durata e attesa media del ricovero per età (regione) del paziente e per codice (descrizione) dell’intervento

16 Data Durata Attesa Ricavo Ricoveri CF <<PK>> Nome Indirizzo Pazienti CF <<PK>> Nome Specializzazione Telefono Medici Codice <<PK>> Descrizione Interventi Codice <<PK>> Nome NumeroLetti Telefono Reparti Codice <<PK>> Descrizione Diagnosi HaFatto HaRichietso Nel Per Con HaAvuto

DW: Modelli dei dati, A. Albano SPECIFICA DEI REQUISITI

17 3.1 Soluzione

Lo schema concettuale della base di dati `e mostrato in Figura 6.

!"#" !$"%&'($ !)*"#" +##,(" -$."/' !"#$%&'" 012334566 7'8, 9&:$*$;;' ()*"&+," 012334566 7'8, <=,.$">$;;";$'&, ?,>,@'&' -&."#" 0':$.,2334566 !,(.*$;$'&, /+,&'%&+," 0':$.,2334566 7'8, 7)8,*'A,##$ ?,>,@'&' !&0)'," 0':$.,2334566 !,(.*$;$'&, 1")2+$3" 4)5),,$ 4)!"#6"&,3$ 7&8 (&' 9$+ 4):%;,$

Figura 6 Schema concettuale della BD dei ricoveri

La specifica dei requisiti viene data elencando gli esempi di analisi e, per ognuna di esse, le dimensioni e le misure utilizzate, e le aggregazioni da calcolare (metriche):

Processo Ricoveri

N Requisito di analisi Dimensioni Misure Metriche

(a) Ricavo totale dei ricoveri per codice (descrizione) della diagnosi e per mese (anno).

Diagnosi(Codice, Descrizione), Data(Mese, Anno)

Ricavo Ricavo totale

(b) Numero totale dei ricoveri e ricavo to-tale per reparto e per sesso (et`a, citt`a, regione) del paziente.

Reparto,

Paziente(Sesso, Et`a, Citt`a, Regione)

Ricavo Numero totale Ricavo totale (c) Ricavo totale, durata e attesa media dei

ricoveri per descrizione della diagnosi e per nome (specializzazione) del medico richiedente. Diagnosi(Descrizione), Medico(Nome, Specializzazione) Ricavo, Durata, Attesa Ricavo totale Durata media Attesa media (d) Ricavo totale, durata e attesa media de

ricoveri per et`a (regione) del paziente e per codice (descrizione) dell’intervento.

Paziente(Et`a, Regione), Intervento(Codice, Descrizione) Ricavo, Durata, Attesa Ricavo totale Durata media Attesa media Dalla specifica dei requisiti scaturisce la seguente granularit`a del fatto e lo schema concettuale del data mart di Figura 7:

Granularit`a del fatto Ricovero

Descrizione Dimensioni preliminari Misure preliminari

Un fatto riguarda un ricove-ro di un paziente, supponen-do che comporti al pi`u un intervento.

Paziente, Data, Reparto, Dia-gnosi, Intervento, Medico che lo ha richiesto

Durata, Attesa, Ricavo

DW: Modelli dei dati, A. Albano SPECIFICA DEI REQUISITI

18

1.1 Soluzione

!"#" !$"%&'($ !)*"#" +##,(" -$."/' !"#$%&'" 012334566 7'8, 9&:$*$;;' ()*"&+," 012334566 7'8, <=,.$">$;;";$'&, ?,>,@'&' -&."#" 0':$.,2334566 !,(.*$;$'&, /+,&'%&+," 0':$.,2334566 7'8, 7)8,*'A,##$ ?,>,@'&' !&0)'," 0':$.,2334566 !,(.*$;$'&, 1")2+$3" 4)5),,$ 4)!"#6"&,3$ 7&8 (&' 9$+ 4):%;,$

Figura 1 Schema concettuale della BD dei ricoveri

Processo Ricoveri

N Requisito di analisi Dimensioni Misure

1 Ricavo totale dei ricoveri per codice (descrizione) della diagnosi e per mese (anno).

Diagnosi(CodiceDiagnosi, DescrizioneDiagnosi, Data(Mese, Anno)

Ricavo

2 Numero totale dei ricoveri e ricavo to-tale per reparto e per sesso (et`a, citt`a, regione) del paziente.

Reparto, Paziente(Sesso, Et`a, Citt`a, Regione)

Ricavo

3 Ricavo totale, durata e attesa media del ricovero per descrizione della diagnosi e per nome (specializzazione) del medico richiedente.

Diagnosi(Descrizione), Medi-co(Nome, Specializzazione)

Ricavo, Durata, At-tesa

4 Ricavo totale, durata e attesa media del ricovero per et`a (regione) del paziente e per codice (descrizione) dell’intervento.

Paziente(Et`a, Regione), Inter-vento(CodiceIntervento, De-scrizioneIntervento)

Ricavo, Durata, At-tesa

Granularit`a del fatto Ricovero

Descrizione Dimensioni preliminari Misure preliminari

Un fatto riguarda un ricove-ro di un paziente, supponen-do che comporti al pi`u un intervento.

Data, Reparto, Diagnosi, In-tervento, Medico che lo ha richiesto

Durata, Attesa, Ricavo

(4)

DW: Modelli dei dati, A. Albano Ricoveri RICOVERI BASE DI DATI DATA MART 19 Sesso Città Regione Età Durata Attesa Ricavo Ricoveri Diagnosi Data Medico Paziente Reparto Intervento Mese Anno Descrizione Diagnosi Codice

Diagnosi InterventoCodice Descrizione Intervento Specializzazione Nome Data Durata Attesa Ricavo Ricoveri CF <<PK>> Nome Indirizzo Pazienti CF <<PK>> Nome Specializzazione Telefono Medici Codice <<PK>> Descrizione Interventi Codice <<PK>> Nome NumeroLetti Telefono Reparti Codice <<PK>> Descrizione Diagnosi HaFatto HaRichietso Nel Per Con HaAvuto Durata Attesa Ricavo Ricoveri

DW: Modelli dei dati, A. Albano RIEPILOGO

20

Esempio di progettazione concettuale a partire dai requisiti di analisi Formulazione requisiti

Funzioni di aggregazioni da usare,

su quale sottoinsieme dei dati, per quali raggruppamenti dei dati, come presentare il risultato.

SELECT X FROM … WHERE B GROUP BY Y ORDER BY W Alternativa: Report di analisi che si vogliono

Problemi dei dati: come si ricavano dalla BD disponibile. Granularità dei fatti

MODELLAZIONE CONCETTUALE: APPROFONDIMENTI

La granularità dei fatti determina il tipo di analisi che si possono fare

Ammontare Movimenti CC Ammontare Movimenti CC

Movimenti sui conti correnti (Fatto istantaneo)

Movimentazione mensile (Fatto istantanea periodica)

Transazione Data Conto Agenzia Data Conto 21 (Fatto istantanea accumulata)

MODELLAZIONE CONCETTUALE: APPROFONDIMENTI Tipi di misure e tipi di aggregazioni

Numeriche (calcolate) additive

? Reclami Cliente Data Prodotto 22 Quantità Prezzo Costo Sconto Ricavo Margine Vendite Negozio Data Prodotto Prodotto Data Magazzino Scorta Livello Riordino Scorte

Numeriche semiadditive (Scorta), Numeriche non additive (LivelloRiordino) (Margine sui ricavi ROS = Margine/Ricavo ?)

Possono mancare

Il tipo di misura si specifica a parte.

MODELLAZIONE CONCETTUALE: APPROFONDIMENTI TIPI DI MISURE

23 Misure di flusso: sono le più comuni e si riferiscono a valori cumulati dello stato di

eventi che si verificano in un intervallo temporale di riferimento. (es. Quantità di prodotti venduti in un giorno) (Additive)

Misure di livello: valore dello stato di eventi che si

verificano in un intervallo temporale di riferimento. (es. Giacenza di un prodotto, Saldo CC) (Semi additive)

Misure unitarie: (Non additive)

Attributi descrittivi Dimensioni o attributi opzionali Dimensioni multivalore

Gerarchie bilanciate, incomplete, ricorsive Gerarchie condivise Dimensioni degeneri 24 Cliente Data Città Stato Giorno Mese Anno Paese Cliente Vendita Cliente Fatturazione Quantità Prezzo Ricavo Provvigione Vendite Articolo Garanzia Nome Agente Nome Supervisore Numero ordine

(5)

MODELLAZIONE CONCETTUALE: ATTRIBUTI DI DATA

25 Column name Data type Format/Example

date_key int yyyymmdd

day_date smalldatetime

day_of_week char Monday

week_begin_date tinyint

week_num tinyint 1 to 52 or 53

month_num tinyint 1 to 12

month_name char January

month_short_name char Jan

month_end_date smalldatetime days_in_month tinyint

yearmo int yyyymm

quarter_num tinyint 1 to 4 quarter_name char 1Q2000 year smallint weekend_ind bit workday_ind bit holiday_ind bit

holiday_name char Thanksgiving

peak_period_ind bit Fatti Data Mese Trimestre Anno GiornoDellaSettimana NumeroDellaSettimana Festività NomeMese GiorniDelMese

Nella tabella relazionale Data come chiave surrogata si può usare l’intero

AAAAMMGG

Attributi dimensionali con valori che cambiano raramente nel tempo

• TIPO 2 (oggi o ieri): si conserva la storia.

• TIPO 3 (oggi e ieri): si conserva la storia e la date della modifica. Attributi dimensionali con valori che cambiano frequentemente nel tempo

• TIPO 4

• TIPO 1 (oggi per ieri): si perde la storia.

26

Questi aspetti non si modellano nello schema concettuale, ma si specificano a parte

DW: Modelli dei dati, A. Albano MODELLO RELAZIONALE

I sistemi ROLAP (Relational OLAP) sono dei DBMS relazionali estesi con funzionalità per supportare efficientemente le applicazioni OLAP.

27

Nei sistemi ROLAP, un DW viene rappresentato con tabelle di uno schema a stella (star schema),

a fiocco di neve (snowflake schema) o a costellazione (constellation schema).

DW: Modelli dei dati, A. Albano SCHEMI A STELLA

Usando uno schema relazionale, la collezione dei fatti viene memorizzata in una tabella con attributi le dimensioni e le misure.

Si parla di schema a stella quando ogni dimensione ha attributi propri che vengono memorizzati in una tabella distinta. Nella tabella dei fatti le dimensioni sono le chiavi esterne.

28

Tutte le chiavi sono surrogate.

UN ESEMPIO DI SCHEMA A STELLA

29 Quantità Prezzo Sconto Ricavo Vendite Negozio Data Prodotto Città Provincia Regione Categoria Nome Giorno Mese Anno Nome FkNegozio FkData FkProdotto Quantità Prezzo Sconto Ricavo Vendite PkProdotto Nome Categoria Prodotto PkNegozio Nome Città Provincia Regione Negozio PkData Giorno Mese Anno Data

DW: Modelli dei dati, A. Albano SCHEMA A FIOCCHI DI NEVE

Lo schema a fiocco di neve è un raffinamento dello schema a stella, con la normalizzazione delle gerarchie, che vengono rappresentate in tabelle separate. 30 FkNegozio FkData FkProdotto Quantità Prezzo Sconto Ricavo Vendite PkProdotto Nome Categoria Prodotto PkNegozio Nome FkCittà Negozio PkData Giorno Mese Anno Data PkCittà Città FkProvincia Città PkProvincia Provincia Regione Provincia

(6)

DW: Modelli dei dati, A. Albano SCHEMA A COSTELLAZIONE

Per situazioni più complesse si può pensare di avere più tabelle dei fatti che condividano alcune tabelle di dimensioni. E' il caso dello schema a

costellazione. 31 FkNegozio FkData FkProdotto Quantità Prezzo Sconto Ricavo Vendite PkProdotto Nome Categoria Prodotto PkNegozio Nome FkCittà Negozio PkData Giorno Mese Anno Data PkCittà Città FkProvincia Città PkProvincia Provincia Regione Provincia PkCliente Nome Indirizzo Cliente _FKCliente FKProdotto DataId Livello Reclami

DW: Modelli dei dati, A. Albano DIMENSIONE DATA:

DAL MODELLO CONCETTUALE AL MODELLO LOGICO

32 Quantità Prezzo Sconto Ricavo Vendite Negozio Data Prodotto Città Provincia Regione Categoria Nome Mese Anno NScontrino FkNegozio FkData FkProdotto Quantità Prezzo Sconto Ricavo NScontrino Vendite PkProdotto Nome Categoria Prodotto PkNegozio Nome Città Provincia Regione Negozio PkData Mese Anno Data

PROGETTAZIONE LOGICA: DIMENSIONI MODIFICABILI TIPO2

33 ClienteSK<<PK>> CF <<PK1>> Città ... Cliente ... ClienteFK ... Vendite ClienteSK<<PK>> PrimaSK Città ... Cliente ... ClienteFK ... Vendite ClienteSK<<PK>> ... Città ... Cliente ... ClienteFK ClientePrimaSK ... Vendite • Clienti con una chiave naturale

• Clienti senza chiave naturale

DW: Modelli dei dati, A. Albano SQL PER ANALISI DEI DATI

SQL è stato esteso con le funzioni analitiche.

34

MODELLO MULTIDIMENSIONALE

I dati vengono rappresentati con una matrice multidimensionale (cubo): un fatto corrisponde ad una cella, individuata dai valori delle possibili coordinate (le dimensioni), che contiene i valori delle possibili misure

P1 P2 P3 P4 T4 T3 T1 T1 M 4 M 3 M 2 M 1 Data Market Prodotto Negozio

35 DW: Modelli dei dati, A. Albano CUBO DEI DATI 2-D

Tabella dei fatti

Cubo 2-D

Rappresentazione a cubo di una tabella dei fatti con due dimensioni

m2 p2 8 p1 m3 50 m1 p2 11 p1 m1 12 Quantità NegozioId ProdottoId VENDITE p2 11 8 50 p1 12 m3 m2 m1 ProdottoId NegozioId 36

(7)

DW: Modelli dei dati, A. Albano CUBO DEI DATI 3-D

Rappresentazione a cubo di una tabella dei fatti con tre dimensiomi

Tabella dei fatti

Cubo 3-D

p2 4 p1 44 m3 m2 m1 p2 11 8 50 p1 12 m3 m2 m1 d1 d2 NegozioId ProdottoId DataId 4 m2 p1 d2 m1 44 p1 d2 11 Quantità 50 8 12 m2 p2 d1 p1 m3 d1 m1 p2 d1 p1 m1 d1 DataId NegozioId ProdottoId VENDITE

37 DW: Modelli dei dati, A. Albano CUBO E GERARCHIE

La rappresentazione delle gerarchie non è immediata, ma è possibile.

20000220 09 20000220 20000920 20000920 2000 02 P1 P2 P3 P4 T4 T3 T1 T1 M4 M3 M2 M1 Data Market Prodotto

Anno Mese Giorno Gerarchia Data

Negozio

38

DW: Modelli dei dati, A. Albano SISTEMI MOLAP

Alcuni sistemi specializzati e i sistemi OLAP implementano direttamente il modello multidimensionale, usando un'opportuna struttura dati permanente tipo matrice. Sono i cosiddetti MOLAP (Multidimensional OLAP).

Una rappresentazione molto comune di un cubo sono le tabelle pivot di Excel

39