Lentelių ryšiai duomenų modelyje

Jūsų naršyklė nepalaiko vaizdo įrašo. Įdiekite „Microsoft Silverlight“, „Adobe Flash Player“ ar „Internet Explorer 9“.
Vaizdo įrašas: „Power View“ ir „Power Pivot“ ryšiai

Padidinkite duomenų analizės efektyvumą kurdami ryšį tarp skirtingose lentelėse esančių duomenų. Ryšys yra dviejų duomenų lentelių sąsaja, sudaryta pagal vieną kiekvienos lentelės stulpelį. Kad geriau suprastumėte, kuo gali būti naudingi ryšiai, įsivaizduokite, kad sekate kliento užsakymų duomenis. Galima sekti visus duomenis vienoje lentelėje, kurios struktūra nurodyta toliau.

CustomerID

Name

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

1

Ashton

chris.ashton@contoso.com

0,05

256

2010-01-07

Kompaktiškas skaitmeninis

11

1

Ashton

chris.ashton@contoso.com

0,05

255

2010-01-03

Veidrodinis fotoaparatas

15

2

Jaworski

michal.jaworski@contoso.com

0,10

254

2010-01-03

Nebrangi filmų montažinė

27

Šis būdas gali veikti, bet jį taikant saugoma daug pasikartojančių duomenų, pvz., kiekvieno užsakymo kliento el. pašto adresas. Saugoti nebrangu, bet pasikeitus el. pašto adresui būtina atnaujinti kiekvieną to kliento eilutę. Vienas iš šios problemos sprendimų yra duomenų paskirstymas keliose lentelėse ir ryšių tarp tų lentelių apibrėžimas. Šis būdas taikomas reliacinėse duomenų bazėse, pvz., SQL serveryje. Importuojamoje duomenų bazėje užsakymo duomenys gali būti pateikiami naudojant tris susijusias lenteles:

Customers

[CustomerID]

Name

Email

1

Ashton

chris.ashton@contoso.com

2

Jaworski

marijus.bagdonas@contoso.com

KlientoNuolaidos

[CustomerID]

DiscountRate

1

.05

2

.10

Orders

[CustomerID]

OrderID

OrderDate

Product

Quantity

1

256

2010-01-07

Kompaktiškas skaitmeninis

11

1

255

2010-01-03

Veidrodinis fotoaparatas

15

2

254

2010-01-03

Nebrangi filmų montažinė

27

Ryšių yra duomenų modelyje, kuris kuriamas tiesiogiai arba kurį jums nedalyvaujant sukuria „Excel“, kai vienu metu importuojate kelias lenteles. Modeliui kurti ir tvarkyti taip pat galima naudoti „Power Pivot“ papildinį. Daugiau informacijos žr. Duomenų modelio kūrimas programoje „Excel“.

Jeigu naudojate „Power Pivot“ papildinį importuodami lenteles iš tos pačios duomenų bazės, „Power Pivot“ gali nustatyti ryšius tarp lentelių pagal [skliaustais] išskirtus stulpelius ir atkurti šiuos ryšius duomenų modelyje, kuris kuriamas jums nedalyvaujant. Daugiau informacijos žr. šio straipsnio dalyje Automatinis ryšių aptikimas ir numatymas. Jeigu lentelės importuojamos iš įvairių šaltinių, galima sukurti ryšius neautomatiškai, kaip nurodyta straipsnyje Ryšių tarp dviejų lentelių kūrimas.

Puslapio viršus

Šiame straipsnyje:

Stulpeliai ir raktai

Ryšių tipai

Ryšiai ir efektyvumas

Keli ryšiai tarp lentelių

Lentelių ryšių reikalavimai

Nepalaikoma lentelės ryšiuose

Sudėtiniai raktai ir peržvalgos stulpeliai

Ryšiai „daugelis su daugeliu“

Automatiniai sujungimai ir ciklai

Automatinis „Power Pivot“ ryšių aptikimas ir numatymas

Automatinis įvardytųjų rinkinių aptikimas

Ryšių numatymas

Stulpeliai ir raktai

Ryšiai pagrįsti kiekvienoje lentelėje esančiais stulpeliais, kuriuose yra tokie patys duomenys. Pavyzdžiui, lentelės Klientai ir Užsakymai gali būti susietos tarpusavyje, nes jose yra stulpelis, kuriame saugomas kliento ID. Pavyzdyje stulpelių pavadinimai sutampa, bet tai nėra būtina. Vieno stulpelio pavadinimas galėtų būti CustomerID, o kito – CustomerNumber, jei tik visose lentelės „Orders“ eilutėse bus nurodytas ID, kuris saugomas ir lentelėje „Customers“.

