Tabelivahelised seosed andmemudelis

Teie brauser ei toeta videot. Installige Microsoft Silverlight, Adobe Flash Player või Internet Explorer 9.
Video: seosed lisandmoodulites Power View ja Power Pivot

Erinevate tabelite andmete vahel seoseid luues saate teha märksa võimsamaid andmeanalüüse. Seos on kahe andmetabeli vaheline ühendus, mille aluseks on kummagi tabeli üks veerg. Seoste kasulikkuse mõistmiseks kujutlege, et jälgite oma ettevõttes klienditellimuste andmeid. Saaksite kõiki andmeid jälgida ühes tabelis, millel on järgmine struktuur.

CustomerID

Name

Email

DiscountRate

OrderID

Tellimiskuupäev

Product

Quantity

1

Ashton

chris.ashton@contoso.com

0,05

256

2010-01-07

Kompaktne digitaalne

11

1

Ashton

chris.ashton@contoso.com

0,05

255

2010-01-03

SLR-kaamera

15

2

Jaworski

michal.jaworski@contoso.com

0,10

254

2010-01-03

Väikse eelarvega filmitegija

27

Selline lähenemine võib toimida, kuid sellega kaasneb rohkelt liigsete andmete talletamist, nagu kliendi meiliaadress iga tellimuse juures. Talletamine on odav, kuid meiliaadressi muutudes peate värskendama selle kliendi kõiki ridu. Üks võimalik lahendus on tükeldada andmed mitmeks tabeliks ja määratleda nende tabelite vahel seosed. See on lähenemisviis, mida kasutavad sellised relatsiooniandmebaasid nagu SQL Server. Näiteks võib imporditav andmebaas esitada tellimuseandmeid, kasutades kolme seotud tabelit:

Kliendid

[CustomerID]

Name

Email

1

Ashton

chris.ashton@contoso.com

2

Jaworski

michal.jaworski@contoso.com

CustomerDiscounts (Kliendiallahindlused)

[CustomerID]

DiscountRate

1

0,05

2

0,10

Tellimused

[CustomerID]

OrderID

Tellimiskuupäev

Product

Quantity

1

256

2010-01-07

Kompaktne digitaalne

11

1

255

2010-01-03

SLR-kaamera

15

2

254

2010-01-03

Väikse eelarvega filmitegija

27

Seosed on olemas konkreetselt loodud andmemudeli sees või sellise andmemudeli sees, mille Excel loob teie eest, kui impordite korraga mitu tabelit. Samuti saate mudeli loomiseks või haldamiseks kasutada Power Pivoti lisandmoodulit. Põhjalikumat teavet leiate artiklist Andmemudeli loomine Excelis.

Kui kasutate Power Pivoti lisandmoodulit tabelite importimiseks samast andmebaasist, võib Power Pivot tuvastada tabelitevahelised seosed [nurksulgudes] asuvate veergude põhjal ja need taastekitada taustal koostatavas andmemudelis. Lisateavet leiate selle artikli jaotisest Seoste automaattuvastus ja tuletamine. Mitmest allikast importides saate seoseid käsitsi luua; seda on kirjeldatud artiklis Kahe tabeli vahel seose loomine.

Lehe algusse

Selle artikli teemad

Veerud ja võtmed

Seoste tüübid

Seosed ja jõudlus

Mitu seost tabelite vahel

Tabeliseose nõuded

Mida tabeliseoses ei toetata

Koondvõtmed ja otsinguveerud

Mitu-mitmele seosed

Iseliitmised ja tsüklid

Seoste automaattuvastus ja tuletamine PowerPivotis

Nimega komplektide automaattuvastus

Seoste tuletamine

Veerud ja võtmed

Seosed põhinevad kõigi tabelite veergudel, mis sisaldavad samu andmeid. Näiteks: klientide ja tellimuste tabelid saab omavahel seostada, kuna neis mõlemas sisaldub kliendi ID veerg. Näites on veerunimed samad, aga see pole kohustuslik. Üks võib olla CustomerID ja teine CustomerNumber, kuid kõik tellimuste tabeli read peavad sisaldama ID-d, mis leidub ka klientide tabelis.

Relatsiooniandmebaasis on mitut tüüpi võtmeid, mis on tavaliselt lihtsalt eriliste omadustega veerud. Iga võtme eesmärgi mõistmine võib teil aidata hallata mitme tabeliga andmemudelit, mis pakub andmeid PivotTable-liigendtabelile, PivotChartile või Power View’ aruandele.

Meie eesmärkide puhul on kõike huvitavamad järgmised võtmed.

  • Primaarvõti: identifitseerib kordumatult tabeli rea, nt klientide tabelis rea CustomerID.

  • Alternatiivvõti (ka kandidaatvõti): lisaks primaatvõtmele teine kordumatu veerg. Näiteks töötajate tabel võib sisaldada nii töötaja ID-d kui isikukoodi, mis mõlemad on kordumatud.

  • Välisvõti: veerg, mis viitab kordumatule veerule teises tabelis, nt CustomerID tellimuste tabelis, mis viitab CustomerID-le klientide tabelis.

