Facts about Facts: Ténytáblák szervezése adattárházi rendszerekben

Az adattárházi rendszer (DWH) meghatározásának folyamata megkezdődött. Körvonalazta a releváns dimenziótáblákat, amelyek az üzleti követelményekhez kapcsolódnak. Ezek a táblák határozzák meg, hogy mit mérlegelünk, figyelünk és skálázunk. Most meg kell határoznunk, hogyan mérünk.

A ténytáblákban tároljuk ezeket a méréseket. Olyan üzleti adatokat tárolnak, amelyek a dimenziókombinációkban aggregálhatók. De az a helyzet, hogy a ténytáblák nem olyan könnyen leírhatóak – sajátos ízük van. Ebben a cikkben megválaszolunk néhány alapvető kérdést a ténytáblákkal kapcsolatban, és megvizsgáljuk az egyes típusok előnyeit és hátrányait.

Mi a ténytáblák?

A legáltalánosabb értelemben a ténytáblák egy üzleti folyamat mérőszámai. Többnyire numerikus adatokat tárolnak, és inkább egy eseménynek, mint egy adott jelentésnek felelnek meg.

A ténytáblák legfontosabb jellemzője az intézkedések mellett a szemcsézettség. A szemcseméret határozza meg, hogy egy adott eseménynél milyen részletezettségi szint figyelhető meg. (Hasonlítsuk ezt össze a “finom szemcseméretű ellenőrzések” kifejezéssel, ami azt jelenti, hogy a felhasználók nagyon apró részleteket ellenőrizhetnek a számlájukra vonatkozóan.)

Minél részletesebb a ténytábla szemcsézettsége, annál jobban kezeli a kiszámíthatatlan üzleti követelményeket. A nagyobb szemcsézettség hátránya, hogy több fizikai helyet igényel az adattárolás. Emellett lassabb teljesítményt is okozhat.

Hogyan vannak felépítve a ténytáblák?

Az általános ténytábla két fő attribútumcsoportból áll:

• Dimenziós táblák idegen kulcsai
• Mértékek

Az idegen kulcsok maguktól értetődőek; a degenerált dimenziók is ebbe a csoportba tartoznak. A degenerált dimenzió olyan dimenzió kulcs, amelynek nincs szülő dimenziótáblája. Ezek akkor fordulnak elő, ha a dimenzióval kapcsolatos minden fontos információ már megtalálható a ténytáblában. Ilyenek például a különböző ellenőrzési fejlécszámok, jegyszámok, rendelésszámok stb.

Méretek (azaz. mérőszámok vagy üzleti tények) a ténytáblában lehetnek:

• Additív: bármely dimenzióban összegezhető
• Félig additív: néhány dimenzióban összegezhető
• Nem additív: nem összegezhető (pl. különböző arányok)

A mérőszámok és idegen kulcsok mellett számos technikai oszlop is lehet. A technikai oszlopok hasznosak az auditáláshoz és a modell alacsony szintű karbantartásához. Az időbélyegzők, amelyek a ténytáblában történő beszúrások vagy frissítések időpontjának jelölésére szolgálnak, gyakori példája a technikai oszlopoknak.

Az általános ténytábla szemléltetésére nézzünk meg egy nagyon egyszerű csillagsémát:

Ebben a modellben egyetlen ténytábla van, amelyet három dimenziós tábla vesz körül. A ténytáblához tartozó idegen kulcsaink a következők:

• time_id – az idő dimenziós táblára utal (dim_time)
• product_id – a termék dimenziós táblára utal (dim_product)
• store_id – a bolt dimenziós táblára utal (dim_store)
• pos_transaction – egy degenerált dimenzió. Ez minden információ a POS-ról, amire szükségünk van, tehát itt tárolhatjuk. Nincs okunk egy másik dimenziótábla létrehozására.

A mérőkulcscsoportunk a következőkből áll:

• sales_quantity – Egy termék egy időben történő értékesítése egy üzletben. Ez a mérőszám additív.
• sales_price – Egy termék ára, ahogyan azt az üzletben eladták. Ez egy nem additív mérték.

Egy technikai oszlop van, time_inserted, amely a sor beszúrásának időpontját tárolja.

