Storage Engine: il suo ruolo nellottimizzazione delle query DAX in LuckyTemplates

In questo tutorial, daremo un'occhiata al secondo motore all'interno dei servizi di analisi: il motore di archiviazione.

Abbiamo discusso del motore di primo livello, il motore delle formule , in un precedente tutorial. Quando gli utenti capiscono come funzionano entrambi questi motori, è più facile ottimizzare e migliorare le prestazioni delle query DAX.

Lo scopo principale del motore di archiviazione è lavorare direttamente con il database.

Il motore delle formule non ha accesso diretto al database, quindi normalmente passa attraverso il motore di archiviazione per questo scopo.

Il motore di archiviazione è disponibile in due tipi: modalità di importazione e DirectQuery . È possibile combinare entrambi i tipi nello stesso modello di dati per creare un modello composito.

Sommario

• Lavorare con la modalità di importazione nel motore di archiviazione
• Utilizzo di DirectQuery nel motore di archiviazione
• Utilizzo di modelli compositi
• Altri punti critici sullo storage engine
• Lavorare su query complicate
• Conclusione

Lavorare con la modalità di importazione nel motore di archiviazione

Innanzitutto, parliamo della modalità di importazione. Questo è anche più comunemente noto come Vertipaq, ma è anche chiamato xVelocity o In Memory Columnar Database.

Ci sono quattro cose fondamentali da capire su come funziona la modalità di importazione.

Innanzitutto, Vertipaq crea una copia dei dati direttamente dall'origine dati e la memorizza nella RAM in formato compresso .

In secondo luogo, i dati elaborati nella modalità di importazione si basano sull'ultima operazione di aggiornamento . Ciò significa che se hai aggiornato i tuoi dati l'ultima volta la settimana scorsa, i dati con cui hai a che fare sono ancora gli stessi dati della settimana scorsa. Ciò è particolarmente importante se si utilizza una configurazione composita in cui una tabella è in modalità di importazione e l'altra tabella è in modalità DirectQuery.

Supponiamo che tu abbia la tabella Prodotti, che è stata aggiornata la scorsa settimana, in modalità di importazione. Per quanto riguarda la tabella Sales, hai deciso di utilizzarla tramite DirectQuery a causa delle sue dimensioni. Supponiamo inoltre che tu stia creando il tuo report basato su entrambe le tabelle in cui stai inserendo la colonna Brand e creando una misura Total Sales sulla stessa tabella Sales. Vuoi anche visualizzare l'importo delle vendite in base al marchio.

Dovresti prima aggiornare i dati provenienti dalla modalità di importazione perché potresti ritrovarti con dati nuovi e aggiornati da DirectQuery e dati obsoleti da Vertipaq. Questo lascerà alcune righe vuote sulla matrice e sulla visualizzazione.

La prossima cosa che devi sapere su Vertipaq è che solo le operazioni di base come , , , o sono nativamente disponibili . Ciò significa che se nel piano di query sono incluse altre operazioni più complicate, il motore di archiviazione dovrebbe chiamare il motore delle formule per risolvere questa parte del codice.

Infine, lo storage engine è un database altamente ottimizzato grazie alla struttura a colonne di Vertipaq . Ciò significa che tutti i dati vengono archiviati colonna per colonna e non riga per riga. A causa di questa struttura, Vertipaq sarà sempre più veloce di una connessione DirectQuery anche se crei indici nel tuo modello di dati relazionale.

Utilizzo di DirectQuery nel motore di archiviazione

La prossima opzione che abbiamo all'interno dei servizi di analisi di LuckyTemplates è DirectQuery. Se utilizzi la connessione DirectQuery, i servizi di analisi fungono solo da pass-through per le query inviate dal motore delle formule.

Quindi diciamo che scrivi una query. Il motore delle formule genererà un piano di query. Successivamente inoltrerà la query allo storage engine, già tradotta nella lingua madre del database. La maggior parte delle volte, queste query arrivano in SQL.

Se la query usa DirectQuery, aspettati che sia sempre aggiornata. Non è necessario preoccuparsi dell'ultimo aggiornamento dei dati.

Il processo di ottimizzazione del codice DAX e del modello di dati dipenderà anche da come è stato creato il database relazionale. Se hai indici nelle tue colonne, le tue query saranno sempre ottimizzate. Ma se il tuo database non è ottimizzato in termini di creazione di report utilizzando i servizi di analisi o LuckyTemplates, potresti dover affrontare alcuni problemi di prestazioni. Quindi sii intenzionale nel creare i tuoi database per lo sviluppo di report.

Utilizzo di modelli compositi

La terza opzione consiste nel creare un modello composito in modo da avere una tabella in modalità di importazione e un'altra tabella in DirectQuery.

Quando utilizzi due tabelle da origini diverse, il motore delle formule invierà una richiesta a Vertipaq e un'altra richiesta all'origine dati DirectQuery. In entrambi i casi, i servizi di analisi recupereranno anche la cache dei dati sia da Vertipaq che da DirectQuery. Il motore delle formule utilizzerà quindi JOIN, o qualsiasi iterazione su entrambe le cache di dati prima di fornire i risultati all'utente finale.

