Több-a-többhöz kapcsolati útmutató
Ez a cikk a Power BI Desktopban dolgozó adatmodellezőkre irányul. Három különböző több-a-többhöz modellezési forgatókönyvet ír le. Útmutatást is nyújt a modellekben való sikeres tervezéshez.
Feljegyzés
Ebben a cikkben nem foglalkozunk a modellkapcsolatok bemutatásával. Ha nem ismeri teljesen a kapcsolatokat, azok tulajdonságait vagy konfigurálását, javasoljuk, hogy először olvassa el a Modellkapcsolatok című cikket a Power BI Desktopban .
Az is fontos, hogy tisztában legyen a csillagséma kialakításával. További információ: A csillagséma és a Power BI fontossága.
Valójában három több-a-többhöz forgatókönyv létezik. Ezek a következő esetekben fordulhatnak elő:
- Két dimenzió típusú tábla összekapcsolése
- Két tény típusú tábla összekapcsolás
- Magasabb szemcsés tény típusú táblák összekapcsolása, amikor a tény típusú tábla a dimenzió típusú tábla sorainál magasabb szemcseméretű sorokban tárolja a sorokat
Feljegyzés
A Power BI natív módon támogatja a több-a-többhöz kapcsolatokat. További információ: Több-többhöz kapcsolatok alkalmazása a Power BI Desktopban.
Több-a-többhöz dimenziók összekapcsolása
Tekintsük át az első több-a-többhöz forgatókönyvtípust egy példával. A klasszikus forgatókönyv két entitást érint: a banki ügyfeleket és a 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ók több ügyfélből áll, gyakran közös fióktulajdonosoknak nevezzük őket.
Ezeknek az entitásoknak a modellezése egyenesen előre halad. Egy dimenzió típusú tábla tárolja a fiókokat, egy másik dimenzió típusú tábla pedig az ügyfeleket. Ahogy a dimenzió típusú táblákra jellemző, minden táblában van egy azonosító oszlop. 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 áthidaló táblának nevezik. 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, akkor tény nélküli ténytáblának nevezzük.
Íme egy egyszerű modelldiagram a három tá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 Ügyfél, é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 Tranzakció nevű tény típusú tá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.
Feljegyzés
A Táblázatsorok nem jeleníthetők meg a Power BI Desktop modelldiagramján. Ebben a cikkben egyértelmű példákkal támogatjuk a vitát.
A négy tábla soradatait a következő listajeles lista ismerteti:
- A Fiók tábla két sorból áll:
- Az 1. fiókazonosító az Account-01-hez tartozik
- A 2. fiókazonosító a 02-s fiókhoz tartozik
- Az Ügyfél táblának két sora van:
- A CustomerID 91 a Customer-91-hez készült
- A CustomerID 92 a Customer-92-hez készült
- Az AccountCustomer tábla három sorból áll:
- Az 1. fiókazonosító a CustomerID 91 azonosítóhoz van társítva
- Az 1. fiókazonosító a CustomerID 92 azonosítóhoz van társítva
- A 2. fiókazonosító a CustomerID 92 azonosítóhoz van társítva
- A Transaction tábla három sorból áll:
- Dátum: 2019. január 1., 1. számlaazonosító, 100. összeg
- Dátum: 2019. február 2., 2. számlaazonosító, 200. összeg
- Dátum : 2019. március 3., 1. számlaazonosító , összeg –25
Nézzük meg, mi történik a modell lekérdezésekor.
Az alábbiakban két vizualizáció foglalja össze a Tranzakció tábla Összeg oszlopát. Az első vizualizációk fiók szerint csoportosítva, így az Összeg oszlopok összege a számlaegyenleget jelöli. A második vizualizációs csoport ügyfél szerint, így az Összeg oszlopok összege az ügyfélegyenleget jelöli.
Az első vizualizáció címe Fiókegyenleg, és két oszlopból áll: Fiók és Összeg. A következő eredményt jeleníti meg:
- A 01. számla egyenlege 75
- A 02. számla egyenlege 200
- Az összeg 275
A második vizualizáció címe Ügyfélegyenleg, és két oszlopból áll: Ügyfél és Összeg. A következő eredményt jeleníti meg:
- Ügyfél-91 egyenleg összege 275
- Ügyfél-92 egyenleg összege 275
- Az összeg 275
A táblasorok és az Account Balance vizualizáció gyors áttekintése megmutatja, hogy az eredmény helyes az egyes fiókokhoz és a teljes összeghez. Ennek az az oka, hogy minden fiók csoportosítása szűrőpropagálást eredményez az adott fiók Tranzakció táblájába.
A Customer Balance vizualizációval azonban valami nem tűnik helyesnek. Az Ügyfélegyenleg vizualizáció minden ügyfele ugyanazt az egyenleget használja, mint a teljes egyenleg. Ez az eredmény csak akkor lehet helyes, ha minden ügyfél minden fiók közös fiókja volt. Ebben a példában nem ez a helyzet. A probléma a szűrés propagálásával kapcsolatos. Nem halad végig a Tranzakció tábláig.
Kövesse az Ügyfél táblától a Tranzakció tábláig a kapcsolatszűrő irányait. Nyilvánvalónak kell lennie, hogy az Account és az AccountCustomer tábla közötti kapcsolat rossz irányba propagálásra kerül. Ennek a kapcsolatnak a szűrési irányát Mindkettő értékre kell állítani.
A vártnak megfelelően nem történt változás az Account Balance vizualizációban.
A Customer Balance vizualizációk azonban most a következő eredményt jelenítik meg:
- Ügyfél-91 egyenleg összege 75
- Ügyfél-92 egyenleg összege 275
- Az összeg 275
Az Ügyfélegyenleg vizualizáció most már helyes eredményt jelenít meg. Kövesse saját maga számára a szűrési utasításokat, és nézze meg, hogyan lettek kiszámítva az ügyfélegyenlegek. Azt is meg kell értenie, hogy a vizualizáció végösszege az összes ügyfelet jelenti.
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 a 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ívak.
Több-a-többhöz dimenziók összekapcsolása – útmutató
Ha több-a-többhöz kapcsolat áll fenn a dimenzió típusú táblák között, a következő útmutatást nyújtjuk:
- Adja hozzá az egyes több-a-többhöz kapcsolódó entitásokat modelltáblaként, biztosítva, hogy egyedi azonosító (ID) oszlopa legyen
- Áthidaló tábla hozzáadása a társított entitások tárolásához
- Egy-a-többhöz kapcsolatok létrehozása a három tábla között
- Konfiguráljon egy kétirányú kapcsolatot, hogy a szűrőpropagálás továbbhaladhasson a tény típusú táblákra
- Ha nem megfelelő hiányzó azonosítóértékekkel rendelkezni, állítsa az azonosítóoszlopok Null értékű tulajdonságát FAL értékre Standard kiadás – az adatfrissítés sikertelen lesz, ha a hiányzó értékek forrásul szolgálnak
- Rejtse el az áthidaló táblát (kivéve, ha a jelentéskészítéshez szükséges további oszlopokat vagy mértékeket tartalmaz)
- A jelentéskészítésre nem alkalmas azonosítóoszlopok elrejtése (például ha az azonosítók helyettesítő kulcsok)
- Ha érdemes láthatóvá tenni egy azonosítóoszlopot, győződjön meg arról, hogy a kapcsolat "egy" diáján van – mindig rejtse el a "több" oldaloszlopot. Ez a legjobb szűrőteljesítményt eredményezi.
- A félreértések és a félreértelmezés elkerülése érdekében közölje a jelentés felhasználóival a magyarázatokat – leírásokat adhat hozzá szövegdobozokkal vagy vizualizációfejléc-elemleírásokkal
Nem javasoljuk, hogy közvetlenül kapcsolja össze a több-a-többhöz dimenzió típusú táblákat. Ehhez a tervezési megközelítéshez több-a-többhöz számosságú kapcsolatot kell konfigurálni. Elméletileg megvalósítható, de azt jelenti, hogy a kapcsolódó oszlopok ismétlődő értékeket tartalmaznak. Ez egy jól elfogadott tervezési gyakorlat, azonban a dimenzió típusú táblák azonosító oszlopot tartalmaznak. A dimenzió típusú tábláknak mindig az AZONOSÍTÓ oszlopot kell használniuk a kapcsolat "egy" oldalaként.
Több-a-többhöz tények összekapcsolás
A második több-a-többhöz típusú forgatókönyv két tény típusú táblát tartalmaz. Két tény típusú tábla közvetlenül kapcsolható össze. 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ény típusú táblát tartalmaz: Megrendelés és teljesítés. A Rendelés tábla rendeléssoronként egy sort tartalmaz, a Fulfillment tábla pedig nulla vagy több sort tartalmazhat rendeléssoronként. A Rendelés tábla sorai értékesítési rendeléseket jelölnek. A Fulfillment tábla sorai a kiszállított rendeléselemeket jelölik. A több-a-többhöz kapcsolat a két OrderID oszlopot kapcsolja össze, és a szűrő propagálása csak a Rendelés táblából (RendelésszűrőkTeljesítése).
A kapcsolat számossága több-a-többhöz beállítással támogatja a duplikált OrderID-értékek tárolását mindkét táblában. A Rendelés táblában ismétlődő OrderID-értékek létezhetnek, mert egy rendelés több sorból áll. A Fulfillment táblában ismétlődő OrderID-értékek létezhetnek, mert a rendelések több sorból állhatnak, a rendeléssorokat pedig számos szállítmány teljesítheti.
Most nézzük meg a táblázat sorait. A Fulfillment táblában figyelje meg, hogy a rendeléssorokat 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 Rendelés tábla öt sorból áll:
- OrderDate 2019. január 1., OrderID 1, OrderLine 1, ProductID Prod-A, OrderQuantity 5, Sales 50
- OrderDate 2019. január 1., OrderID 1, OrderLine 2, ProductID Prod-B, OrderQuantity 10, Sales 80
- OrderDate 2019. február 2., OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Sales 40
- OrderDate 2019. február 2., OrderID 2, OrderLine 2, ProductID Prod-C, OrderQuantity 1, Sales 20
- OrderDate 2019. március 3., OrderID 3, OrderLine 1, ProductID Prod-C, OrderQuantity 5, Sales 100
- A Fulfillment tábla négy sorból áll:
- FulfillmentDate 2019. január 1., FulfillmentID 50, OrderID 1, OrderLine 1, FulfillmentQuantity 2
- FulfillmentDate 2019. február 2., FulfillmentID 51, OrderID 2, OrderLine 1, FulfillmentQuantity 5
- FulfillmentDate 2019. február 2., FulfillmentID 52, OrderID 1, OrderLine 1, FulfillmentQuantity 3
- FulfillmentDate 2019. január 1., FulfillmentID 53, OrderID 1, OrderLine 2, FulfillmentQuantity 10
Nézzük meg, mi történik a modell lekérdezésekor. Az alábbi táblázatvizualizáció a rendelési és teljesítési mennyiségeket hasonlítja össze az Order tábla OrderID oszlopával.
A vizualizáció pontos eredményt ad. A modell hasznossága azonban korlátozott – csak az 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ény típusú táblát közvetlenül több-a-többhöz számosság használatával összekapcsolni. Ennek fő oka az, hogy a modell nem biztosít rugalmasságot a vizualizációk szűrésének vagy csoportosításának módjaiban. A példában a vizualizációk csak az Order tábla OrderID oszlopa alapján szűrhetők vagy csoportosíthatók. További ok az adatok minőségére vonatkozik. Ha az adatok integritási problémái vannak, előfordulhat, hogy a korlátozott kapcsolat természete miatt bizonyos sorokat kihagynak a lekérdezés során. További információ: Modellkapcsolatok a Power BI Desktopban (Kapcsolatértékelés).
A tény típusú táblák közvetlen összekapcsolása helyett javasoljuk, hogy fogadja el a Csillagséma tervezési alapelveit. Ezt dimenzió típusú táblák hozzáadásával teheti meg. A dimenzió típusú táblák ezután egy-a-többhöz kapcsolatok használatával kapcsolódnak a tény típusú táblákhoz. Ez a tervezési megközelítés robusztus, mivel rugalmas jelentéskészítési lehetőségeket biztosít. Lehetővé teszi a dimenzió típusú oszlopok bármelyikének szűrését vagy csoportosítását, valamint a kapcsolódó tény típusú táblák ö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 mind dimenzió típusú táblák, és az egy-a-többhöz kapcsolatok ezeket a táblákat a tény típusú táblákhoz kapcsolják
- Az OrderLine tábla egy OrderLineID oszlopot tartalmaz, amely az OrderID értéket 100-tal megszorozva, valamint az OrderLine értéket tartalmazza – az egyes rendeléssorok egyedi azonosítóját
- A Rendelési és teljesítési táblák mostantól egy OrderLineID oszlopot tartalmaznak, és már nem tartalmazzák az OrderID és az OrderLine oszlopokat
- A Fulfillment tábla mostantól OrderDate és ProductID oszlopokat tartalmaz
- A FulfillmentDate tábla csak a Fulfillment táblához kapcsolódik
- Minden egyedi azonosítóoszlop rejtett
A csillagséma tervezési alapelveinek alkalmazása az alábbi előnyöket biztosítja:
- A jelentésvizualizációk a dimenzió típusú táblák bármely látható oszlopa alapján szűrhetnek vagy csoportosíthatnak
- A jelentésvizualizációk összesíthetik a tény típusú táblák látható oszlopait
- Az OrderLine, OrderDate vagy Product táblákra alkalmazott szűrők mindkét tény típusú táblába propagálhatók
- Minden kapcsolat egy-a-többhöz, és minden kapcsolat egy normál kapcsolat. Az adatintegritási problémák nem lesznek maszkolva. További információ: Modellkapcsolatok a Power BI Desktopban (Kapcsolatértékelés).
Magasabb szemcsés tények összekapcsolás
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: Dátum, Értékesítés, Termék és Cél. A Dátum és a Termék dimenzió típusú táblák, és az egy-a-többhöz kapcsolatok mindegyike az Értékesítési tény típusú táblához kapcsolódik. Eddig jó csillagséma-kialakítást jelent. A Céltábla azonban még nem kapcsolódik a többi táblához.
A Céltábla három oszlopot tartalmaz: Kategória, 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 Céltábla a dimenzió típusú tábláknál magasabb szinten tárolja az adatokat, nem hozható létre egy-a-többhöz kapcsolat. Nos, ez csak az egyik kapcsolatra igaz. Vizsgáljuk meg, hogyan kapcsolódik a Céltábla a dimenzió típusú táblákhoz.
Magasabb szemcsés időszakok összekapcsolása
A Dátum és a Cél tábla közötti kapcsolatnak egy-a-többhöz kapcsolatnak kell lennie. Ennek az az oka, hogy a TargetYear oszlop értékei dátumok. Ebben a példában minden TargetYear oszlop értéke a célév első dátuma.
Tipp.
Ha a tényeket a napnál nagyobb részletességgel tárolja, állítsa az oszlop adattípusát Dátumra (vagy egész számra , ha dátumkulcsokat használ). Az oszlopban tároljon egy értéket, amely az időszak első napját jelöli. Az évidőszakot például az év január 1-jei értékével rögzíti a rendszer, a hónap pedig az adott hónap első napjaként.
Ü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ésvizualizációk jelenthetik, hogy a céldátumok szó szerint az év első napja. 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 részletez. A vizualizáció a TargetQuantity oszlopot összegzi. (A Adatbeállítás nélküli elemek megjelenítése 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 a következő mértékdefiníciót, amely az ISFILTERED DAX függvényt használja. Csak akkor ad vissza értéket, ha a Dátum vagy a Hónap oszlop nincs szűrve.
Target Quantity =
IF(
NOT ISFILTERED('Date'[Date])
&& NOT ISFILTERED('Date'[Month]),
SUM(Target[TargetQuantity])
)
Az alábbi mátrixvizualizáció most a Célmennyiség mértéket használja. Azt mutatja, hogy az összes havi célmennyiség ÜRES.
Magasabb szemcse (nem dátum) összekapcsolás
Más tervezési megközelítésre van szükség, ha egy dimenzió típusú táblából egy tény típusú táblához nem dátum típusú oszlopot kell összekapcsolni (és nagyobb szemcseméretű, mint a dimenzió típusú tábla).
A Kategória oszlopok (a Termék és a Cél táblákból is) duplikált értékeket tartalmaznak. Tehát nincs "egy" az egy-a-többhöz kapcsolathoz. 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ípusú táblától a tény típusú tábláig.
Most nézzük meg a táblázat sorait.
A Céltáblában 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 Termék táblában 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 Termék tábla Kategória oszlopa szerint csoportosított táblavizualizáció a következő eredményt hozza létre.
Ez a vizualizáció a megfelelő eredményt hozza létre. Most nézzük meg, mi történik, ha a Termék tábla Szín 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 Termék tábla Szín 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 Céltá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. Figyelje meg, hogy a kategóriaszint alatt található összes terméktáblaoszlop szűrőinek tesztelése.
Target Quantity =
IF(
NOT ISFILTERED('Product'[ProductID])
&& NOT ISFILTERED('Product'[Product])
&& NOT ISFILTERED('Product'[Color]),
SUM(Target[TargetQuantity])
)
Az alábbi táblavizualizáció most a Célmennyiség 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ípusú táblát egy tény típusú táblához kell kapcsolnia, és a tény típusú tábla a dimenzió típusú tábla sorainál nagyobb szemcsében tárolja a sorokat, az alábbi útmutatást nyújtjuk:
- Magasabb szemcsés ténydátumok esetén:
- A tény típusú táblában tárolja az időszak első dátumát
- Egy-a-többhöz kapcsolat létrehozása a dátumtábla és a tény típusú tábla között
- Egyéb magasabb szemű tények esetén:
- Több-a-többhöz kapcsolat létrehozása a dimenzió típusú tábla és a tény típusú tábla között
- Mindkét típus esetén:
- Mértéklogikával végzett összegzés vezérlése – üres értéket ad vissza, ha az alacsonyabb szintű dimenzió típusú oszlopok szűrésére vagy csoportosítására szolgál
- Összegző tény típusú táblaoszlopok elrejtése – így csak mértékekkel lehet összegezni a tény típusú táblázatot
Kapcsolódó tartalom
A cikkhez kapcsolódó további információkért tekintse meg a következő forrásokat:
Visszajelzés
Hamarosan elérhető: 2024-ben fokozatosan kivezetjük a GitHub-problémákat a tartalom visszajelzési mechanizmusaként, és lecseréljük egy új visszajelzési rendszerre. További információ: https://aka.ms/ContentUserFeedback.
Visszajelzés küldése és megtekintése a következőhöz: