Condividi tramite

Linee guida per le relazioni molti-a-molti

  • Articolo

Questo articolo è destinato a te, modellatore di dati che lavora con Power BI Desktop. Descrive tre diversi scenari di modellazione molti-a-molti. Fornisce inoltre indicazioni su come progettare con successo per loro nei tuoi 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 a stella. Per ulteriori informazioni, vedere Comprendere lo schema a stella e la sua importanza per Power BI.

Esistono tre scenari diversi di tipo molti-a-molti. Possono verificarsi quando è necessario:

Correlare dimensioni molti-a-molti

Lo scenario classico molti a molti riguarda due entità, ad esempio i clienti 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, viene comunemente chiamato titolari di conti comuni.

La modellazione di queste entità è semplice. Una tabella delle dimensioni archivia gli account e un'altra tabella delle dimensioni archivia i clienti. Come è caratteristica delle tabelle delle dimensioni, in ogni tabella è presente una colonna identificatore univoco (ID). Per modellare la relazione tra le due tabelle, è necessaria una terza tabella. Questa tabella è comunemente chiamata 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 identificative, viene chiamata tabella priva di fatti .

Ecco un diagramma semplicistico delle tre tabelle del modello.

Diagramma che mostra tre tabelle del modello. La progettazione è descritta nel paragrafo seguente.

La prima tabella è denominata Accounte contiene due colonne: AccountID e Account. La seconda tabella è denominata AccountCustomere contiene due colonne: AccountID e CustomerID. La terza tabella è denominata Customere 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 dei fatti denominata Transaction. Registra le transazioni di conto. La tabella di raccordo e tutte le colonne identificatrici sono state nascoste.

Diagramma che mostra che un diagramma del modello comprende quattro tabelle. Sono state aggiunte relazioni uno-a-molti per correlare tutte le tabelle.

Per descrivere il funzionamento della propagazione del filtro delle relazioni, il diagramma del modello è stato modificato per visualizzare le righe della tabella.

Diagramma che mostra le tabelle del modello e le relative righe. I dettagli della riga per le quattro tabelle sono descritti nel paragrafo seguente.

I dettagli delle righe per le quattro tabelle sono presentati nell'elenco puntato seguente:

  • La tabella Account ha due righe:
    • AccountID 1 è per Account-01
    • AccountID 2 è 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 CustomerID91
    • AccountID 1 è associato a CustomerID92
    • AccountID 2 è associato a CustomerID92
  • La tabella Transaction ha tre righe:
    • Date 1 gennaio 2019, AccountID3, Amount100
    • Date 2 febbraio 2019, AccountID2, Amount200
    • Date 3 marzo 2019, AccountID1, Amount-25

Vediamo cosa accade quando il modello viene interrogato.

Nell'immagine seguente, ci sono due visualizzazioni di tabella che riepilogano la colonna Amount della tabella Transaction. Il primo oggetto visivo raggruppa per conto, quindi la somma delle colonne Amount rappresenta il saldo del conto . Il secondo grafico raggruppa per cliente, pertanto, la somma delle colonne Amount rappresenta il saldo del cliente .

Diagramma di due viste tabella affiancate. Le viste sono descritte nel paragrafo seguente.

La prima tabella visuale (Account Balance) include due colonne: Account e Amount. Viene visualizzato il risultato seguente:

  • importo del saldo conto-01 è 75.
  • importo del saldo conto-02 è 200.
  • Il totale è 275.

Il secondo visual della tabella (Customer Balance) 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 alla visualizzazione del Saldo conto rivela che il risultato è corretto, per ogni conto e l'importo totale. Ciò è dovuto al fatto che ogni raggruppamento di account comporta una propagazione del filtro nella tabella Transaction per tale account.

Tuttavia, qualcosa non sembra corretto con la visualizzazione Customer Balance. Ogni cliente in questa visualizzazione 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. Si è verificato un problema ed è correlato alla propagazione dei filtri. I filtri non vengono trasmessi fino alla tabella Transaction.

Se si seguono le direzioni del filtro delle relazioni dalla tabella Customer alla tabella Transaction, è possibile determinare che la relazione tra le tabelle Account e AccountCustomer si propaga nella direzione errata. La direzione del filtro per questa relazione deve essere impostata su Both.

Diagramma che mostra che il modello è stato aggiornato. Ora filtra in entrambe le direzioni.

