Tietokanta- ja ohjelmistotaustaisena insinöörinä olen tottunut ratkomaan ongelmia ja luomaan ratkaisuja. Isänä olen tottunut rakentelemaan lasten kanssa legoja. Huhtikuisen datatehtäväni kanssa huomasin näillä asioilla yllättäviäkin yhtäläisyyksiä.
Kookax Oy toimii terveydenhuollon dataratkaisujen parissa, joten todellinen tehtävä liittyi asiakkaamme laskutusratkaisuihin ja laskutusdatan virheiden korjaamiseen. Tästä meillä syntyi ajatus, että tiettyjä toimintoja kannattaa automatisoida niin, että asiakas saa korjattavan datan itselleen helposti käsiteltävään muotoon.
Niinpä sain tehtävän erotella datasta tiedot yksikkökohtaisesti ja tuottaa niistä jokaiseen yksikköön oman Excelinsä. Tässä blogissa käyn läpi kuvitteellisen ja lyhyen esimerkkidatan kautta lävitse yhden ratkaisuvaihtoehdon. Samalla tulen kertoneeksi teille, miksi datan käsittely tuntuu joskus legojen rakentelulta.
Lähdetään datasta
Blogin esimerkkiä varten rakentelin tietokantataulun ASIAKASLASKUTUS. Esimerkissämme on vain kymmenisen riviä ja muutamia tietoja per rivi, mutta toteutusmalli toimii hyvin myös isommalla datamäärällä. Testidatamme liike voisi olla yhtä hyvin yksityinen rautakauppa, työvälinekauppaketju kuin kunnallinen liikelaitos tai sairaanhoitopiiri. Yhtenäisestä koko yrityksen tai laitoksen kattavasta datasta on tarkoitus erotella kutakin yksikköä koskeva data heille käsiteltäväksi tiedostoksi jatkotoimia varten. Näin kukin yksikkö muokkaisi vain omia tietojaan ilman osaamista tietokannoista.
Lähtökohtamme on siis yhtenäinen data, joka tavoitteen mukaan pitäisi jollakin tavalla pilkkoa osiin. Tästä saamme ensimmäisen Lego-vertauksen. Käsissämme on aikanaan tietyllä ohjeella kasattu valmis kokonaisuus, joka pitää nyt purkaa. Irrotetuista osista tulee kasata uudenlaisia pienempiä kokonaisuuksia, kenties lisäämällä niihin osia.
Purkamista ja pilkkomista
Datamme on SQL-tietokannassa, joten sieltähän se on lähdettävä liikkeelle. Esimerkkidatassamme haluamme erotella tulokset myyjäyksikön perusteella. Ratkaisua rakentaessani testailin useita vaihtoehtoja, joista osan hylkäsin tarpeettomina tai tehottomina. Kokeilin muun muassa SQL-kyselynä rakentaa yhdestä taulusta useamman ja nimetä uuden taulun yksikön nimen mukaisesti lennosta. Tämä toimi varsin mukavasti ja on hyödyllistä jatkossa. Tässä vaiheessa talteen jäi lyhyitä skriptin pätkiä ja SQL-kyselyitä sekä muuttujia hyödynnettäväksi muissa projekteissa. Ajatuksena oli tehdä SQL Server Management Studiossa rutiini, joka veisi kyseiset taulut Exceliksi suoraan, mutta tämä ei kuitenkaan varsinaisesti toiminut tarkoituksen mukaisesti. Kirjoitin myös taulun, joka erittelee uuteen tauluun eri yksiköt, joista löytyy tallennettavaa tietoa. Toisin sanoen, eri palikoita yhdistellen ja erotellen kun lopputulos ei olekaan mieleinen – aivan kuin kasaisi pojan kanssa legoja.
Mitä sitten jäi käteen pilkkomisvaiheesta paitsi hylättyjä testailuita ja SQL-kyselyitä?
- Varmuus siitä, että yhdestä datasta pitää erotella muuttujaksi yksilöivä erottava data.
- Käsitys siitä, että talteen otetun tiedon perusteella tulee tehdä toistoja uuden datan tuottamiseksi
- Oletus, että työ kannattaa tehdä SSIS-skriptinä.
Nyt rakennuspalikoina oli siis alkuperäinen yhtenäinen data, sen osia erotteleva SELECT DISTINCT -lause, ja tavoitteena luoda skripti, joka veisi datan kantataulusta Exceliin tietyin ehdoin.
Legot olivat siis löytämässä ensimmäisiä uusia lisäosiaan. Datainsinöörin mielestä asiat pitää rakentaa yksinkertaisesti, joten totta kai hyödynnämme olemassa olevia työkaluja ja niiden automatisoituja osia. Mutkat suoriksi ja Management Studiossa tehtävää luomaan, joka lataa kannasta dataa ulkopuoliseen kohteeseen ja tallennetaan se skriptiksi, jota voisi jatkossa monistaa.
Uuden ratkaisun peruspilari
Halutun datan viennistä Exceliksi ja sen tallentamiseen skriptiksi löytyy Management Studiossa hyvät automatisoidut ominaisuudet. Ei muuta kuin niiden kautta luomaan varsin helposti SSIS -projekti, joka toteuttaa haluttua tehtävää.
Tässä vaiheessa optimisti olettaisi olevansa jo pitkällä tehtävänsä suorittamisessa, koska lopputulemana oikeasti saadaan luotua skripti, joka tekee oikean näköisen Excel-tiedoston. Käytäntö kuitenkin opetti monen purkamisen ja uudelleen rakentamisen kautta, että monistettavuus ei olekaan kaikkein yksinkertaisin toteutettava.
Sen verran olin kuitenkin oikeassa, että peruspilarina automatisoitu skriptin luonti toimii. Luontivaiheessa Excelin sijaan kannattaa kuitenkin luoda Flat file, eli esimerkiksi .txt tai .csv-tiedosto. Tällaista erotinmerkein eroteltua dataa voi hyödyntää useissa eri välivaiheissa edestakaisin. Niinpä tuossa Export Data -vaiheessa kannattaa suosiolla rakentaa jokin muu kuin Excel-tiedosto ja keskittyä sen ratkaisemiseen myöhemmin.
Nyt peruspilari, legotermein rakennusalusta, on paikoillaan ja vieläpä automatisoidusti. Saamme käsiimme erotinmerkein erotellun datan, jonka voi avata Excelissä, vaikkei se vielä Excel olekaan.
Entä sitten? Se on edelleen vain yksi tiedosto
Niin onkin. Olemmeko siis päässeet yhtään sen pidemmälle? Mitä hyötyä tästä on? Tosiasiassa tässä ollaan hyvin pitkällä, vaikkakin meillä on olemassa vasta toiminnallisuus, joka vie datan yhteen tiedostoon. Kaikkein monimutkaisimmat asiat on todellisuudessa jo tehty ja tiedämme, mihin suuntaan pyrimme toteutustamme kehittämään muodostaaksemme halutun lopputuloksen.
Yksittäinen datatehtävä on pelkistetysti näin simppeli: Hae dataa kannasta ja kirjoita se tiedostoon. Tämä meillä on käsissämme jo valmiina.
Nyt, jotta tästä päästäisiin etenemään, tarvitsee rakentaa lähdemuuttujaan perustuva silmukka, jota toistetaan niin useasti, kun erilaisia lähdemuuttujia, eli esimerkissämme myyjäyksiköitä on olemassa. Tässä kohtaa voin tunnustaa, että tehtävääni aloitellessa uskoin tämän olevan hallitsevin, oleellisin ja haastavin elementti.
Kuinka väärässä olinkaan. Visual Studio tarjoaa tähän helpot ja insinöörin mielestä loogiset työkalut, joihin täytyy rakentaa vain muutama poikkeus. Kuten tekstin alkuosa jo näyttää, odottamattomia ongelmia sen sijaan tuli aiemmissa vaiheissa ja sekä kohde- että lähdedataa täytyi työstää eri muotoihin useampaan kertaan ennen kuin varsinaisen ehtosilmukan tekemisestä oli mitään hyötyä. Aivan kuten legoissakin, pala kerrallaan kannattaa edetä, kiirehtimättä lopputulokseen.
Erillisten lopputulosten lähtökohta
Olemassa oleva ja automaattisesti luotu tiedonvienti tietokannasta tiedostoon sijoitetaan ehtosilmukan sisään. Tähän luodaan SQL-kyselyllä ehtolause, joka välittää muuttujana dataa käsiteltäväksi. Näiden ominaisuudet muokataan luomaan tätä haluttua lopputulosta. SQL-lauseeksi annetaan haku, joka määrittelee datastamme erilliset myyjäyksiköt ja välittää sen tulokset muuttujaksi, joka annetaan parametrinä toistettavaan tiedonvientiin. Tämän perusteella haluttu toiminto luodaan niin monta kertaa kuin erillisiä yksiköitä eli erillisiä haluttuja tiedostoja tulee olemaan.
Tämä on se ydinosa, johon ratkaisumme tulee perustumaan. Uusiokäytetyt legopalikat alkavat löytää uuden järjestyksensä, vaikka ne useissa testivaiheissa olivat sekaisin levällään kuin lastenhuoneessa konsanaan. Kehitystyön suurin opetus suoraviivaiselle datainsinöörille oli, että suorin tie ei aina ole helpoin tie. Yksinkertainen ja toimiva voidaan ratkaista myös mutkan kautta. Legotermein, kaikkia vihreitä palikoita ei kannata käyttää kerralla, niitä voi sijoittaa eri kohtiin ratkaisua – myös sinisten legojen sekaan.
Pientä säätöä peruspalikoiden päälle
Ehtosilmukka ja tiedonvälitys silmukkaan on hyvin kohdilleen asettuvia legopalikoita ja tarvittavat muutoskohdat löytyvät viimeistään yrityksen ja erehdyksen kautta, kunhan ei yritä välittää väärän nimistä tietoa väärään paikkaan. Kuten rakennussarjoista tiedämme, legotkin voi asettaa väärään järjestykseen. Tämä on toki mahdollista automatisoidun datan muokkauksen kanssakin ja tuli muutaman kerran koettua.
Tiivistetysti, jotta saat datakäsittelyn legopalikkasi oikeaan järjestykseen, pidä ensisijaisesti huolta siitä, että haet oikeaa dataa, välität sen oikean tyyppisenä muuttujana eteenpäin, ja hyödynnät sitä jatkossa oikeassa muodossa oikeasta paikasta.
Kun kaikesta tästä on huolehdittu, käytettävissäsi on tuotos, jolla saat tehtyä määrittelemääsi paikkaan ja haluamallasi nimellä datatiedoston, kun ajat skriptin.
Edessäsi on kuitenkin vielä pari ongelmaa
Huomasit kenties, että kirjoitin datatiedoston. En kirjoittanut datatiedostoja. Aivan. Tässä vaiheessa on rakennettu ehtolauseita ja toistorakenteita lähdedatalle ja kohteen kirjoittamiselle mutta lopputulos on kuitenkin, pahus sentään, yksi tiedosto – ainoastaan moneen kertaan kirjoitettuna. Tämähän ei suinkaan ole sitä, mitä haettiin. Vielä ei siis saatu palkintoa legopalikoiden asettelemisesta uudelleen.
Joten, ratkaisu on haettava siitä, että ehtosilmukan muuttuja kirjoitetaan myös tiedostonimeen. Samalla tavoin voisit liittää tiedostoon myös muita yksilöiviä tietoja. Olemme jälleen lähempänä ratkaisua. Tämän myötä meillä on halutussa tiedostokansiossa myyjäyksiköittäin eroteltuja tiedostoja.
Mutta nämähän eivät ole Exceleitä?
Aivan totta. Vieläkään ei olla aivan siellä missä pitäisi. Tiedostot avautuvat Excelissä mutta eivät ole helppolukuisia. Voisimme datan ammattilaisina hyödyntää näitä .csv -tiedostoja moneen käyttöön ohjelman pätkissä ja tiedon viennissä sekä tuonnissa. Mutta asiakkaalle nämä pitäisi saada tyylikkäämmän näköiseksi, selkeämmiksi lukea.
Tähän mennessä olemme toteuttaneet ratkaisun, joka tuottaa tiettyyn tiedostokansioon useamman tiedoston lähdedatan mukaisesti. Nyt tarvitsemme toteutuksen, joka muokkaa näitä tiedostoja Excel-tiedostoiksi.
Tätä varten kannattaa luoda oma koodinsa. Meidän ratkaisumme tähän on sellainen, jossa lähdetiedostokansio, kohdetiedostokansio, tietojen erotin ja tiedostotyyppi annetaan koodiin muuttujina. Koodi lukee määritellyt datatiedostot ja kirjoittaa niistä Excel-tiedostot vastaavilla tiedostonimillä kohdetiedostokansioon,
Lego-palikat uudessa järjestyksessä
Nyt halutussa kohteessa on myyjäyksiköittäin eroteltuja Excel-tiedostoja, jotka ovat asiakkaalle selkeitä, ja joiden perusteella he voivat tehdä yksiköitään koskevat muutokset omiin tiedostoihinsa. Datan käsittelyä voi jatkaa tästä eteenpäin monipuolisesti. Prosessien automatisointia voidaan lisätä esimerkiksi liittämällä ratkaisun skriptit jobien sisään ja ajaa ne haluttujen reunaehtojen täyttyessä ja tällöin asianosaisille yksiköille saadaan heitä koskevat tiedot, jolloin he pääsevät toteuttamaan tarvittavat korjaukset itsenäisesti.
Kuten tiedämme, legot voi kasata monella tapaa ja monessa järjestyksessä. Dataakin voi kasata monella tapaa ja monessa järjestyksessä ja hyödyntää eri kohdissa. On kuitenkin olemassa joitakin osia, jotka sopivat vain tiettyyn kohtaan ja pilkun paikallakin on välillä tärkeä merkitys.
Legot voivat silti opettaa meille paljon datan käsittelystä. Samoin lapsiemme tapa käsitellä legoja. Nimittäin, vaikka jokin legosarja olisi joululahjaksi saamisen jälkeen kasattu oikein ohjeiden kanssa, voi samaa sarjaa hyödyntää toisinkin.
- Sen voi purkaa
- Sen voi kasata uudelleen
- Sen voi kasata erilaisena
- Siihen voi lisätä osia tai jättää niitä pois
- Se voi näyttää toisenlaiselta
- Sitä voi hyödyntää toiseen tarkoitukseen.
Listaa voisi jatkaa pitkään. Sama pätee dataan ja sen hyödyntämiseen. Joskus uuden datatehtävän kanssa näyttää siltä, että palikat ovat yhtä levällään kuin lasten legot siivouspäivänä. Silti, pala palalta rakentaen jopa sekavasta legokasasta tai datataulusta saadaan jäsennelty, selkeä ratkaisu. Sellainen, joka ratkaisee ongelman, täyttää tarpeen ja jota on jälleen miellyttävä ja helppo käyttää, kuten vaikkapa esimerkkimme myyjäyksikkö H-009 ja sen data Excelissä.
Kirjoittaja: Jarno Leinonen, data engineer, Kookax Oy