Facts about Facts: Organizing Fact Tables in Data Warehouse Systems

Het proces van het definiëren van uw data warehousing systeem (DWH) is begonnen. U hebt de relevante dimensietabellen geschetst, die gekoppeld zijn aan de bedrijfsvereisten. Deze tabellen definiëren wat we wegen, observeren en schalen. Nu moeten we definiëren hoe we meten.

Fact tabellen zijn waar we deze metingen opslaan. Zij bevatten bedrijfsgegevens die kunnen worden geaggregeerd over dimensiecombinaties. Feit is echter dat feitentabellen niet zo gemakkelijk te beschrijven zijn – ze hebben hun eigen smaak. In dit artikel beantwoorden we een aantal basisvragen over feitentabellen, en bekijken we de voor- en nadelen van elk type.

Wat zijn feitentabellen?

In de meest algemene zin zijn feitentabellen de metingen van een bedrijfsproces. Ze bevatten meestal numerieke gegevens en komen eerder overeen met een gebeurtenis dan met een bepaald rapport.

Het belangrijkste kenmerk van een feitentabel, naast de metingen, is de korrel. De korrel bepaalt welke mate van detail wordt waargenomen voor een bepaalde gebeurtenis. (Vergelijk dit met de uitdrukking “fine-grain controls”, waarmee wordt bedoeld dat gebruikers zeer kleine details voor hun rekening kunnen controleren).

Hoe gedetailleerder de korrel van de feitentabel, des te beter kan deze omgaan met onvoorspelbare zakelijke eisen. Het nadeel van een grotere korrel is dat er meer fysieke ruimte nodig is voor de opslag van gegevens. Het kan ook leiden tot tragere prestaties.

Hoe zijn feitentabellen gestructureerd?

Een algemene feitentabel bestaat uit twee hoofdgroepen van attributen:

• Voormalige sleutels tot dimensionale tabellen
• Maten

Voormalige sleutels spreken voor zich; degenerate dimensies behoren ook tot deze groep. Een gedegenereerde dimensie is een dimensiesleutel zonder bovenliggende dimensietabel. Ze komen voor wanneer alle belangrijke informatie over de dimensie al in de feitentabel staat. Voorbeelden hiervan zijn diverse control header nummers, ticket nummers, order nummers, etc.

Maten (d.w.z. metrics of business facts) in een fact table kunnen zijn:

• Additief: optelbaar over elke dimensie
• Semi-additief: optelbaar over sommige dimensies
• Niet-additief: niet optelbaar (b.v. diverse verhoudingen)

Naast maatregelen en foreign keys, kunnen er vele technische kolommen zijn. Technische kolommen zijn nuttig voor auditing en onderhoud op laag niveau van het model. Tijdstempels, die worden gebruikt om aan te geven wanneer invoegingen of updates in de feitentabel plaatsvinden, zijn een veel voorkomend voorbeeld van een technische kolom.

Om een algemene feitentabel te illustreren, bekijken we een heel eenvoudig sterschema:

In dit model hebben we een enkele feitentabel, omgeven door drie dimensie-tabellen. Onze foreign keys voor de feitentabel zijn:

• time_id – verwijst naar de tijddimensietabel (dim_time)
• product_id – verwijst naar de productdimensietabel (dim_product)
• store_id – verwijst naar de winkeldimensietabel (dim_store)
• pos_transaction – een gedegenereerde dimensie. Dit is alle informatie over POS die we nodig hebben, dus kunnen we die hier opslaan. Er is geen reden om nog een dimensietabel te bouwen.

Onze maatstafsleutelgroep bestaat uit:

• sales_quantity – De verkoop van één product op één moment in één winkel. Deze maatstaf is additief.
• sales_price – De prijs van een artikel zoals verkocht in de winkel. Dit is een niet-additieve maatstaf.

Er is één technische kolom, time_inserted, die het tijdstip opslaat waarop een rij wordt ingevoegd.