Most, hogy már van egy általános elképzelésünk a ténytáblákról, beleássuk magunkat a különböző ízükbe. A ténytábláknak négy típusa van: tranzakciós, időszakos pillanatfelvétel, halmozódó pillanatfelvétel és tény nélküli ténytáblák.

Minden ízlés a mögöttes üzletág ábrázolásában szolgál valamilyen célt, amelyet az adattárházi rendszer támogat. Mielőtt azonban belemerülnénk abba, hogy ezek a különböző ténytáblák mire szolgálnak, beszéljünk egy fontos közös tényezőről: a ritkaságról, vagyis a ténytáblában tárolt adatok arányos mennyiségéről. A ritkaság a szemcsézettséghez kapcsolódik, és hatással van a lekérdezések teljesítményére.

Mi a ténytábla ritkasága?

A ténytábla tervezésekor figyelembe kell venni annak ritkaságát – vagyis azt, hogy a tábla sorainak hány sora van kitöltve, és hány üres. Ha a ténytáblákat sok mögöttes táblából töltjük ki, bölcs dolog megbecsülni a ritkaságot. A becslést a ténytábla szemcseszintje alapján végezzük el. Ha a ténytáblánk megegyezik a mögöttes forrástáblával, akkor a ritkaság kiszámításához használhatjuk a mögöttes tábla kardinalitását.

A ritkaság kiszámításához először megkeressük a kitöltési arányt – vagyis a nem üres cellák számát az összes cellához képest. A kitöltési arány kiszámításához ossza el a forrástábla sorainak számát az egyes dimenziós táblák különálló sorainak számával. A ritkaság ezután 1-(f), ahol f = kitöltési arány.

Példa:

Tegyük fel, hogy a fact_retail_sale táblát két forrásból töltjük ki: egy 100 000 soros adatbázis-táblából és egy 20 000 soros táblázatkezelőből. Ezeket a sorokat évente töltjük ki. A dimenziós táblák a következők: dátumok (365 sor), termékek (100 sor) és üzletek (1000 sor).

A ritkaságot a következőképpen számítjuk ki:

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

Ez a ténytábla ritka, mert sorainak kevesebb mint 1%-a tartalmaz nem nulla értéket.

Egy jó ökölszabály az, hogy ami 2% alatt van, azt nagyon ritkának tekintjük; ami fölötte van, az nem annyira ritkának.

Tranzakciós ténytáblák

Ez a ténytáblák legegyszerűbb és leggyakoribb típusa. Ennek a típusnak a szemcséje tranzakciónként egy sor, vagy egy tranzakció soronként egy sor. A tranzakciós ténytábla szemcséje egy térbeli és időbeli pont. A legapróbb üzleti részleteket tárolják.

Amint egy tranzakció megtörténik, a tranzakcióval kapcsolatos átfogó kontextus rögzítésre kerül. Ez a kontextus sok részletet hoz létre a dimenziótáblákban, ezért sok részletre számítunk.

Mihelyt beszúrunk egy sort egy tranzakciótáblába, azt ritkán, vagy egyáltalán nem látogatjuk újra. Ez arra késztet minket, hogy megfontoljuk az ilyen típusú táblák feltöltésének speciális technikáit. A frissítések hiánya sokkal egyszerűbb ETL (extract, transform, and load process) folyamatot jelent.

Pros: A nagyobb granularitás lehetővé teszi a részletes üzleti tevékenységek nyomon követését.

Hátrányok: Teljesítményproblémák a lekérdezésben. Nehézségek a trendszerű viselkedések értelmezésében rögzített időpontokban.

Példa egy tranzakciós ténytáblára

Nézzünk meg egy tipikus pénzügyi csillagsémát:

A ténytáblában minden egyes számlaműveletet rögzítünk. Ennek a táblának a mintaadatai a következők:

Megjegyzendő: a time_date a dim_time dimenzióban látható módon jelenik meg, és a technikai oszlopot figyelmen kívül hagyjuk.

Amint a számlatulajdonos tranzakciókat hajt végre, például pénzt helyez el és vesz fel, minden műveletet rögzítünk.

Ezzel a struktúrával olyan kérdésekre tudunk válaszolni, mint például: Hány tranzakciót dolgoz fel naponta a vállalkozásunk? Mekkora az egy nap alatt átlagosan felvett összeg? stb.

