Junk-dimensio on paikka pikkusilpulle

Erilaiset statuskentät aiheuttavat tietovarastonkehittäjänä päänvaivaa. Kimballin dimensiomallinnuksen ohjeistuksen mukaan en saisi upottaa niitä suoraan faktatauluun, mutta en saisi myöskään kasvattaa dimensioiden määrää pikkiriikkisillä tauluilla. Minulle jää vaihtoehdoksi kerätä tähteet ja heittää ne omaan dimensioonsa, joka on englantilaiselta kutsumanimeltään junk dimension eli vapaasti suomennettuna sälädimensio.

Minne kentän laittaisin?

Aina tarvittavalle kentälle ei löydy sopivaa paikkaa. Mihin esimerkiksi tallettaisin tilauksen statuksen? Ensimmäisenä vaihtoehtona mieleeni tulee oma dimensiotaulu, jossa on statuksen tunniste ja kuvaus:

Muutaman rivin ja kahden sarakkeen kokoinen dimensio näyttää silmiini hyvin vaivaiselta, sillä olen tottunut, että dimensioissa on satoja rivejä ja useita saraketta. Entä jos jättäisin dimension kokonaan luomatta ja lisäsisin statuksen suoraan faktatauluun?

Nyt pärjään ilman ylimääräistä tynkädimensiota, mutta faktatauluun ilmestyi uusi tekstimuotoinen kenttä, joka vaatii enemmän säilytystilaa kuin numeromuotoiset avaimet. Tämä on vastoin dimensiomallinnuksen hyviä käytäntöjä.

Tekstinpätkä laskutettu sisältää kymmenen kirjainta, mikä vie tilaa 12 tavua (= 10 + 2), kun taas numeromuotoinen avainsarake veisi vain 4 tavua. Onneksi vaikutus on vähäinen, sillä miljoonan rivin faktataulussa ylimääräistä tilaa menee alle 8 megatavua (= 1 000 000 * (12 - 4) / 1024^2) enemmän tilaa kuin numeromuotoista avainta käyttäessä.

Mitä jos minulla olisi muitakin statuskenttiä, joita haluaisin lisätä tietomalliin? Yritykseni tilausjärjestelmä tallentaa omiin sarakkeisiinsa tiedon, onko tehty reklamaatiota ja onko tilaus peruttu. Voisin jatkaa samalla kaavalla kuin ennenkin ja lisätä faktatauluun kaksi uutta saraketta.

En ole ihan tyytyväinen lopputulokseen. Jos raportin käyttäjä tuntee myyntijärjestelmän, hän tietää, että R-kirjaimella merkityistä tilauksista on tehty reklamaatio ja peruutuskentän Epätosi tarkoittaa, että tilausta ei ole peruutettu. Myyntijärjestelmän logiikkaa tuntemattomille merkinnät ovat mystisiä, joten valitsemani selitteet ovat siltä osin epäonnistuneita.

En halua luoda uusia dimensioita, enkä halua sotkea faktataulua arvoilla, jotka eivät sinne kuulu. Onneksi en joudu valitsemaan näiden kahden vaihtoehdon väliltä, vaan minulla on kolmaskin mahdollisuus, jonka tulen esittelemään alla.

Rakenna jämille oma dimensio

Jos dimensiomallinnus etenee kuin satumaailmassa, kaikki raportoinnissa tarvittavat attribuutit löytävät oikean paikan noin kymmenen dimension joukosta. Valitettavasti törmään jatkuvasti tietoihin, jotka eivät tunnu istuvan oikein mihinkään, enkä viitsisi niiden pienuuden takia luoda uutta dimensiotakaan. Näitä kenttiä ovat esimerkiksi statuskentät, joiden arvoina voi olla vain muutamia eri vaihtoehtoja. Näissä tapauksissa joudun kaatamaan jäljelle jääneet kentät omaan sälädimensioonsa (engl. junk dimension).