Nu we een algemeen idee hebben van feitentabellen, zullen we ons gaan verdiepen in de verschillende smaken. Er zijn vier soorten feitentabellen: transactie-, periodieke snapshot-, accumulerende snapshot- en factless feitentabellen.

Elke smaak dient een doel in het vertegenwoordigen van de onderliggende business die het data warehousing systeem ondersteunt. Voordat we echter ingaan op wat deze verschillende feitentabellen doen, moeten we het hebben over een belangrijke gemeenschappelijke factor: sparsity, oftewel de proportionele hoeveelheid gegevens die in een feitentabel is opgeslagen. Sparsity heeft te maken met korrelgrootte en is van invloed op de prestaties van query’s.

What Is Fact Table Sparsity?

Bij het ontwerpen van een fact table moet u rekening houden met de sparsity – d.w.z. hoeveel van de rijen van de tabel gevuld zijn versus hoeveel er leeg zijn. Als we de feitentabellen vullen vanuit vele onderliggende tabellen, is het verstandig om de sparsity in te schatten. We maken de schatting op basis van de mate van korreligheid in de feitentabel. Als uw feitentabel overeenkomt met de onderliggende brontabel, kunt u de cardinaliteit van de onderliggende tabel gebruiken om de sparsity te berekenen.

Om de sparsity te berekenen, vinden we eerst de fill ratio – dat wil zeggen, het aantal niet-lege cellen ten opzichte van het totale aantal cellen. Om de fill ratio te berekenen deelt men het aantal rijen in de brontabel door het aantal afzonderlijke rijen in elke dimensie-tabel. De sparsity wordt dan berekend als 1-(f), waarbij f = fill ratio.

Voorbeeld:

Stel dat wij de tabel fact_retail_sale vullen uit twee bronnen: een databasetabel met 100.000 rijen en een spreadsheet met 20.000 rijen. Deze rijen worden op jaarbasis gevuld. De dimensietabellen zijn: data (365 rijen), producten (100 rijen) en winkels (1.000 rijen).

Wij berekenen de sparsity als:

1-(100.000 + 20.000)/(365*100*1.000) = 0,99671

Deze feitentabel is sparse, omdat minder dan 1% van de rijen waarden heeft die niet nul zijn.

Een goede vuistregel is dat we alles onder de 2% als zeer schaars beschouwen; alles daarboven is niet zo schaars.

Transaction Fact Tables

Dit is het eenvoudigste en meest voorkomende type feitentabel. De korrel van dit type is één rij per transactie, of één rij per regel op een transactie. De korrel van een transactie feitentabel is een punt in ruimte en tijd. Ze bevatten de kleinste zakelijke details.

Als een transactie plaatsvindt, wordt er een uitgebreide context over vastgelegd.

Als een rij eenmaal in een transactietabel is ingevoerd, wordt deze zelden of nooit meer geraadpleegd. Dit brengt ons ertoe na te denken over specifieke technieken om dit soort tabellen te vullen. Geen updates betekent een veel eenvoudiger ETL-proces (extract, transform, and load).

Pros: Hogere granulariteit maakt monitoring van gedetailleerde bedrijfsactiviteiten mogelijk.

Cons: Prestatieproblemen bij query’s.

Een voorbeeld van een transactiefactuurtabel

Laten we eens kijken naar een typisch financieel sterschema:

Wij leggen elke afzonderlijke rekeningactie vast in de fact-tabel. Voorbeeldgegevens uit deze tabel zijn:

Noot: time_date wordt weergegeven zoals het wordt weergegeven in de dimensie dim_time, en de technische kolom wordt genegeerd.

Als de rekeninghouder transacties voltooit, zoals het storten en opnemen van geld, registreren wij elke actie.

Met deze structuur kunnen wij vragen beantwoorden als: Hoeveel transacties per dag verwerkt ons bedrijf?

Periodic Snapshot Fact Tables

