Kuva: Tesla Motors

Voit aloittaa Power Pivotiin tutustumisen tekemällä esimerkiksi Trafin julkaisemasta avoimesta ajoneuvodatasta Power Pivot tietomallin. Jaoin Hexcelligentissä kesäkuussa valmiin Excel-työkirjan Trafin datan tutkimiseen, joten tämänkertaisen jutun tarkoituksena on kertoa askel askeleelta miten teet vastaavan analysointityökirjan itse Power Pivotin avulla. Saatatpa samalla oppia käyttämään välinettä myös johonkin hyödyllisempään.

[EDIT 03/2015: Tämä blogi on kirjoitettu keväällä 2014 julkaistun data-aineiston pohjalta. Trafi julkaisi 01/2015 uuden 3.0 -version datasta, joten blogissa kuvattu data poikkeaa hieman uusimmasta. Voit ladata 3.0 aineiston käsittelyyn tarkoitetun työkirjan Trafin uusi avoin ajoneuvodata 3.0 -jutusta.]

Teet tämän jutun ohjeilla Power Pivotissa tietomallin, jonka avulla voit analysoida suomalaista ajoneuvokantaa esimerkiksi pivot-taulukoina ja Power View -kaavioina.

Seuraamalla ohjeita

1. lataat tiedot Excelin tietomalliin,
2. luot taulujen väliset yhteydet,
3. muokkaat alkuperäisiä kyselyitä,
4. viimeistelet tietomallin poistamalla sekä piilottamalla analysoinnin kannalta turhia sarakkeita,
5. tarkistat tietotyypit
6. sekä opit päivittämään tiedot.

Tulevissa jutuissa kerron muista tietolähteistä, Power Pivotin lasketuista sarakkeista, lasketuista kentistä sekä raportointimahdollisuuksista.

Tarvitset Excelistä 2010, 2013 tai 2016 –version sekä Power Pivotin. Lue ensin ohjeet Power Pivotin aktivoinnista.

Trafin aineisto

Trafin jakama aineisto muodostuu kahdesta tiedostosta, jotka sinun pitää ensin ladata Trafin sivuilta.

Jos haluat lähinnä opetella tietomallin tekemistä, voit käyttää siihen myös tätä suppeaa otosta, jossa on vain noin 300 000 satunnaisesti valittua ajoneuvoa: Suppea testiaineisto Kun teet tietomallin suppean aineiston avulla, voit myöhemmin päivittää tilalle Trafin koko datan.

• Avoindata.csv sisältää kaikkien Suomessa liikennekäytössä olevien ajoneuvojen tekniset tiedot. Datassa on runsaasti koodeja, jotka kuvaavat erilaisia tietoja, kuten ajoneuvotyypin, värin, polttoaineen, jne. Trafin aineistossa on yli 5 miljoonaa riviä eli ajoneuvoa, joten tiedostoa ei voi avata Exceliin sellaisenaan. Power Pivotissa puolestaan ei ole rivirajoitusta ja sen avulla Exceliin saa analysoitavaksi huomattavan suuria aineistoja. Ainoastaan Excelin bittisyys sekä käytössä oleva muistin määrä rajoittavat aineiston käsittelyä Power Pivotissa.
• Koodisto.xlsx sisältää edellä mainitun datan koodien kuvaukset. Koodistossa on yli 8 000 riviä, jotka on ryhmitelty KOODISTONKUVAUS-sarakkeen avulla seuraaviin ryhmiin: Kuntien numerot ja nimet (650 kpl), Ajoneuvomerkit (6363 kpl), Direktiivien mukainen kooditus jossa huomioitu myös kansalliset ajoneuvoluokat (165 kpl), Ajoneuvoluokkaa tarkempi luokittelu ajoneuvoille (384 kpl), Ajoneuvon käyttö (15 kpl), Ajoneuvon väri (39 kpl), Korityyppi (154 kpl), Ohjaamotyyppi (12 kpl), Polttoaine (164 kpl), Vaihteistotyyppi (18 kpl), Voimanvälitys ja tehostamistapa (51 kpl). Koodistossa haastavaa on se, että kaikki eri koodiryhmät on listattu yhtenä luettelona, joten luettelosta pitää ladata tiedot Power Pivotiin useiden kyselyiden avulla, jotta ne saadaan erillisiksi tauluiksi, kuten Kunnat-taulu tai Ajoneuvojen väri -taulu.