Supponiamo quindi che tu stia tentando di visualizzare l'importo delle vendite in base alla marca del prodotto nel tuo rapporto. Il motore di formula invierà una richiesta a Vertipaq, dove recupererà il prodotto, il marchio e la chiave del prodotto. Quindi, da DirectQuery, proverà a recuperare le vendite, il prezzo netto, la quantità di vendita e il codice Product Key delle vendite.

Una volta che ha le due cache di dati basate sulla chiave del prodotto, unirà le due cache di dati e calcolerà l'importo totale delle vendite. Presenterà quindi i risultati all'utente finale.

Altri punti critici sullo storage engine

Ora che abbiamo coperto i suoi diversi tipi, ci sono alcuni altri fattori critici che devi conoscere sul motore di archiviazione per aiutarti a ottimizzare le tue query DAX.

Come accennato in precedenza, il motore di archiviazione fornisce la cache dei dati al motore delle formule sotto forma di cache dei dati non compressi. Quando sei in modalità di importazione, la richiesta che viene inviata a Vertipaq nel processo viene eseguita in un linguaggio xmSQL.

Il linguaggio xmSQL è in qualche modo simile a SQL ma non è completamente lo stesso. Parleremo di xmSWL in dettaglio quando parleremo dei piani di query in un altro tutorial.

È anche importante ricordare che il motore di archiviazione utilizza tutti i core disponibili nella CPU. La possibilità di utilizzare più core è utile nel caso in cui si disponga di più segmenti all'interno del modello di dati.

Supponiamo che tu abbia una tabella in LuckyTemplates con 12 milioni di righe. Questa tabella verrà quindi suddivisa in 12 segmenti perché sia ​​in Power Pivot che in LuckyTemplates ogni segmento si adatta a 1 milione di righe. Ciò è diverso dai servizi di analisi in generale, in cui un segmento ospita 8 milioni di righe.

Quindi, se ho sei core nella mia CPU, tutti e sei i core eseguiranno la scansione dei primi sei dei 12 segmenti contemporaneamente. Una volta terminato, passeranno ai sei segmenti successivi.

Ma se sto lavorando con servizi di analisi in cui il segmento predefinito contiene 8 milioni di righe, verranno utilizzati solo due dei miei sei core: un segmento elaborerà 8 milioni di righe mentre l'altro ne elabora 4 milioni.

Lavorare su query complicate

In precedenza, ho accennato al fatto che la modalità di importazione supporta solo operazioni di base come MIN, MAX, SUM, COUNT e GROUPBY. Quindi cosa succede se lavori su query più complicate?

Supponiamo che tu decida di utilizzare un'istruzione IF nel contesto della riga o un'iterazione nidificata come SUMX sulle tabelle Products e Sales.

In questo caso, il motore di archiviazione non sarà in grado di risolvere la query da solo. Quindi chiamerà il motore di formula, che inizierà a risolvere il complesso calcolo riga per riga all'interno del motore di archiviazione. Alcuni potrebbero pensare che questo sia uno scenario favorevole, con entrambi i motori che lavorano insieme, ma questo è tutt'altro che vero.

Vedete, quando ciò accade, la cache dei dati prodotta dal motore di archiviazione non può essere memorizzata nella cache nel caso in cui ci sia un callbackdataID in quella particolare query. Quindi, se aggiorni l'oggetto visivo, lo stesso calcolo dovrà essere eseguito sia dal motore delle formule che dal motore di archiviazione, anche se hai appena eseguito la stessa query pochi secondi fa. Ciò comporterà ritardi nelle prestazioni e un'esperienza utente negativa.

Si noti inoltre che il motore di archiviazione non sa se le query sono state eseguite tramite DAX o MDX. Come accennato in precedenza, è compito del motore delle formule convertire le query nella lingua corretta prima di passare al piano di query.

Infine, il motore delle formule invia le query al motore di archiviazione una per una. Ciò significa che avere più segmenti è davvero meglio in modo da poter ridurre il tempo di scansione complessivo all'interno di Vertipaq, con più segmenti scansionati contemporaneamente.

DAX per LuckyTemplates: Ottimizzazione tramite motori di formule in DAX Studio
Tecniche e lezioni sull'ottimizzazione delle query DAX
Prestazioni delle query e configurazione di DAX Studio

Conclusione

Comprendere i dettagli del motore di archiviazione aiuta davvero a ottimizzare le query DAX, soprattutto se si utilizza DAX Studio. Se hai seguito anche il tutorial che spiega il motore delle formule, puoi prendere decisioni migliori su come creare query con prestazioni migliori.

Sebbene il motore di formula funga da motore di livello superiore, non c'è dubbio che non può funzionare così bene se non massimizziamo entrambi i motori.

Ti auguro il meglio,