Ottimizzare le query SharePoint in Power BI / Sgart.it

Quando si gestiscono delle liste SharePoint Online in Power BI con più campi di tipo loookup si notano tempi di aggiornamento dei dati dell'ordine dei minuti anche per liste piccole con soli 100 record.

Ovviamente più la lista è grossa e più colonne lookup sono presenti, più questi tempi aumentano esponenzialmente.

Import con connettore SharePoint

description

Normalmente in Power BI si importano le liste tramite il connettore SharePoint Online o SharePoint On-premises (se c'è un gateway istallato) tramite il menu Get Data

Get Data

e in Transform Data si espandono tutte le colonne lookup necessarie selezionando solo i campi necessari

Transform Data

il risultato è questo dove per ogni colonna lookup viene letto il corrispondente campo Title

Get Data

Questo modo di importare i dati è sicuramente user friendly, ma non è assolutamente efficiente.
Anche pochi items i tempi di attesa per il refresh dei dati sono dell'ordine dei minuti.

La lentezza di questo metodo è legata alla gestione dei compi lookup da parte di Power BI.
In pratica per ogni record e per ogni campo lookup viene fatta una query sul singolo item della lista lookup per ricavare i dati necessari.

Questo aumenta il tempo di importazione e la quantità di dati trasferiti.

Import con OData

Per rendere più efficienti le query SharePoint di import in Power BI, su può usare il connettore OData

Get Data - OData

nel campo Url va inserita una query REST OData di SharePoint come questa:

https://sgart.sharepoint.com/_api/web/lists/GetByTitle('TestMultiLookupPowerBI')/items?$expand=Provincia1,Provincia2,Provincia3,Provincia4,Provincia5,Provincia6&$select=Id,Title,Created,Modified,Provincia1/Title,Provincia2/Title,Provincia3/Title,Provincia4/Title,Provincia5/Title,Provincia6/Title

Query OData

Formato OData

In breve, le query REST OData sono composte dalle seguenti parti:

https://tenantName.sharepoint.com/sites/nomesito = il tenant SharePoint Online o la url del sito SharePoint on-premises
/_api = prefisso standard per accedere alle API di SharePoint
/web/lists/GetByTitle('TestMultiLookupPowerBI') = identifica la lista tramite il display name oppure trami il suo guid /web/lists(guid'5bca80cb-14c7-496e-aae2-163e61a810c0')
/items = identifica gli items della lista
? = separatore della query string
$expand = per espandere i campi di tipo lookup separati da virgola (va indicato il nome interno del campo)
$select = per indicare i campi che devono essere ritornati, i campi lookup sono nella forma nomeCampo/nomeCampoDellaListaLookup, tutti separati dalla virgola

I parametri in query string devono essere separati tramite il carattere & (ampersand).

Le query OData supportano anche altri parametri come:

$filter = espressione di filtro, corrisponde alla where di T-SQL, ad esempio: Title eq 'Line 1' and Provincia1/Title eq 'Belluno'
$orderby = per ordinare il risultato
$top = per ritornare solo le prime N righe

Comparazione

Dopo aver aggiunto le due connessioni al report Power BI e premuto il tasto Refresh per aggiornare i dati, si nota subito la differenza

description

L'import standard, con soli 100 items nella lista, impiega alcuni minuti e scarica 529kB, mentre quella con OData impiega pochi secondi e scarica solo 35kB.

Conclusione

Le query OData sono sicuramente meno user friendly e più complicate da scrivere, ma danno grossi vantaggi in termini di tempo e quantità di dati trasferiti.