Popolare un Data Warehouse con SQL Server Data Transformation

Popolare un
Data Warehouse con
SQL Server Data
Transformation Services
(DTS)
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
 ETL - Processo di alimentazione di un Data
Warehouse
 Individuazione fonti dati
 Criteri di normalizzazione
 Processo logico di alimentazione
 DTS come strumento di ETL
www.devleap.it
ETL – processo di
alimentazione
www.devleap.it
Implementazione dello Star Schema
 Estrazione di dati da fonti diverse
 Integrazione, trasformazione e
ristrutturazione dei dati
 Caricamento dei dati in tabelle dimensioni
e tabelle dei fatti
www.devleap.it
Processo di trasformazione dei dati
Source OLTP
Systems
SQL
Server
Temporary Data
Staging Area
Data Marts
Sales
Sales Data
Hardware Data
Oracle
Data
Warehouse
Other
Validate, Gather ,
Make Data Consistent
Transform
Data
Populate Data
Warehouse
www.devleap.it
Service
Other
Distribute
Data
Caricamento dati dimensionali





Accesso a fonti dati eterogenee
Verifica fonti dati
Assicurare consistenza dei dati
Mantenere integrità delle dimensioni
Gestire il cambiamento dei dati
dimensionali
www.devleap.it
Accesso a fonti dati eterogenee
 Fonti relazionali
 OLTP system in SQL Server
 Financial database in Access
 …
 Fonti non relazionali
 File di testo
 Fogli elettronici (Excel)
 …
Northwind
OLTP
External
Files
Files
www.devleap.it
Financial
Spreadsheets
Verifica fonti dati
 Verificare accuratezza fonti dati
 Regole di business
 Requisiti strutturali
 Gestire dati non validi
 Rifiutare dati non validi nel caricamento
 Salvare dati non validi per successive correzioni
www.devleap.it
Rendere i dati consistenti
 Integrare i dati da fonti diverse
 Trasformare i dati in un formato
standardizzato
www.devleap.it
Trasformare i dati
Transform
Change
buyer_name reg_id total_sales
Barr, Adam
II
17.60
Chai, Sean
IV
52.80
O’Melia, Erin
VI
8.82
...
...
...
buyer_name reg_id total_sales
Barr, Adam
2
17.60
Chai, Sean
4
52.80
O’Melia, Erin
6
8.82
...
...
...
Combine
buyer_first
Adam
Sean
Erin
...
buyer_last reg_id total_sales
Barr
2
17.60
Chai
4
52.80
O’Melia
6
8.82
...
...
...
buyer_name reg_id total_sales
Barr, Adam
2
17.60
Chai, Sean
4
52.80
O’Melia, Erin
6
8.82
...
...
...
Calculate
buyer_name price_id
Barr, Adam
0.55
Chai, Sean
1.10
O’Melia, Erin 0.98
...
...
qty_id
32
48
9
...
buyer_name
Barr, Adam
Chai, Sean
O’Melia, Erin
...
www.devleap.it
price_id qty_id total_sales
0.55
32
17.60
1.10
48
52.80
0.98
9
8.82
...
...
...
Mantenere integrità della dimensione
 Chiave surrogata per ogni record
 Definisce la chiave primaria della dimensione
 Collega i campi foreign key della tabella dei fatti
 Caricare un record per ogni chiave
applicativa
 Mantiene univocità nella dimensione
 Dipende da come si gestisce il cambiamento dei
dati dimensionali
 Mantenere l’integrità della tabella dei fatti
www.devleap.it
Gestire cambiamento dati dimensioni
 Dimensioni con valori di colonne che
cambiano
 Inserire nuovi dati
 Modificare dati esistenti
 Soluzione di disegno per slowly-changing
Dimension
 Type 1: Sovrascrivere il record della dimensione
 Type 2: Scrivere un altro record nella
dimensione
 Type 3: Aggiungere attributi al record della
dimensione
www.devleap.it
Definire caricamento tabella dei fatti
 Accesso a fonti dati eterogenee
 Come per dimensioni
 Verifica fonte dati
 Come per dimensioni
 Assegnare foreign key
 Definire misure
 Mantenere integrità dei dati
