Introduzione alla
realizzazione di
Data Warehouse con
Microsoft SQL Server
MCSD MCAD MCSE+I MCSA MCDBA MCT
www.devleap.it
Chi siamo
 www.DevLeap.it
 Un gruppo di 5 persone con tanta voglia di
 Studiare a fondo le tecnologie
 Capire il “behind the scenes”
 Implementare soluzioni reali
 Confrontarsi con le problematiche reali
 Sperimentare nuove idee
 Facciamo Corsi, Conferenze, Training
www.devleap.it
Agenda
 Modellazione di un DataWarehouse
 Data Warehouse e Data Mart
 Componenti di un modello di Data Warehouse
 Differenze tra DB relazionali normalizzati e Data
Warehouse
 Star Schema e Snowflake Schema
www.devleap.it
Modellazione
Data Warehouse
www.devleap.it
Data Warehouse
 Magazzino di dati a livello di impresa
 Insieme di strumenti per convertire un
vasto insieme di dati in informazioni
utilizzabili dall’utente
 Obiettivi:
 Possibilità di accedere a tutti i dati dell’impresa,
centralizzati in un solo database
 Coerenza e consolidamento dei dati
 Velocità nell’accesso alle informazioni
 Supporto per l’analisi dei dati
www.devleap.it
Data Mart
 Magazzino di dati a livello dipartimentale
 E’ un segmento di un Data Warehouse
 E’ fisicamente realizzato come un Data
Warehouse, ma con una finalità più
ristretta:
 I dati coprono solo alcune aree aziendali
(ad es. vendite)
 Minori costi di realizzazione
 Risultati più vicini nel tempo
www.devleap.it
Data Warehouse vs. Data Mart
 Il Data Warehouse è un progetto più vasto,
complesso e costoso, ma garantisce
maggiore coerenza dei dati
 Da un Data Warehouse si possono ricavare
velocemente dei Data Mart (top-down)
 Si può costruire un Data Warehouse unendo
più Data Mart realizzati nel tempo (bottomup)
www.devleap.it
Finalità di un Data Warehouse
 Strumento di supporto decisionale
 Base informativa per costruire sistemi di
analisi e previsione:
 Reports
 On-Line Analytical Processing (OLAP)
 Data Mining
www.devleap.it
Report
 Report che analizzano i dati con una certa
profondità storica
 Possono richiedere tempi di elaborazione
elevati se i dati vanno aggregati
 Spesso ottenibili con soluzioni OLAP
(minore tempo di elaborazione)
www.devleap.it
Cosa è OLAP
 OLAP: On Line Analytical Processing
 È una tecnologia di Business Intelligence
 Sinonimo di termini usati in precedenza:
 DSS: Decision Support System
 EIS: Executive Information System
 OLAP = vista multidimensionale sui dati
www.devleap.it
On-Line Analytical Processing (OLAP)
 I dati, strutturati per dimensioni, vengono
esaminati a diversi livelli di dettaglio
(tramite aggregazione e drill-down)
 Viene anche chiamata analisi multidimensionale dei dati
 Può produrre report stampati, ma è prima
di tutto una funzionalità interattiva (come
le Pivot Table)
 Consente di verificare velocemente ipotesi
formulate dall’utente
www.devleap.it
Data Mining
 Tecniche per aiutare gli utenti ad estrarre
informazioni utili da grandi database
 L’utente finale non deve essere un esperto
di statistica
 Utilizzato per generare ipotesi
www.devleap.it
Caratteristiche di un Data Warehouse
 E’ un archivio di dati con le seguenti
caratteristiche:
 subject oriented
 integrated
 nonvolatile
 time variant
www.devleap.it
Definizione di un Data Warehouse
 E’ il processo più lungo e costoso:
 Identificare gli obiettivi di business
 Raccogliere ed analizzare informazioni
 Definire il modello di dati
 Identificare le fonti di dati
 Definire le trasformazioni necessarie a