Pura zip-tiedosto ja tallenna molemmat tiedostot samaan paikkaan. Tarvitset niitä hetken kuluttua.

Avoindata-tiedoston lataus Power Pivotiin

Trafin data sisältää yli 5 miljoonaa riviä, joten 32-bittisen Excelin muistiavaruus ei riitä koko aineiston käsittelyyn. Suurimmalla osalla käyttäjistä on 32-bittinen Excel, joten kerron ohjeessa miten lataat aineistosta pienemmän osajoukon. Jos sinulla on 64-bittinen Office ja työasemassasi riittävästi muistia, voit ladata koko datan. Löydät bittisyyden tarkistamiseen ohjeen tästä Power Map -jutusta.

1. Aloita uusi Excel-työkirja ja siirry Power Pivotin puolelle valitsemalla PowerPivot > Manage (PowerPivot > Hallinta). Excel 2010 –versiossa painikkeessa lukee Power Pivot Window (PowerPivot ikkuna).
2. Valitse Power Pivotissa Home > From Other Sources (Aloitus > Muista lähteistä) ja valitse listalta vaihtoehto Text File (Tekstitiedosto) ja siirry ohjauksen seuraavaan vaiheeseen.
3. Etsi Browse (Selaa) –painikkeella Avoindata.csv –tiedosto ja valitse se.
4. Varmista, että Power Pivot ymmärtää ensimmäisen rivin otsikoiksi ja että sarake-erottimeksi on tunnistettu pilkku.
5. Kokeile ensirekisteröintipäivän perusteella suodattamalla miten pitkältä aikajaksolta Power Pivot pystyy lataamaan dataa sinun työasemassasi. Esimerkiksi 1.1.2010 lähtien.
   Tässä vaiheessa voi vaikuttaa, että Excel hyytyy täysin, mutta odota silti rauhassa suodatusikkunoiden avautumista. Jos esimerkiksi suodatat ajoneuvot vuoden 2010 alusta lähtien, aineiston rivimäärä on hieman yli miljoona. Voit rajoittaa tulosjoukon kokoa myös suodattamalla mukaan vain pelkät henkilöajoneuvot eli ajoneuvoluokat M1 ja M1G, jolloin voit ladata dataa pidemmältä aikaväliltä.
6. Kokeile aineiston latausta valitsemalla Finish (Valmis). Testatessasi aineiston latausta Power Pivotiin, lataus voi kestää useita minuutteja. Muistin täyttyessä Excel voi myös kaatua.
7. Excel näyttää lopulta miten monta riviä Power Pivotiin latautui.

Tässä vaiheessa työkirja kannattaa tallentaa. Sekä tallennus että Excelin puolelle siirtyminen löytyvät ikkunan vasemmasta yläkulmasta pikatyökaluriviltä. Tallennuksen jälkeen pääset takaisin Power Pivotiin PowerPivot-välilehden ensimmäisellä painikkeella.

Jos vertaat alkuperäisen datatiedoston ja muodostuneen Excel-tiedoston kokoa, huomaat Power Pivotin pakkaavan tiedoston hyvin tehokkaasti!

Kyselyn muuttaminen

Jos haluat muuttaa alkuperäistä kyselyä (esim. tuoda pienemmän rivimäärän tai poistaa turhia sarakkeita), valitse Design > Table Properties (Rakenne > Taulukon ominaisuudet). Valitse lopulta Save (Tallenna). Myös kyselyn muuttaminen on melko hidas operaatio näin suurella aineistolla, joten odota rauhassa vaikka Excel vaikuttaa ettei se reagoi.

Luettelon käsittely Power Pivotissa

Voit suodattaa, lajitella ja tutkia aineistoa Power Pivotin suodatusnuolten avulla. Nämä taulukoissa tehtävät toimenpiteet eivät muuta alkuperäistä kyselyä. Vaikka tiedon lataus ja kyselyn muuttaminen ovat hitaita operaatioita, havaitset varmasti, että aineiston käsittely on hämmästyttävän nopeaa, sillä Power Pivotissa aineiston suodatukset ja lajittelu suorittaa Power Pivot (eli xVelocity, työasemaasi asentunut SQL Server Analysis Services run-time versio) ei siis Excel. Kokeile esimerkiksi rivien lajittelua, 5 miljoonaa riviä vaihtavat järjestystä silmänräpäyksessä!

