Facts about Facts: Fact Tables Organizing in Data Warehouse Systems

Tietovarastojärjestelmän (DWH) määrittelyprosessi on alkanut. Olet hahmotellut asiaankuuluvat dimensiotaulukot, jotka liittyvät liiketoimintavaatimuksiin. Nämä taulukot määrittelevät, mitä punnitsemme, havainnoimme ja skaalaamme. Nyt on määriteltävä, miten mittaamme.

Tietotaulukoihin tallennamme nämä mittaukset. Niissä säilytetään liiketoimintatietoja, jotka voidaan aggregoida eri ulottuvuusyhdistelmiin. Tosiasia on kuitenkin se, että faktataulukoita ei ole niin helppo kuvata – niillä on omat makunsa. Tässä artikkelissa vastaamme muutamiin faktatauluja koskeviin peruskysymyksiin ja tarkastelemme kunkin tyypin hyviä ja huonoja puolia.

Mitä ovat faktataulukot?

Yleisimmässä mielessä faktataulukot ovat liiketoimintaprosessin mittareita. Niissä säilytetään enimmäkseen numeerisia tietoja, ja ne vastaavat pikemminkin tapahtumaa kuin tiettyä raporttia.

Faktataulukon tärkein ominaisuus mittausten ohella on rakeisuus. Grain määrittelee, mikä yksityiskohtaisuuden taso havaitaan tietystä tapahtumasta. (Vertaa tätä ilmaisuun ”hienojakoiset kontrollit”, joka tarkoittaa, että käyttäjät voivat hallita hyvin pieniä yksityiskohtia tilinsä osalta).

Mitä yksityiskohtaisempi faktataulukon raekoko on, sitä paremmin se pystyy käsittelemään arvaamattomia liiketoimintavaatimuksia. Suuremman raekoon haittapuolena on se, että tietojen tallentamiseen tarvitaan enemmän fyysistä tilaa. Se voi myös aiheuttaa hitaampaa suorituskykyä.

Miten faktataulukot rakentuvat?

Yleinen faktataulu koostuu kahdesta tärkeimmästä attribuuttiryhmästä:

• Mittataulukoiden vieraat avaimet
• Mittatiedot

Vieraat avaimet ovat itsestäänselviä; myös degeneroituneet mittatiedot (degenerate dimensions) kuuluvat tähän ryhmään. Degeneroitunut dimensio on dimensioavain, jolla ei ole vanhempaa dimensiotaulukkoa. Niitä esiintyy, kun kaikki ulottuvuutta koskevat tärkeät tiedot ovat jo faktataulussa. Esimerkkejä ovat erilaiset valvontaotsikkonumerot, lippujen numerot, tilausnumerot jne.

Mittarit (esim. mittarit tai liiketoimintatiedot) faktataulussa voivat olla:

• Additiivisia: summautuvia kaikissa ulottuvuuksissa
• Puoli-additiivisia: summautuvia joissakin ulottuvuuksissa
• Ei-additiivisia: ei summautuvia (esim. erilaiset suhdeluvut)

Mittareiden ja vierasavainten lisäksi teknisiä sarakkeita voi olla monia. Tekniset sarakkeet ovat hyödyllisiä mallin tarkastuksessa ja matalan tason ylläpidossa. Aikaleimat, joita käytetään merkitsemään, milloin faktataulussa tapahtuu lisäyksiä tai päivityksiä, ovat yleinen esimerkki teknisestä sarakkeesta.

Yleisen faktataulun havainnollistamiseksi tarkastellaan hyvin yksinkertaista tähtikaaviota:

Tässä mallissa on yksi faktataulu, jota ympäröi kolme ulottuvuustaulua. Fakta-taulun vierasavaimemme ovat:

• time_id – viittaa aikaulottuvuustauluun (dim_time)
• product_id – viittaa tuoteulottuvuustauluun (dim_product)
• store_id – viittaa myymäläulottuvuustauluun (dim_store)
• pos_transaction – degeneroitunut ulottuvuus. Tässä on kaikki tarvitsemamme tiedot POS:sta, joten voimme tallentaa ne tänne. Ei ole mitään syytä rakentaa toista dimensiotaulukkoa.

Mitta-avainryhmämme koostuu:

• sales_quantity – Yhden tuotteen myynti kerrallaan yhdessä myymälässä. Tämä toimenpide on additiivinen.
• sales_price – Tuotteen hinta sellaisena kuin se on myyty myymälässä. Tämä on ei-additiivinen mitta.

Tässä on yksi tekninen sarake, time_inserted, johon tallennetaan aika, jolloin rivi on lisätty.

Nyt kun meillä on yleiskäsitys faktataulukoista, perehdymme niiden eri makuihin. Faktatauluja on neljää tyyppiä: transaktiotaulukot, jaksoittaiset tilannekuvat, kertyvät tilannekuvat ja faktattomat faktataulukot.

Jokainen maku palvelee tarkoitusta sen taustalla olevan liiketoiminnan esittämisessä, jota tietovarastointijärjestelmä tukee. Ennen kuin kuitenkin perehdymme siihen, mitä nämä erilaiset faktataulut tekevät, puhutaan tärkeästä yhteisestä tekijästä: harvuudesta eli faktatauluun tallennetun tiedon suhteellisesta määrästä. Harvinaisuus liittyy rakeisuuteen, ja sillä on vaikutusta kyselyn suorituskykyyn.

Mitä on faktataulukon harvinaisuus?

Faktataulua suunniteltaessa on otettava huomioon sen harvinaisuus eli se, kuinka suuri osa taulun riveistä on täytetty ja kuinka suuri osa tyhjiä. Jos täytämme faktatauluja monista taustalla olevista taulukoista, on viisasta arvioida harvinaisuus. Arvio tehdään faktataulukon rakeisuuden perusteella. Jos faktataulukkosi vastaa taustalla olevaa lähdetaulukkoa, voit käyttää taustalla olevan taulukon kardinaliteettia harvinaisuuden laskemiseen.

Harvinaisuuden laskemiseksi selvitämme ensin täyttösuhteen eli ei-tyhjien solujen määrän suhteessa solujen kokonaismäärään. Täyttösuhteen laskemiseksi jaa lähdetaulukon rivien lukumäärä kunkin ulottuvuustaulukon erillisten rivien lukumäärällä. Tämän jälkeen harvuus lasketaan muodossa 1-(f), jossa f = täyttösuhde.

Esimerkki:

Esitetään, että täytämme fact_retail_sale-taulun kahdesta lähteestä: tietokantataulusta, jossa on 100 000 riviä, ja taulukkolaskentaohjelmasta, jossa on 20 000 riviä. Nämä rivit täytetään vuosittain. Mitoitustaulukot ovat: päivämäärät (365 riviä), tuotteet (100 riviä) ja myymälät (1 000 riviä).

Laskemme harvuuden seuraavasti:

1-(100 000 + 20 000)/(365*100*1 000) = 0.99671

Tämä faktataulukko on harva, koska alle 1 %:lla sen riveistä on ei-nolla-arvoja.

Hyvä nyrkkisääntö on, että pidämme kaikkea, mikä on alle 2 %, hyvin harvalukuisena; kaikki, mikä on yli 2 %, ei ole niin harvalukuista.

Transaktiotietotaulukot

Tämä on yksinkertaisin ja yleisin faktataulukkotyyppi. Tämän tyypin rakeisuus on yksi rivi tapahtumaa kohti tai yksi rivi tapahtuman riviä kohti. Transaktiotietotaulukon raekoko on piste tilassa ja ajassa. Niihin tallennetaan pienimmätkin liiketoimintatiedot.

Tapahtuman tapahtuessa siitä tallennetaan laaja konteksti. Tämä konteksti luo paljon yksityiskohtia dimensiotaulukoihin, joten odotamme, että niitä on paljon.

Kun olemme lisänneet rivin transaktiotaulukkoon, sitä tarkastellaan harvoin, jos koskaan, uudelleen. Tämä saa meidät pohtimaan erityisiä tekniikoita tämäntyyppisten taulukoiden täyttämiseksi. Ei päivityksiä tarkoittaa paljon yksinkertaisempaa ETL-prosessia (extract, transform, and load).

Pros: Suurempi rakeisuus mahdollistaa yksityiskohtaisten liiketoimintojen seurannan.

Miinukset: Suorituskykyongelmat kyselyissä. Vaikeus tulkita trendikäyttäytymistä kiinteinä ajankohtina.

