Taulukoiden väliset suhteet tietomallissa

Selaimesi ei tue videotoimintoa. Asenna Microsoft Silverlight, Adobe Flash Player tai Internet Explorer 9.
Video: Suhteet Power View'ssa ja PowerPivotissa

Luomalla suhteita eri taulukoissa olevien tietojen välille saat lisätehoa tietojesi analysointiin. Suhde on kahden tietotaulukon välillä oleva yhteys, joka perustuu kummankin taulukon yhteen sarakkeeseen. Jotta ymmärtäisit, miksi suhteet ovat hyödyllisiä, kuvittele, että tarvitset tietoja yrityksen asiakastilauksia varten. Voit tallentaa kaikki tiedot yhteen taulukkoon, jonka rakenne on esimerkiksi seuraavanlainen:

Asiakastunnus

Nimi

Sähköposti

Alennusmäärä

Tilaustunnus

Tilauspäivämäärä

Tuote

Määrä

1

Koivula

veli.koivula@contoso.com

.05

256

7.1.2010

Kompakti digitaalinen

11

1

Koivula

veli.koivula@contoso.com

.05

255

3.1.2010

JÄRJESTELMÄKAMERA

15

2

Laurinen

kalevi.laurinen@contoso.com

.10

254

3.1.2010

Edullinen Movie-Maker

27

Tämä menetelmä ehkä toimii, mutta sitä käytettäessä tietyt tiedot, kuten asiakkaan sähköpostiosoite, on tallennettava toistuvasti kutakin tilausta varten. Tallennustila on halpaa, mutta jos sähköpostiosoite muuttuu, joudut päivittämään kaikki kyseisen asiakkaan rivit. Eräs ratkaisu tähän ongelmaan on jakaa tiedot useaan taulukkoon ja määrittää suhteet noiden taulukoiden välille. Tätä menetelmää käytetään relaatiotietokannoissa, kuten SQL Server -tietokannoissa. Esimerkiksi tuotavassa tietokannassa tilaustiedot voivat olla kolmessa taulukossa, joiden väliset suhteet ovat seuraavat:

Asiakkaat

[Asiakastunnus]

Nimi

Sähköposti

1

Koivula

veli.koivula@contoso.com

2

Laurinen

kalevi.laurinen@contoso.com

AsiakkaidenAlennukset

[Asiakastunnus]

Alennusmäärä

1

.05

2

.10

Tilaukset

[Asiakastunnus]

Tilaustunnus

Tilauspäivämäärä

Tuote

Määrä

1

256

7.1.2010

Kompakti digitaalinen

11

1

255

3.1.2010

JÄRJESTELMÄKAMERA

15

2

254

3.1.2010

Edullinen Movie-Maker

27

Suhteet ovat olemassa tietomallissa, jonka luot eksplisiittisesti tai jonka Excel luo puolestasi, kun tuot useita taulukoita samanaikaisesti. Voit luoda mallin ja hallita sitä myös Power Pivot -apuohjelmalla. Lisätietoja on artikkelissa Tietomallin luominen Excelissä.

Jos tuot taulukoita samasta tietokannasta Power Pivot -apuohjelmalla, Power Pivot voi tunnistaa taulukoiden väliset suhteet [hakasulkein] ympäröityjen sarakkeiden perusteella ja muodostaa nämä suhteet uudelleen taustalla rakennettavaan tietomalliin. Lisätietoja on tämän artikkelin kohdassa Suhteiden automaattinen tunnistaminen ja johtaminen. Jos tuot taulukoita useista lähteistä, voit luoda suhteet manuaalisesti artikkelissa Kahden taulukon välisen suhteen luominen kuvatulla tavalla.

Sivun alkuun

Tämän artikkelin sisältö

Sarakkeet ja avaimet

Suhdelajit

Suhteet ja suorituskyky

Useat suhteet taulukoiden välillä

Taulukkosuhteen vaatimukset

Toimet, joita taulukkosuhteessa ei tueta

Yhdistelmäavaimet ja hakusarakkeet

Monta-moneen-suhteet

Itseliitokset ja silmukat

Suhteiden automaattinen tunnistaminen ja johtaminen PowerPivotissa

Automaattinen nimettyjen joukkojen tunnistus

Suhteiden johtaminen

Sarakkeet ja avaimet

