Fakty o faktach: Organizing Fact Tables in Data Warehouse Systems

Proces definiowania systemu hurtowni danych (DWH) został rozpoczęty. Zarysowano odpowiednie tabele wymiarów, które wiążą się z wymaganiami biznesowymi. Tabele te definiują to, co ważymy, obserwujemy i skalujemy. Teraz musimy zdefiniować, jak mierzymy.

Tabele faktów są miejscem, w którym przechowujemy te pomiary. Przechowują one dane biznesowe, które mogą być agregowane w różnych kombinacjach wymiarów. Ale faktem jest, że tabele faktów nie są tak łatwo opisywalne – mają swoje własne smaki. W tym artykule odpowiemy na kilka podstawowych pytań dotyczących tabel faktów, oraz przeanalizujemy wady i zalety każdego z typów.

Czym są tabele faktów?

W najbardziej ogólnym sensie, tabele faktów są miarą procesu biznesowego. Przechowują one głównie dane liczbowe i odpowiadają raczej zdarzeniu niż konkretnemu raportowi.

Najważniejszą cechą tabeli faktów, oprócz miar, jest ziarnistość. Ziarno określa, jaki poziom szczegółowości jest obserwowany dla danego zdarzenia. (Porównaj to z wyrażeniem „kontrola drobnoziarnista”, które oznacza, że użytkownicy mogą kontrolować bardzo małe szczegóły dla swojego konta).

Im bardziej szczegółowa jest tabela faktów, tym lepiej poradzi sobie z nieprzewidywalnymi wymaganiami biznesowymi. Wadą większego ziarna jest to, że więcej miejsca fizycznego jest wymagane do przechowywania danych. Może to również powodować wolniejszą wydajność.

Jak są zbudowane tabele faktów?

Ogólna tabela faktów składa się z dwóch głównych grup atrybutów:

• Klucze obce do tabel wymiarowych
• Wymiary

Klucze obce są oczywiste; zdegenerowane wymiary również należą do tej grupy. Wymiar zdegenerowany jest kluczem wymiaru bez nadrzędnej tabeli wymiarów. Występują one, gdy wszystkie ważne informacje o wymiarze znajdują się już w tabeli faktów. Przykłady obejmują różne numery nagłówków kontrolnych, numery biletów, numery zamówień itp.

Pomiary (tj. metryki lub fakty biznesowe) w tabeli faktów mogą być:

• Addytywne: sumowalne w każdym wymiarze
• Póładdytywne: sumowalne w niektórych wymiarach
• Nieaddytywne: nie sumowalne (np. różne współczynniki)

Poza miarami i kluczami obcymi, może istnieć wiele kolumn technicznych. Kolumny techniczne są przydatne do audytu i niskopoziomowej konserwacji modelu. Timestamps, które są używane do oznaczania, kiedy wstawki lub aktualizacje występują w tabeli faktów, są powszechnym przykładem kolumny technicznej.

Aby zilustrować ogólną tabelę faktów, spójrzmy na bardzo prosty schemat gwiazdy:

W tym modelu mamy pojedynczą tabelę faktów otoczoną przez trzy tabele wymiarów. Nasze klucze obce dla tabeli faktów obejmują:

• time_id – odnosi się do tabeli wymiaru czasu (dim_time)
• product_id – odnosi się do tabeli wymiaru produktu (dim_product)
• store_id – odnosi się do tabeli wymiaru sklepu (dim_store)
• pos_transaction – wymiar zdegenerowany. To są wszystkie potrzebne nam informacje o POS, więc możemy je tutaj przechowywać. Nie ma powodu, aby budować kolejną tabelę wymiarów.

Nasza grupa kluczy miar składa się z:

• sales_quantity – Sprzedaż jednego produktu w jednym czasie w jednym sklepie. Miara ta jest addytywna.
• sales_price – Cena artykułu w postaci sprzedanej w sklepie. Jest to miara nieaddytywna.

Jest jedna kolumna techniczna, time_inserted, która przechowuje czas, kiedy wiersz został wstawiony.