www.devleap.it
Assegnare Foreign Key
Dimension
Tables
customer_dim
201 ALFI Alfreds
product_dim
25 123 Chai
time_dim
134 1/1/2000
Sales Fact Source Data
customer id
ALFI
product id order date quantity_sales amount_sales
123
1/1/2000
400
10,789
 Identificare chiave applicativa
dimensionale nei dati che
alimentano la tabella dei fatti
 Recuperare le chiavi primarie
da ogni tabella dimensionale
per assegnare le foreign key
www.devleap.it
Definire misure
 Caricare le
misure dalla
fonte dati
 Calcolare misure
aggiuntive /
derivate
customer_id
VINET
ALFI
HANAR
...
product_id
9GZ
1KJ
0ZA
...
price
.55
1.10
.98
...
qty
32
48
9
...
Source System Data
customer_key
100
238
437
...
product_key
512
207
338
...
qty
32
48
9
...
Fact Table Data
www.devleap.it
total_sales
17.60
52.80
8.82
...
Mantenere integrità dei dati
 Aderire alla granularità della tabella dei
fatti
 Una tabella dei fatti può avere una sola
granularità
 Bisogna caricare i dati con lo stesso livello di
dettaglio definito dalla granularità
 Verificare constraint colonne
 NOT NULL constraint
 FOREIGN KEY constraint
www.devleap.it
Implementare Staging Table
 Centralizzare e integrare fonti dati diverse
 Spezzare in parti più semplici delle
trasformazioni complessi
 Facilitare il recovery degli errori
www.devleap.it
ETL
 Tool utilizzati per alimentare un data mart
 ETL: Extract, Transform and Load
 Estrazione
 Connettività
 Trasformazione
 Cambia il modello logico
 Individua errori di coerenza
 Corregge o segnala anomalie
 Caricamento
 Connettività
 Ottimizzazione
www.devleap.it
Validazione dei dati
 Convalidare e correggere i dati prima di
importarli nel Data Warehouse
 Determinare e correggere i processi che
invalidano i dati
 Salvare in un log i dati non validi per un
esame successivo
www.devleap.it
Rendere i dati consistenti
 I dati possono essere inconsistenti per vari
motivi:
 I dati provengono da fonti diverse, in cui sono
consistenti, ma vengono rappresentati in modo
diverso nel Data Warehouse
 La stessa informazione è rappresentata in modo
diverso su differenti fonti dati
 Rendere i dati consistenti:
 Eliminare codifiche numeriche in valori leggibili
 Convertire versioni diverse della stessa
informazione in una singola rappresentazione
www.devleap.it
Alimentare un Data Warehouse
 Scegliere il tool ETL appropriato
 Transact-SQL
 Query distribuite
 Utility BCP (bulk copy), istruzione BULK INSERT
 DTS (Data Transformation Services)
www.devleap.it
DTS (Data Transformation Services)
 Incluso in SQL Server 2000
 Supporta qualsiasi fonte (e destinazione)
OLE DB
 Consente di integrare ActiveX Scripts
all’interno delle operazioni disponibili
www.devleap.it
Il Package di DTS
Step 1
Source
SQL Query
Destination
Step 2
Source
Query
Destination
VB
Script
Java
COM
Object
Step 3
Step N
Exec Utility
…..
www.devleap.it
Il Package di DTS
 Contiene la definizione delle attività da
eseguire come parte della trasformazione
 Può essere eseguito dalla GUI, da
scheduler, da command line, da script
 Ciascun package è costituito da passi (step)
multipli
 Gli step vengono eseguiti in serie o in
parallelo
 Fornisce un modello di “workflow”
 Transazioni lungo gli step
www.devleap.it
DTS Package Editor
www.devleap.it
DTS Object Model
DTS Package
Steps
Precedences
Tasks
Custom
ActiveX
Data Pump
Source
Columns
Globals
Destination
Transforms
www.devleap.it
Package steps
 Step “custom” (personalizzato)
 IDTSCustomStep
 Datapump
 Trasformazione generica OLE DB
 ActiveX Scripting
 Logica procedurale (integrata in DataPump)
 EXE
 Integrazione con applicazioni esistenti
