Az adatelemzéseket hatékonyabbá teheti, ha kapcsolatokat hoz létre a különböző táblák között. A kapcsolat két adatokat tartalmazó tábla közötti összeköttetés: mindkét táblázat egy-egy oszlopa a kapcsolat alapja. A kapcsolatok hasznosságának megértéséhez képzelje el, hogy vállalkozásában az ügyfelek megrendeléseire vonatkozó adatokat kell nyomon követnie. Megtehetné azt is, hogy az összes adatot egy, a következő felépítésű táblázatban követi nyomon:
| Ügyfél-azonosító | Név | Kedvezmény | Rendelésazonosító | RendelésDátuma | Termék | Mennyiség | |
|---|---|---|---|---|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com | .05 | 256 | 2010-01-07 | Compact Digital | 11 |
| 1 | Ashton | chris.ashton@contoso.com | .05 | 255 | 2010-01-03 | SLR Camera | 15 |
| 2 | Jaworski | michal.jaworski@contoso.com | .10 | 254 | 2010-01-03 | Budget Movie-Maker | 27 |
Ez a megközelítés működőképes lehet, de nagyon sok felesleges adat tárolásával jár, például minden rendelésnél szerepel a vevő e-mail címe. Az adattárolás olcsó, de ügyelnie kell arra, hogy az adott vevőre vonatkozóan az összes sort frissítse, ha megváltozik a vevő e-mail címe. A probléma egyik megoldása, ha az adatokat több táblázatra osztja fel, és a táblázatok között kapcsolatokat ad meg. Ezt a megközelítést alkalmazzák a relációs adatbázisok, mint például az SQL Server. Az importált adatbázis például a rendelési adatokat akár három kapcsolódó táblában képezheti le:
Ügyfelek
| [Ügyfél-azonosító] | Név | Levelezés |
|---|---|---|
| 1 | Ashton | chris.ashton@contoso.com |
| 2 | Jaworski | michal.jaworski@contoso.com |
Ügyfélkedvezmények
| [Ügyfél-azonosító] | Kedvezmény |
|---|---|
| 1 | ,05 |
| 2 | ,10 |
Rendelések
| [Ügyfél-azonosító] | Rendelésazonosító | RendelésDátuma | Termék | Mennyiség |
|---|---|---|---|---|
| 1 | 256 | 2010-01-07 | Compact Digital | 11 |
| 1 | 255 | 2010-01-03 | SLR Camera | 15 |
| 2 | 254 | 2010-01-03 | Budget Movie-Maker | 27 |
A kapcsolatok léteznek az adatmodellen belül – például egy olyanban, amelyet explicit módon hoz létre, vagy olyanban, amelyet az Excel automatikusan hoz létre Ön helyett több tábla párhuzamos importálásakor. A Power Pivot beépülő modult is használhatja a modell létrehozására vagy kezelésére. További információ az Adatmodell létrehozása az Excel programban című témakörben olvasható.
Ha a Power Pivot modult használja táblák importálására ugyanabból az adatbázisból, a Power Pivot képes észlelni a táblák közötti kapcsolatokat a [szögletes zárójelek] között lévő oszlopok alapján, és ezeket a kapcsolatokat reprodukálni tudja egy háttérben összeállított adatmodellben. További információt a cikk későbbi, Kapcsolatok automatikus észlelése és származtatása című szakaszában olvashat. Ha több forrásból importál táblákat, a kapcsolatokat létrehozhatja kézzel a következő témakörben leírtak szerint: Kapcsolat létrehozása két táblázat között.
Oszlopok és kulcsok
A kapcsolatok olyan oszlopokon alapulnak, amelyek mindegyik táblában ugyanazokat az adatokat tartalmazzák. Kapcsolhat például egy Vevők táblát a Rendelések táblához, ha mindkettő tartalmaz egy ügyfél-azonosítót tároló oszlopot. A példában az oszlopnevek azonosak, de ez nem követelmény. Egyikük lehetne CustomerID (Ügyfél-azonosító), míg a másik CustomerNumber (Ügyfélszám), mindaddig, amíg az Orders (Rendelések) tábla olyan azonosítót tartalmaz, amely a Customers (Ügyfelek) táblában is tárolva van.
A relációs adatbázisokban többféle típusú kulcs található. A kulcsok általában speciális tulajdonságokkal rendelkező oszlopok. Az egyes kulcsok szerepének megértése segíthet egy olyan, több táblázatot tartalmazó adatmodell kezelésében, amely adatokat szolgáltat kimutatásokhoz, kimutatásdiagramokhoz vagy Power View nézetet tartalmazó jelentésekhez.
Bár sokféle típusú kulcs létezik, ezek a legfontosabbak a cél szempontjából:
- Elsődleges kulcs: egyértelműen azonosítja egy tábla egyik sorát, ilyen például a CustomerID (Ügyfél-azonosító) oszlop a Customers (Ügyfelek) táblában.
- Másodlagos kulcs (avagy keresési kulcs): egyedi, az elsődleges kulcstól eltérő oszlop. Például egy Employees (Alkalmazottak) tábla tárolhat egy alkalmazotti azonosítót és egy társadalombiztosítási számot, amelyek egyaránt egyedi azonosítók.
- Külső kulcs: egy másik tábla egyedi oszlopára hivatkozó oszlop, ilyen például az Orders (Rendelések) tábla CustomerID (Ügyfél-azonosító) oszlopa, amely a Customers (Ügyfelek) tábla CustomerID (Ügyfél-azonosító) oszlopára utal.
Az adatmodellekben az elsődleges vagy a másodlagos kulcs az úgynevezett kapcsolódó oszlop. Amennyiben egy táblázat elsődleges és másodlagos kulcsot is tartalmaz, bármelyik használható táblázatkapcsolat alapjául. A külső kulcs nevezhető forrásoszlopnak vagy egyszerűen csak oszlopnak. A példában kapcsolat lenne megadva az Orders (Rendelések) táblában szereplő CustomerID (Ügyfél-azonosító) oszlop (az oszlop) és a Customers (Ügyfelek) tábla CustomerID (Ügyfél-azonosító) oszlopa (a keresési oszlop) között. Amennyiben relációs adatbázisból importál adatokat, az Excel alapértelmezés szerint az egyik táblából kiválasztja a külső kulcsot, a másik táblából pedig a hozzá tartozó elsődleges kulcsot. Bármely olyan oszlop használható azonban keresési oszlopként, amely egyedi értékeket tartalmaz.
A kapcsolatok típusai
Egy ügyfél és egy megrendelés közötti kapcsolat egy-a-többhöz kapcsolat. Mindegyik ügyfélnek több rendelése is lehet, de egy rendeléshez nem tartozhat több ügyfél. Egy másik fontos táblakapcsolat az egy-az-egyhez kapcsolat. Az itt szereplő példában a CustomerDiscounts (Ügyfélengedmények ) tábla, amely minden ügyfél számára külön-egy engedmény mértékét határozza meg, egy-az-egyhez kapcsolatban áll a Customers (Ügyfelek) táblával.
Az alábbi táblázat a három tábla (Customers, CustomerDiscounts és Orders) közötti kapcsolatokat mutatja be:
| Kapcsolat | Típus: | Keresőoszlop | Oszlop |
|---|---|---|---|
| Customers-CustomerDiscounts | „egy az egyhez” | Customers.CustomerID | CustomerDiscounts.CustomerID |
| Customers-Orders | „egy a többhöz” | Customers.CustomerID | Orders.CustomerID |
Megjegyzés
A „több a többhöz” kapcsolatok adatmodellekben nem támogatottak. A „több a többhöz” típusú kapcsolatra példa a Products (Termékek) és a Customers (Ügyfelek) tábla közötti közvetlen kapcsolat, vagyis amikor egy ügyfél több terméket vásárolhat, míg ugyanazt a terméket több ügyfél is megvásárolhatja.
A kapcsolatok és a teljesítmény
Egy kapcsolat létrehozása után az Excel programnak általában újra kell számítania az újonnan létrehozott kapcsolatban szereplő táblázatok oszlopait használó összes képletet. Az adatmennyiségtől és a kapcsolatok bonyolultságától függően az adatok feldolgozása eltarthat egy ideig. További információt a Képletek újraszámítása című témakörben talál.
Több kapcsolat a táblázatok között
Egy adatmodellben több kapcsolat is lehetséges két táblázat között. A pontos számítások létrehozásához az Excelnek egyetlen útvonalra van szüksége az egyik táblázatból a következőbe. Ezért egy-egy táblapár között egy időben csak az egyik kapcsolat aktív. Bár a többi kapcsolat inaktív, a képletekben és a lekérdezésekben megadhat inaktív kapcsolatot.
Diagram nézetben az aktív kapcsolatot folytonos vonal, az inaktív kapcsolatot pedig szaggatott vonal jelöli. Például az AdventureWorksDW2012 adatbázisban a DimDate tábla tartalmaz egy DateKey nevű oszlopot, amely a FactInternetSales tábla három különböző oszlopához ( OrderDate, DueDate és ShipDate) kapcsolódik. Ha a DateKey és az OrderDate oszlop közötti kapcsolat aktív, akkor a képletekben ez lesz az alapértelmezett kapcsolat, amíg nem ad meg mást.
A táblázatkapcsolatokra vonatkozó követelmények
Akkor hozható létre kapcsolat, ha teljesülnek az alábbi követelmények:
| Feltételek | Leírás: |
|---|---|
| Az egyes táblák egyedi azonosítója | Minden táblázatnak rendelkeznie kell egy oszloppal, amely egyedileg azonosítja a táblázat egyes sorait. Ezt az oszlopot gyakran elsődleges kulcsnak hívják. |
| Egyedi keresőoszlopok | A keresési oszlopban szereplő adatértékeknek egyedieknek kell lenniük. Más megfogalmazásban: az oszlop nem tartalmazhat ismétlődő értékeket. Az adatmodellekben a null értékek és az üres karakterláncok egyenértékűek az üres értékkel, amely egy önálló adatérték. Ez azt jelenti, hogy a keresési oszlop nem tartalmazhat több null értéket. |
| Kompatibilis adattípusok | A forrásoszlop és a keresési oszlop adattípusainak kompatibilisnek kell lenniük egymással. Az adattípusokról további információt az Adatmodellekben támogatott adattípusok című témakörben talál. |
Nem támogatott adatbázis-szolgáltatások az Excel-adatmodellben
Egy adatmodellben nem hozható létre táblázatkapcsolat, ha a kulcs összetett kulcs. Emellett kizárólag „egy az egyhez” és „egy a többhöz” kapcsolat hozható létre. A többi kapcsolattípus nem támogatott.
Az összetett kulcsok és a keresési oszlopok
Az összetett kulcs egynél több oszlopból álló kulcs. Az adatmodellekben nem használhatók összetett kulcsok: egy táblázatnak pontosan egy olyan oszloppal kell rendelkeznie, amely egyedileg azonosítja a táblázat minden egyes sorát. Ha olyan táblákat importál, amelyek összetett kulcson alapuló létező kapcsolattal rendelkeznek, a Power Pivot Tábla importálása varázslója figyelmen kívül hagyja ezt a kapcsolatot, mert az nem hozható létre a modellben.
Ha két tábla között kézzel kíván kapcsolatot létrehozni, és több oszlop határozza meg az elsődleges és a külső kulcsokat, a kapcsolat létrehozása előtt az értékek egyesítésével egyetlen kulcsoszlopot kell létrehoznia. Ezt megteheti még az adatok importálása előtt, illetve oly módon, hogy a Power Pivot beépülő modul használatával létrehoz egy számított oszlopot az adatmodellben.
„Több a többhöz” kapcsolatok
Az adatmodellekben nem szerepelhetnek „több a többhöz” kapcsolatok. Amodellben nem vehet fel kapcsolótáblákat . DAX-függvényekkel azonban lehetséges a „több a többhöz” kapcsolatok modellezése.
Önillesztések és hurkok
Az önillesztés nem engedélyezett az adatmodellekben. Az önillesztés egy rekurzív kapcsolat egy tábla és saját maga között. Az önillesztések gyakran a szülő-gyermek hierarchiák meghatározásához kerülnek felhasználásra. Például az alkalmazottakat tartalmazó tábla önillesztésével olyan hierarchia hozható létre, amely mutatja a vállalkozás vezetési struktúráját.
Az Excel nem engedélyezi a kapcsolati hurkok létrehozását a munkafüzetekben. Más szóval a következő kapcsolatkészlet tiltott.
1. tábla, "a" oszlop – 2. tábla, "f" oszlop
2. tábla, "f" oszlop – 3. tábla, "n" oszlop
3. tábla, "n" oszlop – 1. tábla, "a" oszlop
Ha olyan kapcsolatot próbál létrehozni, amely hurkot eredményezne, a program hibát jelez.
Kapcsolatok automatikus észlelése és származtatása a PowerPivot programban
Az adatoknak a Power Pivot bővítménnyel való importálásának egyik előnye, hogy a Power Pivot néha képes kapcsolatok észlelésére és új kapcsolatok létrehozására az Excelben létrehozott adatmodellben.
Több tábla importálásakor a Power Pivot automatikusan észleli a táblák közötti kapcsolatokat. Emellett amikor kimutatást hoz létre, a Power Pivot elemzi a táblákban található adatokat. Észleli azokat a lehetséges kapcsolatokat, amelyek nincsenek meghatározva, valamint javaslatot tesz az ezekben a kapcsolatokban használható, megfelelő oszlopokra.
Az észlelési algoritmus az oszlopok értékeire és metaadataira vonatkozó statisztikai adatok használatával állapít meg feltételezéseket a kapcsolatok valószínűségére vonatkozóan.
- Az összes kapcsolódó oszlopban található adatoknak kompatibilisnek kell lenniük egymással. Az automatikus észlelésnél csak az egész szám és a szöveges adattípusok támogatottak. Az adattípusok részletes ismertetését lásd: Az adatmodellekben támogatott adattípusok.
- Egy kapcsolat sikeres észleléséhez az szükséges, hogy a keresési oszlopban található egyedi kulcsok száma nagyobb legyen a „több” oldalon álló táblázat értékeinek számánál. Más szóval, a kapcsolat „több” oldalán álló kulcsoszlopnak nem szabad olyan értéket tartalmaznia, amely nem szerepel a keresési tábla kulcsoszlopában. Tegyük fel például, hogy van egy, a termékeket azonosítójukkal együtt tartalmazó tábla (a keresési tábla), valamint egy értékesítési tábla, amely felsorolja az értékesítési forgalmat minden egyes termékhez (a kapcsolat „több” oldala). Ha az értékesítési rekordok egy olyan termék azonosítóját is tartalmazzák, amelynek megfelelő azonosító nem található a termékek táblájában, akkor a kapcsolat nem hozható létre automatikusan, de kézzel esetleg létrehozhatja. Ahhoz, hogy az Excel észlelje a kapcsolatot, először frissítenie kell a termékek keresési tábláját, hogy az tartalmazza a hiányzó termékek azonosítóját.
- Győződjön meg arról, hogy a "több" oldalon lévő kulcsoszlop neve megegyezik a keresési tábla kulcsoszlopának nevével. A neveknek nem kell teljesen azonosnak lenniük. Üzleti környezetben például gyakran vannak olyan változatok az oszlopnevekhez, amelyek lényegében ugyanazokat az adatokat tartalmazzák: Emp ID, EmployeeID, Employee ID, EMP_ID stb. Az algoritmus észleli a hasonló neveket, és nagyobb valószínűséget rendel a hasonló vagy pontosan megegyező nevű oszlopokhoz. Ezért a kapcsolat létrehozásának valószínűségét úgy célszerű megpróbálkozni, hogy az importált adatok oszlopait átnevezi a meglévő táblák oszlopaihoz hasonlóra. Ha az Excel több lehetséges kapcsolatot talál, nem hoz létre kapcsolatot.
Ez a tájékoztatás segíthet annak megértésében, hogy miért nem észlel a program minden kapcsolatot, illetve hogy a metaadatok – például a mezőnév és az adattípusok – megváltoztatása hogyan javíthatja a kapcsolatok automatikus észlelésének hatékonyságát. További ismertetést a következő témakörben talál: Kapcsolatok hibaelhárítása.
Elnevezett készletek automatikus észlelése
A program nem észleli automatikusan az elnevezett készletek és egy kimutatásban szereplő kapcsolódó mezők közötti kapcsolatokat. Ezek a kapcsolatok manuálisan állíthatók fel. Ha az automatikus kapcsolatészlelést szeretné használni, távolítsa el az elnevezett készleteket, és vegye fel az elnevezett készlet egyes mezőit közvetlenül a kimutatásba.
Kapcsolatok származtatása
Bizonyos esetekben a táblák közötti kapcsolatok automatikusan láncolódnak. Ez azt jelenti, hogy ha például létrehoz egy kapcsolatot az alábbi első két táblakészlet között, a másik két tábla között a program feltételezi a kapcsolat meglétét, és automatikusan létrejön egy kapcsolat.
Products (Termékek) és Category (Kategória) – manuálisan létrehozva
Category (Kategória) és SubCategory (Alkategória) – manuálisan létrehozva
Products (Termékek) és SubCategory (Alkategória) – következtetett kapcsolat
A kapcsolatok automatikus láncolásához a kapcsolatoknak a fentieknek megfelelően egy irányba kell haladniuk. Amennyiben a kiinduló kapcsolatok például a Sales (Értékesítés) és a Products (Termékek), illetve a Sales (Értékesítés) és a Customers (Ügyfelek) táblák között voltak, a rendszer nem származtat kapcsolatot. Ennek az az oka, hogy a Products (Termékek) és a Customers (Ügyfelek) táblák között „több a többhöz” típusú kapcsolat van.