Santykinėje duomenų bazėje yra kelių tipų raktai, kurie dažniausiai yra specialias ypatybes turintys stulpeliai. Supratus kiekvieno rakto paskirtį bus lengviau tvarkyti kelių lentelių duomenų modelį, teikiantį duomenis „PivotTable“, „PivotChart“ ar „Power View“ ataskaitai.

Mus labiausiai domina toliau nurodyti raktai.

  • Pirminis raktas: unikaliai identifikuoja lentelės eilutę, pvz., CustomerID lentelėje „Customers“.

  • Alternatyvusis raktas (arba pretenduojantis raktas): kitas stulpelis, kuris yra unikalus ne pirminis raktas. Pavyzdžiui, lentelėje Darbuotojai gali būti darbuotojų ID ir socialinio draudimo numeriai, kurie yra unikalūs.

  • Išorinis raktas: stulpelis, nurodantis kitoje lentelėje esantį unikalų stulpelį, pvz., CustomerID lentelėje „Orders“, nurodantis CustomerID lentelėje „CustomerID“.

Duomenų modelyje pirminis raktas arba alternatyvusis raktas vadinamas susijusiu stulpeliu. Jeigu lentelėje yra pirminis ir alternatyvusis raktai, kaip lentelės ryšio pagrindą galima naudoti bet kurį iš jų. Išorinis raktas vadinamas šaltinio stulpeliu arba tiesiog stulpeliu. Mūsų pavyzdyje ryšys bus nustatytas tarp CustomerID lentelėje „Orders“ (stulpelio) ir CustomerID lentelėje „Customers“ (peržvalgos stulpelio). Importuojant duomenis iš santykinės duomenų bazės, pagal numatytuosius nustatymus „Excel“ parenka išorinį raktą iš vienos lentelės ir atitinkamą pirminį raktą iš kitos lentelės. Tačiau peržvalgos stulpeliui galite naudoti bet kurį stulpelį, kuriame yra unikalios reikšmės.

Ryšių tipai

Ryšys tarp klientų ir užsakymų yra ryšys „vienas su daugeliu“. Klientui gali būti priskirti keli užsakymai, bet užsakymui gali būti priskirtas tik vienas klientas. Kiti ryšių tipai yra „vienas su vienu“ ir „daugelis su daugeliu“. „CustomerDiscounts“ lentelėje, kurioje nurodytas kiekvieno kliento vienkartinės nuolaidos dydis, yra ryšys „vienas su vienu“ su lentele „Customers“.

Tolesnėse lentelėse nurodyti ryšiai tarp trijų lentelių:

Ryšys

Tipas

Peržvalgos stulpelis

Stulpelis

Customers-CustomerDiscounts

vienas su vienu

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers – Orders

vienas su daugeliu

Customers.CustomerID

Orders.CustomerID

Pastaba :  Duomenų modelis nepalaiko ryšių „daugelis su daugeliu“. Ryšio „daugelis su daugeliu“ pavyzdys yra tiesioginis ryšys tarp produktų ir klientų, kai klientai gali pirkti daug produktų, o vieną produktą gali pirkti daug klientų.

Ryšiai ir efektyvumas

Sukūrus ryšį „Excel“ paprastai turi perskaičiuoti visas formules, kuriose naujai sukurtam ryšiui naudojami stulpeliai iš lentelių. Apdorojimas gali šiek tiek užtrukti, atsižvelgiant į duomenų kiekį ir ryšių sudėtingumą. Daugiau informacijos žr. Formulių perskaičiavimas.

Keli ryšiai tarp lentelių

Duomenų modelyje gali būti keli ryšiai tarp dviejų lentelių. Programai „Excel“ būtinas vienas kelias iš vienos lentelės į kitą, kad būtų galima tiksliai skaičiuoti. Todėl vienu metu aktyvus tik vienas kiekvienos lentelių poros ryšys. Kiti ryšiai neaktyvūs, bet formulėse ir užklausose tokį ryšį galite nurodyti. Diagramos rodinyje aktyvus ryšys žymimas vientisa linija, o neaktyvūs ryšiai – brūkšninėmis. Pavyzdžiui, „AdventureWorksDW2012“ lentelėje DimDate yra stulpelis DateKey, susijęs su trimis skirtingais stulpeliais lentelėje FactInternetSales: OrderDate, DueDate ir ShipDate. Numatytasis ryšys formulėse yra aktyvus ryšys tarp DateKey ir OrderDate, nebent nurodoma kitaip.