www.devleap.it
DTS Import/Export Wizard
 Il Wizard Import/Export data di SQL Server
genera un package DTS
 Se eseguito immediatamente, resta in
memoria e non viene salvato
 Utile per creare veloci prototipi o per
mappare inizialmente molte tabelle
www.devleap.it
DTS Package Designer

Menu
System



MMC menu
DTS
Package
Designer
menu
Toolbars
DTS
Package
Designer
toolbar
 Task toolbar
 Connection
toolbar


Design Sheet



www.devleap.it
Connections
Tasks
Steps
Connessioni in un package DTS
 Fonti dati
 OLE DB
 ODBC
 Categorie connessioni
 File connections
 Database connections
 Microsoft Data Links
www.devleap.it
Tasks che trasformano dati
 Transform Data Task
Trasforma e inserisce dati
 Data Driven Query Task
Operazioni flessibili con T-SQL
 ParallelDataPumpTask
Elabora rowset gerarchici
www.devleap.it
Tasks che copiano e gestiscono dati




Bulk Insert Task Loads Files into SQL
Server
Execute SQL Task Runs SQL
Statements
Copy SQL Server Objects Task
Copies Objects Between
SQL Server Databases
Transfer Databases Task Copies a
SQL Server Database

Transfer Jobs Task Copies
SQL Server Agent Jobs

Transfer Logins Task Copies
SQL Server Logins

Transfer Master Stored Procedures
Task Copies SQL Server Master
Database Stored Procedures

Transfer Error Messages Task Copies
SQL Server User-specified Error
Messages
www.devleap.it
Tasks che funzionano come Job

ActiveX Script Task Performs UserDefined Logic


FTP Task Transfers Files


Analysis Services Task Processes
Cubes


Data Mining Task Processes Data
Mining Models
Send Mail Task Sends Email
Messages


Execute Process Task
Runs Executable Files
Message Queue Task
Sends and Receives
Messages
Execute Package Task
Executes DTS Packages
Dynamic Properties Task
Changes Package
Properties
www.devleap.it
Applicare vincoli di precedenza
 Vincoli di precedenza
 On Completion
 On Success
 On Failure
 Esecuzione degli step basata su vincoli di
precedenza
 Usare zero, uno o più vincoli di precedenza
 Si devono soddisfare tutto i vincoli di
precedenza definiti
www.devleap.it
Proprietà step
 Transazione
 Joining a step to package transaction
 Committing a transaction
 Rolling back a transaction
 Impostazioni esecuzione
 Eseguire uno step nel thread principale (STA)
 Interruzione di un package in seguito al
fallimento di uno step
 Disabilitazione di uno step
www.devleap.it
Memorizzare ed eseguire package
 Memorizzare i Package
 SQL Server
 SQL Server Meta Data Services
 Structured storage file
 Visual Basic file
 Eseguire i package
 Esecuzione interattiva
 Esecuzione batch
www.devleap.it
Task che copiano e gestiscono dati
 Bulk Insert Task Quickly
Carica file in SQL Server
 Execute SQL Task
Esegue istruzioni T-SQL
 Copy SQL Server Objects Task
Copia oggetti tra istanze di SQL Server 7.0
o SQL Server 2000
 Transfer Database Objects Tasks
Copia informazioni complessive per un
database da un’istanza di SQL Server 7.0 o
SQL Server 2000 a un’istanza di SQL Server
2000
www.devleap.it
Funzionalità Bulk Insert Task
 Caricamento veloce da file a SQL Server
 Supporta destinazione Tabelle o Viste su
SQL Server
 Richiede file delimitati (CSV o lunghezza
fissa)
 Carica i dati senza trasformazioni
 Supporta i Format Files per specificare il
layout del file
 Richiede appartenenza a ruolo sysadmin
o bulkadmin
www.devleap.it
SQL Task
 Esecuzione istruzioni SQL
 Il database connesso deve comprendere la
sintassi SQL
 Supporta una o più istruzioni SQL
 Query parametriche
 Parametri input
 Parametri output
 Si usano le variabili di package