Suhteet perustuvat sellaisiin kussakin taulukossa oleviin sarakkeisiin, jotka sisältävät saman tiedon. Esimerkiksi Asiakkaat- ja Tilaukset-taulukoiden välille voidaan luoda suhde, koska molemmat sisältävät asiakasnumerosarakkeen. Esimerkissä sarakkeiden nimet ovat samat, mutta tämä ei ole välttämätöntä. Toisen nimi voi olla Asiakastunnus ja toisen Asiakasnumero, kunhan kaikki Tilaus-taulukon rivit sisältävät tunnuksen, joka on tallennettu myös Asiakkaat-taulukkoon.

Relaatiotietokannassa on monen tyyppisiä avaimia, jotka ovat tavallisesti vain sarakkeita, joilla on tietyt ominaisuudet. Kun eri avainten tarkoituksen ymmärtää, on helpompi hallita useita taulukoita käsittävää tietomallia, josta pivot-taulukon, pivot-kaavion tai Power View -raportin tiedot ovat peräisin.

Seuraavat avaintyypit soveltuvat parhaiten tarkoituksiimme:

  • Perusavain: yksilöi yhden taulukon rivin, esimerkiksi Asiakkaat-taulukon Asiakastunnus-rivin.

  • Vaihtoehtoinen avain (tai ehdokasavain): yksilöivä sarake, joka on muu kuin perusavain. Esimerkiksi Työntekijä-taulukossa voisi olla työntekijätunnus ja sosiaaliturvatunnus, jotka molemmat ovat yksilöiviä.

  • Viiteavain: sarake, joka viittaa toisen taulukon yksilöivään sarakkeeseen; esimerkiksi Tilaukset-taulukon Asiakastunnus-sarake viittaa Asiakkaat-taulukon Asiakastunnus-sarakkeeseen.

Tietomalliin perusavainta tai vaihtoehtoista avainta kutsutaan nimellä vastaava sarake. Jos taulukossa on sekä perusavain että vaihtoehtoinen avain, voit käyttää kumpaa tahansa taulukkosuhteen perustana. Viiteavaimesta käytetään nimeä lähdesarake tai vain sarake. Esimerkissä suhde määritetään Tilaukset-taulukon Asiakastunnus-sarakkeen (sarake) ja Asiakkaat-taulukon Asiakastunnus-sarakkeen (hakusarake) välille. Jos tuot tietoja relaatiotietokannasta, Excel valitsee oletusarvoisesti toisesta taulukosta viiteavaimen ja toisesta taulukosta vastaavan perusavaimen. Voit kuitenkin käyttää hakusarakkeena mitä tahansa saraketta, jossa on yksilöivät arvot.

Suhdelajit

Asiakkaat- ja Tilaukset-taulukon välinen suhde on yksi-moneen-suhde. Kullakin asiakkaalla voi olla useita tilauksia, mutta tilauksella ei voi olla useita asiakkaita. Muita suhdelajeja ovat yksi-yhteen ja monta-moneen. Asiakasalennukset-taulukon, joka määrittää yhden alennusprosentin kullekin asiakkaalle, ja Asiakkaat-taulukon välillä on yksi-yhteen-suhde.

Seuraavassa taulukossa on esitetty kolmen taulukon väliset suhteet:

Suhde

Tyyppi

Hakusarake

Sarake

Asiakkaat-AsiakkaidenAlennukset

yksi-yhteen

Asiakkaat.Asiakastunnus

AsiakkaidenAlennukset.Asiakastunnus

Asiakkaat-Tilaukset

yksi-moneen

Asiakkaat.Asiakastunnus

Tilaukset.Asiakastunnus

Huomautus:  Tietomalli ei tue monta-moneen-suhteita. Esimerkki monta-moneen-suhteesta on Tuotteet- ja Asiakkaat-taulukoiden välinen suora suhde, koska yksi asiakas voi ostaa useita tuotteita ja useat asiakkaat voivat ostaa samaa tuotetta.

Suhteet ja suorituskyky

Kun suhde on luotu, Excelin on yleensä laskettava kaikki kaavat, jotka käyttävät äskettäin luodun suhteen taulukoissa olevia sarakkeita. Tietojen määrästä ja suhteen monimutkaisuudesta riippuen käsittely voi kestää jonkin aikaa. Lisätietoa on kohdassa Kaavojen uudelleenlaskenta.

Useat suhteet taulukoiden välillä

