Power Query: come alleggerire database accodati

[stefanosiracusano]

Ciao a tutti. Al fine di avere un database completo di budget e consuntivo aggiornato, inserisco query di budget e query di consuntivo normalizzate nello stesso modo divise per anni e alla fine accodate tra di loro; ad oggi dopo aver inserito 3 anni completi, il caricamento delle query è davvero pesante., lo stesso quando esporto i dati in tabella pivot. Teoricamente dovrebbe essere un lavoro di aggiunta degli anni continua, ma la query sta diventando ingestibile. Come alleggerire le query e/o la conseguente tabella pivot di esportazione? Grazie in anticipo

[Andrea90]

@stefanosiracusano

Quale è la tua fonte dati?

L'argomento "performance" è difficile da discutere in un forum perché può dipendere da una moltitudine di fattori e non è possibile capirne la causa solo leggendo la tua descrizione. Inoltre Power Query Engine per excel non è così ottimizzato come invece lo è in Power BI quindi già si parte male...

L'ottimale sarebbe utilizzare un incremental refresh in questi casi, poiché essendo che tu carichi anche dei dati storici questi sono per lo più statici. Ad esempio, i dati del 2022 e 2023 ormai non subiranno più modifiche dunque sarebbe inutile, ogni volta, andare a leggere questi record. Il problema è che l'incremental refresh non è supportato in Excel (in Power BI si), ma comunque anche se fosse supportato, funziona solo con specifiche sorgenti dati.

Altri accorgimenti che possono in qualche modo aiutare (non aspettarti miracoli):

1) Appena estrapoli i dati (si crea il passaggio Origine) elimina tutti i campi che non utilizzerai e filtra solo le righe utili, in questo modo i passaggi successivi che andrai ad applicare saranno utilizzati su di un numero di righe inferiore.

2) Se hai delle duplicazioni di query considera di utilizzare "Riferimento" invece di "Duplicazione"

3) Se la tua fonte dati lo consente applica il più possibile il Query Folding

4) Se il livello di dettaglio che hai nelle tabelle non è utile considera l'operazione di Raggruppamento di Righe

5) Applica il tipo di dati corretto ai diversi campi

6) Controlla se ci sono passaggi nelle query che risultano onerosi in termini di tempi di elaborazione e prova a rivederne il codice

7) Cambia la fonte dati per i valori "storici", ad esempio i file .csv sono molto più veloci nel caricamento dei file excel (che magari utilizzi solo per i dati dell'anno in corso)

8) Nelle impostazioni di ciascuna query abilita il caricamento veloce dei dati.

9) Disabilita l'aggiornamento dati in background di ciascuna query

Altro aspetto importante è la "macchina" che utilizzi per fare queste analisi, quindi versione a 64bit invece che 32, quanta RAM installata, ecc...

Andrea

[stefanosiracusano]

@Andrea90
Grazie per la risposta.
In merito ai punti:
3) Come faccio ad eseguire il query folding?
7) Intendi salvare con nome i file di origine in CSV (delimitato dal separatore di elenco)?

[Andrea90]

@stefanosiracusano

Il query folding non lo attivi dipende dal tipo di sorgente dati che hai, per questo te l’ho chiesto come primo punto (al quale non hai risposto).

Per il punto 7 si, ma era solo un esempio per confrontare la velocità di caricamento dati da un file .xlsx ad un csv.

Andrea

[stefanosiracusano]

@Andrea90

Come sorgente dati, utilizzo tabelle di fogli excel e a questo punto saranno CSV. Con xls o csv è possibile attivare il query folding?

[Andrea90]

Con xls o csv è possibile attivare il query folding?

No non è possibile

Andrea