www.devleap.it
Trasformazioni DTS
www.devleap.it
DTS Data Pump
 Architettura a elevate prestazioni
 OLE DB Service Provider
 Estendibile via COM e ActiveX Scripts
IUnknown
IDTSDataPump
Data Pump
www.devleap.it
Elaborazione Data Pump
Source
OLE DB
ODBC
Fixed Field
ASCII Delimited
1.
2.
3.
Connessione a sorgente e
destinazione
Lettura metadati OLE DB
su colonne sorgenti e
destinazione
Raccoglie definizioni delle
trasformazioni dati
X Forms
Destination
ActiveX Script
Copy
Trim String
…
Custom
DTS Data Pump
In Out
www.devleap.it
OLE DB
ODBC
Fixed Field
ASCII Delimited
Repl. Publication
4.
Implementa le
trasformazioni
5.
Scrive i record sulla
destinazione
Transform Data Task
 Funzionalità di spostamento e
trasformazione dei dati
 Copia i dati tra fonti dati eterogenee
 Applica trasformazioni opzionali a livello di
colonna
 Funzionalità estese di trasferimento dei
dati
 Supporta elaborazione “batch” dei dati
 Fornisce capacità di gestione degli errori
 Contiene impostazioni di ottimizzazione per
destinazioni SQL Server
www.devleap.it
Impostare sorgente e destinazione
 Sorgente
 Connessione sorgente
 Tabella, query, vista o file sorgente
 Destinazione
 Connessione destinazione
 Tabella destinazione esistente o struttura
tabellare
 Nuova tabella destinazione o struttura tabellare
 La creazione avviene a design-time, non a ogni
esecuzione
 Il DTS Import/Export wizard crea un DTS con due
step separati, uno crea la tabella e l’altro l’alimenta
www.devleap.it
Definizione del mapping delle colonne



www.devleap.it
Mapping
uno-a-uno
Mapping
simmetrico
molti-a-molti
Mapping
asimmetrico
Costruire trasformazioni
ActiveX Script
www.devleap.it
Trasformazioni ActiveX Script
 Trasformazioni ActiveX Script
 Contengono logica di trasformazione definita
dall’utente
 Sono interpretate al momento dell’esecuzione
 Linguaggi di scripting
 VB Script
 JScript
 Qualsiasi linguaggio di scripting installato
www.devleap.it
Definire trasformazioni ActiveX Script
 Funzione che contiene la logica di
trasformazione dei dati
 Mapping delle colonne sorgenti e
destinazione
 Il valore di ritorno (definito nelle costanti
DTSTransformStat) definisce l’azione da
eseguire per il record
 Consente di fare trasformazioni 1:N e N:1
 Es. Pivot o Unpivot di una tabella
www.devleap.it
Costanti DTSTransformStat
DTSTransformStat Constant Description
DTSTransformStat_OK
Default conversions succeed and insert
the record into the destination
DTSTransformStat_Error
Terminate further processing of this row
and return an error for the record
DTSTransformStat_SkipFetch Do not fetch the next row
DTSTransformStat_SkipInsert
Do not write the current row to
the destination
DTSTransformStat_SkipRow
Terminate further processing of this row for
non-error reasons
www.devleap.it
Creare record multipli
Product Group
Drinks
Hot Beverage
Fruit Juice
Hot Chocolate
Apple Cider
Product Group
Beverage
Drinks
Hot Chocolate
Drinks
Apple Cider
www.devleap.it
Conclusioni
 DTS è uno strumento di sviluppo
 Riduce il codice da scrivere per le
operazioni ripetitive
 Mantiene aperte tutte le strade di
personalizzazione quando è necessario
 VBScript, JScript
 Componenti ActiveX
 Supporto per automazione procedure e
generazione log dettagliati
www.devleap.it
Altre Informazioni
 Dove posso ottenere maggiori informazioni
 www.microsoft.com/italy/sql
 www.microsoft.com/italy/businessintelligence
 www.microsoft.com/sql
 www.microsoft.com/sql/evaluation/bi
 msdn.microsoft.com
 Developer resources
 Microsoft Developer Network
 www.devleap.it
 www.sqljunkies.com
www.devleap.it