May 23, 2015 - SQL Saturday

Tabular vs. Multidimensional in
Analysis Services
Alberto Ferrari
@FerrariAlberto
www.sqlbi.com
May 23, 2015
#sqlsatTorino
#sqlsat400
Sponsors
May 23, 2015
#sqlsatTorino
#sqlsat400
Organizers
May 23, 2015
#sqlsatTorino
#sqlsat400
Alberto Ferrari




Consulente e trainer su Business Intelligence
Autore di libri
SSAS Maestro – MVP – MCP
www.sqlbi.com
May 23, 2015
#sqlsatTorino
#sqlsat400
Agenda





Funzionalità a confronto
Prestazioni e hardware
Scalabilità e manutenibilità
Compatibilità
Licensing
May 23, 2015
#sqlsatTorino
#sqlsat400
UDM esordì nel 2005
 C’era una volta UDM
 Unified Dimensional Model
 Un solo tool di modellazione per la BI
Un modello per domarli
May 23, 2015
#sqlsatTorino
#sqlsat400
2012: BI Semantic Model
 UDM non c’è più, lunga vita a BISM
 Business Intelligence Semantic Model
 Un solo tool per la modellazione BI
 Diviso in due tipologie
 Tabular
 Multidimensional
 Due tecniche di modellazione
 Nello stesso prodotto
May 23, 2015
#sqlsatTorino
#sqlsat400
BI Semantic Model: Vision
Third-party
applications
Databases
May 23, 2015
Reporting
Services
Power
View
LOB Applications
Excel
Files
PowerPivot
OData Feeds
SharePoint
Insights
Cloud Services
#sqlsatTorino
#sqlsat400
Tabular: il nuovo modello
 Parte della vision di BISM
 Super veloce con xVelocity in-memory
engine
 Altissimo rapporto di compressione dei dati
 Basato sul modello relazionale
 Programmabile con DAX
 A confronto con OLAP
 Quasi sempre più veloce. Molto più veloce.
 Più facile da imparare e usare
May 23, 2015
#sqlsatTorino
#sqlsat400
È l’approccio giusto?
Sono xVelocity,
il tuo nuovo motore
Non avrai altro motore al
di fuori di me
May 23, 2015
#sqlsatTorino
#sqlsat400
O è meglio investigare?
Ora ho due motori
Quali sono le loro capacità?
Quando dovrei scegliere uno
piuttosto che l’altro?
May 23, 2015
#sqlsatTorino
#sqlsat400
Cosa è xVelocity in-memory?




In-memory database
Basato su metodologia relazionale
Column oriented database
Dati memorizzati in formato compresso
 Anche in memoria
 Dati su disco sono solo un backup
May 23, 2015
#sqlsatTorino
#sqlsat400
Row Storage Layout
Tabella Customers
Niente di nuovo. Questo è lo standard dei database che hanno
implementato un layout di tabelle su disco sin dagli anni ’70.
Tecnicamente prende il nome di “row store”
May 23, 2015
#sqlsatTorino
#sqlsat400
Column Storage Layout
Customers Table
ID
Name
Address
City
State
Bal Due
1
Bob
…
…
…
3,000
2
Sue
…
…
…
3
Ann
…
…
4
Jim
…
5
Liz
6
ID
Name
Address
City
State
Bal Due
500
1
Bob
…
…
…
3,000
…
1,700
2
Sue
…
…
…
500
…
…
1,500
3
Ann
…
…
…
1,700
…
…
…
0
4
Jim
…
…
…
1,500
Dave
…
…
…
9,000
5
Liz
…
…
…
0
7
Sue
…
…
…
1,010
6
Dave
…
…
…
9,000
8
Bob
…
…
…
50
7
Sue
…
…
…
1,010
9
Jim
…
…
…
1,300
8
Bob
…
…
…
50
9
Jim
…
…
…
1,300
Tabelle memorizzate “per colonna”, tutti i valori di una
colonna in un solo oggetto
May 23, 2015
#sqlsatTorino
#sqlsat400
Column vs Row Storage
 Column Storage
 Accesso veloce a singole colonne
 Tempo necessario per materializzare righe
 Si paga in CPU per ridurre I/O
 Row Storage
 Accesso veloce a singole righe
 Non è necessaria nessuna materializzazione
 Si paga in I/O per ridurre CPU