Periodic snapshot tables leggen de cumulatieve prestaties van het bedrijf vast in vooraf bepaalde perioden. Een vooraf bepaald interval voor het nemen van snapshots is de sleutel: dagelijks, wekelijks, maandelijks, enz. De resultaten worden opgeslagen in de feitentabel van de periodieke momentopname.

Dit type feitentabel geeft ons veel speelruimte: wij kunnen alle informatie opnemen die de activiteit over een bepaalde periode beschrijft.

Het is mogelijk om van een transactie naar een periodieke tabel over te gaan. (Er zijn enkele nette algoritmen in SQL die hiermee omgaan, zoals we in komende artikelen zullen zien). Het is slechts een kwestie van het optellen van de transacties van de ene momentopname naar de andere. Net zoals transactie feitentabellen veel dimensies hebben, heeft de momentopname meestal minder dimensies.

Pros: Prestatiewinst, longitudinaal overzicht van de business, fysieke compactheid.

Cons: Lagere korrel.

Een voorbeeld van een periodieke snapshot-facttabel

Laten we eens een ander financieel sterschema bekijken:

Laten we aannemen dat we deze fact table en de bijbehorende data mart uit dezelfde bron hebben gebouwd als het vorige voorbeeld. We hadden deze feitentabel zelfs kunnen bouwen op basis van de gegevens in de vorige feitentabel.

We moeten de balance_amount op elke momentopnamedatum laten zien; in dit geval is de momentopnamegranulariteit één dag. Wij aggregeren alle uitgaande transacties op een dagelijkse basis, en het stortingstype is optellen en opnemen is aftrekken. Het resultaat is:

Noot: Wij presenteren time_date zoals het wordt weergegeven in de dimensie dim_time en de technische kolom wordt genegeerd.

Nu kunnen wij een aantal veel voorkomende zakelijke vragen beantwoorden, zoals: Hoeveel dagen heeft een klant een positief rekeningsaldo in een maand? Wat is het gemiddelde saldo van een bepaald type klant per maand?

Herschikking van een periodieke snapshot-facttabel

Het is u misschien opgevallen dat de gegevens in het vorige voorbeeld redundant zijn weergegeven. Soms is zelfs de snapshot feitentabel te groot en neemt de performance een duik. Wat moeten we dan doen? We kunnen onze snapshot feitentabel reorganiseren tot iets compacters.

We hebben twee nieuwe kolommen toegevoegd, date_from en date_to. Zij geven de periode aan waarin de balance_amount geldig was.

De gegevens in deze tabel zouden er als volgt uitzien:

Telkens wanneer de balance_amount verandert, sluiten wij de tijdsperiode en openen wij een nieuwe.

Merk op dat de kolom date_to een niet-inclusief tijdsbegrenzingsteken bevat. We moeten hier rekening mee houden wanneer we gegevens opvragen. We vinden ook een interessante datum – 31/12/9999. Deze datum toont aan dat de laatste rij het laatst bekende saldo is. Wij zullen deze bijwerken wanneer het saldo verandert.

Hieronder ziet u de SQL die wij gebruiken om een snapshot op een bepaalde datum (:date) op te vragen.

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
Het antwoord staat in de eerste regel van de query.
Facttabellen zijn het vlees van de data warehousing-wereld. Ze kunnen vele terabytes groot zijn en nemen de meeste ruimte in een datawarehouse in beslag. Een fout in het begin van het ontwerpproces van de feitentabel leidt tot veel problemen, die niet eenvoudiger op te lossen zijn naarmate het magazijn zich verder ontwikkelt! 
Het is mogelijk om gegevens te reorganiseren van het ene type feitentabel naar het andere. In een volgend bericht zullen we technieken hiervoor onderzoeken. Wat zijn uw ervaringen met verschillende soorten feitentabellen? Is het je gelukt om ze te mixen op een nieuwe en opwindende manier? Als dat gelukt is, laat het me weten.