Tämä blogi päättää blogisarjani SQL Server-tietokantojen klassiseen DataOpsiin liittyen. Tässä blogissa keskitytään taulujen (uudelleen-) luomiseen, SQL Server jobien (uudelleen-)luomiseen sekä tietokantatuotteen versiointiin.

Clean install-skriptimoduulit

Tänään läpikäydään seuraavat skriptikokonaisuudet:

• Populate tables -skripti populoi käyttäjätaulujen sisällön.
• Jobs -skripti (uudelleen-)luo kantaratkaisun SQL Server jobit.
• Version -skriptihoitaa lopuksi versiopäivityksen lokitauluun.

Populate tables -skripti

Populate tables -skriptin vaiheet ovat:

• Populoidaan käyttäjätaulut pää- ja viiteavainten mukaisessa parent-child -hierarkiajärjestyksessä.

Ohessa esimerkkkiskripti, jossa lisätään tauluun useampi rivi kerralla. Tämä on eritoten kätevää silloin, kun lisättäviä tietueita on kymmeniä tai vielä paljon enemmän. Vältyt näin turhan koodin kirjoittamiselta. Voit toki myös generoida T-SQL-koodin, joka luo dynaamisesti luontilausekkeet haluttuun muotoon, tai sitten voit käyttää jonkin kolmannen osapuolen koodingenerointityökaluja.

Entä kun haluat lisätä tauluun vain tietyt rivit, mutta joudutkin ajamaan kyseisen skriptin uudelleen? Silloin on näppärä käyttää IF EXISTS-määrettä seuraavalla tavalla:

Jobs -skripti

Jobs -skriptin vaiheet ovat:

• Poistetaan vanhat SQL Server jobit
• (Uudelleen-) Luodaan SQL Server jobit

Esimerkki vanhan SQL Server jobin poistosta scheduleineen. SQL Server jobit löytyvät msdb:n sysjobs_view -näkymästä.

Varsinaisen SQL Server jobin luontiin tarvitset seuraavia system stored procedureja:

• Sp_add_job
• Sp_add_job_server
• Sp_add_job_step
• Sp_add_jobschedule

Esimerkiksi; näin luodaan uusi SQL Server jobi, joka putsaa käyttäjätauluista  vanhentuneita rivejä:

Ensin lisätään jobi ja enabloidaan se. Sitten liitetään jobi serveriin, missä se ajetaan:

Lisätään SQL Server jobille steppi, jossa haluttu rivienpoistoproseduuri suoritetaan:

Lopuksi luodaan ja enabloidaan haluttu schedule jobille, tässä tapauksessa toistuva:

Version -skripti

Version-skriptin tehtävänä on tallentaa tuotteen versiointitiedot kronologisesti tiatokantatauluun. Tämän kautta on sitten helppo seurata, että missä tuoteversiossa mennään milloinkin. Versiotaulun rakenne voi olla yksinkertaisimmillaan esimerkiksi tällainen:

• Versionumerokenttä, esimerkiksi 16100.
• Versionimikenttä, esimerkiksi V16 CU1.
• Asennuspvm -kenttä, esimerkiksi 2024-10-30 15:50:00.000

Hyvänä käytäntönä voidaan pitää sitä, että jokainen yksittäinen asennus jättää tähän tauluun tietueen versiohistoriaan. Tästä on iloa installerille. 😉

Loppusanat

Tämä olikin sitten tämän blogisarjan viimeinen postaus. Nyt sinulla pitäisi olla ihan hyvät perusteet hallussa siitä, kuinka tietokantaskriptejä tehdään tuotekehitysmielessä. Systemaattisuus, modulaarisuus ja toistettavuus on tässä hommassa kaiken A ja O.

Miten voisimme olla avuksi juuri sinun tietokantaongelmissasi?

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Saatat olla kiinnostunut:

SQL-tietokanta – historia, nykytila ja tulevaisuus: historia

Tässä blogissa käsittelen DataOpsia (Data Operations) yleisellä tasolla, sen eri teemoja ja miksi se on yritykselle tärkeää datan ja kehitysprosessien laadukkaassa hallinnassa. Tulevissa blogeissa käsitellään konkreettisia esimerkkejä siitä, kuinka toteuttaa lähteiden hallinta, jatkuva integrointi, jatkuva käyttöönotto ja yhteistyöhön perustuvat kehitysympäristöt.

DataOps on yhteistyöhön perustuva lähestymistapa datanhallintaan ja kehitysprosesseihin, jonka tavoitteena on parantaa viestintää, integraatiota ja automaatiota organisaation sisällä. DataOps on erityisen hyödyllinen data-alustan kehitystiimin ja datan käyttäjien välisessä tehokkaassa yhteistyössä. DataOpsin päätavoitteena on nopeuttaa arvon tuottamista varmistamalla, että datan, datamallien ja niihin liittyvien prosessien toimitus ja muutokset ovat ennustettavissa ja hallittavissa.

Tämä lähestymistapa hyödyntää teknologiaa datan toimitusprosessien suunnittelun, käyttöönoton ja hallinnan automatisoimiseksi. Samalla DataOps varmistaa, että datanhallintaan liittyvät valvonta- ja ohjauskäytännöt ovat riittävällä tasolla. Lisäksi se käyttää metadataa tehostaakseen datan käytettävyyttä ja lisätäkseen sen arvoa jatkuvasti muuttuvassa ja kehittyvässä ympäristössä.

Miten DataOps eroaa perinteisestä DevOpsista?

Usein kuulee sanottavan, että DataOps on vain uudelleenbrändäys DevOpsista, ja siinä on osittain perää. Perinteinen DevOps soveltaa CI/CD (Continuous Integration / Continuous Deployment) -menetelmiä ohjelmistokehitykseen, kun taas DataOps keskittyy datatuotteiden ja data-alustojen hyödyntäjien ja tuottajien siilojen purkamiseen, hyödyntäen perinteisiä DevOps- ja Data Governance -toimintatapoja.

DataOpsilla ja DevOpsilla on paljon yhteistä, sillä molemmat lähestymistavat ovat syntyneet tarpeesta tehostaa ja automatisoida monimutkaisia prosesseja, jotka vaativat useiden tiimien välistä yhteistyötä. Alla on listattu yleisellä tasolla keskeisiä yhteisiä piirteitä:

• Yhteistyön korostaminen
• Automaatio
• Jatkuva integrointi ja jatkuva toimitus (CI/CD)
• Iteratiivinen ja ketterä kehitys
• Laadunhallinta ja seuranta
• Kulttuurin muutos
• Skaalautuvuus

DataOps ja CI/CD  

CI/CD on keskeinen osa DataOps-toimintoja, ja se tuo mukanaan prosesseja, jotka parantavat datan hallintaa ja analysointia tehokkuuden ja laadun näkökulmasta. Seuraavassa selitän, miten CI/CD toimii DataOpsissa.

Continuous Integration (CI) DataOpsissa

Continuous Integration eli jatkuva integrointi tarkoittaa sitä, että datan, koodin ja mallien muutokset yhdistetään automaattisesti osaksi yhteistä projektia säännöllisin ja tihein väliajoin. DataOpsissa tämä voi tarkoittaa seuraavia toimintoja:

• Datan ja mallien versiohallinta: Kun datamallit tai -putket päivittyvät, ne integroidaan välittömästi yhteiseen koodivarastoon. Tämä varmistaa, että tiimi työskentelee aina uusimman version kanssa ja mahdollistaa muutosten seurannan ja hallinnan.
  
• Automaattinen testaus: Ennen kuin uudet muutokset otetaan käyttöön, ne testataan automaattisesti. Tämä voi sisältää datan laadunvalvontatestejä, mallien tarkkuuden varmistamista ja virheiden tunnistamista. Näin varmistetaan, että muutokset eivät riko olemassa olevia toimintoja.
  
• Yhteistyön tehostaminen: CI mahdollistaa sen, että useat tiimin jäsenet voivat työskennellä samanaikaisesti ja integroida työnsä saumattomasti. Tämä vähentää konflikteja ja nopeuttaa kehitystä.

Continuous Deployment (CD) DataOpsissa

Continuous Deployment eli jatkuva käyttöönotto tarkoittaa, että hyväksytyt ja testatut muutokset otetaan automaattisesti käyttöön tuotantoympäristössä. DataOpsissa tämä voi tarkoittaa seuraavia toimintoja:

• Automaattinen julkaisu: Kun muutos on testattu ja hyväksytty, se siirretään automaattisesti tuotantoon. Tämä voi koskea esimerkiksi uusia datamalleja, ETL-prosesseja (Extract, Transform, Load) tai datan visualisointeja.
  
• Nopea reagointi: CD mahdollistaa sen, että datan ja mallien muutokset voidaan ottaa käyttöön nopeasti ja turvallisesti. Tämä on erityisen tärkeää, kun on tarpeen reagoida nopeasti markkinamuutoksiin tai liiketoiminnan tarpeisiin.
  
• Jatkuva parantaminen: Koska muutokset voidaan ottaa käyttöön nopeasti, tiimit voivat keskittyä jatkuvaan parantamiseen. Tämä johtaa iteratiiviseen kehitykseen, jossa jokainen muutos tuo lisäarvoa.

DataOps ja Data Governance

Data governance, eli datan hallintamalli, on erityisen tärkeä osa DataOps-kokonaisuutta. Data governance viittaa niihin käytäntöihin, prosesseihin ja teknologioihin, joiden avulla organisaatiot varmistavat, että heidän hallussaan oleva data on laadukasta, turvallista, ja käytettävissä oikeaan aikaan ja oikeassa paikassa. DataOpsin kontekstissa data governance liittyy läheisesti CI/CD-prosesseihin ja toimii niiden rinnalla varmistamassa, että kaikki datan käsittelyyn liittyvät toimet ovat hallittuja ja säädeltyjä.

Data governance DataOpsissa

Data governance on olennainen osa DataOpsia, koska se varmistaa, että datan elinkaarta hallitaan tehokkaasti ja turvallisesti. Seuraavassa on muutamia keskeisiä osa-alueita, joissa data governance on erityisen tärkeä:

• Datan laatu: DataOpsin yhteydessä data governance sisältää prosessit, joiden avulla varmistetaan datan tarkkuus, eheys ja ajantasaisuus. CI/CD-prosessit voivat tuoda uutta dataa ja datamalleja tuotantoon nopeasti, mutta ilman tehokasta datan hallintamallia riskinä on, että huonolaatuista dataa päätyy järjestelmiin. Data governance asettaa standardit ja tarkastukset, joilla varmistetaan datan laatu koko sen elinkaaren ajan.
• Tietoturva ja yksityisyys: Data governance vastaa myös siitä, että organisaation dataa käsitellään turvallisesti ja lainsäädännön mukaisesti. Tämä on erityisen tärkeää, kun CI/CD-prosessien kautta tehdään jatkuvia muutoksia ja päivityksiä, sillä niiden täytyy noudattaa tietoturvaa ja yksityisyyttä koskevia vaatimuksia. Data governance varmistaa, että oikeat käyttäjät pääsevät käsiksi vain heille sallittuun dataan ja että kaikki käyttö ja muutokset ovat jäljitettävissä.
• Metadata ja datan jäljitettävyys: Metadata, eli tietoa datasta, on keskeinen osa DataOpsin data governance -prosessia. Metadataa käytetään paitsi datan ymmärtämiseen, myös sen jäljitettävyyden varmistamiseen. Tämä tarkoittaa, että jokainen datan muutos ja sen alkuperä voidaan jäljittää, mikä on tärkeää laadunhallinnan, virheenkorjauksen ja sääntelyn noudattamisen kannalta.
• Compliance ja sääntely: Monet toimialat ovat tiukasti säänneltyjä, ja data governance auttaa organisaatioita varmistamaan, että he noudattavat kaikkia asiaankuuluvia lakeja ja säännöksiä. CI/CD-prosessien avulla tehdyt muutokset on varmistettava niin, että ne ovat sääntöjen mukaisia ja että organisaation data pysyy sääntelyn vaatimusten mukaisena.

Data governance ja CI/CD: Yhteistyö DataOpsissa

Data governance ja CI/CD toimivat yhdessä DataOpsin sisällä varmistaakseen, että dataa käsitellään tehokkaasti ja turvallisesti. CI/CD-prosessit mahdollistavat nopean ja jatkuvan muutoksen, mutta ilman vahvaa data governance -kehystä tämä voisi johtaa kaaokseen ja virheisiin. Data governance tuo järjestystä ja luotettavuutta näihin prosesseihin, varmistaen samalla, että organisaation data on aina laadukasta, turvallista ja käyttökelpoista.

Tällä tavoin DataOps pystyy tukemaan organisaation tavoitteita hyödyntää dataa tehokkaasti ja samalla täyttää kaikki tietoturva- ja sääntelyvaatimukset. Data governance on siis kriittinen osa DataOpsia, ja sen merkitys kasvaa entisestään, kun organisaatiot pyrkivät automatisoimaan ja tehostamaan datan hallintaprosessejaan.

Loppusanat

Tässä blogissa käsittelin DataOpsin keskeisiä periaatteita, erityisesti sen suhdetta CI/CD-prosesseihin ja data governanceen. DataOps tarjoaa organisaatioille tehokkaan tavan hallita dataa ja kehitysprosesseja, tuoden yhteen eri tiimit ja varmistamalla, että dataa käsitellään luotettavasti ja laadukkaasti. Tulevissa blogeissa sukellamme syvemmälle DataOpsin käytäntöihin ja esimerkkeihin siitä, miten tämä lähestymistapa voi auttaa organisaatiotasi hyödyntämään dataa entistä paremmin.

DataOps on ratkaiseva tekijä nykypäivän datalähtöisessä maailmassa, ja sen merkitys tulee vain kasvamaan, kun organisaatiot pyrkivät pysymään kilpailukykyisinä ja reagoimaan nopeasti markkinoiden muutoksiin. Ottamalla DataOpsin osaksi liiketoimintaasi, voit varmistaa, että organisaatiosi data on aina ajan tasalla, luotettavaa ja käyttökelpoista, ja että pystyt hyödyntämään sen täyden potentiaalin.

Kiinnostuitko DataOpsista ja sen eri käytännöistä? Voisimmeko ehkä olla avuksi? Ole hyvä ja ota yhteyttä!

Robin Aro
robin.aro@dbproservices.fi
Lead Data Engineer
DB Pro Services Oy

Tämä blogi jatkaa blogisarjaani SQL Server-tietokantojen klassiseen DataOpsiin liittyen. Tässä blogissa keskitytään ohjelmoitavuuden (uudelleen-) luomiseen.

Clean install-skriptimoduulit

Tänään läpikäydään seuraavat skriptikokonaisuudet:

• Drop programmability
• Table types
• Create programmability

Huom! Skriptit on ajettava juuri tässä järjestyksessä.

Drop programmability -skripti

Drop programmability-skriptin vaiheet ovat:

• Pudotetaan valikoidut käyttäjän määrittelemät proseduurit (usp_)
• Pudotetaan valikoidut käyttäjän määrittelemät funktiot (ufn_)

Käyttäjän määrittelemät proseduurit on hyvä etuliitteistää ”usp_” -alkuisiksi, jotta ne erottuvat systeemin stored proceduresta (sp_). Ohessa skripti, joka automatisoi kaikkien käyttäjän määrittelemien proseduurien poistolausekkeet. Huomaathan, että kyseinen esimerkkiskripti ei itse poista proseduureja, vaan ainoastaan listaa poistolausekkeet. Jää sitten installerin tehtäväksi suorittaa varsinainen poisto-operaatio. Voit halutessasi muuttaa koodia siten, että proseduurien poisto suoritetaan while-slimukassa esimerkiksi exec -komennolla tai sp_execute_sql -systeemiproseduuria käyttäen. Proseduurit löytyvät sys.objects -näkymästä ja niiden tyyppi on ’P’, eli proseduuri.

Käyttäjän määrittelemät funktiot on vastaavasti hyvä etuliitteistää ”ufn_” -alkuisiksi, jotta ne erottuvat systeemifnktioista (fn_). Ohessa vastaavasti skripti, joka automatisoi kaikkien käyttäjän määrittelemien funktioiden poiston luontilauseet. Muutoin samat huomiot kuin proseduurien suhteen.

Funktiot löytyvät sys.objects -näkymästä ja niiden tyyppi on jokin seuraavista:

• TF = SQL table-valued-function (TVF)
• FN = SQL Scalar function
• IF = SQL inline table-valued function (TVF)

Tämän lisäksi on vielä joukko hupomioitavia CLR-funktioita:

• AF = Aggregate function
• FS = Assembly scalar-function
• FT = Assembly table-valued function

Table types -skripti ja TVP:t

Taulutyypit ovat erittäin käteviä monessa tilanteessa. Jos ne eivät ole sinulle ennestään tuttuja, pieni esittely lienee paikallaan: Niin kutsutut TVP:t (”Table-Valued Parameters”) määritetään käyttämällä käyttäjän määrittämiä taulutyyppejä. TVP:iden avulla voit välittää useita tietueita T-SQL-lauseeseen tai rutiiniin, kuten proseduuriin tai funktioon luomatta väliaikaista taulukkoa tai useita parametreja. Käytännössä nämä ovat siis eräänlaisia parametrilistoja. Suorituskykyiseksi tämän tekniikan tekee se, että T-SQL välittää TVP:t rutiineihin viittaamalla, jotta syöttötiedoista ei tehdä erillistä kopiota.

TVP on aina rajattu proseduuriin, funktioon tai dynaamiseen Transact-SQL-tekstiin, aivan kuten muutkin parametrit. Vastaavasti TVP:llä on vaikutusalue kuten millä tahansa muulla paikallisella muuttujalla, joka luodaan käyttämällä DECLARE-lausetta. Voit määritellä TVP:t dynaamisissa T-SQL-lausekkeissa ja välittää nämä muuttujat TVP:nä proseduureihin ja funktioihin.

TVP:t tarjoavat enemmän fleksiä ja joissakin tapauksissa paremman suorituskyvyn kuin temporary tablet tai muut parametriluettelon välittämistavat. Taulukkoarvoiset parametrit tarjoavatkin tietokantakoodarille seuraavia hyötyjä:

• Ne eivät ota lukkoja alkuperäisessä datan populoinnissa clientilta
• Ne tarjoavat yksinkertaisemman ohjelmointimallin
• Voit toteuttaa monimutkaistakin ohjelmalogiikkaa yhdessä rutiinissa
• Vähennät server roundtrippejä
• Voit käsitellä eri kardinaliteetin taulurakenteita
• Rakenteet ovat vahvasti tyypitettyjä
• Voit antaa clientin valita sort orderin ja uniikit avaimet
• TVP:n välimuistikäsittely vastaa proseduureissa temppitauluja

Taulutyypin poisto onnistuu seuraavanlaisesti:

Taulutyyppiä luotaessa sen olemassaolemattomuus varmistetaan sys.types -näkymän kautta esimerkiksi seuraavanlaisesti:

Create programmability -skripti

Tässä skriptissä (uudelleen-) luodaan käyttäjän määrittelemät proseduurit ja funktiot kääntäisessä kutsumajärjestyksessä:

1. Ensin esitellään sellaiset proseduurit ja funktiot, jotka eivät ole riipuvaisia toisista proseduureista ja funktioista.
   
2. Sitten esitellään sellaiset proseduurit ja funktiot, joita kutsutaan muista proseduureista ja funktioista siten, että järjestys on kutsumajärjestyksen suhteen käänteinen: Jos A kutsuu B:tä joka kutsuu C:tä, C esitellään ensin, sitten B, lopuksi A.
   
3. Viimeisimpänä esitellään näinollen sellaiset proseduurit ja funktiot, jotka ovat eniten riippuvaisia muista proseduureista ja funktioista.

Loppusanat

Jälleen kerran voimme todeta, että systeemiolioiden kautta operointi helpottaa merkittävästi rutiinien ohjelmoitavuutta. Tämän lisäksi opimme, että taulutyypit ovat erinomainen lisäkonsti temppitaulujen sekä CTE:iden (Common Table Expressions) lisäksi silloin, kun ohjelmoitavuus kompleksisoituu rutiinien, kuten käyttäjän määritetlemien proseduurien välillä ja välitettävän tiedon määrä on suuri.

Ovatko DBA:si ylityöllistettyjä? Meiltä saat juuri sopivan kaistan black belt -tason DBA-osaamista tukemaan liiketoimintaasi. Pidetään pyörät pyörimässä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Sinua saattaa kiinnostaa myös:
Master Data ja Master Datan hallinta osana Business Intelligence arkkitehtuuria
SQL-tietokanta – historia, nykytila ja tulevaisuus: historia
Power BI pro ja eri lisensiointimallit
Power BI-raportointi – tasapaino tekniikan ja muotoilun välillä 

Tämä blogi jatkaa blogisarjaani SQL Server-tietokantojen klassiseen DataOpsiin liittyen. Tässä blogissa keskitytään taulujen ja näkymien poistoon sekä (uudelleen-) luontiin.

Yleisesti nimeämiskäytännöistä

Endoton suositus ja paras käytäntö kaikkien tietokantaolioiden määrämuotoiselle nimeämiselle loogisesti oliotyypin ja käyttötarkoituksen mukaan. Vältä ennen kaikkea teknisiä nimiä ja kirjainlyhenteitä niin paljon kuin mahdollista. Tärkeintä on, että koodisi on ymmärrettävää. Intellisense on olemassa pitkiäkin olionimiä varten. Puolipisteen kurinalainen käyttö statementtien lopuksi sekä koodin selkeä indentointi on myös hyvin tärkeää opetella, jotta koodi olisi mahdollisimman luettavaa.

Clean install-skriptimoduulit

Muistathan,että clean install-skriptinkin ajo voi mennä syystä tai toisesta pieleen. Tämän takia, ja jotta seuraavan tuoteversion skriptaaminen ja asentaminen olisi halpompaa, on syytä tehdä kaikista skripteistäsi uudelleenajettavia.

Tänään keskitytään Tables and views -skriptiin.

Skriptin vaiheistus

Tables and views-skriptissä (uudelleen-)luodaan nimensä mukaisesti käyttäjätaulut. Tässä vaiheessa tehdään ainakin seuraavat toimenpiteet siten, että skriptin uudelleenajettavuus on mahdollista:

• Pudotetaan käyttäjätaulujen pää- ja viiteavaimet
• Pudotetaan käyttäjänäkymät
• Pudotetaan käyttäjätaulut
• Luodaan käyttäjätaulut
• Lisätään käyttäjätauluihin constraintit ja defaultit
• Lisätään käyttäjätauluihin viiteavaimet
• Lisätään käyttäjänäkymät
• (Uudelleen-)luodaan muut kuin viiteavainindeksit
• Luodaan viiteavainindeksit
• Otetaan tarvittaessa kompressointi käyttöön