Tietomallissa voi olla useita suhteita kahden taulukon välillä. Tarkkojen laskutoimitusten luomiseksi Excel edellyttää, että kustakin taulukosta seuraavaan taulukkoon on vain yksi suhde. Niinpä kunkin taulukkoparin välillä voi olla vain yksi aktiivinen suhde kerrallaan. Muut ovat passiivisia, mutta kaavoissa ja kyselyissä voi määritellä passiivisen suhteen. Kaavionäkymässä aktiivinen suhde on kiinteä viiva ja passiiviset ovat katkoviivoja. Esimerkiksi AdventureWorksDW2012-mallitietokannan DimDate-taulukko sisältää DateKey-sarakkeen, jolle on määritetty suhde kolmeen eri FactInternetSales-taulukon sarakkeeseen: OrderDate, DueDate ja ShipDate. Jos DateKey- ja OrderDate-sarakkeiden välinen suhde on aktiivinen, se on kaavojen oletussuhde, ellei toisin ole määritetty.

Sivun alkuun

Vaatimukset taulukkosuhteelle

Suhteen voi luoda, kun seuraavat vaatimukset täyttyvät:

Ehdot

Kuvaus

Kunkin taulukon yksilöivä tunnus

Jokaisessa taulukossa on oltava yksi sarake, joka yksilöi taulukon jokaisen rivin. Tästä sarakkeessa käytetään usein nimitystä perusavain.

Yksilöivät hakusarakkeet

Hakusarakkeen tietoarvojen tulee olla yksilöiviä. Toisin sanoen sarakkeessa ei saa olla kaksoisarvoja. Tietomallissa nollat ja tyhjät merkkijonot vastaavat tyhjää, joka on erillinen tietoarvo. Tämä tarkoittaa, että hakusarakkeessa ei saa olla useita nollia.

Yhteensopivat tietotyypit

Lähdesarakkeen ja hakusarakkeen tietotyyppien on oltava yhteensopivia. Lisätietoja tietotyypeistä on kohdassa Tietomalleissatuetut tietotyypit.

Toimet, joita taulukkosuhteessa ei tueta

Tietomallissa ei voi luoda taulukkosuhdetta, jos avain on yhdistelmäavain. Luotava suhde voi lisäksi olla vain yhden suhde yhteen tai yhden suhde moneen. Muuntyyppisiä suhteita ei tueta.

Yhdistelmäavaimet ja hakusarakkeet

Yhdistelmäavain muodostuu useasta sarakkeesta. Tietomalleissa ei voi käyttää yhdistelmäavaimia, sillä taulukon jokaisen rivin yksilöimiseen on käytettävä täsmälleen yhtä saraketta. Jos tuot taulukoita, joissa on määritetty yhdistelmäavaimeen perustuva suhde, Power Pivotin ohjattu taulukon tuontitoiminto ohittaa suhteen, koska suhdetta ei voi luoda malliin.

Jos haluat luoda suhteen kahden taulukon välille, joiden perus- ja viiteavaimet määrittää usea sarake, sinun on ensin yhdistettävä arvot yhdeksi avainsarakkeeksi, ennen kuin luot suhteen. Voit tehdä tämän ennen tietojen tuomista tai luomalla tietomalliin lasketun sarakkeen Power Pivot -apuohjelmalla.

Monta-moneen-suhteet

Tietomallissa ei voi olla monta-moneen-suhteita. Malliin ei voi lisätä liitostaulukoita . Voit kuitenkin jäljitellä monta-moneen-suhteita DAX-funktioilla.

Itseliitokset ja silmukat

Itseliitokset eivät ole sallittuja tietomallissa. Itseliitos on rekursiivinen suhde taulukon ja sen itsensä välillä. Itseliitosten avulla määritetään esimerkiksi pää- ja aliobjektien välisiä hierarkioita. Voit esimerkiksi luoda hierarkian, joka esittää yrityksen johtamisketjun, liittämällä Työntekijät-taulukon itseensä.

Excel ei salli silmukoiden luomista suhteiden kesken työkirjassa. Toisin sanoen seuraavat suhteet on kielletty.

  • Taulukon 1 sarake a Taulukon 2 sarakkeeseen f     

  • Taulukon 2 sarake f Taulukon 3 sarakkeeseen n     

  • Taulukon 3 sarake n Taulukon 1 sarakkeeseen a     

Jos yrität luoda suhteen, josta seuraa silmukka, ilmenee virhe.

Sivun alkuun

Suhteiden automaattinen tunnistaminen ja johtaminen Power Pivotissa

Power Pivot -apuohjelmalla tietojen tuomisen yhtenä etuna on se, että Power Pivot voi toisinaan tunnistaa suhteita ja luoda uusia suhteita Exceliin luomassaan tietomallissa.

Kun tuot useita taulukoita, Power Pivot tunnistaa taulukoiden väliset suhteet automaattisesti. Kun luot Pivot-taulukon, Power Pivot myös analysoi taulukoissa olevat tiedot. Se tunnistaa mahdollisesti määrittämättä jääneet suhteet ja ehdottaa noihin suhteisiin sisällytettäviä sarakkeita.