consolidare i dati
 Stabilire profondità temporale, creare una base
storica
www.devleap.it
Identificare gli obiettivi
 Il Data Warehouse è uno strumento di
supporto alle decisioni: l’utente è in genere
il management aziendale
 Bisogna individuare i fatti di rilievo per i
decision-maker, non per l’operatività
quotidiana
 Acquisire la visione aziendale del
management
www.devleap.it
Raccogliere informazioni
 Spesso le informazioni necessarie sono già
raccolte e presentate in qualche report
 Chiedere ai decision-maker di compilare
tabelle excel con le informazioni che
vorrebbero avere
www.devleap.it
Definire il modello di dati
 Identificare gli eventi da misurare:
 Vendite
 Chiamate al customer-service
 Interventi di assistenza
 Produzione
 Mantenere flessibilità per il futuro:
 Nuovi prodotti
 Nuovi centri assistenza
 Nuove linee di produzione
www.devleap.it
Identificare le fonti dati
 In una grande azienda sono spesso fonti
eterogenee
 Molti dati possono risiedere su archivi non
strutturati:
 Fogli Excel
 E-mail
 Individuare le modalità di accesso periodico
alle fonti dati per alimentare il Data
Warehouse
www.devleap.it
Consolidare i dati
 Decidere le trasformazioni da applicare ai
dati per eliminare le differenze di:
 valuta
 notazione metrica
 fiscali
 memorizzazione fisica (tipo dei dati)
 Definire un processo automatico e ripetibile
di trasformazione dei dati
www.devleap.it
Base storica
 Prima che il Data Warehouse diventi
operativo, è probabile che esistano delle
operazioni una-tantum per creare una base
storica iniziale
 Le trasformazioni iniziali possono differire
da quelle periodiche di un sistema in
produzione:
 mole di dati da trasferire
 aggiornamento completo vs. incrementale
www.devleap.it
Il Data Warehouse al lavoro
Source OLTP
Systems
Data Marts
Clients
Data
Warehouse
1
Retrieve Data
2 Transform Data
3
Populate
Data Warehouse
4
www.devleap.it
Populate
Data Marts
5
Query
Data
Da OLTP a OLAP
 Passando da un sistema transazionale ad
un sistema di analisi, cambiano le
caratteristiche di:
 normalizzazione
 prestazioni su query e modifica dei dati
 profondità storica
 complessità delle query
 dettaglio degli eventi rilevati
www.devleap.it
Database OLTP
 Caratteristiche di un database per un
ambiente operativo:
 Normalizzazione completa
 Alto numero di tabelle e di associazioni
 Dati memorizzati al minimo livello di granularità
 Interrogazioni richiedono join di molte tabelle
 La struttura dei dati non varia di frequente
 Ottimizzato per inserimento dei dati
www.devleap.it
Database OLAP
 Caratteristiche di un database per un
ambiente analitico:
 Entità denormalizzate
 Disegno del database più semplice (meno
tabelle e meno associazioni) per una
comprensione più facile da parte dell’utente
 I dati memorizzati possono essere aggregati
(riassuntivi)
 Le interrogazioni richiedono poche join
 Ottimizzato per la consultazione, per l’utente è
read-only
www.devleap.it
Ottimizzare i database OLAP
 Una base dati per OLAP è prima di tutto
denormalizzata
 Esistono dei modelli generici pensati per
queste esigenze:
 Star Schema
 Snowflake Schema
 Un database OLAP può essere realizzato
sfruttando un generico database
relazionale, ma esistono soluzioni
specifiche diverse (OLAP Server)
www.devleap.it
Componenti di un modello DW
Tabella delle
Dimensioni
Comuni
Dimensioni
Tabella dei Fatti
Comune
Prodotto
Prodotti
Tempo Unità
Misure
Fatturato
Fatti
Tempo
www.devleap.it
Componenti di un modello DW
 Tabella dei fatti
Contiene misure numeriche che descrivono un
evento di business, come una vendita o una
transazione bancaria
 Fatto