Käyttäjätaulujen pää- ja viiteavainten pudotus

Tämä homma hoituu parhaiten silmukalla. Näin säästyt turhalta koodaamiselta. Koodiesimerkissä tuhotaan 5 avainta kerrallaan niin kauan kuin avaimia riittää, näin toiminto on tehokas ja joustava eritoten, jos avaimia on satoja tai jopa tuhansia kappaleita.

Käyttäjänäkymien pudotus

Tässä ei pitäisi olla mitään sen ihmeellisempää. Käyttäjänäkymät nimetään esim. prefixillä ”v_”. If exists -määre on tietty pakollinen skriptin uudelleenajettavuutta varten ja olioviittaukset kannattaa lähtökohtaisesti kirjoittaa aina unicodena.

Käyttäjänäkymän pudotus.

Käyttäjätaulujen pudotus

Käyttäjätauluihin voi poistettaessa viitata näppärästi suoraan if exists-määreellä, niin kuin itse asiassa näkymiinkin.

Käyttäjätaulun pudotus.

Käyttäjätaulujen luonti

Käyttäjätaulujen osalta rajaan tämän blogin partitioimattomien taulujen käsittelyyn. Voisinkin kirjoittaa partitioitavista tauluista sitten ihan oman blogin, se kun on sen verran laaja kokonaisuus.

Esimerkissä luodaan käyttäjätaulu, jonka olemassaolo tarkistetaan sys.objects -näkymän kautta. Tässä on tärkeää muistaa, että taulun type -kentän tulee olla ”U”, eli käyttäjätaulu. Taulun käyttötarkoitukseen voi käyttää skeemaa tai sitten esimerkiksi kolmikirjainlyhennettä, mikäli skeemalla on esimerkiksi jokin muu käytttötarkoitus.

Taulua luotaessa sille määritellään kentät, joiden tietotyypit ja kenttäpituudet kannattaa miettiä huolella suorituskykynäkökulmasta eritoten silloin, kun taulu voi kasvaa isoksi. Joka tapauksessa kannattaa noudattaa kaikissa tietotyypeissä sekä kenttäpituuksissa johdonmukaisuuden periaatetta. Muista myös määrittää kentälle aina, voiko se saada null-arvon vai ei. Myös taulun pääavain luodaan tässä yhteydessä.

Käyttäjätaulun luonti.

Constrainttien ja default-arvojen lisäys käyttäjätauluihin

Constrainttien ja default-arvojen poistoissa viitataan sys.objects -näkymään type -kentän saadessa arvon ”D”.

Defaultin luonti.

Constraintin luonti.

Viiteavainten lisäys käyttäjätauluihin

Viiteavainta lisättäessä NOT EXISTS -määreelle annetaan referenssi sys.foreign_keys -näkymään, josta haetaan OBJECT_ID -funktiolla viiteavaimen nimi. Tämän lisäksi parent_object_id -kentän tulee viitata käyttäjätauluun itseensä. Mahdollinen cascadointi on myös määriteltävä tässä yhteydessä. CASCADE-määre tulee antaa viiteavaimelle päivittämisen (UPDATE) ja / tai deletoinnin (DELETE) yhteyteen. Kaskasointi tarkoittaa sitä, että mikäli viitatusta päätaulusta poistuu tai siinä päivitetään arvoja, päivitykset heijastuvat myös viittaavaan tauluun. Tässä taulussa sellaista ei ole määritelty.

Viiteavaimen lisäys käyttäjätauluun.

Käyttäjänäkymien lisääminen

Käyttäjänäkymien luonti on SQL Serverissä varsin suoraviivaista. Erikoisuutena voisi mainita SCHEMABINDING -option näkymää luotaessa: Se sitoo luodun näkymän pohjana olevan taulun tai taulujen rakenteeseen. Tämä on näppärää, jos haluat esimerkiksi varmistaa, että näkymät ovat aina ajantasaisia tai haluat lisätä indeksoinnin itse näkymään. Kun SCHEMABINDING on määritetty, perustaulua tai -tauluja ei voi muokata tavalla, joka vaikuttaisi näkymän määritykseen. Itse näkymämääritystä onkin ensin muokattava tai se on poistettava, jotta muokattavan taulun riippuvuudet poistetaan. Edelleen, kun käytät SCHEMABINDING:ia, SELECT:in on sisällettävä viitattujen taulujen, näkymien tai käyttäjän määrittämien funktioiden (UDF) kaksiosaiset nimet (schema.object). Kaikkien viitattujen objektien on myös oltava samassa tietokannassa.

Näkymiä tai tauluja, jotka osallistuvat SCHEMABINDING-lausekkeella luotuun näkymään, ei voi pudottaa, ellei näkymää poisteta tai muuteta niin, että sillä ei enää ole rakenteen sidontaa. Muussa tapauksessa tietokantamoottori aiheuttaa virheen. Myös ALTER TABLE -lausekkeiden suorittaminen tauluissa, jotka osallistuvat näkymiin, joissa on SCHEMABINDING, epäonnistuu, koska nämä lausekkeet vaikuttavat näkymän määritykseen.

Käyttäjänäkymän lisääminen.

Tavallisten indeksien lisäys

Indeksi poistetaan viittaamalla suoraan indeksin nimeen NOT EXISTS -määreellä kyseisessä taulussa. Lisäyksessä on hyvä muistaa, että mm. paksussa indeksissä kenttäjärjestys on B-tree -indeksin fyysinen järjestys ja että on mahdollisesti väliä sillä, kummassa järjestyksessä kunkin kentän indeksi fyysisesti organisoidaan (ASC vs. DESC).

Indeksin täyttöaste (fillfactor) on arvioitava sen muutosherkkyyden perusteella. Muuttumaton data, joka vain kerran kirjoitetaan tauluun ja sitä sitten selectoidaan, voi saada 100% täyttöasteen, mutta muutoin se on tyypillisesti välillä 60-95%. Oikein valittu täyttäaste voi vähentää mahdollisia page splittejä tarjoamalla riittävästi tilaa indeksin laajentamiselle, kun tietoja lisätään tauluun. Kun uusi rivi lisätään täydelle indeksisivulle, tietokantamoottori siirtää noin puolet riveistä uudelle sivulle, jotta tuolle riville jää tilaa. Tätä uudelleenjärjestelyä kutsutaan page splitiksi. Page split tekee tilaa uusille tietueille, mutta sen suorittaminen voi viedä aikaa ja vaatii paljon resursseja. Lisäksi se voi aiheuttaa fragmentaatiota, joka lisää storagen I/O-aktiviteettia. Jos sivujakoja tapahtuu usein, indeksi voidaan muodostaa uudelleen käyttämällä uutta tai aiemmin luotua fillfactor-arvoa tietojen jakamiseksi uudelleen. Esimerkin indeksisivuun kohdistuu kohtalaisesti UPDATE– / DELETE -toimintaa.

Indeksin lisäys.

Viiteavainindeksien lisäys

Viiteavaimia voi olla todella paljon ja kun ne on nimetty systemaattisesti samalla prefixillä, on varsin triviaalia automatisoida viiteavainindeksien lisäys. Esimerkkiskriptissä on tosin vakio täyttöaste indeksille, joten voit halutessasi optimoida skriptiä hieman pidemmälle.

Viiteavainindeksien automaattinen lisäys iteraattorilla.

Kompressoinnin käyttöönotto tietokannassa

Taulukompressointi säästää tilaa helposti tietokannassa jopa 50% ja sen CPU-footprint on 1% luokkaa, joten se kannattaa ottaa ehdottomasti käyttöön. Kompressointia on tarjolla rivitasolla (ROW) ja sivutasolla (PAGE). Yleensä sivutason kompressointi on tehokkaampi, eritoten datamart-tyyppisissä denormalisoiduissa tietokantaskeemoissa. Kompressoinnin hyötyä voi myös arvioida sp_estimate_data_compression_savings –systeemiproseduurilla. Proseduuri on tosin aika hidas isoja tauluja vasten ja jos tauluja on paljon, kannattaa joko automatisoida koko prosessi acceptance test -ympäristössä tai sitten käyttää aina rekursiivisesti PAGE -tason kompressointia, joka kokemukseni mukaan on 90-prosenttisesti oikea vaihtoehto. Kokeiltuasi eri vaihtoehtoja todennäköisesti päädyt seuraavaan ratkaisuun:

Loppusanat

Tietokantaskripteissä on hallinnallisesti neljä dimensiota: Systemaattisuus vs. luovuus ja dynaamisuus vs. staattisuus. Luovuuden ja dynaamisuuden kasvaessa koodin luettavuus ja ylläpidettävyys usein kärsivät, mutta toisaalta selviät paljon vähemmällä koodilla ja voit tehdä automaatioita aivan uudella tavalla. Your choice!

Tarvitseeko tiimisi auttavia, kokeneita DBA-käsipareja? Ole hyvä ja ota meihin yhteyttä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Tämä blogi jatkaa blogisarjaani SQL Server-tietokantojen klassiseen DataOpsiin liittyen. Tässä blogissa pureudutaan ns. Clean install -skriptin tekoon, eli kun softa asennetaan ensimmäisen kerran hermeettiseen ympäristöön. Kuten edellisessä blogissani aihetta sivusin, on tätä varten viisasta luoda muuttumaton skriptirunko, jossa tietokannan ns. corekomponentit asennetaan ja jäädytetään tähän versioon, jotta uusiin versioihin voidaan tehdä omat erilliset päivitysskriptinsä.

Clean install-skriptimoduulit

Clean install-skriptistä tulee helposti aika iso. Siinä missä pienelle, yksinkertaiselle tietokannalle voi riittää yksi skriptimoduuli ja tuhat koodiriviä, kannattaa hieman kompleksisempi tietokanta purkaa osiin. Tämä takaa ketterän ylläpidettävyyden ja lisää selkeyttä ja rakennetta skripteihisi ja toimii hyvänä pohjana tulevien versioiden ylläpitoskripteille. Tässä vielä kertauksen vuoksi Clean Install -skriptin mahdollinen jako moduuleihin. Moduuleita nimettäessä voi selkeyden vuoksi käyttää ajojärjestykseen liittyvää enumerointia. Tällainen rakenne mahdollistaa jopa kymmenien tuhansien koodirivien modulaarisen toteutuksen. Proseduurit ja funktiot voit pilkkoa kunkin omikse skripteikseen jos ne ovat kompleksisia ja niitä on todella paljon. Tämä lisää kokonaisuuden ylläpidettävyyttä.

Clean Install-skripteissä sinulla olisi hyvä olla vähintään:

• Core-skripti, jossa tietokannan (uudelleen-) luontiskriptit käyttäjyyksineen.
• Tables and views -skriptiin sijoitetaan taulut ja näkymät uudelleenluonteineen.
• Drop procedures -skripti (tai Drop Programmability) tiputtaa kaikki user stored proceduret ja käyttäjäfunktiot tietokannasta.
• Table types -skripti (uudelleen-)luo kaikki taulutyypit.
• Create procedures (tai Create Programmability) -skripti (uudelleen-)luo kaikki user stored procedure ja käyttäjäfunktiot.
• Encrypted procedures -skripti (uudelleen-)luo kaikki kryptatut user stored proceduret.
• Populate tables -skripti truncatoi ja populoi käyttäjätaulujen sisällön (=alustus).
• Jobs -skripti (uudelleen-)luo ratkaisun SQL Server jobit.
• Version -skripti hoitaa lopuksi versiopäivityksen lokitauluun.

SQLCMD -tila

SQL Server Management Studion (SSMS) SQLCMD-tila on erityinen suoritustila, jonka avulla voit suorittaa SQLCMD-komentoja ja T-SQL -kyselyitä SSMS:n Query Analyzerissä. Kun SQLCMD-tila on käytössä, voit sisällyttää komentosarjoihisi SQLCMD-kohtaisia komentoja, mikä mahdollistaa monimutkaisempia ja automatisoidumpia tehtäviä, kuten komentosarjojen suorittamisen useille palvelimille, muuttujien käyttämisen tai ulkoisten komentosarjojen suorittamisen tiedostoista. Edelleen, voit suorittaa näitä komentoja esim. Windows Installerista käsin, jolloin voit syöttää TSQL-asennusskripteillesi ympäristömuuttujien arvoja esimerkiksi promptaamalla, saaden näin skriptit konfiguroitaviksi ja dynaamisiksi.

Voit aktivoida SQLCMD-tilan SSMS:ssä Query-valikosta valitsemalla ”SQLCMD Mode”, mikäli haluat suorittaa tietokantaskriptejäsi ko. tilassa:

Ympäristömuuttujat määritellään “:setvar” -määreellä, esimerkiksi:

:setvar DBFilePrimaryPath “C:\Program Files\MyDB\SQLData\Data”

Edelleen, ympäristömuuttujaan voidaan viitata T-SQL-skriptissä seuraavasti:

$(DBFilePrimaryPath)

Core-skripti

Core-skriptiin on hyvä sijoittaa ainakin:

• Tietokannan (uudelleen-)luonti
• Tietokanta-asetukset
• Loginit
• Käyttäjät
• Roolit
• Käyttöoikeudet
• Kredentiaalit
• Proxyt

SQL Server-tietokannan (uudelleen-)luonti

Ohessa esimerkki käyttäjätietokannan (uudelleen-)luonnista:

Huomaathan, että skriptissä on parametroitu muun muassa tiedostojen saantipolut, oletuskoko sekä kasvuvauhti tietyillä oletusarvoilla. Tällä tavalla käyttäjä voi asennuksen yhteydessä, esimerkiksi Windows installerin kautta vaikuttaa deployattavan tietokannan sijaintiin, kokoon sekä kasvuvauhtiin, jolloin ne saadaan heti konfiguroitua ympäristölle optimaalisiksi.

Koska skripti on uudelleenajettava, seuraavaksi skriptissä poistetaan mahdollisesti jo luotu tietokanta. Sitten luodaan tietokanta. Tämän jälkeen tulee tietokannan varsinainen luontiskripti, jossa on parhaana käytäntönä omat file grouppinsa big datalle, datalle sekä indekseille jaoteltuina kukin neljään datatiedostoon suorituskyvyn takaamiseksi.

Filegroupeista ja niiden luonnista sen verran, että SQL Serverin versiosta 2016 lähtien traceflag 1117:aa tietokantatasolla emuloiva AUTOGROW_ALL_FILES -optio kannattaa laittaa heti alkuun päälle: Tällöin, kun datatiedostoa kasvatetaan, kasvatetaan kaikkia tiedostoja samanaikaisesti, jotta ne pysyvät samankokoisina, mikä vähentää allokaation contentionia. Huom! Kyseisen määrityksen aikana kannassa ei saa olla kiinni muita käyttäjiä, joten sitä ei pysty enää tekemään onlinena, vaan se kannattaa laittaa heti päälle:

ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [BIG_DATA] AUTOGROW_ALL_FILES

Tiedostokoot ja niiden kasvattaminen SQL Server-tietokannassa

Tämän lisäksi keskeisiä seikkoja ovat myös järkevät aloituskoot ja kasvuinkrementit loki- ja datatiedostoille, jotka riippuvat installaation koosta ja tiedostojen kasvuodotuksista. Tästä aiheesta voisikin kirjoittaa oman bloginsa, kuitenkin ohessa muutama keskeinen nosto: Nyrkkisääntö kasvunvaralle on noin 10 – 25% per inkrementti, mutta kuitenkin siten, että määrittellään kasvu magatavuina, ei prosentteina. Näin kasvu ei karkaa lapasesta ajan saatossa. Pienemmillä tietokannoilla voi olla sopivaa 64 – 256 MB kasvuinkrementti, kun taas suuremmilla se voi olla helposti jopa 1, 2, 4 GB tai enemmän.

Vakioitu tiedostokoon kasvu vähentää tiedostojen pirstoutumisen riskiä. Kun datatiedostot kasvavat pienin, tasaisin askelin, käyttöjärjestelmän on helpompi varata yhtenäistä levytilaa, mikä auttaa ylläpitämään hyvää I/O-suorituskykyä. Pirstoutuneet tiedostot voivat lisätä levyn I/O-toimintoja, mikä hidastaa tietokannan suorituskykyä. Tyypillisesti kannattaa myös laittaa instant file initialization -optio päälle (”perform volume maintenance tasks”). Se on SQL Serverin ominaisuus, joka mahdollistaa datatiedostojen merkittävästi nopeamman luomisen ja laajentamisen ohittamalla uuden levytilan nollaamisen. Tämä on erityisen tärkeää siksi, että SQL Server joutuu joka kerta odottamaan, kun sen datatiedosto kasvaa. Jos instant file initialization -optio ei ole päällä, se on SQL Serverin näkövinkkelistä vähän kuin katselisi robotin kävelyä mudassa.

Kun SQL Server luo tapahtumalokitiedoston tai kasvattaa sitä, se jakaa lokitiedoston pienempiin segmentteihin, joita kutsutaan virtuaalisiksi lokitiedostoiksi (vlf). SQL Server hallitsee näitä VLF-tiedostoja sisäisesti mahdollistaen tapahtumalokin tehokkaan käsittelyn. Luotujen VLF-tiedostojen määrä riippuu tapahtumalokitiedoston koosta ja niiden lisäysten koosta, joilla loki kasvaa. Lokitiedostoissa n. 1000 vlf:ää alkaa olla tyypillisesti SQL Serverin toimintaa hidastava määrä. Tämän takia ei kannata antaa lokitiedostolle alun alkaenkaan liian pientä tiedostokokoa.

Näin ollen, kuten arvata saattaa, olisikin suotavaa, että että data- ja lokitiedostojen autogrowtheja yritetään välttää viimeiseen asti. Se on aina hidastava tekijä, vaikka Instant File Initialization -oikeus olisi käytössä. Tiedostot kannattaakin jo alkuun määritellä täyttämään n. 80-prosenttisesti niille suunnitellut ja määritellyt levytilat, mikäli tämä vain suinkin on mahdollista.

Huom! Jos olet tekemässä tietokantaasi muulle kuin fyysiselle tai virtualisoidulle palvelimelle, preferenssit ovat esimerkiksi Azure PaaS-ratkaisussa (Managed Instance, SQL database) kovin erilaiset, eikä yllä oleva skripti ole näihin käyttötapauksiin sinällään käyttökelpoinen. Tässäkin olisi uuden blogin kirjoittamisen paikka.

Pari huomiota tietokantaoptioista

Tietokantaoptioista nostan pari tärkeää optiota ylitse muiden:

• SET AUTO_UPDATE_STATISTICS -optio kannattaa pitää päällä, mikäli haluat, että SQL Server päivittää automaattisesti indeksi- ja taulustatistiikkoja, kun ne happanevat. Prosessi lähtee käyntiin silloin, kun jokin kysely koskee vanhentuneisiin taulu- tai indeksistatistiikkoihin. (~>=20% muuttunutta dataa). Tämä johtaa yleensä tasaisempaan ympärivuorokautiseen suorituskykyyn kuin ilman tätä optiota, koska query optimizer kykenee tekemään järkevämpiä suoritussuunnitelmia. Kuitenkin esimerkiksi ympäristössä, jossa on SQL Serverin standard edition tai muutoin niin laajoja ja hardware-resurssien kannalta raskaita indeksi- ja statistiikkapäivityksiä, että ne halutaan ajoittaa aina palveluajan ulkopuolelle, ei tuolloin tätä optiota kannata pitää päällä laisinkaan.
  
• SET AUTO_UPDATE_STATISTICS_ASYNC -option ollessa päällä SQL Server sallii kyselyn suorittamisen samalla, kun sille tuoreutetaan statistiikkoja, mutta vain, jos myös SET AUTO_UPDATE_STATISTICS -optio on päällä. Tällöin SQL Serverin ei tarvitse odottaa tähän kyselyyn liittyvien statistiikkojen päivitystä, joka voi kestää joskus useita minuutteja, pahimmillaan jopa tunteja, vaan käyttää vanhaa, saatavilla olevaa statistiikkaa kyselyn suorittamiseen. Tätä optiota kannattaa siis soveltaa etenkin silloin, kun sinulla on isoja, herkästi muuttuvia tietokantatauluja, joihin kohdistuvissa kyselyissä on korkea vasteaikavaatimus.

Loppusanat

Muista kuitenkin, että vaikka kuinka yrittäisit ennakoida ja parametroida kaikki mahdolliset ympäristömuuttujat, on huolellinen integraatio- ja hyväksymistestaus kaiken A ja O. Silti, etenkin tuotteen ensimmäisissä versioissa, saattaa herkästi jäädä jotain oleellista tai yllättävää huomioimatta. Rapatessa roiskuu.

Haluatko kysyä jotakin SQL Server DataOpsiin tai tietokantoihin liittyen? Ole hyvä ja ota yhteyttä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Microsoft SQL Server-tietokantaskriptien luominen ja skriptien ylläpito ovat oma taiteenlajinsa, josta iso osa on ohjelmistotuotannollisia periaatteita, alan parhaita käytäntöjä, sekä koko joukko erilaisia teknisiä niksejä, jotka helpottavat lähdekoodinhallintaa.

Yleisesti ottaen systeemityössä tietokannan rakennetta (kaikki objektit) kutsutaan englanniksi nimellä schema, joka taasen ei ole sama asia kuin SQL Serverin schema. Edelleen; tietokannan päivitystä uuteen tilaan kutsutaan englanniksi yleensä termillä schema migration, eli skeemamigraatio.

Kaksi pääsuuntausta skeemamigraatioon ovat:

