10 modi per migliorare le prestazioni di PowerPivot

Quando pubblichi report PowerPivot sul Web, intendi offrire al tuo pubblico la migliore esperienza possibile. Gran parte di questa esperienza è garantire che le prestazioni siano buone. La parola prestazioni (in relazione ad applicazioni e report) è in genere sinonimo di velocità, ovvero la velocità con cui un'applicazione esegue determinate azioni come l'apertura all'interno del browser, l'esecuzione di query o il filtraggio.

1Limitare il numero di righe e colonne nelle tabelle del modello di dati.

Un'enorme influenza sulle prestazioni di PowerPivot è il numero di colonne portate o importate nel modello di dati. Ogni colonna importata è un'altra dimensione che PowerPivot deve elaborare durante il caricamento di una cartella di lavoro. Non importare colonne extra "per ogni evenienza": se non sei sicuro di utilizzare determinate colonne, semplicemente non inserirle. Queste colonne sono abbastanza facili da aggiungere in seguito se trovi che ne hai bisogno.

Più righe significano più dati da caricare, più dati da filtrare e più dati da calcolare. Evita di selezionare un'intera tabella se non è necessario. Utilizza una query o una vista nel database di origine per filtrare solo le righe che devi importare. Dopotutto, perché importare 400.000 righe di dati quando puoi utilizzare una semplice clausola WHERE e importarne solo 100.000?

2Utilizzare le viste invece delle tabelle.

A proposito di visualizzazioni, per le migliori pratiche, utilizza le visualizzazioni ogni volta che è possibile.

Sebbene le tabelle siano più trasparenti delle visualizzazioni, consentendoti di vedere tutti i dati grezzi e non filtrati, vengono fornite con tutte le colonne e le righe disponibili, che tu ne abbia bisogno o meno. Per mantenere il tuo modello di dati PowerPivot a una dimensione gestibile, sei spesso costretto a compiere il passaggio aggiuntivo di filtrare in modo esplicito le colonne che non ti servono.

Le viste non solo possono fornire dati più puliti e intuitivi, ma aiutano anche a semplificare il modello di dati PowerPivot limitando la quantità di dati importati.

3Evitare relazioni a più livelli.

Sia il numero di relazioni che il numero di livelli di relazione hanno un impatto sulle prestazioni dei report di PowerPivot. Quando crei il tuo modello, segui le best practice e disponi di un'unica tabella dei fatti contenente principalmente dati numerici quantitativi (fatti) e tabelle delle dimensioni che si riferiscono direttamente ai fatti. Nel mondo dei database, questa configurazione è uno schema a stella, come mostrato.

Evitare di creare modelli in cui le tabelle delle dimensioni sono correlate ad altre tabelle delle dimensioni.

4Lascia che i server di database di back-end facciano il crunch.

La maggior parte degli analisti di Excel che non conoscono PowerPivot tende a estrarre i dati non elaborati direttamente dalle tabelle nei server di database esterni. Dopo che i dati non elaborati sono in PowerPivot, creano colonne e misure calcolate per trasformare e aggregare i dati in base alle esigenze. Ad esempio, gli utenti in genere estrae i dati sui ricavi e sui costi e quindi creano una colonna calcolata in PowerPivot per calcolare il profitto.

Quindi perché fare in modo che PowerPivot esegua questo calcolo quando il server di back-end avrebbe potuto gestirlo? La realtà è che i sistemi di database back-end come SQL Server hanno la capacità di modellare, aggregare, pulire e trasformare i dati in modo molto più efficiente rispetto a PowerPivot. Perché non utilizzare le loro potenti capacità per massaggiare e modellare i dati prima di importarli in PowerPivot?

Piuttosto che estrarre dati grezzi dalla tabella, prendi in considerazione l'utilizzo di query, viste e stored procedure per eseguire quanto più lavoro di aggregazione e crunch dei dati possibile. Questa leva riduce la quantità di elaborazione che PowerPivot dovrà eseguire e migliora naturalmente le prestazioni.

5Attenzione alle colonne con valori non distinti.

Le colonne con un numero elevato di valori univoci sono particolarmente difficili per le prestazioni di PowerPivot. Colonne come ID transazione, ID ordine e Numero fattura spesso non sono necessarie nei report e nei dashboard di PowerPivot di alto livello. Quindi, a meno che non siano necessari per stabilire relazioni con altre tabelle, escluderli dal modello.

6Limitare il numero di slicer in un report.

L'affettatrice è una delle migliori nuove funzionalità di business intelligence (BI) di Excel negli ultimi anni. Utilizzando le affettatrici, puoi fornire al tuo pubblico un'interfaccia intuitiva che consente il filtraggio interattivo dei tuoi report e dashboard di Excel.

Uno dei vantaggi più utili dell'affettatrice è che risponde ad altre affettatrici, fornendo un effetto filtro a cascata. Ad esempio, la figura illustra non solo che facendo clic su Midwest nell'affettatrice della regione si filtra la tabella pivot ma che anche l'affettatrice del mercato risponde, evidenziando i mercati che appartengono alla regione del Midwest. Microsoft chiama questo comportamento filtro incrociato.

Per quanto utile sia lo slicer, sfortunatamente è estremamente negativo per le prestazioni di PowerPivot. Ogni volta che un'affettatrice viene modificata, PowerPivot deve ricalcolare tutti i valori e le misure nella tabella pivot. Per fare ciò, PowerPivot deve valutare ogni riquadro nell'affettatrice selezionata ed elaborare i calcoli appropriati in base alla selezione.

7Crea filtri dei dati solo sui campi dimensione.

I filtri dei dati legati a colonne che contengono molti valori univoci spesso causano un maggiore impatto sulle prestazioni rispetto alle colonne che contengono solo una manciata di valori. Se un'affettatrice contiene un numero elevato di riquadri, prendi in considerazione l'utilizzo di un elenco a discesa Filtro tabella pivot.

In una nota simile, assicurati di dimensionare correttamente i tipi di dati delle colonne. Una colonna con pochi valori distinti è più chiara di una colonna con un numero elevato di valori distinti. Se stai archiviando i risultati di un calcolo da un database di origine, riduci il numero di cifre (dopo il decimale) da importare. Ciò riduce la dimensione del dizionario e, possibilmente, il numero di valori distinti.

8Disabilita il comportamento del filtro incrociato per alcuni filtri dei dati.

La disabilitazione del comportamento del filtro incrociato di un'affettatrice essenzialmente impedisce a quell'affettatrice di modificare le selezioni quando si fa clic su altre affettatrici. Ciò impedisce a PowerPivot di valutare i titoli nell'affettatrice disabilitata, riducendo così i cicli di elaborazione. Per disabilitare il comportamento del filtro incrociato di un filtro dei dati, selezionare Impostazioni filtro dati per aprire la finestra di dialogo Impostazioni filtro dati. Quindi deseleziona semplicemente l'opzione Indica visivamente elementi senza dati.

9Utilizzare misure calcolate anziché colonne calcolate.

Utilizzare misure calcolate anziché colonne calcolate, se possibile. Le colonne calcolate vengono archiviate come colonne importate. Poiché le colonne calcolate interagiscono intrinsecamente con altre colonne nel modello, calcolano ogni volta che la tabella pivot viene aggiornata, indipendentemente dal fatto che vengano utilizzate o meno. Le misure calcolate, d'altra parte, vengono calcolate solo al momento della query.

Le colonne calcolate assomigliano alle colonne regolari in quanto entrambe occupano spazio nel modello. Al contrario, le misure calcolate vengono calcolate al volo e non occupano spazio.

10Aggiorna a Excel a 64 bit.

Se continui a riscontrare problemi di prestazioni con i rapporti di PowerPivot, puoi sempre acquistare un PC migliore, in questo caso eseguendo l'aggiornamento a un PC a 64 bit con Excel a 64 bit installato.

PowerPivot carica l'intero modello di dati nella RAM ogni volta che ci lavori. Più RAM ha il tuo computer, meno problemi di prestazioni riscontri. La versione a 64 bit di Excel può accedere a più RAM del tuo PC, assicurando che disponga delle risorse di sistema necessarie per elaborare modelli di dati più grandi. In effetti, Microsoft consiglia Excel a 64 bit per chiunque lavori con modelli composti da milioni di righe.

Ma prima di iniziare frettolosamente a installare Excel a 64 bit, devi rispondere a queste domande:

Hai già installato Excel a 64 bit?

I tuoi modelli di dati sono abbastanza grandi?

Hai un sistema operativo a 64 bit installato sul tuo PC?

Gli altri componenti aggiuntivi smetteranno di funzionare?