Esimerkki tapahtumien faktataulusta

Katsotaanpa tyypillistä finanssitähden skeemaa:

Tallennamme jokaisen yksittäisen tilitapahtuman faktatauluun. Esimerkkitietoja tästä taulusta ovat:

Huomaa: time_date esitetään siten kuin se näkyy dim_time-ulottuvuudessa, ja teknistä saraketta ei oteta huomioon.

Kun tilinomistaja suorittaa tapahtumia, kuten rahan tallettamista ja nostamista, tallennamme jokaisen toiminnon.

Tämän rakenteen avulla voimme vastata seuraaviin kysymyksiin: Kuinka monta tapahtumaa liiketoimintamme käsittelee päivässä? Mikä on keskimääräinen nostettu summa päivässä? jne.

Jaksoittaiset tilannekuvatietotaulukot

Jaksoittaiset tilannekuvatietotaulukot tallentavat liiketoiminnan kumulatiivisen suorituskyvyn ennalta määritettyinä ajanjaksoina. Avainasemassa on ennalta määrätty aikaväli tilannekuvien ottamiselle: päivittäin, viikoittain, kuukausittain jne. Tulokset tallennetaan jaksoittaiseen tilannekuvatietotaulukkoon.

Tätyyppinen tietokantataulukko antaa paljon liikkumavaraa: siihen voidaan sisällyttää mitä tahansa tietoa, joka kuvaa toimintaa tietyn ajanjakson aikana.

Tapahtumasta voidaan siirtyä jaksoittaiseen taulukkoon. (SQL:ssä on joitakin siistejä algoritmeja, jotka käsittelevät tätä, kuten näemme tulevissa artikkeleissa). Kyse on vain transaktioiden yhteenlaskemisesta tilannekuvasta toiseen. Aivan kuten transaktioiden faktataulukoissa on monia ulottuvuuksia, tilannekuvassa on yleensä vähemmän ulottuvuuksia.

Pros: Suorituskykyhyöty, liiketoiminnan pitkittäisnäkymä, fyysinen tiiviys.

Miinukset: Pienempi jyvitys.

Esimerkki jaksoittaisesta tilannekuvatietotaulusta

Katsotaanpa erästä toista finanssitilaston tähdistöskeemaa:

Asetetaan, että olemme rakentaneet tämän tilannekuvatietotietotaulukon ja siihen liittyvän datamartin samasta lähdeviitekehyksestä kuin edellisen esimerkin. Olisimme voineet jopa rakentaa tämän faktataulun edellisen faktataulun tiedoista.

Meidän on näytettävä balance_amount jokaisena tilannekuvapäivänä; tässä tapauksessa tilannekuvan rakeisuus on yksi päivä. Yhdistämme kaikki lähtevät tapahtumat päivittäin, ja talletustyyppi on lisäävä ja nosto on vähentävä. Tulos on:

Huomautus: Esitämme time_date sellaisena kuin se näkyy dim_time-ulottuvuudessa, ja tekninen sarake jätetään huomiotta.

Nyt voimme vastata joihinkin yleisiin liiketoimintakysymyksiin, kuten: Kuinka monta päivää asiakkaalla on positiivinen tilisaldo kuukaudessa? Mikä on tietyn tyyppisen asiakkaan keskimääräinen saldo joka kuukausi? jne.

Jaksottaisen tilannekuvan faktataulukon uudelleenjärjestäminen

Olet ehkä huomannut jonkin verran redundanssia edellisessä esimerkissä näytetyissä tiedoissa. Joskus jopa snapshot-faktataulu on liian suuri ja suorituskyky romahtaa. Mitä silloin on tehtävä? Voimme järjestää snapshot-faktataulumme uudelleen joksikin kompaktimmaksi.

Olemme lisänneet kaksi uutta saraketta, date_from ja date_to. Ne kuvaavat ajanjaksoa, jona balance_amount oli voimassa.

Taulukon tiedot näyttäisivät tältä:

Joka kerta, kun balance_amount muuttuu, suljemme aikajakson ja avaamme uuden.

