Több-több kapcsolatok útmutatója

Ez a cikk adatmodellezőként célozza meg Önt, aki a Power BI Desktoppal dolgozik. Három különböző sok-a-sokhoz modellezési esetet ír le. Továbbá útmutatást ad arra vonatkozóan is, hogyan tervezzen sikeresen a modellekben azok számára.

Három különböző több-a-többhöz forgatókönyv létezik. Ezek akkor fordulhatnak elő, amikor szükséges, hogy:

• Két dimenziótábla összekapcsolása
• Két ténytábla összekapcsolás
• Magasabb részletességű ténytáblák összekapcsolása, amikor a ténytábla a dimenziótábla sorainál nagyobb részletességgel tárolja a sorokat

Több-több kapcsolatok összekapcsolása

A klasszikus több-a-többhöz kapcsolódó helyzet két entitást érint, például banki ügyfeleket és bankszámlákat. Vegye figyelembe, hogy az ügyfelek több fiókkal is rendelkezhetnek, és a fiókok több ügyfélhez is tartozhatnak. Ha egy fióknak több ügyfele van, gyakran közös fióktulajdonosok.

Ezeknek az entitásoknak a modellezése egyenesen előre halad. Egy dimenziótábla tárolja a fiókokat, egy másik dimenziótábla pedig az ügyfeleket. Ahogy a dimenziótáblákra jellemző, minden táblában egyedi azonosító (ID) oszlop található. A két tábla közötti kapcsolat modellezéséhez egy harmadik táblára van szükség. Ezt a táblát gyakran nevezik áthidaló táblázatnak. Ebben a példában az a cél, hogy minden ügyfélfiók-társításhoz egy sort tároljon. Érdekes, hogy ha ez a tábla csak azonosító oszlopokat tartalmaz, ténytáblának nevezzük.

Íme egy egyszerű diagram a három modelltábláról.

Az első tábla neve Account, és két oszlopot tartalmaz: AccountID és Account. A második tábla neve AccountCustomer, és két oszlopot tartalmaz: AccountID és CustomerID. A harmadik tábla neve Customer, és két oszlopot tartalmaz: CustomerID és Customer. A táblák között nem léteznek kapcsolatok.

Két egy-a-többhöz kapcsolat van hozzáadva a táblák összekapcsolásához. Íme egy frissített modelldiagram a kapcsolódó táblákról. Hozzáadtunk egy Transaction nevű ténytáblát. A fióktranzakciókat rögzíti. Az áthidaló táblázat és az összes azonosítóoszlop el lett rejtve.

A kapcsolatszűrő propagálásának működésének leírásához a modelldiagram módosult a táblasorok megjelenítéséhez.

A négy tábla sorainak adatai a következő pontozott listában jelennek meg:

• A Account tábla két sorból áll:
  • AccountID 1 a(z) Account-01 számára
  • AccountID 2 a Account-02 számára
• A Customer tábla két sorból áll:
  • CustomerID 91Ügyfél-91 számára van
  • CustomerID 92 a Customer-92 számára
• A AccountCustomer tábla három sorból áll:
  • AccountID 1 kapcsolódik CustomerID91
  • AccountID 1 társítva van CustomerID92-hez
  • AccountID 2 össze van kapcsolva CustomerID92
• A Transaction tábla három sorból áll:
  • Date 2019. január 1., AccountID1, Amount100
  • Date 2019. február 2., AccountID2., Amount200
  • Date 2019. március 3-án, AccountID1, Amount-25

Nézzük meg, mi történik a modell lekérdezésekor.

A következő képen két táblavizualizáció található, amelyek a Transaction tábla Amount oszlopát összegzik. Az első vizuális elem fiók szerint van csoportosítva, így a Amount oszlopok összege a számlaegyenleg-t képviseli. A második vizualizáció ügyfél szerint van csoportosítva, így a Amount oszlopok összege a ügyfélegyenleg.

Az első táblavizualizáció (Account Balance) két oszlopból áll: Account és Amount. A következő eredményt jeleníti meg:

• Account-01 egyenleg összege 75.
• Account-02 egyenleg összege 200.
• Az összeg 275.

A második táblavizualizáció (Customer Balance) két oszlopból áll: Customer és Amount. A következő eredményt jeleníti meg:

