Creare un modello di dati utilizzo efficiente della memoria mediante Excel e il componente aggiuntivo PowerPivot

Importante :  Il presente articolo è stato tradotto automaticamente, vedere la dichiarazione di non responsabilità. Per visualizzare la versione inglese dell'articolo, fare clic qui.

In Excel 2013 o versione successiva, è possibile creare modelli di dati contenenti milioni di righe e quindi eseguire analisi potente dei dati rispetto a questi modelli. Modelli di dati possono essere creati con o senza il componente aggiuntivo PowerPivot per supportare qualsiasi numero di tabelle pivot, grafici e visualizzazioni di Power View nella stessa cartella di lavoro.

Nota : In questo articolo vengono illustrati i modelli di dati in Excel 2013. Tuttavia, le caratteristiche di PowerPivot introdotte in Excel 2013 e modellazione dei dati stesso applicano anche a Excel 2016. Non esiste in modo efficace piccola differenza tra le due versioni di Excel.

Sebbene sia possibile creare con facilità i modelli di grandi quantità di dati in Excel, esistono diversi motivi non a. Prima di grandi dimensioni modelli che contengono molteplici tabelle e colonne sono eccessivo per la maggior parte delle analisi e apportare per un elenco di campi complesso. Modelli di grandi dimensioni, secondo usare memoria efficace, influire negativamente sulle altre applicazioni e report che condividono le stesse risorse di sistema. Infine, in Office 365, sia SharePoint Online ed Excel Web App limitare le dimensioni di un file di Excel a 10 MB. Per i modelli di dati di cartella di lavoro che contengono milioni di righe, si verificheranno il limite di 10 MB molto rapidamente. Vedere limiti e specifiche dei modelli di dati.

Questo articolo illustra come creare un modello progettato in modo tale che sia più facile da usare e che usi meno memoria. Le procedure consigliate riportate di seguito per la progettazione di modelli efficienti risulteranno utili nel corso del tempo per qualsiasi modello creato e usato, sia che venga visualizzato in Excel 2013, in SharePoint Online di Office 365, in un server Office Web Apps o in SharePoint 2013.

Si consiglia di eseguire anche l'utilità di ottimizzazione dimensioni cartella di lavoro. Analizza la cartella di lavoro di Excel e, se possibile, comprime ulteriormente. Scaricare l' utilità di ottimizzazione dimensioni cartella di lavoro.

In questo articolo

Rapporti di compressione e motore analitica in memoria

Importanza di una colonna inesistente per l'utilizzo di memoria insufficiente

Due esempi di colonne da escludere

Come escludere colonne non necessarie

Filtro delle righe necessarie?

Cosa fare se occorre la colonna. ridurre il costo di spazio?

Modifica delle colonne Datetime

Modifica della query SQL

Utilizzo di DAX misure calcolate anziché colonne

Quali 2 colonne mantenere

Conclusioni

Collegamenti correlati

Rapporti di compressione e motore di analisi in memoria

I modelli di dati di Excel utilizzano il motore di analisi in memoria per archiviare dati in memoria. Il motore implementa potenti tecniche di compressione per ridurre i requisiti di archiviazione, riducendo al minimo le dimensioni di un set di risultati rispetto alle dimensioni originali.

In media, si può prevedere che le dimensioni di un modello di dati siano da 7 a 10 volte minori rispetto agli stessi dati nel relativo punto di origine. Se ad esempio si importano 7 MB di dati da un database di SQL Server, le dimensioni del modello di dati in Excel potrebbero essere ridotte a 1 MB o anche meno. Il grado di compressione effettivo dipende prevalentemente dal numero di valori univoci presenti in ogni colonna. Per archiviare un numero maggiore, di valori univoci è necessaria una quantità maggiore di memoria.

La compressione e i valori univoci sono concetti importanti da considerare, perché per creare un modello efficiente con un utilizzo minimo della memoria è necessario massimizzare la compressione. Il modo più semplice per ottenere questo risultato consiste nell'eliminare tutte le colonne non realmente necessarie, in particolare se includono un numero elevato di valori univoci.