Huomaa, että sarakkeessa date_to on ei-integroiva aikarajausmerkki. Tämä on otettava huomioon tietoja kysyttäessä. Löydämme myös yhden mielenkiintoisen päivämäärän – 31/12/9999. Tämä päivämäärä osoittaa, että viimeinen rivi on viimeinen tunnettu saldo. Päivitämme sen, kun saldo muuttuu.

Alhaalla näemme SQL:n, jota käytämme tilannekuvan kyselyyn tietyllä päivämäärällä (:date).

SELECT ft.*FROMfact_account_snapshot ftWHERE date_from 
Accumulating Snapshot Fact Tables
The accumulating snapshot fact table is the least common type, but it's also by far the most interesting and challenging. We use this table type when we represent processes that have a definite beginning and end.They are most appropriate when users must perform pipeline and workflow analysis.
Accumulating snapshot tables usually have multiple date foreign keys, each corresponding to a major milestone in the workflow. And while the processes captured in this type of table have beginnings and ends, intermediate steps are recorded as well.
Transaction fact tables may have several dates associated with each row; the accumulating fact table allows for many of the date values to be unknown when the row is first loaded. This is unique among fact tables, and it can lead to anomalies – incomplete dimensions – in dimensional modeling.
We solve this by adding a default value to the missing fields in the fact table and the appropriate dimension table. As we revisit the row containing specific workflows, we must implement a different type of ETL for working with these tables.
Pros: Enables complex analysis of tables and workflow analysis.
Cons: High ETL complexity.
An Example of an Accumulating Snapshot Fact Table
In this example, imagine that we have to analyze the effectiveness of our loan approval process, from initial application to final funding. Obviously, we first need to prepare our data!

  

Our loan approval process has four steps:
 
Loan application from a bank customer
 
Submission of complete documentation needed for loan approval (appraisal, credit status etc.)
 
Approval of the loan application from a higher-ranking bank employee
 
Funding the loan
 
One row in our fact table represents one instance of this process.
As we see in the data table, not all our workflows are complete; future actions will make us revisit the fact table and update our data. This is the main difference between this and other types of fact table. 
Some questions that we can answer with this type of fact table are: Where is the bottleneck in the loan approval process? What is causing it? What is the average time needed for end-to-end approval? etc.
 Let's look at the data from the fact table. We'll split the display into two tables:


Looking at the data, we see a strange date: 01.01.1900. This date represents a missing or "not yet filled" date. It is more practical to have a default value for missing key data so we don't have abnormalities when we do a roll-up. 
We can also draw some conclusions from this data. Bigger loans take more time to process. This is normal because they need more documentation and safety checks. Smaller loans complete the process much faster.
Factless Fact Tables
This inconspicuous fact table can also be found in the data warehouse modeling world. The factless fact table does not have any measurements; it only holds foreign keys to dimensional tables. This information is enough to answer relevant business questions.
Pros: Simplicity
Cons: Limited usefulness
An Example of a Factless Fact Table
In the following example, we see a star schema featuring a factless fact table. The schema is used for tracking student attendance, with four dimensional tables representing courses, facilities, dates, and departments.


With this schema, we can find answers to questions like: How many students attended a class last month? What course has the lowest attendance this year? What facility is not being used? etc.
A simple query can answer the last question. The code would be:
SELECT course_id, count(*) number of attendanceFROMfact_student_attendance ftJOIN dim_course dc ON (dc.course_id = ft.course_id)JOIN dim_time dt ON (dt.time_id = ft.time_id)WHERE dt.time_year = 2016GROUP BY course_idORDER BY count(*) desc
Vastauksemme palautetaan kyselyn ensimmäisellä rivillä.
Tietokantataulukot ovat tietovarastomaailman lihaa. Niiden koko voi olla useita teratavuja, ja ne vievät eniten tilaa tietovarastossa. Virhe faktataulujen suunnittelun alkuvaiheessa tuottaa monia ongelmia, joiden ratkaiseminen ei helpotu tietovaraston kehittyessä! 
Tietoja on mahdollista järjestää uudelleen yhdestä faktataulukkotyypistä toiseen. Tutustumme tekniikoihin, joilla tämä voidaan tehdä, tulevassa postauksessa. Entä sinun kokemuksesi erityyppisistä faktataulukoista? Oletko onnistunut sekoittamaan niitä jollakin uudella ja jännittävällä tavalla? Jos olet onnistunut, kerro minulle.