Creare un modello di dati con un utilizzo efficiente della memoria usando Excel e il componente aggiuntivo Power Pivot

In Excel 2013 o versioni successive è possibile creare modelli di dati contenenti milioni di righe e quindi eseguire una potente analisi dei dati rispetto a questi modelli. I 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 descritti i modelli di dati in Excel 2013. Tuttavia, le stesse funzionalità di modellazione dei dati e Power Pivot introdotte in Excel 2013 si applicano anche a Excel 2016. La differenza tra queste versioni di Excel è effettivamente minima.

Anche se è possibile creare in modo semplice modelli di dati enormi in Excel, sono diversi i motivi per cui non farlo. In primo luogo, i modelli di grandi dimensioni che contengono moltitudini di tabelle e colonne sono esagerati per la maggior parte delle analisi e costituiscono un elenco di campi ingombrante. In secondo luogo, i modelli di grandi dimensioni usano memoria preziosa, che interessano negativamente altre applicazioni e report che condividono le stesse risorse di sistema. Infine, in Office 365, sia SharePoint Online che Excel Web App limitano le dimensioni di un file di Excel a 10 MB. Per i modelli di dati della cartella di lavoro che contengono milioni di righe, è possibile eseguire rapidamente il limite di 10 MB. Vedere specifiche e limiti del modello di dati.

In questo articolo imparerai a creare un modello strettamente costruito che è più facile da usare e che usa meno memoria. Il tempo necessario per apprendere le procedure consigliate in un modello efficiente verrà pagato lungo la strada per qualsiasi modello creato e usato, indipendentemente dal fatto che si stia visualizzando in Excel 2013, Office 365 SharePoint Online, in un server di Office Web Apps o in SharePoint 2013.

Valuta anche l'uso dell'utilità di ottimizzazione delle dimensioni della cartella di lavoro. Analizza la cartella di lavoro di Excel e, se possibile, la comprime ulteriormente. Scaricare lo strumento di ottimizzazione delle dimensioni della cartella di lavoro.

In questo articolo

Rapporti di compressione e motore di analisi in memoria

Niente batte una colonna inesistente per un uso limitato della memoria

Due esempi di colonne da escludere sempre

Come escludere colonne non necessarie

Come filtrare solo le righe necessarie

Cosa fare se è necessaria la colonna; è ancora possibile ridurre il costo dello spazio?

Modifica delle colonne DateTime

Modifica della query SQL

Uso di misure calcolate DAX anziché colonne

Quali sono le due colonne da mantenere?

Conclusione

Collegamenti correlati

Rapporti di compressione e motore di analisi in memoria

I modelli di dati in Excel usano il motore di analisi in memoria per archiviare i dati in memoria. Il motore implementa potenti tecniche di compressione per ridurre i requisiti di archiviazione, riducendo un set di risultati finché non si tratta di una frazione delle dimensioni originali.

In media, è possibile prevedere che un modello di dati sia da 7 a 10 volte più piccolo degli stessi dati al suo punto di origine. Se ad esempio si importano 7 MB di dati da un database di SQL Server, il modello di dati in Excel potrebbe essere facilmente 1 MB o inferiore. Il grado di compressione effettivamente raggiunto dipende principalmente dal numero di valori univoci in ogni colonna. I valori più univoci, maggiore è la memoria necessaria per archiviarli.

Perché si parla di compressione e valori univoci? Poiché la creazione di un modello efficiente che riduce al minimo l'utilizzo della memoria riguarda la massimizzazione della compressione e il modo più semplice per eseguire questa operazione consiste nell'eliminare tutte le colonne di cui non si ha realmente bisogno, in particolare se tali colonne includono un numero elevato di valori univoci.

Nota:  Le differenze nei requisiti di archiviazione per singole colonne possono essere enormi. In alcuni casi, è preferibile avere più colonne con un numero basso di valori univoci anziché una colonna con un numero elevato di valori univoci. La sezione sulle ottimizzazioni DateTime include questa tecnica in dettaglio.