Andmemudelis viidatakse primaarvõtmele ja alternatiivvõtmele kui seotud veerule. Kui tabelis on nii primaar- kui alternatiivvõti, võite tabeliseose alusena kasutada emba-kumba neist. Välisvõtmele viidatakse kui lähteveerule või lihtsalt veerule. Meie näites määratletaks seos tellimuste tabeli veeru CustomerID (veerg) ja klientide tabeli veeru CustomerID (otsinguveerg) vahel. Kui impordite andmeid relatsiooniandmebaasist, valib Excel vaikimisi välisvõtme ühest tabelist ja vastava primaarvõtme teisest tabelist. Üldiselt saate otsinguveeruna kasutada suvalist kordumatute väärtustega veergu.

Seoste tüübid

Klientide ja tellimuste vaheline seos on üks-mitmele. Igal kliendil võib olla mitu tellimust, kuid tellimusel ei saa olla mitut klienti. Muud seosetüübid on üks-ühele ja mitu-mitmele. Tabelil CustomerDiscounts, mis määrab iga kliendi jaoks ühe allahindlusmäära, on klientide tabeliga üks-ühele seos.

Järgmine tabel näitab kolme tabeli vahelisi seoseid.

Seos

Tüüp

Otsinguveerg

Veerg

Customers-CustomerDiscounts

üks-ühele

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

üks mitmele

Customers.CustomerID

Orders.CustomerID

Märkus. :  Andmemudelis pole mitu-mitmele seosed toetatud. Mitu-mitmele seose näide on vahetu seos toodete ja klientide vahel, kus klient saab osta mitu toodet ja sama toodet saab osta mitu klienti.

Seosed ja jõudlus

Pärast iga seose loomist peab Excel tavaliselt ümber arvutama kõik valemid, mis kasutavad uue loodud seose tabelite veerge. Töötlemine võib aega võtta, sõltuvalt andmete hulgast ja seoste keerukusest. Täpsema teabe saamiseks vaadake lehte Valemite ümberarvutamine.

Mitu seost tabelite vahel

Andmemudelis võib kahe tabeli vahel olla mitu seost. Täpsete arvutuste loomiseks on Excelil vaja ühest tabelist teise ühtainsat teed. Niisiis on iga tabelipaari vahel korraga aktiivne ainult üks seos. Muud seosed on passiivsed, kuid passiivset seost saab määrata valemites ja päringutes. Diagrammivaates on aktiivne seos märgitud ühtlase joonega, samas kui passiivsed on kriipsjoonega. Näiteks AdventureWorksDW2012 tabel DimDate sisaldab veergu DateKey, mis on seotud tabeli FactInternetSales kolme eri veeruga: OrderDate, DueDate ja ShipDate. Kui aktiivne seos on veergude DateKey ja OrderDate vahel, on see valemites vaikeseos, kui te just ei määra mõnda teist.

Lehe algusse

Tabeliseose nõuded

Seose saab luua, kui järgmised nõuded on täidetud.

Kriteeriumid

Kirjeldus

Iga tabeli kordumatu ID

Igal tabelil peab olema üks veerg, mis identifitseerib kõik selle tabeli read kordumatul viisil. Seda veergu nimetatakse tihti primaarvõtmeks.

Kordumatud otsinguveerud

Otsinguveeru andmeväärtused peavad olema kordumatud. Teisisõnu ei tohi veerg sisaldada kordusi. Andmemudelis võrduvad nullväärtused ja tühjad stringid tühiväärtusega, mis on eraldi andmeväärtus. See tähendab, et otsinguveerus ei või mitut nulli olla.

Ühilduvad andmetüübid

Lähteveeru ja otsinguveeru andmetüübid peavad omavahel ühilduma. Lisateavet andmetüüpide kohta leiate lehelt Andmemudelites toetatud andmetüübid.

Mida tabeliseoses ei toetata

Andmemudelis ei saa tabeliseost luua, kui võti on koondvõti. Samuti on lubatud ainult üks ühele ja üks mitmele seosed. Muid seosetüüpe ei toetata.

Koondvõtmed ja otsinguveerud

Koondvõti koosneb mitmest veerust. Andmemudelid ei saa koondvõtmeid kasutada; tabelis peab alati olema täpselt üks veerg, mis identifitseerib kordumatul viisil kõik tabeli read. Kui impordite koondvõtmel põhineva olemasoleva seosega tabeleid, eirab tabelite importimise viisard Power Pivotis seda seost, kuna seda ei saa mudelis luua.

Kui loote seost kahe tabeli vahel, millel on mitu primaar- ja välisvõtmeid määratlevat veergu, ühendage väärtused üheainsa võtmeveeru tegemiseks enne seose loomist. Seda saate teha enne andmete importimist või luues andmemudelis Power Pivoti lisandmooduli abil arvutusliku veeru.

Mitu-mitmele seosed

Andmemudelis ei saa olla mitu-mitmele seoseid. Liitmiktabeleid ei saa lihtsalt mudelisse lisada. Küll aga saate DAX-i funktsioonide abil modelleerida mitu-mitmele seoseid.