Diagramma che mostra gli stessi due oggetti visivi del report affiancati. Il primo oggetto visivo non è cambiato, mentre il secondo oggetto visivo è stato modificato.

Come previsto, non è stata apportata alcuna modifica alla visualizzazione del saldo del conto.

Il grafico Customer Balance ora visualizza il seguente risultato:

  • Cliente-91 importo del saldo è 75.
  • importo del saldo cliente-92 è 275.
  • Il totale è 275.

La visualizzazione del Customer Balance ora mostra un risultato corretto. Segui le istruzioni del filtro per conto proprio e osserva come sono stati calcolati i saldi dei clienti. Comprendere anche che il totale visivo significa tutti i clienti.

Un utente che non ha familiarità con le relazioni del modello potrebbe concludere che il risultato non è corretto. Potrebbero chiedere: 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 cliente può rappresentare l'aggiunta di più saldi del conto; pertanto, i saldi dei clienti sono non cumulativi.

Guida sul rapporto delle dimensioni molti-a-molti

Quando si dispone di una relazione molti-a-molti tra le tabelle dimensionali, seguire queste indicazioni:

  • Aggiungere ogni entità correlata molti-a-molti come tabella del modello, assicurandosi che abbia una colonna ID.
  • Aggiungere una tabella di collegamento per archiviare le entità associate.
  • Creare relazioni uno-a-molti tra le tre tabelle.
  • Impostare una relazione bidirezionale per consentire la propagazione dei filtri fino alla tabella dei fatti.
  • Quando non è appropriato avere valori ID mancanti, disabilitare la proprietà Is Nullable. L'aggiornamento dei dati avrà esito negativo quando i valori mancanti vengono rilevati.
  • Nascondere la tabella di collegamento, a meno che non contenga altre colonne o misure necessarie per il reporting.
  • Nascondere tutte le colonne ID non adatte per la creazione di report, ad esempio quando le colonne archiviano valori di chiave surrogata.
  • Se è opportuno lasciare visibile una colonna ID, assicurarsi che si tratti del lato "uno" della relazione, nascondendo sempre la colonna laterale "molti". Ciò è dovuto al fatto che i filtri applicati all'unica diapositiva generano prestazioni di filtro migliori.
  • Per evitare confusione o interpretazioni errate, comunicate le spiegazioni agli utenti del report. È possibile aggiungere descrizioni con caselle di testo o tooltip dell'intestazione visiva.

Non è consigliabile correlare direttamente le tabelle delle dimensioni 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 potrebbero contenere valori duplicati. È una procedura di progettazione ben accettata tuttavia, che le tabelle delle dimensioni abbiano una colonna ID. Le tabelle delle dimensioni devono usare sempre la colonna ID come lato "uno" di una relazione.

Correlare fatti in un rapporto molti a molti

Un tipo di scenario molti-a-molti diverso prevede la correlazione di due tabelle di fatti. Due tabelle dei fatti 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 dei fatti: 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 spediti. Una relazione molti-a-molti è correlata alle colonne OrderID in ogni tabella, con propagazione del filtro solo dalla tabella Order (ovvero la tabella Order filtra la tabella Fulfillment).

Diagramma che mostra un modello contenente due tabelle: Ordine e Adempimento.

La cardinalità della relazione è impostata su Many-to-many per supportare l'archiviazione di valori duplicati della colonna OrderID in entrambe le tabelle. Nella tabella Order i valori ID duplicati possono esistere perché un ordine può avere più righe. Nella tabella Fulfillment possono esistere valori ID duplicati perché gli ordini possono avere più righe e le righe degli ordini possono essere evase da diverse 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.

Diagramma che mostra le righe della tabella del modello. I dettagli della riga per le due tabelle sono descritti nel paragrafo seguente.

I dettagli delle righe per le due tabelle sono descritti nell'elenco puntato seguente.

  • La tabella Order ha cinque righe:
    • OrderDate 1 gennaio 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
    • OrderDate 1 gennaio 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
    • OrderDate 2 febbraio 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
    • OrderDate 2 febbraio 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
    • OrderDate 3 marzo 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
  • La tabella Fulfillment ha quattro righe:
    • FulfillmentDate 1 gennaio 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
    • FulfillmentDate 2 febbraio 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
    • FulfillmentDate 2 febbraio 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
    • FulfillmentDate 1 gennaio 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Vediamo cosa accade quando si interroga il modello. Ecco una tabella di confronto delle quantità di ordine e di evasione in base alla colonna OrderID della tabella Order.