Nota : I requisiti di archiviazione possono essere notevolmente diversi in base alle singole colonne. In alcuni casi, è preferibile avere più colonne con un numero ridotto di valori univoci anziché un'unica colonna con un numero elevato di valori univoci. Questa tecnica viene descritta in dettaglio nella sezione sulle ottimizzazioni della colonna DateTime.

Importanza di escludere colonne non necessarie per ridurre l'utilizzo di memoria

Per un utilizzo più efficiente della memoria, è importante innanzitutto evitare di importare colonne non necessarie. Per creare un modello efficiente, esaminare ogni colonna e verificare se contribuisce effettivamente all'analisi da eseguire. In caso di dubbi, escludere la colonna. In seguito, è sempre possibile aggiungere nuove colonne, se necessario.

Due esempi di colonne da escludere

Il primo esempio fa riferimento a dati provenienti da un data warehouse. Nei data warehouse è frequente trovare elementi di processi ETL che caricano e aggiornano i dati. Quando vengono caricati i dati, vengono create colonne come "data creazione", "data aggiornamento" e "esecuzione ETL". Queste colonne non sono necessarie nel modello e dovrebbero essere deselezionate durante l'importazione di dati.

Il secondo esempio riguarda l'omissione della colonna di chiave primaria durante l'importazione di una tabella dei fatti.

Molte tabelle, incluse quelle dei fatti, contengono chiavi primarie. Per la maggior parte delle tabelle, ad esempio quelle contenenti dati su clienti, dipendenti o vendite, è preferibile avere una chiave primaria da utilizzare per creare relazioni nel modello.

Le tabelle dei fatti sono diverse. In questo tipo di tabella la chiave primaria viene utilizzata per identificare in modo univoco ogni riga. Anche se è necessaria ai fini della normalizzazione, la chiave primaria risulta meno utile in un modello di dati in cui si desidera inserire solo le colonne utilizzate per l'analisi o per stabilire relazioni tra tabelle. Per questo motivo, quando si importano dati da una tabella dei fatti, non includere la relativa chiave primaria. Le chiavi primarie delle tabelle dei fatti consumano quantità enormi di spazio nel modello, anche se non offrono vantaggi, in quanto non possono essere utilizzate per creare relazioni.

Nota : Nei data warehouse e nei database multidimensionali le tabelle di grandi dimensioni costituite da dati prevalentemente numerici vengono spesso definite come "tabelle dei fatti". Le tabelle dei fatti solitamente includono dati sulle prestazioni o sulle transazioni aziendali, ad esempio punti dati su vendite e costi aggregati e allineati a unità organizzative, prodotti, segmenti di mercato, aree geografiche e così via. Tutte le colonne di una tabella dei fatti che contengono dati aziendali o che possono essere utilizzate per riferimenti incrociati ai dati archiviati in altre tabelle dovrebbero essere incluse nel modello per supportare l'analisi dei dati. La colonna da escludere è la colonna della chiave primaria della tabella dei fatti, costituita da valori univoci che esistono solo in questa tabella e in nessun'altra. Poiché le tabelle dei fatti sono di notevoli dimensioni, i principali vantaggi per l'efficienza del modello derivano dall'escludere righe o colonne da queste tabelle.

Come escludere colonne non necessarie

Modelli efficienti contengano solo le colonne che è necessario effettivamente nella cartella di lavoro. Se si desidera controllare le colonne da includere nel modello, è necessario utilizzare l'importazione guidata tabella nel componente aggiuntivo PowerPivot per importare i dati piuttosto che la finestra di dialogo "Importa dati" in Excel.

All'avvio dell'Importazione guidata tabella, è possibile selezionare le tabelle da importare.

Importazione guidata tabella nel componente aggiuntivo PowerPivot

Per ogni tabella, è possibile fare clic sul pulsante Visualizza anteprima e applica filtro e selezionare le parti della tabella effettivamente necessarie. È consigliabile deselezionare prima tutte le colonne e quindi procedere a selezionare quelle desiderate, dopo aver valutato se sono o meno necessarie per l'analisi.

Riquadro di anteprima nell'Importazione guidata tabella

Filtro delle righe necessarie

Molte tabelle di database e data warehouse aziendali contengono dati cronologici accumulati per lunghi periodi di tempo. Inoltre, a volte le tabelle a cui si è interessati contengono informazioni relative ad aree di attività non necessarie per specifiche analisi.