• Customer-91 egyenlegösszeg 275.
• Customer-92 egyenleg összege 275.
• Az összeg 275.

A táblasorok és a Számlaegyenleg ábrázolás gyors áttekintése megmutatja, hogy az eredmény az egyes számlák és a teljes összeg tekintetében helyes. Ennek az az oka, hogy minden fiók csoportosítása szűrőpropagálást eredményez az adott fiók Transaction táblájába.

A Customer Balance vizualizációval azonban valami nem tűnik helyesnek. A vizualizáció minden ügyfelének annyi az egyenlege, mint a teljes egyenleg. Ez az eredmény csak akkor lehetne helyes, ha minden ügyfél minden számla közös számlatulajdonosa lenne. Ebben a példában nem ez a helyzet. Van egy probléma, amely a szűrés propagálásával kapcsolatos. A szűrők nem haladnak végig a Transaction tábláig.

Ha az Customer táblától a Transaction tábláig követi a kapcsolatszűrő irányait, megállapíthatja, hogy a Account és a AccountCustomer táblák közötti kapcsolat nem a megfelelő irányba propagálja a kapcsolatot. A kapcsolat szűrőirányának Bothkell lennie.

A vártnak megfelelően nem történt változás az Account Balance vizualizációban.

A Customer Balance vizualizáció azonban a következő eredményt jeleníti meg:

• Customer-91 egyenleg összege 75.
• Customer-92 egyenleg összege 275.
• Az összeg 275.

Az Ügyfélegyenleg vizualizáció most már helyes eredményt jelenít meg. Kövesse a szűrési utasításokat saját magának, és nézze meg, hogyan számították ki az ügyfélegyenlegeket. Azt is meg kell értenie, hogy a vizualizáció összege azt jelenti, minden ügyfél.

Ha valaki nem ismeri a modellkapcsolatokat, az arra következtethet, hogy az eredmény helytelen. Feltehetik a kérdést: Miért nem egyenlő a Customer-91 és Customer-92 teljes egyenlege 350 -nek (75 + 275)?

A kérdésre a válasz a több a többhöz kapcsolat megértésében rejlik. Minden ügyfélegyenleg több számlaegyenleg hozzáadását is jelentheti, így az ügyfélegyenlegek nem additív.

Sok-sok kapcsolat dimenzióinak kapcsolódása – útmutató

Ha több-a-többhöz kapcsolatról van szó a dimenziótáblák között, kövesse az alábbi útmutatót:

• Az egymáshoz kapcsolódó, több-a-többhöz viszonyban álló entitásokat adja hozzá mint modelltáblát, és biztosítsa, hogy rendelkezzen azonosító oszloppal.
• Adjon hozzá egy áthidaló táblát a társított entitások tárolásához.
• Hozzon létre egy-a-többhöz kapcsolatokat a három tábla között.
• Állítson be egy kétirányú kapcsolatot, hogy a szűrőpropagálás folytatódhasson a ténytábláig.
• Ha nem megfelelő a hiányzó azonosítóértékek használata, tiltsa le a Is Nullable tulajdonságot – a hiányzó értékek forrásának létrehozásakor az adatfrissítés sikertelen lesz.
• Rejtse el az áthidaló táblát (kivéve, ha más oszlopokat vagy mértékeket tartalmaz a jelentéskészítéshez).
• Rejtse el azokat az azonosító oszlopokat, amelyek nem alkalmasak jelentéskészítésre (például ha az oszlopok helyettesítő kulcsértékeket tárolnak).
• Ha érdemes láthatóvá tenni egy azonosítóoszlopot, győződjön meg arról, hogy a kapcsolat "egy" oldalán van – mindig rejtse el a "több" oldaloszlopot. Ennek az az oka, hogy az "egy" diára alkalmazott szűrők jobb szűrési teljesítményt eredményeznek.
• A félreértések és a félreértelmezés elkerülése érdekében adjon meg magyarázatokat a jelentés felhasználóinak – szövegdobozokkal vagy vizualizációfejléc-elemleírásokkal.

