Tietokanta PowerPivotiin

Päivitetty 25.1.2016

Relaatiotietokanta

Relaatiotietokantaan pyritään tallentamaan kukin tieto vain yhteen paikkaan. Näin tiedon muuttuessa riittää tehdä päivitys yhteen paikkaan. Tästä seuraa, että toisiinsa liittyviä tietoja täytyy pilkkoa eri taulukoihin. Taulukot yhdistyvät toisiinsa avainkenttien välityksellä.

Esimerkki. Tilausrivit voidaan tallentaa omaan taulukkoonsa. Tilausriville tallennetaan tilatun tuotteen tuotenumero, mutta ei muita tietoja tuotteesta. Muut tuotetiedot sijaitsevat tuotetietoja sisältävässä taulukossa. Yhteys (relaatio) taulujen välille muodostuu tuotenumeron välityksellä. Tuotenumero siis löytyy sekä tilausrivien taulukosta että tuotetietojen taulukosta. Muita tuottetietoja ei tarvitse tallentaa eri paikkoihin.

Excel 2013 lähtien voin käyttää relaatiotietokantoja Excelin PowerPivot-sovelluksella. Relaatiotietokannan tiedoista voin laskea erilaisia yhteenvetoja Excelin pivot-taulukoinnilla.

Käytän seuraavassa esimerkkinä julkisesti saatavilla olevaa Access-muotoista AdventureWorks-tietokantaa (http://adventureworksaccess.codeplex.com/).

Tietokannan tuonti

Jos Excelin yläreunassa ei ole PowerPivot-välilehteä, niin otan PowerPivotin käyttöön:

  • Valitsen File – Options – Add-Ins (Tiedosto – Asetukset – Apuohjelmat)
  • Valitsen alareunan pudotusvalikosta COM Add-ins (COM-apuohjelmat) ja napsautan Go (Siirry) -painiketta
  • Valitsen Microsoft Office PowerPivot for Excel
  • OK.

power1

Pääsen PowerPivotiin valitsemalla PowerPivot-välilehdeltä Manage.

power2

Tietojen noutamiseksi valitsen PowerPivotin Home-välilehdeltä Get External Data – From Database – From Access.

power3

Table Import Wizard -ikkunassa voin nimetä tietokantayhteyden ja haen tietokannan Browse-painikkeella. Käyttäjänimeä ja salasanaa tarvitsen suojattuihin tietokantoihin (AdventureWorks ei ole salasanasuojattu).

Siirryn eteenpäin Next-painikkeella. Seuraavassa vaiheessa valitsen Select from a list of tables and views to choose the data to import ja siirryn eteenpäin Next-painikkeella.

Seuraavassa vaiheessa valitsen haluamani taulukot.

power4

Jos haluan tarkastella  tilausten arvoja alueittain ja tuoteryhmittäin, niin valitsen AdventureWorks-tietokannasta seuraavat taulukot:

  • Production_Product
  • Production_ProductCategory
  • Production_ProductSubcategory
  • Sales_SalesOrderDetail
  • Sales_SalesOrderHeader
  • Sales_SalesTerritory

Valintojen jälkeen napsautan Finish-painiketta.

Tietojen tuonti kestää jonkin aikaa. Kun saan ilmoituksen onnistumisesta (Success), niin napsautan Close-painiketta.

power6

Tietokannasta tuodut taulukot avautuvat PowerPivotiin omille välilehdilleen.

Taulukoiden väliset yhteydet

Näet taulukoiden väliset yhteydet valitsemalla Home-välilehdeltä View – Diagram View.

power7

Jos alkuperäisessä tietokannassa on määritelty taulukoiden väliset yhteydet, niin niitä ei tarvitse enää PowerPivotissa määritellä.

Esimerkiksi Sales_SalesOrderHeader ja Sales_SalesOrderDetail taulukoiden välillä on yhteys (relaatio). Sales_SalesOrderHeader-taulukon tilaus voi sisältää useita tilausrivejä. Tiettyyn tilaukseen liittyvät tilausrivit löytyvät tilausnumeron SalesOrderID perusteella Sales_SalesOrderDetail-taulukosta.

power8

Taulukoita yhdistävän viivan päässä esiintyvä 1 tarkoittaa, että taulukoita yhdistävän avainkentän arvot ovat ainutkertaisia. Esimerkiksi Sales_SalesOrderHeader taulukossa SalesOrderID-kentän arvot ovat ainutkertaisia eli kukin tilausnumero esiintyy taulukossa vain kerran.

Taulukoita yhdistävä viivan päässä esiintyvä * tarkoittaa, että taulukoita yhdistävä avainkentän arvot voi esiintyä taulukossa useitakin kertoja. Esimerkiksi Sales_SalesOrderDetail-taulukossa sama SalesOrderID-arvo esiintyy useita kertoja, jos tilaus sisältää useita tilausrivejä.

Pivot-taulukoita tietokannasta

Tietokantoja tuodaan PowerPivotiin, jotta niistä voidaan laskea erialaisia yhteenvetoja Pivot-taulukoinnilla. Voin aloittaa pivot-taulukoinnin joko PowerPivot-ikkunasta Home-välilehden PivotTable-toiminnolla tai Excel-ikkunasta Insert-välilehden PivotTable-toiminnolla. Jos aloitan Excel-ikkunasta, niin minun pitää varmistaa Create PivotTable -ikkunassa, että käytän työkirjan tietomallia (workbook’s Data Model)

power9

Pivot-taulukoinnin kenttäluettelosta (PivotTable Fields) löydät kaikki tietomallin taulukot ja niiden kentät.

Löydät PowerPivotiin ladatun tietokannan (tietomallin) ja yhden esimerkin pivot-taulukoinnista Excel-tiedostosta PowerPivot1.xlsx.

Tärkeää

Excel-ikkunasta pääset PowerPivot-ikkunaan valitsemalla PowerPivot-välilehdeltä Manage. Excel-ikkuna jää auki taustalle.

PowerPivotiin tuotu tietokanta tallentuu Excel-tiedoston mukana.

Voin vapaasti muokata PowerPivotin tietokantaa. Muutokset tallentuvat vain Excel-tiedostoon, ei alkuperäiseen tietokantaan.

Kun alkuperäiseen tietokantaan tulee muutoksia, niin voin päivittää PowerPivotiin tuodun tietokannan PowerPivot-ikkunan Home-välilehden Refresh-toiminnolla.

 

 

 

 

Mainokset