May 23, 2015
#sqlsatTorino
#sqlsat400
Run Length Encoding (RLE)
Quarter
ProdID
Price
Quarter
Start
Count
Price
Q1
1
100
Q1
1
310
100
Q1
1
120
Q2
311
290
Q1
1
315
…
…
…
Q1
1
100
Q1
1
315
Q1
2
198
…
2
450
Q2
2
320
Q2
…
320
315
100
315
ProdID
Start
Count
1
1
5
2
6
3
…
…
…
51
5
150
56
3
256
Q2
1
150
1
Q2
1
256
2
Q2
1
450
Q2
1
192
Q2
1
184
Q2
2
310
Q2
2
251
…
2
266
May 23, 2015
120
198
450
320
320
450
Compressione RLE applicata solo
se la dimensione dei dati
compressi è inferiore all’originale
192
184
310
251
266
#sqlsatTorino
#sqlsat400
Dictionary Encoding
Quarter
Quarter
Q1
0
Q1
0
Q1
0
Q1
0
Q2
1
Q2
1
…
…
Q2
1
Q3
Q3
DISTINCT
2
2
Q3
2
Q3
2
Q4
3
Q4
3
Q4
3
Q4
3
…
…
May 23, 2015
Quarter
Quarter
0
Q1
1
Q2
2
Q3
3
Q4
R.L.E.
Solo 4 valori.
Bastano 2 bit per
rappresentarli tutti.
xVelocity Store
Quarter
Start
Count
0
1
4
1
5
10
2
11
4
3
15
15
#sqlsatTorino
#sqlsat400
xVelocity in-memory Compression
 Dictionary Encoding
 Interviene quasi sempre (eccezione: valori interi)
 Rende tabelle indipendenti dal tipo di dati
 RLE Encoding
 Solo se i dati compressi sono più piccoli
dell’originale
 Dipende molto dall’ordine dei dati
 SSAS sceglie automaticamente l’ordinamento
migliore
 In finestre di dati da 8Mb
May 23, 2015
#sqlsatTorino
#sqlsat400
Limitazioni di Tabular
 Il database risiede completamente in
memoria
 Funzionalità mancanti
 Non c’è supporto per le traduzioni
 Non c’è il concetto di «Set»
 MDX Script non è disponibile
 Solo relazioni 1-molti (m2m in arrivo, già
presenti in Power BI)
May 23, 2015
#sqlsatTorino
#sqlsat400
Distinct Count
 In Multidimensional sono misure
costosissime
 Measure group dedicato
 Necessario partizionamento
 In base al numero di core
 Disegno ottimizzato per l’hardware
 Scarse prestazioni per process
 ORDER BY durante il process della tabella dei
fatti
May 23, 2015
#sqlsatTorino
#sqlsat400
Distinct Count
 In Tabular sono misure semplici
 Distinct Count su qualsiasi colonna
 Dictionary encoding aiuta molto
 Calcoli in memoria
 Nessun process speciale per Distinct Count
May 23, 2015
#sqlsatTorino
#sqlsat400
Tool Dimension
 Tecnica comune in Multidimensional
 Tool dimension modifica i calcoli
 Interviene su misure esistenti
 Usato spesso per dimensione Data
 Tecnica basata su
 [Measures].CurrentMember
 SCOPE
 Funzionalità non disponibile in DAX…
