Fakten über Fakten: Organisation von Faktentabellen in Data-Warehouse-Systemen

Der Prozess der Definition Ihres Data-Warehousing-Systems (DWH) hat begonnen. Sie haben die relevanten Dimensionstabellen skizziert, die mit den Geschäftsanforderungen verknüpft sind. Diese Tabellen definieren, was wir wiegen, beobachten und skalieren. Jetzt müssen wir definieren, wie wir messen.

In den Faktentabellen speichern wir diese Messungen. Sie enthalten Geschäftsdaten, die über Dimensionskombinationen hinweg aggregiert werden können. Fakt ist jedoch, dass Faktentabellen nicht so einfach zu beschreiben sind – sie haben ihren eigenen Geschmack. In diesem Artikel werden wir einige grundlegende Fragen zu Faktentabellen beantworten und die Vor- und Nachteile der einzelnen Typen untersuchen.

Was sind Faktentabellen?

Im allgemeinsten Sinne sind Faktentabellen die Messungen eines Geschäftsprozesses. Sie enthalten meist numerische Daten und entsprechen eher einem Ereignis als einem bestimmten Bericht.

Das wichtigste Merkmal einer Faktentabelle, neben den Messwerten, ist die Auflösung. Sie legt fest, welcher Detailgrad für ein bestimmtes Ereignis beobachtet wird. (Man vergleiche dies mit dem Ausdruck „fine-grain controls“, was bedeutet, dass Benutzer sehr kleine Details für ihr Konto kontrollieren können.)

Je detaillierter die Faktentabelle ist, desto besser kann sie mit unvorhersehbaren Geschäftsanforderungen umgehen. Der Nachteil einer größeren Auflösung ist, dass mehr physischer Platz für die Datenspeicherung benötigt wird. Es kann auch zu einer langsameren Leistung führen.

Wie sind Faktentabellen strukturiert?

Eine allgemeine Faktentabelle besteht aus zwei Hauptattributgruppen:

• Fremdschlüssel zu Dimensionstabellen
• Maße

Fremdschlüssel sind selbsterklärend; degenerierte Dimensionen gehören ebenfalls zu dieser Gruppe. Eine degenerierte Dimension ist ein Dimensionsschlüssel, der keine übergeordnete Dimensionstabelle hat. Sie treten auf, wenn alle wichtigen Informationen über die Dimension bereits in der Faktentabelle enthalten sind. Beispiele sind verschiedene Kontrollkopfnummern, Ticketnummern, Auftragsnummern usw.

Maßnahmen (d.h. Metriken oder Business Facts) in einer Faktentabelle können sein:

• Additiv: summierbar über jede Dimension
• Semi-additiv: summierbar über einige Dimensionen
• Nicht-additiv: nicht summierbar (z.B. verschiedene Kennzahlen)

Neben Kennzahlen und Fremdschlüsseln kann es viele technische Spalten geben. Technische Spalten sind nützlich für die Prüfung und Wartung des Modells auf niedriger Ebene. Zeitstempel, die verwendet werden, um zu markieren, wann Einfügungen oder Aktualisierungen in der Faktentabelle auftreten, sind ein gängiges Beispiel für eine technische Spalte.

Um eine allgemeine Faktentabelle zu veranschaulichen, betrachten wir ein sehr einfaches Sternschema:

In diesem Modell haben wir eine einzelne Faktentabelle, die von drei Dimensionstabellen umgeben ist. Unsere Fremdschlüssel für die Faktentabelle umfassen:

• time_id – bezieht sich auf die Zeitdimensionstabelle (dim_time)
• product_id – bezieht sich auf die Produktdimensionstabelle (dim_product)
• store_id – bezieht sich auf die Filialdimensionstabelle (dim_store)
• pos_transaction – eine degenerierte Dimension. Dies sind alle Informationen über POS, die wir brauchen, also können wir sie hier speichern. Es gibt keinen Grund, eine weitere Dimensionstabelle zu erstellen.

Unsere Maßnahmen-Schlüsselgruppe besteht aus:

• sales_quantity – Der Verkauf eines Produkts zu einem Zeitpunkt in einem Geschäft. Diese Maßnahme ist additiv.
• sales_price – Der Preis eines Artikels, wie er in der Filiale verkauft wird. Dieses Maß ist nicht additiv.

Es gibt eine technische Spalte, time_inserted, die den Zeitpunkt des Einfügens einer Zeile speichert.

Nachdem wir nun eine allgemeine Vorstellung von Faktentabellen haben, werden wir uns mit ihren verschiedenen Varianten beschäftigen. Es gibt vier Arten von Faktentabellen: Transaktionstabellen, periodische Snapshot-Tabellen, akkumulierende Snapshot-Tabellen und faktenlose Faktentabellen.