Con l'Importazione guidata tabella, è possibile escludere dati cronologici o non correlati, con un notevole risparmio di spazio nel modello. Nell'immagine seguente viene applicato un filtro per date in modo da recuperare solo le righe contenenti dati relativi all'anno corrente, escludendo i dati cronologici non necessari.

Riquadro di filtro nell'Importazione guidata tabella

Come ridurre i requisiti di spazio di una colonna

È possibile applicare altre tecniche per rendere una colonna idonea per la compressione. Tenere presente che l'unica caratteristica della colonna che influisce sulla compressione è il numero di valori univoci. In questa sezione verrà illustrato come modificare alcune colonne per ridurre il numero di valori univoci.

Modifica delle colonne DateTime

In molti casi, le colonne DateTime occupano una notevole quantità di spazio. È comunque possibile ridurre in vari modi i requisiti di archiviazione per questo tipo di dati. Le tecniche variano in base al modo in cui viene utilizzata la colonna e al livello di familiarità con la creazione di query SQL.

Le colonne DateTime includono una parte di data e un'ora. Per verificare se una colonna è necessaria, rispondere alla stessa domanda più volte per una colonna DateTime:

  • È necessaria la parte dell'ora?

  • È necessaria la parte dell'ora a livello di ore, minuti, secondi o millisecondi?

  • Esistono più colonne DateTime perché si desidera calcolarne la differenza o solo per aggregare i dati in base ad anno, mese, trimestre e così via?

La risposta a ognuna di queste domande determina le opzioni disponibili per gestire la colonna DateTime.

Tutte queste soluzioni richiedono la modifica di una query SQL. Per semplificare la modifica della query, è consigliabile escludere con un filtro almeno una colonna in ogni tabella. In questo modo, è possibile modificare la costruzione della query da un formato abbreviato (SELECT *) in un'istruzione SELECT che include i nomi di colonna completi, che sono più semplici da modificare.

Esaminiamo le query create. Dalla finestra di dialogo Proprietà tabella è possibile passare all'editor di query e visualizzare la query SQL corrente per ogni tabella.

barra multifunzione della finestra di powerpivot con il comando proprietà tabella

In Proprietà tabella selezionare Editor di query.

Aprire l'editor di query dalla finestra di dialogo Proprietà tabella

Nell'editor di query è visualizzata la query SQL utilizzata per popolare la tabella. Se durante l'importazione è stata esclusa una colonna, la query includerà i nomi di colonna completi:

query sql utilizzata per recuperare i dati

Se invece la tabella è stata importata interamente, senza deselezionare alcuna colonna né applicare alcun filtro, la query verrà visualizzata nel formato "SELECT * FROM", che è più difficile da modificare:

query sql nella sintassi predefinita abbreviata

Modifica della query SQL

Dopo aver individuato la query, è possibile modificarla per ridurre ulteriormente le dimensioni del modello.

  1. Per le colonne contenenti valute o dati decimali, utilizzare la sintassi seguente per eliminare i decimali, se non sono necessari:

    "SELECT ROUND([Nome_colonna_decimali],0)… ."

    Se i centesimi sono necessari, ma non le frazioni di centesimi, sostituire 0 con 2. Se si utilizzano numeri negativi, è possibile arrotondarli a unità, decine, centinaia e così via.

  2. Se è disponibile una colonna DateTime denominata dbo.Bigtable.[Date Time] e la parte relativa all'ora non è necessaria, utilizzare la sintassi seguente per eliminarla:

    "SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) "

  3. Se è disponibile una colonna DateTime denominata dbo.Bigtable.[Date Time] e sono necessarie sia la parte relativa alla data che quella dell'ora, utilizzare più colonne nella query SQL anziché la singola colonna DateTime:

    "SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]"

    Utilizzare tutte le colonne necessarie per archiviare ogni parte in colonne distinte.

  4. Se è necessario includere le ore e i minuti e si preferisce visualizzarli in un'unica colonna dell'ora, è possibile utilizzare la sintassi seguente:

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Se sono disponibili due colonne DateTime, ad esempio [Start Time] e [End Time] e in realtà è necessaria solo la differenza tra i due orari in una colonna denominata [Duration], rimuovere entrambe le colonne dall'elenco e aggiungere quanto segue:

    "datediff(ss,[Start Date],[End Date]) as [Duration]"

    Se si utilizza la parola chiave ms anziché ss, si otterrà la durata in millisecondi