• Migraatioskriptit, jotka ajetaan aina järjestyksessä ja vievät skeeman hallitusti tunnettuun tilaan versiomuutos kerrallaan. Yleissääntönä vain lisätään uusia skriptejä, ei poisteta vanhoja
• Tilaperusteiset (state) strategiat, joissa määritetään tavoitetila ja softatyökalut koettavat päätellä lennossa, miten se saadaan haluttuun tavoitetilaan. Tällaisia työkaluja ovat esimerkiksi Visual Studion SQL Server Data Tools (SQL Server Data Tools | Visual Studio – Visual Studio (microsoft.com) sekä Redgate SQL Compare.

Tässä blogisarjassani käsittelen ensiksimainittua tapaa, joka mahdollistaa mielestäni koodipuolella enemmän asioita, vaikka onkin työläämpää ylläpitää ja kehittää. Blogi SQL Server Data Toolsista ja vastaavista työkaluista saa siis ainakin toistaiseksi vielä odottaa.

Tarkoituksenani on käydä läpi myös hieman tietokantasuunnittelun käytännön filosofiaa sekä toteutustapaa, jossa tietokantaskriptit ovat alun alkaen dynaamisia, eikä sinun tarvitse kyhätä tietokannallesi ylenmäärin luonti- ja ylläpitoskriptejä. Lisäksi käyn läpi perusteita pilkkoa luontiskriptit loogisiin kokonaisuuksiin, jotta tietokantaratkaisusta tulee ohjelmistotuotannollisesti modulaarisempi ja monikäyttöisempi eri päivitysskenaarioihin liittyen. Blogisarjassa sivuutetaan hieman myös lähdekoodinhallintaa.

Oma taustani Microsoft SQL Server-tietokannoissa alkaa vuodesta 1999, josta lähtien olen suunnitellut ja koodannut useita kymmeniä tietokantaratkaisuja, myös kansainvälisiä tietokantatuotteita kuten Wicom Data Universe sekä SQL Governor. Nykyisin johdan SQL Governorin R&D:tä sekä toimin yhtenä teknisistä liideistä dataprojekteissamme DB Pro Servicessä.

Pilko elefantti osiin – SQL-skriptien modularisointi

Kuten sanottua, tietokantaskriptien modulariointi tekee tietokantaratkaisustasi joustavamman eri käyttöskenaarioihin. Tämän lisäksi skriptit säilyvät helpommin ylläpidettävinä ja muodostavat hyvin suunniteltuina selkeitä, loogisia kokonaisuuksia, jotka on helppo vaiheistaa, olipa sitten kysymys clean installista, softapäivityksestä, hotfixistä tai asennuksen poistosta. Sinun kannattaakin kategorioida skriptimoduulisi näiden loogisten kokonaisuuksien pohjalta:

• Clean install = Softan ensimmäinen asennuskerta hermeettiseen ympäristöön. Tätä varten on viisasta luoda muuttumaton skriptirunko, jossa tietokannan ns. corekomponentit asennetaan ja jäädytetään tiettyyn versioon.
• Softapäivitys = Uuden softaversion päivittäminen edellisestä seuraavaan major- tai minor -versioon. Tätä varten tarvitset omat sql-päivitysskriptit eli ns. skeemamigraatioskriptit. Tällä tavalla voit tarvittaessa kelata tuoteversiopäivityksen useita versioita eteenpäin.
• Hotfix = Pikakorjaus, jossa yleensä ajetaan yksittäinen .sql-tiedosto korjaamaan yksittäistä bugia. Hotfixit on aina hyvä koostaa seuraavaan major- tai minor-softapäivitysversioon.
• Asennuksen poiston tulisi jättää ympäristö mahdollisimman lähelle asennusta edeltänyttä tilaa ja siivota vanha softakoodi pois ympäristöstä kokonaisuudessaan. Tätä varten tarvitset yleensä vain yhden tietokantaskriptin.

Clean install -skriptimoduulit

Clean installissa on yleensä eniten skriptimoduuleja, koska se toimii runkona koko tietokantaratkaisullesi. Skriptit kannattaa dynamisoida SQL command-parametreillä, jolloin niitä voi ajaa kätevästi esim. Windows installerista käsin kutsuen. Windows installerin on toki toivuttava kaikista niistä mahdollisista tilanteista, joissa tietokantaskripteissä tulee poikkeuksia. Lisäksi skriptimoduulien tulee olla uudelleen ajettavia (=idempotent), eli kaikkialla tietokantaskripteissä on käytettävä mm. DROP / CREATE- taikka CREATE OR ALTER -määreitä ja tarpeen tullen TRUNCATE TABLE -määrettä.

Tyypilliset clean install-skriptimoduulit ovat:

• Core-skriptiin on hyvä sijoittaa tietokannan (uudelleen-) luontiskriptit käyttäjyyksineen. Ethän käytä default sa-tunnusta, sillä tämä ei ole koskaan tietoturvallinen vaihtoehto SQL Server-ympäristössä.
• Tables and views -skriptiin sijoitetaan taulut ja näkymät uudelleenluonteineen.
• Drop procedures -skripti tiputtaa kaikki user stored proceduret ja käyttäjäfunktiot kannasta.
• Table types -skripti (uudelleen-)luo kaikki taulutyypit.
• Create procedures -skripti (uudelleen-)luo kaikki user stored proceduret ja käyttäjäfunktiot. Jos proseduureja on todella paljon ja ne ovat pitkiä, haluat ehkä modularisoida proseduurit ja funktiot kunkin omikse skripteikseen.
• Encrypted procedures -skripti (uudelleen-)luo kaikki kryptatut user stored proceduret.
• Populate tables -skripti truncatoi ja populoi käyttäjätaulujen sisällön (=alustus).
• Jobs -skripti (uudelleen-)luo ratkaisun SQL Server jobit.
• Version -skripti hoitaa lopuksi versiopäivityksen lokitauluun.

Tulen tässä blogisarjassa kirjoittamaan osasta keskeisimpiä skriptimoduuleja erillisiä blogeja, joissa kuvaan kunkin skriptimoduulin parhaita käytäntöjä hieman tarkemmin koodiesimerkkeineen.

Softapäivitysskriptimoduulit

• Version upgrade -skripti (yksi per versiosiirtymä, yhden major-version inkrementein)
• Version -skripti hoitaa lopuksi major- tai minor -versiopäivityksen lokitauluun.

Hotfix-skriptimoduulit

• Hotfix-skripti sisältää kuvauksen korjatusta ongelmasta sekä itse spesifin korjauksen havaittuun bugiin. Suosituksena on enintään yksi bugikorjaus per yksi tiedosto.
• Version -skripti hoitaa lopuksi hotfix-versiopäivityksen lokitauluun.

Asennuksen poistoskriptimoduulit

• Undeploy-skripti hoitaa koko tietokannan poiston.

Tietokannat kasvavat – ajattele isosti tai Siperia opettaa

Muutama varoituksen sananen agile-kulttuuria vastaan, nimenomaan tietokannoissa kun se tuppaa olemaan joiltan osin haastavaa, tai jopa ei-sovellettavaa: Tietokannat kasvavat ajan saatossa, yhdet lineaarisesti, toiset eksponentiaalisesti. Ohjelmistotuotannollisesti sinun tulisi kyetä edes karkeasti arvioimaan, kuinka suureksi tietokantaratkaisusi voi kasvaa seuraavien 10 vuoden aikana, sillä ei ole lainkaan tavatonta, että tietokantaohjelmistojen elinkaaret ovat jopa kymmeniä vuosia. Esimerkiksi SQL Governor -tietokantaohjelmistomme enimmäinen versio näki päivänvalonsa jo loppuvuodesta 2011. Tätä blogia kirjoittaessani teemme parhaillaan ohjelmistoversiota 16.

On varsin tyypillinen virhe, että tietokantatarpeet alimitoitetaan alussa. Normaali kehityskaari, jonka olen vuosien saatossa nähnyt on, että ensin on tehty jotain XML- tai JSON-pohjaista ja sitten siirrytty partitioimattomaan standalone-OLTP-kantaratkaisuun ja kun tämäkään ei riitä, aletaan kyhäämään taulupartitiointia ja kompressointia sekä viimein erillistä datamarttia, että saadaan operatiivista ja analyyttistä kuormaa jaettua keskenään. Edelleen saatetaan päätyä jopa klusteroituun HA-ratkaisuun. Tällainen kehityskaari on tehoton ja maksaa tietokantakeskeisessä ohjelmistokehityksessä pahimmillaan jopa moninkertaisen euromäärän verrattuna siihen, että alun alkaen mietitään ohjelmiston kasvupotentiaali ja kohdemarkkina siten, että se kestää asiakassegmentin muutoksen isompaan kategoriaan, mahdollisen tietokannanhallintajärjestelmän saatavuusluokituksen nousun sekä siirtymän on premisestä julkipilveen.

Esimerkiksi SQL Governor-tuotteemme tietokannat olivat ohjelmiston alkutaipaleella maksimissaan gigatavun luokkaa. Tänä päivänä ne voivat kasvaa järeimmissä asiakasympäristöissä viidessä vuodessa jopa useiden teratavutasolle, kun lähdetietokantapalvelimia on useita satoja. Onneksi toteutimme tietokantaan lokitaulujen rinnalle jo alun alkaen tähtimallinnetut, conformed data warehouse bus -arkkitehtuurin mukaiset taulut, joka mahdollistaa tehokkaan ja kompaktin historiatietojen tallentamisen ja raportoinnin isoillakin datamäärillä. Suurimmat (~>30M riviä) tietokantataulut tulee partitioida heti alun alkaen. SQL Server -tietokantoihin kun on työlästä ja aikaa vievää sorvata partitiointia jälkikäteen ilman erinäisiä ei-triviaaleja skriptejä ja datamigraatiosta aiheutuvia käyttökatkoja – etenkin jos kyseessä on tuote. Siperia opettaa.

Lähdekoodin hallinnasta

Lähdekoodin hallintaan suosittelen Git:iä, koska se on ilmainen ja integroituu Azure DevOpsiin. Tietokantaskriptien osalta on aivan yhtä keskeistä kuin managed koodissakin, että versioidaan release candidaatteja jo hyvin aikaisessa vaiheessa osana muuta build-toiminnallisuutta.

Loppusanat

Tietokantojen lähdekoodinhallinta on monimutkaista ja pikkutarkkaa puuhaa. Juurikin tietokannoille ominainen datamassojen persistointi tuo enemmän haasteita päivityksiin ja bugikorjauksiin kuin managed-koodissa. Ja vaikka kuinka yrittäisit valmistautua kaikkeen ikävään, migraatioskripteissäkin voi tulla tuotannossa yllätyksiä mm. suurista datamääristä johtuen. Myös erilaiset ympäristötekijät, kuten aikavyhöhykkeet ja kollaatiot voivat hankaloittaa tietokantaskriptien yhteensovittamista. Toimintatapoihin vaikuttavia erityispiirteitä ja haasteita voivat myös olla riippuvuuksien määrä yhdistettynä sekä asiakasympäristöjen monimuotoisuuteen.

On kuitenkin paljon käytäntöjä, joilla helpottaa tätä tuskaa. Juuri näihin kikka-kolmosiin pureudun tässä blogisarjassani.

Kiinnostuitko SQL Server-tietokannoista? Voisimmeko ehkä olla avuksi? Ole hyvä ja ota yhteyttä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

CEO & Chairman

DB Pro Services Oy

Johdanto

Kuka käyttää tietokantoja? Kaikenlaiset monikansalliset, valtiolliset, kunnalliset, viranomais- sekä kaupalliset tahot kuin myös yhdistykset tarvitsevat toimintaansa tietokantoja. Tietokannat voivat sijaita pilvipalveluiden takana sekä ylipäätään erilaisissa laitteissa, kuten palvelimissa, pöytätietokoneissa, kännyköissä, tai sulautetuissa järjestelmissä. SQL-tietokannat ovatkin hallinneet maailmaa jo useiden vuosikymmenien ajan, ja ovat tänä päivänä yhä eniten käytetyin tietokantojen ilmenemismuoto. Pienemmissä yrityksissä voi olla vain kourallinen yksittäisiä tietokantoja, kun taas suuressa monikansallisessa yhtiössä tai softatalossa niitä voi olla jopa miljoonittain. Jokaista tietokantaa tulee hoitaa eri tavoin. SQL-tietokannat eivät pysy ajan saatossa toimintakuntoisina huoltamattomina. Tämä blogini tekee katsauksen tietokannanhallinnan eri aspekteihin juuri tästä näkökulmasta: Mitä tulisi valvoa, jotta vältytään tietokanta-alustojen erilaisilta vaaratilanteilta?

Mietitäänpä ensin, mitä tietokannat sisältävät. SQL-tietokantoihin tallennetaan sekä yrityksen transaktionaalinen data (OLTP), että niistä summattu ja jalostettu historiatieto (datamart, Data Warehouse). Transaktionaalisia SQL-tietokantoja ovat tyypillisesti erilaiset SCM-tietokannat sekä ERP-tietokannat, talousohjelmistojen tietokannat, CRM-tietokannat sekä muut sisällönhallinnalliset tietokannat ja rekisterit ja erilaiset tietovarastot. Yleisesti ottaen tietokantoja tarvitaan miltei poikkeuksetta sellaisissa sovelluksissa ja järjestelmissä, jotka keräävät paljon dataa, ovat reaaliaikaisia tai historioivat dataa päätöksenteon tueksi. Toisin sanoen, SQL-tietokanta sisältää liiketoiminnan kannalta keskeistä, kriittistä tietoa. Ilman tätä tietoa monet yritykset, yhteisöt ja laitteet eivät yksinkertaisesti pysty toimimaan.

Kun tietokantasi sanoo poks

Mitä tapahtuu, jos tietokantaa pyörittävä fyysinen alusta tai jokin softakonfiguraatio hajoaa niin, että tulee käyttökatko? Miten tästä toivutaan? Kuinka monta päivää, tuntia, minuuttia tai jopa sekuntia kyetään toimimaan ilman toimivaa tietokanta-alustaa ja miten paljon dataa ollaan valmiita käyttökatkon takia menettämään? Entä kuinka tällaisista kriisitilanteista toivutaan mahdollisimman luotettavasti, nopeasti ja tehokkaasti? Kaikkeen tähän vastauksena on DBA (Database Administrator), siis henkilö, joka pitää huolta tietokannoista teknisesti. Minun mielestäni jokaisella softatalolla, keskisuurella sekä suuryrityksellä tulisi olla vähintään yksi DBA, joskus jopa tiimi, jonka lisäksi yrityksen on hyvä käyttää ulkoisia, erityisluontoisia DBA-palveluita kokeneelta ulkoiselta toimijalta. Tämä siksi, että tuskin kukaan DBA hallitsee kaikkia tietokannanhallinnan osa-alueita täydellisesti ja ulkoiset toimijat ovatkin hyvin usein erikoistuneet joihinkin tiettyihin vaativiin DBA-toimintoihin. Tällöin omalle DBA:lle ja / tai konesalipalveluntarjoajan DBA-tiimille säästyy paremmin resursseja yrityksen SQL-tietokantojen ydintoiminnoista huolehtimiseen ja luonteeltaan usein väliaikaiset mutta vaativat spesialistien tehtävät voidaan jättää kolmansille osapuolille. Näin saadaan laadukkain ja kustannustehokkain kokonaisratkaisu.

Seuraavissa kappaleissa käyn läpi keskeisiä näkökulmia tietokantojen ylläpitämisen suhteen: Mikä voi mennä pieleen. Vastaamme on tullut DB Pro:n ja DB Pro Services:in toimintavuosien varrella useita satoja erilaisia Microsoft SQL Servereiden ongelmakohtia, jotka olemme, uskallan sanoa, yli 99-prosenttisesti, kyenneet tehokkaasti ratkaisemaan!

Saatavuus

Tietokantojen saatavuus on kaiken A ja O. Jos esimerkiksi yhteys liiketoimintakriittiseen tietokantaan lakkaa, peli on menetetty ja aletaan laskea vahinkojen määrää. Suuremmissa yrityksissä vahingot ovat tällöin tyypillisesti kymmenistä tuhansista euroista miljooniin euroihin päivässä. Mitä sitten pitäisi tehdä, jotta tällaiselta vältyttäisiin?

SQL-tietokantapalveluiden saatavuuden osalta seurattaviin kohteisiin kuuluvat mm. erilaiset julkiset tietokantapalvelut (IaaS, PaaS, SaaS), data centerit,  verkko, tallennuskapasiteetti sekä fyysiset palvelimet. Ohjelmistoteknisiä osa-alueita ovat mm. virtualisointi ja hyperkonvergenssi kuten Nutanix ja VMware, erilaiset korkean käytettävyyden (HA) ratkaisut sekä käyttöjärjestelmät, SQL-tietokannanhallintajärjestelmät ja tietokannat itsessään varmuuskopioineen.

Kokemukseni mukaan tyypillisimmät virheet ovat vastoin parhaita käytäntöjä tehdyt laite- ja tietokantakonfiguraatiot, alimitoitettu kapasiteetti sekä inhimilliset erehdykset. Yleisiä syitä tietokantojen saatavuusongelmiin ovat mm. ennakoimattomat työkuormahuiput, standalone-palvelimen vikaantuminen, jolloin tietokannatkin lakkaavat toimimasta, tai HA-ratkaisussa tietokannanhallintajärjestelmän palvelinnoodien vikautunut yliheitto, kun  työkuormaa ei saada onnistuneesti siirrettyä vikaantuneelta palvelimelta toiselle, sekä riittämätön kapasiteetti kun yhden tai useamman vikaantuneen palvelininstanssin työkuormaa uudelleenohjataan yliheitettyyn palvelimeen. Muita hyvin tyypillisiä tapauksia ovat tietokantojen datatiedostojen epäoptimaaliset konfiguraatiot tallennusjärjestelmää vasten, korruptoituneet tietokannat sekä toimimattomat tietokantapalautukset sekä vakavat lukkotilanteet (deadlock) operatiivisissa tietokannoissa, pintaa raapaistaakseni.

Näiden ehkäisemiseksi voidaan auditoida tietokanta-alustan konfiguraatiot sekä toteuttaa ne parhaiden käytäntöjen mukaan. Täytyy myös muistaa, että jokaisella rauta- ja softatoimittajalla on useimmiten omat suosituksensa. Myöskään tietokantabackupeilla ei tee mitään ilman säännönmukaisia palautustestauksia ja operatiivisella tietokannalla voi joutua äkkiä heittämään vesilintua, ellei indeksien ja statistiikkojen huoltoajot ole konfiguroitu tietokantaympäristölle optimaalisella tavalla ja niin edelleen.

Suorituskyky

Huonontunut SQL-tietokannan suorituskyky voi pahimmillaan aiheuttaa saatavuusongelman. Heikko SQL-tietokannan suorituskyky voi tuottaa erittäin huonon käyttäjäkokemuksen ja näin nakertaa käyttäjien työmotivaatiota sekä ennen kaikkea hidastaa organisaation normiprosesseja ja sitoa ylimääräisiä henkilöstöresursseja merkittävästi käyttäjäkunnan osalta, joskus jopa useita kymmeniä prosentteja. Tälle hintalapun laskeminen voi olla jopa hieman pelottavaa. Esimerkiksi softataloille tämä on todella kriittistä: Tietokannan on toimittava mahdollisimman tehokkaasti, koska jokainen uusi softan käyttäjä on kertoimena X tietokantaratkaisun kustannusrakenteelle: Ohjelmointivirheiden vaikutukset kumuloituvat ja onnistunut käyttäjäkokemus on keskeinen imagoasia.

Tyypillisiä SQL Server -tietokantojen suorituskyvyn pullonkauloja löytyy mm. käyttöjärjestelmäasetuksista, tietokantainstanssien konfiguraatioista, tietokantojen asetuksista ja kompressoinnista, tietokantataulujen partitioinnista, indeksoinnista, -statistiikasta ja niiden huoltoajoista sekä eritoten T-SQL-koodista. Kokemukseni mukaan 70% performanssiongelmista johtuukin huonosta T-SQL-koodista ja epäoptimaalisista asetuksista, loput 30% laiteongelmista ja eritoten kapasiteettivajeesta.

Performanssiongelmia voidaan ehkäistä diagnosoimalla huonosti toimivat tietokantapalvelimet ja tietokannat, tyypillisesti käyttämällä jotain ulkoista ohjelmistoa kuten SQL Governor-ohjelmistomme, jolloin saadaan sekä kokonaiskuva, että detaljitason löydökset kaivettua systeemistä nopeasti esille kokeneen DBA:n avustamana ja kohdennettua korjaavat toimenpiteet priorisoidussa järjestyksessä oikeisiin kohteisiin.

Kapasiteetti

Kun palvelinalustan fyysinen kapasiteetti ei riitä, palvelin alkaa hidastua ja pahimmillaan aiheuttaa epäkäytettävyystilanteen. Juuri siksi SQL-tietokantojen huolellinen kapasiteettisuunnittelu on tärkeää. Fyysisessä kapasiteetissa tyypillisimmät rajat SQL-tietokannoilla tulevat vastaan tallennuskapasiteetissa (IOPS / throughput, levylatenssi) sekä prosessorikapasiteetissa ja muistissa, joskus myös verkossa. Esimerkiksi prosessorin ylikuormittuminen on usein kriittinen ongelma OLTP-tyyppisessä tietokantapalvelimessa: Suoritettavia tehtäviä on prosessorilla enemmän kuin se ehtii parhaimmillaankaan hoitaa.

Paras keino välttää SQL-tietokantojen kapasiteettivaje suunniteltaessa uutta ympäristöä on pitkälliseen historia- ja trenditelemetriikkaan perustuva analyysi, jossa pyritään ottamaan huomioon mm. palvelinkapasiteetin peruskuorma eli baseline, sen kausivaihtelut, kuormapiikit, sekä palvelukatkosten ja patchayksen aiheuttamat anomaliat palvelin- instanssi- sekä tietokanta- ja aina datafile-tasolla saakka. Tällä tavalla pystytään ennakoimaan ja tarkasti laskemaan nykyhetken ja tulevaisuuden kapasiteettitarpeet. SQL Governor-ohjelmistossamme on useita kansainvälisiä patentteja sisältävät laskentamoduulit, joilla voidaan tarkasti määrittää tämänhetkinen sekä tulevaisuuden kapasiteettitarve palvelin-, instanssi- ja kanta sekä datafile-kohtaisesti sekä laskemaan auki erilaiset HA- ja migraatio ja konsolidointiskenaariot esimerkiksi on premisestä pilvialustaan tai toisinpäin. Olemme käyttäneet SQL Governor-ohjelmistoamme menestyksellisesti lukemattomissa migraatioissa ja pilvitransitioissa vuosien varrella.

Tietoturva

Tietoturvaa ei ole kenelläkään vara lakaista maton alle, etenkään näinä aikoina. Tässä ennaltaehkäisevä toiminta on kaiken keskiössä. SQL-tietokantaympäristöissä on lukuisia asetuksia, jotka tulee ottaa huomioon mahdollisimman tietoturvallisessa ratkaisussa. Täytyy varautua myös siihen, että tietokannat joutuisivat joka tapauksessa vääriin käsiin. Tällöin on syytä kryptata kaikki liiketoimintakriittinen tieto, ja jo mieluiten tänä päivänä symmetrisillä, kvanttiturvallisilla tiedonsalausalgoritmeillä.

Tietoturvaan liittyy paljon vastuuta ja vaaranpaikkoja. Blackbelt DBA-tiimimme tarjontaan kuuluvat myös SQL Server -ympäristöjen tietoturva-auditoinnit. Auditoinnin lopputuloksena saat hyvän käsityksen tietokanta-alustasi turvallisuudesta sekä mahdollisista haavoittuvuuksista, sekä ohjeet haavoittuvuuksien korjaamiseksi.

Avain hyvinvoivaan SQL-tietokanta-alustaan on jatkuva seuranta

SQL-tietokanta on kuin viritetty kilpa-auto: Se vaatii määräaikaishuoltoja sekä jatkuvaa seurantaa. Kokenut DBA kykenee usein ennakoimaan epäkäytettävyystilanteita ja muita vaaran paikkoja seuraamalla esimerkiksi tietokantojen lukkotilanteita, raudan resource spillejä, wait statistiikan trendejä, kyselysuunnitelmien resurssikulutuksen kausiluontoisuutta ja kehitystä sekä indeksoinnin kokonaistehokkuutta.

Vanha kansanviisaus kuuluu; ”hätä ei tule kello kaulassa”. Näin tekoälyn aikakaudella uskallan kuitenkin haastaa tätä viisautta. Ennakoivaa analytiikkaa hyödyntävä SQL Governor-tuotteemme tarjoaa tähän kaikkeen tehokkaan, monella tapaa ennaltaehkäisevän työkalupaletin, jolla saat kattavan kokonaiskuvan SQL-tietokantaympäristöstäsi 24 / 7 / 365 ja pystyt usein ennakoimaan poikkeustilanteita, jolloin DBA:lle jää enemmän aikaa reagoida sekä näin keskittyä sinne missä apua tarvitaan. Blackbelt DBA:mme yhdessä SQL Governorin kanssa voi olla juuri sinun SQL-tietokantaympäristösi ”tapaturmavakuutus”.

Kiinnostuitko? Ota yhteyttä niin keskustellaan lisää!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy


Sinua saattaa kiinnostaa myös:
SQL tietokanta-historia
Ennakoiva analytiikka
Power BI pro ja eri lisensiointimallit

Blogisarjassani käsittelen tietokantojen kehittymistä 1960-luvulta tähän päivään sekä tulevaisuuden skenaarioihin. Tämä blogisarjan kolmas ja viimeinen osa hahmottelee sitä, millaisia haasteita tietokantojen nyyteknologioissa näen nyt ja tulevaisuudessa, sekä millaisia tietokantateknologioita tulevaisuus voisi tuoda tullessaan.

Esipuhe

Tulevaisuus. Mitä se on? Loppujen lopuksi tulevaisuus teknologian saralla on erilaisten resessiivisten ja näkyvien prosessien jatkumo, joka joskus ilmenee uusina innovaatioina ja yliopistollisen perustutkimuksen sekä soveltavista teknologioista tunnettujen puutteiden parannusinkrementteinä. Kaikki merkittävät uudistukset lähtevät ennakkoluulottomasta mitä jos -ajattelusta sekä naiivista unelmoinnista: ”Voisiko ehkä ollakin näin tai näin?” Tärkeintä on, että antaa tässä assosiatiivisessa horisontissa itselleen täyden vapauden ideoida, vaikka se onkin yleensä vain 2% koko aivotyöstä – ja loput 98% sitten sitä raakaa duunia.

SQL-tietokannat – mihin koskee?

Moderneissakin tietokannanhallintajärjestelmissä ajaudutaan melko usein erilaisiin suorituskyvyllisiin ongelmiin. Pullonkaulat voivat ilmetä esimerkiksi verkossa, tallennuskapasiteetissa, muistissa, prosessorikuormissa, tietokantataulujen lukituksissa, asetuksissa, indeksoinnissa ja statistiikassa – käytännössä miltei missä vaan. Kuitenkin, erinäisten tutkimusten sekä  kokemusperäisen tietoni pohjalta viimeisten 25 vuoden ajalta voin karkeasti arvioida, että keskimäärin 70% kaikista tietokantojen performanssiongelmista johtuu huonosta koodista ja 30% infra- sekä kapasiteettiongelmista.

Miksi sitten näin on? Syy on yhtälailla yksinkertainen kuin monimutkainenkin: Tietokannanhallintajärjestelmät ovat yksinkertaisesti kompleksisia kokonaisuuksia.

Data explosionin, tekoälyn ja julkipilvistymisen myötä tietokannanhallintajärjestelmille on syntynyt joukko uusia haasteita: Samalla kun kaikki pitää pakata pienempään, pitää pystyä yhtälailla lisäämään datan prosessointinopeutta sekä säilömään lähtökohtaisesti yhä toisarvoisemmalta tuntuvampi data talteen mahdollisia myöhempiä tietotarpeita varten: Juuri mitään dataa ei enää uskalleta heittää menemään – eikä toisaalta tarvitsekaan. Lisäksi kyberuhat ovat kasvaneet vihamielisten tahojen nostaessaan päätään maailmanpoliittisessa myllerryksessä – tiedon pitää olla samaan aikaan ”kaikkien ulottuvilla eikä kenenkään saatavilla”.

Tekoäly – sinne missä kipu on

Eräs kehityssuunta, johon olen itsekin kantanut korteni kekoon jo vuodesta 2011 lähtien kun ryhdyin yrittäjäksi, on tekoälyn nousu ja ylipäätään ihmisälyn kaltainen automaatio. Tästä on nähtävissä useita hyviä esimerkkejä esimerkiksi Microsoft-tietokantamaailmassa: SQL Server osaa ehdottaa käyttöstatistiikan perusteella sopivia tai ainakin osa-optimaalisia indeksejä, kyselyiden suoritussuunnitelmat vaihtelevat suotuisasti käyttötapauksista riippuen ja niihin voidaan tarpeen tullen vaikuttaa sekä edelleen konfiguroitaessa SQL-palvelininstanssia tietyt instanssikohtaiset asetukset sekä väliaikaistietokannan (tempdb) datatiedostomäärä optimoidaan automaattisesti installerin toimesta riippuen prosessoriytimistä jne jne.

Kuitenkin, hyvästä yrityksestä huolimatta, suurin osa aivotyöstä ja performanssiongelmien selvittelystä jää edelleen DBA: n ja tietokantakehittäjän päänvaivaksi. Miksei voisi esimerkiksi olla niin, että tietokanta osaisi edes raportoida siitä, että indeksistatistiikka ei ole kunnossa jonka seurauksena SQL Server arpoo sattumanvaraisesti erittäin hitaita suoritussuunnitelmia, välillä taas kunnollisia? Miksei tietokannanhallintajärjestelmä voisi itse havaita epäoptimaalisia konfiguraatioita ja tilastollisen päättelyn sekä anonyymin, telemetriikkapohjaisen parviälyn perusteella korjata, tai edes ehdottaa optimaalisempaa konfiguraatiota? Onko vika sitten liian modulaarisessa ajattelussa, kun tuhannet ja tuhannet sovelluskehittäjät koodaavat siiloissaan satoja uusia ominaisuuksia kuhunkin vallitsevaan tietokantateknologiaan joka vuosi? Onko visionäärisyys unohdettu? Minä ainakin tietokantaihmisenä olisin paljon tyytyväisempi kun nämä asiat olisivat kunnossa. Paljolti onkin keksintöjä kiittäminen tyytymättömyydestä: Eikö tätä voisi tehdä jotenkin järkevämmin?

Automaatio yleistyy – DBA-rooli polarisoituu

Eräs hallitsevien tietokannanhallintajärjestelmien automaatiota jarruttava osa-alue ovat pienemmät tietokantaohjelmistotalot, jotka ovat rakentaneet omaa IPR:ää tuotteidensa ympärille ja näin hidastaneet isoja softataloja tekemästä samankaltaisia automaatioita ja älykkäitä toiminnallisuuksia omiin tuotteisiinsa. DBA:t ottavat mielellään käyttöön juuri tällaisia työkaluja, jotka mahdollistavat tehokkaamman tietokannanhallinnan kokonaisuudessaan.

Kasvavan automaation seurauksena perinteinen tietokantojen hallinta tulee jäämään yhä vähemmälle tarpeelle: Oikeilla tietokantateknologioilla yksi DBA tekee viiden DBA:n duunit. Tämänsuuntaisesta teknologisesta kehityskulusta on erinomaisena esimerkkinä mm. Nutanixin NDB-tietokantatuote, joka abstrahoi monimutkaisen, eri tietokannanhallintajärjestelmien välillä erilaisen tavan tehdä versiopäivitykset, kloonaus, backupit sekä devops: Olkoon sitten tietokantasi SQL Server, Oracle, PostgreSQL tai vaikkapa MySql niin yhden helppokäyttöisen käyttöliittymän kautta voit hallinnoida kaikkia tietokantojasi samanmuotoisesti ja tehokkaasti. Tällöin aikaasi säästyy vaativammille tietokannanhallintatehtäville enemmän, kuten auditoinneille, kapasiteettisuunnittelulle, datamigraatioille ja performanssioptimoinnille. Toisaalta, näihinkin osa-alueisiin tulee koko ajan lisää automatisoivia tuotteita ja teknologioita, kuten open source-pohjainen, powershell-kirjastoihin perustuva dbatools sekä jo aiemmin mainitsemani SQL Governor-tuote.

Tämän kehityksen myötä DBA:n pitää ottaa haltuun yhä enemmän monimutkaisia asioita. Sellaisia asioista, joita on haastavaa tai miltei mahdotonta täysin automatisoida. Näitä ovat usein erilaiset DR-tilanteet, data-arkkitehtuurisuunnittelu, tietoturva ja tietomallinnus sekä monimutkaiset tietokantadiagnostiikat.

Uskallankin väittää, että jo tänä päivänä perinteinen DBA-rooli on polarisoitunut kahteen eri ryhmittymään: Lähempänä infraa olevat tietokannanhallintatehtäviin keskittyvät DBA:t, jotka käyttävät vahvasti eri automatisoinnin työkaluja, sekä data-arkkitehtuureihin, HA/DR:ään ja diagnostiikkaan keskittyvät DBA:t, jotka ovat toimineet alalla jo hyvin pitkään.

Kvanttitietokoneet tulevat – onko vahvasti salattu tietokantasi turvassa?

Suurin osa tietokannoistamme sisältää bisneskriittistä tietoa ja sen tulisi näin ollen olla salattua. Tähän on esimerkiksi SQL Serverissä ollut tarjolla vahvaa data- ja lokitiedostojen kryptausta (TDE) sisäänrakennettuna ominaisuutena jo pitkän aikaa. Kryptograafikkona eräs huolenaiheeni onkin ollut viime vuosina kvanttiteknologian sisäinen kilpajuoksu: Mustahatut vastaan valkohatut, eli kumpi on vallalla ensin – kvanttiturvallinen kryptaus vai perinteisen tiedonsalauksen potentiaalisesti hetkessä murtava kvanttilaskenta? Toisin kuin symmetrinen kryptaus kuten AES ja TDES riittävällä avainkoolla , asymmetrinen kryptaus on haavoittuvainen kvanttitietokoneiden laskentatehon edessä: Kvanttitekniikassa Shorin algoritmi voi kertoa suuria lukuja eksponentiaalisesti paljon nopeammin kuin klassiset tietokoneet. Koska epäsymmetriset algoritmit luottavat täysin siihen, että tavalliset tietokoneet eivät löydä alkutekijöitä nopeasti, ne ovat pysyneet turvassa. Valitettavasti monet epäsymmetriset salausalgoritmit on jo matemaattisesti todistettu rikottavaksi kvanttitietokoneilla käyttämällä Shorin algoritmia mukaan lukien RSA, Diffie-Hellman ja ECC. Onneksi kuitenkin SQL Serverin TDE on toteutettu AES / TDES:llä. Monet isot IT-talot kuten IBM onkin valmistautunut kvanttiaikakauteen kehittämällä kvanttiturvallisen teknologiaratkaisun: Quantum Safe | IBM Quantum Computing

Yhteenveto

Uudet teknologiat kuten tekoäly, koneoppiminen ja kvanttilaskenta avaavat uusia sfäärejä valtavien datamassojen tehokkaalle käsittelylle ja sovellusalueille: Ihminen tulee pääsemään lähemmäksi Laplacen demonia, jonka kaltaisen tietoteorian itsekin tietämättäni joskus parikymppisenä kehitin: 1800-luvun alussa mekanistisen luonnonfilosofian mukaan maailmaa voitiin tarkastella deterministisenä koneistona, jonka kaikkien osasten tarkat paikat ja nopeudet voitiin täsmällisesti tietää ja määritellä. Teoksessaan ”Taivaan mekaniikka”, aika pelottavan osuvasti, ranskalainen matemaatikko ja tähtitieteilijä Pierre-Simon de Laplace (1749–1827)  lausui seuraavaa: ”Jos älylle, joka pystyy hallitsemaan kaikkia luonnossa vaikuttavia voimia ja joka on niin suuri, että se voi analysoida kaiken, annettaisiin tiedot maailmankaikkeuden suurimpien kappaleiden liikkeistä ja keveimpien atomien käyttäytymisestä, se järjestäisi ne yhdeksi liikelaiksi; mikään ei olisi epävarmaa, niin tulevaisuus kuin menneisyyskin olisivat nykyisyyttä sen silmien edessä.” Väitänkin, että tekoälyn kehittymisen myötä ajaudumme vääjäämättömästi lähemmäs maailmankuvaa, jossa ihmisistä tulee älyllisesti laiskoja, mutta tiedollisesti kaikkivoipia: Keskimääräinen älykkyys laskee, väestö polarisoituu.

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

CEO & Chairman

DB Pro Services Oy

Tutustu myös näihin blogeihimme:
Ennakoiva analytiikka,
Power BI pro ja eri lisensointimallit
SQL-tietokanta: Nykytila

Tässä blogisarjassani käsittelen tietokantojen kehittymistä 1960-luvulta tähän päivään sekä pyrin hahmottelemaan tulevaisuutta. Tämä blogisarjan toinen osa kertoo ymmärrettävästi siitä, millaisia tietokantateknologioita nykyisin (2024) käytetään.

Taustaa

Tietokannanhallintajärjestelmät (DBMS) ovat olleet keskeisiä tietojen tallennuksen, haun ja hallinnan alalla vuosikymmeniä. DBMS:n kehitystä on ohjannut tarve käsitellä kasvavia tietomääriä, varmistaa turvallisuus sekä tarjota korkean käytettävyyden ja suorituskyvyn ratkaisuja, jotka voivat täyttää modernien sovellusten vaatimukset. Nykyaikaiset  DBMS:t heijastavat merkittäviä edistysaskelia ja innovaatioita, jotka vastaavat nykypäivän haasteisiin eri toimialoilla.

SQL-tietokantojen evoluutio historiasta nykypäivään

Tietokantaympäristöt ovat kehittyneet yksinkertaisista tiedostojärjestelmistä monimutkaisiin järjestelmiin, jotka on suunniteltu hallitsemaan suuria, hajautettuja kokonaisuuksia vaihtelevine ​​tietotyyppeineen. Perinteiset relaatiotietokannanhallintajärjestelmät (RDBMS) kuten Oracle, MySQL ja SQL Server, ovat pitkään hallinneet markkinoita. Nämä järjestelmät järjestävät tiedot tauluiksi ja käyttävät Structured Query Language (SQL) -kieltä tietojen määrittelyyn ja käsittelyyn.

Viime vuosina NoSQL-tietokantojen, kuten MongoDB, Cassandra ja Redis, suosio on kasvanut. Nämä järjestelmät on suunniteltu käsittelemään strukturoimatonta dataa tarjoamalla joustavia skeemoja ja tukemaan horisontaalista skaalausta. NoSQL-tietokannat ovat erityisen suosittuja big data -sovelluksissa, reaaliaikaisissa verkkosovelluksissa ja hajautetuissa arkkitehtuureissa.

Lisäksi on kehitetty NewSQL-tietokantoja, kuten Google Spanner ja CockroachDB, jotka yhdistävät perinteisen RDBMS:n ACID-ajattelun (Atomicity, Consistency, Isolation, Durability) NoSQL-järjestelmien skaalautuvuuteen. Nämä hybridiratkaisut pyrkivät tarjoamaan molempien maailmojen parhaat puolet varmistaen tietojen eheyden ja korkean käytettävyyden.

SQL-tietokannat pilvessä

DBMS:n integrointi pilvilaskentaan on mullistanut tavan, jolla tietokannat otetaan käyttöön, hallitaan ja skaalataan. Pilvipohjaiset tietokantajärjestelmät, kuten Amazon RDS, Microsoft Azure SQL Database ja Google Cloud SQL, tarjoavat hallittuja palveluita, jotka vähentävät organisaatioiden hallinnollista taakkaa. Nämä alustat tarjoavat automaattisia varmuuskopioita, korjauksia, skaalauksia ja korkean käytettävyyden, minkä ansiosta yritykset voivat keskittyä sovellusten kehittämiseen ja innovaatioihin runsaan ylläpitotyön sijaan.

Palvelimettomat tietokannat, kuten Amazon Aurora Serverless ja Azure Cosmos DB, lisäävät entisestään joustavuutta sallimalla resurssien automaattisen skaalauksen kysynnän mukaan. Tämä pay-as-you-go-malli varmistaa kustannustehokkuuden ja suorituskyvyn optimoinnin ilman manuaalisia toimenpiteitä.

SQL-tietokantojen suorituskyky ja skaalautuvuus

Nykyaikaiset DBMS-järjestelmät on suunniteltu käsittelemään nykyaikaisten sovellusten tuottamaa valtavaa datamassaa, jopa petatavuja ja enemmän. Tekniikoita, kuten sharding, partitiointi ja muistinvaraiset tietokannat (esim. Redis ja SAP HANA), on kehitetty suorituskyvyn parantamiseksi ja nopean tiedonsaannin varmistamiseksi.

Muistinvaraisettietokannat tallentavat tiedot järjestelmän käyttömuistiin levyn sijaan, mikä vähentää merkittävästi luku- / kirjoituslatensseja. Tämä on ratkaisevan tärkeää sovelluksille, jotka vaativat reaaliaikaista tietojenkäsittelyä ja analytiikkaa, kuten rahoituskaupan alustoilla ja IoT-järjestelmillä.

SQL-tietokantojen tietoturva ja komplianssi

Tietosuojan ja tietoturvan merkityksen kasvaessa nykyaikaisissa tietokannanhallintajärjestelmissä on vankat suojausominaisuudet. Staattinen salaus ja siirronaikainen salaus, edistyneet todennusmekanismit ja hienorakenteiset pääsynhallintalaitteet ovat vakiovarusteita. Myös GDPR:n, HIPAA:n ja CCPA:n kaltaisten säädösten noudattaminen on nykyisin ensisijaista – modernit tietokantaratkaisut tarjoavat työkaluja data governanceen ja audit trailien hallinnointiin.

Edistynyt analytiikka ja tekoälyn integraatio SQL-tietokannoissa

Kehittyneen analytiikan ja tekoälyn (AI) integrointi tietokantaan on avannut uusia mahdollisuuksia tiedon hyödyntämiseen. Nykyaikaiset tietokannat tukevat monimutkaisia ​​kyselyitä, tietovarastointia ja reaaliaikaista analytiikkaa, minkä ansiosta yritykset voivat saada käyttökelpoisia oivalluksia tiedoistaan – jopa kilpailueduksi tai innovaatioksi saakka.

Tekoälyllä toimivat ominaisuudet, kuten SQL Governor-tuotteen koneoppiva kapasiteettisuunnittelu ja ennakoivat hälytykset, taikka automatisoitu indeksointi, kyselyoptimointi ja poikkeamien havaitseminen parantavat modernien tietokannanhallintajärjestelmien  tehokkuutta ja luotettavuutta. Myös Oraclen Autonomous Database käyttää koneoppimista automatisoidakseen rutiininomaisia ​​tietokannan hallintatehtäviä, mikä parantaa suorituskykyä ja vähentää inhimillisiä virheitä.

Yhteenveto

Nykyaikaisten tietokannanhallintajärjestelmien tilaa leimaa monipuolisuus, suorituskyky ja jonkin sorttinen älykkyys. Erilaisten tietokannanhallintajärjestelmätyyppien monimuotoisuus perinteisistä RDBMS-järjestelmistä NoSQL:ään ja NewSQL:ään vastaa moderneihin tietotarpeisiin ja käyttötapauksiin. Pilvi-integraatio, muistinvarainen käsittely, vankka tietoturva ja tekoälyyn perustuvat toiminnot heijastavat tietokantojen jatkuvaa kehitystä ja mukauttamista vastaamaan digitaaliajan vaatimuksiin. Tietomäärän ja monimutkaisuuden kasvaessa jatkuvasti tietokantateknologioiden innovaatioilla on ratkaiseva rooli tiedonhallinnan tulevaisuuden muovaamisessa. Millainen tämä tulevaisuus sitten on? Kerron siitä lisää blogisarjani seuraavassa osassa.

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Tietojenkäsittelytieteessä vain harvat ismit säilyvät vuosikymmeniä. Kuitenkin SQL-relaatiotietokantojen kohdalla näin on päässyt käymään. Tässä blogisarjassani käsittelen tietokantojen kehittymistä 1960-luvulta tähän päivään sekä pyrin hahmottelemaan tulevaisuutta. Blogisarjani ensimmäinen osa valottaa tietokantojen aamunkoittoa ja historiaa.

SQL-relaatiotietokantojen historia lähtee liikkeelle jo 1970-luvulta saakka. Tätä ennen on kuitenkin hyvä ymmärtää, mistä lähtökohdista päädyttiin relaatiotietokantoihin ja SQL:ään.

Tietokannan määritelmä

Tietojenkäsittelytieteessä tietokanta on ”järjestetty kokoelma tietoja tai tietovarastotyyppi, joka perustuu tietokannanhallintajärjestelmän (DBMS) käyttöön.” Toisin sanoen se on ohjelmisto, joka on vuorovaikutuksessa loppukäyttäjien, sovellusten ja itse tietokannan kanssa tietojen tallentamiseksi ja analysoimiseksi.  Tietokannanhallintajärjestelmä kattaa lisäksi tietokannan hallinnointiin tarjotut keskeiset toiminnot. Tietokannan, tietokannanhallintajärjestelmän ja siihen liittyvien sovellusten kokonaisuutta voidaan kutsua tietokantajärjestelmäksi. Usein termiä “tietokanta” käytetään myös löyhästi viittaamaan mihin tahansa DBMS:ään, tietokantajärjestelmään tai tietokantaan liittyvään sovellukseen.

Pieniä tietokantoja voidaan säilöä tiedostojärjestelmässä, siinä missä suuremmat tietokannat voivat vaatia alustakseen useamman palvelimen muodostaman klusterin tai pilvipohjaisen alustaratkaisun. Tietokantasuunnittelu yleisesti on yhdistelmä teoriaa sekä parhaita käytäntöjä, jossa tulee keskeisenä elementtinä on tietomallinnus. Tämän lisäksi tietokantaa suunniteltaessa tulee ottaa huomioon tehokas datan esitysmuoto ja tallennus, kyselykieli / -kielet, tietoturva ja yksityisyys sekä hajautetun tietojenkäsittelyn haasteet mukaan lukien jatkuva pääsy dataan sekä vikasietoisuus. Tietomallinnuksesta voit lukea lisää blogisarjastani Tietomallinnus – intro – DB Pro Services.

Terminologia

Tietokanta” viittaa yleisesti relationaaliseen dataan, joka on järjestetty tietokannanhallintajärjestelmässä siten, että käyttäjät pääsevät ohjelmallisesti käsiksi tähän dataan tai sen osajoukkoon, jotka sijaitsevat yhdessä tai useammassa tietokannassa.

Tietokannanhallinta voidaan jakaa neljään eri pääkategoriaan:

“Data definition”tarkoittaa datan organisoimiseen liittyviä luonti- muutos- ja poisto-operaatioita, eli suomeksi sanottuna itse tietorakenteiden hallinnan toimenpiteitä.

“Update” tarkoittaa varsinaisen datan lisäys- muutos- ja poisto-operaatioita olemassa olevien tietorakenteiden puitteissa.

“Retrieval” tarkoittaa tiedon tarjoamista tietokannasta eteenpäin prosessoitavaksi muille sovelluksille ymmärrettävässä muodossa. Data voi olla tallennettuna valmiiksi ymmärrettävässä muodossa, tai sitten sitä pitää implisiittisesti yhdistellä tai muokata ymmärrettävään muotoon.

“Administration” sisältää suuren määrän erilaisia tietokantojen hallintaan liittyviä tehtäviä, kuten tietokannanhallintajärjestelmien asennus ja konfigurointi, käyttäjätilien ja käyttöoikeuksien hallinta ja tietoturva, datan eheystarkistukset, jatkuvuudenhallinta sekä korkea käytettävyys, tietokantojen varmistus- palautustoimenpiteet, performanssidiagnostiikka, kapasiteettisuunnittelu jne.

Fyysisesti tietokantapalvelimet ovat dedikoituja palvelimia, joissa tietokannat käyttävät tietokannanhallintajärjestelmää ja siihen liittyviä ohjelmistoja. Tietokantapalvelimet ovat yleensä moniprosessorisia tietokoneita, joissa on runsaasti muistia ja RAID-levyryhmiä, joita käytetään tehokkaaseen tallennukseen.

Tietokannat voidaan luokitella kolmeen eri pääryhmään niiden sisältämän datan esitysmuodon (data format) perusteella:

• Strukturoitu
• Semistrukturoitu
• Strukturoimaton

Strukturoitu data perustuu staattiseen datan esitysmuotoon eli skeemaan, jolloin kaikella datalla on samat tietokentät ja ominaisuudet. Tällöin eri oliot esitellään kaksiulotteisina tietokantatauluina, joiden kentät kuvaavat olioiden eri ominaisuuksia ja yksittäiset rivit tyypillisesti kutakin oliota. Hyvä esimerkki tästä on tietovarastoinnissa paljon käytetty päivätaulu, jossa on tehokkuuden ja yksinkertaisuuden vuoksi valmiiksi laskettuna keskeisiä ominaisuuksia eri päivämäärille.

D_DATE:

Strukturoitu data koostuu tyypillisesti relaatioista olioiden välillä, jotka ovat tyypillisesti luonteeltaan hierarkisia. Lue lisää relaatiomallista täältä: Tietomallinnus – Kolmas normaalimuoto (OLTP)  – DB Pro Services.

Semistrukturoitu data on informaatiota, jolla on jonkinlainen struktuuri, mutta joka voi varioida oliokohtaisesti. Tästä hyvänä esimerkkinä toimii esimerkiksi henkilö, jolla voi olla yksi tai monta puhelinnumeroa, tai ei ollenkaan puhelinnumeroa. Tunnetuimpia semistrukturoidun datan esitysformaatteja on JSON (JavaSript Object Notation). Tässä esimerkki parista kontaktihenkilöstä CRM-tietokannassa:

Kaikki data ei ole strukturoitua tai edes puolirakenteista. Esimerkiksi kuvilla, ääni- ja videotiedoilla, dokumenteilla sekä binääritiedostoilla ei yleensä ole tiettyä rakennetta. Tällaista dataa kutsutaan strukturoimattomaksi dataksi.

Relaatiotietokannat

Toinen tapa luokitella tietokantoja on jakaa ne relationaalisiin ja ei-relationaalisiin tietokantoihin. Relaatiotietokantoja ovat sellaiset tietokannat, joissa säilötään ja kysellään strukturoitua dataa. Tällaista dataa ovat oliot, kuten esimerkiksi CRM-tietokannoissa yritykset, henkilöt ja tuotteet. Jokaisella oliolla on oma pääavaimensa (=”primary key”), joka identifioi olion muista saman taulun olioista. Tätä avainta käytetään viittauksissa tauluista toisiin (=viiteavain eli ”foreign key”).

Pää- ja viiteavainten käyttö mahdollistaa relaatiotietokannan normalisoinnin, mikä osittain tarkoittaa duplikaattien poistamista siten, että esimerkiksi yksittäisen asiakkaan tiedot tallennetaan kerran ja vain kerran, eikä erikseen jokaisen asiakkaan tilaamalle tuotteelle. Datan kyselyyn ja tallentamiseen käytetään  SQL-kiletä (Structured Query Language), joka perustuu ANSI-standardiin, joka on sama useissa tietokantajärjestelmissä.

Ei-relationaaliset tietokannat

Ei-relationaaliset tietokannat ovat tiedonhallintajärjestelmiä, jotka eivät käytä relaatioskeemaa. Ei-relaatiotietokantoja kutsutaan usein NoSQL-tietokannoiksi, vaikka jotkut niistä tukevatkin SQL-kielen muunnelmaa. Näitä on neljää päätyyppiä:

• Avain-arvo -tietokannat (Key-value databases)
• Dokumenttitietokannat (Document databases)
• Kolumnaariset tietokannat (Column family databases)
• Graafitietokannat (Graph databases)

SQL-tietokantojen historia

SQL-tietokantojen historia lähtee liikkeelle 1960-luvulta, kun tietokoneiden prosessoriteho, muisti, tallennusmediat ja verkot tulivat pisteeseen, jolloin tietokantoja pystyttiin alkaa fyysisesti toteuttamaan. Tämän mahdollistivat lopullisesti 60-luvun puolivälissä kehitetyt tallennusjärjestelmät, kuten magneettilevyt, joiden käyttö yleistyi varsin nopeasti. Tätä ennen käytössä olivat sekventiaaliset magneettinauhat. Edelleen; tietokantateknologian kehittyminen voidaan jakaa kolmeen eri aikakauteen:

• Navigationaalinen aikakausi
• SQL/relationaalinen aikakausi
• Post-relationaalinen aikakausi

Kaksi tärkeintä varhaista navigointitietomallia olivat hierarkkinen malli ja CODASYL-malli (verkkomalli). Niille oli ominaista osoittimien (usein fyysisten levyosoitteiden) käyttö tietueiden välisten suhteiden seuraamiseksi.

Relaatiomallin esitteli Edgar F. Codd vuonna 1970. Tässä keskeisenä ajatuksena on etsiä dataa sisällön, eikä niinkään linkkien kautta. Kuitenkin, vasta 1980-luvulla tietokoneet alkoivat olla riittävän tehokkaita pyörittämään relaatiotietokantoja. Niistä tulikin hyvin suosittuja 1990-luvulla ja tänä päivänä ne ovat yhä käytetyimpiä SQL-tietokantatyyppejä. Näitä ovat mm. Microsoft SQL Server, IBM DB2, Oracle sekä MySQL.

1960-luku ja navigationaaliset tietokannat

Joskus 60-luvun puolivälissä “tietokanta” -termi alkoi vakiintua ensimmäisten direct-access-storagejärjestelmien myötä, mikä mahdollisti nauhapohjaisten järjestelmien päivittäisten eräajojen sijaan interaktiivisen käyttökokemuksen tietokoneiden kehittyessä tehokkaammiksi ja kyvykkäämmiksi. Tuohon aikaan Charles Bachman, IDS-tietokantatuotteen luoja, perusti Database Task Groupin CODASYL:in kanssa, joka standardoin COBOL-kielen. Vuonna 1971 markkinalle syntyi COBOL-pohjainen standardi, jonka ympärille versoi nopeasti monia tuotteita.

CODASYL-lähestymistapa tarjosi sovelluksille mahdollisuuden navigoida linkitetyssä tietojoukossa, joka muodostettiin suureksi verkoksi. Sovellukset saattoivat löytää tietueita jollakin kolmesta menetelmästä:

• Pääavaimen perusteella (ns. “CALC key”, joka luotiin tyypillisesti hashaamalla (HASH))
• Navigoimalla relaatioita pitkin (sets) tietueesta seuraavaan
• Skannaamalla kaikki tietueet sekventiaalisessa järjestyksessä (=alusta loppuun)

Myöhemmin järjestelmiin lisättiin ns. B-puita (B-tree), jotta voitiin tarjota tehokkaampia vaihtoehtoisia pääsypolkuja dataan. B-puuta voisi verrata puurakenteisesti navigoitavaan monitasoiseen puhelinluetteloon, jossa etsittävä data on järjestetty aakkosjärjestykseen ja siten merkittävästi nopeammin haettavissa kuin perinteisellä sekventiaalisella metodilla. Monet CODASYL-tietokannat lisäsivät loppukäyttäjiä varten myös ns. “deklaratiivisen kyselykielen”, joka oli erillään navigointisovellusliittymästä. CODASYL-tietokannat olivat kuitenkin monimutkaisia ​​ja vaativat huomattavaa koulutusta ja vaivannäköä hyödyllisten sovellusten tuottamiseksi.

1970-luku oli SQL-relaatiotietokantojen lähtölaukaus

Edgar F. Codd työskenteli IBM:llä San Josessa, Kaliforniassa, yhdessä haarakonttorissa, joka oli pääasiassa mukana kiintolevyjärjestelmien kehittämisessä. Hän oli tyytymätön CODASYL-lähestymistavan navigointimalliin, erityisesti “hakutoiminnon” puutteeseen. Vuonna 1970 hän kirjoitti useita artikkeleita, joissa esiteltiin uusi lähestymistapa tietokantojen rakentamiseen, joka lopulta huipentui uraauurtavaan tiedon relaatiomalliin suurille jaetuille “tietopankeille”.

Codd käytti matemaattisia termejä uuden mallinsa määrittämiseen: relaatiot (relation), monikot (tuples) ja toimialueet (domain) saivat korvata CODASYL:in taulukot, rivit ja sarakkeet. Codd kritisoikin myöhemmin käytännön toteutusten taipumusta poiketa mallin perustana olevista matemaattisista perusteista.

Hierarkkisissa ja verkkomalleissa tietueiden sisäinen rakenne sai olla hyvinkin monimutkainen. Esimerkiksi työntekijän palkkahistoria voitiin esittää “toistuvana ryhmänä” työntekijätietueessa. Relaatiomallissa normalisointiprosessi johti siihen, että tällaiset sisäiset rakenteet korvattiin useissa taulukoissa pidetyillä tiedoilla, jotka yhdistettiin vain loogisten avainten avulla.

Sen lisäksi, että Codd tunnisti tietueet käyttämällä loogisia tunnisteita levyosoitteiden sijaan, hän muutti tapaa, jolla sovellukset kokosivat tietoja useista tietueista: Sen sijaan, että sovelluksia vaadittaisiin keräämään tietoja yksi tietue kerrallaan navigoimalla linkkejä, käytettiin deklaratiivista kyselykieltä. Kyselykielellä ilmaistiin, että mitä tietoja vaaditaan sen sijaan, että niitä pitäisi navigoida. Toisin sanoen; tehokkaan pääsypolun löytäminen tietoihin tuli tietokannan hallintajärjestelmän vastuulle sovellusohjelmoijan sijaan. Tätä prosessia, jossa kyselyt ilmaistiin matemaattisella logiikalla, alettiin kutsua kyselyoptimoinniksi.

Coddin paperin noteerasi kaksi ihmistä Berkeleystä, Eugene Wong ja Michael Stonebraker. He aloittivat INGRES-nimisen hankkeen käyttämällä rahoitusta, joka oli jo myönnetty maantieteelliseen opiskelijapohjaiseen tietokantaprojektiin sekä koodin tuottamiseen. Vuodesta 1973 alkaen INGRES toimitti ensimmäiset testituotteensa, jotka olivat yleisesti valmiita laajaan käyttöön vuonna 1979. INGRES oli samanlainen kuin System R useilla tavoilla, mukaan lukien “kielen” käyttö tiedonhakuun, joka tunnetaan nimellä QUEL. Ajan myötä INGRES siirtyi yleistyvään SQL-standardiin.

Integroidut tietokannat

1970-80 -luvuilla pyrittiin suunnittelemaan tietokantoja myös tekemällä niille spesifiä rautaa ja sovelluskerrosta. Tavoitteena oli maksimoida performanssi matalilla kustannuksilla. Tällaisia kokonaisratkaisuja olivat mm. IBM:n System/38 sekä Teradatan ja Britton Leen “database machine”. Myös ICL kehitti ns. CAFS-kiihdyttimen, joka oli eräänlainen kovalevyn kontrolleri, jossa oli ohjelmoitava hakuominaisuus. Ongelmaksi näissä spesifeissä ratkaisuissa muodostui kuitenkin hidas jatkokehitettävyys verrattuna geneerisiin rautaratkaisuihin. Vaikka nykyisin suurin osa tietokannoista elääkin omaa elämäänsä geneerisen raudan rinnalla, silti esim. Netezza ja Oracle (Exadata) kehittävät edelleen tämäntyyppisiä tietokantaratkaisuja.

1970-luvun loppu ja SQL-tietokannat

IBM rupesi kehittämään System R:ää 1970-luvun alkupuolella ja se näki päivän valon 1974 ja -75 -taitteessa. Edistyksellistä System R:ssä oli, että data oli pilkottu useampaan tietueeseen siten, ettei sitä enää tarvinnut hakea yhdestä isosta “möhkäleestä”. Vuosina 1978 ja -79 testattiin tuotteen monikäyttäjäympäristöjä sekä standardoitiin SQL-kyselykieli. Toisin sanoen; Coddin ideat alkoivat saamaan tuulta alleen. Tämä johti IBM:n jatkokehittämään System R:stä tuotentokelpoisen SQL/DS:n, ja myöhemmin, laajemmin tunnetun Database 2:n (IBM DB2).

Larry Ellison astui kuvioihin Oracle-tietokannallaan vuonna 1978, joka perustui IBM:n System R:ään. Oracle V2 valmistui vuonna 1979.

Tällä välin Stonebraker otti oppia INGRES:istä ja kehitti uuden tietokannan, postgresin, joka tunnetaan nykyisin paremmin nimellä PostgreSQL. Postgresin päälle pystytään rakentamaan järeitä, korkean käytettävyyden globaaleja ratkaisuja ja se olikin vuonna 2023 eniten käytettyjä tietokantoja maailmassa yhdessä MySQL:n ja Microsoftin SQL Serverin kanssa.

1970-luvun puolivälissä kehitettiin myös MimerSQL-niminen tietokanta Uppsalan yliopistossa, mutta se konsolidoitiin yksityisyritykseen vuonna 1984. Toinen tietomalli, ns. ER-malli (entity-relationship model), kehitettiin vuonna 1976 ja se sai nopeasti suosiota. Sittemmin ER-malli istutettiin relaatiomalliin sopivaksi.

1980-luku ja desktop computing

Kun laskentateho kehittyi, 1980-luvulla kokeiltiin myös lähestymistapaa, jossa tietokannat olivat paikallisia, eli pyörivät clienteilla. Tästä käytettiin termiä “desktop computing”. Clientit käyttivät mm. taulukkolaskentaohjelmia, kuten Lotus 1-2-3:aa sekä  ja tietokantasoftaa, kuten helppokäyttöiseksi suunniteltua dBASE:a, jonka luoja oli C. Wayne Ratliff. dBASE:sta tulikin 1980-luvun loppupuolen ja 1990-luvun alkupuolen välillä todellinen hittituote.

1990-luku ja olio-orientoituneisuus

1990-luvulla nostivat päätään objekti-orientoituneet tietokannat (object-oriented databases, “OO databases”). Tämä johtui pitkälti sovelluskehittäjien keskuudessa suosituksi nousseesta olio-ohjelmoinnista, jonka jatkeeksi tietokannat pyrittiin valjastamaan vaihtelevin tuloksin. Oliotietokantojen ideana on järjestää data olioiksi (object) ja niiden ominaisuuksiksi (attribute), sen sijaan, että normalisoitaisiin relaatiomallin kaltaisesti kaikki taulut. Tämä vaikuttaa tapaan käsitellä olioita sekä niiden välisiä suhteita. Hyvää tässä lähestymistavassa on yksinkertaisuus käyttäjän kannalta sekä se, että dataa voidaan kyselllä sille suunnitellulla spesifillä oliokielellä, joka on yksinkertaisempaa kuin perinteinen SQL. Huonoja puolia OO:ssa ovat kompromissit tietokannan suorituskyvyn suhteen. Olenkin vuosien varrella törmännyt useasti järeisiin OO-sovelluksiin, joiden tietokantapään suorituskyvyssä on ollut merkittäviä ongelmia.

2000-luku, NoSQL sekä NewSQL

2000-luku toi mukanaan XML-tietokannat, jotka ovat rakenteeltaan strukturoituja dokumenttitietokantoja. Näitä tietokantoja voidaan kysellä perustuen XML-dokumenttiattibuutteihin. Tällaiset tietokannat palvelevat parhaiten rakenteisia dokumenttikokoelmia, joiden esitysmuoto voi vaihdella säännönmukaseista fleksiibelimpään. Hyvänä esimerkkinä tästä ovat esimerkiksi patentit ja verotiedot.

NoSQL-tietokannat ovat tyypillisesti hyvin nopeita, eivät edellytä tiettyä tietokantaskeemaa ja niissä pyritään välttämään resurssiintensiivisiä JOIN-operaatioita denormalisoimalla dataa. Tällaiset tietokannat skaalautuvat horisontaalisesti.

Viime vuosina on ollut kysyntää massiivisesti hajautetuille tietokannoille, joilla on korkea partitiotoleranssi, mutta ns. CAP-teoreeman mukaan hajautetun järjestelmän on mahdotonta tarjota samanaikaisesti eheyden (consistency), saatavuuden (availability) ja osion (partition) toleranssia. Hajautettu järjestelmä voi täyttää minkä tahansa kaksi näistä takeista samanaikaisesti, mutta ei kaikkia kolmea. Tästä syystä monet NoSQL-tietokannat käyttävät ns. lopullista eheyttä (eventual consistency) tarjotakseen sekä saatavuus- että osiotoleranssitakuut heikentyneellä tietojen eheydellä.

NewSQL on nykyaikaisten relaatiotietokantojen arkkityyppi, jonka tavoitteena on tarjota sama skaalautuva suorituskyky kuin NoSQL-järjestelmät tarjoavat OLTP-työkuormiin, kuitenkin edelleen käyttäen SQL:ää säilyttäen näin olleen perinteisen tietokantajärjestelmän ACID-takuut (atomicity, consistency, isolation, durability).

Seuraavassa blogipostauksessani kuvailen tietokantojen nykytilaa.

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Puhuttaessa modernista tietovarastoinnista ja sen arkkitehtuureista ei voi olla törmäämättä erilaisten tietojärvien ja tietovarastojen käsitteisiin. Tässä blogikirjoituksessa käsitellään tietoalustojen evoluutiota perinteisestä tietoalustasta ja tietovarastoinnista kohti Lakehouse-tietoarkkitehtuuria, erityisesti tarkastellen Lakehousea ja sen mahdollistavaa Delta Lakea -teknologiaa.   

Kuvataan Lakehouse- ja Mitaliarkkitehtuuri Microsoftin Fabric SaaS-palvelun ratkaisuna. Microsoft Fabric on kattava analytiikan ja tietovarastoinnin ratkaisu, joka sisältää tärkeimmät modernit työkalut tietojenkäsittelyyn, tallentamiseen ja analysointiin reaaliajassa.

Miksi Lakehouse ja Delta Lake

Perinteiset tietoalustat ja tietovarastot ovat keskeisessä roolissa organisaatioiden datanhallinnassa. Kuitenkin teknologisen kehityksen myötä tarve entistä joustavammille ja monipuolisemmille ratkaisuille on kasvanut. Tässä evoluutiossa Lakehouse edustaa innovatiivista askelta, joka yhdistää perinteisen tietovaraston (Data Warehouse) luotettavuuden ja rakenteisen datan käsittelyn tietojärven (Data Lake) joustavuuteen ja monimuotoisuuteen erilaisten tietojen hallinnassa ja käsittelyssä.

Delta Lake puolestaan on avainasemassa tämän kehityksen toteuttamisessa, tarjoten avoimen lähdekoodin tallennuskerroksen, joka tuo ACID-transaktiot (Atomicity, Consistency, Isolation, and Durability), metadatan hallinnan ja luotettavuuden tietojärven ympäristöön. Delta Lake -teknologia antaa tiedon varastoinnille joustavuutta  ja tehokkuutta. Lakehouse-tietoarkkitehtuurin, erityisesti Delta Laken, merkitys korostuu jatkossakin datavetoisten yritysten keskuudessa.

Tietovarastot ja tietojärvet ovat vakiintuneita tietoalustoja, joita käytetään yhdessä tai erikseen riippuen datan volyymista ja käyttötarkoituksista. Molemmilla on vahvuutensa ja haasteensa, ja seuraavassa käsitellään näitä näkökohtia sekä pohditaan, miksi Lakehouse-tietoarkkitehtuuri on luonnollinen kehitysaskel tietovarastoratkaisuna.

Perinteisen ja modernin tietoalustan haasteita jotka Lakehouse pyrkii ratkomaan

Perinteiset tietovarastot mahdollistavat historiallisten datajoukkojen järjestämisen analytiikan ja Business Intelligencen (BI) tarpeisiin. Datamäärien kasvaessa perinteiset tietovarastot voivat muuttua kustannustehottomiksi laskentaresurssien ja tallennustilan yhdistetyn käytön vuoksi. Lisäksi ne eivät sovellu optimaalisesti reaaliaikaisen suoratoistodatan käsittelyyn, ja eräajoprosessit voivat olla haasteellisia sopeutumaan nopeasti muuttuviin datavirtoihin. Rakenteellisen datan (Structured) hallinta on perinteisen tietovaraston vahvuus, mutta ne kohtaavat vaikeuksia puolirakenteellisen (Semi-structured) ja rakenteettoman (Unstructured) datan käsittelyssä.

Perinteisen tietovaraston haasteiden monimutkaisuuden ratkaisemiseksi rinnalle on otettu käyttöön tietojärviä. Tietojärvet tarjoavat alhaisen tallennuskustannuksen ja kyvyn käsitellä dataa eri formaateissa. Niiden monipuolisuus tekee niistä sovellettavia eri käyttötarkoituksiin, kuten edistyneeseen analytiikkaan ja koneoppimiseen.

Yksinkertaistettuna modernissa pilvipohjaisessa tietoalustaratkaisussa rakenteellinen data luetaan tietovarastoon ja sen rinnalla toimii tietojärvi puolirakenteellisen ja rakenteettoman datan säilömiseen. Tietojärveä voidaan käyttää myös datan syöttökerroksena tietovarastolle.

Tietojärvillä on kuitenkin myös rajoituksensa. Tiedostomuodossa tallentaminen ilman määriteltyä rakennetta tekee kriittisen tietojenhallinnan vaikeaksi, mikä vaikeuttaa ETL-transaktioiden suorittamista ja johtaa haasteisiin datan eheyden ja yhdenmukaisuuden hallinnassa. Delta Lake ja Lakehouse-tietoarkkitehtuuri pyrkivät ratkomaan yllä mainittuja haasteita.

Alla oleva kuva kuvaa evoluutiota perinteisestä tietoalustasta moderniin pilvipohjaiseen tietoalustaan ja siitä aina Lakehouse-tietoarkkitehtuuriin. Katsotaan seuraavassa kappaleessa tarkemmin Delta Lakea ja Lakehouse-tietoarkkitehtuuria.

Delta Lake Lakehouse-tietoarkkitehtuurin mahdollistaja

Delta Lake mahdollistaa Lakehouse-tietoarkkitehtuurin käyttöönoton tarjoamalla vahvan perustan tiedon tallennuskerroksena. Lakehouse-tietoarkkitehtuuri taas pyrkii jatkuvaan ja yksinkertaistettuun tapaan järjestää tietoa hyödyntäen Delta Laken tuomia etuja.

Yhdellä tietoalustalla toimiminen poistaa siiloja ja yksinkertaistaa prosesseja. Data tallennetaan Delta Lakeen delta-formaatissa parquet-tiedostoiksi. Tämä pienentää datan varastoinnin kustannuksia ja parantaa kyselysuorituskykyä. Tehokkaan datan varastoinnin ja nopean kyselysuorituskykynsä ansiosta Delta Lake tukee erinomaisesti suoratoisto ja eräajoprosessointia, mikä tekee siitä monipuolisen ratkaisun erilaisten tietokäsittelytarpeiden täyttämiseksi.

Lisäksi Delta Lake käyttää JSON-metadatatiedostoja, jotka sisältävät tiedot tauluista, tiedostojen sijainneista ja määritellyistä rakenteista. JSON-metadatat mahdollistavat tietojen hallinnan ja käsittelyn, mikä on keskeistä tietoalustan tehokkaassa toiminnassa. Metadata mahdollistaa aikamatkustamisen (time travel). Aikamatkustuksen avulla voi palauttaa tietyn hetken tilanteen aiempiin versioihin, eli tiettyyn ajanhetkeen, ja tämä kyky perustuu tarkkaan metadatatiedostojen hallintaan. Aikamatkustamisessa on rajoituksensa: se rajoittuu niihin versioihin, jotka on tallennettu Delta Lakeen, ja määrityksiin historiatietojen säilytysajoista.

Lakehouse-tietoarkkitehtuuri Microsoft Fabric

Mitaliarkkitehtuuri – Kuinka Organisoida Data Lakehousessa?

Käydään läpi Lakehouse-tietoarkkitehtuuria tarkemmin Microsoftin Fabric SaaS-palvelun ratkaisuna. Esimerkin avulla saamme käytännönläheisemmän kuvan.  Alla olevaan kuvaan olen piirtänyt yksinkertaistetun Lakehouse-ratkaisun, jossa tietoalustaan luetaan useasta eri tietolähteestä tai lähdejärjestelmästä tietoa, joko reaaliajassa tai eräajoin eli tiettyinä ennakkoon määriteltyinä hetkinä.

Data luetaan tietoalustaan integraatiotyökalulla, tässä tapauksessa Azure Data Factory, joko suoraan tai hyödyntäen väliaikaista laskeutumisalustaa (valinnainen, ei kuvattu ratkaisussa), ja tallennetaan Lakehouseen. Fabric mahdollistaa myös olemassa olevien tietojärvien nopean hyödyntämisen shortcut-toiminnolla. Fabricissa tiedot tallennetaan parquet-tiedostomuodossa One Lake -tietoalustaan riippumatta siitä, onko kyseessä Lakehouse vai Warehouse -tietovarasto. One Lake perustuu Delta Lake -teknologiaan. Olipa kyseessä T-SQL-datavarastointi, Spark tai KQL-suoratoisto, jokainen Fabricin työkuorma toimii delta-taulukoiden kanssa.

Lakehouse-tietoarkkitehtuuri rakennetaan usein Databricksin kehittämää mitaliarkkitehtuuria hyödyntäen. Mitaliarkkitehtuurissa ja kuvan esimerkissä on kolme kerrosta tiedon säilyttämiseen, muokkaamiseen, prosessointiin ja jakamiseen. Esimerkkiarkkitehtuurissa hyödynnetään muistikirjoja (Notebook) tiedon muokkaamiseen, rikastamiseen ja siirtämiseen kerrokselta toiselle. Muistikirjat voidaan ajastaa ja orkestroida Azure Data Factoryn putkilla (Pipeline). Muistikirjojen ajastamiseen ja ajamiseen voidaan hyödyntää myös muita tekniikoita.

Mitaliarkkitehtuurin tavoite on organisoida dataa loogisesti Lakehousen sisällä eri kerrosten välillä. Kerrokset on nimetty mitalien mukaan pronssi, hopea ja kulta. Mitalin kirkkaampi väri viittaa datan laatuun eli tavoitteena on prosessoida, yhdistää ja parantaa datanlaatua kerroksittain aina loppukäyttäjälle asti. On hyvä tunnistaa, että mitaliarkkitehtuurin kolmekerroksinen tietovarastointimetodi on hyvin samankaltainen perinteisempien source, staging ja curated ratkaisujen kanssa.

Mitaliarkkitehtuurin etuja ovat:  

• Yksinkertainen ja looginen rakenne.
• Mahdollistaa inkrementaalisen kehittämisen.
• Tukee erilaisia työmääriä ja soveltuu erilaisiin käyttötarkoituksiin.
• Hopeakerros voidaan toteuttaa erilaisilla tietomalleilla tarpeen mukaan, kuten esimerkiksi Data Vault tai malleilla, jotka ovat lähellä kolmannen normaalimuodon rakennetta.
• Arkkitehtuuri tukee joustavuutta analytiikassa ja datankäsittelyssä. Data tieteilijät ja analyytikot voivat työskennellä viimeisimpien saatavilla olevien tietojen kanssa ja edistää nopeampaa päätöksentekoa ja analytiikkaa.
• Mitaliarkkitehtuurin modulaarinen luonne edistää yhteensopivuutta eri komponenttien ja järjestelmien välillä. Tämä on erityisen arvokasta heterogeenisissä dataympäristöissä, joissa käytetään erilaisia työkaluja ja alustoja.
• Taulukkojen uudelleen rakentaminen raakatiedoista milloin tahansa.
• ACID-tapahtumat ja Time Travel -toiminnallisuudet

Mitaliarkkitehtuurin kerrosten tehtävät:

• Pronssikerros (Bronze)
  • Yleisesti pronssikerrokseen tiedot tallennetaan raakamuodossa eli kopiona lähdejärjestelmästä ilman muuntoja:
    • Pronssikerroksessa dataa ei muunneta alkuperäisestä muodostaan, jotta alkuperäinen tieto säilyy ja on saatavilla sellaisenaan tulevia tarpeita varten.
  • Data on pronssikerroksessa vain lukumuodossa:
    • Dataa voidaan lukea ja käyttää, mutta siihen ei tehdä muutoksia.
  • Tietoja voidaan säilöä eri tietomuodoissa, esimerkiksi erilaisina tiedostoina (Parquet, JSON tai CSV) tai delta-tauluina:
    • Pronssikerroksessa tarjotaan joustavuutta tallennettavan datan muodossa. Se voi sisältää eri tietomuotoja, mikä mahdollistaa erilaisten tietojen tehokkaan käsittelyn.
  • Pronssikerroksen data voidaan syöttää kokonaisuudessaan aina uudelleen lähdejärjestelmästä (Full load) tai lisätä ainoastaan muuttuneet tiedot olemassa olevan datan jatkoksi inkrementaalisesti (Delta load).
  • Sallii lähteeksi transaktiopohjaisen tai reaaliaikaisen prosessoinnin:
    • Pronssikerros tarjoaa joustavuutta datan keräämisessä, ja se voi käsitellä tietoa sekä transaktiopohjaisesti että reaaliaikaisesti lähteen luonteesta riippuen.

• Hopeakerros (Silver)
  • Pääasiallinen tiedonsäilytyskerros:
    • Hopeakerros toimii pääsäilytysalueena, jossa dataa pidetään ensisijaisesti lähdejärjestelmästä tuotuna. Tämä varmistaa, että alkuperäinen data säilyy turvallisesti ja eheänä.
  • Järjestetään, siivotaan ja yhdistetään pronssikerroksen data yhtenäiseen tietomalliin:
    • Hopeakerroksessa toteutetaan toimet, kuten tietojen järjestely, puhdistaminen ja yhdistäminen yhtenäiseen rakenteeseen. Näin varmistetaan, että data on valmiina siirrettäväksi seuraavaan kehitysvaiheeseen.
  • Luodaan historiointikäytännöt ja historioidaan dataa:
    • Hopeakerroksessa implementoidaan historiointikäytännöt, jotka mahdollistavat datan historian seuraamisen ajan myötä. Tämä tarjoaa kattavan näkymän datan kehityksestä ja muutoksista.
  • Yhtenäistetään tietomuoto:
    • Microsoft Fabric tallentaa datan yhtenäisessä muodossa parquet-tiedostoformaatissa One Lake -tietoalustaan. Tämä varmistaa, että dataa voidaan käsitellä yhdenmukaisesti ja tehokkaasti.

• Kultakerros (Gold)
  • Data on järjestetty valmiiksi ja käytettävissä raportoinnille ja loppukäyttäjille:
    • Kultakerros tarjoaa valmiiksi järjestetyn datan, joka on helposti käytettävissä raportoinnissa ja loppukäyttäjien tarpeisiin. Tämä edistää nopeaa ja tehokasta päätöksentekoa.
  • Data mallinnetaan yleensä tähtimalliin faktoiksi ja dimensioiksi:
    • Kultakerroksessa suoritetaan tietomallinnus, yleensä tähtimalliin, jotta data voidaan tehokkaasti kuvata faktoiksi ja dimensioiksi. Tämä parantaa tiedon käsiteltävyyttä ja ymmärrettävyyttä.
  • Voidaan organisoida projektikohtaisiin tai liiketoimintakohtaisiin kokonaisuuksiin:
    • Kultakerroksessa dataa voidaan organisoitua projektikohtaisesti tai liiketoimintakohtaisesti tarpeiden mukaan. Tämä mahdollistaa joustavan käytön eri liiketoiminta-alueilla.
  • Sovelletaan liiketoimintasääntöjä ja tehdään monimutkaisia transformaatioita sekä laskentaa:
    • Kultakerroksessa toteutetaan liiketoimintasääntöjä ja suoritetaan monimutkaisia transformaatioita ja laskentaa. Tämä varmistaa, että data vastaa tarkasti liiketoiminnan tarpeita.
  • Kokonaisuus edustaa datatuotetta:
    • Kultakerroksen lopputulos muodostaa laadukkaan datatuotteen, joka vastaa liiketoiminnan tarpeisiin. Datatuote tarjoaa vahvan perustan tehokkaalle päätöksenteolle ja liiketoiminnan kehittämiselle.

Mitaliarkkitehtuurin kultakerros on hyödynnettävissä raportoinnille. Data on jalostettu valmiiksi ja hyödynnettävissä liiketoiminta-alueittain kokonaisuuksina tai osina. Power BI mahdollistaa datan visualisoinnin ja mahdollisten uusien mittareiden luomisen kultakerroksen datasta.

Kultakerrosta voidaan hyödyntää myös Data Science ja AI pohjaisten ratkaisujen rakentamiseen. Data Science ja AI ratkaisut käyttävät usein mallien kouluttamiseen dataa mahdollisimman läheltä lähdettä joten Lakehouse-tietoarkkitehtuuri mahdollistaa datan hyödyntämisen Data Science ja AI ratkaisuihin myös pronssi- ja hopeakerroksesta, joskin pronssikerrosta hyödyntäessä datan laatu saattaa olla riittämätön.

Yhteenveto

Lakehouse-tietoarkkitehtuuri luo tehokkaan skaalautuvan tietoalustan, jossa tietoa järjestetään ja muokataan asteittain kerrosten välillä. Lakehouse soveltuu erinomaisesti edistyneelle analytiikalle ja vaativille hybridiratkaisuille, joissa tietoa luetaan eri lähteistä, joko reaaliajassa tai eräajoin.

On hyvä huomioida, että modernit ratkaisut, kuten Microsoft Fabricin Delta Lake-pohjaisen yhtenäisen tiedonvarastoinnin (One Lake) menetelmien avulla mahdollistetaan Lakehouse ja Warehouse ratkaisujen sujuva yhteensopivuus. Tämä taas antaa lähes rajattomat mahdollisuudet luoda hybridiratkaisuja, jotka vastaavat monimutkaisiin liiketoiminta- ja teknisiin vaatimuksiin.

Perinteisille ratkaisulle on yhä paikkansa erilaisissa käyttötapauksissa. Aina pilvimigraatioissa ja tietovarastoinnin uudistushankkeissa on tärkeää tehdä kattava analyysi eri vaihtoehdoista uudeksi tietoalustaksi ja datastrategiasta.

Liikkeelle Datastrategiasta Tietoalusta-ratkaisua valittaessa

Tietoalustan arkkitehtuuria valittaessa tulee aina tehdä tarkat strategiset linjaukset:

• Käyttötapausanalyysi
  • Mihin kerättävää dataa käytetään ja hyödynnetään.
  • Data ja liiketoimintastrategian yhtenäisyys.
• Missä muodossa ja mistä lähdedata on saatavissa
  • Rakenteellinen, puolirakenteellinen ja ei-rakenteellinen data.
  • Reaaliaikainen vai eräajo.
  • Sisäinen vai ulkoinen data.
  • Minkälaisia integraatioita tarvitaan.
• Millä syklillä dataa tietoalustaan tallennetaan.
• Arkkitehtuuri, työkalut ja komponentit
• Tiedonhallinta.
• Tietoturva ja käyttäjähallinta.

Me DB Pro Servicellä teemme kattavia datastrategiakonsultaatioita. Voit lukea tarkemmin datastrategiasta blogeissamme: Kuinka määritellä datastrategian suunta 

DB Pro Services tarjoaa kattavia ratkaisuja ja asiantuntijapalveluita tekoälyn käyttöönottoon liittyviin haasteisiin. Tarjontamme kattaa muun muassa datastrategian, modernien data-alustojen sekä edistyneen analytiikan kokonaisuudet. Ota yhteyttä, niin autamme sinua ja organisaatiotasi hyödyntämään tietoa tehokkaasti ja menestymään kilpailussa!

Robin Aro

Lead Data Engineer

robin.aro@dbproservices.fi

DB Pro Services Oy

Johdanto

Tietoon perustuva päätöksenteko on nykypäivän liiketoiminnassa yhä tärkeämpää. Se auttaa yrityksiä tekemään parempia päätöksiä, optimoimaan toimintaa ja saavuttamaan kilpailuetua. Tiedon jalostamisen peruspilareina toimivat tietovarastointi ja raportointi. Tässä blogikirjoituksessa käsitellään näitä kahta keskeistä osa-aluetta ja selitetään, miksi ne ovat niin tärkeitä liiketoiminnalle.

Mitä on tietovarastointi?

Tietovarastointi tarkoittaa yläkäsitteenä prosessia, jossa yrityksen eri toiminnoista kerätty data tallennetaan keskitettyyn paikkaan yhtenäistetysti. Tämä tietovarasto on suunniteltu niin, että dataa on helppo käsitellä, jakaa ja tutkia erilaisten raporttien ja analyysien muodossa. Tietovarastoinnilla ei tarkoiteta pelkästään datan säilyttämistä, vaan se kattaa myös muita datan elinkaareen kuuluvia asioita. Tietovarasto parantaa operatiivisten järjestelmien suorituskykyä ja saatavuutta, koska raportointia ei tarvitse tehdä enää niiden päältä, vaan raportoinnin työkuormat ohjataan kulkemaan tietovaraston kautta. Myös raporttien teko tietovarastosta on helpompaa kuin vastaavan tiedon onkiminen operatiivisista järjestelmistä. Käydään seuraavaksi läpi laadukkaan tietovarastoinnin ominaisuuksia.

Tietoturva

Datan turvallisuus on erityisen tärkeää nykypäivän digitaalisessa ympäristössä, jossa tietomurrot ja -vuodot ovat yleistyneet. Laadukkaat tietovarastot ovat suunniteltu noudattamaan tietoturvan parhaita käytäntöjä, kuten laadukkaan salauksen ja pääsynhallinnan. Monet tietovarastot hyödyntävät monitasoista autentikointia (MFA) ja roolipohjaista pääsynhallintaa varmistaakseen, että vain oikeutetut henkilöt pääsevät käsiksi arkaluontoiseen tietoon. Lisäksi ne voivat sisältää erilaisia valvontamekanismeja, jotka ilmoittavat epäilyttävästä toiminnasta, jotta mahdollisiin tietoturvariskeihin voidaan puuttua välittömästi.

Tietoturva ei ole vain tekninen vaatimus, vaan myös liiketoiminnallinen tarve. Se on olennainen osa yrityksen mainetta ja luottamusta, ja sen laiminlyönti voi johtaa paitsi taloudellisiin tappioihin, myös vahinkoon yrityksen brändille.

Lähdeintegraatiot

Lähdeintegraatiolla tarkoitetaan tietovarastoinnin prosessia, jossa eri datalähteistä kerätty tieto yhdistetään yhteen keskitettyyn tietovarastoon. Tämä ei ole pelkästään datan siirtämistä paikasta toiseen, vaan se sisältää usein myös datan muuntamista, puhdistamista ja rikastamista, jotta se on sisällöltään eheää ja yhdenmukaista, ymmärrettävässä muodossa raportointia ja analytiikkaa varten, sekä yhteismitallista muiden lähteiden kanssa.

Laadunhallinta

Datan laadunhallinta on olennainen osa tietovarastoinnin prosessia. Huonolaatuinen tai virheellinen data voi johtaa väärään analyysiin ja päätöksentekoon, mikä voi olla kallista yritykselle. Siksi tietovarastointiin sisältyy useita vaiheita datan laadun varmistamiseksi. Esimerkiksi datan puhdistaminen tarkoittaa virheellisen tai puuttuvan tiedon korjaamista, normalisointi puolestaan tarkoittaa datan muuttamista yhteensopivaan ja vertailukelpoiseen muotoon. Validointi taas on prosessi, jossa varmistetaan, että data on luotettavaa ja täyttää ennalta määritellyt laatuvaatimukset.

Nämä toimenpiteet eivät ole yksittäisiä tehtäviä, vaan ne ovat osa jatkuvaa laadunhallintaprosessia, joka alkaa datan keräämisestä ja jatkuu läpi koko sen elinkaaren. Tämä takaa tietovarastosta saadun tiedon olevan aina mahdollisimman tarkkaa, ajantasaista ja luotettavaa, mikä on välttämätöntä tehokkaalle päätöksenteolle.

Saatavuus

Laadukaskaan tieto ei tuota lisäarvoa, mikäli se ei ole kenenkään saatavilla. Datan saatavuus on kriittinen tekijä tietovarastoinnin onnistumisessa ja vaikuttaa suoraan yrityksen kykyyn tehdä informoituja päätöksiä. Laadukas tietovarasto on suunniteltu niin, että se mahdollistaa datan helpon ja nopean jakamisen eri osastojen, tiimien ja jopa ulkoisten sidosryhmien, kuten asiakkaiden tai kumppaneiden, kesken. Tämä poistaa pullonkauloja ja tehostaa päätöksentekoprosessia.

Nykyteknologian ansiosta tietovarastot tarjoavat dataa reaaliaikaisesti ja eri laitteille – mukaan lukien mobiililaitteet. Tämä mahdollistaa joustavuuden ja liikkuvuuden, mikä on erityisen tärkeää etätyöympäristöissä ja globaaleissa organisaatioissa.

Lisäksi hyvin suunniteltu tietovarasto tukee erilaisia käyttöoikeusasetuksia, jolloin voidaan varmistaa, että henkilöt pääsevät käsiksi vain heille relevanttiin tietoon. Tämä ei ainoastaan paranna tietoturvaa, vaan myös tekee datan hyödyntämisestä tehokkaampaa, kun jokainen tiimi tai osasto voi keskittyä juuri siihen dataan, joka on heille olennaista.

Arkistointi ja varmuuskopiointi

Datan pitkäaikainen säilyttäminen ja varmuuskopiointi ovat keskeisiä tietovarastoinnin elementtejä, jotka toimivat yrityksen datan turvaverkkona. Ne eivät ainoastaan suojaa arvokasta dataa, vaan myös mahdollistavat sen palauttamisen odottamattomissa tilanteissa, kuten tietojärjestelmäongelmissa. Tämä sisältää säännölliset varmuuskopiot ja datan elinkaaren hallinnan kattaen myös arkistoinnin ja versionhallinnan. Nämä toimet yhdessä varmistavat datan eheyden ja saatavuuden – myös kriisitilanteissa.

Mitä on raportointi?

Raportointi on keskeinen osa yrityksen tiedonhallintaa ja päätöksentekoprosessia, joka linkittyy vahvasti tietovarastointiin. Se tarkoittaa prosessia, jossa kerätty ja tietovarastossa säilytetty data muunnetaan merkitykselliseksi tiedoksi erilaisten raporttien ja analyysien avulla. Raportit voivat olla monimuotoisia, kattaen kaiken yksinkertaisista myyntitilastoista aina johdon mittaristoihin, monimutkaisiin ennakoiviin analyyseihin ja algoritmeihin, jotka ennustavat yrityksen tulevaisuuden suuntaviivoja. Käydään seuraavaksi läpi raportoinnin keskeisimpiä elementtejä.

Laadukas raportointijärjestelmä

Laadukas raportointijärjestelmä on joustava, skaalautuva ja helppokäyttöinen. Se mahdollistaa datan nopean ja tehokkaan analysoinnin, ja sen tulokset ovat helposti jaettavissa eri sidosryhmille. Nykyaikaiset raportointityökalut, kuten Microsoft Power BI, tarjoavat mahdollisuuden reaaliaikaiseen seurantaan, automatisoituun raportointiin ja monipuolisiin visualisointeihin, jotka tiivistävät tiedosta helpommin ymmärrettävää ja hyödynnettävää.

Visualisointi

Raportoinnin yksi keskeisistä elementeistä on datan visualisointi, joka tarkoittaa tiedon esittämistä graafisessa muodossa, kuten kuvaajina ja taulukoina. Hyvä visualisointi auttaa tekemään monimutkaisesta datasta helpommin ymmärrettävää ja saavutettavaa, ja se voi tuoda esiin näkemyksiä, jotka jäisivät helposti huomaamatta pelkästään raakadatan tarkastelussa. Visualisointien esittämisen tulee olla selkeää ja ytimekästä, keskittyen olennaisiin tietoihin ja mahdollistaen nopeat ja informoidut päätökset.

Laadukas visualisointi helpottaa tiedon esittämistä. Huomataan helposti tuotteen 2 poikkeava käytös päivinä 15–20.

Käyttäjäystävällisyys

Raporttien tulee olla helposti saatavilla ja ymmärrettäviä kaikille niille, joille ne on tarkoitettu. Tämä tarkoittaa, että raporttien tulisi olla selkeitä, visuaalisesti houkuttelevia ja helppolukuisia. Nykyaikaiset raportointityökalut mahdollistavat usein interaktiivisen raportoinnin, jossa käyttäjät voivat tutkia dataa itsenäisesti ja porautua yksityiskohtiin tarpeen mukaan.

Reaaliaikaisuus

Yhä useammin raportoinnin odotetaan olevan reaaliaikaista tai lähes reaaliaikaista, jotta päätöksentekijät voivat reagoida nopeasti muuttuviin tilanteisiin ja hyödyntää mahdollisuuksia ajoissa. Reaaliaikainen raportointi voi auttaa organisaatioita pysymään kilpailukykyisinä dynaamisessa liiketoimintaympäristössä, ja se voi tukea proaktiivista, dataohjattua, päätöksentekoa. Tämä edellyttää vahvaa teknologista infrastruktuuria ja kykyä käsitellä ja analysoida suuria datamääriä tehokkaasti ja luotettavasti.

Kuinka tietovarastointi ja raportointi liittyvät toisiinsa?

Tietovarastointi ja raportointi ovat syvästi kytkeytyneitä prosesseja, jotka yhdessä mahdollistavat informaation muuntamisen merkityksellisiksi oivalluksiksi ja päätöksenteon tueksi. Tietovarastointi kattaa datan keräämisen, integroinnin, pitkäaikaisen säilyttämisen sekä hallinnan, ja luo näin vankan perustan, jolle raportointi rakentuu. Raportointi puolestaan keskittyy datan analysointiin, visualisointiin ja jakamiseen. Näin organisaation jäsenille luodaan mahdollisuus ymmärtää ja hyödyntää dataa tehokkaasti.

Kun nämä kaksi elementtiä – tietovarastointi ja raportointi – toimivat saumattomasti yhdessä, mahdollistavat ne yrityksille kyvyn navigoida datavetoisessa maailmassa, tehdä informoituja päätöksiä ja luoda strategioita, jotka ovat linjassa yrityksen tavoitteiden ja markkinatilanteen kanssa. Tämä yhdistelmä on erityisen voimakas, kun se integroidaan osaksi yrityksen kulttuuria ja päätöksentekoprosesseja, mahdollistaen aidosti datavetoisen organisaation.

Esimerkkitapaus: Sirpaloituneesta datasta laadukkaaksi tietovarastoksi

Sirpaloituneen datan ongelma

Yrityksissä dataa syntyy monista eri lähteistä, kuten myynnistä, markkinoinnista, tuotannosta ja asiakaspalvelusta. Tämä data on usein tallennettu erillisiin järjestelmiin, jotka eivät välttämättä ole yhteensopivia keskenään. Tämä johtaa sirpaloituneeseen dataympäristöön, joka tekee tiedon hallinnasta ja analysoinnista haastavaa.

Ratkaisuna keskitetty tietovarastointi

Ensimmäinen askel sirpaloituneen datan kokoamisessa on dataintegraatio. Tässä vaiheessa eri lähteistä tuleva data tuodaan yhteen, keskitettyyn, sijaintiin. Nykyään raakadata tuodaan yleensä pilvessä toimivaan tietoaltaaseen (data lake). Raakadata jatkaa matkaansa tietoaltaasta tietomalliltaan strukturoituun tietovarastoon, josta se on saatavilla eri raportointi- ja analytiikkatyökalujen käyttöön. Etenkin taulukkomuotoisen datan tietovarastona käytetään usein SQL-tietokantoja.

Dataintegraatio ELT-prosessilla

Yksi yleisimmin käytetty menetelmä datan integroimiseksi on ELT-prosessi, joka on lyhenne sanoista Extract, Load ja Transform (kerää, lataa, muunna). ELT:llä tarkoitetaan kolmiosaista prosessia, jossa data ensin kerätään lähdejärjestelmistä, ladataan tietoaltaaseen ja lopulta muokataan yhdenmuotoiseksi tietovarastoon.

Lopputuloksena keskitetty tietovarasto

Lopputuloksena saatu tietovarasto suunnitellaan niin, että sieltä on helppo hakea, käsitellä ja analysoida dataa. Se toimii yrityksen keskeisenä tietopankkina, josta eri osastot ja johdon edustajat voivat helposti saada tarvitsemansa tiedot.

Tämä keskitetty lähestymistapa ratkaisee monia sirpaloituneen datan aiheuttamia ongelmia, kuten tiedon eheyden ja saatavuuden haasteet. Se mahdollistaa myös tehokkaan, konsolidoidun raportoinnin ja analyysin, jotka ovat keskeisiä tiedolla johtamisessa.

Kokonaisuudessaan keskitetty tietovarastointi ei ole vain tekninen toimenpide, vaan strateginen investointi yrityksen tulevaisuuteen. Se luo perustan, jolle voidaan rakentaa tehokas tiedolla johtamisen ekosysteemi.

Lopuksi

Datavetoinen päätöksenteko on enemmän kuin vain numeroiden tuijottamista; se on kokonaisvaltainen lähestymistapa, joka yhdistää tietovarastoinnin ja raportoinnin voiman. Joten, jos haluat tehdä parempia päätöksiä, optimoida toimintaasi ja saada kilpailuetua, on aika panostaa peruspilareihin: tietovarastointiin ja raportointiin.

Tiedolla johtaminen vaatii oikeanlaista osaamista ja työkaluja. Me DB Pro Servicellä ymmärrämme nämä haasteet ja olemme erikoistuneet auttamaan yrityksiä rakentamaan tehokkaita tietovarastointi- ja raportointiratkaisuja. Tarjoamme kattavia palveluita, jotka kattavat koko tiedolla johtamisen elinkaaren – lähtien datan keräämisestä ja integroinnista aina edistyneeseen analytiikkaan ja raportointiin.

Olemme työskennelleet monenlaisten yritysten ja toimialojen parissa, ja meillä on laaja kokemus erilaisten dataympäristöjen hallinnasta. Käytämme alan parhaita käytäntöjä ja uusimpia teknologioita varmistaaksemme, että saat parhaan mahdollisen hyödyn datastasi.

Ota yhteyttä meihin, jos olet kiinnostunut viemään datasi seuraavalle tasolle. Asiantuntijamme ovat valmiita auttamaan sinua löytämään juuri sinun yrityksellesi sopivan ratkaisun. Katsotaan yhdessä, kuinka voimme auttaa sinua saavuttamaan tavoitteesi datan avulla.

DB Pro Services asiantuntijatiimi

Jatkan taasen blogisarjaani tietomallinnuksesta. Edellisessä postauksessani kuvasin lumihiutalemallia. (Tietomallinnus – Osa 4: Lumihiutalemalli (Snowflake schema)). Tänään läpikäyn Ralph Kimballin koulukunnan ns. Enterprise Data Warehouse BUS -mallinnusmenetelmää. Toiselta nimeltään tämä tunnetaan myös Conformed Data Warehouse BUS:ina. Alla linkit tietomallinnuksen blogisarjan muihin blogeihin.

• Tietomallinnus – Osa 1: Intro
• Tietomallinnus – Osa 2: Kolmas normaalimuoto (OLTP)
• Tietomallinnus – Osa 3: Tähtimalli (Star schema)
• Tietomallinnus – Osa 4: Lumihiutalemalli (Snowflake schema)
• Tietomallinnus – Osa 5: Enterprise Data Warehouse BUS
• Tietomallinnus – Osa 6: Data Vault

Enterprise Data Warehouse BUS -mallin hyödyt

Enterprise Data Warehouse BUS -menetelmä (EDW BUS) on menetelmä, joka perustuu Ralph Kimballin kehittämään dimensiomallinnuksen arkkitehtuuriin. Tässä menetelmässä tietovarasto on suunniteltu yhteisten dimensiomallien ympärille, jotka voivat olla jaettuja useiden tietomallien kesken. Tämä lähestymistapa tarjoaa useita etuja tietokantojen mallinnuksessa ja tietovarastoinnissa:

1. Yhdenmukaisuus ja standardointi

• Yhteiset dimensiot: EDW BUS -menetelmässä käytetään yhteisiä dimensioita (kuten aika, tuote, asiakas), joita voidaan jakaa eri osastojen ja liiketoimintayksiköiden välillä. Tämä yhdenmukaistaa tiedot ja mahdollistaa yhteismitalliset analyysit ja raportit.
• Standardoidut prosessit: Yhteisten dimensioiden ja faktataulujen käyttö varmistaa, että organisaatio noudattaa standardoituja prosesseja tietojen tallentamisessa ja hakemisessa.

2. Joustavuus ja laajennettavuus

• Modulaarinen rakenne: EDW BUS -menetelmä on modulaarinen, mikä tarkoittaa, että uusia liiketoiminta-alueita voidaan lisätä olemassa olevaan tietovarastoon ilman suuria muutoksia sen rakenteeseen. Uudet faktataulut ja dimensioita voidaan helposti lisätä BUS-arkkitehtuuriin.
• Helppo ylläpito: Tietovaraston ylläpito ja päivittäminen on helpompaa, koska yhteisiä dimensioita voidaan käyttää uudelleen eri liiketoimintayksiköiden välillä.

3. Parempi tiedon analyysi ja päätöksenteko

• Kokonaisvaltainen näkymä: Yhteiset dimensiot mahdollistavat kattavamman ja yhtenäisemmän näkymän liiketoiminnan suorituskykyyn, mikä tukee paremmin päätöksentekoa.
• Yhteismitallisuus: Koska kaikki liiketoiminta-alueet käyttävät samoja dimensioita, tiedot ovat yhteismitallisia, mikä mahdollistaa paremmat vertailut ja analyysit eri liiketoiminta-alueiden välillä.

4. Tehokas integrointi ja tietojen hallinta

• Helppo integrointi: EDW BUS -menetelmä helpottaa tietojen integrointia eri lähdejärjestelmistä, koska yhteiset dimensio- ja faktataulut toimivat standardoituna viitekehyksenä tiedon tallentamiselle ja hakemiselle.
• Tietojen hallinnan parantaminen: Yhteisten dimensioiden käyttö parantaa tietojen hallintaa ja yhdenmukaisuutta, koska samat dimensiot ja standardit koskevat koko organisaatiota.

5. Kustannustehokkuus

• Kustannussäästöt: Koska yhteisiä dimensioita ja faktatauluja käytetään laajasti, tietovaraston kehittämis- ja ylläpitokustannukset pienenevät. Uusien liiketoimintatarpeiden tukeminen on kustannustehokkaampaa, koska uusia osia voidaan lisätä olemassa oleviin rakenteisiin.
• Parempi resurssien hyödyntäminen: Resurssien käyttö on tehokkaampaa, koska yhteisiä rakenteita ja prosesseja voidaan käyttää uudelleen.

6. Parempi skaalautuvuus

• Laajennettavuus: EDW BUS -arkkitehtuuri on suunniteltu skaalautuvaksi, mikä mahdollistaa tietovaraston kasvattamisen liiketoiminnan tarpeiden mukaan ilman suuria rakenteellisia muutoksia.
• Joustava tietojen lisäys: Uusien dimensioiden ja faktataulujen lisääminen on suoraviivaista ja yksinkertaista, mikä helpottaa tietovaraston laajentamista ja mukauttamista liiketoiminnan muuttuviin tarpeisiin.

Yhteenvetona Enterprise Data Warehouse BUS -menetelmä tarjoaa merkittäviä etuja tietovarastoinnissa, kuten yhdenmukaisuuden, joustavuuden, tehokkaan integraation, kustannustehokkuuden ja paremman päätöksenteon tukemisen. Tämä tekee siitä erinomaisen valinnan organisaatioille, jotka haluavat rakentaa skaalautuvan ja tehokkaan tietovarastoratkaisun.

Mikä on Enterprise Data Warehouse BUS?

Enterprise Data Warehouse BUS on eräs fyysisen tietomallinnuksen menetelmä, tai enemmänkin arkkitehtuurinen tapa ajatella tietomallinnusta, jolla voidaan rakentaa konsernitietovarastoja tähtimallin päälle siten, että se ottaa huomioon bisneksen ns. 360-näkymän. Tämä tarkoittaa käytännössä eri järjestelmien välistä yhteistä master dataa, jotka mallinnetaan dimensioiksi.

Enterprise Data Warehouse BUS:in ideana on:

–  Selkeyttää riippuvuussuhdetta master datan kehityksen ja EDW-kehityksen välillä

–  Toimia nimensä mukaisesti tehokkaana EDW-mallinnusmenetelmänä

–  Maksimoida 360-näkymä bisnekseen

–  Minimoida muutostarpeet fyysisessä tietomallissa ajan saatossa

Master data ja Enterprise Data Warehouse BUS

Yleisesti ajatellaan, että monilähteisen konsernitietovaraston voi rakentaa vasta, kun master-datan hallinta on implementoitu. Tämä onkin lähtökohtaisesti suotavaa, koska tällöin saadaan ns. ”yksi totuus” eri järjestelmien välisestä datasta ja datan laatu sekä rikastamisprosessit ovat paremmin hallussa. Koska kuitenkin usein tähän ruhtinaallisuuteen ei ole aikaa, päätetään silti tehdä konsernitietovarasto, vaikka master datan osalta oltaisiinkin vaiheessa, tai joskus jopa alkutekijöissään. Se, mitä minimissään kannattaa kuitenkin tehdä tällaisissa tapauksissa, on se, että määritellään sellainen master data, joka halutaan tuoda konsernitietovarastoon raportoinnin piiriin. Nämä ovat ns. konformoituja dimensioita (”conformed dimensions”). Kussakin tällaisessa dimensiossa määritellään se ja vain se data, joka esiintyy eri järjestelmien välillä samanmuotoisena, kun kaikki osajärjestelmät yhdistetään yksilöivien tietojen (=natural key) kautta keskenään. Tyypillisiä konformoituja dimensioita ovat esimerkiksi kalenteridimensiot, kuten kuukausi, päivä ja tunti sekä tuote-, yritys- ja henkilötietoihin sekä geografiaan ja demografiaan liittyvät dimensiot.

Kuinka Enterprise Data Warehouse BUS -väylä rakennetaan

Kun konformoidut dimensiot on ensiksi määritelty, voidaan tämän päälle sitten menestyksellisesti rakentaa joko syklisellä tai iteroivalla metodilla konsernitietovarasto. Tätä voidaan tehdä joko osajärjestelmä kerrallaan tai sitten sisällyttäen skeemaan ensin kriittiset, sitten tärkeät ja sitten vähemmän tärkeät tiedot, kunhan faktataulujen granulariteetti pysyy samana. Tällöin tähän konformoitujen dimensioiden ”väylään” (BUS) syntyy vähitellen yhä enemmän viitteitä yhä useammista faktatauluista ja säästytään isolta refaktorointityöltä sekä fyysisen tietomallin, että integraatioajojen (ETL / ELT) osalta.

Konformoitujen dimensioiden matriisi osana suunnittelua

Konformoitujen dimensioiden määrittämistä selkeyttää paljon, mikäli laaditaan organisaation keskeiset bisnesprosessit ja dimensiotietoineen. Tämä harjoitus kannattaa tehdä Kimballin mukaan siksi, ettei unohdeta yhtäkään sellaista dimensiota, jotka ovat tietyille bisnesprosesseille yhteisiä. Yksi bisnesprosessi synnyttää aina yhdestä useampaan faktaskeemaa itse tietovarastoon, ja tällä tavalla nähdään helposti, mitkä dimensiot ovat konformoituja useamman bisnesprosessin suhteen.

Esimerkkimme: Enterprise Data Warehouse BUS Matrix

Enterprise Data Warehouse BUS – haitat

–  Vaatii ainakin osittaista panostamista master dataan (on toisaalta hyväkin asia)

–  Vie alussa hieman enemmän aikaa toteuttaa kuin puhdas tähtimalli; ensimmäinen julkaisusykli on pidempi

Enterprise Data Warehouse BUS – hyödyt

–  Säästytään isolta refaktorointityöltä fyysisen skeeman ja tietomallin osalta ajan kuluessa

–  Luo säästöjä ja nopeuttaa Time-To-Solutionia kokonaisratkaisussa

–  Saadaan maksimaalinen 360-näkymä bisnesdataan

–  Tietomalli yksinkertaistuu (vähemmän tauluja)

Esimerkkimme – Enterprise Data Warehouse BUS dimensionaalinen malli

Yhteenveto

Conformed Data Warehouse BUS on yksinkertainen ja nerokas tapa säästää aikaa ja vaivaa monilähteisessä tietovarastoinnissa, kuten EDW-hankkeissa. Vanhaa japanilaista viisautta pilke silmäkulmassa soveltaen: ”Mikään ei ole niin tärkeää tietovarastoinnissa kuin täysin valmis master data – eikä sekään ole niin kovin tärkeää.”

Haluatko keskustella kanssani tietomallinnuksesta? Ota yhteyttä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Jatkan jälleen blogisarjaani tietomallintamisesta. Edellisessä postauksessani kuvasin tähtimallin keskeisiä elementtejä. (Tietomallinnus – Osa3: Tähtimalli (Star schema)). Tänään läpikäyn lumihiutalemallia (=Snowflake schema). Alla linkit muihin tietomallinnuksen blogisarjan blogeihin.

• Tietomallinnus – Osa 1: Intro
• Tietomallinnus – Osa 2: Kolmas normaalimuoto (OLTP)
• Tietomallinnus – Osa 3: Tähtimalli (Star schema)
• Tietomallinnus – Osa 4: Lumihiutalemalli (Snowflake schema)
• Tietomallinnus – Osa 5: Enterprise Data Warehouse BUS
• Tietomallinnus – Osa 6: Data Vault

Lumihiutalemallin (Snowflake schema) hyödyt

Lumihiutalemalli (Snowflake schema) on tietokantojen mallinnustapa, joka on erityisen hyödyllinen tietyissä tietovarasto- ja analytiikkasovelluksissa. Tässä on keskeisiä hyötyjä lumihiutalemallin käytöstä:

1. Tiedon normalisointi ja redundanssin vähentäminen

• Tietojen normalisointi: Lumihiutalemalli hyödyntää korkeamman asteen normalisointia, mikä tarkoittaa, että dimensioiden taulut on jaettu alidimensioihin. Tämä vähentää tietojen redundanssia ja parantaa tietojen eheyttä.
• Pienempi tietokantakoko: Koska tietojen redundanssi on minimoitu, tietokannan fyysinen koko on usein pienempi kuin tähtimallissa, mikä voi säästää tallennustilaa ja kustannuksia.

2. Parannettu tietojen eheys

• Tietojen eheys: Koska lumihiutalemallissa tiedot ovat normalisoituja, yhdenmukaisuuden varmistaminen on helpompaa. Tämä vähentää virheiden ja ristiriitaisten tietojen riskiä.
• Yksinkertaistettu tietojen hallinta: Normalisoidut taulut helpottavat tietojen päivittämistä ja ylläpitoa, koska tiedot ovat hajautettu useisiin liittyviin tauluihin.

3. Tehokkaampi tietojen hallinta ja kyselyiden tarkkuus

• Tarkemmat kyselyt: Lumihiutalemallin normalisointi mahdollistaa tarkemmat kyselyt, koska se tarjoaa yksityiskohtaisempaa tietoa dimensioista ja niiden alidimensioista.
• Tehokkaat liittymät: Vaikka lumihiutalemalli saattaa vaatia useampia liittymiä (joins), se hyötyy usein siitä, että liittymät ovat tarkempia ja hyödyntävät pienempiä tauluja, mikä voi joissakin tapauksissa parantaa suorituskykyä.

4. Joustavuus ja laajennettavuus

• Modulaarisuus: Lumihiutalemalli on modulaarinen ja helposti laajennettavissa uusilla dimensioilla ja alidimensioilla ilman suuria muutoksia olemassa olevaan rakenteeseen.
• Joustavuus: Normalisoidun rakenteen ansiosta lumihiutalemalli voi olla joustavampi käsiteltäessä monimutkaisia liiketoimintaprosesseja ja -sääntöjä.

5. Parempi analytiikka monimutkaisille tietorakenteille

• Monimutkaiset tietorakenteet: Lumihiutalemalli soveltuu hyvin tilanteisiin, joissa on tarpeen käsitellä monimutkaisia ja hierarkkisia tietorakenteita. Tämä mahdollistaa tarkemman analyysin ja raportoinnin.
• Hierarkkisten suhteiden hallinta: Lumihiutalemalli tukee hierarkkisten suhteiden ja monitasoisten dimensioiden mallintamista, mikä voi olla hyödyllistä monimutkaisissa liiketoimintaskenaarioissa.

Yhteenveto

Lumihiutalemallin käyttö tietokantojen mallinnuksessa tarjoaa etuja erityisesti tietojen normalisoinnin, eheyden, joustavuuden ja monimutkaisten tietorakenteiden hallinnan näkökulmasta. Vaikka se saattaa vaatia enemmän liittymiä kyselyissä verrattuna tähtimalliin, sen tarjoamat hyödyt tietojen hallinnassa ja analytiikassa voivat olla merkittäviä monissa käyttötapauksissa.

Lumihiutalemallin perusteet

Lumihiutalemalli on eräs fyysisen tietomallintamisen menetelmä, jolla voidaan rakentaa tietovarastoja ja data martteja. Se on läheistä sukua tähtimallille ja hieman etäisempi esi-isä data vaultille.

Itse miellän lumihiutalemallin skeeman eräänlaiseksi OLTP-mallin ja tähtimallin välimuodoksi. Sillä kun on piirteitä sekä ei-toiminnallisia ominaisuuksia molemmista. Lumihiutalemallissa on enemmän tauluja sekä niiden välisiä liitoksia kuin tähtimallissa, toisin sanoen malli on normalisoidumpi kuin tähtimallissa mutta denormalisoidumpi kuin OLTP-mallissa: Siinä missä tähtimallissa kunkin faktataulun ympärille generoituu yksiulotteisia ”tähden sakaroita” eli dimensioita, lumihiutalemallissa normalisoidaan dimensiorakennetta niveltämällä tähtien sakaroihin ns. ”alidimensioita”, aivan kuten lumihiutaleen kiderakenteessa. Esimerkiksi; sen sijaan, että kasvattaisimme faktataulun viiteavainmäärää, luomme uuden alidimension jatkoksi tarkimman granulariteetin omaavalle dimensiolle, johon viittaamme tästä karkeamman granulariteetin dimensiosta. Tässä reunaehtona on, että dimensiot liittyvät loogisesti toisiinsa, kuten esim. Tuotedimensio ja Tuoteryhmädimensio. (Tuotteella on yksi Tuoteryhmä ja Tuoteryhmässä voi olla monta Tuotetta).

Lumihiutalemallin keskeiset sudenkuopat

–       Erillisiä hierarkioita ei kannata yleensä purkaa lähinnä suorituskykynäkökulmasta omiksi lumihiutaleikseen, ellei tällä sitten saavuteta esimerkiksi konkreettista tilansäästöä tai esimerkiksi käytettävä BI-teknologiaratkaisu suosii ko. mallia.

–       Lumihiutalemallissa taululiitosten määrä aina kasvaa ja komplisoi tietomallia sekä hidastaa SQL-kyselyitä konkreettisesti. Siksi se sopii vain tiettyihin käyttötapauksiin.

–       Lumihiutalemallin ylläpitäminen voi ajan saatossa tulla kankeaksi ja työlääksi ETL-prosessin osalta, eritoten mikäli lähdejärjestelmien tietomallit elävät paljon.

Lumihiutalemallin tekniset hyödyt

–       Tilansäästöt voivat olla merkittäviäkin tietyissä käyttötapauksissa.

–       Eräs lumihiutalemallin eduista on ns. ”bridge”-tekniikka eli siltaus, jonka avulla voidaan purkaa monen suhde moneen -relaatio järkevästi siten, että meillä on faktataulu, johon kytketään dimensiotaulu siten, että alidimension ja dimension välille syntyy bridgetaulu, joka normalisoi monen suhde moneen -relaation viittaamalla yhtä aikaa dimensioon ja sen alidimensioon; näin atomisoiden dimensio-alidimensio -arvoparit. Esimerkiksi; mikäli meillä on Tuote joka voi kuulua moneen Tuoteryhmään ja Tuoteryhmä joka voi linkittyä moneen Tuotteeseen. Edelleen; bridgetauluun voidaan tuoda ns. ”weighting factor” -kenttä, joka pilkotaan alidimension esiintymien suhteessa per dimensio tietuetasolla murto-osaksi sadasta prosentista. Esimerkiksi; jos meillä on vaikkapa sairaalajärjestelmässä potilas, joka saa 3 diagnoosia ajanhetkellä t, on hänen diagnostinen weighting factorinsa 100% / 3 = 0,333… (desimaalilukuna). Tällöin voidaan laskea faktoja sekä dimension että alidimension suhteen, koska datan summautuvuus on grainin suhteen vakio (3 x 0,333… = 1).

Esimerkkimme lumihiutalemallista.

Oheisesta tietomallista voitaisiin kysellä varsin triviaalisti vaikkapa laskutustiedot potilaittain ja kohteittain tai vastaavasti summata vastaavat tiedot diagnooseittain. Mikäli sama tulos haluttaisiin saavuttaa tähtimallilla, voisi vaihtoehtona olla esim: 1) syventää granulariteettia ja linkittää diagnostinen dimensio suoraan faktan piiriin tai: 2) luoda diagnostisia filtterikenttiä potilasdimensioon, joka voisi toisaalta johtaa hankalasti ylläpidettävään tietomalliin, koska diagnoosit voivat elää ajan funktiona sekä edelleen: 3) luoda useampi faktataulu.

