Linee guida per il modello DirectQuery in Power BI Desktop

Questo articolo è destinato ai modelli di dati che sviluppano modelli DirectQuery di Power BI, sviluppati usando Power BI Desktop o il servizio Power BI. Descrive casi d'uso, limitazioni e linee guida per DirectQuery. In particolare, le linee guida sono progettate per determinare se DirectQuery è la modalità appropriata per il modello e migliorare le prestazioni dei report in base ai modelli DirectQuery. Questo articolo si applica ai modelli DirectQuery ospitati nel servizio Power BI o Server di report di Power BI.

Questo articolo non è destinato a fornire una discussione completa sulla progettazione del modello DirectQuery. Per un'introduzione, vedere l'articolo Modelli DirectQuery in Power BI Desktop . Per una discussione più approfondita, vedere direttamente il white paper DirectQuery in SQL Server 2016 Analysis Services . Tenere presente che il white paper descrive l'uso di DirectQuery in SQL Server Analysis Services. Gran parte del contenuto, tuttavia, è ancora applicabile ai modelli DirectQuery di Power BI.

Nota

Per considerazioni sull'uso della modalità di archiviazione DirectQuery per Dataverse, vedere Linee guida per la modellazione di Power BI per Power Platform.

Questo articolo non tratta direttamente i modelli compositi. Un modello composito è costituito da almeno un'origine DirectQuery ed eventualmente più. Le linee guida descritte in questo articolo sono ancora rilevanti, almeno in parte, per la progettazione di modelli compositi. Tuttavia, le implicazioni della combinazione di tabelle di importazione con tabelle DirectQuery non rientrano nell'ambito di questo articolo. Per altre informazioni, vedere Usare modelli compositi in Power BI Desktop.

È importante comprendere che i modelli DirectQuery impongono un carico di lavoro diverso nell'ambiente Power BI (servizio Power BI o Server di report di Power BI) e anche nelle origini dati sottostanti. Se si determina che DirectQuery è l'approccio di progettazione appropriato, è consigliabile coinvolgere le persone giuste nel progetto. Spesso si nota che una corretta distribuzione di modelli DirectQuery è il risultato di un team di professionisti IT che lavorano a stretto contatto. Il team è in genere costituito da sviluppatori di modelli e amministratori del database di origine. Può anche coinvolgere architetti di dati e data warehouse e sviluppatori ETL. Spesso, le ottimizzazioni devono essere applicate direttamente all'origine dati per ottenere risultati ottimali sulle prestazioni.

Ottimizzare le prestazioni dell'origine dati

L'origine del database relazionale può essere ottimizzata in diversi modi, come descritto nell'elenco puntato seguente.

Nota

Non tutti i modelli hanno le autorizzazioni o le competenze necessarie per ottimizzare un database relazionale. Sebbene sia il livello preferito per preparare i dati per un modello DirectQuery, alcune ottimizzazioni possono essere ottenute anche nella progettazione del modello, senza modificare il database di origine. Tuttavia, i migliori risultati di ottimizzazione vengono spesso ottenuti applicando ottimizzazioni al database di origine.

• Assicurarsi che l'integrità dei dati sia completa: è particolarmente importante che le tabelle di tipo dimensione contengano una colonna di valori univoci (chiave dimensione) mappati alle tabelle di tipo fatto. È anche importante che le colonne della dimensione di tipo fatto contengano valori di chiave di dimensione validi. Consentono di configurare relazioni di modello più efficienti che prevedono valori corrispondenti su entrambi i lati delle relazioni. Quando i dati di origine non dispongono dell'integrità, è consigliabile aggiungere un record di dimensione "sconosciuto" per ripristinare in modo efficace i dati. Ad esempio, è possibile aggiungere una riga alla tabella Product per rappresentare un prodotto sconosciuto e quindi assegnargli una chiave fuori intervallo, ad esempio -1. Se le righe nella tabella Sales contengono un valore product key mancante, sostituirle con -1. Garantisce che ogni valore product key Sales abbia una riga corrispondente nella tabella Product .