May 23, 2015
#sqlsatTorino
#sqlsat400
Gerarchie Parent / Child
 Multidimensional supporta gerarchie P/C
 Ma sono lente
 Si ottimizzano trasformandole in gerarchie
normali
 P/C Naturalization
 Tabular supporta solo gerarchie regolari
 Le parent/child vanno sempre convertite
 Proprietà HideMemberIf non disponibile
 Si può simulare in DAX
 Non è semplice ma funziona correttamente
May 23, 2015
#sqlsatTorino
#sqlsat400
Unary Operator
 Multidimensional supporta unary operator
 Usati spesso in gerarchie P/C
 Calcoli molto lenti
 Usati su cubi finanziari
 Su dati pre-aggregati
 Interfaccia utente molto comoda per la loro
definizione
 Tabular non supporta unary operator
 Implementare calcoli in normali misure DAX
 Ma le formule diventano molto complesse
May 23, 2015
#sqlsatTorino
#sqlsat400
Custom Rollup
 Estende il concetto di unary operator
 Calcoli basati sui dati
 Basse prestazioni durante i calcoli
 Va bene su piccoli cubi finanziari
 O per implementare conversione valuta
 Non c’è supporto in Tabular
 Calcoli data-driven non implementabili in DAX
 Ma la conversione delle valute può essere
anche più veloce rispetto a Multidimensional
May 23, 2015
#sqlsatTorino
#sqlsat400
Uso gerarchie
 Gerarchie in Multidimensional
 Uno dei pilastri della modellazione
 Es.: «Incidenza % rispetto a parent nella gerarchia»
 Gerarchie in Tabular
 Elenco di colonne
 Non ci sono funzioni per gestirle in DAX
 Il calcolo del rapporto rispetto al parent diventa
complesso
 Comunque gli sviluppatori, conoscendo il
modello, riescono ad adattarsi a questa
limitazione
May 23, 2015
#sqlsatTorino
#sqlsat400
Calcoli a livello foglia
 Diversi scenari
 Aggregazioni ponderate
 Conversione valute
 Facili da calcolare in DAX
 Molto più veloci che in MDX
 Non richiedono modifiche al modello dati!
May 23, 2015
#sqlsatTorino
#sqlsat400
Script MDX non disponibile
 Uso di Script MDX
 Semplici calcoli
 Valutazioni complesse tramite SCOPE
 Business logic completa in alcuni modelli
 È corretto memorizzare la business logic in
MDX?
 No!
 A volte è necessario
 In quei casi, DAX non è l’opzione migliore
 Provare a spostare la business logic nell’ETL
May 23, 2015
#sqlsatTorino
#sqlsat400
Prestazioni a confronto
Tabular
•
•
•
•
•
Scansioni complete
Uso intensivo CPU e cache L2
Cache solo per storage engine
Collo di bottiglia: velocità bus RAM
Ottimizzazioni
• Modellazione per aumentare
compressione e diminuire RAM
usata
• DAX per diminuire scansioni e
diminuire carico da formula engine
May 23, 2015
Multidimensional
•
•
•
•
Pre-aggregazioni
Cache gerarchica su storage engine
Cache anche su formula engine
Accesso I/O random frequente
• Usa cache sistema
• Ottimizzazioni
• Preaggregazioni
• Dischi SSD per migliorare I/O
• MDX per ridurre uso formula
engine
#sqlsatTorino
#sqlsat400
Quale hardware per Analysis Services?
Multidimensional
Tabular
RAM
Media (16/32 Gb)
Grande (64/128 Gb)
RAM Velocità
Importante
Cruciale
Numero di core
4/8/16
4/8/16
Velocità CPU
Importante
Fondamentale
Velocità disco
Molto importante
Inutile
Disco SSD
Fortemente consigliato
Inutile
Velocità rete (verso client)
Importante
Importante
Concorrenza/scalabilità verticale
Molto buona
Poca esperienza per dirlo…
Virtualizzazione
Fattibile sfruttando preaggregazioni
Vale la pena? Rapporto
costi/benefici discutibile
May 23, 2015
#sqlsatTorino
#sqlsat400
SQL Server Appliances
SQL Engine appliance
PowerPivot appliance
May 23, 2015
#sqlsatTorino
#sqlsat400
Scalabilità
 Scalabilità verticale
 Non serve aumentare numero core con Tabular
 Collo di bottiglia è la RAM
 Più RAM per Multidimensional per contenere