Yhteenveto

Lumihiutalemallilla voi olla paikkansa silloin kun tietomalli on todella kompleksinen tai datamäärä muutoin nousee tähtimallin kanssa ongelmaksi. Normalisoinnilla on kuitenkin hintansa, eritoten ylläpidettävyyden ja SQL-kyselyiden suorituskyvyn suhteen.

Haluatko keskustella tietomallintamisesta? Ote yhteyttä niin jutellaan.

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Tämä kirjoitus jatkaa blogisarjaani tietomallintamisesta. Edellisessä blogipostauksessani käsittelin kolmatta normaalimuotoa. (Tietomallinnus – Osa 2: Kolmas normaalimuoto (OLTP)). Tänään puhutaan tähtimallista (=star schema). Alla linkit muihin tietomallinnuksen blogisarjan blogeihin.

• Tietomallinnus – Osa 1: Intro
• Tietomallinnus – Osa 2: Kolmas normaalimuoto (OLTP)
• Tietomallinnus – Osa 3: Tähtimalli (Star schema)
• Tietomallinnus – Osa 4: Lumihiutalemalli (Snowflake schema)
• Tietomallinnus – Osa 5: Enterprise Data Warehouse BUS
• Tietomallinnus – Osa 6: Data Vault

Tähtimallin (star schema) hyödyt

