In Excel è possibile creare modelli di dati contenenti milioni di righe e quindi eseguire potenti analisi dei dati su questi modelli. I modelli di dati possono essere creati con o senza il componente aggiuntivo Power Pivot per supportare un numero qualsiasi di tabelle pivot, grafici e visualizzazioni di Power View nella stessa cartella di lavoro.
Sebbene sia possibile creare facilmente modelli di dati di grandi dimensioni in Excel, esistono diversi motivi per non farlo. In primo luogo, i modelli di grandi dimensioni che contengono moltitudini di tabelle e colonne sono eccessivi per la maggior parte delle analisi e creano un elenco di campi ingombrante. In secondo luogo, i modelli di grandi dimensioni consumano memoria preziosa, influenzando negativamente altre applicazioni e report che condividono le stesse risorse di sistema. Infine, in Microsoft 365 sia SharePoint Online che Excel Web App limitano le dimensioni di un file di Excel a 10 MB. Per i modelli di dati delle cartelle di lavoro che contengono milioni di righe, il limite di 10 MB è piuttosto rapido. Vedere Specifica e limiti del modello di dati.
In questo articolo imparerai a creare un modello strettamente costruito che è più facile da usare e usa meno memoria. Dedicare del tempo all'apprendimento delle procedure consigliate per una progettazione efficiente di modelli ripagherà lungo la strada per qualsiasi modello creato e utilizzato, sia che lo si visualizzi in Excel, Microsoft 365 SharePoint Online, in un server di Office Web App Web o in SharePoint.
Valutare anche l'esecuzione di Ottimizzatore dimensioni cartella di lavoro. Analizza la cartella di lavoro di Excel e, se possibile, la comprime ulteriormente. Scaricare Workbook Size Optimizer.
Contenuto dell'articolo
Niente batte una colonna inesistente per un utilizzo ridotto della memoria
E se avessimo bisogno della colonna; Possiamo ancora ridurre il suo costo di spazio?
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 fino a raggiungere una frazione delle sue dimensioni originali.
In media, un modello di dati può essere da sette a dieci volte più piccolo degli stessi dati nel punto di origine. Ad esempio, se si importano 7 MB di dati da un database di SQL Server, il modello di dati in Excel potrebbe facilmente essere di 1 MB o meno. Il grado di compressione effettivamente raggiunto dipende principalmente dal numero di valori univoci in ogni colonna. Maggiore è il numero di valori univoci, maggiore è la quantità di memoria necessaria per archiviarli.
Perché si parla di compressione e valori univoci? Perché la creazione di un modello efficiente che riduce al minimo l'utilizzo della memoria è tutta una questione di massimizzazione della compressione, e il modo più semplice per farlo consiste nell'eliminare le colonne non necessarie, soprattutto se tali colonne includono un numero elevato di valori univoci.
Nota
Le differenze nei requisiti di archiviazione per le singole colonne possono essere enormi. In alcuni casi, è preferibile avere più colonne con un numero basso di valori univoci piuttosto che una colonna con un numero elevato di valori univoci. La sezione sulle ottimizzazioni Datetime illustra in dettaglio questa tecnica.
Niente batte una colonna inesistente per un utilizzo ridotto della memoria
La colonna con più efficienza in termini di memoria è quella che non è mai stata importata. Se si desidera creare un modello efficiente, esaminare ogni colonna e chiedersi se contribuisce all'analisi che si desidera eseguire. Se non lo è o non sei sicuro, omettilo. È sempre possibile aggiungere nuove colonne in un secondo momento, se necessario.
Due esempi di colonne che devono essere sempre escluse
Il primo esempio riguarda i dati che provengono da un data warehouse. In un data warehouse, è comune trovare artefatti di processi ETL che caricano e aggiornano i dati nel warehouse. 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 i 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, includono chiavi primarie. Per la maggior parte delle tabelle, ad esempio quelle che contengono dati su clienti, dipendenti o vendite, è necessaria la chiave primaria della tabella in modo da poterla usare 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. Sebbene sia necessario ai fini della normalizzazione, è meno utile in un modello di dati in cui si vogliono usare solo le colonne per l'analisi o per stabilire relazioni tra tabelle. Per questo motivo, quando si esegue l'importazione da una tabella dei fatti, non includerne la chiave primaria. Le chiavi primarie in una tabella dei fatti occupano enormi quantità di spazio nel modello, ma non offrono alcun vantaggio, perché non possono essere usate per creare relazioni.
Nota
Nei data warehouse e nei 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 dati sulle prestazioni aziendali o sulle transazioni, ad esempio punti dati di vendita e di costo 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 ai dati archiviati in altre tabelle devono essere incluse nel modello per supportare l'analisi dei dati. La colonna da escludere è la colonna chiave primaria della tabella dei fatti, costituita da valori univoci che esistono solo nella tabella dei fatti e in nessun'altra posizione. Poiché le tabelle dei fatti sono così grandi, alcuni dei maggiori vantaggi in termini di efficienza del modello derivano dall'esclusione di righe o colonne dalle tabelle dei fatti.
Come escludere le colonne non necessarie
I modelli efficienti contengono solo le colonne effettivamente necessarie nella cartella di lavoro. Se si vuole controllare quali colonne sono incluse nel modello, sarà necessario usare l'Importazione guidata tabelle nel componente aggiuntivo Power Pivot per importare i dati invece della finestra di dialogo "Importa dati" in Excel.
All'avvio dell'Importazione guidata tabelle selezionare le tabelle da importare.
Per ogni tabella, è possibile fare clic sul pulsante Anteprima & filtro e selezionare le parti della tabella veramente necessarie. È consigliabile deselezionare tutte le colonne e quindi procedere al controllo delle colonne desiderate, dopo aver considerato se sono necessarie per l'analisi.
Che ne dici di filtrare solo le righe necessarie?
Molte tabelle nei database aziendali e nei data warehouse contengono dati cronologici accumulati in lunghi periodi di tempo. Inoltre, le tabelle a cui si è interessati potrebbero contenere informazioni per aree dell'azienda non necessarie per l'analisi specifica.
L'Importazione guidata tabelle consente di filtrare i dati cronologici o non correlati risparmiando così una grande quantità di spazio nel modello. Nell'immagine seguente un filtro data viene usato per recuperare solo le righe che contengono dati per l'anno corrente, escludendo i dati cronologici che non saranno necessari.
E se avessimo bisogno della colonna; Possiamo ancora ridurre il suo costo di spazio?
Ci sono alcune tecniche aggiuntive che si possono applicare per rendere una colonna un candidato migliore per la compressione. Tenere presente che l'unica caratteristica della colonna che influisce sulla compressione è il numero di valori univoci. In questa sezione viene illustrato come modificare alcune colonne per ridurre il numero di valori univoci.
Modifica delle colonne Datetime
In molti casi, le colonne Datetime occupano molto spazio. Fortunatamente, esistono diversi modi per ridurre i requisiti di archiviazione per questo tipo di dati. Le tecniche variano in base all'uso della colonna e al livello di familiarità con la creazione di query SQL.
Le colonne datetime includono una parte di data e un'ora. Quando ci si chiede se è necessaria una colonna, porre più volte la stessa domanda per una colonna Datetime:
- Ho bisogno della parte tempo?
- Ho bisogno della parte temporale a livello di ore? , minuti? , Secondi? , millisecondi?
- Sono presenti più colonne Datetime perché si vuole calcolare la differenza o semplicemente per aggregare i dati per anno, mese, trimestre e così via.
Il modo in cui si risponde a ognuna di queste domande determina le opzioni per gestire la colonna Datetime.
Tutte queste soluzioni richiedono la modifica di una query SQL. Per semplificare la modifica delle query, è consigliabile filtrare almeno una colonna in ogni tabella. Applicando un filtro a una colonna, si modifica la costruzione della query da un formato abbreviato (SELECT *) a un'istruzione SELECT che include nomi di colonna completi, che sono molto più facili da modificare.
Esaminare le query che vengono create automaticamente. Dalla finestra di dialogo Proprietà tabella è possibile passare all'editor di query e visualizzare la query SQL corrente per ogni tabella.
In Proprietà tabella selezionare Editor di query.
L'editor di query mostra la query SQL usata per popolare la tabella. Se durante l'importazione è stata filtrata una colonna, la query include nomi di colonna completi:
Al contrario, se è stata importata una tabella nella sua interezza, senza deselezionare alcuna colonna o applicare alcun filtro, la query verrà visualizzata come "Seleziona * da ", che sarà più difficile da modificare:
|
|---|
Modifica della query SQL
Ora che si sa come trovare la query, è possibile modificarla per ridurre ulteriormente le dimensioni del modello.
- Per le colonne contenenti dati di valuta o decimali, se i decimali non sono necessari, usare questa sintassi per eliminare i decimali:
"SELEZIONA ROUND([Decimal_column_name],0)... .”
Se occorrono centesimi ma non frazioni di centesimi, sostituire 0 con 2. Se si utilizzano numeri negativi, è possibile arrotondare a unità, decine, centinaia e così via. - Se è presente una colonna Datetime denominata dbo. Tabella grande. [Data Ora] e non è necessaria la parte Ora, usare la sintassi per eliminare l'ora:
"SELECT CAST (dbo. Tabella grande. [Data ora] come data) AS [Data ora]) " - Se è presente una colonna Datetime denominata dbo. Tabella grande. [Data e ora] e sono necessarie sia la parte di data che quella di ora, usare più colonne nella query SQL invece della singola colonna Datetime:
"SELECT CAST (dbo. Tabella grande. [Data Ora] come data ) AS [Data Ora],
DatePart(HH, DBO. Tabella grande. [Data e ora]) come [Data Ora Ore],
DatePart(mi, dbo. Tabella grande. [Data e ora]) come [Data Ora Minuti],
DatePart(ss, dbo. Tabella grande. [Data e ora]) come [Data Ora Secondi],
DatePart(MS, DBO. Tabella grande. [Data e ora]) come [Data Ora Millisecondi]"
Usare tutte le colonne necessarie per archiviare ogni parte in colonne separate. - Se sono necessarie ore e minuti e si preferisce che siano insieme in un'unica colonna temporale, è possibile utilizzare la sintassi :
Timefromparts(datepart(hh, dbo. Tabella grande. [Data e ora]), datepart(mm, dbo. Tabella grande. [Data e ora])) come [Data Ora HourMinute] - Se sono presenti due colonne datetime, ad esempio [Ora di inizio] e [Ora di fine], e ciò di cui hai veramente bisogno è la differenza di tempo in secondi come colonna denominata [Durata], rimuovi entrambe le colonne dall'elenco e aggiungi:
"datediff(ss,[Data inizio],[Data fine]) come [Durata]"
Se si utilizza la parola chiave ms invece di ss, si ottiene la durata in millisecondi
Uso delle misure calcolate di DAX al posto delle colonne
Se si è già usato il linguaggio delle espressioni DAX, si potrebbe già sapere che le colonne calcolate vengono usate per derivare nuove colonne in base a un'altra colonna del modello, mentre le misure calcolate vengono definite una sola volta nel modello, ma valutate solo se usate in una tabella pivot o in un altro report.
Una tecnica per risparmiare memoria consiste nel sostituire le colonne normali o calcolate con misure calcolate. L'esempio classico è Prezzo unitario, Quantità e Totale. Se si dispone di tutti e tre, è possibile risparmiare spazio mantenendone solo due e calcolando il terzo tramite DAX.
Quali 2 colonne dovresti tenere?
Nell'esempio precedente mantenere Quantità e Prezzo unitario. Questi due hanno valori inferiori rispetto al totale. Per calcolare il totale, aggiungere una misura calcolata come:
"TotaleVendite:=somma.SOMMA('Tabella Vendite','Tabella Vendite'[Prezzo unitario]*'Tabella Vendite'[Quantità])"
Le colonne calcolate sono come le colonne normali, in quanto occupano entrambe spazio nel modello. Al contrario, le misure calcolate vengono calcolate al volo e non occupano spazio.
Conclusione
In questo articolo sono stati illustrati diversi approcci che consentono di creare un modello più efficiente dal punto di vista della memoria. Per ridurre le dimensioni del file e i requisiti di memoria di un modello di dati, il modo consiste nel ridurre il numero complessivo di colonne e righe e il numero di valori univoci visualizzati in ogni colonna. Ecco alcune tecniche che abbiamo trattato:
- La rimozione delle colonne è ovviamente il modo migliore per risparmiare spazio. Decidere quali colonne sono veramente necessarie.
- A volte è possibile rimuovere una colonna e sostituirla con una misura calcolata nella tabella.
- Potrebbe non essere necessario disporre di tutte le righe di una tabella. È possibile applicare filtri alle righe nell'Importazione guidata tabella.
- In generale, la suddivisione di una singola colonna in più parti distinte è un buon modo per ridurre il numero di valori univoci in una colonna. Ognuna delle parti avrà un numero ridotto di valori univoci e il totale combinato sarà inferiore alla colonna unificata originale.
- In molti casi, sono necessarie anche le parti distinte da usare come filtri dei dati nei report. Quando è opportuno, è possibile creare gerarchie da parti come ore, minuti e secondi.
- In molti casi, le colonne contengono più informazioni di quelle necessarie. Si supponga, ad esempio, che in una colonna siano archiviati i decimali, ma che sia stata applicata la formattazione per nasconderli tutti. L'arrotondamento può essere molto efficace per ridurre le dimensioni di una colonna numerica.
Dopo aver eseguito il possibile per ridurre le dimensioni della cartella di lavoro, è consigliabile eseguire anche Ottimizzazione dimensioni cartella di lavoro. Analizza la cartella di lavoro di Excel e, se possibile, la comprime ulteriormente. Scaricare Workbook Size Optimizer.
Collegamenti correlati
Specifica e limiti del modello di dati
Ottimizzatore delle dimensioni della cartella di lavoro
PowerPivot: potente strumento di analisi e modellazione dei dati in Excel