cache, CPU più veloci per Tabular
 Scalabilità orizzontale
 Approccio analogo tra Tabular e Multidimensional
 Diversa modalità di aggiornamento incrementale
May 23, 2015
#sqlsatTorino
#sqlsat400
Manutenzione
 Più alta in Multidimensional
 Usage-based optimization per nuove
aggregazioni
 Ridotta in Tabular
 Valutare ricostruzione dizionari in caso di
aggiornamenti incrementali e partizionamento
May 23, 2015
#sqlsatTorino
#sqlsat400
Compatibilità
 Client OLAP/MDX esistenti funzionano con
Tabular
 Difficile migrare report esistenti
 Non si può ricreare un modello in Tabular con le
stesse entità di Multidimensional
 Differenze nei dettagli, es. chiavi su attributi
 Nuovi client DAX (Power View) solo su
Tabular
 Power BI è Tabular…
May 23, 2015
#sqlsatTorino
#sqlsat400
Licensing
 Standard
 SSAS Multidimensional, no Tabular
 Funzioni limitate in Multidimensional (es. no partizioni,
…)
 Business Intelligence
 Per Server, richiede CAL
 Tutte le funzionalità per Multidimensional e Tabular
 No partizioni e no columnar storage in SQL Server
 Enterprise
 Per Core, nessuna CAL
 Tutte le funzionalità per Multidimensional e Tabular
 Include partizioni e columnar storage in SQL Server
May 23, 2015
#sqlsatTorino
#sqlsat400
Bisogna migrare da Multidimensional?
 Non è necessario
 Opzioni diverse di modellazione
 Colonne calcolate
 Relazioni complesse in DAX
 Tecniche di calcolo differenti
 Evaluation Context
 Porting implica creazione di un nuovo
progetto
May 23, 2015
#sqlsatTorino
#sqlsat400
Multidimensional: The BI Path
Complessità
del modello
di dati
Visual Studio
SSAS
Multidimensional
Funzionalità richieste
May 23, 2015
#sqlsatTorino
#sqlsat400
Tabular: The BI Path
Complessità
Visual
Studio
del modello
di dati
EXCEL
SSAS Tabular
PowerPivot
for
SharePoint
PowerPivot
for Excel
Funzionalità richieste
May 23, 2015
#sqlsatTorino
#sqlsat400
Ricapitolando
 Tabular non è il nuovo UDM
 È una delle opzioni di modellazione per BISM
 Side by side con Multidimensional
 DAX è più semplice da imparare e usare rispetto
a MDX
 Nessuna esperienza con SSAS? Usa Tabular
 Nuovo progetto? Considera Tabular come
un’opzione
 Attenzione alle limitazioni
 Bisogna imparare DAX prima di cominciare
 Diverse tecniche di modellazione
May 23, 2015
#sqlsatTorino
#sqlsat400
Prossimo passo?
 Imparare DAX
 Davvero, è importante!
 DAX è semplice, ma non è facile
 Imparare diverse tecniche di modellazione
 Dedicare del tempo per conoscere Tabular
 Costruire qualche prototipo
 Non fare un porting, costruire un nuovo progetto
 Imparare a pensare in DAX
May 23, 2015
#sqlsatTorino
#sqlsat400
#sqlsatTorino
#sqlsat400
SPEAKERSCORE
http://speakerscore.com/MK1T
THANKS!
May 23, 2015
#sqlsatTorino
#sqlsat400