Tähtimalli (star schema) on tietokantojen mallinnustapa, joka on erityisen hyödyllinen tietovarastoissa ja tietokanta-analytiikassa. Tässä on muutamia keskeisiä hyötyjä tähtimallin käytöstä:

1. Yksinkertaisuus ja helppolukuisuus

• Käyttäjäystävällinen rakenne: Tähtimallin selkeä ja yksinkertainen rakenne tekee siitä helppolukuisen ja ymmärrettävän sekä teknisille että liiketoiminnan käyttäjille.
• Yksinkertaiset kyselyt: Tähtimallin yksinkertainen rakenne mahdollistaa suoraviivaiset ja tehokkaat SQL-kyselyt, mikä helpottaa tietojen hakemista ja analysointia.

2. Tehokas kyselyjen suorituskyky

• Parannettu suorituskyky: Tähtimalli on optimoitu tietojen nopeaan hakemiseen, koska se vähentää monimutkaisten liittymien (joins) tarvetta verrattuna muihin malleihin, kuten lumihiutalemalliin (snowflake schema).
• Indeksointi: Dimension taulut voivat hyödyntää tehokasta indeksointia, mikä parantaa kyselyjen suorituskykyä.

3. Joustavuus ja laajennettavuus

• Laajennettavuus: Tähtimalli on helposti laajennettavissa uusilla dimensioilla ja tosiasioilla ilman suuria muutoksia olemassa oleviin tauluihin tai kyselyihin.
• Käyttö tapauksiin soveltuvuus: Tähtimalli sopii hyvin moniin liiketoiminnan tarpeisiin, erityisesti silloin, kun analysoidaan suuria tietomääriä ja tarvitaan nopeita vastauksia ad-hoc-kyselyihin.