Jede Variante erfüllt einen bestimmten Zweck bei der Darstellung des zugrunde liegenden Geschäfts, das das Data Warehousing-System unterstützt. Bevor wir uns jedoch damit befassen, was diese verschiedenen Faktentabellen tun, sollten wir über einen wichtigen gemeinsamen Faktor sprechen: Sparsamkeit oder die proportionale Menge der in einer Faktentabelle gespeicherten Daten. Sparsity steht im Zusammenhang mit der Maserung und wirkt sich auf die Abfrageleistung aus.

Was ist die Sparsity von Faktentabellen?

Beim Entwurf einer Faktentabelle ist die Sparsity zu berücksichtigen, d. h. wie viele Zeilen der Tabelle gefüllt sind und wie viele leer sind. Wenn wir die Faktentabellen aus vielen zugrundeliegenden Tabellen füllen, ist es ratsam, die Sparsamkeit zu schätzen. Diese Schätzung basiert auf dem Grad der Maserung der Faktentabelle. Wenn Ihre Faktentabelle mit der zugrundeliegenden Quelltabelle übereinstimmt, können Sie die Kardinalität der zugrundeliegenden Tabelle verwenden, um die Sparsamkeit zu berechnen.

Um die Sparsamkeit zu berechnen, ermitteln wir zunächst den Füllungsgrad, d. h. die Anzahl der nicht leeren Zellen im Verhältnis zur Gesamtzahl der Zellen. Um den Füllungsgrad zu ermitteln, dividieren Sie die Anzahl der Zeilen in der Quelltabelle durch die Anzahl der eindeutigen Zeilen in jeder Dimensionstabelle. Die Sparsamkeit wird dann als 1-(f) berechnet, wobei f = Füllungsgrad ist.

Beispiel:

Angenommen, wir füllen die Tabelle fact_retail_sale aus zwei Quellen: einer Datenbanktabelle mit 100.000 Zeilen und einer Tabellenkalkulation mit 20.000 Zeilen. Diese Zeilen werden auf jährlicher Basis gefüllt. Die Dimensionstabellen sind: Daten (365 Zeilen), Produkte (100 Zeilen) und Geschäfte (1.000 Zeilen).

Wir berechnen die Sparsamkeit wie folgt:

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

Diese Faktentabelle ist spärlich, weil weniger als 1 % ihrer Zeilen Nicht-Null-Werte haben.

Eine gute Faustregel besagt, dass wir alles unter 2 % als sehr spärlich betrachten; alles darüber ist nicht so spärlich.

Transaktionsfaktentabellen

Dies ist der einfachste und häufigste Typ von Faktentabellen. Die Struktur dieses Typs ist eine Zeile pro Transaktion oder eine Zeile pro Zeile in einer Transaktion. Die Größe einer Transaktionsfaktentabelle ist ein Punkt in Raum und Zeit. Sie enthalten die kleinsten geschäftlichen Details.

Wenn eine Transaktion stattfindet, wird ein umfangreicher Kontext über sie erfasst. Dieser Kontext erzeugt viele Details in den Dimensionstabellen, so dass wir viele davon erwarten.

Wenn wir eine Zeile in eine Transaktionstabelle einfügen, wird sie selten, wenn überhaupt, wieder aufgerufen. Dies veranlasst uns, spezielle Techniken für das Auffüllen dieser Art von Tabellen in Betracht zu ziehen. Keine Aktualisierungen bedeuten einen viel einfacheren ETL-Prozess (Extrahieren, Transformieren und Laden).

Profis: Höhere Granularität ermöglicht die Überwachung detaillierter Geschäftsaktivitäten.

Nachteil: Leistungsprobleme bei Abfragen. Schwierigkeiten bei der Interpretation von Trendverhalten zu festen Zeitpunkten.

Ein Beispiel für eine Transaktionsfaktentabelle

Werfen wir einen Blick auf ein typisches Financial-Star-Schema:

Wir zeichnen jede einzelne Kontobewegung in der Faktentabelle auf. Beispieldaten aus dieser Tabelle umfassen:

Anmerkung: time_date wird so dargestellt, wie es in der Dimension dim_time gezeigt wird, und die technische Spalte wird ignoriert.

Wenn der Kontoinhaber Transaktionen durchführt, wie z. B. Einzahlungen und Abhebungen, zeichnen wir jede Aktion auf.

Mit dieser Struktur können wir Fragen beantworten wie: Wie viele Transaktionen verarbeitet unser Unternehmen pro Tag? Wie hoch ist der durchschnittliche Betrag, der an einem Tag abgehoben wird? usw.