Teraz, gdy mamy już ogólne pojęcie o tabelach faktów, zagłębimy się w ich różne smaki. Istnieją cztery rodzaje tabel faktów: transakcyjne, okresowe, akumulacyjne i bezfaktowe.

Każdy smak służy celowi w reprezentowaniu podstawowego biznesu, który wspiera system hurtowni danych. Jednakże, zanim zagłębimy się w to, co robią te różne tabele faktów, porozmawiajmy o ważnym wspólnym czynniku: rozproszeniu, lub proporcjonalnej ilości danych przechowywanych w tabeli faktów. Ma ona wpływ na wydajność zapytań.

Czym jest rozproszenie danych w tabeli faktów?

Podczas projektowania tabeli faktów, należy rozważyć jej rozproszenie – tzn. jaka liczba wierszy tabeli jest wypełniona, a jaka pusta. Jeśli wypełniamy tabele faktów z wielu tabel bazowych, rozsądnie jest oszacować nieliczność. Oszacowania dokonujemy w oparciu o poziom ziarnistości w tabeli faktów. Jeśli twoja tabela faktów pasuje do bazowej tabeli źródłowej, możesz użyć kardynalności bazowej tabeli do obliczenia nieliczbowości.

Aby obliczyć nieliczbowość, najpierw znajdujemy współczynnik wypełnienia – to jest liczbę niepustych komórek do całkowitej liczby komórek. Aby obliczyć współczynnik wypełnienia, podziel liczbę wierszy w tabeli źródłowej przez liczbę odrębnych wierszy w każdej tabeli wymiarowej. Rozproszenie jest następnie obliczane jako 1-(f), gdzie f = współczynnik wypełnienia.

Przykład:

Załóżmy, że wypełniamy tabelę fact_retail_sale z dwóch źródeł: tabeli bazy danych zawierającej 100 000 wierszy i arkusza kalkulacyjnego zawierającego 20 000 wierszy. Wiersze te są wypełniane w cyklu rocznym. Tabele wymiarów to: daty (365 wierszy), produkty (100 wierszy) i sklepy (1 000 wierszy).

Obliczamy nieliczność jako:

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

Ta tabela faktów jest nieliczna, ponieważ mniej niż 1% jej wierszy ma wartości niezerowe.

Dobrą zasadą jest to, że wszystko poniżej 2% uważamy za bardzo rzadkie; wszystko powyżej tego poziomu nie jest tak rzadkie.

Transakcyjne tabele faktów

Jest to najprostszy i najbardziej powszechny typ tabeli faktów. Ziarno tego typu to jeden wiersz na transakcję lub jeden wiersz na wiersz transakcji. Ziarno tabeli faktów transakcji jest punktem w czasie i przestrzeni. Przechowują one najmniejsze szczegóły biznesowe.

Jak transakcja ma miejsce, obszerny kontekst o niej jest przechwytywany. Kontekst ten tworzy wiele szczegółów w tabelach wymiarów, więc oczekujemy, że będzie ich dużo.

Po wstawieniu wiersza do tabeli transakcji jest on rzadko, jeśli w ogóle, ponownie odwiedzany. To prowadzi nas do rozważenia konkretnych technik wypełniania tego typu tabel. Brak aktualizacji oznacza znacznie prostszy proces ETL (extract, transform, and load).

Pros: Wyższa granularność umożliwia monitorowanie szczegółowych działań biznesowych.

Konsekwencje: Problemy z wydajnością w zapytaniach. Trudność w interpretacji trendów w stałych punktach czasowych.

Przykład tabeli faktów transakcji

Przyjrzyjrzyjmy się typowemu schematowi gwiazdy finansowej:

W tabeli faktów zapisujemy każdą pojedynczą operację na koncie. Przykładowe dane z tej tabeli obejmują:

Uwaga: time_date jest reprezentowany tak, jak jest pokazany w wymiarze dim_time, a kolumna techniczna jest ignorowana.

Jak właściciel konta wykonuje transakcje, takie jak wpłacanie i wypłacanie pieniędzy, rejestrujemy każdą akcję.