4. Ylläpidon helppous

• Yksinkertainen ylläpito: Tähtimallin yksinkertainen rakenne tekee sen ylläpidosta helppoa. Muutokset dimensioihin ja tosiasioihin voidaan tehdä ilman suuria vaikutuksia muihin osiin tietovarastoa.
• Selkeä rakenne: Selkeä ja looginen rakenne helpottaa tietovaraston hallintaa ja tukee paremmin tietojen eheyttä ja laatua.

5. Tiedon monimuotoisuuden hallinta

• Moniulotteinen analyysi: Tähtimalli mahdollistaa moniulotteisen analyysin käyttämällä erilaisia dimensioita (esim. aika, paikka, tuote), mikä tekee siitä erinomaisen valinnan liiketoiminta-analytiikkaan ja raportointiin.
• Käyttäjäkeskeisyys: Tähtimallin käyttö mahdollistaa liiketoimintakäyttäjille suunnattujen raporttien ja kyselyjen suunnittelun heidän tarpeidensa mukaisesti, koska tiedot ovat helposti saatavilla ja analysoitavissa.

Miksi tähtimalli (star schema)

Yhteenvetona tähtimalli tarjoaa yksinkertaisen, tehokkaan ja joustavan tavan tietojen mallintamiseen ja hakemiseen tietovarastoissa, mikä parantaa sekä suorituskykyä että käytettävyyttä. Tämä tekee siitä suositun valinnan monissa liiketoiminnan analytiikkasovelluksissa.