Periodikus pillanatfelvétel ténytáblák

A periodikus pillanatfelvétel táblák az üzlet kumulatív teljesítményét rögzítik előre meghatározott időszakokban. A pillanatfelvételek készítésének előre meghatározott időintervalluma a kulcs: napi, heti, havi stb. Az eredményeket az időszakos pillanatfelvétel ténytáblába mentjük.

Ez a fajta ténytábla nagy mozgásteret biztosít: bármilyen információt beépíthetünk, amely egy adott időszak tevékenységét írja le.

A tranzakcióról az időszakos táblára való áttérés lehetséges. (Az SQL-ben van néhány ügyes algoritmus, amely ezzel foglalkozik, amint azt a következő cikkekben látni fogjuk). Csak össze kell adni a tranzakciókat egyik pillanatfelvételről a másikra. Ahogy a tranzakciós ténytábláknak sok dimenziójuk van, úgy a pillanatfelvételnek általában kevesebb dimenziója van.

Előnyök: Teljesítménynövekedés, az üzlet hosszanti nézete, fizikai tömörség.

Hátrányok: kisebb szemcseméret.

Egy példa az időszakos pillanatfelvétel ténytáblára

Nézzünk egy másik pénzügyi csillagsémát:

Tegyük fel, hogy ezt a ténytáblát és a hozzá tartozó adatmartot ugyanabból a forrásból építettük fel, mint az előző példát. Ezt a ténytáblát akár az előző ténytábla adataiból is felépíthettük volna.

A balance_amount-t minden pillanatfelvétel időpontjában meg kell jelenítenünk; ebben az esetben a pillanatfelvétel szemcsézettsége egy nap. Az összes kimenő tranzakciót napi szinten aggregáljuk, és a befizetés típusa hozzáadás, a kivonás pedig kivonás. Az eredmény:

Megjegyzés: A time_date-t úgy mutatjuk be, ahogyan az a dim_time dimenzióban szerepel, és a technikai oszlopot figyelmen kívül hagyjuk.

Most néhány gyakori üzleti kérdésre tudunk válaszolni, mint például: Hány napig van egy ügyfélnek pozitív számlaegyenlege egy hónapban? Mekkora egy adott ügyféltípus átlagos egyenlege havonta? stb.

Időszakos pillanatfelvétel ténytábla átszervezése

Elképzelhető, hogy észrevett némi redundanciát az előző példában megjelenített adatokban. Néha még a pillanatfelvétel ténytábla is túl nagy, és a teljesítmény zuhanásba kerül. Mi a teendő ilyenkor? Átszervezhetjük a pillanatfelvétel ténytáblánkat valami kompaktabbá.

Elhelyeztünk két új oszlopot, date_from és date_to. Ezek azt az időszakot jelölik, amelyben a balance_amount érvényes volt.

Az adatok ebben a táblázatban így néznének ki:

Minden alkalommal, amikor a balance_amount megváltozik, lezárjuk az időszakot, és nyitunk egy újat.

Megjegyezzük, hogy a date_to oszlop egy nem kizárólagos időhatárolót tartalmaz. Ezt figyelembe kell vennünk az adatok lekérdezésekor. Találunk egy érdekes dátumot is – 31/12/9999. Ez a dátum azt mutatja, hogy az utolsó sor az utolsó ismert egyenleg. Frissíteni fogjuk, amikor az egyenleg megváltozik.

Az alábbiakban láthatjuk az SQL-t, amelyet egy adott időpontban (:date) készült pillanatfelvétel lekérdezéséhez használunk.

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
A lekérdezés első sorában kapjuk vissza a válaszunkat.
A ténytáblák az adattárházak világának húsa. Méretük több terabájtot is elérhet, és a legtöbb helyet foglalják el egy adattárházban. A ténytáblák tervezési folyamatának korai szakaszában elkövetett hiba számos problémát generál, amelyek megoldása a raktár fejlődésével sem lesz könnyebb! 
Az adatokat át lehet szervezni az egyik típusú ténytáblából a másikba. Ennek technikáit egy következő bejegyzésben fogjuk megvizsgálni. Milyen tapasztalatai vannak a különböző típusú ténytáblákkal kapcsolatban? Sikerült valamilyen új és izgalmas módon keverni őket? Ha sikerült, ossza meg velem.