Dzięki tej strukturze możemy odpowiedzieć na pytania takie jak: Ile transakcji dziennie przetwarza nasza firma? Określony interwał wykonywania zrzutów jest kluczowy: dzienny, tygodniowy, miesięczny, itd. Wyniki są zapisywane w okresowej tabeli faktów.

Ten typ tabeli faktów daje nam dużo swobody: możemy włączyć dowolną informację, która opisuje aktywność w danym okresie czasu.

Przejście od transakcji do tabeli okresowej jest możliwe. (Istnieje kilka zgrabnych algorytmów w SQL, które sobie z tym radzą, jak zobaczymy w kolejnych artykułach). Jest to tylko kwestia sumowania transakcji z jednej migawki do drugiej. Tak jak tabele faktów transakcji mają wiele wymiarów, migawki mają zwykle mniej wymiarów.

Zalety: Wzrost wydajności, podłużny widok biznesu, fizyczna zwartość.

Konsekwencje: Niższa ziarnistość.

Przykład okresowej tabeli faktów Snapshot

Rozważmy inny schemat gwiazdy finansowej:

Załóżmy, że zbudowaliśmy tę tabelę faktów i jej data mart z tego samego źródła, co poprzedni przykład. Mogliśmy nawet zbudować tę tabelę faktów z danych w poprzedniej tabeli faktów.

Potrzebujemy pokazać balance_amount w każdej dacie migawki; w tym przypadku, granulacja migawki jest jednodniowa. Agregujemy wszystkie transakcje wychodzące na bazie dziennej, przy czym typ wpłaty to dodawanie, a wypłaty to odejmowanie. Wynik to:

Uwaga: Przedstawiamy time_date tak, jak jest to pokazane w wymiarze dim_time, a kolumna techniczna jest ignorowana.

Teraz możemy odpowiedzieć na kilka typowych pytań biznesowych, takich jak: Przez ile dni w miesiącu klient ma dodatnie saldo na koncie? Jakie jest średnie saldo określonego typu klienta w każdym miesiącu? itp.

Reorganizacja okresowej migawkowej tabeli faktów

Możesz zauważyć pewną redundancję w danych wyświetlanych w poprzednim przykładzie. Czasami nawet tabela faktów migawkowych jest zbyt duża i wydajność spada. Co wtedy zrobić? Możemy zreorganizować naszą tabelę faktów migawkowych w coś bardziej kompaktowego.

Dodaliśmy dwie nowe kolumny, date_from i date_to. Reprezentują one okres czasu, w którym balance_amount był ważny.

Dane w tej tabeli wyglądałyby następująco:

Za każdym razem, gdy zmienia się balance_amount, zamykamy okres czasu i otwieramy nowy.

Zauważ, że kolumna date_to zawiera delimiter czasowy nie zawierający informacji. Musimy wziąć to pod uwagę przy wyszukiwaniu danych. Znajdujemy również jedną ciekawą datę – 31/12/9999. Data ta pokazuje, że ostatni wiersz jest ostatnim znanym saldem. Zaktualizujemy go, gdy saldo ulegnie zmianie.

Poniżej możemy zobaczyć SQL, którego używamy do zapytania o migawkę w konkretnej dacie (: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
Nasza odpowiedź jest zwracana w pierwszej linii zapytania.
Tabele faktów są mięsem świata hurtowni danych. Ich rozmiar może wynosić wiele terabajtów i zajmują one najwięcej miejsca w hurtowni danych. Błąd popełniony na wczesnym etapie projektowania tabeli faktów generuje wiele problemów, które nie stają się łatwiejsze do rozwiązania w miarę rozwoju hurtowni! 
Możliwa jest reorganizacja danych z jednego typu tabeli faktów do innego. Zbadamy techniki, aby to zrobić w nadchodzącym poście. Jakie są Twoje doświadczenia z różnymi typami tabel faktów? Czy udało Ci się je połączyć w jakiś nowy i ekscytujący sposób? Jeśli tak, daj mi znać.