Eräs fyysisten tietomallien tyypeistä on ns. tähtimalli. Se on raportointitietokannoissa (data mart, EDW) yleisimmin käytetty tietomalli. Tähtimalli on myös OLAP-teknologiassa käytetty skeema ja sitä käytetään hyvin yleisesti myös Power BI-raportoinnissa. Tähtimallin skeema sijoitetaan lähes poikkeuksetta omaan tietokantaansa sen intensiivisten lataus- / tietokantakyselykuormien takia, jotka poikkeavat merkittävästi perinteisten OLTP-kantojen työkuormatyypeistä (vrt. OLTP:n purskeiset vs. DW:n sekventiaaliset työkuormat). Vaikka tähtimallilla onkin tyypillisesti helppoa ja nopeaa mallintaa DW-tietokanta, ei sekään automaattisesti sovellu kaikkiin DW-käyttötapauksiin parhaalla mahdollisella tavalla.

Itse sain ensi puraisuni tähtimallista jo 90-luvun loppupuolella. Tämän jälkeen olen ehtinyt suunnitella ja toteuttaa vuosien varrella useita kymmeniä tähtimallisia tietokantoja moniin eri käyttötarkoituksiin.

Esimerkkicase

Esimerkkiasiakkaamme, kuvitteellinen B2C-yritys myy globaalisti yksityishenkilöasiakkailleen erilaisia tuotteita. Tuotteita voi ostaa kerralla useamman kappaleen ja niillä on aina kunakin ajanhetkenä tietty yksikkö- sekä näin ollen kokonaismyyntihinta. Asiakkaallamme on tarve tuottaa monipuolisesti raportteja sekä hyödyntää edistynyttä analytiikkaa erillisestä raportointikannasta ilman, että tuotantopalvelimen CRM-kanta häiriintyy (CPU-kuorma, levylatenssit, muistinkäyttö, lukitukset jne.). Ratkaisuksi tähän luodaan tähtimallinen data mart -tietokanta, johon tiedot ladataan operatiivisesta tietokannasta yöllisinä eräajoina.