• Aggiungere indici: definire indici appropriati, in tabelle o viste, per supportare il recupero efficiente dei dati per il filtro e il raggruppamento degli oggetti visivi del report previsti. Per le origini di SQL Server, database SQL di Azure o Azure Synapse Analytics (in precedenza SQL Data Warehouse), vedere Architettura e progettazione degli indici di SQL Server e Guida alla progettazione per informazioni utili sulle linee guida sulla progettazione degli indici. Per SQL Server o database SQL di Azure origini volatili, vedere Introduzione a Columnstore per l'analisi operativa in tempo reale.

• Progettare tabelle distribuite: per le origini di Azure Synapse Analytics (in precedenza SQL Data Warehouse), che usano l'architettura MPP (Massively Parallel Processing), è consigliabile configurare tabelle di tipo fact di grandi dimensioni come tabelle hash distribuite e di tipo dimensione da replicare in tutti i nodi di calcolo. Per altre informazioni, vedere Linee guida per la progettazione di tabelle distribuite in Azure Synapse Analytics (in precedenza SQL Data Warehouse).

• Assicurarsi che le trasformazioni dei dati necessarie siano materializzate: per le origini di database relazionali di SQL Server (e altre origini di database relazionali), le colonne calcolate possono essere aggiunte alle tabelle. Queste colonne si basano su un'espressione, ad esempio Quantity moltiplicata per UnitPrice. Le colonne calcolate possono essere rese persistenti (materializzate) e, come le colonne normali, a volte possono essere indicizzate. Per altre informazioni, vedere Indici per le colonne calcolate.

  Si considerino anche viste indicizzate che possono preaggregare i dati della tabella dei fatti a un livello più elevato. Ad esempio, se la tabella Sales archivia i dati a livello di riga dell'ordine, è possibile creare una vista per riepilogare questi dati. La vista può essere basata su un'istruzione edizione Standard LECT che raggruppa i dati della tabella Sales per data (a livello di mese), cliente, prodotto e riepiloga i valori delle misure, ad esempio vendite, quantità e così via. La vista può quindi essere indicizzata. Per le origini di SQL Server o database SQL di Azure, vedere Creare viste indicizzate.

• Materializzare una tabella data: un requisito di modellazione comune prevede l'aggiunta di una tabella data per supportare il filtro basato sul tempo. Per supportare i filtri noti basati sul tempo nell'organizzazione, creare una tabella nel database di origine e assicurarsi che venga caricata con un intervallo di date che includono le date della tabella dei fatti. Assicurarsi inoltre di includere colonne per periodi di tempo utili, ad esempio anno, trimestre, mese, settimana e così via.

Ottimizzare la progettazione del modello

Un modello DirectQuery può essere ottimizzato in molti modi, come descritto nell'elenco puntato seguente.

• Evitare query di Power Query complesse: è possibile ottenere una progettazione efficiente del modello rimuovendo la necessità di applicare qualsiasi trasformazione alle query di Power Query. Ogni query viene mappata a una singola tabella o vista di origine del database relazionale. È possibile visualizzare in anteprima una rappresentazione dell'istruzione di query SQL effettiva per un passaggio applicato a Power Query selezionando l'opzione Visualizza query nativa.

  

  

• Esaminare l'uso di colonne calcolate e modifiche al tipo di dati: i modelli DirectQuery supportano l'aggiunta di calcoli e passaggi di Power Query per convertire i tipi di dati. Tuttavia, le prestazioni migliori sono spesso ottenute materializzando i risultati della trasformazione nell'origine del database relazionale, quando possibile.

