Linee guida per le relazioni molti-a-molti
Questo articolo è destinato a un modello di dati che usa Power BI Desktop. Descrive tre diversi scenari di modellazione molti-a-molti. Fornisce inoltre indicazioni su come progettarli correttamente nei modelli.
Nota
Un'introduzione alle relazioni tra modelli non è descritta in questo articolo. Se non si ha familiarità con le relazioni, le relative proprietà o come configurarle, è consigliabile leggere prima l'articolo Relazioni tra modelli in Power BI Desktop .
È anche importante avere una conoscenza della progettazione dello schema star. Per altre informazioni, vedere Informazioni sullo schema star e sull'importanza di Power BI.
Esistono, infatti, tre scenari molti-a-molti. Possono verificarsi quando è necessario:
- Correlare due tabelle di tipo dimensione
- Correlare due tabelle di tipo fatto
- Correlare tabelle di tipo fact-type più elevato, quando la tabella di tipo fatto archivia le righe con un livello di granularità superiore rispetto alle righe della tabella di tipo dimensione
Nota
Power BI supporta ora in modo nativo relazioni molti-a-molti. Per altre informazioni, vedere Applicare relazioni molti-molti in Power BI Desktop.
Correlare dimensioni molti-a-molti
Si consideri il primo tipo di scenario molti-a-molti con un esempio. Lo scenario classico riguarda due entità: i clienti bancari e i conti bancari. Si consideri che i clienti possono avere più account e che gli account possono avere più clienti. Quando un account ha più clienti, vengono comunemente chiamati titolari di conti comuni.
La modellazione di queste entità è semplice. Una tabella di tipo dimensione archivia gli account e un'altra tabella di tipo dimensione archivia i clienti. Come è caratteristica delle tabelle di tipo dimensione, è presente una colonna ID in ogni tabella. Per modellare la relazione tra le due tabelle, è necessaria una terza tabella. Questa tabella viene comunemente definita tabella di bridging. In questo esempio, lo scopo è archiviare una riga per ogni associazione di account cliente. È interessante notare che, quando questa tabella contiene solo colonne ID, viene chiamata tabella dei fatti senza fatti.
Ecco un diagramma di modello semplicistico delle tre tabelle.
La prima tabella è denominata Account e contiene due colonne: AccountID e Account. La seconda tabella è denominata AccountCustomer e contiene due colonne: AccountID e CustomerID. La terza tabella è denominata Customer e contiene due colonne: CustomerID e Customer. Le relazioni non esistono tra nessuna delle tabelle.
Vengono aggiunte due relazioni uno-a-molti per correlare le tabelle. Ecco un diagramma del modello aggiornato delle tabelle correlate. È stata aggiunta una tabella di tipo fatto denominata Transaction . Registra le transazioni di conto. La tabella di bridging e tutte le colonne ID sono state nascoste.
Per descrivere il funzionamento della propagazione del filtro delle relazioni, il diagramma del modello è stato modificato per visualizzare le righe della tabella.
Nota
Non è possibile visualizzare righe di tabella nel diagramma del modello di Power BI Desktop. Questa operazione viene eseguita in questo articolo per supportare la discussione con esempi chiari.
I dettagli della riga per le quattro tabelle sono descritti nell'elenco puntato seguente:
- La tabella Account ha due righe:
- AccountID 1 è per Account-01
- AccountID 2 è per Account-02
- La tabella Customer ha due righe:
- CustomerID 91 è per Customer-91
- CustomerID 92 è per Customer-92
- La tabella AccountCustomer ha tre righe:
- AccountID 1 è associato a CustomerID 91
- AccountID 1 è associato a CustomerID 92
- AccountID 2 è associato a CustomerID 92
- La tabella Transaction include tre righe:
- Data 1 gennaio 2019, AccountID 1, Importo 100
- Data 2 febbraio 2019, AccountID 2, Importo 200
- Data 3 marzo 2019, AccountID 1, Importo -25
Vediamo cosa accade quando viene eseguita una query sul modello.
Di seguito sono riportati due oggetti visivi che riepilogano la colonna Amount della tabella Transaction . Il primo oggetto visivo raggruppa per conto e quindi la somma delle colonne Amount rappresenta il saldo del conto. Il secondo oggetto visivo raggruppa per cliente e quindi la somma delle colonne Amount rappresenta il saldo del cliente.
Il primo oggetto visivo è denominato Account Balance e include due colonne: Account e Amount. Viene visualizzato il risultato seguente:
- L'importo del saldo conto-01 è 75
- L'importo del saldo dell'account-02 è 200
- Il totale è 275
Il secondo oggetto visivo è denominato Customer Balance e include due colonne: Customer e Amount. Viene visualizzato il risultato seguente:
- L'importo del saldo cliente-91 è 275
- L'importo del saldo cliente-92 è 275
- Il totale è 275
Una rapida occhiata alle righe della tabella e all'oggetto visivo Saldo conto rivela che il risultato è corretto, per ogni conto e l'importo totale. Poiché ogni raggruppamento di account comporta una propagazione del filtro nella tabella Transazioni per tale account.
Tuttavia, qualcosa non viene visualizzato correttamente con l'oggetto visivo Customer Balance . Ogni cliente nell'oggetto visivo Customer Balance ha lo stesso saldo del saldo totale. Questo risultato potrebbe essere corretto solo se ogni cliente era un titolare congiunto di ogni conto. Questo non è il caso in questo esempio. Il problema è correlato alla propagazione dei filtri. Non viene propagata fino alla tabella Transaction .
Seguire le istruzioni del filtro delle relazioni dalla tabella Customer alla tabella Transaction . Dovrebbe essere evidente che la relazione tra la tabella Account e AccountCustomer si propaga nella direzione errata. La direzione del filtro per questa relazione deve essere impostata su Entrambi.
Come previsto, non è stata apportata alcuna modifica all'oggetto visivo Saldo conto.
Tuttavia , gli oggetti visivi di Customer Balance visualizzano il risultato seguente:
- L'importo del saldo cliente-91 è 75
- L'importo del saldo cliente-92 è 275
- Il totale è 275
L'oggetto visivo Customer Balance visualizza ora un risultato corretto. Seguire le istruzioni di filtro per se stessi e vedere come sono stati calcolati i saldi dei clienti. Comprendere anche che il totale visivo indica tutti i clienti.
Un utente che non ha familiarità con le relazioni del modello potrebbe concludere che il risultato non è corretto. Potrebbero chiedersi: perché non è il saldo totale per Customer-91 e Customer-92 uguale a 350 (75 + 275)?
La risposta alla loro domanda consiste nel comprendere la relazione molti-a-molti. Ogni saldo del cliente può rappresentare l'aggiunta di più saldi del conto e quindi i saldi dei clienti non sono additivi.
Correlare le linee guida per le dimensioni molti-a-molti
Quando si dispone di una relazione molti-a-molti tra tabelle di tipo dimensione, vengono fornite le indicazioni seguenti:
- Aggiungere ogni entità correlata molti-a-molti come tabella del modello, assicurandosi che abbia una colonna identificatore univoco (ID)
- Aggiungere una tabella di bridging per archiviare le entità associate
- Creare relazioni uno-a-molti tra le tre tabelle
- Configurare una relazione bidirezionale per consentire la propagazione dei filtri per continuare con le tabelle di tipo fatto
- Quando non è appropriato disporre di valori ID mancanti, impostare la proprietà Is Nullable delle colonne ID su FAL edizione Standard. L'aggiornamento dei dati avrà esito negativo se i valori mancanti vengono originati
- Nascondere la tabella bridging (a meno che non contenga colonne o misure aggiuntive necessarie per la creazione di report)
- Nascondere tutte le colonne ID non adatte per la creazione di report( ad esempio, quando gli ID sono chiavi surrogate)
- Se è opportuno lasciare visibile una colonna ID, assicurarsi che si tratti della diapositiva "uno" della relazione, nascondendo sempre la colonna laterale "molti". Si ottengono prestazioni di filtro ottimali.
- Per evitare confusione o interpretazione errata, comunicare spiegazioni agli utenti del report, è possibile aggiungere descrizioni con caselle di testo o descrizioni comando intestazione visiva
Non è consigliabile correlare direttamente le tabelle con tipo di dimensione molti-a-molti. Questo approccio di progettazione richiede la configurazione di una relazione con cardinalità molti-a-molti. Concettualmente può essere ottenuto, ma implica che le colonne correlate conterranno valori duplicati. Tuttavia, è una procedura di progettazione ben accettata, che le tabelle di tipo dimensione hanno una colonna ID. Le tabelle di tipo dimensione devono usare sempre la colonna ID come lato "uno" di una relazione.
Correlare fatti molti-a-molti
Il secondo tipo di scenario molti-a-molti prevede la correlazione di due tabelle di tipo fatto. Due tabelle di tipo fatto possono essere correlate direttamente. Questa tecnica di progettazione può essere utile per un'esplorazione rapida e semplice dei dati. Tuttavia, e per essere chiari, in genere non è consigliabile questo approccio di progettazione. Verrà spiegato perché più avanti in questa sezione.
Si consideri un esempio che include due tabelle di tipo fatto: Order e Fulfillment. La tabella Order contiene una riga per riga di ordine e la tabella Fulfillment può contenere zero o più righe per riga di ordine. Le righe nella tabella Order rappresentano gli ordini di vendita. Le righe nella tabella Fulfillment rappresentano gli articoli degli ordini che sono stati spediti. Una relazione molti-a-molti è correlata alle due colonne OrderID, con propagazione del filtro solo dalla tabella Order (Order filters Fulfillment).
La cardinalità della relazione è impostata su molti-a-molti per supportare l'archiviazione di valori OrderID duplicati in entrambe le tabelle. Nella tabella Order i valori OrderID duplicati possono esistere perché un ordine può avere più righe. Nella tabella Fulfillment i valori OrderID duplicati possono esistere perché gli ordini possono avere più righe e le righe degli ordini possono essere soddisfatte da molte spedizioni.
Verranno ora esaminate le righe della tabella. Nella tabella Fulfillment si noti che le righe degli ordini possono essere soddisfatte da più spedizioni. L'assenza di una riga di ordine indica che l'ordine è ancora da soddisfare.
I dettagli della riga per le due tabelle sono descritti nell'elenco puntato seguente:
- La tabella Order include cinque righe:
- OrderDate 1 gennaio 2019, OrderID 1, OrderLine 1, ProductID Prod-A, OrderQuantity 5, Sales 50
- OrderDate 1 gennaio 2019, OrderID 1, OrderLine 2, ProductID Prod-B, OrderQuantity 10, Sales 80
- OrderDate 2 febbraio 2019, OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Sales 40
- OrderDate 2 febbraio 2019, OrderID 2, OrderLine 2, ProductID Prod-C, OrderQuantity 1, Sales 20
- OrderDate 3 marzo 2019, OrderID 3, OrderLine 1, ProductID Prod-C, OrderQuantity 5, Sales 100
- La tabella Fulfillment contiene quattro righe:
- FulfillmentDate 1 gennaio 2019, FulfillmentID 50, OrderID 1, OrderLine 1, FulfillmentQuantity 2
- FulfillmentDate 2 febbraio 2019, FulfillmentID 51, OrderID 2, OrderLine 1, FulfillmentQuantity 5
- FulfillmentDate 2 febbraio 2019, FulfillmentID 52, OrderID 1, OrderLine 1, FulfillmentQuantity 3
- FulfillmentDate 1 gennaio 2019, FulfillmentID 53, OrderID 1, OrderLine 2, FulfillmentQuantity 10
Vediamo cosa accade quando viene eseguita una query sul modello. Di seguito è riportato un oggetto visivo tabella che confronta le quantità di ordine e evasione in base alla colonna OrderID della tabella OrderID.
L'oggetto visivo presenta un risultato accurato. Tuttavia, l'utilità del modello è limitata, ma è possibile filtrare o raggruppare solo in base alla colonna OrderID della tabella Order.
Correlare indicazioni su molti-a-molti fatti
In genere, non è consigliabile correlare direttamente due tabelle di tipo fatto usando la cardinalità molti-a-molti. Il motivo principale è che il modello non offre flessibilità nei modi in cui vengono filtrati o raggruppati gli oggetti visivi del report. Nell'esempio è possibile filtrare o raggruppare solo gli oggetti visivi in base alla colonna OrderID della tabella Order. Un motivo aggiuntivo riguarda la qualità dei dati. Se i dati presentano problemi di integrità, è possibile che alcune righe vengano omesse durante l'esecuzione di query a causa della natura della relazione limitata. Per altre informazioni, vedere Relazioni tra modelli in Power BI Desktop (valutazione delle relazioni).
Invece di correlare direttamente le tabelle di tipo fatto, è consigliabile adottare principi di progettazione dello schema Star. A tale scopo, aggiungere tabelle di tipo dimensione. Le tabelle di tipo dimensione sono quindi correlate alle tabelle di tipo fatto usando relazioni uno-a-molti. Questo approccio di progettazione è affidabile perché offre opzioni di creazione di report flessibili. Consente di filtrare o raggruppare utilizzando una delle colonne di tipo dimensione e riepilogare qualsiasi tabella di tipo fact correlata.
Si consideri una soluzione migliore.
Si notino le modifiche di progettazione seguenti:
- Il modello include ora quattro tabelle aggiuntive: OrderLine, OrderDate, Product e FulfillmentDate
- Le quattro tabelle aggiuntive sono tutte tabelle di tipo dimensione e le relazioni uno-a-molti sono correlate alle tabelle di tipo fatto
- La tabella OrderLine contiene una colonna OrderLineID , che rappresenta il valore OrderID moltiplicato per 100, più il valore OrderLine , un identificatore univoco per ogni riga di ordine
- Le tabelle Order e Fulfillment contengono ora una colonna OrderLineID e non contengono più le colonne OrderID e OrderLine
- La tabella Fulfillment contiene ora le colonne OrderDate e ProductID
- La tabella FulfillmentDate è correlata solo alla tabella Fulfillment
- Tutte le colonne di identificatore univoco sono nascoste
L'applicazione dei principi di progettazione dello schema star offre i vantaggi seguenti:
- Gli oggetti visivi del report possono filtrare o raggruppare in base a qualsiasi colonna visibile dalle tabelle di tipo dimensione
- Gli oggetti visivi del report possono riepilogare qualsiasi colonna visibile dalle tabelle di tipo fatto
- I filtri applicati alle tabelle OrderLine, OrderDate o Product verranno propagati a entrambe le tabelle di tipo fatto
- Tutte le relazioni sono uno-a-molti e ogni relazione è una relazione regolare. I problemi di integrità dei dati non verranno mascherati. Per altre informazioni, vedere Relazioni tra modelli in Power BI Desktop (valutazione delle relazioni).
Correlare fatti di granularità più elevati
Questo scenario molti-a-molti è molto diverso dagli altri due già descritti in questo articolo.
Si consideri un esempio che include quattro tabelle: Date, Sales, Product e Target. Date e Product sono tabelle di tipo dimensione e le relazioni uno-a-molti sono correlate alla tabella dei fatti Sales. Finora, rappresenta una buona progettazione dello schema star. La tabella Target , tuttavia, deve essere ancora correlata alle altre tabelle.
La tabella Target contiene tre colonne: Category, TargetQuantity e TargetYear. Le righe della tabella rivelano una granularità dell'anno e della categoria di prodotti. In altre parole, gli obiettivi, usati per misurare le prestazioni delle vendite, vengono impostati ogni anno per ogni categoria di prodotto.
Poiché la tabella Target archivia i dati a un livello superiore rispetto alle tabelle di tipo dimensione, non è possibile creare una relazione uno-a-molti. Beh, è vero solo per una delle relazioni. Si esaminerà ora come la tabella Target può essere correlata alle tabelle di tipo dimensione.
Correlare periodi di tempo più elevati
Una relazione tra le tabelle Date e Target deve essere una relazione uno-a-molti. Perché i valori della colonna TargetYear sono date. In questo esempio, ogni valore della colonna TargetYear è la prima data dell'anno di destinazione.
Suggerimento
Quando si archiviano fatti con una granularità temporale superiore al giorno, impostare il tipo di dati della colonna su Date (o Numero intero se si usano chiavi di data). Nella colonna archiviare un valore che rappresenta il primo giorno del periodo di tempo. Ad esempio, un periodo di anno viene registrato come 1 gennaio dell'anno e un periodo di mese viene registrato come primo giorno del mese.
È tuttavia necessario prestare attenzione per garantire che i filtri a livello di mese o di data producano un risultato significativo. Senza una logica di calcolo speciale, gli oggetti visivi del report possono segnalare che le date di destinazione sono letteralmente il primo giorno di ogni anno. Tutti gli altri giorni, e tutti i mesi tranne gennaio, riepilogeranno la quantità di destinazione come BLANK.
L'oggetto visivo matrice seguente mostra cosa accade quando l'utente del report esegue il drill-through da un anno ai relativi mesi. L'oggetto visivo riepiloga la colonna TargetQuantity . (L'oggetto Mostra elementi senza opzione dati abilitata per le righe della matrice.
Per evitare questo comportamento, è consigliabile controllare il riepilogo dei dati dei fatti usando misure. Un modo per controllare il riepilogo consiste nel restituire BLANK quando vengono sottoposti a query periodi di tempo di livello inferiore. Un altro modo, definito con alcuni DAX sofisticati, consiste nel suddividere i valori in periodi di tempo di livello inferiore.
Si consideri la definizione di misura seguente che usa la funzione DAX ISFILTERED . Restituisce solo un valore quando le colonne Date o Month non vengono filtrate.
Target Quantity =
IF(
NOT ISFILTERED('Date'[Date])
&& NOT ISFILTERED('Date'[Month]),
SUM(Target[TargetQuantity])
)
L'oggetto visivo matrice seguente usa ora la misura Quantità di destinazione . Mostra che tutte le quantità di destinazione mensili sono BLANK.
Correlare granularità superiore (non data)
È necessario un approccio di progettazione diverso quando si correla una colonna non data da una tabella di tipo dimensione a una tabella di tipo fatto (e si trova a un livello più elevato rispetto alla tabella di tipo dimensione).
Le colonne Category (dalle tabelle Product e Target ) contengono valori duplicati. Quindi, non c'è "uno" per una relazione uno-a-molti. In questo caso, è necessario creare una relazione molti-a-molti. La relazione deve propagare i filtri in una singola direzione, dalla tabella di tipo dimensione alla tabella di tipo fact.
Verranno ora esaminate le righe della tabella.
Nella tabella Target sono presenti quattro righe: due righe per ogni anno di destinazione (2019 e 2020) e due categorie (Abbigliamento e Accessori). Nella tabella Product sono presenti tre prodotti. Due appartengono alla categoria abbigliamento, e uno appartiene alla categoria accessori. Uno dei colori dell'abbigliamento è verde e i due rimanenti sono blu.
Il raggruppamento di oggetti visivi di tabella in base alla colonna Category della tabella Product produce il risultato seguente.
Questo oggetto visivo produce il risultato corretto. Si esaminerà ora cosa accade quando la colonna Color della tabella Product viene usata per raggruppare la quantità di destinazione.
L'oggetto visivo produce una rappresentazione errata dei dati. Cosa succede qui?
Un filtro sulla colonna Color della tabella Product restituisce due righe. Una delle righe è per la categoria Clothing e l'altra per la categoria Accessori. Questi due valori di categoria vengono propagati come filtri alla tabella Target . In altre parole, poiché il colore blu viene usato dai prodotti di due categorie, tali categorie vengono usate per filtrare le destinazioni.
Per evitare questo comportamento, come descritto in precedenza, è consigliabile controllare il riepilogo dei dati dei fatti usando misure.
Si consideri la definizione di misura seguente. Si noti che tutte le colonne della tabella Product al di sotto del livello di categoria vengono testate per i filtri.
Target Quantity =
IF(
NOT ISFILTERED('Product'[ProductID])
&& NOT ISFILTERED('Product'[Product])
&& NOT ISFILTERED('Product'[Color]),
SUM(Target[TargetQuantity])
)
L'oggetto visivo tabella seguente usa ora la misura Quantità di destinazione . Mostra che tutte le quantità di destinazione del colore sono BLANK.
La progettazione del modello finale è simile alla seguente.
Correlare informazioni dettagliate più elevate
Quando è necessario correlare una tabella di tipo dimensione a una tabella di tipo fatto e la tabella di tipo fatto archivia le righe a un livello più elevato rispetto alle righe della tabella di tipo dimensione, vengono fornite le indicazioni seguenti:
- Per date dei fatti più elevati:
- Nella tabella di tipo fatto archiviare la prima data del periodo di tempo
- Creare una relazione uno-a-molti tra la tabella date e la tabella di tipo fatto
- Per altri fatti più elevati:
- Creare una relazione molti-a-molti tra la tabella di tipo dimensione e la tabella di tipo fatto
- Per entrambi i tipi:
- Controllare il riepilogo con la logica di misura: restituisce BLANK quando vengono usate colonne di tipo dimensione di livello inferiore per filtrare o raggruppare
- Nascondere le colonne di tabella di tipo fatto riepilogabile. In questo modo, è possibile usare solo le misure per riepilogare la tabella di tipo fatto
Contenuto correlato
Per altre informazioni relative a questo articolo, vedere le risorse seguenti:
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per