Nem javasoljuk, hogy közvetlenül kapcsolja össze a több-több kapcsolatokkal rendelkező dimenziótáblákat. Ehhez a tervezési megközelítéshez egy sok-a-sokhoz számosságú kapcsolatot kell beállítani. Elméletileg megvalósítható, de azt jelenti, hogy a kapcsolódó oszlopok ismétlődő értékeket tartalmazhatnak. Ez egy jól elfogadott tervezési gyakorlat, azonban a dimenziótáblák azonosító oszlopot tartalmaznak. A dimenziótábláknak mindig az AZONOSÍTÓ oszlopot kell használniuk a kapcsolat "egy" oldalaként.

"Sok-sokhoz kapcsolódó tények"

Egy másik több-a-többel szcenárió típus két ténytábla összekapcsolását foglalja magában. Két ténytábla közvetlenül is összekapcsolható. Ez a tervezési technika hasznos lehet a gyors és egyszerű adatfeltáráshoz. Azonban, és hogy egyértelmű legyen, általában nem javasoljuk ezt a tervezési megközelítést. A szakasz későbbi részében elmagyarázzuk, hogy miért.

Vegyünk egy példát, amely két ténytáblát tartalmaz: Order és Fulfillment. A Order táblázat rendeléssoronként egy sort tartalmaz, a Fulfillment táblázat pedig nulla vagy több sort tartalmazhat rendeléssoronként. A Order tábla sorai értékesítési rendeléseket jelölnek. A Fulfillment tábla sorai a kiszállított rendelési cikkeket jelölik. A több-a-többhöz kapcsolat összekapcsolja az egyes táblák OrderID oszlopait, a szűrők csak a Order táblából terjednek (azaz a Order tábla szűri a Fulfillment táblát).

A kapcsolat számossága Many-to-many értékre van állítva, amely támogatja az ismétlődő OrderID oszlopértékek tárolását mindkét táblában. A Order táblában ismétlődő azonosítóértékek létezhetnek, mert egy sorrend több sort tartalmazhat. A Fulfillment táblában ismétlődő azonosítóértékek létezhetnek, mert a rendelések több sorból is lehetnek, és a rendeléssorokat számos szállítmány teljesítheti.

Nézzük meg most a táblázat sorait. A Fulfillment táblában figyelje meg, hogy a rendelési sorokat több szállítmány is teljesíteni tudja. (A rendeléssor hiánya azt jelenti, hogy a rendelés még nem teljesíthető.)

A két tábla soradatait a következő listajeles lista ismerteti:

• A Order táblázat öt sorból áll:
  • OrderDate 2019. január 1.,, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate 2019. január 1., OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate 2019. február 2.,, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate 2019. február 2.,, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate 2019. március 3., OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• A Fulfillment táblázat négy sorból áll:
  • FulfillmentDate 2019. január 1.,, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate 2019. február 2.,, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate 2019. február 2.,, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate 2019. január 1.,, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Nézzük meg, mi történik, amikor lekérdezzük a modellt. Az alábbi táblázatvizualizáció a rendelési és teljesítési mennyiségeket hasonlítja össze a Order tábla OrderID oszlopával.

A vizualizáció pontos eredményt ad. A modell hasznossága azonban korlátozott, mert csak a Order tábla OrderID oszlopa alapján szűrhet vagy csoportosíthat.

Több-a-többhöz tények összekapcsolására vonatkozó útmutató

Általában nem ajánlott két ténytáblát közvetlenül összekapcsolni több-a-többhöz számosság használatával. Ennek fő oka az, hogy a modell nem biztosít rugalmasságot a jelentésvizualizációk szűrésének vagy csoportosításának módjában. A példában a vizualizációk csak a Order tábla OrderID oszlopa alapján szűrhetők vagy csoportosíthatók. Egy másik ok az adatok minőségére vonatkozik. Ha az adatok integritási problémákat tapasztalnak, előfordulhat, hogy a lekérdezés során bizonyos sorok kimaradnak a több ember közötti számosság és korlátozott kapcsolatokmiatt.

A ténytáblák közvetlen összekapcsolása helyett javasoljuk, hogy csillagséma-tervet implementáljon. Ez azt jelenti, hogy dimenziótáblákat ad hozzá. Ennek következtében ezek a dimenziótáblák egy-többhöz kapcsolatokkal kapcsolódnak a ténytáblákhoz. Ez a tervezési megközelítés robusztus, mivel hatékonyan nyújt rugalmas jelentéskészítési lehetőségeket. Lehetővé teszi a dimenziótábla oszlopainak bármelyikével való szűrést vagy csoportosítást, valamint a kapcsolódó ténytáblák oszlopainak összegzését.