Utilizzo di misure calcolate DAX anziché colonne

Se il linguaggio delle espressioni DAX è già stato utilizzato in precedenza, potrebbe essere noto che le colonne calcolate vengono utilizzate per derivare nuove colonne in base ad altre colonne del modello, mentre le misure calcolate vengono definite una volta nel modello ma valutate solo se utilizzate in una tabella pivot o in un altro rapporto.

Una delle tecniche disponibili per risparmiare memoria consiste nel sostituire le colonne regolari o calcolate con misure calcolate. L'esempio classico è rappresentato da Prezzo unitario, Quantità e Totale. Se sono disponibili tutte e tre queste colonne, è possibile risparmiare spazio mantenendone solo due e calcolando la terza mediante DAX.

Quali colonne mantenere

Nell'esempio seguente mantenere le colonne Quantità e Prezzo unitario, in quanto contengono meno valori rispetto al totale. Per calcolare il totale, aggiungere una misura calcolata come la seguente:

"TotalSales:=sumx(‘Sales Table’,’Sales Table’[Prezzo unitario]*’Sales Table’[Quantità])"

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

Conclusioni

In questo articolo sono stati illustrati diversi approcci che possono rivelarsi utili per creare un modello con un utilizzo più efficiente della memoria. Per ridurre i requisiti di memoria e le dimensioni dei file di un modello di dati, è necessario ridurre il numero complessivo di colonne e righe, nonché il numero di valori univoci visualizzati in ogni colonna. Di seguito sono riportate alcune delle tecniche illustrate:

  • La rimozione di colonne è ovviamente il modo migliore per risparmiare spazio. Stabilire quali sono le colonne effettivamente necessarie.

  • A volte è possibile rimuovere una colonna e sostituirla con una misura calcolata nella tabella.

  • Le righe di una tabella potrebbero non essere tutte necessarie. È possibile escludere mediante filtro alcune righe nell'Importazione guidata tabella.

  • In generale, la divisione di una singola colonna in più parti distinte è un modo efficace per ridurre il numero di valori univoci in una colonna. Ogni parte conterrà un numero ridotto di valori univoci e il totale combinato sarà minore del valore della colonna unificata originale.

  • In molti casi, può anche essere necessario utilizzare le parti distinte come filtri dati nei rapporti. Se appropriato, è possibile creare gerarchie da parti come Ore, Minuti e Secondi.

  • Spesso, le colonne contengono una quantità di informazioni maggiore del necessario. Si supponga ad esempio che una colonna archivi decimali, ma che sia stata applicata una formattazione per nasconderli tutti. L'arrotondamento può essere molto efficace per ridurre le dimensioni di una colonna numerica.

Ora che si esegue questa operazione è possibile per ridurre le dimensioni della cartella di lavoro, si consiglia di eseguire anche l'utilità di ottimizzazione dimensioni cartella di lavoro. Analizza la cartella di lavoro di Excel e, se possibile, comprime ulteriormente. Scaricare l' utilità di ottimizzazione dimensioni cartella di lavoro.

Link correlati

Specifica e limiti del modello di dati

Download di Utilità di ottimizzazione dimensioni cartella di lavoro

PowerPivot: Potente strumento di analisi e modellazione dei dati in Excel

Nota : Dichiarazione di non responsabilità per la traduzione automatica: Il presente articolo è stato tradotto tramite un software di traduzione automatica e non da una persona. Microsoft offre le traduzioni automatiche per consentire a coloro che non conoscono la lingua inglese di leggere gli articoli sui prodotti, sui servizi e sulle tecnologie Microsoft. Dal momento che l'articolo è stato tradotto automaticamente, potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli.

Amplia le tue competenze
Esplora i corsi di formazione
Ottieni in anticipo le nuove caratteristiche
Partecipa al programma Office Insider

Queste informazioni sono risultate utili?

Grazie per i tuoi commenti e suggerimenti

Grazie per il tuo feedback! Potrebbe essere utile metterti in contatto con uno dei nostri operatori del supporto di Office.

×