Niente batte una colonna inesistente per un uso limitato della memoria

La colonna più efficiente della memoria è quella che non hai mai importato in primo luogo. Se si vuole creare un modello efficiente, esaminare ogni colonna e chiedersi se contribuisce all'analisi che si vuole eseguire. Se non è così o non si è sicuri, lasciarlo fuori. Se necessario, è sempre possibile aggiungere nuove colonne in un secondo momento.

Due esempi di colonne da escludere sempre

Il primo esempio si riferisce ai dati originati da un data warehouse. In un data warehouse è comune trovare gli elementi dei processi ETL che caricano e aggiornano i dati in warehouse. Le colonne come "create date", "Update date" e "ETL Run" vengono create quando i dati vengono caricati. Nessuna di queste colonne è necessaria nel modello e deve essere deselezionata quando si importano dati.

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

Molte tabelle, incluse le tabelle dei fatti, hanno le chiavi primarie. Per la maggior parte delle tabelle, ad esempio quelle che contengono dati cliente, dipendente o vendite, è necessario usare la chiave primaria della tabella in modo che sia possibile usarla per creare relazioni nel modello.

Le tabelle dei fatti sono diverse. In una tabella dei fatti, la chiave primaria viene usata per identificare in modo univoco ogni riga. Anche se necessario per scopi di normalizzazione, è meno utile in un modello di dati in cui si vogliono solo le colonne usate per l'analisi o per stabilire relazioni tra tabelle. Per questo motivo, quando si esegue l'importazione da una tabella dei fatti, non includere la chiave primaria. Le chiavi primarie in una tabella dei fatti consumano enormi quantità di spazio nel modello, ma non consentono di sfruttare i vantaggi, perché non possono essere usati per creare relazioni.

Nota:  In data warehouse e database multidimensionali le tabelle di grandi dimensioni costituite principalmente da dati numerici vengono spesso definite "tabelle dei fatti". Le tabelle dei fatti includono in genere le prestazioni aziendali o i dati delle transazioni, ad esempio i punti di vendita e i dati sui 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 usate per il riferimento incrociato di dati archiviati in altre tabelle devono essere incluse nel modello per supportare l'analisi dei dati. La colonna che si vuole escludere è la colonna chiave primaria della tabella dei fatti, costituita da valori univoci presenti solo nella tabella dei fatti e in nessun altro posto. Poiché le tabelle dei fatti sono così vaste, alcune delle più grandi plusvalenze nell'efficienza del modello derivano dall'esclusione di righe o colonne dalle tabelle dei fatti.

Come escludere colonne non necessarie

I modelli efficienti contengono solo le colonne necessarie in realtà nella cartella di lavoro. Se si vogliono controllare le colonne incluse nel modello, è necessario usare l'importazione guidata tabella nel componente aggiuntivo Power Pivot per importare i dati anziché la finestra di dialogo "Importa dati" in Excel.

Quando si avvia l'importazione guidata tabella, è possibile selezionare le tabelle da importare.

Importazione guidata tabella nel componente aggiuntivo PowerPivot

Per ogni tabella, è possibile fare clic sul pulsante Anteprima & filtro e selezionare le parti della tabella di cui si ha realmente bisogno. È consigliabile deselezionare prima tutte le colonne e quindi procedere con il controllo delle colonne desiderate, dopo aver valutato se sono necessarie per l'analisi.

Riquadro di anteprima nell'Importazione guidata tabella

Come filtrare solo le righe necessarie

Molte tabelle nei database aziendali e nei data warehouse contengono dati cronologici accumulati per lunghi periodi di tempo. Inoltre, potresti scoprire che le tabelle a cui sei interessato contengono informazioni per le aree dell'azienda non necessarie per l'analisi specifica.

Usando l'importazione guidata tabella è possibile filtrare i dati cronologici o non correlati e quindi risparmiare molto spazio nel modello. Nell'immagine seguente viene usato un filtro data per recuperare solo le righe che contengono dati per l'anno corrente, esclusi i dati cronologici che non saranno necessari.