• Non usare il filtro di data relativa di Power Query: è possibile definire il filtro relativo della data in una query di Power Query. Ad esempio, per recuperare gli ordini di vendita creati nell'ultimo anno (rispetto alla data odierna). Questo tipo di filtro si traduce in una query nativa inefficiente, come indicato di seguito:

  …
  from [dbo].[Sales] as [_]
  where [_].[OrderDate] >= convert(datetime2, '2018-01-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2019-01-01 00:00:00'))  
  

  Un approccio di progettazione migliore consiste nell'includere colonne temporali relative nella tabella data. Queste colonne archiviano i valori di offset relativi alla data corrente. Ad esempio, in una colonna RelativeYear il valore zero rappresenta l'anno corrente, -1 rappresenta l'anno precedente e così via. Preferibilmente, la colonna RelativeYear viene materializzata nella tabella date. Anche se meno efficiente, può anche essere aggiunto come colonna calcolata del modello, in base all'espressione usando le funzioni DAX TODAY e DATE .

• Mantenere le misure semplici: almeno inizialmente, è consigliabile limitare le misure alle aggregazioni semplici. Le funzioni di aggregazione includono SUM, COUNT, MIN, MAX e AVERAGE. Quindi, se le misure sono sufficientemente reattive, è possibile sperimentare con misure più complesse, ma prestando attenzione alle prestazioni per ognuna. Sebbene la funzione CALCULATE DAX possa essere usata per produrre espressioni di misura sofisticate che modificano il contesto di filtro, possono generare query native costose che non funzionano correttamente.

• Evitare relazioni sulle colonne calcolate: le relazioni del modello possono correlare una singola colonna in una tabella a una singola colonna in una tabella diversa. In alcuni casi, tuttavia, è necessario correlare le tabelle usando più colonne. Ad esempio, le tabelle Sales e Geography sono correlate da due colonne: CountryRegion e City. Per creare una relazione tra le tabelle, è necessaria una singola colonna e nella tabella Geography la colonna deve contenere valori univoci. La concatenazione del paese/area geografica e della città con un separatore trattino potrebbe ottenere questo risultato.

  La colonna combinata può essere creata con una colonna personalizzata di Power Query o nel modello come colonna calcolata. È tuttavia consigliabile evitare perché l'espressione di calcolo verrà incorporata nelle query di origine. Non solo è inefficiente, in genere impedisce l'uso di indici. Aggiungere invece colonne materializzate nell'origine del database relazionale e valutarne l'indicizzazione. È anche possibile prendere in considerazione l'aggiunta di colonne chiave surrogate alle tabelle di tipo dimensione, che è una pratica comune nelle progettazioni di data warehouse relazionali.

  Esiste un'eccezione a questa guida e riguarda l'uso della funzione DAX COMBINEVALUES . Lo scopo di questa funzione è supportare le relazioni tra modelli a più colonne. Anziché generare un'espressione usata dalla relazione, genera un predicato di join SQL a più colonne.

• Evitare relazioni nelle colonne "Identificatore univoco": Power BI non supporta in modo nativo il tipo di dati identificatore univoco (GUID). Quando si definisce una relazione tra colonne di questo tipo, Power BI genera una query di origine con un join che coinvolge un cast. Questa conversione dei dati in fase di query genera in genere prestazioni scarse. Fino a quando questo caso non viene ottimizzato, l'unica soluzione alternativa consiste nel materializzare le colonne di un tipo di dati alternativo nel database sottostante.

• Nascondere la colonna a un lato delle relazioni: la colonna a un lato di una relazione deve essere nascosta. In genere è la colonna chiave primaria delle tabelle di tipo dimensione. Se nascosto, non è disponibile nel riquadro Campi e quindi non può essere usato per configurare un oggetto visivo. La colonna sul lato molti può rimanere visibile se è utile raggruppare o filtrare i report in base ai valori della colonna. Si consideri, ad esempio, un modello in cui esiste una relazione tra le tabelle Sales e Product . Le colonne di relazione contengono valori sku prodotto (Unità di mantenimento dello stato delle scorte). Se lo SKU del prodotto deve essere aggiunto agli oggetti visivi, deve essere visibile solo nella tabella Sales . Quando questa colonna viene usata per filtrare o raggruppare in un oggetto visivo, Power BI genera una query che non deve unire le tabelle Sales e Product .

