Introduzione alla tecnologia OLAP: Microsoft SQL Analisys Services

Introduzione alla tecnologia OLAP:
Microsoft SQL Analisys Services
Agenda
• Perchè mi serve il Data Warehouse e OLAP ?
• Caricamento e trasformazione dei dati (DTS)
• Basi dati multidimensionali (OLAP)
• Analysis Services step by step
• Caratteristiche di un database Olap
• Interrogare i cubi (Mdx e dintorni)
• Gestire la sicurezza in Analysis Services
• Deploy della soluzione
Perchè mi serve il Data
Warehouse e OLAP ?
Perchè mi serve il Data Warehouse e OLAP ?
• Dati = informazione non è sempre vero
• Sono organizzati per una elaborazione
transazionale
• Non forniscono dati “attendibili”
• Rallentano il sistema
• Spesso devono essere integrati con
sorgenti di diversa natura
Perchè mi serve il Data Warehouse e OLAP ?
• La soluzione è creare un db ad-hoc per le
analisi
• Lo scopo è di “concentrare” tutti i dati
dell’ azienda in un unico punto
• Integra informazioni provenienti da
sorgenti diverse
• I dati sono trasformati / “puliti”
• Il disegno e’ ottimizzato per la lettura
• Possiamo considerarlo come un db
Read-Only
Creare il Data Warehouse
• E’ un progetto molto difficoltoso :
– Capire e identificare gli obbiettivi della
analisi
– Trovare le informazioni e i dati sorgenti
– Applicare eventuali trasformazioni /
normalizzazioni per consolidare i dati
– Essere flessibili, riuscire a gestire
cambiamenti e “modifiche nella storia”
Creare il 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
– Base di partenza per OLAP
Creare il Data Warehouse
• Passi per la creazione del Data Warehouse
– Identificare gli eventi da misurare
• Vendite
• Movimentazione di magazzino
• Customer satisfaction
• Ecc.
– Identificare le fonti dati
• I dati possono arrivare da fonti diverse ed
eterogenee, non strutturate (Excel,file di testo,…)
– Consolidare i dati
• Trasformazioni per eliminare le differenze
– Es. Lira vs. Euro
– Definire processo di aggiornamento
• Intervallo di aggiornamento del DW
Creare il Data Warehouse
• Data Mart
– Poichè il processo per la creazione di un DW è
spesso lungo e difficoltoso, è possibile creare
dei processi intermedi
– “Mini” DW tematici per rispondere ad esigenze
specifiche (es. vendite, marketing, controllo di
gestione ecc.)
– L’insieme di tutti i Data Mart costituisce il DW
Componenti di un modello Data Warehouse
• Tabella dei fatti
– Contiene gli elementi da misurare
• es. (vendite,movimenti e transazioni ecc.)
– Elemento centrale del DW
• Misure
– Sono i valori che vogliamo analizzare
rappresentati delle quantità
• es. (importi, quantità, numero di
transazioni)
– Sono contenute nella tabella dei fatti
Componenti di un modello Data Warehouse
• Tabella delle dimensioni
– Descrive e rappresenta l’entità di business
– Fornisce un contesto alle misure
– È il “per” nelle analisi
• Es. (Venduto PER cliente,Venduto PER
prodotto)
• Dimensione
– E’ il contenuto della tabella dimensione
– Spesso è gerarchica
• Es. (Categoria -> SottoCategoria -> Prodotto)
Componenti di un modello Data Warehouse
Tabella delle
Dimensioni
Comuni
Dimensioni
Tabella dei Fatti
Comune
Prodotti
Tempo
Misure
Prodotto Tempo Unità Fatturato
Fatti
Componenti di un modello Data Warehouse
• Esempio Dw da Northwind
– Introduzione al nostro Case Study
– DEMO
Struttura di un Data Warehouse
• La struttura di un DW è riconducibile
a 2 modelli :
– Star Schema (a stella)
– Snowflake Schema (fiocco di neve)
Struttura di un Data Warehouse
• Star Schema
– Lo Star Schema è la modellizzazione più
semplice ed efficace dei componenti di un DW
– Ogni tabella dei fatti è associata a N tabelle
dimensionali
– Le relazioni gerarchiche all’interno di una
dimensione (per es. anno/mese/giorno)
vengono mantenute in una sola tabella
dimensionale
Star Schema
Employee_Dim
EmployeeKey
EmployeeID
.
.
.
Time_Dim
Sales_Fact
TimeKey
TheDate
.
.
.
Dimensional Keys
Shipper_Dim
ShipperKey
ShipperID
.
.
.
Product_Dim
TimeKey
TimeKey
EmployeeKey
EmployeeKey
ProductKey
ProductKey
CustomerKey
CustomerKey
ShipperKey
ShipperKey
RequiredDate
.
.
.
ProductKey
ProductID
.
.
.
Multipart Key
Measures
Customer_Dim
CustomerKey
CustomerID
.
.
.
Struttura di un Data Warehouse
• Snowflake schema
– Le gerarchie all’interno delle dimensioni sono
mantenute in tabelle separate
– È leggermente più complesso di una struttura a
stella
– È meno efficiente
Caricamento e
trasformazione dei dati
(DTS)
Alimentare un Data Warehouse
• Scegliere il tool appropriato:
– Transact-SQL
– Query distribuite
– Utility BCP (bulk copy), istruzione BULK
INSERT
– DTS (Data Transformation Services)
DTS (Data Transformation Services)
• Tool per automatizzare operazioni di
trasformazione/trasferimento di dati
• Incluso in SQL Server 7/2000
• Supporta qualsiasi fonte (e destinazione)
OLE DB
• Consente di integrare ActiveX Scripts
all’interno delle operazioni disponibili
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
DTS Package Editor
DTS (Data Transformation Services)
• DEMO !!!
Basi dati
multidimensionali
(OLAP)
Database OLAP
•
•
•
•
I dati sono vastissimi
Le query non sempre sono “immediate”
C’è bisogno di un “cane da query”
Le applicazioni spesso producono report
cartacei e/o fogli excel
• I report e/o fogli excel più che rispondere
a domande le generano
Database OLAP
• È una “organizzazione” multidimensionale
dei dati provenienti dal DW
• Il cubo rappresenta la struttura logica di
un database Olap
• Le dimensioni e i fatti vengono organizzati
in un modello intuitivo di facile utilizzo da
parte degli utenti
Database OLAP
Atlanta
Chicago
Denver
Grapes
Cherries
Melons
Apples
Detroit
Q1
Q2
Q3
Time Dimension
Q4
Database OLAP
• Il cubo consente di rappresentare in modo
intuitivo e maneggevole la dipendenza di
un fatto da 3 dimensioni
• L’ipercubo è una generalizzazione del cubo
su n dimensioni, con 1 <= n <= 
• Per semplicità, si usa fare riferimento al
“cubo” indipendentemente dal numero di
dimensioni
Database OLAP
• Ogni cella è un valore
• Il valore di ogni cella è l’intersezione tra
dimensioni
Database OLAP
Sales
Fact
Atlanta
Chicago
Denver
Grapes
Cherries
Melons
Apples
Dallas
Q1
Q2
Q3
Time Dimension
Q4
Database OLAP
• Le dimensioni ci permettono di “affettare”
a “dadi” il cubo
• Le gerarchie all’interno delle dimensioni
consentono di “trapanare” all’ interno del
cubo per scendere/salire nei
dettagli/aggregazioni (DrillDown/DrillUp)
• Può produrre report stampati, ma è prima
di tutto una funzionalità interattiva
• Consente di verificare velocemente ipotesi
formulate dall’utente
Analysis Services step by step
Analysis Server
• Analysis Server gestisce una base dati
multidimensionale per ottimizzare
l’accesso ai dati attraverso client OLAP
• Un database è un insieme di cubi
• Cubi di uno stesso database possono
condividere una o più dimensioni
• I cubi vengono alimentati con fonti
relazionali accessibili via OLE DB
Architettura Analysis Server
Server
Custom
Applications
Client
OLAP Manager
Client
Application
DSO
Client
Application
Source data
Relational
Database
ROLAP data
OLE DB
HOLAP
Cube
Data Storage
ADO MD
MOLAP
OLAP Server
PivotTable
Service
Analysis Manager
• Snap-In per MMC (Microsoft Management
Console)
• Consente di amministrare store e
processing multidimensionali
• Integra diversi editor specializzati e
wizard per progettare la soluzione
– Cube Editor
– Dimension Editor
Analysis Manager
Analysis Manager
• Passi necessari per creare un cubo
– Creare un data source (la fonte dati oledb)
– Eseguire il wizard
•
•
•
•
Selezionare la tabella dei fatti
Identificare le misure
Selezionare le tabelle delle dimensioni
Identificare le dimensioni
–
–
–
–
Star Schema
Snowflake Schema
Parent Child
Time dimension
• Processare il cubo
Processare il cubo
• E’ l’operazione che “popola” il cubo
– Trasferisce i dati dal Data Warehouse al
database OLAP
– Crea le aggregazioni e somma i dati
• ci sono alcuni “dettagli” da considerare (li
vediamo tra breve)
Analysis Manager
• Il nostro primo cubo !!!
– DEMO!!!
Caratteristiche di un
database Olap
Cube Storage Options
• Lo spazio rappresenta un problema
– I dati sono aggregati a più livelli
– Sono duplicati (ripetuti) all’interno del cubo
– All’aumentare delle dimensioni e misure le
richieste di spazio aumentano
esponenzialmente
Cube Storage Options
Fatturato
Home
Business
Totale
Desktop
70
50
120
Hardware
Laptop Server
30
15
5
45
5
Software
Totale Italiano Inglese
100
80
70
25
5
170
105
5
Totale
80
30
110
Totale
180
100
280
• Celle di dettaglio: 10 (di cui 2 vuote) (A)
• Celle di sintesi: 14! (B)
• Celle complessive: 24 (C)
• Rapporto (C)/(A): 2.4!
Numero di Aggregazioni
Cube Storage Options
• “Data Explosion !!!”
•70000
•65536
•60000
•50000
•40000
•30000
•20000
•16384
•10000
•0
•16
•2
•81
•3
•256 •1024
•4
•5
•4096
•6
•7
Numero di Dimensioni
(4 livelli in ciascuna dimensione)
•8
Cube Storage Options
“Data Sparsity”
Fatturato
Milano
Roma
Napoli
Torino
Venezia
Bologna
Aosta
Trieste
Ancona
Firenze
Bari
Palermo
Cagliari
Genova
Reggio C.
P200
134
PII266
PII300
100
100
80
60
134
70
100
Win98
45
30
10
80
23
100
45
134
70
45
234
70
30
10
80
23
100
234
70
30
Office97
134
70
234
70
100
80
30
10
80
23
100
80
60
134
PII400
80
234
100
80
60
134
PII350
134
70
45
134
70
WinNT
200
120
80
100
200
120
80
100
200
120
80
100
200
120
80
SQL/Srv
200
In generale, molte celle possono essere
vuote, soprattutto scendendo nei dettagli
200
200
200
Cube Storage Options
• MOLAP, ROLAP e HOLAP
– Cosa sono?
– Quali sono le prestazioni e l’efficienza
caratteristiche di ciascuno?
– Come scegliere?
Cube Storage Options
•Molap : Multidimensional OLAP
– I dati vengono caricati in OLAP
Services via OLE DB
– Sia i dettagli che le
aggregazioni sono memorizzati
nello store di OLAP Services in
formato nativo
– Stesso contenuto delle tabelle
ROLAP
– E’ molto efficiente, sfrutta
compressione e data sparsity
– Processo del cubo molto veloce
Cube Storage Options
•ROLAP : Relational OLAP
– Aggregazioni create nel RDBMS per
velocizzare le query
– Popolazione delle tabelle via
istruzioni SQL di tipo “INSERT INTO”
• Nessun dato nello store di OLAP
Services
– Indici creati automaticamente
– Le tabelle risultanti sono molto leggibili
– Supporta provider OLE DB
– Query piu’ lente
– Processo piu’ lento
– Risparmio spazio
– Per cubi Real Time
Cube Storage Options
•HOLAP - Hybrid OLAP : il compromesso
– La via di mezzo
• Mantiene i fatti in RDBMS
• Le aggregazioni sono nello
store MOLAP
– Evita la duplicazione dei dati
– Si perdono prestazioni quando
si deve accedere ai dettagli
– Tempi di processo simili a
MOLAP
– Perdo in termini di prestazioni
quando vado sui dettagli
Cube Storage Options
• IMPORTANTE !!!
• Aggregare al 100 % non e’ necessario:
– Aumenta tempo di processo e
richieste di spazio
– Cache !!!!!
– Regola 80/20
Cube Storage Options
• In pratica
– C’è il “solito wizard”
Dettagli sulle dimensioni
• La dimensione è composta da livelli e membri
– Ogni livello da dettaglio diverso (All,Bread
Bagels..)
– Membri sono figli di un livello
– Livello All è il totale generale
Dettagli sulle dimensioni
• Le dimensioni possono essere Shared o
Private
– Shared
• Condivise da più cubi
• Amministrate in unico punto (Dimension
Editor)
• Se ricostruisco la struttura, i cubi che la
condividono non sono disponibili
• Non possono essere trasformate in private
• Più semplice la manutenzione
Dettagli sulle dimensioni
• Le dimensioni possono essere Shared o
Private
– Private
• Usate solo da un singolo cubo
• Gestite all’ interno del Cube Editor
• Ricreate ogni volta che elaboro il cubo
• Non possono essere trasformate in Shared
Dettagli sulle dimensioni
• Proprietà delle dimensioni
– Cube editor per le Private, Dimension editor per le
Shared
– Member Key Column:
• Definisce i membri in un livello
• Possono essere definite con espressioni
– Member Name Column:
• Definisce il “nome” per i membri di un livello
• Possono essere definite con espressioni
– Sorting Member
• Definisce l’ordine all’ interno di un livello
• DEMO!!!
Dettagli sulle dimensioni
• Dimensioni parent child
– Classica tabella con autoreferenza
– Sono basate su due campi all’ interno della
stessa tabella
– Più lente perché calcolate a “runtime”
– “Members with data” gestisce legame con
tabella dei fatti
• Leaf Members Only
• Non-leaf Data Hidden
• Non-leaf Data Visible
• DEMO!!!
Dettagli sulle dimensioni
• Dimensione tempo
– Può essere creata in automatico con wizard a
partire da un campo data/stringa della tabella
dei fatti
– Se gestita come una normale tabella delle
dimensioni possiamo gestire altri dettagli
legate alla data es. festivo, prefestivo ecc.
– Può essere shared
– Occupa meno spazio !!!
• DEMO !!!
Dettagli sulle dimensioni
• Member Properties
– Possono avere altri campi nella tabella delle
dimensioni “interessanti” ma non abbastanza
da creare delle dimensioni
– Non occupano spazio
– Sono la base per creare Dimensioni Virtuali
Dettagli sulle dimensioni
• Dimensioni virtuali
– Sono basate sui membri virtuali
– Non hanno aggregazioni
– Ma non aumentano spazio e tempo di
elaborazione
– Più lente rispetto a normali dimensioni
– Ideali per analisi richieste da pochi utenti
• DEMO !!!
Mdx
•
•
•
•
Multi-Dimensional eXpression
Parte della specifica OLEDB for OLAP
Ora parte anche delle specifiche XML/A
Nasce per reporting e analisi
Mdx
• Lo usiamo per
• Query Statements
• Per costruire reports (“simile” a SQL)
• Formule
• Membri calcolati (simile a Excel)
• Management (cubi locali)
• Alter Cube, Update Cube, etc.
Mdx
• Mdx formule
• Nuove misure
• Nuovi membri
• Tuple based
• Average Price =
• [Sales Amount] / [Order Quantity]
• come riferimento Excel: =B5/B4
• Set based
• Year To Date =
• Sum(PeriodsToDay(PeriodsToDate([Order
Date].[Calendar Quarter]),[Order Quantity])
• Come Excel ranges: =Sum(B2:B10)
• ([Unit
Sales],ancestor(Product,[Product].[(All)]))
Mdx ogni cella ha un nome
([All Product],
[All State],
Dollars)
All Product
Dollars
Units
Bread
Dairy
Meat
(Bread, USA, Units)
(Meat,
Mexico,
Dollars)
Mdx
• Tupla
• Coordinate multidimensionali di una cella
• 1 membro da ogni dimensione
• Se piu’ dimensioni separo con ,
• Fa messa tra ()
• Se una dimensione non è specificata
usa membro di default o membro
corrente
Mdx
(State.CurrentMember,
Time.Calendar.CurrentMember,
Product.CurrentMember,
[Sales Units])
Mdx
• Set
– Insieme di tuple
Mdx
• MDX query
– È composta da 3 parti
– Dicers: assi (normalmente Column e Row)
– Data Grid – riempita dalla risoluzione degli
assi
– Slicers: Filtro
• SELECT
<Set> On Columns,
<Set> On Rows
FROM <Cube>
• WHERE <Slicers>
Mdx
• Funzioni (tra le piu’ usate)
– Order
– Head/Tail
– TopCount
– Filter
– Crossjoin
– Generate
Mdx
• Attributi aggiuntivi
– Hierarchize (ordina dimensione)
– NON EMPTY (toglie righe vuote)
– CELL PROPERTIES Formatted_Value;
Gestire la sicurezza in
Analysis Services
Gestire la sicurezza in Analysis Services
• Amministrativa
– La sicurezza e’ basata su Windows 2000 o
Windows NT 4.0 Security
– Per amministrare Analysis Services è
necessario far parte del gruppo Olap
Administrators, creato al momento
dell’installazione
– L’ autenticazione può essere effettuata tramite
HTTP (IIS autentication)
Gestire la sicurezza in Analysis Services
• Utenti
– Si basa sulla creazioni di ruoli all’interno di
Analysis Services
– Posso limitare l’utente a vedere singole
dimensioni, livelli, membri
– Consente di arrivare alla singola cella
all’interno del cubo
Gestire la sicurezza in Analysis Services
• Il ruolo viene creato a livello di database
dal Database Role Manager
• All’interno di ciascun cubo si possono
specificare i dettagli tramite il Cube Role
Manager
Gestire la sicurezza in Analysis Services
Gestire la sicurezza in Analysis Services
Gestire la sicurezza in Analysis Services
Deploy della soluzione
Deploying an OLAP Solution
• Aggiornamento dei cubi
– Full Process
• quando :
– viene creato
– Aggiungo,elimino o modifico una misura
– Rebuild di una dimensione shared
• conseguenze :
– Il cubo non e’ disponibile
– Elaborazione lunga
Deploying an OLAP Solution
• Aggiornamento dei cubi
– Incremental Update
• quando :
– Aggiungo nuovi dati al DW
• conseguenze :
– Non “costa molto” in quanto il cubo rimane
disponibile
Deploying an OLAP Solution
• Aggiornamento dei cubi
– Refresh
• quando :
– Ho sbagliato un aggiornamento e riparto da zero
– Necessità di dati da diverse data source
• conseguenze :
– Il cubo viene ricreato ma la sua struttura non
cambia
– il cubo rimane disponibile
Deploying an OLAP Solution
• Aggiornamento delle dimensioni (Shared)
– Rebuild
• quando :
– aggiungo, elimino un livello, rinomino o elimino un
membro oppure lo passo di livello
• conseguenze :
– Nessun cubo interessato è disponibile ed inoltre deve
prevedere un full process prima di diventarlo 
– Incremental Update
• quando :
– Nuovi membri o Member Properties
• conseguenze :
– I cubi rimangono disponibili
– I nuovi membri aggiunti legati a tabelle dei fatti non
ancora processate appariranno senza valore
Deploying an OLAP Solution
• Si può automatizzare
il tutto con DTS
tramite l’ Olap
Administrative Task
DEMO !!!
Deploying an OLAP Solution
• Backup e restore di un cubo
– Msmdarch utility a linea di comando per
backup e restore (possibilità di schedulare)
– Analysis Manager (pulsante destro del mouse)
– Crea file .cab
Deploying an OLAP Solution
• Per trasferire database da un server ad un
altro si può fare copia incolla !!!!
– DEMO !!!
Deploying an OLAP Solution
• Per installare PivotTable Services lato
client nel cd di sql i percorsi sono i
seguenti :
MSOLAP\Install\PTS\PTSFULL.EXE
MSOLAP\Install\PTS\PTSLITE.EXE
Le novità
• Reporting Services
– “Capiscono” mdx
• Demo
• XMLA (xml for analisys)
– Analysis Services diventa un web services
• ADOMD.NET
– Managed Provvider per dot net (ancora in beta)
• Yukon
– Grandi novità….
• Demo