Introduzione alla tecnologia Introduzione alla tecnologia OLAP: Microsoft SQL Analisys OLAP: Microsoft SQL Analisys
ServicesServices
Agenda
• Perchè mi serve il Data Warehouse e OLAP ?• Caricamento e trasformazione dei dati (DTS)
• Caratteristiche di un database Olap
• Analysis Services step by step
• Interrogare i cubi (Mdx e dintorni)
• Gestire la sicurezza in Analysis Services
• Deploy della soluzione
• Basi dati multidimensionali (OLAP)
Perchè mi serve il Data Warehouse e OLAP ?
Perchè mi serve il Data Warehouse e OLAP ?
• Dati = informazione non è sempre vero
• Spesso devono essere integrati con sorgenti di diversa natura
• Rallentano il sistema
• Non forniscono dati “attendibili”
• Sono organizzati per una elaborazione transazionale
Perchè mi serve il Data Warehouse e OLAP ?
• La soluzione è creare un db ad-hoc per le analisi
• Possiamo considerarlo come un db Read-Only
• Il disegno e’ ottimizzato per la lettura
• I dati sono trasformati / “puliti”
• Integra informazioni provenienti da sorgenti diverse
• Lo scopo è di “concentrare” tutti i dati dell’ azienda in un unico punto
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
ComuneComune ProdottoProdotto TempoTempo UnitàUnità FatturatoFatturato
Tabella delleDimensioni
Tabella delleDimensioniComuniComuniComuniComuni
ProdottiProdottiProdottiProdotti
TempoTempoTempoTempo
Tabella dei FattiMisureMisureMisureMisure
FattiFattiFattiFatti
DimensioniDimensioniDimensioniDimensioni
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 SchemaEmployee_DimEmployee_DimEmployee_DimEmployee_Dim
EmployeeKeyEmployeeKeyEmployeeID...
EmployeeID...
EmployeeKey
Time_DimTime_DimTime_DimTime_DimTimeKeyTimeKeyTheDate...
TheDate...
TimeKeyProduct_DimProduct_DimProduct_DimProduct_Dim
ProductKeyProductKeyProductID...
ProductID...
ProductKey
Customer_DimCustomer_DimCustomer_DimCustomer_DimCustomerKeyCustomerKeyCustomerID...
CustomerID...
CustomerKeyShipper_DimShipper_DimShipper_DimShipper_Dim
ShipperKeyShipperKeyShipperID...
ShipperID...
ShipperKey
Sales_FactSales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKey
TimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyRequiredDate...
RequiredDate...
TimeKey
CustomerKeyShipperKey
ProductKeyEmployeeKey
Multipart KeyMultipart KeyMultipart KeyMultipart Key
MeasuresMeasuresMeasuresMeasures
Dimensional KeysDimensional KeysDimensional KeysDimensional Keys
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 datimultidimensionali
(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
Detroit
Denver
Chicago
Atlanta
Q4Q1 Q2 Q3Time Dimension
CherriesGrapes
ApplesMelons
Produ
cts D
imen
sion
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
Q4Q1 Q2 Q3Time Dimension
Dallas
Denver
Chicago
Mar
kets
Dim
ensi
on
Apples
CherriesGrapes
AtlantaSales Fact
Melons
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
OLAP ManagerCustom
Applications
DSO
OLAP Server
Data Storage
ROLAP data
MOLAP
HOLAP
Source data
Cube
RelationalRelationalDatabaseDatabase
Client
ClientApplication
ADO MDClient
Application
OLE DB
PivotTableService
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
Hardware SoftwareFatturatoDesktop Laptop Server Totale Italiano Inglese Totale
Totale
Home 70 30 100 80 80 180Business 50 15 5 70 25 5 30 100Totale 120 45 5 170 105 5 110 280
• Celle di dettaglio:Celle di dettaglio: 10 (di cui 2 vuote) (A)10 (di cui 2 vuote) (A)• Celle di sintesi:Celle di sintesi: 14! (B)14! (B)
• Celle complessive:Celle complessive: 24 (C)24 (C)• Rapporto (C)/(A):Rapporto (C)/(A): 2.4! 2.4!
Cube Storage Options
• “Data Explosion !!!”
Numero di DimensioniNumero di Dimensioni
Nu
mer
o d
i A
gg
reg
azio
ni
Nu
mer
o d
i A
gg
reg
azio
ni
(4 livelli in ciascuna dimensione)(4 livelli in ciascuna dimensione)
•16 •81 •256 •1024•4096
•16384
•65536
•0
•10000
•20000
•30000
•40000
•50000
•60000
•70000
•2 •3 •4 •5 •6 •7 •8
Cube Storage Options
“Data Sparsity”Fatturato P200 PII266 PII300 PII350 PII400 Win98 Office97 WinNT SQL/SrvMilano 134 100 80 45 200 200Roma 100 234 134 120Napoli 80 70 30 70 80Torino 60 23 10 100Venezia 134 100 80 45 200 200Bologna 100 234 134 120Aosta 80 70 30 70 80Trieste 60 23 10 100Ancona 134 100 80 45 200 200Firenze 100 234 134 120Bari 80 70 30 70 80Palermo 60 23 10 100Cagliari 134 100 80 45 200 200Genova 100 234 134 120Reggio C. 80 70 30 70 80
In generale, molte celle possono essere vuote, soprattutto scendendo nei dettagli
Cube Storage Options
• MOLAP, ROLAP e HOLAP– Cosa sono?– Quali sono le prestazioni e l’efficienza
caratteristiche di ciascuno?– Come scegliere?
Cube Storage Options
– 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
•Molap : Multidimensional OLAP
Cube Storage Options
– 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
•ROLAP : Relational OLAP
Cube Storage Options
– 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
•HOLAP - Hybrid OLAP : il compromesso
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([Orde
r 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
Bread
Dairy
Meat
UnitsDollars
(Bread, USA, Units)(Bread, USA, Units)
([All Product], ([All Product], [All State],[All State],Dollars)Dollars)
(Meat, (Meat, Mexico,Mexico,Dollars)Dollars)
USA
Cana
da
Mexico
All S
tate
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])
(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)
• 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
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
Top Related