Riquadro di filtro nell'Importazione guidata tabella

Cosa fare se è necessaria la colonna; è ancora possibile ridurre il costo dello spazio?

Ci sono alcune tecniche aggiuntive che puoi applicare per rendere una colonna un candidato migliore per la compressione. Tieni presente che l'unica caratteristica della colonna che influenza la compressione è il numero di valori univoci. In questa sezione imparerai a modificare alcune colonne per ridurre il numero di valori univoci.

Modifica delle colonne DateTime

In molti casi, le colonne DateTime richiedono molto spazio. Fortunatamente, esistono diversi modi per ridurre i requisiti di archiviazione per questo tipo di dati. Le tecniche variano a seconda della modalità di utilizzo della colonna e del livello di comfort nella creazione di query SQL.

Le colonne DateTime includono una parte della data e un'ora. Quando ti chiedi se hai bisogno di una colonna, Richiedi più volte la stessa domanda per una colonna DateTime:

  • È necessaria la parte relativa all'ora?

  • È necessaria la parte relativa all'ora al livello delle ore? minuti? Secondi? millisecondi?

  • Sono presenti più colonne DateTime perché si vuole calcolare la differenza tra di esse oppure semplicemente per aggregare i dati per anno, mese, trimestre e così via.

La risposta a ognuna di queste domande determina le opzioni per la gestione della colonna DateTime.

Tutte queste soluzioni richiedono la modifica di una query SQL. Per semplificare la modifica della query, è consigliabile filtrare almeno una colonna in ogni tabella. Filtrando una colonna, è possibile modificare la struttura delle query da un formato abbreviato (SELECT *) a un'istruzione SELECT che include nomi di colonna completi, che sono molto più facili da modificare.

Esaminiamo le query create per l'utente. Nella finestra di dialogo Proprietà tabella è possibile passare all'editor di query e vedere 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

L'editor di query Mostra la query SQL usata per popolare la tabella. Se è stata filtrata una colonna durante l'importazione, la query include i nomi di colonna completi:

Query SQL utilizzata per recuperare i dati

Al contrario, se è stata importata una tabella integralmente, senza deselezionare alcuna colonna o applicando un filtro, la query verrà visualizzata come "Select * from", che sarà più difficile da modificare:

Query SQL nella sintassi predefinita abbreviata

Modifica della query SQL

Ora che si sa come trovare la query, è possibile modificarla per ridurre ulteriormente le dimensioni del modello.

  1. Per le colonne contenenti dati valuta o decimali, se non sono necessari i decimali, usare questa sintassi per eliminare i decimali:

    "Seleziona Arrotonda ([Decimal_column_name]; 0)... .”

    Se hai bisogno di centesimi ma non di frazioni di centesimi, Sostituisci 0 per 2. Se si usano numeri negativi, è possibile arrotondare a unità, decine, centinaia e così via.

  2. Se si ha una colonna DateTime denominata dbo. BigTable. [Data ora] e non è necessaria la parte relativa all'ora, usa la sintassi per eliminare l'ora:

    "SELECT CAST (dbo. BigTable. [Date Time] As Date) AS [Date Time]) "

  3. Se si ha una colonna DateTime denominata dbo. BigTable. [Data ora] ed è necessario utilizzare sia le parti di data che ora, usare più colonne della query SQL invece della singola colonna DateTime:

    "SELECT CAST (dbo. BigTable. [Date Time] As Date) AS [Date Time],

    DatePart (HH, dbo. BigTable. [Data ora]) come [data ore ora],

    DatePart (mi, dbo. BigTable. [Data ora]) come [data ora minuti],

    DatePart (SS, dbo. BigTable. [Data ora]) come [data ora secondi],

    DatePart (MS, dbo. BigTable. [Data ora]) come [data/ora millisecondi] "

    Usare tutte le colonne necessarie per archiviare ogni parte in colonne separate.

  4. Se occorrono ore e minuti e li si preferisce insieme come colonna di una sola volta, è possibile usare la sintassi seguente:

    TIMEFROMPARTS (DatePart (HH; dbo. BigTable. [Date Time]), DatePart (mm, dbo. BigTable. [Data ora])) come [data ora HourMinute]

  5. Se sono presenti due colonne DateTime, ad esempio [ora inizio] e [ora fine], e ciò che serve è la differenza di fuso orario tra di essi in secondi come colonna denominata [durata], rimuovere entrambe le colonne dall'elenco e aggiungere:

    "DateDiff (SS, [Data inizio], [data di fine]) come [durata]"

    Se si usa la parola chiave MS invece di SS, si otterrà la durata in millisecondi