Pystyisit tekemään jo tästä aineistosta pivot-taulukoita ja voit mainiosti kokeilla, mutta ne eivät ole kovin informatiivisia, sillä sarakkeet sisältävät lähinnä vain numeerisia koodeja (ajoneuvoryhmäkoodi, ajoneuvon käyttökoodi, korityyppikoodi jne.).

Varmaankin haluat koodien sijaan käyttää Koodisto.xlsx –työkirjassa olevia kuvailevia tekstejä, joten jatketaanpa mallin rakentamista. Trafin datan rakenteesta johtuen seuraava vaihe vaatii 11 eri latauksen tekemisen, joten tallenna työkirja tässä vaiheessa ja hae kuppi kahvia.

Koodiston lataus Power Pivotiin

Koodisto sisälsi kaikki koodit yhdessä luettelossa ryhmiteltyinä KOODISTOKUVAUS-sarakkeen perusteella. Ryhmiä on yhteensä 11, joten sinun pitää toteuttaa 11 kappaletta kyselyitä, jotta saat kunkin ryhmän omana taulunaan ja pystyt yhdistämään taulun vastaavaan data-aineiston kenttään eli sarakkeeseen.

1. Kun teet ensimmäistä kyselyä, valitse Power Pivotissa komento Home > From Other Sources (Aloitus > Muista lähteistä) ja valitse listalta vaihtoehto Excel File (Excel-tiedosto) ja siirry ohjauksen seuraavaan vaiheeseen.
2. Etsi jälleen Browse (Selaa) –painikkeella Koodisto.xlsx –tiedosto ja rastita Use first row as column headers (Käytä ensimmäistä riviä sarakeotsikoina) ja siirry seuraavaan vaiheeseen.
3. Tiedot ovat ensimmäisessä taulukossa, joten valitse Taul1$ ja siirry Preview & Filter (Esikatsele ja suodata) -toiminnolla suodattamaan taulukosta esimerkiksi pelkät Kuntien numerot ja nimet. Poista muut valinnat.
4. Suodata myös vain suomenkieliset rivit mukaan. Kun myöhemmin yhdistämme tauluja toisiinsa, kooditauluissa ei saa olla samaa koodintunnusta useita kertoja.
5. Hyväksy OK:lla ja vaihda taulun nimeksi esimerkiksi Kunnat. Nimen voi vaihtaa Power Pivotissa myös latauksen jälkeen.
6. Suorita lataus Finish (Valmis) –painikkeella.

Power Pivotissa on nyt kaksi taulua: Avoindata ja Kunnat. Vaihda Avoindata-taulun nimeksi Ajoneuvodata pikavalikon avulla.

Huomaa! Power Pivotissa on tärkeää nimetä taulut ennen kuin ryhdyt tekemään esimerkiksi kaavoja. Tiettyjen nimien muuttaminen jälkeenpäin voi aiheuttaa runsaasti lisätyötä.

Nyt alkaa se tylsin kahvia vaativa vaihe. Nouda samalla tavalla kaikki muut 10 koodiryhmää, mutta käytä hakemiseen komentoa Home > Existing Connections (Aiemmin luodut yhteydet), valitse Koodisto ja Open (Avaa), valitse jälleen Taul1$ ja suodata seuraava koodiryhmä ja sen suomenkieliset rivit ja anna taululle nimi. (Excel 2010 -versiossa Existing connetions -komento on toisella välilehdellä.)

Kun haet tietolähteestä, mistä olet jo kerran hakenut, käytä aina edellä mainittua komentoa, jotta et muodosta useita erillisiä yhteyksiä (connections) samaan tietolähteeseen. Kun tietolähde eli tässä tapauksessa tiedosto siirtyy toiseen paikkaan tai sen nimi vaihtuu, riittää että muutat yhtä tietolähdettä yllä olevan ikkunan Edit (Muokkaa) –komennolla.

Tallenna työkirjasi kunkin taulun luonnin jälkeen.

Anna tauluille esimerkiksi seuraavat nimet:

