Ottimizzare Power Query quando si espandono le colonne della tabella

La semplicità e la facilità d'uso che consente agli utenti di Power BI di raccogliere rapidamente i dati e generare report interessanti e potenti per prendere decisioni aziendali intelligenti consente anche agli utenti di generare facilmente query con prestazioni scarse. Ciò si verifica spesso quando sono presenti due tabelle correlate nel modo in cui una chiave esterna correla le tabelle SQL o gli elenchi di SharePoint. Per il record, questo problema non è specifico di SQL o SharePoint e si verifica in molti scenari di estrazione dei dati back-end, soprattutto in cui lo schema è fluido e personalizzabile. Non c'è anche nulla di intrinsecamente sbagliato nell'archiviazione di dati in tabelle separate che condividono una chiave comune, in realtà si tratta di un tenet fondamentale della progettazione e della normalizzazione del database. Ma implica un modo migliore per espandere la relazione.

Si consideri l'esempio seguente di un elenco di clienti di SharePoint.

E l'elenco di posizioni seguente si riferisce a.

Quando ci si connette per la prima volta all'elenco, la posizione viene visualizzata come record.

Questi dati di primo livello vengono raccolti tramite una singola chiamata HTTP all'API di SharePoint (ignorando la chiamata ai metadati), che è possibile visualizzare in qualsiasi debugger Web.

Quando si espande il record, vengono visualizzati i campi uniti dalla tabella secondaria.

Quando si espandono righe correlate da una tabella a un'altra, il comportamento predefinito di Power BI consiste nel generare una chiamata a Table.ExpandTableColumn. È possibile visualizzare questo valore nel campo della formula generata. Sfortunatamente, questo metodo genera una singola chiamata alla seconda tabella per ogni riga della prima tabella.

Questo aumenta il numero di chiamate HTTP di una per ogni riga nell'elenco primario. Questo potrebbe non sembrare molto nell'esempio precedente di cinque o sei righe, ma nei sistemi di produzione in cui gli elenchi di SharePoint raggiungono centinaia di migliaia di righe, questo può causare una riduzione significativa dell'esperienza.

Quando le query raggiungono questo collo di bottiglia, la mitigazione migliore consiste nell'evitare il comportamento di chiamata per riga usando un join di tabella classico. In questo modo si garantisce che sia presente una sola chiamata per recuperare la seconda tabella e che il resto dell'espansione possa verificarsi in memoria usando la chiave comune tra le due tabelle. La differenza di prestazioni può essere enorme in alcuni casi.

Prima di tutto, iniziare con la tabella originale, notando la colonna che si vuole espandere e assicurandosi di avere l'ID dell'elemento in modo che sia possibile trovarne la corrispondenza. In genere la chiave esterna è denominata come il nome visualizzato della colonna con ID aggiunto. In questo esempio si tratta di LocationId.

In secondo luogo, caricare la tabella secondaria, assicurandosi di includere l'ID, ovvero la chiave esterna. Fare clic con il pulsante destro del mouse sul pannello Query per creare una nuova query.

Unire infine le due tabelle usando i rispettivi nomi di colonna corrispondenti. In genere è possibile trovare questo campo espandendo prima la colonna, quindi cercando le colonne corrispondenti nell'anteprima.

In questo esempio è possibile vedere che LocationId nell'elenco primario corrisponde all'ID nell'elenco secondario. L'interfaccia utente lo rinomina in Location.Id per rendere univoco il nome della colonna. A questo punto si userà questa informazione per unire le tabelle.

Facendo clic con il pulsante destro del mouse sul pannello query e scegliendo Nuova query>combina>query di merge come Nuovo, viene visualizzata un'interfaccia utente intuitiva che consente di combinare queste due query.

Selezionare ogni tabella dall'elenco a discesa per visualizzare un'anteprima della query.

Dopo aver selezionato entrambe le tabelle, selezionare la colonna che unisce le tabelle in modo logico (in questo esempio si tratta di LocationId dalla tabella primaria e ID della tabella secondaria). La finestra di dialogo indica il numero di righe corrispondenti usando tale chiave esterna. È probabile che si voglia usare il tipo di join predefinito (a sinistra esterna) per questo tipo di dati.

Selezionare OK e verrà visualizzata una nuova query, ovvero il risultato del join. L'espansione del record ora non implica chiamate aggiuntive al back-end.

L'aggiornamento di questi dati comporterà solo due chiamate a SharePoint, una per l'elenco primario e una per l'elenco secondario. Il join verrà eseguito in memoria, riducendo significativamente il numero di chiamate a SharePoint.

Questo approccio può essere usato per due tabelle in PowerQuery con una chiave esterna corrispondente.