Diagramma che mostra una visualizzazione a tabella con tre colonne: OrderID, OrderQuantity e FulfillmentQuantity.

L'immagine presenta un risultato accurato. Tuttavia, l'utilità del modello è limitata perché è possibile filtrare o raggruppare solo in base alla tabella OrderOrderID colonna.

Guida sulla correlazione di fatti molti-a-molti

In genere, non è consigliabile correlare due tabelle di fatti utilizzando direttamente la cardinalità molti-a-molti. Il motivo principale è che il modello non offre flessibilità nei modi in cui gli oggetti visivi del report filtrano o raggruppano. Nell'esempio, solo gli oggetti visivi possono essere filtrati o raggruppati nella colonna OrderID della tabella Order. Un altro motivo 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 cardinalità molti-a-uomo e relazioni limitate.

Anziché correlare direttamente le tabelle dei fatti, è consigliabile implementare un progettazione dello schema a stella . Ciò significa che si aggiungono tabelle delle dimensioni. Queste tabelle delle dimensioni sono quindi correlate alle tabelle dei fatti tramite relazioni uno-a-molti. Questo approccio di progettazione è affidabile perché offre in modo efficiente opzioni flessibili per la creazione di report. Consente di filtrare o raggruppare utilizzando una delle colonne della tabella delle dimensioni e riepilogare le colonne di qualsiasi tabella dei fatti correlata.

Si consideri una soluzione migliore.

Diagramma che illustra un modello costituito da sei tabelle: OrderLine, OrderDate, Order, Fulfillment, Product e FulfillmentDate.

Si notino le modifiche di progettazione seguenti:

  • Il modello include ora quattro tabelle aggiuntive: OrderLine, OrderDate, Producte FulfillmentDate.
  • Le quattro tabelle aggiuntive sono tutte tabelle di dimensioni in cui le relazioni uno-a-molti le collegano alle tabelle dei fatti.
  • La tabella OrderLine contiene la colonna OrderLineID, che archivia il valore OrderID moltiplicato per 100, più il valore della colonna OrderLine, ovvero un ID per ogni riga dell'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 colonne OrderDate e ProductID.
  • La tabella FulfillmentDate ha una relazione solo con la tabella Fulfillment.
  • Tutte le colonne ID sono nascoste.

L'adozione di una progettazione di uno schema a stella offre i seguenti vantaggi:

  • Gli oggetti visivi del report possono filtrare o raggruppare da qualsiasi colonna visibile dalle tabelle delle dimensioni.
  • Gli oggetti visivi del report possono riepilogare qualsiasi colonna visibile dalle tabelle di fatti.
  • I filtri applicati alle tabelle OrderLine, OrderDateo Product vengono propagati a entrambe le tabelle di fatti.
  • 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 sulla valutazione delle relazioni, vedere relazioni tra modelli di in Power BI Desktop.

Correlare dati di grana più fine

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, Producte Target. Le tabelle Date e Product sono tabelle delle dimensioni, e ciascuna ha una relazione uno-a-molti con la tabella dei fatti Sales. Finora, rappresenta un buon design dello schema a stella. La tabella Target, tuttavia, non è ancora stata correlata alle altre tabelle.

Diagramma che mostra un modello che comprende quattro tabelle: Date, Sales, Product e Target.

La tabella Target contiene tre colonne: Category, TargetQuantitye 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.

Diagramma che mostra le tabelle dei fatti Sales e Target. La tabella dei fatti Target include tre colonne: TargetYear, Category e TargetQuantity.

Poiché la tabella Target archivia i dati a un livello superiore rispetto alle tabelle delle dimensioni, 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 delle dimensioni.

Correlare periodi di tempo a maggiore granularità

Una relazione tra le tabelle Date e Target deve essere una relazione uno-a-molti. Ciò è dovuto al fatto che i valori di colonna TargetYear sono date. In questo esempio ogni colonna TargetYear archivia la prima data dell'anno di destinazione.

Consiglio