Uso di misure calcolate DAX anziché colonne

Se si è già lavorato con il linguaggio dell'espressione DAX, è possibile che le colonne calcolate vengano usate per derivare nuove colonne in base a un'altra colonna del modello, mentre le misure calcolate sono definite una sola volta nel modello, ma vengono valutate solo se usate in un Tabella pivot o altro report.

Una tecnica per il salvataggio della memoria consiste nel sostituire le colonne regolari o calcolate con le misure calcolate. L'esempio classico è il prezzo unitario, la quantità e il totale. Se hai tutti e tre, puoi risparmiare spazio mantenendo solo due e calcolando il terzo usando DAX.

Quali sono le due colonne da mantenere?

Nell'esempio precedente Mantieni la quantità e il prezzo unitario. Questi due hanno meno valori del totale. Per calcolare Total, aggiungere una misura calcolata come:

"TotalSales: = SUMX (' Sales Table ',' Sales Table ' [prezzo unitario] *' Sales Table ' [quantità])"

Le colonne calcolate sono come le colonne normali che occupano entrambi spazio nel modello. Al contrario, le misure calcolate vengono calcolate al volo e non occupano spazio.

Conclusione

In questo articolo sono stati discussi diversi approcci che consentono di creare un modello più efficiente per la memoria. Per ridurre le dimensioni dei file e i requisiti di memoria di un modello di dati, è possibile ridurre il numero complessivo di colonne e righe e il numero di valori univoci visualizzati in ogni colonna. Ecco alcune tecniche che abbiamo analizzato:

  • La rimozione delle colonne è ovviamente il modo migliore per risparmiare spazio. Scegliere le colonne effettivamente necessarie.

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

  • Potrebbero non essere necessarie tutte le righe di una tabella. È possibile filtrare le righe nell'importazione guidata tabella.

  • In generale, suddividere una singola colonna in più parti distinte è un buon modo per ridurre il numero di valori univoci in una colonna. Ogni parte avrà un numero limitato di valori univoci e il totale combinato sarà più piccolo della colonna unificata originale.

  • In molti casi è anche necessario che le parti distinte vengano usate come filtri dei dati nei report. Quando necessario, è possibile creare gerarchie da parti come ore, minuti e secondi.

  • Molte volte, le colonne contengono più informazioni di quante ne occorrano. Supponiamo ad esempio che una colonna memorizzi i decimali, ma hai applicato la formattazione per nascondere tutti i decimali. L'arrotondamento può essere molto efficace per ridurre le dimensioni di una colonna numerica.

Dopo aver eseguito le operazioni possibili per ridurre le dimensioni della cartella di lavoro, è consigliabile eseguire anche l'utilità di ottimizzazione delle dimensioni della cartella di lavoro. Analizza la cartella di lavoro di Excel e, se possibile, la comprime ulteriormente. Scaricare lo strumento di ottimizzazione delle dimensioni della cartella di lavoro.

Collegamenti correlati

Specifica e limiti del modello di dati

Download per ottimizzare le dimensioni della cartella di lavoro

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

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

Queste informazioni sono risultate utili?

Grazie per il feedback!

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

×