Una riga nella tabella dei fatti; contiene uno o più
valori numerici che misurano un evento
 Misura
Una colonna numerica della tabella dei fatti
 Dimensione
Una entità di business che descrive il quando,
chi, dove, come di un fatto (tempo, prodotto,
cliente, ...)
www.devleap.it
Star Schema
 Lo Star Schema è la modellizzazione più
semplice ed efficace dei componenti di un
data warehouse
 Ogni tabella dei fatti è associata ad N
tabelle dimensionali
 Relazioni gerarchiche all’interno di una
dimensione (per es. anno/mese/giorno)
vengono mantenute in una sola tabella
dimensionale
www.devleap.it
Star Schema
Employee_Dim
EmployeeKey
EmployeeID
.
.
.
Time_Dim
Sales_Fact
TimeKey
TheDate
.
.
.
Product_Dim
Dimensional Keys
Shipper_Dim
TimeKey
TimeKey
EmployeeKey
ProductKey
CustomerKey
ShipperKey
RequiredDate
.
.
.
ProductKey
ProductID
.
.
.
Multipart Key
Measures
Customer_Dim
ShipperKey
ShipperID
CustomerKey
CustomerID
.
.
.
.
.
.
www.devleap.it
Star Schema
 La tabella dei fatti può contenere misure
che si riferiscono a livelli di dettaglio
differenti, in funzione della dimensione
 La tabella dei fatti può contenere lo stesso
dato più volte (a livello di riepilogo
giornaliero, mensile ed annuale)
www.devleap.it
Snowflake Schema
Primary Dimension Table
Sales_Fact
TimeKey
EmployeeKey
ProductKey
CustomerKey
ShipperKey
RequiredDate
.
.
.
Product_Dim
ProductKey
Product Name
Product Size
Product Brand ID
Secondary Dimension Tables
Product_Brand_Id
Product Brand
Product Category ID
Product_Category_Id
Product Category
Product Category ID
www.devleap.it
Scelta dello schema
Star
Snowflake
Easier
More Difficult
Numero di tabelle
Less
More
Complessità query
Simpler
More Complex
Prestazioni query
Quicker
Slower
Comprensibilità del modello
www.devleap.it
Granularità del modello
 Determinare i requisiti di analisi
 Scegliere il livello di dettaglio più basso
 Richiede più spazio su disco
 Maggiore tempo di elaborazione
 Fornisce capacità analitiche con maggiore
dettaglio
 Conformare le misure alla granularità
definita
www.devleap.it
Definire le dimensioni





Definire caratteristiche delle dimensioni
Identificare gerarchie
Definire dimensioni convenzionali
Condividere le dimensioni tra i Data Mart
Definire altri tipi di dimensioni
www.devleap.it
Caratteristiche delle dimensioni
 Applicare le caratteristiche alla tabella delle
dimensioni
 Definire una chiave primaria
 Includere colonne correlate e descrittive (usare
testo, non codici)
 Designing for Usability and Extensibility
 Minimizzare o evitare l’uso di codici o
abbreviazioni
 Creare colonne utili per i livelli di aggregazione
 Evitare valori mancanti o NULL
 Minimizzare il numero di righe che cambia nel
tempo
www.devleap.it
Gerarchie nelle Dimensioni
Gerarchia Consolidata
Gerarchia Separata
Località Negozio
Continente
Località Negozio
Continente Paese Regione
Città
Negozio
Continente
Paese
Paese
Regione
Regione
Città
Città
Negozio
Negozio
01
www.devleap.it
Definire dimensioni convenzionali
 Dimensione Tempo (o Data – Ora)
 “spezzare” le informazioni sulla data in attributi
individuali
 Rappresentare la data come giorni lavorativi,
weekend, vacanza, stagione, periodo fiscale,
ecc.
 Dettaglio limitato alla granularità della tabella
dei fatti
 Dimensione geografica
 Dimensione prodotto
 Dimensione cliente