Quando si archiviano fatti con una granularità temporale superiore al giorno, impostare il tipo di dati della colonna su Data (o su Numero intero se si utilizzano chiavi di data). Nella colonna, memorizzare un valore che rappresenta il primo giorno del periodo. 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, le visualizzazioni del report potrebbero segnalare che le date target sono letteralmente il primo giorno di ogni anno. Tutti gli altri giorni, e tutti i mesi tranne gennaio, riepilogheranno la quantità obiettivo come BLANK.

La seguente visualizzazione a matrice mostra cosa accade quando l'utente del report esegue il drill-down da un anno ai relativi mesi. Il visuale riassume la colonna TargetQuantity. L'opzione Mostra elementi senza dati è stata abilitata per le righe della matrice.

Diagramma che mostra una vista matrice che rivela l'obiettivo per l'anno 2020 di 270. Fornisce valori errati per data.

Per evitare questo comportamento, è consigliabile controllare il riepilogo dei dati fattuali utilizzando metriche. Un modo per controllare il riepilogo consiste nel restituire BLANK quando vengono interrogati i 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 ISFILTERED DAX. Restituisce solo un valore quando le colonne Date e Month non vengono filtrate.

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

Nella matrice visiva seguente viene utilizzata la misura Target Quantity. Mostra che tutte le quantità obiettivi mensili sono BLANK.

Diagramma che mostra due visualizzazioni in matrice. Il primo rivela l'obiettivo del primo mese del 2020 pari a 270 mentre il secondo è vuoto.

Collegare a un livello di dettaglio più fine (non data)

È necessario un approccio di progettazione diverso quando si correla una colonna non legata a una data da una tabella dimensionale a una tabella dei fatti, ed è a un livello di granularità superiore rispetto alla tabella dimensionale.

Le colonne Category (sia dalle tabelle Product che da Target) contengono valori duplicati. Quindi, non c'è un lato "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 delle dimensioni alla tabella dei fatti.

Diagramma che mostra un modello delle tabelle Target e Product. Una relazione molti-a-molti collega le due tabelle.

Ora diamo un'occhiata alle righe della tabella.

Diagramma che mostra un modello contenente due tabelle: Target e Product. Una relazione molti-a-molti è correlata alle due colonne Category.

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.

Una visualizzazione della tabella raggruppata in base alla colonna Category della tabella Product produce il seguente risultato. Tuttavia, questo oggetto visivo produce il risultato corretto. Consideriamo ora cosa accade quando la colonna Color della tabella Product viene usata per raggruppare la quantità target.

Diagramma che mostra due oggetti visivi a forma di tabella. Il primo raggruppa per categoria e il secondo per colore. Il secondo oggetto visivo produce un risultato non corretto.

La visualizzazione 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 utilizzato dai prodotti appartenenti a due categorie, tali categorie vengono usate per filtrare gli obiettivi.

Per evitare questo comportamento, come descritto in precedenza, è consigliabile controllare il riepilogo dei dati relativi ai fatti usando delle 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])
)

Nella visualizzazione tabella seguente viene utilizzata la misura Target Quantity. Mostra che tutte le quantità di destinazione del colore sono BLANK.

Diagramma che mostra due oggetti visivi tabella. Il primo raggruppa per categoria e il secondo per colore. Il secondo oggetto visivo produce un risultato corretto di vuoto.

La progettazione del modello finale è simile alla seguente.

Diagramma che mostra un modello con le tabelle Data e Obiettivo correlate a una relazione uno-a-molti.

Orientamento su dati più dettagliati

Quando è necessario correlare una tabella delle dimensioni a una tabella dei fatti e la tabella dei fatti memorizza le righe a un livello più dettagliato rispetto alle righe della tabella delle dimensioni, seguire queste indicazioni:

  • Per date dei dati a grana più fine
    • Nella tabella dei fatti archiviare la prima data del periodo di tempo.
    • Creare una relazione uno-a-molti tra la tabella delle date e la tabella dei fatti.
  • Per altre informazioni più granulari
    • Creare una relazione molti-a-molti tra la tabella delle dimensioni e la tabella dei fatti.
  • Per entrambi i tipi
    • Controlla il riepilogo con la logica di misura: restituisci BLANK quando vengono utilizzate colonne di dimensione di un livello inferiore per filtrare o raggruppare.
    • Nascondere le colonne riassumibili della tabella dei fatti, in modo che solo le misure possano essere utilizzate per riassumere la tabella dei fatti.

Contenuto correlato

Per altre informazioni relative a questo articolo, vedere le risorse seguenti: