In Excel è possibile creare modelli di dati contenenti milioni di righe e quindi eseguire un'analisi efficace dei dati rispetto a 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.
Anche se è possibile creare facilmente enormi modelli di dati in Excel, esistono diversi motivi per cui non è necessario. Prima di tutto, i modelli di grandi dimensioni che contengono moltissime tabelle e colonne sono troppo specializzati per la maggior parte delle analisi e rendono scomodo l'elenco dei campi. 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 della cartella di lavoro che contengono milioni di righe, il limite di 10 MB sarà piuttosto rapido. Vedere Specifica e limiti del modello di dati.
In questo articolo imparerai a creare un modello strettamente costruito che sia più facile da usare e usi meno memoria. Dedicare il tempo necessario per apprendere le procedure consigliate per una progettazione efficiente dei modelli renderà più efficiente qualsiasi modello creato e usato, sia che lo si stia visualizzando in Excel, Microsoft 365 SharePoint Online, in un server Office Web Apps o in SharePoint.
Valutare anche l'esecuzione di Workbook Size Optimizer. Analizza la cartella di lavoro di Excel e, se possibile, la comprime ulteriormente. Scaricare Workbook Size Optimizer.
Contenuto dell'articolo
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 ottenere una frazione delle dimensioni originali.
In media, un modello di dati sarà da 7 a 10 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 essere facilmente di 1 MB o meno. Il grado di compressione effettivamente raggiunto dipende principalmente dal numero di valori univoci in ogni colonna. Più valori univoci sono, maggiore è la memoria necessaria per archiviarli.
Perché stiamo parlando di compressione e valori univoci? Poiché la creazione di un modello efficiente che riduci al minimo l'uso della memoria è necessario ottimizzare la compressione e il modo più semplice consiste nell'eliminare le colonne che non sono realmente necessarie, soprattutto 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 illustra in dettaglio questa tecnica.
Niente batte una colonna inesistente per un uso ridotto della memoria
La colonna più efficiente in termini di memoria è quella che non è mai stata importata 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 lo fa o non sei sicuro, lascialo fuori. Se necessario, è sempre possibile aggiungere nuove colonne in un secondo momento.
Due esempi di colonne da escludere sempre
Il primo esempio riguarda dati provenienti da un data warehouse. In un data warehouse è comune trovare gli elementi dei processi ETL che caricano e aggiornano i dati nel magazzino. Le colonne come "data di creazione", "data di aggiornamento" e "esecuzione ETL" vengono create quando i dati vengono caricati. Nessuna di queste colonne è necessaria nel modello e deve essere deselezionata durante l'importazione dei dati.
Il secondo esempio consiste nell'omettere la colonna di chiave primaria durante l'importazione di una tabella dei fatti.
Molte tabelle, incluse le tabelle dei fatti, hanno chiavi primarie. Per la maggior parte delle tabelle, ad esempio quelle che contengono dati su clienti, dipendenti o vendite, è consigliabile impostare 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 per scopi di normalizzazione, è meno utile in un modello di dati in cui si vogliono usare 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 relativa chiave primaria. Le chiavi primarie in una tabella dei fatti occupano enormi quantità di spazio nel modello, ma non offrono alcun vantaggio, in quanto 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 sono spesso denominate "tabelle dei fatti". Le tabelle dei fatti includono in genere dati sulle prestazioni aziendali o sulle transazioni, ad esempio punti dati su vendite e costi aggregati e allineati alle unità organizzative, ai prodotti, ai segmenti di mercato, alle aree geografiche e così via. Tutte le colonne di una tabella dei fatti che contengono dati business o che possono essere usate per fare 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 di chiave primaria della tabella dei fatti, costituita da valori univoci presenti solo nella tabella dei fatti e in nessun'altra posizione. Poiché le tabelle dei fatti sono così grandi, alcuni dei maggiori miglioramenti nell'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. Per controllare quali colonne sono 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, si selezionano le tabelle da importare.
Per ogni tabella è possibile fare clic sul pulsante Anteprima & filtro e selezionare le parti effettivamente necessarie. È consigliabile deselezionare tutte le colonne e quindi procedere al controllo delle colonne desiderate, dopo aver considerato se sono necessarie per l'analisi.
Come filtrare solo le righe necessarie?
Molte tabelle in database e data warehouse aziendali contengono dati cronologici accumulati in lunghi periodi di tempo. Inoltre, è possibile che le tabelle a cui si è interessati contengano informazioni per aree dell'azienda non necessarie per un'analisi specifica.
Con 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 non necessari.
E se fosse necessaria la colonna; possiamo ancora ridurre il suo costo dello spazio?
Esistono alcune tecniche aggiuntive che è possibile 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 si apprenderà in che modo alcune colonne possono essere modificate 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 al modo in cui si usa la colonna e al livello di comfort durante la creazione di query SQL.
Le colonne Datetime includono una data part 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 tempo al livello di ore? verbale? Secondi? Millisecondi?
-
Si hanno più colonne Datetime perché si vuole calcolare la differenza tra di esse o semplicemente 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. Filtrando una colonna, si modifica la struttura della query da un formato abbreviato (SELECT *) a un'istruzione SELECT che include nomi di colonna completi, che sono molto più facili da modificare.
Diamo un'occhiata alle query 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 è stata filtrata una colonna durante l'importazione, 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 decimali o di valuta, se i decimali non sono necessari, usare questa sintassi per eliminare i decimali:
"SELECT ROUND([Decimal_column_name],0)... .”
Se sono necessari i centesimi ma non le frazioni di centesimo, sostituire lo 0 con 2. Se si usano numeri negativi, è possibile arrotondare a unità, decine, centinaia e così via.
-
Se si ha una colonna Datetime denominata dbo. Bigtable. [Data/ora] e non è necessaria la parte Ora, utilizzare la sintassi per eliminare l'ora:
"SELECT CAST (dbo. Bigtable. [Data/ora] come data) AS [Data/ora]) "
-
Se si ha una colonna Datetime denominata dbo. Bigtable. [Data/ora] e sono necessarie sia le parti Data che Ora, usare più colonne nella query SQL invece della singola colonna Datetime:
"SELECT CAST (dbo. Bigtable. [Data/ora] come data ) AS [Data/ora],
datepart(hh; dbo. Bigtable. [Data/ora]) come [Date Time Hours],
datepart(mi; dbo. Bigtable. [Data/ora]) come [Minuti data/ora],
datepart(ss; dbo. Bigtable. [Data/ora]) come [Secondi data/ora],
datepart(ms, dbo. Bigtable. [Data/ora]) come [Millisecondi di data/ora]"
Usare tutte le colonne necessarie per archiviare ogni parte in colonne separate.
-
Se sono necessarie ore e minuti e li si preferisce come colonna di una sola volta, è possibile usare la sintassi:
Timefromparts(datepart(hh, dbo. Bigtable. [Data ora]), datepart(mm, dbo. Bigtable. [Data/ora])) come [Data ora HourMinute]
-
Se sono presenti due colonne datetime, ad esempio [Ora inizio] e [Ora fine], e ciò di cui si ha realmente bisogno è la differenza di ora tra le colonne in secondi come colonna denominata [Durata], rimuovere entrambe le colonne dall'elenco e aggiungere:
"data.diff;[Data inizio],[Data di fine]) come [Durata]"
Se usi la parola chiave ms invece di ss, otterrai la durata in millisecondi
Uso delle misure calcolate DAX anziché delle colonne
Se si usa il linguaggio delle espressioni DAX in precedenza, è possibile che si sappia già che le colonne calcolate vengono 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 valutate solo se usate in una tabella pivot o in un altro rapporto.
Una tecnica di salvataggio della memoria consiste nel sostituire le colonne normali o calcolate con misure calcolate. L'esempio classico è Prezzo unitario, Quantità e Totale. Se si hanno tutte e tre le opzioni, è possibile risparmiare spazio mantenendo solo due e calcolando la terza usando DAX.
Quali 2 colonne conservare?
Nell'esempio precedente mantenere Quantità e Prezzo unitario. Questi due hanno meno valori del Totale. Per calcolare totale, aggiungere una misura calcolata come:
"TotalSales:=sumx('Tabella Vendite','Tabella Vendite'[Prezzo unitario]*'Tabella Vendite'[Quantità])"
Le colonne calcolate sono simili a normali colonne in cui entrambi occupano spazio nel modello. Al contrario, le misure calcolate vengono calcolate al volo e non occupano spazio.
Conclusione
In questo articolo abbiamo parlato di diversi approcci che possono aiutarti a creare un modello più efficiente in termini di memoria. Il modo per ridurre le dimensioni del file e i requisiti di memoria di un modello di dati consiste nel ridurre il numero complessivo di colonne e righe e il numero di valori univoci visualizzati in ogni colonna. Ecco alcune tecniche illustrate:
-
La rimozione di colonne è naturalmente il modo migliore per risparmiare spazio. Decidere quali colonne sono realmente 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 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. Ognuna delle parti avrà un numero ridotto di valori univoci e il totale combinato sarà minore della colonna unificata originale.
-
In molti casi sono necessarie anche le parti distinte da usare come filtri dei dati nei report. Se appropriato, è possibile creare gerarchie da parti come Ore, Minuti e Secondi.
-
Spesso le colonne contengono più informazioni di quante ne servono. Si supponga, ad esempio, che una colonna archivi i decimali, ma che sia stata applicata una 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 disponibili per ridurre le dimensioni della cartella di lavoro, è consigliabile eseguire anche Workbook Size Optimizer. 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
PowerPivot: potente strumento di analisi e modellazione dei dati in Excel