Koodistossa oleva ryhmä	Taulun nimi
Ajoneuvomerkit	Ajoneuvomerkit
Ajoneuvon käyttö	Ajoneuvon käyttö
Ajoneuvon väri	Ajoneuvon väri
Direktiivien mukainen luokitus, jossa huomioitu myös kansalliset ajoneuvoluokat.	Ajoneuvoluokka
Ajoneuvoluokkaa tarkempi luokittelu ajoneuvoille	Ajoneuvoluokan tarkenne
Korityyppi	Korityyppi
Ohjaamotyyppi	Ohjaamotyyppi
Polttoaine	Polttoaine
Vaihteistotyyppi	Vaihteistotyyppi
Voimanvälitys ja tehostamistapa	Voimanvälitystapa

Tämän operaation jälkeen Power Pivot -työkirjassasi pitäisi olla 12 taulua: varsinainen datataulu + 11 koodistotaulua, jotka sisältävät kunkin ryhmän suomenkieliset koodit.

Muista tallentaa ennen jatkamista, jotta et menetä tietoja! Jos työkirjan koko on työasemasi muistin äärirajoilla, Excel on herkkä kaatumaan.

Taulujen välisten yhteyksien muodostaminen

Tällä hetkellä taulujen välillä ei vielä ole mitään älykkyyttä. Ne ovat vain erillisiä tauluja eikä Power Pivot ymmärrä miten esimerkiksi Ajoneuvon väri –taulu liittyy Ajoneuvodata-tauluun. Jos käsittelisit tällaisia tauluja normaalisti Excelissä ja haluaisit muodostaa tiedoista pivot-taulukoita, joutuisit keräämään tiedot Ajoneuvodata-tauluun VLOOKUP (PHAKU) –funktioilla, jotka ovat hyvin hitaita suurissa aineistoissa ja kasvattavat lisäksi tiedostokokoa.

Power Pivotissa tietoja ei tarvitse kerätä yhteen tauluun, vaan riittää että taulujen välille luodaan yhteydet. Tässä vaiheessa on hyvä, jos sinulla on ymmärrystä relaatiotietokannoista ja taulujen välisistä yhteyksistä, mutta älä huoli jos ei ole. Saat tietomallin kuntoon tämän ohjeen perusteella vaikket relaatiotietokannoista mitään tietäisikään.

Power Pivotissa on taulukkonäkymän lisäksi Diagram (Kaavionäkymä) –näkymä, jota käytetään yhteyksien muodostamiseen. Näkymäpainikkeet ovat ikkunan oikeassa alakulmassa ja Home (Aloitus) –välilehdellä. (Jos sinulla ei ole näkymäpainikkeita, työasemassasi on ikivanha Power Pivot -versio, joka on syytä päivittää uudempaan.)

Siirry Diagram (Kaavionäkymä) –näkymään ja sijoittele taulut vetämällä siten, että Ajoneuvodata on muiden taulujen keskellä, sillä kaikki muut taulut tullaan yhdistämään siihen.

Esimerkiksi Ajoneuvodatan ajoneuvoluokka-kenttä pitää yhdistää Ajoneuvoluokka-taulun KOODINTUNNUS-kenttään, kuten yllä olevassa kuvassa on jo tehty.

Muodostat yhteyden vetämällä KOODINTUNNUS-kentän ajoneuvoluokka-kentän päälle. Muistithan suodattaa kooditauluihin vain suomenkieliset rivit. Jos kooditaulussa on sama koodi useita kertoja, yhteyttä ei voi muodostaa. VLOOKUP (PHAKU) -funktiokaan ei pystyisi hakemaan tietoja, jos hakutaulussa olisi useita vastineita. Kun yhdistät tauluja toisiinsa, yhteyden toisessa päässä saa olla vain yksilöllisiä arvoja.

Muodosta seuraavat yhteydet (tallennathan tässäkin vaiheessa usein):

Ajoneuvodata-taulun	KOODINTUNNUS taulusta
Ajoneuvoluokka	Ajoneuvoluokka
Ajoneuvonkaytto	Ajoneuvon kaytto
Tälle kentälle ei löydy vastinetta Ajoneuvodata-taulusta ??Ajoneuvodata-taulussa on sen sijaan kenttä merkkiSelvakielinen (tämä on aineiston hankalin kenttä, sillä ajoneuvomerkit on kirjoitettu hyvin sekalaisissa muodoissa)	Ajoneuvomerkit
kunta	Kunnat
korityyppi	Korityyppi
ohjaamotyyppi	Ohjaamotyyppi
kayttovoima	Polttoaine
ajoneuvoryhma (näin ainakin minä päättelin)	Ajoneuvoluokan tarkenne
voimanvalJaTehostamistapa	Voimanvalitystapa
vaihteisto	Vaihteistotyyppi
vari	Ajoneuvon vari