Esimerkkimme tähtimallista.

Tähtimallin taulut

Tähtimallissa on periaatteessa vain kahdenlaisia tauluja: Faktatauluja sekä niitä ympäröiviä dimensiotauluja. Sen normalisointi toteutetaan toisessa normaalimuodossa (2NF), jolloin sama tieto toistuu (=redundanssi) useamman kerran tietokannassa. Tällä tekniikalla saavutetaan kuitenkin merkittäviä helppokäyttöisyys- ja suorituskykyhyötyjä luotaessa erilaisia raportteja sekä analyysejä historiatyyppisestä datasta. Kuinka temppu sitten käytännössä tehdään?

Faktat ja dimensiot

Faktataulu sisältää tapahtumamuotoista tietoa, eli laskennallisia suureita, sekä niiden viittaukset tapahtumia kuvaaviin olioihin (dimensiot) sekä dimensioattribuutteihin. Jokaisesta faktataulun tapahtumasta (=transaction) on viittaukset sitä ympäröiviin dimensiotauluihin. Tätä voidaan ajatella siten, että jokaisen “tähden” ytimenä toimii faktataulu ja dimensiot ovat tähteä ympäröiviä sakaroita. Edelleen huomataan, että faktataulu sisältää avaimien lisäksi ainoastaan laskennallisia, aggregoitavia tietojäseniä eli mittaritietoa (=measure). Tarvittaessa faktataulun suorituskykyä voi parantaa lisäämällä sinne erilaisia laskennallisia kolumneja (=calculated members), jotka summautuvat eri tasoilla dimensioiden suhteen. Mitä nämä dimensiot sitten ovat?

Dimensionaalisissa tauluissa kuvataan kunkin faktarivin ominaisuuksia halutulla tiedon tarkkuustasolla. Eli esimerkiksi sitä, mikä oli tuotteen myyntipäivä tai että kuka ja mistä osti tietyn tuotteen. Kaikki dimensioattribuutit on jalostettu helposti ymmärrettävään muotoon ja NULL-arvot korvataan ns. ”undefined” -referensseillä eli default-arvoilla jotka kuvaavat sitä, että kentälle ei ole määritelty arvoa lainkaan, jotta turhalta kolmikantalogiikalta vältytään raportoinnin yhteydessä. Kirjainlyhenteiden ja koodien lisäksi monissa attribuuteissa käytetään luonnollista kieltä, jolloin datamassa muodostuu informatiivisemmaksi sekä helpommaksi raportoida ja analysoida. Tämän lisäksi on tyypillistä, että yksittäisistä dimensiojäsenistä (=dimension members) päätellään erilaisia ryhmitteleviä tekijöitä, kuten vaikkapa yrityksen liikevaihtoluokka. Operatiiviset e. luonnolliset avaimet (=natural key) kuljetetaan massalatausten (=ETL, ELT) mukana dimensiotauluihin omiksi kentikseen.

Tähtimallin avaimet

Tähtimallille ominaista ovat inkrementaaliset kokonaislukuavaimet eli nk. surrogaattiavaimet (=surrogate keys), jolloin skeeman suorituskyky on mahdollisimman hyvä. SQL Server -maailmassa kannattaa lähtökohtaisesti käyttää bigint -tietotyyppiä taulujen pääavaimille (=primary key) silloin, kun on odotettavissa, että kantaan tullaan hilloamaan vähintään miljardeja tietueita. Lisäksi annan pienen näppärän vinkin päivädimension pääavaimen luomiseen: Siihen kannattaa sijoittaa suoraan päivämäärä muodossa YYYYMMDD. Tämä mahdollistaa mm. sen, että faktataulun viiteavaimesta näkee suoraan, mille ajanjaksolle faktatieto sijoittuu.

Tähtimallin nimeämiskäytännöistä

Käytännön syistä on hyödyllistä nimetä fakta- ja dimensiotaulut aina siten, että niiden tyyppi voidaan tunnistaa kirjoitusasunsa perusteella. Itse pidän käytännöstä, jossa nimetään faktataulut “F_” -prefiksillä ja tarpeen tullen “Fact” -postfiksillä sekä dimensiotaulut “D_” -prefiksillä. Tämä mahdollistaa tunnistettavuuden lisäksi myös tietokantaympäristössä erilaisten skriptiautomaatioiden luonnin helpottumisen. Pääavaimet kannattaa nimetä selkeyden vuoksi “PK_” ja viiteavaimet (=referential key) “FK_”.

Tähtimallin granulariteetti

Faktataulun dimensioreferenssit käytännössä määrittelevät yhdessä tiedon esitystarkkuuden (=granularity). Tätä tarkkuustasoa ei voi myöhemmin enää tarkentaa rikkomatta koko dimensionaalista mallia, refaktoroimalla sitä ja tekemällä ETL-latauksia kokonaan uudelleen. Tämän vuoksi onkin tärkeää, että ensimmäinen mietitty asia per yksittäinen tähtimallin skeema (=fakta + dimensiot) on juurikin sen granulariteetti: Esimerkin data marttiimme onkin päätetty kerätä CRM-kannasta tuotemyyntien tapahtumatietoa päivätasolla, tuotteittain, henkilöittäin sekä kohdekaupungeittain. Jos esimerkiksi haluaisimme tietää jälkeenpäin tunnin tarkkuudella, että mihin aikaan jotain tiettyä tuotetta on tilattu, emme saisi tätä tietoa raportoitua data martin kautta, koska granulariteetti aikadimension suhteen on päivä (D_Date).

Faktataulun summautuvuus (=additivity)

Faktataulun kukin mitta-arvo voi summautua eri tavoin. Näitä on kolmea eri tyyppiä. Ne ovat:

–       Non-additive measures. Tällaisia ovat sellaiset mitta-arvot, jotka eivät aggregoidu millään dimensiotasolla.

–       Semi-additive measures. Tällaisia ovat sellaiset mitta-arvot, jotka aggregoituvat oikein vain tietyillä dimensiotasoilla.

–       Full-additive measures. Tällaisia ovat sellaiset mitta-arvot, jotka aggregoituvat oikein kaikkien dimensiotasojen suhteen.

Aikadimensio ja dimensiohierarkiat

Aikadimensio sisältää hierarkkisen (=dimension hierarchy) kuvauksen kuhunkin faktataulun tapahtumaan liittyvästä ajankohdasta, esimerkiksi vuosi-kuukausi-päivämäärä. Esimerkissämme on jokseenkin kattava aikadimensio, mutta perusteellisissa aikasarja-aritmetiikkaa vaativissa ympäristöissä saattaa olla tarvetta jopa kymmenille hierarkian tasoille (=level). Myös rinnakkaisia hierarkioita voi olla useita, esim. vuosi-kuukausi-päivämäärä vs. vuosi-kvartaali-kuukausi. Aikadimension generointi kannattaa automatisoida erillisellä skriptillä, jolloin sen ylläpitäminen on vaivatonta.

Role playing -dimensiot

Tähtimallille ominaista on dimensiotaulujen monikäyttöisyys. Ajatellaanpa vaikkapa aikadimensiota (D_Date). Voimme haluta seurata tuotteen myyntiä esimerkiksi sekä myyntipäivän (FK_SalesDate), että tilauspäivän (FK_OrderDate) suhteen. Tämä on mahdollista luomalla yksinkertaisesti kullekin tällaiselle tarpeelle oma viiteavaimensa faktatauluun, joka sitten viittaa samaan dimensioon mutta mahdollisesti eri riviin kyseisessä taulussa. Esimerkiksi siten, että FK_OrderDate viittaa tilauspäivämäärätietueeseen ’18.6.2023’ aikadimensiossa (D_Date) ja FK_SalesDate viittaa vastaavasti tuotteen myyntipäivämäärään ’19.6.2023’.

Parent-Child -hierarkiat

Parent-Child-hierarkialla (Parent-Child hierarchy) voidaan kuvata hierarkkista dimensiota, jonka syvyys vaihtelee. Tällainen tyypillinen hierarkia on esimerkiksi organisaatiodimensio, jossa organisaation eri tasoilla voi olla vaihteleva määrä esihenkilöitä sekä alaisia. Viittaukseen käytetään tyypillisesti ns. ParentId -kenttää, joka viittaa dimensiotauluun itseensä (=implosion).

Hitaasti muuttuvat dimensiot (SCD, Slowly Changing Dimension)

Hitaasti muuttuvat dimensiot (=SCD) kuvaavat dimensiotiedon muutosta ajan funktiona. Tämä on tietovaraston merkittävä etu verrattuna operatiivisiin tietokantoihin, joissa on tyypillistä että niistä säilötään vain viimeisin tieto. Näitä ovat SCD0, SCD1, SCD2, SCD3, SCD4, SCD5, SCD6 sekä SCD7, joista yleisimpiä ovat SCD0, SCD1 ja SCD2. Kaikki nämä tietueet identifioidaan luonnollisen avaimen perusteella. Tyypillisimmät SCD:t ovat:

–       SCD0-dimensiossa säilytetään aina alkuperäinen arvo. Tällaista tietoa ovat mm. auton rekisterinumero sekä henkilön syntymäpäivä. Tämän SCD-tyypin heikkous on se, että dimensiohistoriaa ei synny.

–       SCD1-tyyppisessä dimensiossa kenttäkohtaiset muutokset jyrätään aina yli ilman historiointia. Tämän SCD-tyypin heikkous on se, että vanhaa dimensiohistoriaa ei säilötä siihen linkitetyn faktan suhteen, vaan ainoastaan viimeinen arvo merkkaa.

–       SCD2-tyyppisessä dimensiossa joko lisätään aina kokonaan uusi rivi tietueen muuttuessa höystettynä versioattribuutilla tai sitten lisätään start date- ja end date -kentät näyttämään mihin kukin tietue on voimassa (NULL end datena nykytilanteessa). Kolmas vaihtoehto on merkata kullekin tietueelle effective date ja current flag (N/Y). Tämän SCD-tyypin heikkous on lähinnä se, että mikäli dimension muutostiheys on suuri ja attribuutteja on paljon, dataa voi kertyä todella runsaasti – tämä voi olla joskus haaste latausajoille sekä taulun indeksoinnin suhteen.

Monsteridimensiot

Monstereita ovat sellaiset dimensiot, joissa tietuemäärä kasvaa niin suureksi, että se alkaa vaikuttamaan tietovarastokannan suorituskykyyn. Näitä dimensioita kannattaa usein pilkkoa pienemmiksi, luokitteleviksi dimensioiksi, jotta suorituskyky paranee. Samalla säästetään tilaa. Tähän on olemassa lukuisia eri tekniikoita sekä parhaita käytäntöjä. Eräs tekniikka on luoda ns. identity-profile-dimensiopareja, joista identity-dimensioon säilötään dimension muuttumattomat attribuutit ja profile-dimensioon dimension muuttuvat tiedot. Tämän seurauksena tietuemäärät vähenevät ja turhalta toisteisuudelta vältytään. Tietovarastossakin rajulla denormalisoinnilla on hintansa.

Yhteenveto

Tähtimalli voi kuulostaa simppeliltä ja pitkälle sitä onkin, mutta isommissa ja kompleksisemmissa ympäristöissä huono mallintamistekniikka johtaa helposti kömpelöön tietomalliin, jota on hankala ylläpitää ja jonka suorituskyky ja tilantarpeet ovat haastavat ja raportoitavuus sekä tuki kehittyneelle analytiikalle ovat puutteelliset. Tämän takia tähtimallista sekä siihen liittyvästä ns. dimensiomallintamisesta on jokaisen päteväksi tietomallintajaksi tähyävän syytä tuntea enemmänkin kuin vain perusteet. Tässä blogipostauksessani kävin läpi pääsääntöisesti vain perusteita. Erinomaista syväluotausta tähtimalliin liittyen löydät mm. Ralph Kimballin kirjasta ”The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling”.

Onko organisaatiossanne tietovarastointitarpeita? Otathan yhteyttä niin keskustellaan lisää!

Jani K. Savolainen
jani.savolainen@dbproservices.fi
0440353637
VP & Chairman
DB Pro Services Oy