Parhaimmillaan yksittäinen dimensio kuvastaa jotakin liike-elämän oliota kuten asiakasta. Junk dimension on poikkeus, sillä se on sekalaisten kenttien kaatopaikka. Sinne joutuvat kentät, joille ei löydy muutakaan sopivaa paikkaa. Kun kerään irtonaiset kentät yhteen ja samaan paikkaan, saan luotua yhden keskikokoisen dimension, johon voi viitata yhdellä ainoalla avaimella. Minun ei siis tarvitse kasvattaa faktataulua useilla sarakkeilla pikkusilpun takia.

Voin rakentaa sälädimension, joko

• merkitsemällä dimensiotauluun kaikki mahdolliset yhdistelmät
• tai keräämällä dimensiotauluun vain järjestelmässä esiintyneet yhdistelmät

Kaikkien yhdistelmien sälätaulu

Esittelemässäni myyntijärjestelmässä on kolme tilausstatusta, kaksi reklamaatiotilaa ja kaksi peruutustilaa eli yhteensä 3 * 2 * 2 = 12 vaihtoehtoa. Kyse on joukko-opista tutusta operaatiosta eli karteesisesta tulosta, joka on erittäin helppo muodostaa tietokantaohjelmissa CROSS JOIN -komennolla.

SELECT *
FROM Tilausstatukset
CROSS JOIN Reklamaatiotilat
CROSS JOIN Peruutustilat

Vedin tässä hieman mutkia suoriksi, sillä vaihtoehtoja on enemmän kuin hetki sitten väitin. Meillä on kolme tilausstatusta myyntijärjestelmässä, mutta toisinaan emme tiedä statuksen tilaa eli neljäntenä arvona on tyhjä eli Null. Myös reklamaatio- ja peruutusarvo voi saada tyhjän arvon eli vaihtoehtoja on oikeasti 4 * 3 * 3 = 36.

Tyhjissä arvoissa on se etu, että voimme napata sälädimensiosta rivin, joka antaa arvon vain yhdelle tilakentälle, kuten peruutukselle, ilman että meidän tarvitsee arvailla tilausstatuksen tai reklamaation arvoja. Tällöin voin käyttää sälädimensiota sellaisissakin faktatauluissa, joissa ei ole kaikkia sälädimension kenttiä käytettävissä.

Kaikkien yhdistelmien taulu on siitä mukava, että luon dimension vain kerran eikä minun tarvitse päivittää taulukkoa myöhemmin, sillä kaikki tarvittava tieto on jo olemassa. Taulukossa on paljon sellaisiakin yhdistelmiä, joita ei koskaan tule esiintymään myyntijärjestelmässä. Tällä kertaa rivimäärä pysyi kohtuullisena, mutta entä jos minulla olisi ollut 8 statuskenttää, jotka kaikki voivat saada 4 erilaista arvoa. Silloin rivejä olisi ollut jo 4^8 eli 65 536 erilaista vaihtoehtoa. Käytännössä näin monta eri vaihtoehtoa ei tarvita, vaikka ne teoriassa olisivatkin mahdollisia. Tosielämässä erilaisia vaihtoehtoja saattaa esiintyä vain muutamia satoja, jolloin tuhannet rivit dimensiossa jäävät tyhjänpantiksi.

Esiintyvien yhdistelmien sälätaulu

Jos haluan rakentaa tiiviin sälätaulun ilman, että mukaan tulee turhia käyttämättömiä rivejä, pitää asiaa lähestyä eri tavalla. Pohjaksi ei tule statusten kaikki mahdolliset arvot, vaan pelkästään laskuriveillä ilmoitetut arvot. Joudun siis käymään kaikki laskurivit läpi ja tarkistamaan, mitä mahdollisia vaihtoehtoja on käytetty.

Vaikka tehtävä kuulostaa raskaalta, se on onneksi helpommin ohjelmoitu kuin tehty. Minun tarvitsee vain käyttää uniikit arvot poimivaa DISTINCT -määritystä tietokantakyselyssä ja saan haluamani vastauksen.

SELECT DISTINCT
StatusID,
ReklamaatioID,
PeruutusID

FROM
Laskurivi

Lopputuloksena on karteesista tuloa kevyempi dimensio, joka sisältää vain oleellisen. Haittapuolena on, että dimension joutuu päivittämään joka kerta, kun faktatauluun tulee uusi rivejä.