Periodische Snapshot-Faktentabellen

Periodische Snapshot-Tabellen zeichnen die kumulierte Leistung des Unternehmens in vordefinierten Zeiträumen auf. Der Schlüssel dazu ist ein vorher festgelegtes Intervall für die Erstellung von Momentaufnahmen: täglich, wöchentlich, monatlich, usw. Die Ergebnisse werden in der Faktentabelle des periodischen Schnappschusses gespeichert.

Diese Art von Faktentabelle gibt uns viel Spielraum: Wir können jede Information einbeziehen, die die Aktivität über einen bestimmten Zeitraum beschreibt.

Es ist möglich, von einer Transaktion zu einer periodischen Tabelle überzugehen. (In SQL gibt es einige nette Algorithmen, die sich damit befassen, wie wir in den nächsten Artikeln sehen werden.) Es geht nur darum, die Transaktionen von einem Snapshot zu einem anderen zu addieren. So wie Transaktionsfaktentabellen viele Dimensionen haben, hat der Snapshot in der Regel weniger Dimensionen.

Pros: Leistungsgewinn, Längsschnittansicht des Geschäfts, physische Kompaktheit.

Nachteil: Geringere Körnung.

Ein Beispiel für eine periodische Snapshot-Faktentabelle

Betrachten wir ein anderes Finanz-Star-Schema:

Angenommen, wir haben diese Faktentabelle und ihren Data Mart aus derselben Quelle wie im vorherigen Beispiel erstellt. Wir hätten diese Faktentabelle sogar aus den Daten der vorherigen Faktentabelle erstellen können.

Wir müssen die balance_amount an jedem Snapshot-Datum anzeigen; in diesem Fall ist die Snapshot-Granularität ein Tag. Wir aggregieren alle ausgehenden Transaktionen auf täglicher Basis, und die Einzahlungsart ist addierend und die Auszahlungsart ist subtrahierend. Das Ergebnis lautet:

Anmerkung: Wir stellen time_date so dar, wie es in der Dimension dim_time gezeigt wird, und die technische Spalte wird ignoriert.

Jetzt können wir einige allgemeine Geschäftsfragen beantworten, z. B.: Wie viele Tage hat ein Kunde in einem Monat einen positiven Kontostand? Wie hoch ist der durchschnittliche Saldo eines bestimmten Kundentyps pro Monat? usw.

Reorganisation einer periodischen Snapshot-Faktentabelle

Sie haben vielleicht eine gewisse Redundanz in den im vorherigen Beispiel angezeigten Daten festgestellt. Manchmal ist sogar die Snapshot-Faktentabelle zu groß und die Leistung leidet darunter. Was ist dann zu tun? Wir können unsere Snapshot-Faktentabelle in etwas Kompakteres umorganisieren.

Wir haben zwei neue Spalten hinzugefügt, date_from und date_to. Sie stellen den Zeitraum dar, in dem die balance_amount gültig war.

Die Daten in dieser Tabelle würden wie folgt aussehen:

Jedes Mal, wenn sich die balance_amount ändert, schließen wir den Zeitraum und öffnen einen neuen.

Beachten Sie, dass die Spalte date_to ein nicht-einschließendes Zeittrennzeichen enthält. Dies müssen wir bei der Datenabfrage berücksichtigen. Wir finden auch ein interessantes Datum – 31/12/9999. Dieses Datum zeigt, dass die letzte Zeile der letzte bekannte Saldo ist. Wir werden sie aktualisieren, wenn sich der Saldo ändert.

Unten sehen wir das SQL, das wir verwenden, um einen Schnappschuss zu einem bestimmten Datum (:date) abzufragen.

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
Unsere Antwort wird in der ersten Zeile der Abfrage zurückgegeben.
Faktentabellen sind das Fleisch der Data-Warehousing-Welt. Ihre Größe kann viele Terabytes betragen, und sie nehmen den meisten Platz in einem Data Warehouse ein. Ein Fehler zu Beginn des Entwurfsprozesses von Faktentabellen führt zu vielen Problemen, die im Laufe der Entwicklung des Warehouse nicht einfacher zu lösen sind! 
Es ist möglich, Daten von einer Art von Faktentabelle in eine andere umzuorganisieren. Wir werden uns in einem der nächsten Beiträge mit den entsprechenden Techniken befassen. Wie sind Ihre Erfahrungen mit verschiedenen Arten von Faktentabellen? Ist es Ihnen gelungen, sie auf eine neue und aufregende Weise zu mischen? Wenn ja, lassen Sie es mich wissen.