Creare tabelle data in Power Pivot in Excel

Nota: Riteniamo importante fornire il contenuto della Guida più recente non appena possibile, nella lingua dell'utente. Questa pagina è stata tradotta automaticamente e potrebbe contenere errori di grammatica o imprecisioni. L'intento è quello di rendere fruibile il contenuto. Nella parte inferiore della pagina è possibile indicare se le informazioni sono risultate utili. Questo è l'articolo in lingua inglese per riferimento.

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 di dati, creazione di relazioni e 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 Intelligence temporali di DAX, vedere Time Intelligence in PowerPivot in Excel.

Nota: In Power Pivot i termini "misura" e "campo calcolato" sono sinonimi. In questo articolo stiamo usando la misura nome. Per altre informazioni, vedere misure in Power Pivot.

Sommario

Informazioni sulle tabelle data

Aggiunta di una tabella data al modello di dati

Importare una tabella data da un database relazionale

Creare una tabella data in Excel

Procedura: Creare una tabella data in Excel e copiarla nel modello di dati

Aggiunta di nuove colonne data alla tabella data

Funzioni di data e ora (DAX)

Esempi di formule per un anno di calendario

Anno

Mese

Trimestre

Nome del mese

Esempi di formule per un anno fiscale

Anno fiscale

Mese fiscale

Trimestre fiscale

Festività o date speciali

Calendario personalizzato con tredici periodi da quattro settimane

Settimana

Punto

Anno fiscale del periodo

Periodo nell'anno fiscale

Relazioni

Più relazioni

Relazioni inattive

Più tabelle data

Proprietà Date Table

Uso delle ore

Aumentare l'usabilità delle date

Appendice

Conversione di date del tipo di dati testo nel tipo di dati data

Risorse aggiuntive

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

Tabella pivot Totale vendite per trimestre fiscale

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

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

Tabella data con date contigue

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

Finestra di dialogo 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

  1. In Excel, in un foglio di lavoro vuoto, nella cella a1Digitare il nome di un'intestazione di colonna per identificare un intervallo di date. In genere, si trattadi un valore simile a data, DateTime o DateKey.

  2. Nella cella A2 digitare una data di inizio. Ad esempio 1/1/2010.

  3. 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.

    Colonna Data in Excel

  4. Selezionare tutte le righe nella colonna Data, incluso il nome dell'intestazione nella cella A1).

  5. Nel gruppo Stili fare clic su Formatta come tabella e selezionare uno stile.

  6. Nella finestra di dialogo Formatta come tabella fare clic su OK.

    Colonna Data in Power Pivot

  7. Copiare tutte le righe, inclusa l'intestazione.

  8. In Power Pivot, nella scheda Home fare clic su Incolla.

  9. In Incolla anteprima _GT_ nome tabella digitare un nome, ad esempio Data o Calendario. Lasciare selezionata l'opzione USA prima riga come intestazionidi colonna e quindi fare clic su OK.

    Anteprima Incolla

    La nuova tabella data (denominata Calendario in questo esempio) in Power Pivot avrà l'aspetto seguente:

    Tabella data in Power Pivot

    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 lo sviluppo anno su 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, potrebbe includere già i diversi tipi di colonne di data desiderate. In alcuni casi potrebbe essere necessario modificare alcune di queste colonne o creare colonne di data aggiuntive. Questo è particolarmente vero se crei una tabella data personalizzata in Excel e la copi nel modello di dati. Fortunatamente, la creazione di nuove colonne data in PowerPivot è molto semplice con le funzioni di data e ora in DAX.

Suggerimento: Se non hai ancora lavorato con DAX, è un ottimo punto di partenza per imparare a usare Guida introduttiva: informazioni sulle nozioni fondamentali di DAX in 30 minuti in Office.com.

Funzioni di data e ora (DAX)

Se si hanno sempre le funzioni di data e ora nelle formule di Excel, è probabile che si abbia 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 di data e ora in DAX non corrispondono alle funzioni di Time Intelligence. Per altre informazioni, vedere Time Intelligence in PowerPivot in Excel 2013.

In DAX sono incluse le funzioni di data e ora seguenti:

Esistono anche molte altre funzioni DAX che è possibile usare nelle formule. Ad esempio, molte delle formule descritte usano funzioni matematiche e trigonometriche come mod e Tronca, funzioni logiche come see testo come formato 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 year restituisce l'anno dal valore nella colonna Data. Poiché il valore nella colonna Date è del tipo di dati datetime, la funzione YEAR restituirà l'anno della colonna.

Colonna Anno

Mese

=MONTH([data])

In questa formula, come per la funzione YEAR, è possibile usare semplicemente la funzione Month per restituire un valore Month dalla colonna Data.

Colonna Mese

Trimestre

=INT(([Mese]+2)/3)

In questa formula usiamo 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.

Colonna Trimestre

Nome del mese

=FORMAT([data],"mmmm")

In questa formula, per ottenere il nome del mese, usiamo la funzione Format per convertire un valore numerico dalla colonna data in testo. Specifichiamo la colonna data come primo argomento e quindi il formato; Desideriamo che il nome del mese visualizzi tutti i caratteri, quindi usiamo "MMMM". Il risultato avrà l'aspetto seguente:

Colonna NomeMese

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.

Colonna GiornoSettimana
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.

Tabella pivot di esempio

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 alcuna 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, usiamo prima una funzione se per verificare se il valore di month è minore o uguale a 6. Nel secondo argomento, se il valore di month è minore o uguale a 6, quindi restituire il valore della colonna Year. In caso contrario, restituisce il valore di Year e Aggiungi 1.

Colonna AnnoFiscale

Un altro modo per specificare un valore per il mese di fine anno è creare una misura che specifichi semplicemente il mese. Ad esempio, FYE: = 6. È quindi possibile fare riferimento al nome della misura al posto del numero del mese. Ad esempio, = se ([Month] < = [FYE], [anno], [anno] + 1). In questo modo è più flessibile fare 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].

Colonna MeseFiscale

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].

Colonna TrimestreFiscale

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 può essere necessario escludere quelle date da altre colonne di 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. Le formule a cui fa riferimento possono usare la funzione LOOKUPVALUE per restituire i 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à.

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.

Colonna Settimana

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:

Colonna Settimana

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:

Colonna Periodo

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:

Colonna AnnoFiscalePeriodo

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. Possiamo usare il mod (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:

Colonna PeriodoInAnnoFiscale

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.

Tabella pivot di esempio per l'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.

Relazioni nella vista diagramma

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 nei valori e i campi FiscalYear e FiscalQuarter della tabella data del calendario vengono posizionati in righe.

Tabella pivot Totale per trimestre fiscale Elenco dei campi di una tabella pivot

Questa semplice tabella pivot funziona correttamente e permette di sommare le vendite complessive in base alla data ditransazione in DateKey. La nostra misura Total Sales USA le date in DateKey e viene sommata in base all'anno fiscale e al trimestre fiscale perché esiste una relazione tra DateKey nella tabella Sales e la colonna data nella tabella data del 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 della nave deve specificare la relazione inattiva usando la funzione USERELATIONSHIP .

Ad esempio, poiché esiste una relazione inattiva tra la colonna DataSpedizione nella tabella Sales e la colonna data nella tabella calendario, è possibile creare una misura che somma le vendite totali in base alla 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.

Ora, se creiamo una tabella pivot e impostiamo la misura totale vendite in base alla data di spedizione in valori e anno fiscale e trimestre fiscale in righe, viene visualizzato lo stesso totale complessivo, ma tutti gli altri importi per l'anno fiscale e il trimestre fiscale sono diversi perché si basano sulla data di spedizione e non la data della transazione.

Tabella pivot Totale Vendite per Data Spedizione Elenco dei campi di una tabella pivot

L'uso di relazioni inattive consente di usare una sola tabella data, ma richiede che tutte le misure, ad esempio le vendite totali in base alla data di spedizione, facciano riferimento alla relazione inattiva nella relativa 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.

  • Una colonna DataRestituzione con la data e l'ora di ricezione di 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 vuole creare relazioni inattive tra DataSpedizione e ReturnDate e il campo data nella tabella calendario, con la necessità di formule speciali per le misure, è possibile creare tabelle data aggiuntive per la data e la data di restituzione. Sarà quindi possibile creare relazioni attive tra di esse.

Relazioni con più tabelle data nella vista diagramma

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 creiamo la nostra tabella pivot e impostiamo la nostra misura di vendita totale in valori e ShipFiscalYear e ShipFiscalQuarter in righe, vediamo gli stessi risultati che abbiamo visto quando abbiamo creato una relazione inattiva e un campo speciale totale vendite in base alla data di spedizione.

Tabella pivot Totale Vendite per Data Spedizione con calendario spedizione Elenco campi tabella pivot

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 il transito delle 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 le funzioni Time-Intelligence di DAX potrebbero non restituire i 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à.

Finestra di dialogo Contrassegna come tabella data

Procedura: Impostare la proprietà Date Table

  1. Nella finestra di Power Pivot selezionare la tabella Calendario.

  2. Nella scheda Progettazione fare clic su Contrassegna come tabella data.

  3. 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 Sales. Tutte le aggregazioni per i dati di questa tabella sono necessarie solo per il livello giornaliero, usando le colonne nella tabella data del calendario come anno, mese, trimestre, ecc. Il tempo incluso nel valore non è rilevante, ma solo la data effettiva.

Colonna DateTimeKey

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:

Colonna Data in Power Pivot

Per creare una relazione tra la colonna DateTimeKey nella tabella Sales e la colonna data nella tabella calendario, è possibile creare una nuova colonna calcolata nella tabella dei fatti Sales e usare la funzione Tronca per troncare il valore di data e ora nell'DateTimeKey colonna in un valore di data corrispondente ai valori della colonna data nella 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:

Colonna DateKey

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:

Colonna DateTimeKey

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 che consentano di 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. Ulteriori informazioni sulle funzioni di Power Pivot e Time Intelligence.

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:

Colonna DataOra in una tabella dei fatti.

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.

Tipo di dati nella barra multifunzione

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:

Errore di mancata corrispondenza

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.

Colonna Data nella tabella dei fatti

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

Date in Power Pivot

Calcoli in Power Pivot

Guida introduttiva: Informazioni sulle nozioni fondamentali di DAX in 30 minuti

Riferimento alle espressioni di analisi dati

Centro risorse DAX

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.

×