www.devleap.it
Condividere le dimensioni tra i
Data Mart
Production
One instance exist and is
shared among data marts
Time
Multiple
instances exist in
individual data marts
Purchasing
www.devleap.it
Sales
Definire la tabella dei fatti
 Applicare la granularità definita
 Garantire la consistenza tra le misure
 Usare valori numerici e aggregabili
www.devleap.it
Minimizzare dimensione tabella fatti
 Ridurre il numero di colonne
 Dati ridondanti
 Dati non richiesti per l’analisi
 Ridurre la dimensione di ogni colonna
 Usare chiavi surrogate
 Assicurarsi che i campi carattere e binari siano a
lunghezza variabile
 Strano però nella tabella dei fatti avere campi così,
a meno che non siano degenerate dimension
 Disegno Star Schema risultante:
 Tabelle dei fatti – lunghe e strette
 Tabelle dimensioni – corte e larghe
www.devleap.it
Implementare uno Star Schema
 Stima dimensioni Data Warehouse
 Creare database
 Creare tabelle
 Creare constraint
 Creare indici
www.devleap.it
Stima dimensioni Data Warehouse
 Dimensioni tabella dei fatti
 Granularità
 Byte per riga
Variables:
Years of data = 5
Customers = 10,000
Average number of transactions per customer per day = 4
Description
Number of rows in fact table
Estimated row size of fact table
Estimated data warehouse size
Calculation Method
Value
10,000 x 4 x 365 x 5
73,000,000
(7 IDs x 4 bytes) + (5 measures x 4 bytes) ~ 48 bytes
~3.5 GB
48 bytes x 73,000,000 rows
www.devleap.it
Creare Database
 Usare opzioni CREATE DATABASE
 SIZE
 MAXSIZE
 FILEGROWTH
 Impostare opzioni Database
 Trunc. log on chkpt. (Recovery Model: Simple)
www.devleap.it
Creare tabelle
 Creare una tabella
 Specificare NULL o NOT NULL
 Quasi sempre NOT NULL
 Valutare uso di NULL per misure con Analysis
Services
 Generare valori colonne
www.devleap.it
Creare constraints
 Usare PRIMARY KEY
 Non consente valori duplicati
 Consente creazione indici
 Non consente valori NULL
 Uso di FOREIGN KEY
 Tabella dei fatti punta a tabelle dimensioni
 Definisce un riferimento a una colonna con
constraint PRIMARY KEY o UNIQUE
 Specifica i range di valori accettabili
www.devleap.it
Uso di Foreign Key
FOREIGN KEY
Constraint
product_key
customer_key
order_date_key
FOREIGN KEY
Constraint
customer_dim_key
time_dim_key
FOREIGN KEY Constraint
product_dim_key
www.devleap.it
Creare indici
 Creazione indici per data warehouse
 Definire chiave primaria in tabelle dimensioni
 Dichiarare relazioni foreign key
 Definire chiave primaria in tabella dei fatti
 Con Analysis Services si può evitare multipart key
 Valutare uso di chiave surrogata
 Definire indici per ogni foreign key nella tabella
dei fatti
 Valutare prestazioni se i dati si leggono una volta
sola per alimentare i cubi di Analysis Services
 Usare chiavi surrogate
 Indici clustered, nonclustered e composti
www.devleap.it
Conclusioni
 Il Data Warehouse è un mezzo, non un fine
 Facilmente interrogabile dall’utente
 Fonte dati per Analysis Services (OLAP)
 Modellazione denormalizzata secondo
canoni precisi
 Star Schema
 Alimentazione non continua
 Di solito giornaliera, settimanale o mensile
www.devleap.it
Altre Informazioni
 Dove posso ottenere maggiori informazioni
 http://www.microsoft.com/sql
 http://www.microsoft.com/sql/evaluation/bi
 http://msdn.microsoft.com
 Developer resources
 Microsoft Developer Network
 http://www.devleap.it
 http://www.sqljunkies.com
www.devleap.it