Puslapio viršus

Lentelių ryšių reikalavimai

Ryšį galima kurti, tada, kai įgyvendinami toliau nurodyti reikalavimai.

Kriterijai

Aprašas

Kiekvienos lentelės unikalus identifikatorius

Kiekvienoje lentelėje turi būti vienas stulpelis, kuriame unikaliai identifikuojama kiekviena lentelės eilutė. Šis stulpelis dažnai vadinamas pirminiu raktu.

Unikalūs peržvalgos stulpeliai

Duomenų reikšmės peržvalgos stulpelyje turi būti unikalios. Kitaip tariant, stulpelyje negali būti dublikatų. Duomenų modelyje nuliai ir tuščios eilutės yra atitikmenys tuščių reikšmių, kurios yra konkrečios duomenų reikšmės. Tai reiškia, kad peržvalgos stulpelyje negali būti kelių nulių.

Suderinami duomenų tipai

Duomenų tipai šaltinio stulpelyje ir peržvalgos stulpelyje turi būti suderinami. Daugiau informacijos apie duomenų tipus žr. Duomenų modeliuose palaikomiduomenų tipai.

Nepalaikoma lentelės ryšiuose

Duomenų modelyje negalima kurti lentelių ryšių, jeigu raktas yra sudėtinis. Galima kurti tik ryšius „vienas su vienu“ ir „vienas su daugeliu“. Kiti ryšių tipai nepalaikomi.

Sudėtiniai raktai ir peržvalgos stulpeliai

Sudėtinį raktą sudaro daugiau negu vienas stulpelis. Duomenų modeliuose sudėtinių raktų naudoti negalima. Lentelėje visada turi būti vienas stulpelis, unikaliai identifikuojantis kiekvieną lentelės eilutę. Importuojant lenteles, kuriose yra ryšys, pagrįstas sudėtiniu raktu, „Power Pivot“ lentelės importavimo vediklis nepaisys to ryšio, nes jo modelyje sukurti negalima.

Norėdami sukurti ryšį tarp dviejų lentelių, kuriose yra keli stulpeliai, apibrėžiantys pirminius ir išorinius raktus, iš pradžių sujunkite reikšmes, kad sukurtumėte vieną rakto stulpelį, ir tik tada kurkite ryšį. Tą galima atlikti prieš importuojant duomenis arba sukuriant duomenų modelyje apskaičiuojamąjį stulpelį naudojant „Power Pivot“ papildinį.

Ryšiai „daugelis su daugeliu“

Duomenų modelyje negali būti ryšių „daugelis su daugeliu“. Negalima tiesiog įtraukti sujungimo lentelių į modelį. Tačiau modeliuojant ryšius „daugelis su daugeliu“ galima naudoti DAX funkcijas.

Vidiniai sujungimai ir ciklai

Vidiniai sujungimai duomenų modelyje neleidžiami. Vidinis sujungimas yra rekursinis ryšys tarp lentelės ir jos pačios. Vidiniai sujungimai dažnai naudojami pirminėms ir antrinėms hierarchijoms apibrėžti. Pavyzdžiui, galima sujungti lentelę Darbuotojai su ja pačia, kad būtų sukurta hierarchija, parodanti verslo valdymo grandinę.

„Excel“ darbaknygėje negalima kurti ciklų tarp ryšių. Kitaip tariant, toliau nurodyto ryšių rinkinio naudoti negalima.

  • 1 lentelė, stulpelis   su   2 lentele, stulpelis f

  • 2 lentelė, stulpelis f   su   3 lentele, stulpelis n

  • 3 lentelė, stulpelis n   su   1 lentele, stulpelis a

Jeigu bandysite sukurti ryšį, pagal kurį bus sukuriamas ciklas, bus pateikiama klaida.

Puslapio viršus

Automatinis ryšių aptikimas ir numatymas „Power Pivot“

Vienas iš duomenų importavimo naudojant „Power Pivot“ papildinį pranašumų yra tas, kad „Power Pivot“ kartais gali aptikti ryšius ir sukurti naujų ryšių duomenų modelyje, kurį kuria „Excel“.