Tunnistusalgoritmi tekee päätelmiä suhteiden todennäköisyydestä sarakkeiden arvoja ja metatietoja koskevien tilastollisten tietojen avulla.

  • Kaikkien suhteissa käytettyjen sarakkeiden tietotyyppien on oltava yhteensopivia. Automaattinen tunnistus tukee vain kokonaisluku- ja tekstitietotyyppejä. Lisätietoja tietotyypeistä on ohjeaiheessa Tietomalleissa tuetut tietotyypit.

  • Jotta suhteen tunnistaminen voi onnistua, hakusarakkeessa olevien yksilöivien avainten määrän on oltava suurempi kuin taulukon monta-puolella olevien arvojen määrä. Suhteen monta-puolen avainsarake ei siis saa sisältää mitään arvoja, joita ei ole hakutaulukon avainsarakkeissa. Oletetaan esimerkiksi, että käytössä on taulukko, jossa tuotteet on lueteltu tunnusten kanssa (hakutaulukko), sekä myyntitaulukko, jossa on lueteltu kunkin tuotteen myynnit (suhteen monta-puoli). Jos myyntitietueet sisältävät sellaisen tuotteen tunnuksen, jolla ei ole vastaavaa tunnusta tuotetaulukossa, suhdetta ei voi luoda automaattisesti, mutta sen luominen manuaalisesti saattaa olla mahdollista. Jotta Excel voi tunnistaa suhteen, sinun on ensin päivitettävä tuotteiden hakutaulukkoon puuttuvien tuotteiden tunnukset.

  • Pidä huoli, että monta-puolen avainsarakkeen nimi on samankaltainen kuin hakutaulukon avainsarakkeen nimi. Nimien ei tarvitse olla täsmälleen samoja. Esimerkiksi liiketoimintaympäristössä käytännössä samoja tietoja sisältävien sarakkeiden nimet ovat usein muunnelmia toisistaan, kuten esimerkiksi Työntek ID, Työntekijän tunnus, Työntekijän_tunnus ja niin edelleen. Algoritmi tunnistaa samankaltaiset nimet ja määrittää suuremman todennäköisyyden sellaisten sarakkeiden välille, joiden nimet ovat samankaltaisia tai täsmälleen samoja. Voit siis yrittää parantaa suhteen tunnistamisen todennäköisyyttä nimeämällä tuotavat tietosarakkeet niin, että nimet muistuttavat aiemmin luoduissa taulukoissa olevien sarakkeiden nimiä. Jos Excel löytää monta mahdollista suhdetta, se ei luo suhdetta.

Tämä tieto voi auttaa ymmärtämään, miksi toiminto ei havaitse kaikkia suhteita, tai miksi metatietojen, kuten kenttien nimien ja tietotyyppien, muuttaminen voi parantaa automaattisen suhteiden tunnistamisen tuloksia. Lisätietoja on ohjeaiheessa Suhteiden vianmääritys.

Automaattinen nimettyjen joukkojen tunnistus

Nimettyjen joukkojen ja liittyvien Pivot-taulukkokenttien yhteyttä ei tunnisteta automaattisesti. Voit luoda nämä suhteet manuaalisesti. Jos haluat käyttää automaattista suhteen tunnistusta, irrota kukin nimetty joukko ja lisää nimetystä joukosta yksittäisiä kenttiä suoraan Pivot-taulukkoon.

Suhteiden johtaminen

Joissakin tapauksissa taulukoiden väliset suhteet ketjutetaan automaattisesti. Jos esimerkiksi luot suhteen seuraavan esimerkin kahden ensimmäisen taulukon välille, suhde johdetaan myös kahden muun taulukon välille ja luodaan automaattisesti.

  • Tuotteet ja Luokka – luodaan automaattisesti

  • Luokka ja Aliluokka – luodaan automaattisesti

  • Tuotteet ja Aliluokka – suhde johdetaan

Jotta suhteiden ketjuttaminen voisi tapahtua automaattisesti, suhteiden on oltava yksisuuntaisia, kuten yllä. Jos suhteet olisi alun perin luotu esimerkiksi taulukoiden Myynti ja Tuotteet sekä taulukoiden Myynti ja Asiakkaat välille, suhdetta ei johdeta. Tämä johtuu siitä, että suhde taulukoiden Tuotteet ja Asiakkaat välillä on monta-moneen-suhde.

Sivun alkuun

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×