Le tabelle data in Power Pivot sono essenziali per esplorare e calcolare i dati nel tempo. In questo articolo vengono fornite informazioni dettagliate sulle tabelle data e su come è possibile crearle in Power Pivot. In particolare, in questo articolo vengono illustrati gli argomenti seguenti:
-
Importanza di una tabella data per l'esplorazione e il calcolo dei dati in base a data e ora.
-
Come usare Power Pivot per aggiungere una tabella data al modello di dati.
-
Come creare nuove colonne data, quali Anno, Mese e Periodo in una tabella data.
-
Come creare relazioni tra tabelle data e tabelle dei fatti.
-
Come usare le ore.
Questo articolo è destinato ai nuovi utenti di Power Pivot. Tuttavia, è importante avere già una buona conoscenza dell'importazione dei dati, della creazione di relazioni e della creazione di colonne e misure calcolate.
Questo articolo non descrive come usare le funzioni di Time-Intelligence DAX nelle formule di misura. Per altre informazioni su come creare misure con le funzioni di Business Intelligence per le tempiere DAX, vedere Business Intelligence per le tempiere in Power Pivot in Excel.
Nota: In Power Pivot i termini "misura" e "campo calcolato" sono sinonimi. In questo articolo viene usata la misura del nome. Per altre informazioni, vedere Misure in Power Pivot.
Sommario
Informazioni sulle tabelle data
Quasi tutte le analisi dei dati prevedono l'esplorazione e il confronto dei dati in base a date e ore. È ad esempio possibile sommare gli importi delle vendite per l'ultimo trimestre fiscale e quindi confrontare i totali ottenuti con quelli degli altri trimestri oppure è possibile calcolare il saldo di chiusura di fine mese per un conto. In ognuno di questi casi, si useranno le date per raggruppare e aggregare le transazioni di vendita o i saldi per un determinato periodo di tempo.
Report Power View
Una tabella data può contenere molte rappresentazioni diverse di date e ore. In una tabella data, ad esempio, saranno spesso presenti colonne quali Anno fiscale, Mese fiscale, Trimestre fiscale o Periodo fiscale che è possibile selezionare come campi da un Elenco campi durante l'applicazione dei filtri dati nelle tabelle pivot o nei report Power View.
Elenco campi Power View
Affinché le colonne data quali Anno, Mese e Trimestre includano tutte le date comprese nel rispettivo intervallo, nella tabella data deve essere presente almeno una colonna con un set di date contiguo. Ovvero, in una colonna deve essere presente una riga per ogni giorno di ogni anno incluso nella tabella data.
Se ad esempio nei dati da esplorare sono presenti le date dal 1° febbraio 2010 al 30 novembre 2012 e si vuole creare un report in base a un anno di calendario, sarà necessario avere una tabella data con almeno un intervallo di date compreso tra il 1° gennaio 2010 e il 31 dicembre 2012. Tutti gli anni della tabella data devono contenere tutti i giorni per ogni anno. Se i dati verranno aggiornati regolarmente con dati più recenti, è possibile estendere la data di fine di uno o due anni, in modo da non dover aggiornare la tabella data con il passare del tempo.
Tabella data con un set di date contiguo
Se si genera un report in base a un anno fiscale, è possibile creare una tabella data con un set di date contiguo per ogni anno fiscale. Se ad esempio l'anno fiscale inizia il 1° marzo e sono disponibili i dati per gli anni fiscali a partire dal 2010 fino alla data corrente (ad esempio l'anno fiscale 2013), è possibile creare una tabella data con inizio il 1/3/2009 e che includa almeno ogni giorno di ogni anno fiscale fino all'ultima data nell'anno fiscale 2013.
Se si prevede di generare un report sia per l'anno di calendario che per l'anno fiscale, non è necessario creare tabelle data separate. Una singola tabella data può includere le colonne per l'anno di calendario, l'anno fiscale e perfino il calendario con tredici periodi da quattro settimane. L'importante è che la tabella data contenga un set di date contiguo per tutti gli anni inclusi.
Aggiunta di una tabella data al modello di dati
Per aggiungere una tabella data al modello di dati è possibile eseguire le operazioni seguenti:
-
Importare una tabella data da un database relazionale
-
Creare una tabella data in Excel e quindi copiarla o collegarla a una nuova tabella in Power Pivot.
-
Importare da Microsoft Azure Marketplace.
Ognuna di queste procedure verrà ora esaminata in dettaglio.
Importare una tabella data da un database relazionale
Se si importano alcuni o tutti i dati da un data warehouse o da un altro tipo di database relazionale, è probabile che sia già presente una tabella data con le relazioni tra essa e gli altri dati che si stanno importando. Le date e i formati corrisponderanno alle date dei dati nella tabella dei fatti e tali date avranno un inizio in un passato non recente e si estenderanno fino a un lontano futuro. La tabella data scelta per l'importazione potrebbe risultare di grandi dimensioni e contenere un intervallo di date ben più esteso rispetto a quello che si intende includere nel modello di dati. È possibile usare le funzionalità di filtro avanzato di Importazione guidata tabella di Power Pivot per scegliere in modo selettivo solo le date e le colonne specifiche necessarie. Questa operazione consente di ridurre notevolmente le dimensioni della cartella di lavoro e di migliorare le prestazioni.
Importazione guidata tabella
Nella maggior parte dei casi, non sarà necessario creare colonne aggiuntive quali Anno fiscale, Settimana, Nome del mese e così via, in quanto saranno già presenti nella tabella importata. In alcuni casi, tuttavia, dopo avere importato la tabella data nel modello di dati, potrebbe essere necessario creare altre colonne data, a seconda del tipo di report che si intende creare. Le funzioni DAX consentono di semplificare questa operazione. Altre informazioni sulla creazione dei campi delle tabelle data verranno fornite più avanti in questo articolo. Ogni ambiente è diverso dall'altro. Se non si è certi se nelle origini dati in uso sono presenti tabelle data o calendario correlate, rivolgersi all'amministratore del database.
Creare una tabella data in Excel
È possibile creare una tabella data in Excel e quindi copiarla in una nuova tabella nel modello di dati. Si tratta di un'operazione facile che offre molta flessibilità.
Quando si crea una tabella data in Excel, è necessario iniziare da una singola colonna con un intervallo di date contiguo. È quindi possibile creare colonne aggiuntive quali Anno, Trimestre, Mese, Anno fiscale, Periodo e così via, nel foglio di lavoro di Excel usando le formule di Excel, oppure, dopo avere copiato la tabella nel modello di dati, è possibile crearle come colonne calcolate. La procedura per la creazione di altre colonne data in Power Pivot è illustrata nella sezione Aggiunta di nuove colonne data alla tabella data più avanti in questo articolo.
Procedura: Creare una tabella data in Excel e copiarla nel modello di dati
-
In Excel, nella cella A1 di un foglio di lavoro vuoto digitare un nome di intestazione di colonna per identificare un intervallo di date. In genere, si tratta didate, datetime o datekey.
-
Nella cella A2 digitare una data di inizio. Ad esempio 1/1/2010.
-
Fare clic sul quadratino di riempimento e trascinarlo verso il basso fino a un numero di riga indicante la data di fine. Ad esempio 31/12/2016.
-
Selezionare tutte le righe nella colonna Data, incluso il nome dell'intestazione nella cella A1).
-
Nel gruppo Stili fare clic su Formatta come tabella e selezionare uno stile.
-
Nella finestra di dialogo Formatta come tabella fare clic su OK.
-
Copiare tutte le righe, inclusa l'intestazione.
-
In Power Pivot, nella scheda Home fare clic su Incolla.
-
In Anteprima Incolla > Nome tabella digitare un nome, ad esempio Data o Calendario. Lasciare selezionata l'opzione Usa la prima riga come intestazioni dicolonna e quindi fare clic su OK.
La nuova tabella data (denominata Calendario in questo esempio) in Power Pivot avrà l'aspetto seguente:
Nota: È anche possibile creare una tabella collegata usando l'opzione Aggiungi a modello di dati. Questa operazione, tuttavia, comporta un inutile aumento delle dimensioni della cartella di lavoro in quanto nella cartella di lavoro sono presenti due versioni della tabella data, una in Excel e una in Power Pivot.
Nota: Il nome date è una parola chiave in Power Pivot. Se alla tabella creata in Power Pivot si assegna il nome Date, sarà necessario racchiudere il nome della tabella tra virgolette in ogni formula DAX che fa riferimento ad essa in un argomento. Tutte le immagini e le formule di esempio riportate in questo articolo fanno riferimento a una tabella data denominata Calendario creata in Power Pivot.
Nel modello di dati è ora presente una tabella data. È quindi possibile aggiungere nuove colonne data quali Anno, Mese e così via, mediante DAX.
Aggiunta di nuove colonne data alla tabella data
Una tabella data con una singola colonna data per ogni giorno e ogni anno è importante per la definizione di tutte le date incluse in un intervallo di date. È inoltre necessaria per creare una relazione tra la tabella dei fatti e la tabella data. Questa singola colonna di date con una riga per ogni giorno non è tuttavia utile ai fini dell'analisi in base alle date in una tabella pivot o in un report Power View. La tabella data dovrà includere le colonne che consentono di aggregare i dati per un intervallo o un gruppo di date. Ad esempio, è possibile sommare gli importi delle vendite per mese o trimestre oppure creare una misura che calcola la crescita anno dopo anno. In ognuno di questi casi è necessario che nella tabella data siano presenti le colonne per anno, mese o trimestre al fine di aggregare i dati in base al periodo desiderato.
Se la tabella data è stata importata da un'origine dati relazionale, è possibile che includa già i diversi tipi di colonne data desiderate. In alcuni casi, è consigliabile modificare alcune di queste colonne o creare colonne data aggiuntive. Ciò è particolarmente vero se si crea una tabella data personalizzata in Excel e la si copia nel modello di dati. Fortunatamente, la creazione di nuove colonne data in Power Pivot è piuttosto semplice con le funzioni di data e ora in DAX.
Suggerimento: Se non si ha ancora lavorato con DAX, un ottimo punto di partenza per imparare è con la Guida introduttiva: Informazioni sulle nozioni di base di DAX in 30 minuti su Office.com.
Funzioni di data e ora (DAX)
Se si ha familiarità con le funzioni di data e ora nelle formule di Excel, probabilmente si avrà familiarità con le funzioni di data e ora. Sebbene tali funzioni siano simili alle rispettive controparti in Excel, è necessario tenere conto di alcune differenze importanti.
-
Le funzioni di data e ora (DAX) usano un tipo di dati datetime.
-
Possono accettare valori di una colonna come argomento.
-
Possono essere usate per restituire e/o modificare i valori di data.
Queste funzioni vengono spesso usate per la creazione di colonne data personalizzate in una tabella data, pertanto è importante comprendere come funzionano. Molte di queste funzioni verranno usate per creare le colonne relative all'anno, al trimestre, al mese fiscale e così via.
Nota: Le funzioni data e ora in DAX non sono le stesse delle funzioni di Business Intelligence per le tempie. Per altre informazioni, vedere Business Intelligence per le ore in Power Pivot in Excel.
In DAX sono incluse le funzioni di data e ora seguenti:
È anche possibile usare molte altre funzioni DAX nelle formule. Molte delle formule descritte in questo articolo, ad esempio, usano funzioni matematiche e trigonometriche come MOD e TRUNC, funzioni logiche come SE e funzioni di testo come FORMAT Per altre informazioni su altre funzioni DAX, vedere la sezione Risorse aggiuntive più avanti in questo articolo.
Esempi di formule per un anno di calendario
Negli esempi seguenti vengono descritte le formule usate per creare colonne aggiuntive in una tabella data denominata Calendario. Una colonna, la colonna Data, esiste già e contiene un intervallo di date contiguo dall'1/1/2010 al 31/12/2016.
Anno
=YEAR([data])
In questa formula la funzione ANNO restituisce l'anno dal valore della colonna Data. Poiché il valore nella colonna Date è del tipo di dati datetime, la funzione YEAR restituirà l'anno della colonna.
Mese
=MONTH([data])
In questa formula, analogamente alla funzione ANNO, è possibile usare semplicemente la funzione MESE per restituire un valore di mese dalla colonna Data.
Trimestre
=INT(([Mese]+2)/3)
In questa formula viene usata la funzione INT per restituire un valore di data come numero intero. Nell'argomento della funzione INT è necessario specificare il valore della colonna Mese, aggiungere 2 e quindi dividere per 3 per ottenere il trimestre, dal primo al quarto.
Nome del mese
=FORMAT([data],"mmmm")
In questa formula, per ottenere il nome del mese, si usa la funzione FORMAT per convertire un valore numerico dalla colonna Data in testo. La colonna Data viene specificata come primo argomento e quindi come formato. vogliamo che il nome del mese mostri tutti i caratteri, quindi usiamo "mmmm". Il risultato avrà l'aspetto seguente:
Se si vuole che venga restituito il nome del mese abbreviato a tre lettere, sarà necessario usare la stringa "mmm" nell'argomento della funzione FORMAT.
Giorno della settimana
=FORMAT([data],"ddd")
In questa formula verrà usata la funzione FORMAT per ottenere il nome del giorno. Poiché si vuole che venga restituito il nome del giorno abbreviato, verrà specificato "ddd" nell'argomento della funzione FORMAT.
Tabella pivot di esempio
Se per le date sono presenti campi come Anno, Trimestre, Mese e così via, è possibili usarli in una tabella pivot o in un report. Ad esempio, nell'immagine seguente è presente il campo ImportoVendite della tabella dei fatti Vendite in VALORI e i campi Anno e Trimestre delle dimensioni in RIGHE. ImportoVendite è aggregato per il contesto dell'anno e del trimestre.
Esempi di formule per un anno fiscale
Anno fiscale
=IF([Mese]<= 6,[Anno],[Anno]+1)
In questo esempio l'anno fiscale inizia il 1° luglio.
Non esiste una funzione che può estrarre un anno fiscale da un valore di data perché le date di inizio e di fine di un anno fiscale sono spesso diverse da quelle di un anno di calendario. Per ottenere l'anno fiscale, viene prima di tutto usata una funzione SE per verificare se il valore per Mese è minore o uguale a 6. Nel secondo argomento, se il valore di Mese è minore o uguale a 6, restituisce il valore della colonna Anno. In caso contrario, restituisci il valore di Year e aggiungi 1.
Un altro modo per specificare un valore di fine mese per l'anno fiscale consiste nel creare una misura che specifica semplicemente il mese. Ad esempio, FYE:=6. È quindi possibile fare riferimento al nome della misura al posto del numero del mese. Ad esempio, =SE([Mese]<=[FYE],[Anno],[Anno]+1). In questo modo si offre maggiore flessibilità quando si fa riferimento al mese di fine anno fiscale in diverse formule.
Mese fiscale
=IF([Mese]<= 6, 6+[Mese], [Mese]- 6)
In questa formula, si specifica se il valore per [Mese] è minore o uguale a 6, quindi si prende 6 e si aggiunge il valore di Mese oppure si sottrae 6 dal valore di [Mese].
Trimestre fiscale
=INT(([MeseFiscale]+2)/3)
La formula usata per il trimestre fiscale è simile a quella usata per il trimestre dell'anno di calendario. L'unica differenza sta nel fatto che occorre specificare [MeseFiscale] anziché [Mese].
Festività o date speciali
Potrebbe essere necessario includere una colonna data indicante le festività o altre date speciali. Ad esempio è possibile sommare i totali delle vendite per il giorno di Capodanno aggiungendo un campo per le festività a una tabella pivot come filtro dati. In altri casi è consigliabile escludere tali date da altre colonne data o in una misura.
La procedura per includere le festività o le date speciali è facile. In Excel è possibile creare una tabella contenente le date da includere. È quindi possibile copiare o usare l'opzione Aggiungi a modello di dati per aggiungerla al modello di dati come tabella collegata. Nella maggior parte dei casi, non sarà necessario creare una relazione tra la tabella e la tabella Calendario. Qualsiasi formula che fa riferimento al valore può usare la funzione CERCA.VALORE per restituire valori.
Di seguito è riportato un esempio di tabella di Excel contenente le festività da aggiungere alla tabella data:
|
Data |
Festa |
|---|---|
|
1/1/2010 |
Primo dell'anno |
|
15/8/2010 |
Ferragosto |
|
25/12/2010 |
Natale |
|
1/1/2011 |
Primo dell'anno |
|
15/8/2011 |
Ferragosto |
|
25/12/2011 |
Natale |
|
1/1/2012 |
Primo dell'anno |
|
15/8/2012 |
Ferragosto |
|
25/12/2012 |
Natale |
|
1/1/2013 |
Primo dell'anno |
|
15/8/2013 |
Ferragosto |
|
25/12/2013 |
Natale |
|
15/8/2014 |
Ferragosto |
|
25/12/2014 |
Natale |
|
1/1/2014 |
Primo dell'anno |
|
15/8/2014 |
Ferragosto |
|
25/12/2014 |
Natale |
|
1/1/2015 |
Primo dell'anno |
|
15/8/2015 |
Ferragosto |
|
25/12/2015 |
Natale |
|
1/1/2016 |
Primo dell'anno |
|
15/8/2016 |
Ferragosto |
|
25/12/2016 |
Natale |
Nella tabella data verrà creata una colonna denominata Festa e si userà una formula come questa:
=LOOKUPVALUE(Festività[Festa],Festività[data],Calendario[data])
Osservare la formula con attenzione.
La funzione LOOKUPVALUE consente di ottenere i valori dalla colonna Festa nella tabella Festività. Nel primo argomento viene specificata la colonna in cui verrà inserito il valore del risultato. Verrà quindi specificata la colonna Festa nella tabella Festività in quanto si tratta del valore che dovrà essere restituito.
=LOOKUPVALUE(Festività[Festa],Festività[data],Calendario[data])
Verrà quindi specificato il secondo argomento, ovvero la colonna di ricerca contenente le date da cercare. A questo scopo, verrà specificata la colonna Data nella tabella Festività, come illustrato di seguito:
=LOOKUPVALUE(Festività[Festa],Festività[data],Calendario[data])
Infine, verrà specificata la colonna della tabella Calendario contenente le date che si vogliono cercare nella tabella Festa. Si tratta della colonna Data della tabella Calendario.
=LOOKUPVALUE(Festività[Festa],Festività[data],Calendario[data])
La colonna Festa restituirà il nome del giorno festivo per ogni riga contenente un valore di data corrispondente a una data nella tabella Festività.
Calendario personalizzato con tredici periodi da quattro settimane
Alcune organizzazioni, ad esempio negozi al dettaglio o ristoranti, creano report basati su intervalli di tempo diversi, ad esempio tredici periodi da quattro settimane. In un calendario con tredici periodi da quattro settimane, ogni periodo conta 28 giorni e di conseguenza contiene quattro lunedì, quattro martedì, quattro mercoledì e così via. Ogni periodo contiene lo stesso numero di giorni e, in genere, le festività cadranno sempre nello stesso periodo di ogni anno. Un periodo può avere inizio qualsiasi giorno della settimana. Come per le date in un calendario o in un anno fiscale, è possibile usare DAX per creare colonne aggiuntive con date personalizzate.
Negli esempi riportati di seguito, il primo periodo completo inizia la prima domenica dell'anno fiscale. In questo caso l'anno fiscale inizia il 1° luglio.
Settimana
Questo valore indica il numero della settimana a partire dalla prima settimana completa dell'anno fiscale. In questo esempio la prima settimana completa inizia la domenica, pertanto la prima settimana completa nel primo anno fiscale della tabella Calendario inizia effettivamente il 4/7/2010 e continua fino all'ultima settimana completa nella tabella Calendario. Sebbene questo valore in sé non risulti particolarmente utile nelle analisi, è necessario calcolarlo per usarlo in altre formule relative al periodo di 28 giorni.
=INT([data]-40356)/7)
Osservare la formula con attenzione.
Innanzitutto, viene creata una formula che restituisce i valori dalla colonna Data come numeri interi, in questo modo:
=INT([data])
Si passa quindi al calcolo della prima domenica del primo anno fiscale. Questa data è il 4/7/2010.
Ora, si sottrae 40356 (il numero intero per 27/06/2010, ovvero l'ultima domenica dell'anno fiscale precedente) da quel valore per ottenere il numero di giorni dall'inizio dei giorni della tabella Calendario, in questo modo:
=INT([data]-40356)
Si divide quindi il risultato per 7 (giorni in una settimana), in questo modo:
=INT(([data]-40356)/7)
Il risultato avrà l'aspetto seguente:
Periodo
Il periodo di questo calendario personalizzato contiene 28 giorni e inizierà sempre di domenica. Questa colonna restituirà il numero del periodo che inizia la prima domenica del primo anno fiscale.
=INT(([Settimana]+3)/4)
Osservare la formula con attenzione.
Innanzitutto, viene creata una formula che restituisce un valore dalla colonna Settimana come numero intero, in questo modo:
=INT([Settimana])
A questo valore si aggiunge 3, in questo modo:
=INT([Settimana]+3)
Si divide quindi il risultato per 4, in questo modo:
=INT(([Settimana]+3)/4)
Il risultato avrà l'aspetto seguente:
Anno fiscale del periodo
Questo valore restituisce l'anno fiscale per un periodo.
=INT(([Periodo]+12)/13)+2008
Osservare la formula con attenzione.
Innanzitutto, viene creata una formula che restituisce un valore dalla colonna Periodo e si aggiunge 12:
= ([Periodo]+12)
Si divide il risultato per 13 in quanto nell'anno fiscale sono presenti tredici periodi di 28 giorni:
=(([Periodo]+12)/13)
Si aggiunge 2010 perché si tratta del primo anno nella tabella:
=(([Periodo]+12)/13)+2010
Infine, si usa la funzione INT per rimuovere le eventuali frazioni del risultato e ottenere un numero intero, e quindi si divide per 13, in questo modo:
=INT(([Periodo]+12)/13)+2010
Il risultato avrà l'aspetto seguente:
Periodo nell'anno fiscale
Questo valore restituisce il numero del periodo, da 1 a 13, iniziando dal primo periodo completo (che comincia di domenica) in ogni anno fiscale.
=IF(MOD([Periodo],13), MOD([Periodo],13),13)
Questa formula è un po' più complessa, pertanto verrà prima descritta in modo più comprensibile. La formula indica che verrà diviso il valore di [Periodo] per 13 per ottenere il numero di periodo (da 1 a 13) nell'anno. Se quel numero è 0, verrà restituito 13.
Innanzitutto, viene creata una formula che restituisce il resto del valore di Period diviso 13. È possibile usare la funzione RESTO (funzioni matematiche e trigonometriche) in questo modo:
=MOD([Periodo],13)
Questa funzione restituisce il risultato desiderato, tranne nel caso in cui il valore di Periodo è 0 perché le date non rientrano nel primo anno fiscale, come nei primi cinque giorni dell'esempio sulla tabella data Calendario. È possibile risolvere il problema mediante una funzione IF. Se il risultato è 0, la formula dovrà restituire 13, in questo modo:
=IF(MOD([Periodo],13),MOD([Periodo],13),13)
Il risultato avrà l'aspetto seguente:
Tabella pivot di esempio
L'immagine seguente mostra una tabella pivot con il campo ImportoVendite della tabella dei fatti Vendite in VALORI e i campi AnnoFiscalePeriodo e PeriodoInAnnoFiscale della tabella data delle dimensioni in RIGHE. ImportoVendite è aggregato per il contesto per anno fiscale e per periodi di 28 giorni nell'anno fiscale.
Relazioni
Dopo avere creato una tabella data nel modello di dati, per iniziare a esplorare i dati nelle tabelle pivot e nei report e per aggregare i dati in base alle colonne della tabella data delle dimensioni, è necessario creare una relazione tra la tabella dei fatti contenente i dati delle transazioni e la tabella data.
Poiché è necessario creare una relazione in base alle date, sarà necessario accertarsi di creare la relazione tra le colonne con valori del tipo di dati datetime (Data).
Per ogni valore di data nella tabella dei fatti deve essere presente un valore corrispondente nella colonna di ricerca correlata nella tabella data. Ad esempio, per una riga (record transazione) nella tabella dei fatti Vendite con un valore di 15/8/2012 12:00 nella colonna DateKey deve essere presente un valore corrispondente nella colonna Data correlata nella tabella data (denominata Calendario). Questo è uno dei principali motivi per i quali è necessario che la colonna data nella tabella data contenga un intervallo di date contiguo che includa tutte le date possibili nella tabella dei fatti.
Nota: Le colonne data in ogni tabella devono essere dello stesso tipo di dati (Data), ma il formato è indifferente.
Nota: Se Power Pivot non consente di creare relazioni tra le due tabelle, è possibile che nei campi data le date e le ore non siano archiviate allo stesso livello di precisione. A seconda della formattazione della colonna, i valori potrebbero presentare lo stesso aspetto pur essendo archiviati in modo diverso. Altre informazioni sull'uso delle ore.
Nota: Evitare l'uso di chiavi surrogate Integer nelle relazioni. Quando si importano dati da un'origine dati relazionale, spesso le colonne di data e ora sono rappresentate da una chiave surrogata, ovvero una colonna Integer usata per rappresentare una data univoca. In Power Pivot è necessario evitare di creare relazioni tramite chiavi data/ora Integer e, invece usare colonne che contengono valori univoci con un tipo di dati date. Sebbene l'uso di chiavi surrogate sia considerato una procedura consigliata nei data warehouse tradizionali, le chiavi Integer non sono necessarie in Power Pivot e possono rendere difficile raggruppare valori nelle tabelle pivot per periodi di data diversi.
Se viene restituito un errore di mancata corrispondenza del tipo quando si tenta di creare una relazione, è probabile che la colonna nella tabella dei fatti non sia del tipo di dati Data. Questo può verificarsi quando Power Pivot non riesce a convertire automaticamente un tipo di dati non data (in genere un tipo di dati testo) in un tipo di dati data. È comunque possibile usare la colonna nella tabella dei fatti ma sarà necessario convertire i dati con una formula DAX in una nuova colonna calcolata. Vedere Conversione di date del tipo di dati testo nel tipo di dati data nell'appendice.
Più relazioni
In alcuni casi, potrebbe essere necessario creare più relazioni o più tabelle data. Se ad esempio sono presenti più campi data nella tabella dei fatti Vendite, come DateKey, DataSpedizione e DataRestituzione, è possibile creare una relazione con il campo Data nella tabella data Calendario ma solo uno di questi può rappresentare una relazione attiva. In questo caso, poiché DateKey rappresenta la data della transazione e quindi la data più importante, si tratta di quello più indicato come relazione attiva. Le relazioni degli altri campi sono inattive.
La tabella pivot seguente calcola il totale delle vendite per anno fiscale e trimestre fiscale. Una misura denominata Total Sales, con la formula Total Sales:=SUM([SalesAmount]), viene inserita in VALUES e i campi AnnoFi fiscale e TrimestreFifisale della tabella data Calendario vengono inseriti in RIGHE.
Questa semplice tabella pivot funziona correttamente e permette di sommare le vendite complessive in base alla data ditransazione in DateKey. La misura Total Sales usa le date in DateKey e viene sommata per anno fiscale e trimestre fiscale perché esiste una relazione tra DateKey nella tabella Vendite e la colonna Data nella tabella data Calendario.
Relazioni inattive
Che cosa si dovrebbe fare per sommare il totale vendite in base alla data di spedizione anziché alla data della transazione? È necessario creare una relazione tra la colonna DataSpedizione nella tabella Vendite e la colonna Data nella tabella Calendario. Se non si crea la relazione, le aggregazioni resteranno basate sulla data della transazione. È tuttavia possibile creare più relazioni, anche se solo una di esse può essere attiva, e poiché la data della transazione è quella più importante, avrà la relazione attiva con la tabella Calendario.
In questo caso, DataSpedizione ha una relazione inattiva, quindi qualsiasi formula di misura creata per aggregare i dati in base alle date di spedizione deve specificare la relazione inattiva usando la funzione USERELATIONSHIP .
Ad esempio, poiché esiste una relazione inattiva tra la colonna DataSpedizione nella tabella Vendite e la colonna Data nella tabella Calendario, è possibile creare una misura che somma il totale delle vendite per data di spedizione. Per specificare la relazione da usare, verrà creata una formula come questa:
Totale Vendite per Data Spedizione:=CALCULATE(SUM(Vendite[ImportoVendite]), USERELATIONSHIP(Vendite[DataSpedizione], Calendario[Data]))
Questa formula consente di: Calcolare la somma di ImportoVendite applicando un filtro mediante la relazione tra la colonna DataSpedizione nella tabella Vendite e la colonna Data nella tabella Calendario.
A questo punto, se si crea una tabella pivot e si inserisce la misura Totale vendite per data spedizione in VALORI e Anno fiscale e Trimestre fiscale in RIGHE, il totale complessivo sarà lo stesso, ma tutti gli altri importi di somma per l'anno fiscale e il trimestre fiscale saranno diversi perché si basano sulla data di spedizione e non sulla data della transazione.
L'uso delle relazioni inattive consente di usare una sola tabella data, ma richiede che qualsiasi misura, ad esempio Totale Vendite per Data spedizione, faccia riferimento alla relazione inattiva nella formula. Un'alternativa prevede l'uso di più tabelle data.
Più tabelle data
Un altro modo per usare più colonne data nella tabella dei fatti consiste nel creare più tabelle data e quindi creare relazioni attive distinte tra di esse. Si osservi di nuovo l'esempio relativo alla tabella Vendite. Sono presenti tre colonne contenenti date in base alle quali è possibile aggregare i dati:
-
Una colonna DateKey con la data di vendita per ogni transazione.
-
Una colonna DataSpedizione con la data e l'ora in cui gli articoli venduti sono stati spediti al cliente.
-
DataRestituzione: con la data e l'ora in cui è stato ricevuto uno o più articoli restituiti.
Ricordare sempre che il campo DateKey con la data della transazione è quello più importante. La maggior parte delle aggregazioni viene eseguita in base a tali date, pertanto sarà necessario creare una relazione tra questo campo e la colonna Data nella tabella Calendario. Se non si vogliono creare relazioni inattive tra DataSpedizione e DataRestituibile e il campo Data nella tabella Calendario, richiedendo così formule di misure speciali, è possibile creare tabelle data aggiuntive per la data di spedizione e la data di restituzione. Sarà quindi possibile creare relazioni attive tra di esse.
In questo esempio è stata creata un'altra tabella data denominata CalendarioSpedizione. Questa operazione prevede inoltre la creazione di altre colonne data e poiché tali colonne data si trovano in una tabella data diversa, saranno denominate in modo da differenziarle dalle stesse colonne presenti nella tabella Calendario. Sono state pertanto create colonne denominate AnnoSpedizione, MeseSpedizione, TrimestreSpedizione e così via.
Se si crea la tabella pivot e si inserisce la misura Total Sales in VALUES e ShipFiscalYear e ShipFiscalQuarter in ROWS, vengono visualizzati gli stessi risultati visualizzati quando è stata creata una relazione inattiva e uno speciale campo calcolato Total Sales per Data spedizione.
Ognuno di questi approcci richiede un'attenta valutazione. Quando si usano più relazioni con una singola tabella data, potrebbe essere necessario creare misure speciali per transitare le relazioni inattive usando la funzione USERELATIONSHIP. D'altro canto, la creazione di più tabelle dati può generare confusione nell'elenco campi e poiché sono presenti più tabelle nel modello di dati, sarà richiesta una maggiore quantità di memoria. Provare e scegliere la procedura più adatta alle proprie esigenze.
Proprietà Date Table
La proprietà Date Table imposta i dati necessari affinché le funzioni di Business Intelligence per le gerarchie temporali, ad esempio TOTALYTD, PREVIOUSMONTH e DATESBETWEEN, funzionino correttamente. Quando viene eseguito un calcolo mediante una di queste funzioni, il motore di formule di Power Pivot sa già dove recuperare le date necessarie.
Avviso: Se questa proprietà non è impostata, le misure che usano funzioni da Time-Intelligence DAX potrebbero non restituire risultati corretti.
Quando si imposta la proprietà Date Table, è necessario specificare una tabella data e una colonna data del tipo di dati Data (datetime) per la proprietà.
Procedura: Impostare la proprietà Date Table
-
Nella finestra di Power Pivot selezionare la tabella Calendario.
-
Nella scheda Progettazione fare clic su Contrassegna come tabella data.
-
Nella finestra di dialogo Contrassegna come tabella data selezionare una colonna con valori univoci e con il tipo di dati Data.
Uso delle ore
Tutti i valori data con un tipo di dati Data in Excel o in SQL Server sono in realtà un numero. In questo numero sono incluse cifre che fanno riferimento all'ora. Nella maggior parte dei casi, l'ora per ogni riga corrisponde alla mezzanotte. Ad esempio, se il campo DateTimeKey in una tabella dei fatti Vendite contiene valori come 10/19/2010 12:00:00 AM, significa che i valori sono al livello di precisione del giorno. Se i valori del campo DateTimeKey includono un'ora, ad esempio 19/10/2010 8:44:00, significa che i valori sono al livello di precisione del minuto. I valori possono anche essere al livello di precisione dell'ora o persino del secondo. Il livello di precisione nel valore dell'ora determina un impatto significativo sul modo in cui si crea la tabella data e sulle relazioni tra questa e la tabella dei fatti.
Sarà necessario specificare se i dati verranno aggregati al livello di precisione del giorno o dell'ora. In altre parole, nella tabella data è possibile usare colonne quali Mattina, Pomeriggio o Ora come campi data dell'ora nelle aree Riga, Colonna o Filtro della tabella pivot.
Nota: I giorni rappresentano l'unità di tempo più piccola che le funzioni di Business Intelligence per le gerarchie temporali di DAX possono usare. Pertanto, se non è necessario utilizzare valori di ora, si deve ridurre la granularità dei dati per usare i giorni come unità minima.
Se si prevede di aggregare i dati al livello dell'ora, nella tabella data sarà necessario includere una colonna data con l'ora. Di fatto, sarà necessario aggiungere una colonna data con una riga per ogni ora o persino per ogni minuto, di ogni giorno e per ogni anno dell'intervallo di date. Questo perché per creare una relazione tra la colonna DateTimeKey nella tabella dei fatti e la colonna data nella tabella data è necessario che siano presenti valori corrispondenti. Se sono inclusi molti anni, la tabella data potrebbe diventare eccessivamente grande.
Nella maggior parte dei casi, tuttavia, i dati vengono aggregati al livello di precisione del giorno. Ovvero, si useranno colonne quali Anno, Mese, Settimana o Giorno della settimana nelle aree Riga, Colonna o Filtro della tabella pivot. In questo caso, la colonna data nella tabella data deve contenere solo una riga per ogni giorno dell'anno, come descritto in precedenza.
Se la colonna data include un livello di precisione all'ora, ma si intende aggregare i dati solo al livello del giorno, per creare la relazione tra la tabella dei fatti e la tabella data potrebbe essere necessario modificare la tabella dei fatti creando una nuova colonna in cui i valori della colonna data siano troncati al valore del giorno. In altre parole, occorre convertire 19/10/2010 8:44:00 in 19/10/2010 00:00:00. Sarà quindi possibile creare la relazione tra questa nuova colonna e la colonna data nella tabella data, in quanto i valori corrispondono.
Esaminiamo un esempio. Questa immagine mostra una colonna DateTimeKey nella tabella dei fatti Vendite. Tutte le aggregazioni per i dati in questa tabella devono essere solo a livello di giorno, usando colonne nella tabella data Calendario come Anno, Mese, Trimestre e così via. L'ora inclusa nel valore non è rilevante, ma solo la data effettiva.
Poiché non è necessario analizzare i dati a livello dell'ora, non è necessario che la colonna Data nella tabella data Calendario includa una riga per ogni ora e ogni minuto di ogni giorno di ogni anno. Pertanto, la colonna Data della tabella data avrà l'aspetto seguente:
Per creare una relazione tra la colonna DateTimeKey nella tabella Vendite e la colonna Data nella tabella Calendario, è possibile creare una nuova colonna calcolata nella tabella dei fatti Vendite e usare la funzione TRUNC per troncare il valore di data e ora nella colonna DateTimeKey in un valore di data corrispondente ai valori della colonna Data della tabella Calendario. Ecco la formula:
=TRUNC([DateTimeKey],0)
Verrà creata una nuova colonna (denominata DateKey) con la data della colonna DateTimeKey e l'ora 00:00:00 per ogni riga:
Ora è possibile creare una relazione tra questa nuova colonna (DateKey) e la colonna Data nella tabella Calendario.
Allo stesso modo, è possibile creare una colonna calcolata nella tabella Vendite per ridurre la precisione dell'ora della colonna DateTimeKey al livello di precisione dell'ora. In questo caso, la funzione TRUNC non è adatta, ma è comunque possibile usare altre funzioni di data e ora DAX per estrarre e riconcatenare un nuovo valore al livello di precisione dell'ora. A tale scopo, è possibile usare una formula come questa:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
La nuova colonna avrà l'aspetto seguente:
Poiché la colonna Data nella tabella data contiene valori al livello di precisione dell'ora, è possibile creare una relazione tra di esse.
Aumentare l'usabilità delle date
Molte delle colonne data create nella tabella data sono necessarie per altri campi, ma non sono particolarmente utili ai fini dell'analisi. Ad esempio, il campo DateKey nella tabella Vendite a cui si è fatto riferimento in questo articolo è importante in quanto per ogni transazione, la transazione è registrata come avvenuta in una determinata data e ora. Ma ai fini dell'analisi e dei report, questa informazione non risulta particolarmente utile in quanto non è possibile usare questo campo come campo riga, colonna o filtro in una tabella pivot o in un report.
Allo stesso modo, in questo esempio la colonna Data nella tabella Calendario è molto utile, persino strategica, ma non è possibile usarla come dimensione in una tabella pivot.
Affinché le tabelle e le colonne siano più utili possibile e per semplificare l'esplorazione degli elenchi campi della tabella pivot o dei report Power View, è necessario nascondere le colonne non necessarie dagli strumenti client. In alcuni casi, potrebbe essere utile nascondere anche determinate tabelle. La tabella Festività mostrata in precedenza contiene le date delle feste che sono importanti per determinate colonne nella tabella Calendario ma non è possibile usare le colonne Data e Festa stesse della tabella Festività come campi in una tabella pivot. Anche in questo caso, per semplificare l'esplorazione dell'elenco campi, è possibile nascondere l'intera tabella Festività.
Le convenzioni di denominazione sono inoltre un altro importante aspetto di cui occorre tenere conto quando si usano le date. È possibile assegnare alle tabelle e alle colonne di Power Pivot i nomi desiderati. Se tuttavia la cartella di lavoro verrà condivisa con altri utenti, è necessario tenere conto del fatto che una convenzione di denominazione ottimale semplifica l'identificazione di tabelle e date, non solo nell'elenco campi ma anche nelle formule di Power Pivot e DAX.
Dopo aver creato una tabella data nel modello di dati, è possibile iniziare a creare misure per sfruttare al meglio i dati. Alcuni campi saranno più semplici, ad esempio la somma dei totali delle vendite per l'anno corrente, mentre altri potrebbero essere più complessi e richiedere l'applicazione di un filtro per raggruppare un determinato intervallo di date univoche. Per altre informazioni, vedere Misure in Power Pivot e Funzioni di business intelligence per le tempie.
Appendice
Conversione di date del tipo di dati testo nel tipo di dati data
In alcuni casi, in una tabella dei fatti con dati relativi alle transazioni potrebbero essere presenti date del tipo di dati testo. Ad esempio, una data visualizzata come 2012-12-04T11:47:09 in realtà non è affatto una data o comunque non è un tipo di data che Power Pivot può gestire. È semplicemente un testo che visualizza una data. Per creare una relazione tra una colonna data nella tabella dei fatti e una colonna data in una tabella data, entrambe le colonne devono essere del tipo di dati Data.
In genere, quando si tenta di modificare il tipo di dati per una colonna di date del tipo di dati testo e in una colonna del tipo di dati Data, Power Pivot riesce a interpretare le date e convertirle automaticamente nel tipo di dati data vero. Se Power Pivot non riesce a eseguire una conversione del tipo di dati, verrà restituito un errore di mancata corrispondenza del tipo.
Tuttavia, sarà ancora possibile convertire le date in un tipo di dati Data vero. È possibile creare una nuova colonna calcolata e usare una formula DAX per analizzare l'anno, il mese, il giorno, l'ora e così via, dalle stringhe di testo e quindi concatenarle di nuovo in un modo che Power Pivot riesca a interpretare come data vera.
Nell'esempio seguente è stata importata una tabella dei fatti denominata Vendite in Power Pivot. Contiene una colonna denominata DataOra. Ecco come sono visualizzati i valori:
Se si osserva il tipo di dati nel gruppo Formattazione della scheda Home di Power Pivot, è possibile vedere che si tratta del tipo di dati Testo.
Non sarà quindi possibile creare una relazione tra la colonna DataOra e la colonna Data nella tabella data in quanto i tipi di dati non corrispondono. Se si tenta di impostare il tipo di dati su Data, viene restituito un errore di mancata corrispondenza del tipo:
In questo caso, Power Pivot non è riuscito a convertire il tipo di dati da testo a data. È comunque possibile usare questa colonna, ma per convertirla nel tipo di dati data vero sarà necessario creare una nuova colonna che analizzi il testo e che lo trasformi in un valore che Power Pivot è in grado di convertire nel tipo di dati Data.
Come indicato in precedenza nella sezione Uso dell'ora di questo articolo, a meno che l'analisi non richieda un livello di precisione all'ora del giorno, è consigliabile convertire le date della tabella dei fatti al livello di precisione del giorno. Tenendo presente questo suggerimento, i dati della nuova colonna verranno convertiti al livello di precisione del giorno, escludendo quindi l'ora. È possibile convertire i valori della colonna DataOra in un tipo di dati Data e rimuovere l'ora usando la formula seguente:
=DATE(LEFT([DataOra],4), MID([DataOra],6,2), MID([DataOra],9,2))
Verrà creata una nuova colonna, in questo caso, denominata Data. Power Pivot rileva i valori che devono essere date e imposta automaticamente il tipo di dati su Data.
Per mantenere il livello di precisione dell'ora, è sufficiente estendere la formula per includere le ore, i minuti e i secondi.
=DATE(LEFT([DataOra],4), MID([DataOra],6,2), MID([DataOra],9,2)) +
TIME(MID([DataOra],12,2), MID([DataOra],15,2), MID([DataOra],18,2))
Ora che è stata creata una colonna Data del tipo di dati Data, è possibile creare una relazione tra essa e la colonna data in una data.
Altre risorse
Guida introduttiva: Informazioni sulle nozioni fondamentali di DAX in 30 minuti