Importuojant kelias lenteles „Power Pivot“ automatiškai aptinka visus esamus ryšius tarp lentelių. Kai kuriama „PivotTable“, „Power Pivot“ analizuoja lentelėse esančius duomenis. Papildinys aptinka galimus ryšius, kurie nebuvo apibrėžti, ir pasiūlo atitinkamus stulpelius, į kuriuos bus įtraukti tie ryšiai.

Aptikimo algoritmas naudoja statistinius reikšmių duomenis ir stulpelių metaduomenis, kad būtų galima numatyti ryšių galimybes.

  • Visuose susijusiuose stulpeliuose esančių duomenų tipai turi būti suderinami. Naudojant automatinį aptikimą palaikomi tik sveikųjų skaičių ir teksto duomenų tipai. Daugiau informacijos apie duomenų tipus žr. Duomenų modeliuose palaikomi duomenų tipai.

  • Siekiant sėkmingai aptikti ryšį, peržvalgos stulpelyje esančių unikalių raktų skaičius turi būti didesnis negu lentelės reikšmės dalyje „daugelis“. Kitaip tariant, rakto stulpelyje, priklausančiame ryšio daliai „daugelis“, negali būti jokių reikšmių, kurių nėra peržvalgos lentelės rakto stulpelyje. Tarkime, kad yra lentelė, kurioje išvardyti produktai ir jų ID (peržvalgos lentelė) ir pardavimo lentelė, kurioje nurodyti kiekvieno produkto pardavimo duomenys (ryšio dalis „daugelis“). Jeigu pardavimo įrašuose yra produkto ID, kuriam lentelėje Produktai nepriskirtas atitinkamas ID, ryšio negalima sukurti automatiškai, bet jį gali pavykti sukurti neautomatiškai. Jeigu norite, kad „Excel“ aptiktų ryšį, iš pradžių būtina atnaujinti produktų peržvalgos lentelę naudojant trūkstamų produktų ID.

  • Įsitikinkite, kad dalies „daugelis“ rakto stulpelio pavadinimas yra panašus į peržvalgos lentelės rakto stulpelio pavadinimą. Pavadinimai neprivalo visiškai sutapti. Pavyzdžiui, verslo aplinkoje dažnai naudojami keli stulpelių pavadinimų variantai, o tuose stulpeliuose saugomi panašūs duomenys: Emp ID, EmployeeID, Employee ID, EMP_ID ir t. t. Algoritmas aptinka panašius pavadinimus ir priskiria stulpeliams, kurių pavadinimai yra panašūs arba vienodi, didesnę tikimybę. Todėl norėdami padidinti ryšio sukūrimo tikimybę galite pakeisti importuojamų duomenų stulpelių pavadinimus į tokius, kurie panašūs į suteiktus esamų lentelių stulpeliams. Jeigu „Excel“ aptinka kelis galimus ryšius, ryšys nesukuriamas.

Ši informacija padės suprasti, kodėl aptinkami ne visi ryšiai arba kaip metaduomenų pakeitimai, pvz., lauko pavadinimai ir duomenų tipai, padėtų tobulinti automatinio ryšio aptikimo rezultatus. Daugiau informacijos žr. Ryšių trikčių diagnostika.

Automatinis įvardytųjų rinkinių aptikimas

„PivotTable“ tarp įvardytųjų rinkinių ir susijusių laukų ryšiai automatiškai neaptinkami. Šiuos ryšius galima sukurti neautomatiškai. Jeigu norite naudoti automatinį ryšių aptikimą, pašalinkite kiekvieną įvardytąjį rinkinį ir įtraukite atskirus laukus iš įvardytojo rinkinio tiesiogiai į „PivotTable“.

Ryšių numatymas

Tam tikrais atvejais ryšiai tarp lentelių automatiškai sujungiami. Jeigu kuriamas ryšys tarp dviejų pirmųjų lentelių rinkinių (tolesnis pavyzdys), numatoma, kad ryšys egzistuoja tarp kitų dviejų lentelių, ir jis sukuriamas automatiškai.

  • Produktai ir kategorija – sukurtas neautomatiškai

  • Kategorija ir subkategorija – sukurtas neautomatiškai

  • Produktai ir subkategorija – ryšys numatytas

Kad ryšiai būtų automatiškai sujungti, jie turi būti nukreipti viena kryptimi, kaip parodyta anksčiau. Jeigu pradiniai ryšiai buvo tarp elementų Pardavimas ir produktai bei Pardavimas ir klientai, ryšys nėra numatomas. Taip yra todėl, kad ryšys tarp elementų Produktai ir Klientai yra ryšys „daugelis su daugeliu“.

Puslapio viršus

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×