Lopulta sinulla pitäisi olla tietomalli, jossa lähes jokainen taulu on yhdistetty ajoneuvodata-tauluun.

Ehkäpä tässä vaiheessa on syytä mainita, että Trafin data on hyvin sekalaista ja osin virheellistä. Aineistosta löytyy 419-ovisia ajoneuvoja ja esimerkiksi ajoneuvojen merkit on kirjoitettu monissa eri muodoissa.

Tietomallin viimeistely

Turhien tietojen poistaminen

Tietomallista kannattaa aina poistaa tarpeettomat tiedot. Kun poistat sarakkeita, muutat alkuperäistä kyselyä. Poistot olisi voinut tehdä myös alkuperäisen  kyselyn luonnin yhteydessä.

Saat poistettuja sarakkeita takaisin komennolla Design > Table Properties (Rakenne > Taulun ominaisuudet):

Suosittelen poistamaan kaikista kooditauluista lyhyen selitteen, järjestysnumeron, koodistonkuvauksen ja kieli-sarakkeen.

Teknisten sarakkeiden piilottaminen

KOODINTUNNUSta ei voi poistaa, sillä sen avulla muodostetaan yhteys ajoneuvodata-tauluun, mutta senkin voi piilottaa, jottei se näy turhaan Excelin puolella pivot-taulukon kenttäluettelossa, kun muodostat pivot-taulukoita. Piilotetut kentät näytetään Power Pivotissa harmaina.

Myös Ajoneuvodata-taulusta kannattaa piilottaa ne kentät, jotka sisältävät koodeja ja joiden tarkoitus on vain toimia yhdistävänä kenttänä kooditauluihin. Tämä kaikki tehdään, jotta tietomallia on mukava käsitellä Excelin pivot-taulukoissa.

Piilotuksia voi tehdä myös Diagram-näkymässä:

Kenttien nimeäminen

Lisäksi kunkin taulun PITKASELITE-kentän nimi on hyvä muuttaa kuvaavaksi kaksoisnapsauttamalla kentän (sarakkeen) nimeä. Tässä aineistossa PITKASELITE-kentälle voi antaa saman nimen kuin taululle on annettu. Esimerkiksi Vaihteistotyyppi-taulun PITKASELITE nimetään Vaihteistotyyppi-nimiseksi. Halutessasi voit nimetä myös kaikki Ajoneuvodata-taulun kentät fiksummin.

Nyt tietomallista on miellyttävämpi tehdä pivot-taulukoita, kun mallissa ei näy turhia kenttiä ja ne on nimetty järkevästi.

Tietotyypit

Lopuksi kannattaa vielä tarkistaa, että kunkin sarakkeen tietotyyppi on tunnistettu oikein. Voit myös muotoilla sarakkeita.

Yhteyksien päissä olevien kenttien tietotyypit

Huomaa! Jotta raportit antavat oikeita tuloksia, yhteyksien päissä olevien kenttien pitää olla samaa tietotyyppiä. Sinun pitää siis käydä muuttamassa seuraavien taulujen KOODINTUNNUS-kenttä kokonaisluvuksi, kuten se on ajoneuvodata-taulussakin: Kunnat, Ajoneuvon käyttö, Ajoneuvoluokan tarkenne, Ohjaamotyyppi ja Voimanvälitystapa.

Datan päivitys

Kun Trafi julkaisee seuraavan data-aineiston voit päivittää tiedot Home > Refresh (Aloitus > Päivitä) –toiminnolla.

Raportointi

Ja sitten vain pivotteja tekemään:

Teslojahan on Suomessa tällä hetkellä 61:

Pysy kanavalla, sillä tämän jutun myötä aloitin Excel Self-Service BI kirjoitussarjan. Tulen kirjoittamaan syksyllä useita Power Pivotiin, Power View –kaavioihin, Power Query –hakuihin ja Power BI:hin liittyviä juttuja, ohjeita ja tarinoita.

PS. Käytän 64-bittistä Exceliä ja koko aineiston käsittely sujui mainiosti. Kerropa kommenteissa, miten suuren aineiston (esim. aikavälin) pystyit lataamaan 32-bittiseen Exceliin.j