Per poter monitorare ed analizzare correttamente il lancio esecutivo di ogni query, sono state eseguite le seguenti operazioni per ogni test:
1. Scelta del modello da analizzare (Tabulare o Multidimenionale) e della relativa macchina server dove sono collocate e instanziate i rispettivi cubi precedentemente processati:
• Server con cubi TGK_FULL o TGK_LIGHT (Modello Tabulare) • Server con cubo TGK_M (Modello Multidimensionale)
2. Avvio del software di SQL Server Profiler, sulla macchina server selezionata utile per il tracciamento e monitoraggio delle query.
3. Avvio dal computer locale del software Microsoft Excel (64 bit) per la creazione e definizione di una connessione dati al cubo di SSAS (cubo tabulare o cubo multidimensionale) mediante la compilazione del Server Name e delle credenziali di accesso.
4. Definizione del report e della relativa query da analizzare tramite la gestione della Pivot Table su Microsoft Excel.
5. Esecuzione query su Excel.
6. Recupero dei tempi di esecuzione query da SQL Server Profiler.
7. Avvio del metodo di pulizia cache mediante file XML for Analysis in una istanza di SQL Server Management Studio con connessione dati al cubo analizzato.
La versione del software Microsoft Excel a 64bit è giustificata dal fatto che quella a 32bit limita l’utilizzo di soli 2GB di RAM sul computer locale, restituendo spesso errori legati alla memoria non disponibile durante la l’esecuzione di query complesse. La versione da 64Bit, invece, sfrutta tutta la RAM disponibile nella macchina locale (8GB) senza riscontrare problemi di esecuzione, recupero e di memorizzazione dati nei report finali.
Come detto precedentemente, nel modello multidimensionale, dopo
l’esecuzione delle query sul cubo, vengono mantenuti i risultati nella memoria cache e come tale più il cubo viene utilizzato (esecuzione di query sequenziali), migliori sono le prestazioni delle query nel tempo.
Per definire un confronto prestazionale più veritiero è stata applicato
precedentemente all’esecuzione di ogni query, uno script che disabilita la warm cache, assicurando che tale non distorca i risultati dei test. È utile usare uno specifico metodo definito “ClearCache” in un file XML for Analysis (XMLA) per svuotare e cancellare le cache creata in risposta ad una query MDX. Tale comando, viene eseguito in due step che consistono, nell'invalidare la cache quando il metodo “ClearCache” viene invocato e successivamente la ricezione dalla query come feedback di ritorno una volta eseguita.
Questo comporta una riduzione del consumo di memoria evidente solo dopo che la cache sia stata effettivamente svuotata.
Gli effetti dell’esecuzione della ClearCache variano a seconda che si stia utilizzando un modello tabulare o multidimensionale.
Per cancellare la cache è necessario:
1. Ottenere l’identificatore dell’oggetto di un cubo mediante valore ID relativo alle relative proprietà.
2. Eseguire la query, creando una query XMLA con il metodo “ClearCache” con una sintassi predefinita in Figura 8.0.
Figura 8.5: Metodo “ClearCache” in un file XMLA
Per i database multidimensionali, Analysis Services costruisce la cache nella “Formula Engine” e nel “Storage Engine” per i risultati delle query dove è di assoluta importanza l’eliminazione, mentre nei modelli tabulari sono
generalmente memorizzati in memoria, dove le aggregazioni e gli altri calcoli vengono eseguiti al momento dell’esecuzione di ogni query. Come tale, il comando ClearCache ha un effetto limitato ma non completamente nullo su progetti tabulari.
Per garantire un esatto confronto prestazionale, si è scelto di eliminare comunque la cache, per ogni esecuzione query, in entrambe le modellazioni, garantendo le singole esecuzioni atomiche e sequenziali.
I test sono stati condotti su due server identici e separati dotati di ben 64 GB di RAM, 1 TB di memoria secondaria e 8 CPU (Intel Xeon 2.5GHz) ciascuno con sistema operativo MS Windows Server 2012 a 64 bit.
<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object>
<DatabaseID>ID</DatabaseID> </Object>
L’analisi è condotta sui tempi di esecuzione, per lo svolgimento di ogni
interrogazione sottomessa. A partire dalla configurazione della query mediante Pivot Table di Excel è possibile generare ed eseguire query determinando i tempi di inizio e di fine query determinando la completa durata espressa in secondi mediante il tool SQL Server Profiler. Da notare, che il tempo di esecuzione non comprende il tempo di estrazione e caricamento dei dati utili per la visualizzazione finale dei report.
7.3 REPORTING DELLE PERFORMANCE
Con questa modalità si predispone il foglio Excel per la connessione al server OLAP specificando il cubo che si vuole analizzare (tabulare o
multidimensionale).
Completata la procedura di connessione, il software costruirà in automatico una Pivot Table parametrizzato con i dati presenti nel cubo.
Grazie alla gestione delle quattro aree della Pivot Table con l’inserimento dei relativi attributi dimensionali, è possibile definire e generare un set di query MDX utili per i nostri confronti esecutivi.
Nelle tabelle successive sono riportati i vari campi parametrizzati nella Pivot Table per un set di query e i relativi risultati dei tempi di esecuzione per ogni query, espressi in secondi, sia per la modellazione classica multidimensionale che per quella tabulare analizzando i diversi comportamenti esecutivi.
DESCRIZIONE
QUERY RIGHE COLONNE VALORI FILTRI
A:
Report degli importi effettivi, con viste temporali differenti in colonna (mensile bimestrale, etc..) raffigurate per entità, conti annessi e mesi dell’anno 2016. • COD_AZIENDA (dim. ENTITA’) • COD_CONTO (dim. CONTO) • PERIODO (dim. PERIODO) • LUNGHEZ ZA_PERIO DO (tutte le viste temporali) • AMOUNT (Misura) • COD_SCENARIO = 2016ACT (dim. SCENARIO)
Tabella 8.6: Query per analisi delle performance sulla granularità
Le query A, B, C, riportate in Tabella 8.6, sono eseguite senza la presenza di strutture gerarchiche e quindi analizzando il comportamento di esecuzione sulla granularità più bassa del modello ovvero sui membri foglia dei rispettivi attributi dimensionali (livello di dettaglio massimo).
"
"
Figura 8.7: Risultati dei tempi esecutivi per le query A, B e C B:
Report degli importi effettivi, con vista periodica mensile raffigurate per entità, conti annessi e mesi dell’anno 2016. • COD_AZIENDA (dim. ENTITA’) • COD_CONTO (dim. CONTO) • COD_PERIODO (dim. PERIODO) - • AMOUNT (Misura) • COD_SCENARIO = 2016ACT (dim. SCENARIO) • LUNGHEZZA_PE RIODO = ’01’ (vista periodica mensile) C:
Report degli importi effettivi dell’anno 2016 raffigurate per entità, conti annessi, mesi dell’anno e per vista periodica annuale e mensile. • COD_AZIENDA (dim. ENTITA’) • COD_CONTO (dim. CONTO) • COD_PERIODO (dim. PERIODO) • LUNGHEZZA_P ERIODO - • AMOUNT (Misura) • COD_SCENARIO = 2016ACT (dim. SCENARIO) • LUNGHEZZA_PE RIODO = ’01’ and ‘00’ (vista periodica mensile e bimestrale)
Come è possibile notare dai risultati, espressi in secondi nel grafico,
l’esecuzione della query A in modellazione tabulare su dati con un livello di dettaglio massimo, risulta essere molto più performate (circa 18 volte meglio) rispetto al modello multidimensionale che non riceve risposta entro i primi 30 minuti di esecuzione (1800 secondi) in presenza di query abbastanza
complesse in Figura 8.7, grazie all’introduzione della nuova tecnica di memorizzazione in modellazione tabulare “in-memory”. Entrambe le esecuzioni sono state effettuate su cubi con dati Online che Offline rispettivamente “Tgk_Full” e “Tgk_M”.
In Figura 8.8 si evidenzia l’ottima scalabilità ottenuta in modellazione tabulare, ovvero quanto si discosta la durata di esecuzione query, all’aumentare
progressivo dei dati. Questo è stato ottenuto confrontando le stesse esecuzioni QUERY A, B, C elaborati dal cubo tabulare in presenza di dati online/offline e dal cubo con soli dati online precedentemente processati (TGK LIGHT parziale e TGK FULL completo).
Figura 8.8: Risultati sulla scalabilità del modello tabulare (Query A, B e C)
Successivamente è stato possibile evidenziare il comportamento esecutivo in presenza di strutture gerarchiche, parametrizzando le query D ed E eseguite esclusivamente sulle gerarchie dimensionali CONTO ed ENTITA’ mentre D.1 ed E.1 sono le stesse query precedentemente descritte ma in presenza di importi relativi a viste temporali differenti (integrazioni con altre dimensioni del modello) riassunte in Tabella 8.9.
DESCRIZIONE QUERY RIGHE COLONNE VALORI FILTRI
D:
Report degli importi raffigurate gerachicamente per la dimensione entità
• COD_AZIENDA (Gerachia della dim. ENTITA’)
- • AMOUNT (Misura) - E:
Report degli importi raffigurate gerachicamente per la dimensione conto
• COD_CONTO (Gerarchia della dim. CONTO)
- • AMOUNT (Misura) -
Tabella 8.9: Query per analisi delle performance sulle gerarchie
In questo caso, in presenza delle strutture gerarchica sugli attributi
multidimensionali è possibile applicare metodi come il Drill-Down (aumento di dettaglio dei dati mediante l’aumento del livello di dettaglio di una delle dimensioni presenti, con la riduzione di livello in una gerarchia) o Roll-Up (riduzione di dettaglio dei dati mediante la riduzione del livello di dettaglio di una delle dimensioni presenti, con l’aumento di livello in una gerarchia) sul cubo, evidenziando i relativi tempi di esecuzione durante il passaggio da un livello di gerarchia ad un altro.
Data la rapida esecuzione, i risultati rappresentati in Figura 9.0 dell’esecuzione query D ed E sono espresse in millessimi di secondi mentre le query D.1 ed E. 1 sono espresse in secondi.
D.1:
Report degli importi con viste temporali differenti in colonna (mensile bimestrale, etc..) raffigurate
gerachicamente per la dimensione entità
• COD_AZIENDA (Gerachia della dim. ENTITA’) • LUNGHEZZA_ PERIODO (tutte le viste temporali) • AMOUNT (Misura) -
E.1 Report degli importi, con viste temporali differenti in colonna (mensile bimestrale, etc..) raffigurate gerarchicamente per la dimensione conto. • COD_CONTO (Gerarchia della dim. CONTO) • LUNGHEZZA_ PERIODO (tutte le viste temporali) • AMOUNT (Misura) -
"
"
"
Dati i relativi tempi di esecuzione delle query D, E, D.1, E.1 (in presenza o in assenza di ulteriori dimensioni) durante il passaggio da un livello di gerarchia ad un altro possiamo estrarre le seguenti considerazioni:
• Escludendo la gerarchia di livello più basso la scalabilità per entrambe le modellazioni risulta essere abbastanza buona.
• Il modello multidimensionale risulta essere più efficiente nelle gerarchie di livello più alto grazie alla presenza delle pre-aggregazioni dati in memoria ma risulta inefficiente nel livello più basso (foglia) ovvero di dettaglio massimo. • Il modello tabulare si comporta quasi sempre in maniera efficiente non
presentando picchi di rallentamenti esecutivi mantenendo una certa
progressione sul tempo di esecuzione da un livello di gerarchia ad un altro. Infine si è deciso di realizzare una query F, composta dalle gerarchie
dimensionali precedenti (per la dimensione CONTO ed ENTITA’), incrociando più dimensioni e rendendo la query più complessa per i nostri scopi di analisi raffigurata nella seguente Tabella 9.1
Tabella 9.1: Query per analisi delle performance generali
DESCRIZIONE
QUERY RIGHE COLONNE VALORI FILTRI
F: Report degli importi effettivi, con vista periodica mensile raffigurate gerarchicamente per le dimensioni Entità e Conto e per mesi dell’anno 2016.
• COD_AZIENDA (Gerachia della dim. ENTITA’) • COD_CONTO
(Gerarchia della dim. CONTO) • COD_PERIODO (dim. PERIODO) • AMOUNT (Misura) • COD_SCENARIO = 2016ACT (dim. SCENARIO) • LUNGHEZZA_PERI ODO = ’01’ (vista periodica mensile)
Figura 9.2: Risultati delle performance sulla query generale
In quest’ultimo caso, la query è costituita dalla presenza delle gerarchie conto ed entità e da ulteriori attributi dimensionali differenti. È possibile ricondurre tale query come un classico esempio di esecuzione da parte del cliente in fase finale di utilizzo del modello dal software CPM.
Si può evidenziare, dai risultati in Figura 9.2 espressi in secondi, il passaggio da un livello di gerarchia ad un altro per la gerarchia entità e successivamente per la gerarchia conto.
Come è possibile osservare dal grafico, il modello tabulare evidenzia risultati generalmente migliori in termini di efficienza rispetto alla modellazione
multidimensionale poiché l’ottimizzazione applicata in modellazione tabulare è più efficace rispetto all’ottimizzazione applicata in modellazione
multidimensionale in presenza di dati aggregati, gerarchie e da complessi incroci dimensionali.
Per questi motivi possiamo concludere che in istanze comuni di esecuzione (come in Tabella 9.1) la nuova modellazione di tipo tabulare, realizzato appositamente per soddisfare l’esigenza problematica del cliente relativa al rallentamento durante l’esecuzione query, risulta una soluzione efficace ed efficiente per i nostri obiettivi di partenza.
8 CONCLUSIONI
L’attività di stage svolta, ha richiesto una mia partecipazione attiva dandomi la possibilità di inserirmi in un gruppo di lavoro ben preparato e organizzato, e di seguire, in prima persona, l’evoluzione di una soluzione di business
intelligence, dallo studio del problema fino alla sua completa realizzazione. Il motivo che mi ha portato alla scelta di questo progetto, è principalmente l’interesse che ho verso le tematiche che riguardano la “business intelligence” in genere. Il percorso evolutivo di questo lavoro è iniziato con lo studio del problema ricercando informazioni sul “cosa” fosse già stato fatto ed
implementato in passato e le scelte di sviluppo adottate come soluzione finale. A partire dal modello correntemente utilizzato nell’azienda ospitante si è cercato di proporre una soluzione/modello più efficiente per gli utilizzi finali del cliente. Tale soluzione è stata definita applicando le conoscenze sui concetti di modellazioni Microsoft SSAS tabulare e multidimensionale, riscontrando relative problematiche di sviluppo e applicando le opportune operazioni di ETL risolutive per la migrazione da un modello all’altro. La progettazione tabulare, in fase di ETL, è risultata più semplice da gestire nelle prime fasi di definizione strutturale nel modello e più complessa nelle fasi di definizione della misura in presenza di relazioni NaN non supportate. Il linguaggio DAX adottato in progetti tabulari è meno intuitivo rispetto al linguaggio MDX in presenza di modelli strutturalmente più complessi da gestire. Le ultime fasi del progetto hanno riguardato le analisi delle performance finali prima e dopo l’applicazione della soluzione adottata. Dai risultati si deduce che il modello tabulare è migliore, in termini di efficienza, rispetto al modello multidimensionale non presentando picchi di rallentamento evidenti in qualsiasi tipologia di istanza query anche in presenza di gerarchie (punto dolente della modellazione tabulare a causa della assenza di pre-raggruppamenti). Visti i buoni risultati, si potrà pensare di convalidare tale
soluzione interamente nel software “Corporate Performance Management” dell’azienda ospitante.
Questo lavoro è stato molto interessante perché ha arricchito le mie conoscenze riguardo alle tematiche dell’innovazione tecnologica e della business intelligence e si è rivelato un buon esempio del fatto che in determinati contesti aziendali, grazie all’innovazione tecnologica, è
consigliabile sviluppare un modello completamente da zero con tecniche e strumenti di nuova generazione senza dover applicare costantemente metodi riparativi e di ottimizzazione delle risorse ad un modello ormai datato. Tale progetto di tesi può essere utilizzato come riferimento per tutte quelle persone e aziende che hanno intenzione di cambiare tipologia di modellazione Analysis Service per progetti futuri, determinando le scelte di sviluppo più opportune in base al proprio contesto lavorativo.
Generalmente, escludendo particolari contesti specifici di utilizzo, se ci si stà avviando ad una nuova soluzione OLAP con tool Microsoft, l’opzione migliore è quella di orientarsi verso la nuova modellazione di tipo tabulare.
9 ASPETTATIVE FUTURE
Rivedendo le pubblicazioni software passate da Microsoft, a partire dalla versione 2008 in poi non sono state aggiunte caratteristiche importanti per la modellazione multidimensionale mentre vengono fornite più funzioni per quella tabulare, in più ques’ultima è anche strettamente correlata al tool Excel e l’azienda proprietaria sta cercando di affermarsi la quota di Business
Intelligence self-service come leader di mercato con strumenti come PowerPivot e PowerBI.
Quindi si può dedurre un investimento maggiore per la nuova modellazione, per competere con altre tecnologie in-memory come SAP HANA, Tableau, QlikView ecc. È interessante considerare gli effetti dell’integrazione di queste tecnologie, capire come ottenere il meglio, sperando che Microsoft continuerà a investire sul lato server. Per il futuro sul linguaggio query adottato, possiamo dire che il linguaggio MDX è paragonabile alla durata del linguaggio C++. Oggi, molte persone usano il C# ma in alcune applicazioni, C++ risulta essere l’unica opzione disponibile. Inoltre, MDX è utilizzato da altri prodotti e da un certo numero di aziende esterne (Pentaho, SAP HANA etc..), mentre DAX è utilizzato solo da prodotti Microsoft (Power Pivot e SSAS Tabular).
DAX è un linguaggio interessante, molto più semplice e facile da imparare rispetto al linguaggio MDX per le persone con un background linguistico SQL e poiché stiamo ancora parlando di una base DAX 1.0, ricordando l’evoluzione del linguaggio MDX tra il 2005 e il 2009 dove si aggiunsero molteplici
caratteristiche interessanti, possiamo dire che DAX è ancora in una fase di alta crescita. Dal lato di sviluppo, non ci si aspettano grandi cambiamenti nel linguaggio MDX, ma si spera ad una migliore integrazione tra i due linguaggi sia in modellazione Multidimensionale che Tabulare. Ad esempio, sarebbe interessante poter scrivere una espressione DAX per una misura MDX in un modello Multidimensionale. Possiamo immaginare la potenza di scrivere un
gerarchici definiti in un cubo. Molto potente, non facile da padroneggiare, ma davvero interessante per certe condizioni e contesti di sviluppo.
Possiamo aspettarci che Microsoft continui a lavorare ad una possibile fusione tra le due modellazioni nelle prossime versioni anche se risultano molto differenti a livello strutturale oppure al miglioramento e all’investimento della singola modellazione ovvero migliorando il modello tabulare con l’inserimento di funzionalità multidimensionali ancora non supportate oppure migliorando la modellazione multidimensionale con le tecniche di compressione e di memorizzazione tabulare, questa scelta di investimento univoco ovviamente dipenderà da quanto le due modellazioni vengono utilizzate nel tempo da aziende di consulenza.
10 BIBLIOGRAFIA
[Scheer 06] A.-W., Jost, W., Heß, H., Kronz, A. (2006) Springer
“Corporate Performance Management”
[Albano 17] [Ruggieri 17] A.Albano, S.Ruggieri (2017) Università di Pisa
“Decision Support Database Essentials”
[Ballard 16] Chuck Ballard (2016) IBM Corp.
“Dimensional Modeling: In a Business Intelligence Environment”
[Russo 12] Marco Russo (2012) Microsoft
“Microsoft 2012 SSAS The BISM Tabular Model” “Tabular Modeling in Microsoft SSAS”
[Reference 12] Microsoft (2012)
“Data Analysis Expressions (DAX) Reference” “Multidimensional Expressions (MDX) Reference”
[Multidimensional 17] SSAS Multidimensional (2017) Microsoft
<https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional- models/multidimensional-models-ssas>
[Tabular 17] SSAS Tabular (2017) Microsoft
<https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/ tabular-models-ssas.>
11 RINGRAZIAMENTI
Questa tesi di laurea coincide con il raggiungimento di uno dei miei più
importanti obiettivi della mia vita. Desidero ringraziare quindi tutti coloro che hanno permesso e favorito, il raggiungimento di tale obiettivo.
Innanzitutto il prof. Salvatore Rinzivillo, relatore di questa tesi, per la grande disponibilità e cortesia dimostratemi, e per tutto l’aiuto fornito durante la stesura. Desidero, ringraziare il tutor aziendale Francesco Morini per avermi dedicato numerose ore per la mia formazione, CCH Tagetik che mi ha dato l’opportunità di svolgere lo stage aziendale.
Un pensiero ed un ringraziamento significativo va anche alla mia famiglia. I miei genitori sono stati speciali nel sostenermi sempre e comunque. Ringrazio mio fratello Vito, mia cognata Imma e il piccolo Francesco, tre pezzi della mia vita importanti e fondamentali che hanno saputo sostenermi e gli sono
riconoscente per tutto l’affetto dimostratomi.
Infine un ultimo ringraziamento, ma non per questo meno sentito, va ai miei amici, colleghi ed in particolare a Hekmat Ghanem che mi è sempre stato vicino durante questi anni universitari da studente fuori sede, aiutandomi nei momenti difficili e creando sempre nuove occasioni per ridere e stare insieme.