In Excel 2013 o versione successiva, è possibile creare modelli di dati contenenti milioni di righe e quindi eseguire analisi dei dati efficaci 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 Power View nella stessa cartella di lavoro.
Nota: Questo articolo descrive 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 alle Excel 2016. In effetti, le differenze tra queste versioni di Excel.
Anche se è possibile creare facilmente modelli di dati Excel, esistono diversi motivi per non crearlo. Prima di tutto, i modelli di grandi dimensioni che contengono moltissime tabelle e colonne sono troppo complessi per la maggior parte delle analisi e costituiscono un elenco di campi ingombrante. In secondo piano, i modelli di grandi dimensioni usano memoria preziosa, con effetti negativi su 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 Excel a 10 MB. Per i modelli di dati della cartella di lavoro che contengono milioni di righe, il limite di 10 MB verrà raggiunto abbastanza rapidamente. Vedere Specifiche 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. L'apprendimento delle procedure consigliate per una progettazione efficiente dei modelli ripaga i costi di qualsiasi modello creato e utilizzato, sia che lo si visualizzi in Excel 2013, Microsoft 365 SharePoint Online, in un Office Web Apps Server o in SharePoint 2013.
È consigliabile eseguire anche l'Ottimizzatore dimensioni cartella di lavoro. Analizza la cartella di Excel e, se possibile, la comprime ulteriormente. Scaricare l'Ottimizzatore dimensioni cartella di lavoro.
Contenuto dell'articolo
Rapporti di compressione e motore di analisi in memoria
I modelli di dati Excel 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, è possibile prevedere che un modello di dati sia 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 SQL Server, il modello di dati in Excel potrebbe essere facilmente inferiore a 1 MB. Il grado di compressione effettivamente ottenuto 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? 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 farlo è eliminare le colonne non 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, è meglio avere più colonne con un numero basso di valori univoci invece di una colonna con un numero elevato di valori univoci. La sezione sulle ottimizzazioni Datetime illustra questa tecnica in dettaglio.
Niente batte una colonna inesistente per un utilizzo ridotto della memoria
La colonna più efficiente in termini di memoria è quella che non è mai stata importata. Se si vuole creare un modello efficiente, esaminare ogni colonna e chiedersi se contribuisce all'analisi da eseguire. Se non lo fa o non si è sicuri, lasciarlo fuori. Se necessario, è sempre possibile aggiungere nuove colonne in un secondo momento.
Due esempi di colonne che devono essere sempre escluse
Il primo esempio riguarda i dati provenienti da un data warehouse. In un data warehouse, è comune trovare gli elementi dei processi ETL che caricano e aggiornano i dati nel warehouse. Quando i dati vengono caricati, vengono create colonne come "data di creazione", "data di aggiornamento" e "esecuzione ETL". 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 chiavi primarie. Per la maggior parte delle tabelle, ad esempio quelle che contengono dati relativi a clienti, dipendenti o vendite, è necessario specificare 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. Anche se 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 includere la chiave primaria. Le chiavi primarie in una tabella dei fatti consumano 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 sono spesso denominate "tabelle dei fatti". Le tabelle dei fatti includono in genere dati sulle prestazioni aziendali o sulle transazioni, ad esempio i punti dati relativi a 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 business o che possono essere usate per creare riferimenti incrociati 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 altro punto. Poiché le tabelle dei fatti sono così enormi, alcuni dei maggiori vantaggi 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 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 della tabella effettivamente necessarie. È consigliabile deselezionare prima tutte le colonne e quindi procedere con la verifica delle colonne desiderate, dopo aver considerato se sono necessarie per l'analisi.
Come si filtrano 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 contengono informazioni per le aree aziendali non necessarie per l'analisi specifica.
Con l'Importazione guidata tabella è possibile filtrare i dati cronologici o non correlati, risparmiando così 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.
Cosa succede se è necessaria la colonna; possiamo ancora ridurre il 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 verrà illustrato come modificare alcune colonne per ridurre il numero di valori univoci.
Modifica delle colonne Datetime
In molti casi, le colonne Datetime hanno una grande quantità di spazio. Fortunatamente, esistono diversi modi per ridurre i requisiti di archiviazione per questo tipo di dati. Le tecniche variano a seconda del modo in cui si usa la colonna e del livello di comfort nella creazione SQL query.
Le colonne Datetime includono una parte data e un'ora. Quando ci si chiede se è necessaria una colonna, porre la stessa domanda più volte per una colonna Datetime:
-
È necessaria la parte temporale?
-
È necessaria la parte oraria a livello di ore? , minuti? , 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.
La modalità di risposta a ognuna di queste domande determina le opzioni per gestire la colonna Datetime.
Tutte queste soluzioni richiedono la modifica di una SQL query. Per semplificare la modifica della 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. Nella finestra di dialogo Proprietà tabella è possibile passare all'editor di query e visualizzare la query SQL query corrente per ogni tabella.
In Proprietà tabella selezionare Editor di query.
L'editor di query mostra SQL query 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 dati
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 non sono necessari i decimali, usare questa sintassi per eliminare i decimali:
"SELEZIONA ARROTONDA([Decimal_column_name];0)... .”
Se sono necessari i centesimi ma non le frazioni di centesimi, sostituire 0 per 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, usare 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] ed è necessario usare sia le parti Data che Ora, usare più colonne nella query di SQL invece della singola colonna Datetime:
"SELECT CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],
datepart(hh; dbo. Bigtable. [Data/ora]) come [Date Time Hours],
datepart(mi; dbo. Bigtable. [Data/ora]) come [Data Ora Minuti],
datepart(ss; dbo. Bigtable. [Data/ora]) come [Date Time Seconds],
datepart(ms; dbo. Bigtable. [Data/ora]) as [Date Time Milliseconds]"
Usare tutte le colonne necessarie per archiviare ogni parte in colonne separate.
-
Se sono necessarie ore e minuti e si preferiscono insieme come colonna una sola volta, è possibile usare la sintassi:
Timefromparts(datepart(hh; dbo. Bigtable. [Data Ora]), datepart(mm; dbo. Bigtable. [Data ora])) as [Date Time HourMinute]
-
Se si hanno due colonne datetime, ad esempio [Ora inizio] e [Ora fine], e ciò che serve è la differenza di ora in secondi come colonna denominata [Durata], rimuovere entrambe le colonne dall'elenco e aggiungere:
"datediff(ss;[Data inizio];[Data fine]) come [Durata]"
Se si usa la parola chiave ms invece di ss, si otterrà la durata in millisecondi
Uso di misure calcolate DAX al posto delle colonne
Se si è già lavorato con 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 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 tutti e tre, è possibile risparmiare spazio mantenendone solo due e calcolando il terzo usando DAX.
Quali 2 colonne conservare?
Nell'esempio precedente mantenere Quantità e Prezzo unitario. Questi due valori sono inferiori rispetto al totale. Per calcolare Il totale, aggiungere una misura calcolata come:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Le colonne calcolate sono simili a normali colonne in cui entrambe le colonne prendono spazio nel modello. Al contrario, le misure calcolate vengono calcolate al volo e non hanno spazio.
Conclusione
In questo articolo sono stati descritti diversi approcci che consentono di 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 è 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.
-
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. Ognuna delle parti avrà un numero limitato di valori univoci e il totale combinato sarà minore della colonna unificata originale.
-
In molti casi, è necessario usare anche le parti distinte come filtri dei dati nei report. Se necessario, è possibile creare gerarchie da parti come Ore, Minuti e Secondi.
-
Molte volte, le colonne contengono più informazioni di quante ne hai bisogno. Si supponga, ad esempio, che una colonna archivi i decimali, ma che sia stata applicata 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 che è possibile eseguire per ridurre le dimensioni della cartella di lavoro, è consigliabile eseguire anche Ottimizzatore dimensioni cartella di lavoro. Analizza la cartella di Excel e, se possibile, la comprime ulteriormente. Scaricare l'Ottimizzatore dimensioni cartella di lavoro.
Collegamenti correlati
Specifica e limiti del modello di dati
Ottimizzatore dimensioni cartella di lavoro
PowerPivot: potente strumento di analisi e modellazione dei dati in Excel
