Power BI on täysverinen Business Intelligence -alusta SaaS-palveluna. Microsoft yritti aiemmin tarjota itsepalveluraportointia Power Query / Power View / SharePoint hässäkällä, mutta se oli (ainakin minusta) vaikeakäyttöistä ja sekavaa.
Power BI onkin sitten aivan toisella tasolla.
Käyttö on helppoa (varsinkin mikäli Excel on tuttu) ja työkalut ovat hyvät ja monipuoliset. Ainoa mitä Power BI:llä et voi tehdä ovat perinteiset pikselintarkat raportit. Mutta kuka niitä nykyään muutenkaan tekee?
Power BI -ratkaisu koostuu muiden Business Intelligence -ratkaisujen tapaan seuraavista osista:
- Ladataan tiedot eri tietolähteistä. Latausten yhteydessä tietoja tarvittaessa siivotaan.
- Ladatusta tiedosta muodostetaan tietomalli.
- Tietomallin pohjalta muodostetaan raportteja ja visualisointeja
- Raportit ja visualisoinnit julkaistaan muiden käyttäjien käytettäväksi
Vaikka yrityksessäsi olisikin jo käytössä perinteinen raportointiratkaisu, suosittelen silti tutustumaan Power BI:hin. Voit käyttää tietolähteinä olemassa olevia tietokantoja (jopa kuutioita), jolloin pääset nopeasti hyödyntämään Power BI:n erinomaisia visualisointityökaluja.
Esimerkki – henkilöautot
Käydään osa-alueet läpi esimerkin avulla. Minua kiinnostaa millaisilla autoilla suomalaiset ajavat. Minkä merkkisillä, ikäisillä, värisillä ja paljonko ajetaan keskimäärin vuodessa? Tiedot löytyvät helposti internetistä, mutta paljon mielenkiintoisempaa on selvittää se itse.
Lataa ensin työasemallesi Power BI Desktop.
Tietojen lataaminen
Tietolähteinä käytän kolmea tiedostoa:
- Trafin ajoneuvodataa (5+ miljoonaa riviä, csv)
- Trafin aineistokuvausta ja muuttujaluetteloa (excel)
- Kunnat.net tarjoamaa alueluokat ja kuntanumerot 2015 (excel)
Aloitetaan ajoneuvodatalla. Käynnistetään Power BI Desktop ja valitaan Get Data.
Valitaan tietolähteeksi csv ja yhdistetään se OneDrive -kansioon tallennettuun Trafin AvoinData 4,7 -tiedostoon. Get Data -näkymästä saa hyvän kuvan mitä kaikkea voi käyttää Power BI:n tietolähteinä.
Esikatselusta nähdään että csv-tiedoston mukana tulee otsikot ja lataus osaa ne parsia. Hyvältä näyttää.
Haluan kuitenkin siistiä ja muokata dataa. Painetaan Edit-painiketta joka avaa kyselyeditorin.
Kyselyeditori generoi PowerQuery -lauseita, joilla dataa muokataan. Lähdedata (tässä tapauksessa csv-tiedosto) pysyy siis aina muuttumattomana, PowerQueryllä dataa mainpuloidaan tiedon lataamisen yhteydessä. Voin korvata lähdetiedoston uudemmalla versiolla (kunhan sen muoto ei ole muuttunut) ja kaikki edelleen toimii.
Aivan aluksi valitsen kaikki turhat sarakkeet ja poistan ne.
Otan mukaan ainoastaan ajoneuvoluokat M1 ja M1G jotka pitävät sisällään henkilöautot. Rivien suodattaminen sarakkeen arvojen mukaan toimii aivan kuin excelissä.
Suodatetaan samalla tavalla mukaan ainoastaa rivit joissa ajoneuvonkäyttö = 1 (yksityinen käyttö) sekä rivit joissa matkamittarilukema on kerrottu (pois kaikki joiden arvo on null tai 0).
MerkkiSelvakielinen -saraketta on käytetty hieman luovasti (Ford, Ford-Ka, Ford ka…) joten pilkon sen osiin ensin ensimmäisen väliviivan kohdalta ja sitten uudestaan ensimmäisen välilyönnin kohdalta ja vielä kolmannen kerran kenoviivan kohdalta.
Näin mallimerkintä 1.1.1 sarakkeeseen jää jäljelle automerkki ilman turhia lisämääreitä.
Osa automerkeistä on kirjoitettu kokonaan isoilla kirjaimilla, joten yhdenmukaistetaan vielä kirjoitusasu:
Osassa automerkeistä on kirjoitusvirheitä, nämä voidaan korjata korvaa arvot -toiminnolla.
Aineistomme ydin on nyt valmis. Tehdyt toimenpiteet näkyvät kokonaisuudessaan Power Querynä Advanced Editorissa. Kyselyä voi myös muokata. Suosittelen ottamaan alkuperäisen talteen ennen kuin aloitat sillä kyselyn saa helposti solmuun.
Seuraavaksi lisätään kuntakoodit excelistä (New Source – Excel). Valitaan Alueluokat 2015 -välilehti mukaan. Näemme esikatselusta että sisältöä pitää siivota kyselyeditorissa.
Excelin yläosassa on erilaisia kuvaustekstejä. Poistetaan ensimmäiset 12 riviä:
Näyttää heti paremmalta. Asetaan ensimmäinen rivi otsikkoriviksi:
Vaihdetaan vielä Kuntanumero-sarakkeen otsikoksi pelkkä Kunta ja vaihdetaan sen tietotyyppi kokonaisluvuksi:
Kolmanneksi tietoaineistoksi otetaan autojen värikoodien selitykset jotka löytyvät Trafin aineiston kuvauksista. Luodaan näitä varten käsin taulu (Enter Data) ja kopioidaan kaksi saraketta siihen suoraan.
Tietolähteet ovat nyt kohdallaan joten sirrytään seuraavaan vaiheeseen. Kyselyeditorissa määritellyt toimenpiteet suoritetaan koko aineistoon hyväksymällä ne (Home – Close & Apply). Tämä kestää hetken.
Tietomalli
Power BI ei vielä tunnistanut tietolähteiden välisiä suhteita. Mennään Relationships -välilehdelle, valitaan Manage Relationships ja esiin tulevasta valikosta Autodetect Relationships.
Power BI huomaa että autojen värit lötyvät omasta taulustaan ja kuntien lisätiedot omastaan ja luo niiden välille suhteet. Tämähän alkaa jo näyttämään joltain!
Minua kiinnostaa autojen iät. Ajoneuvodatassa on mukana auton ensirekisteröintipäivä, josta se voidaan laskea. Luodaan hiiren oikealla uusi sarake jonka arvo määräytyy seuraavanlaisella kaavalla:
Aineiston tuoreimmat ensirekisteröinnit ovat 30.9.2016 (aineisto on päivätty 1.10.2016) joten lasketaan autojen iät verrattuna siihen. Päivämäärien erotus on päiviä, joten jakamalla se 365:llä saadaan erotus vuosina.
Lasketaan vielä autolla vuosittain ajetut kilometrit. Tämä on karkea arvio sillä emme tiedä missä kohtaa vuotta mittarilukema on otettu ylös. Auton ikä voi olla myös nolla jolloin vuoden ajomääräksi tulisi ääretön. Käytetään jakajana isomaa arvoista auton ikä ja 1.
Viimeistään tässä kohtaa huomaamme että aineistossa on (luonnollisesti) myös virheitä. Kolmella autolla on ajettu negatiivisia kilometreja ja lukuisilla autoilla on ajettu alle 10km.
Kaikki datan siivoaminen tapahtuu tietojen lataamisvaiheessa kyselyeditorilla. Voimme muokata kyselyjä missä vaiheessa tahansa kun keksimme logiikan jolla haluamme aineistoa siivota. Esimerkiksi poistaa aineistosta kaikki rivit joissa matkamittarilukema on alle 0.
Tulemme käyttämään visualisoinnissa karttapohjaa. Pelkän kunnan nimen käyttö ei toimi sillä samannimisiä paikkoja on muuallakin kuin vain Suomessa. Tehdäänkin uusi sarake, jossa kunnan nimen perään lisätään maatunnus.
Visualisointi
Aloitetaan tiedon visualisointi lisäämällä Report-välilehdelle TreeMap. Asetetaan ryhmiksi automerkkien selväkieliset nimet ja arvoiksi automerkkien selväkielisten nimien lukumäärät. Nyt meillä on siisti visualisointi josta nähdään helposti miten autokanta jakautuu eri automerkkien kesken.
Seuravaksi halutaan kuvaaja josta näkee miten autojen iät jakaantuvat. Lisätään kaavio (Line and stacked column chart), jonka vaaka-akselilla on autojen iät ja arvoina autojen lukumäärät.
Huomataan että 114 vuoden ikäisiä autoja on yllättävän paljon. Aineistoa tutkimalla selviää että nämä ovat autoja joiden ensirekisteröintipäivä on tyhjä. Filtteröidään kuvaajaa siten että esitetään ainoastaan alle 35 vuoden ikäiset autot.
Lisätään toinen kuvaaja josta nähdään miten eri ikäisillä autoilla ajetaan vuodessa ja mitkä ovat niiden keskimääräiset Co²-päästöt. Oleellista on vaihtaa vuosikilometrit ja Co²-päästöjen arvot keskiarvoiksi (oletus on summa).
Visualisoinnit ovat automaattisesti kytköksissä toisiinsa. Kun valitaan TreeMapista Volvo, niin yläosan kuvaajat näyttävät Volvojen luvut (tumman vihreällä). Nähdään helposti että Volvoilla ajetaan enemmän kuin autoilla keskimäärin.
Lisätään raportille:
- mittari (Gauge): keskimääräiser Co²-päästöt (average of Co²)
- mittari: keski-ikä (Average of Auton_ika)
- numero (Card): autojen lukumäärä (Count of jarjno)
- numero: keskimääräinen vuosittainen ajo (average of km_vuodessa)
Lisätään piirakkakaavio (Pie Chart) kuvaamaan eriväristen autojen osuuksia. Värien selkokieliset nimet löytyvät Autojen värit -taulukosta. Lukumäärät saadaan ottamalla mukaan jarnro-sarakkeen arvojen lukumäärä. Vaihdetaan piirakan värit vastaamaan autojen värejä.
Lopuksi lisätään kartta johon on merkitty ajoneuvojen todennäköiset käyttökunnat. Pallon halkaisija kertoo ko kunnalla olevien autojen lukumäärän ja värisävy niiden keskimääräisen iän.
Visualisointi on nyt valmis.
Aineistomme mukaan Suomessa on noin 2.26 miljoonaa yksityiskäytössä olevaa henkilöautoa. Merkkien kärki on Toyota, Volswagen, Volvo ja Ford. Autojen keski-ikä on 11.66 vuotta ja keskimääräinen ajosuorite 20tkm vuodessa.
Kuvaajista näemme helposti että aineistossa (tai siinä miten käytän sitä) on jotain pielessä. Alle 2-vuotiailla autoilla ajetaan keskimäärin 120tkm vuodessa. Alle 4-vuotiaita autoja on myöskin selvästi vähemmän kuin yli 4-vuotiaita.
Tehokkaat visualisointityökalut tuovat nämä virheet nopeasti esille.
Raportin julkaisu
Julkaistaan raportti lopuksi Power BI -palveluun, jolloin sen voi muun muasssa jakaa muiden käyttöön.
Julkaistu raportti näyttää tältä.
Se toimii samalla tavalla kuin Power BI Desktopilla, kaikki visualisoinnit ovat yhteydessä toisiinsa. Kun klikkaat 5-vuotiaiden autojen pylvästä, näet saman tien 5- vuotiaiden autojen lukumäärän, keskimääräiset ajokilometrit jne.
Voit tutkia valmista raporttia suoraan tästä.
Mikäli Power BI kiinnostaa sinua enemmän niin suosittelen tutustumaan Heidi Enhon erinomaiseen HExcelligence.fi -blogiin.
Tämä kirjoitus on osa laajempaa sarjaa jossa käyn läpi Office 365:n työkaluja. Mistä niissä on kyse ja miten niitä voisi hyödyntää.
Edit: Korjauksia Trafin avustuksella
Jäi häiritsemään nuo selvästi virheelliset luvut (missä ovat 0-3 vuotiaat autot ja miksi niillä on ajettu niin kauhean paljon?). Kysyin asiasta Trafi:lta ja heidän asiantuntijan vastaus johdattikin minut nopeasti oikeille jäljille. Eli missä olin mokannut.
- Suodatin alussa pois kaikki rivit joissa matkamittarilukema on tyhjä tai nolla. Oletin että näistä riveistä on kilometrit jäänyt syöttämättä ja rivit sekoittaisivat laskentaa. Osassa riveissä varmaan näin, mutta nämä 0km rivit pitävät sisällään suuren osan uusista autoista (ensirekisteröinti ilmoitus tms). Niitä kun ei katsasteta ensimmäisinä vuosinaan. Poistin tämän suodatuksen ja käppyrät oikenivat oitis loogisempaan asentoon.
- Auton ikää laskiessa käytin ensirekisteröintipäivää. Oletin että se on se ensimmäinen päiväys. Aineistosta käy kuitenkin ilmi että monesti käyttöönottopäivä on vuosia aiemmin kuin ensirekisteröinti (auto ollut välillä poissa rekisteristä tai tuotu ulkomailta?). Korjasin myös tämän.
Nyt uudehkoja autoja on järkevän näköinen määrä. Niillä on lukujen mukaan ajettu kovin vähän, mikä selittyy sillä ettei mitarilukemia ole päästy ottamaan ylös katastuskonttorilla.
Samalla laski kauttaaltaan autojen keskimääräinen vuosittainen ajosuorite.Tämä johtuu siitä että vaihtaessani autojen iän laskentatapaa, päivittyi autokannan keski-ikä 6kk vanhemmaksi.
Timo Pertilä 