AS2000, MDX, partizioni, performance e ... alternativa al Profiler!

In questi ultimi giorni ho affrontato un problema di performance di un mio vecchio progetto abbastanza complesso. Interamente basato su Analysis Services 2000 e SQL Servrer 2000, è costituito da 34 cubi, 19 cubi virtuali e 212 (decentododici!!!) dimensioni condivise.

Tralasciando i dettagli tecnici che hanno di fatto imposto scelte architetturali che hanno prodotto questi numeri decisamente importanti, il problema che si è presentato è quello di performance totalmente insoddisfacenti nell'esecuzione di alcune query MDX.

Per inquadrare meglio il problema, va detto che i 34 cubi fisici sono in realtà 17 "coppie" di cubi identici dal punto di vista delle dimensioni (un cubo contiene una sola misura in "DistinctCount" e l'altro contiene tutte misure additive). Su ogni coppia di cubi è costruito un cubo virtuale che è poi il cubo al quale accedono gli utenti.

Ognuno dei 34 cubi fisici è partizionato per data e contiene dati a "stock" che si aggiornano mensilmente. La fase ETL è demandata all'esecuzione schedulata di VB script che, attraverso l'uso di Stored Procedure, si preoccupano del caricamento dei dati e del partizionamento del data mart di SQL Server (creando nuove tabelle fatti ad ogni nuovo caricamento ed eventualmente cancellando quelle "out of range"), della verifica della qualità dei dati (provenienti da estrazioni lato mainframe) e della registrazione in apposite tabelle di SQL Server (e su file) di tutti i log e le statistiche relativi alle azioni intraprese. Gli stessi script istanziano anche una ".dll" (scritta appositamente) che, utilizzando DSO, si fa carico del partizionamento dei cubi (creando una partizione per ogni tabella fisica) e dell'elaborazione di dimensioni e cubi (fisici e virtuali).

Tra i 19 cubi virtuali, ve ne sono due particolari che sono costruiti su più cubi fisici (una dozzina)! Le query in questione insistono proprio su questi ultimi ed i loro tempi di esecuzione spaziano da alcuni minuti a ... infinito (nel senso che nessuno ha mai avuto la pazienza di vedere se venivano effettivamente risolte). Va anche detto che queste query sono particolarmente complesse (molte decine di righe di codice) e contengono parecchio membri e set calcolati. Inoltre, per la tipologia dei dati in gioco, non possono sfruttare le aggregazioni dei cubi ma devono necessariamente fare una scansione dei dati contenuti nelle partizioni.

Dopo avere analizzato il codice ed eliminato diversi potenziali "colli di bottiglia", le prestazioni non sono migliorate in maniera significativa e comunque non per tutte le query.

Ci si è concentrati quindi su altri aspetti "esterni" e, dopo avere scartato diverse ipotesi, si è cominciato a sospettare che il partizionamento dei cubi non fosse corretto. O meglio, ci si è chiesti se effettivamente venissero utilizzate solo le partizioni utili alla risoluzione delle query.

Se il progetto fosse stato realizzato con SQL 2005 ed Analysis Services 2005 ci sarebbe venuto in soccorso il Profiler, ma con AS 2000 come si poteva fare?

Quello che noi sapevamo era dove (fisicamente) risiedono i file relativi alle partizioni dei cubi: nelle relative sottocartelle all'interno della cartella "Data" di Analysis Services.

Quello che (di fatto) volevamo sapere era "quali" di questi file venivano usati da Analysis Services per risolvere le query.

Quale strumento ci poteva aiutare a scoprirlo???   Il mitico FileMon di Mark Russinovich!!! (od eventualmente il suo successore Process Monitor).

Quindi, lanciato il programma, impostato come filtro il solo "msmdsrv.exe", avviata la registrazione degli eventi, lanciata la prima query e .... haha!!! venivano usate TUTTE le partizioni di TUTTI i cubi!!!!

Ci si è quindi accorti di un errore strutturale per cui lo slicer impostato nelle partizioni era incompleto e quindi insufficiente (ma solo per alcune situazioni particolari, guarda caso quelle delle query utilizzate!!!) a risolvere efficientemente le query MDX.

A questo punto è stato semplice (beh, insomma, quasi!!!) modificare la dll per correggere il problema e partizionare correttamente i cubi.

Morale... mi ero dimenticato quanto fosse "dura" la vita da "cubista" solo 5 anni fa (o se preferite solo una release fa)!!!