Iseliitmised ja tsüklid

Iseliitmised pole andmemudelis lubatud. Iseliitmine on rekursiivne seos tabeli ja iseenda vahel. Iseliitmisi kasutatakse tihti ema-/tütarhierarhiate määratlemisel. Näiteks: võiksite töötajate tabeli liita tabeli endaga, et luua ettevõtte haldusahelat kirjeldav hierarhia.

Excel ei luba töövihikus seoste vahel tsükleid luua. Teisisõnu on järgmised seosekomplektid keelatud.

  • Tabel 1, veerg a   ja   Tabel 2, veerg f

  • Tabel 2, veerg f   ja   Tabel 3, veerg n

  • Tabel 3, veerg n   ja   Tabel 1, veerg a

Kui proovite luua seost, mis tekitaks tsükli, tekib tõrge.

Lehe algusse

Seoste automaattuvastus ja tuletamine lisandmoodulis Power Pivot

Üks eeliseid andmete importimisel Power Pivoti lisandmooduliga seisneb selles, et Power Pivot võib vahel seoseid tuvastada ja luua uued seosed Excelisse tehtavas andmemudelis.

Kui impordite mitu tabelit, tuvastab Power Pivot automaatselt tabelitevahelised olemasolevad seosed. Samuti analüüsib Power Pivot tabelites andmeid PivotTable-liigendtabeli loomisel. See tuvastab võimalikud määratlemata seosed ja soovitab sobivaid veerge nendes seostes kaasamiseks.

Tuvastusalgoritm kasutab veergude metaandmete ja väärtuste statistilisi andmeid, et teha järeldusi seoste tõenäosuse kohta.

  • Kõigi seotud veergude andmetüübid peavad omavahel ühilduma. Automaattuvastuse puhul toetatakse ainult täisarvu ja teksti andmetüüpe. Lisateavet andmetüüpide kohta leiate lehelt Andmemudelites toetatudandmetüübid.

  • Seose edukaks tuvastamiseks peab otsinguveeru kordumatute võtmete arv olema suurem kui väärtused tabeli poolel "mitmele". Teisisõnu ei tohi seose poolel "mitmele" paiknev võtmeveerg sisaldada ühtki väärtust, mis pole otsingutabeli võtmeveerus. Näiteks: oletame, et teil on tabel, kus on tooted ja nende ID-d (otsingutabel), ja müügitabel, kus on kõigi toodete müük (seose pool "mitmele"). Kui teie müügikirjed sisaldavad toote ID-d, millele ei leidu toodete tabelis vastavat ID-d, ei saa seost automaatselt luua, kuid võib-olla saab selle teha käsitsi. Võimaldamaks Excelil seoseid tuvastada peate kõigepealt värskendama toodete otsingutabelit puuduvate toodete ID-dega.

  • Veenduge, et võtmeveeru nimi poolel "mitmele" sarnaneb otsingutabeli võtmeveeru nimega. Nimed ei pea tingimata kattuma. Ettevõtte puhul on teil tihti iseenesest samu andmeid sisaldavate nimede variatsioone: Emp ID, EmployeeID, Employee ID, EMP_ID jne. Algoritm tuvastab sarnased nimed ja määrab suurema tõenäosuse veergudele, millel on sarnased või kattuvad nimed. Niisiis võite seose loomise tõenäosuse suurendamiseks proovida veerud imporditavates andmetes ümber nimetada, pannes olemasolevate tabelite veergudele sarnanevad nimed. Kui Excel leiab mitu võimalikku seost, jäetakse seos loomata.

Sellest teabest on teil ehk kasu mõistmaks, miks kõiki seoseid ei tuvastata ja kuidas metaandmete (nt väljanimi ja andmetüübid) muutused võivad automaatse seosetuvastuse tulemusi parandada. Lisateavet vaadake lehelt Seoste tõrkeotsing.

Nimega komplektide automaattuvastus

Nimega komplektide ja PivotTable-liigendtabeli seotud väljade vahel ei tuvastata seoseid automaatselt. Need seosed saab luua käsitsi. Kui soovite kasutada automaatset seosetuvastust, eemaldage kõik nimega komplektid ja lisage nimega komplekti üksikväljad otse PivotTable-liigendtabelisse.

Seoste tuletamine

Mõnel juhul määratakse tabeliseosed ahelana. Näiteks: kui loote seose kahe esimese alltoodud tabelikomplekti vahel, järeldatakse, et seos on olemas ka kahe teise tabeli vahel ja see seos luuakse automaatselt.

  • Tooted ja Kategooria -- loodud käsitsi

  • Kategooria ja Alamkategooria -- loodud käsitsi

  • Tooted ja Alamkategooria -- seos on tuletatud

Seoste automaatseks aheltuletuseks peavad seosed minema ühes suunas, nagu ülal näidatud. Kui algseosed oleks nt Müük ja Tooted ning Müük ja Kliendid, siis seost ei tuletataks. Põhjuseks on see, et toodete ja klientide seos on mitu-mitmele tüüpi.

Lehe algusse

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×