Vegyünk egy jobb megoldást.

Figyelje meg a következő tervezési módosításokat:

• A modell négy további táblával rendelkezik: OrderLine, OrderDate, Productés FulfillmentDate.
• A négy további tábla dimenziótábla, ahol az egy-a-többhöz való kapcsolatok a ténytáblákhoz kötik őket.
• A OrderLine tábla tartalmazza a OrderLineID oszlopot, amely a OrderID értékét 100-tal szorozva tárolja, valamint az OrderLine oszlop értékét – az egyes rendeléssorok azonosítóját.
• A Order és Fulfillment táblák mostantól OrderLineID oszlopot tartalmaznak, és már nem tartalmazzák a OrderID és OrderLine oszlopokat.
• A Fulfillment táblázat mostantól OrderDate és ProductID oszlopokat tartalmaz.
• A FulfillmentDate tábla csak a Fulfillment táblához kapcsolódik.
• Minden azonosítóoszlop rejtett.

Időt szánva egy csillagséma megtervezésére és bevezetésére, az alábbi előnyöket nyújtja:

• A jelentés-vizualizációk a dimenziótáblák bármely látható oszlopa alapján szűrhetők vagy csoportosíthatók vagy.
• A jelentésvizualizációk összegezni a ténytáblák látható oszlopait.
• A OrderLine, OrderDatevagy Product táblákra alkalmazott szűrők mindkét ténytáblára is érvényesülnek.
• Minden kapcsolat egy-többhöz viszonyban van, és mindegyik kapcsolat egy normál kapcsolat. Az adatintegritási problémák nem lesznek maszkolva. A kapcsolatértékelésről további információt Modellkapcsolatok a Power BI Desktopbancímű témakörben talál.

Magasabb szintű tények összekapcsolása

Ez a több-a-többhöz forgatókönyv nagyon különbözik a cikkben már ismertetett másik kettőtől.

Vegyünk egy példát, amely négy táblát foglal magában: Date, Sales, Productés Target. A Date és Product táblák dimenziótáblák, és az egy-a-többhöz kapcsolatok mindegyike a Sales ténytáblához kapcsolódik. Eddig jó csillagséma tervezést képvisel. A Target tábla azonban még nem kapcsolódik a többi táblához.

A Target tábla három oszlopot tartalmaz: Category, TargetQuantityés TargetYear. A táblázat soraiban az év és a termékkategória részletessége látható. Más szóval az értékesítési teljesítmény mérésére használt célértékek minden évben az egyes termékkategóriákhoz vannak beállítva.

Mivel a Target tábla magasabb szinten tárolja az adatokat, mint a dimenziótáblák, nem hozható létre egy-többhöz kapcsolat. Nos, ez csak az egyik kapcsolatra igaz. Nézzük meg, hogyan kapcsolódik a Target tábla a dimenziótáblákhoz.

Magasabb szemcsés időszakok összekapcsolása

A Date és a Target táblák közötti kapcsolatnak egy-a-többhöz típusúnak kell lennie. Ennek az az oka, hogy a TargetYear oszlop értékei dátumok. Ebben a példában minden TargetYear oszlop a célév első dátumát tárolja.

Ügyelni kell azonban arra, hogy a hónap- vagy dátumszintű szűrők értelmes eredményt hozhassanak. Speciális számítási logika nélkül a jelentések vizualizációi jelezhetik, hogy a céldátumok szó szerint az év első napjai. Minden más nap – és január kivételével minden hónap – ÜRES értékként összegzi a célmennyiséget.

Az alábbi mátrixvizualizáció bemutatja, mi történik, ha a jelentés felhasználója egy évről hónapra lehatolást hajt végre. A vizualizáció összefoglalja a TargetQuantity oszlopot. (A Adat nélküli elemek megjelenítése lehetőség engedélyezve van a mátrixsorokhoz.)

Ennek a viselkedésnek a elkerülése érdekében javasoljuk, hogy mértékekkel szabályozza a tényadatok összegzését. Az összegzés szabályozásának egyik módja, ha üres értéket ad vissza alacsonyabb szintű időszakok lekérdezésekor. A kifinomult DAX-okkal definiált másik módszer az értékek felosztása alacsonyabb szintű időszakokra.

Vegye figyelembe az alábbi mértékdefiníciót, amely az ISFILTERED DAX-függvényt használja. Csak akkor ad vissza értéket, ha a Date és Month oszlopok nincsenek szűrve.

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

Az alábbi mátrixvizualizáció a Target Quantity mértéket használja. Azt mutatja, hogy az összes havi célmennyiség ÜRES.

Magasabb részletesség (nem dátum) összefüggésbe hozás

Más tervezési megközelítésre van szükség, ha egy dimenziótáblából származó nem dátumoszlopot egy ténytáblához (és a dimenziótáblánál magasabb szemcseméretnél) kell összekapcsolni.

A Category oszlopok (mind a Product, mind a Target táblákból) ismétlődő értékeket tartalmaznak. Tehát egy-a-többhöz kapcsolatnak nincs "egy" oldala. Ebben az esetben létre kell hoznia egy több-a-többhöz kapcsolatot. A kapcsolatnak egyetlen irányban kell propagálja a szűrőket a dimenziótáblától a ténytábláig.

Most nézzük meg a táblázat sorait.

A Target táblázatban négy sor található: két sor minden célévhez (2019 és 2020), valamint két kategória (Ruházat és kiegészítők). A Product táblázatban három termék található. Kettő a ruházati kategóriába tartozik, az egyik a kiegészítők kategóriájába tartozik. Az egyik ruhaszín zöld, a többi kettő kék.

A Product tábla Category oszlopa szerint csoportosított táblavizualizáció a következő eredményt hozza létre. Ez a vizualizáció azonban a megfelelő eredményt hozza létre. Tekintsük át, mi történik, ha a Product tábla Color oszlopa a célmennyiség csoportosítására szolgál.

A vizualizáció tévesen jeleníti meg az adatokat. Mi történik itt?

A Product tábla Color oszlopának szűrője két sort eredményez. Az egyik sor a Ruházat kategóriára, a másik pedig a Kellékek kategóriára tartozik. Ezt a két kategóriaértéket a rendszer szűrőkként propagálja a Target táblába. Más szóval, mivel a kék színt két kategóriából származó termékek használják, ezek a kategóriák a célok szűrésére szolgálnak.

A korábban ismertetett viselkedés elkerülése érdekében javasoljuk, hogy mértékekkel szabályozza a tényadatok összegzését.

Vegye figyelembe a következő mértékdefiníciót. Vegye észre, hogy az összes Product táblaoszlop, amely a kategóriaszint alatt található, szűrők alapján kerül tesztelésre.

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

Az alábbi táblázatvizualizáció a Target Quantity mértéket használja. Azt mutatja, hogy az összes szín célmennyisége ÜRES.

A végső modellterv a következőhöz hasonlóan néz ki.

Magasabb szintű tények összekapcsolásának útmutatója

Ha egy dimenziótáblát egy ténytáblához kell kapcsolnia, és a ténytábla a dimenziótábla sorainál magasabb szemcsés sorokban tárolja a sorokat, kövesse az alábbi útmutatást:

• Magasabb gabonaadat-dátumok
  • A ténytáblában tárolja az időszak első dátumát.
  • Hozzon létre egy-a-többhöz kapcsolatot a dátumtábla és a ténytábla között.
• Egyéb magasabb szintű tények
  • Hozzon létre egy több-több közötti kapcsolatot a dimenziótábla és a ténytábla között.
• Mindkét típushoz
  • Mértéklogikával végzett összegzés vezérlése – üres értéket ad vissza, ha az alacsonyabb szintű dimenzióoszlopok szűrésére vagy csoportosítására szolgálnak.
  • Összegezhető ténytáblaoszlopok elrejtése – így csak mértékekkel lehet összegezni a ténytáblát.

Kapcsolódó tartalom

A cikkhez kapcsolódó további információkért tekintse meg a következő forrásokat:

• Modellkapcsolatok a Power BI Desktopban
• Értsd meg a csillagsémát és annak fontosságát a Power BI esetében
• Kapcsolat hibaelhárítási útmutatója
• Kérdések? Próbálja megkérdezni a Fabric Community-t
• Javaslatok? Ötletek hozzáadása a Háló fejlesztéséhez