• Impostare le relazioni per applicare l'integrità: la proprietà Presupporre l'integrità referenziale delle relazioni DirectQuery determina se Power BI genera query di origine usando un inner join anziché un outer join. In genere migliora le prestazioni delle query, anche se dipende dalle specifiche dell'origine del database relazionale. Per altre informazioni, vedere Presupporre le impostazioni di integrità referenziale in Power BI Desktop.

• Evitare l'uso del filtro delle relazioni bidirezionali: l'uso del filtro delle relazioni bidirezionali può portare a istruzioni di query che non funzionano correttamente. Usare questa funzionalità di relazione solo quando necessario ed è in genere il caso di implementare una relazione molti-a-molti in una tabella di bridging. Per altre informazioni, vedere Relazioni con cardinalità molti-molti in Power BI Desktop.

• Limitare le query parallele: è possibile impostare il numero massimo di connessioni che DirectQuery apre per ogni origine dati sottostante. Controlla il numero di query inviate simultaneamente all'origine dati.

  • L'impostazione è abilitata solo quando nel modello è presente almeno un'origine DirectQuery. Il valore si applica a tutte le origini DirectQuery e a tutte le nuove origini DirectQuery aggiunte al modello.
  • L'aumento delle Connessione massime per ogni valore dell'origine dati garantisce l'invio di più query (fino al numero massimo specificato) all'origine dati sottostante, utile quando numerosi oggetti visivi si trovano in una singola pagina o molti utenti accedono contemporaneamente a un report. Una volta raggiunto il numero massimo di connessioni, vengono accodate altre query fino a quando non diventa disponibile una connessione. L'aumento di questo limite comporta un carico maggiore sull'origine dati sottostante, pertanto l'impostazione non garantisce un miglioramento delle prestazioni complessive.
  • Quando il modello viene pubblicato in Power BI, anche il numero massimo di query simultanee inviate all'origine dati sottostante dipende dall'ambiente. Diversi ambienti(ad esempio Power BI, Power BI Premium o Server di report di Power BI) possono imporre vincoli di velocità effettiva diversi. Per altre informazioni sulle limitazioni delle risorse di capacità di Power BI Premium, vedere Distribuzione e gestione delle capacità Premium di Power BI.

Ottimizzare le progettazioni dei report

I report basati su un modello semantico DirectQuery (noto in precedenza come set di dati) possono essere ottimizzati in molti modi, come descritto nell'elenco puntato seguente.

• Abilitare le tecniche di riduzione delle query: Opzioni di Power BI Desktop e Impostazioni include una pagina Riduzione query. Questa pagina include tre opzioni utili. È possibile disabilitare l'evidenziazione incrociata e il filtro incrociato per impostazione predefinita, anche se può essere sottoposto a override modificando le interazioni. È anche possibile visualizzare un pulsante Applica nei filtri dei dati e nei filtri. Le opzioni di filtro dei dati o filtro non verranno applicate finché l'utente del report non fa clic sul pulsante. Se si abilitano queste opzioni, è consigliabile farlo per la prima volta quando si crea il report.
• Applicare prima i filtri: quando si progettano i report, è consigliabile applicare tutti i filtri applicabili, a livello di report, pagina o oggetto visivo, prima di eseguire il mapping dei campi ai campi visivi. Ad esempio, anziché trascinare le misure CountryRegion e Sales e quindi filtrare in base a un determinato anno, applicare prima il filtro sul campo Year . Poiché ogni passaggio della compilazione di un oggetto visivo invierà una query e, mentre è possibile apportare un'altra modifica prima del completamento della prima query, il caricamento non è necessario sull'origine dati sottostante. Applicando i filtri in anticipo, in genere tali query intermedie sono meno costose e veloci. Inoltre, se non si applicano i filtri in anticipo, è possibile superare il limite di 1 milione di righe, come descritto in informazioni su DirectQuery.
• Limitare il numero di oggetti visivi in una pagina: quando viene aperta una pagina del report (e quando vengono applicati i filtri di pagina) tutti gli oggetti visivi in una pagina vengono aggiornati. Esiste tuttavia un limite al numero di query che possono essere inviate in parallelo, imposte dall'ambiente Power BI e dall'impostazione Numero massimo di Connessione per modello di origine dati, come descritto in precedenza. Pertanto, man mano che aumenta il numero di oggetti visivi di pagina, è possibile che vengano aggiornati in modo seriale. Aumenta il tempo impiegato per aggiornare l'intera pagina e aumenta anche la probabilità che gli oggetti visivi visualizzino risultati incoerenti (per origini dati volatili). Per questi motivi, è consigliabile limitare il numero di oggetti visivi in qualsiasi pagina e avere invece pagine più semplici. La sostituzione di più oggetti visivi scheda con un singolo oggetto visivo scheda a più righe può ottenere un layout di pagina simile.
• Disattivare l'interazione tra oggetti visivi: l'evidenziazione incrociata e le interazioni con filtri incrociati richiedono l'invio di query all'origine sottostante. A meno che queste interazioni non siano necessarie, è consigliabile disattivarli se il tempo impiegato per rispondere alle selezioni degli utenti sarebbe eccessivamente lungo. Queste interazioni possono essere disattivate, per l'intero report (come descritto in precedenza per le opzioni di riduzione delle query) o caso per caso. Per altre informazioni, vedere Come gli oggetti visivi si filtrano tra loro in un report di Power BI.

Oltre all'elenco precedente di tecniche di ottimizzazione, ognuna delle funzionalità di creazione di report seguenti può contribuire ai problemi di prestazioni:

• Filtri di misura: gli oggetti visivi contenenti misure (o aggregazioni di colonne) possono avere filtri applicati a tali misure. Ad esempio, l'oggetto visivo seguente mostra Sales by Category, ma solo per le categorie con più di $15 milioni di vendite.

  

  Può comportare l'invio di due query all'origine sottostante:

  • La prima query recupererà le categorie che soddisfano la condizione (vendite > di 15 milioni di dollari)
  • La seconda query recupererà quindi i dati necessari per l'oggetto visivo, aggiungendo le categorie che soddisfano la condizione alla clausola WHERE

  In genere viene eseguita correttamente se sono presenti centinaia o migliaia di categorie, come in questo esempio. Le prestazioni possono tuttavia ridurre le prestazioni, se il numero di categorie è molto maggiore (e in effetti la query avrà esito negativo se sono presenti più di 1 milione di categorie che soddisfano la condizione, a causa del limite di 1 milione di righe descritto in precedenza).

• Filtri TopN: è possibile definire filtri avanzati per filtrare solo i valori primi (o inferiori) N classificati da una misura. Ad esempio, per visualizzare solo le prime cinque categorie nell'oggetto visivo precedente. Analogamente ai filtri delle misure, verranno inviate anche due query all'origine dati sottostante. Tuttavia, la prima query restituirà tutte le categorie dall'origine sottostante e quindi le prime N vengono determinate in base ai risultati restituiti. A seconda della cardinalità della colonna interessata, può causare problemi di prestazioni (o errori di query dovuti al limite di 1 milione di righe).

• Mediana: in genere, qualsiasi aggregazione (Sum, Count Distinct e così via) viene inserita nell'origine sottostante. Tuttavia, non è vero per Median, poiché questa aggregazione non è supportata dall'origine sottostante. In questi casi, i dati di dettaglio vengono recuperati dall'origine sottostante e Power BI valuta la mediano dai risultati restituiti. È opportuno calcolare la mediano su un numero relativamente ridotto di risultati, ma i problemi di prestazioni (o gli errori di query dovuti al limite di 1 milione di righe) si verificheranno se la cardinalità è elevata. Ad esempio, la popolazione di paese/area geografica media può essere ragionevole, ma il prezzo di vendita mediano potrebbe non essere.

• Filtri dei dati a selezione multipla: consentire la selezione multipla nei filtri dei dati e nei filtri può causare problemi di prestazioni. Infatti, poiché l'utente seleziona altri elementi del filtro dei dati (ad esempio, creando fino ai 10 prodotti a cui si è interessati), ogni nuova selezione comporta l'invio di una nuova query all'origine sottostante. Mentre l'utente può selezionare l'elemento successivo prima del completamento della query, comporta un carico aggiuntivo sull'origine sottostante. Questa situazione può essere evitata mostrando il pulsante Applica, come descritto in precedenza nelle tecniche di riduzione delle query.

• Totali visivi: per impostazione predefinita, le tabelle e le matrici visualizzano totali e subtotali. In molti casi, è necessario inviare query aggiuntive all'origine sottostante per ottenere i valori per i totali. Si applica ogni volta che si usano le aggregazioni Count Distinct o Median e in tutti i casi quando si usa DirectQuery su SAP HANA o SAP Business Warehouse. Questi totali devono essere disattivati (usando il riquadro Formato) se non necessario.

Convertire in un modello composito

I vantaggi dei modelli Import e DirectQuery possono essere combinati in un singolo modello configurando la modalità di archiviazione delle tabelle del modello. La modalità di archiviazione tabelle può essere Import o DirectQuery o entrambe, note come Dual. Quando un modello contiene tabelle con modalità di archiviazione diverse, è noto come modello composito. Per altre informazioni, vedere Usare modelli compositi in Power BI Desktop.

Esistono molti miglioramenti funzionali e delle prestazioni che è possibile ottenere convertendo un modello DirectQuery in un modello composito. Un modello composito può integrare più di un'origine DirectQuery e può includere anche aggregazioni. È possibile aggiungere tabelle di aggregazione alle tabelle DirectQuery per importare una rappresentazione riepilogata della tabella. Possono ottenere miglioramenti notevoli delle prestazioni quando gli oggetti visivi eseguono query su aggregazioni di livello superiore. Per altre informazioni, vedere Aggregazioni in Power BI Desktop.

Informare gli utenti

È importante informare gli utenti su come lavorare in modo efficiente con i report basati su modelli semantici DirectQuery. Gli autori di report devono essere istruiti sul contenuto descritto nella sezione Ottimizzare le progettazioni dei report.

È consigliabile informare gli utenti dei report sui report basati su modelli semantici DirectQuery. Può essere utile comprendere l'architettura dei dati generale, incluse eventuali limitazioni pertinenti descritte in questo articolo. Informarli di aspettarsi che le risposte di aggiornamento e il filtro interattivo potrebbero talvolta essere lenti. Quando gli utenti del report capiscono perché si verifica una riduzione delle prestazioni, è meno probabile che perdano fiducia nei report e nei dati.

Quando si recano report su origini dati volatili, assicurarsi di informare gli utenti del report sull'uso del pulsante Aggiorna. Informarli anche che potrebbe essere possibile visualizzare risultati incoerenti e che un aggiornamento del report può risolvere eventuali incoerenze nella pagina del report.

Contenuto correlato

Per altre informazioni su DirectQuery, vedere le risorse seguenti:

• Modelli DirectQuery in Power BI Desktop
• Usare DirectQuery in Power BI Desktop
• Risoluzione dei problemi del modello DirectQuery in Power BI Desktop
• Domande? Contattare la community di Power BI
• inviare suggerimenti, Contribuire con idee per migliorare Power BI