Joudun siis valitsemaan, kumpi on pienempi paha: karteesisen tulon iso sälädimensio vai distinct-kyselyn jatkuva ylläpito. Tässä kyseisessä esimerkissä valitsisin ehdottomasti karteesisen tulon, sillä rivimäärä jää niin pieneksi ettei turhien vaihtoehtojen poistosta ole mitään käytännön hyötyä.

Viittaukset sälätauluun

Kun rakennan faktataulua, haen tauluun dimensioviittaukset eli vierasavaimet. Korvaan silloin faktataulussa olevan luonnollisen avaimen keinotekoisella dimensioavaimella. Sälädimensioon viitatessa en käytä vain yhtä luonnollista kenttää vaan useita yhdellä kertaa. Käytännössä yhdistän dimensiotaulun faktatauluun ulkoliitoksella ja määritän liitosperusteeksi kaikki sälätaulun luonnolliset avaimet.

FROM fakta
LEFT JOIN dimensio
  ON fakta.kenttä1 = dimensio.kenttä1
  AND fakta.kenttä2 = dimensio.kenttä2
  AND fakta.kenttä3 = dimensio.kenttä3

Tämä lähestymistapa on hankala, jos kentät sattuvat sisältämään tyhjiä arvoja ja usein niin onkin. Sen vuoksi suosin tapaa, jossa luon dimensiotauluun erillisen yhdistetyn kentän, joka sisältää kaikki liitokseen tarvittavat kentät. Tavallisesti käytän välissä jotakin erotinmerkkiä kuten pystyviivan merkkiä |. Lopputuloksena on kenttä 100|R|Tosi. Silloin haen vierasavaimen näin:

FROM fakta
LEFT JOIN dimensio
  ON CONCAT(
    fakta.kenttä1, '|'
    fakta.kenttä2, '|'
    fakta.kenttä3
  ) = dimensio.yhdistekenttä

Tämäkään ei ole idioottivarma temppu, sillä tietokannan kentät saattavat sisältää valitsemani erottelumerkin, jolloin kaksi uniikkia riviä voi saada saman keinotekoisen avaimen. Näin ei onneksi yleensä tapahdu, mutta tilanne on hyvä tiedostaa.

Miltä sälädimensio lopulta näyttää?

Tällä kertaa päätän luoda sälätaulun, joka sisältää kaikki mahdolliset vaihtoehdot, myös tyhjät sellaiset, ja vierasavaimen hakua varten luon uuden yhdistelmäavaimen.

Aloitan luomalla kaikki dimensioon tarvittavat taulut ja tiedot:

CREATE TABLE #Tilausstatus (
	TilausstatusID int,
	Tilausstatus varchar(50)
);

INSERT INTO #Tilausstatus
VALUES
	(NULL, 'Tuntematon'),
	(100, 'Kesken'),
	(200, 'Valmis'),
	(300, 'Laskutettu')
;

CREATE TABLE #Reklamoitu (
	ReklamoituID char(1),
	Reklamoitu varchar(50)
);

INSERT INTO #Reklamoitu
VALUES
	(NULL, 'Tuntematon'),
	('R', 'Reklamoitu'),
	('H', 'Hyväksytty')
;

CREATE TABLE #Peruutettu (
	PeruutusID varchar(50),
	Peruutus varchar(50)
);

INSERT INTO #Peruutettu
VALUES
	(NULL, 'Tuntematon'),
	('Tosi', 'Peruutettu'),
	('Epätosi', 'Hyväksytty')
;

Seuraavaksi haen kyselyillä kaikkien näiden taulujen karteesisen tulon sekä luon yhdistelmäavaimen, jota kutsun taulussa nimellä LuonnollinenAvain:

SELECT
CONCAT(
	TilausstatusID, '|',
	PeruutusID, '|',
	ReklamoituID
) as LuonnollinenAvain,
*
FROM #Tilausstatus
CROSS JOIN #Peruutettu
CROSS JOIN #Reklamoitu
;

Lopputuloksena on 36 riviä sisältävä säledimensio, joka sisältää kaikki mahdolliset vaihtoehdot. Viimeisenä silauksena loisin vielä yhden sarakkeen lisää eli keinotekoisen avaimen, jonka jälkeen taulukon riveihin voisi viitata numeroilla 1-36.