Fakta om fakta: Organisering av faktatabeller i datalagringssystem

Processen med att definiera ditt datalagringssystem (DWH) har påbörjats. Du har beskrivit de relevanta dimensionstabellerna, som är kopplade till verksamhetskraven. Dessa tabeller definierar vad vi väger, observerar och skalar. Nu måste vi definiera hur vi mäter.

Fakttabeller är den plats där vi lagrar dessa mätningar. De innehåller affärsdata som kan aggregeras över dimensionskombinationer. Men faktum är att faktatabeller inte är så lätta att beskriva – de har egna smaker. I den här artikeln besvarar vi några grundläggande frågor om faktatabeller och undersöker för- och nackdelar med varje typ.

Vad är faktatabeller?

I den mest allmänna bemärkelsen är faktatabeller mätningar av en affärsprocess. De innehåller mestadels numeriska data och motsvarar en händelse snarare än en viss rapport.

Den viktigaste egenskapen hos en faktatabell, förutom mått, är korn. Grain definierar vilken detaljnivå som observeras för en viss händelse. (Jämför detta med uttrycket ”fine-grain controls”, som innebär att användarna kan kontrollera mycket små detaljer för sitt konto).

Desto mer detaljerade faktatabellens korn är, desto bättre kommer den att hantera oförutsägbara verksamhetskrav. Nackdelen med större korn är att det krävs mer fysiskt utrymme för datalagring. Det kan också leda till långsammare prestanda.

Hur är faktatabeller strukturerade?

En allmän faktatabell består av två huvudsakliga attributgrupper:

• Förändiga nycklar till dimensionstabeller
• Mått

Förändiga nycklar är självförklarande, degenererade dimensioner hör också till denna grupp. En degenererad dimension är en dimensionsnyckel som inte har någon överordnad dimensionstabell. De förekommer när all viktig information om dimensionen redan finns i faktatabellen. Exempel på detta är olika kontrollnummer, biljettnummer, ordernummer osv.

Mått (dvs. mätvärden eller affärsfakta) i en faktatabell kan vara:

• Additiva: kan summeras över alla dimensioner
• Semi-additiva: kan summeras över vissa dimensioner
• Icke-additiva: kan inte summeras (t.ex. olika nyckeltal)

Förutom mått och främmande nycklar kan det finnas många tekniska kolumner. Tekniska kolumner är användbara för revision och underhåll av modellen på låg nivå. Tidsstämplar, som används för att markera när insättningar eller uppdateringar sker i faktatabellen, är ett vanligt exempel på en teknisk kolumn.

För att illustrera en generisk faktatabell tittar vi på ett mycket enkelt stjärnschema:

I den här modellen har vi en enda faktatabell omgiven av tre dimensionstabeller. Våra främmande nycklar för faktatabellen omfattar:

• time_id – hänvisar till tidsdimensionstabellen (dim_time)
• product_id – hänvisar till produktdimensionstabellen (dim_product)
• store_id – hänvisar till butiksdimensionstabellen (dim_store)
• pos_transaction – en degenererad dimension. Detta är all information om POS som vi behöver, så vi kan lagra den här. Det finns ingen anledning att bygga ytterligare en dimensionstabell.

Vår mätnyckelgrupp består av:

• sales_quantity – Försäljningen av en produkt vid ett tillfälle i en butik. Detta mått är additivt.
• sales_price – Priset på en produkt som säljs i butiken. Detta är ett icke additivt mått.

Det finns en teknisk kolumn, time_inserted, som lagrar tiden när en rad infogas.

När vi nu har en allmän uppfattning om faktatabeller ska vi gräva i deras olika varianter. Det finns fyra typer av faktatabeller: transaktionstabeller, periodiska ögonblicksbilder, ackumulerande ögonblicksbilder och faktalösa faktatabeller.

Varje smak tjänar ett syfte när det gäller att representera den underliggande verksamheten som datalagringssystemet stöder. Men innan vi fördjupar oss i vad dessa olika faktatabeller gör, låt oss tala om en viktig gemensam faktor: sparsamhet, eller den proportionella mängden data som lagras i en faktatabell. Sparsamhet är relaterat till korn och har en effekt på frågeprestanda.

Vad är faktatabellens sparsamhet?

När du utformar en faktatabell bör du ta hänsyn till dess sparsamhet, dvs. hur många av tabellens rader som är fyllda jämfört med hur många som är tomma. Om vi fyller faktatabellerna från många underliggande tabeller är det klokt att uppskatta sparsamheten. Vi gör uppskattningen på grundval av graden av korn i faktatabellen. Om din faktatabell matchar den underliggande källtabellen kan du använda den underliggande tabellens kardinalitet för att beräkna sparsamhet.

För att beräkna sparsamhet hittar vi först fyllnadsgraden – det vill säga antalet icke-tomma celler i förhållande till det totala antalet celler. För att beräkna fyllnadsgraden dividerar du antalet rader i källtabellen med antalet distinkta rader i varje dimensionstabell. Sparsamhet beräknas sedan som 1-(f), där f = fyllnadsgrad.

Exempel:

Antag att vi fyller tabellen fact_retail_sale från två källor: en databastabell med 100 000 rader och ett kalkylblad med 20 000 rader. Dessa rader fylls på årsbasis. Dimensionstabellerna är: datum (365 rader), produkter (100 rader) och butiker (1 000 rader).

Vi beräknar glesheten på följande sätt:

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

Denna faktatabell är gles eftersom mindre än 1 % av dess rader har värden som inte är noll.

En bra tumregel är att vi anser att allt under 2 % är mycket sparsamt; allt över det är inte så sparsamt.

Transaktionsfaktatabeller

Detta är den enklaste och vanligaste typen av faktatabell. Kornet i denna typ är en rad per transaktion, eller en rad per rad på en transaktion. En transaktionstabell är en punkt i tid och rum. De innehåller de minsta affärsdetaljerna.

När en transaktion äger rum, registreras ett omfattande sammanhang om den. Detta sammanhang skapar många detaljer i dimensionstabellerna, så vi förväntar oss en hel del av dem.

När vi väl har lagt in en rad i en transaktionstabell återbesöks den sällan eller aldrig. Detta får oss att överväga särskilda tekniker för att fylla dessa typer av tabeller. Inga uppdateringar innebär en mycket enklare ETL-process (extrahera, omvandla och ladda).

Pros: Högre granularitet gör det möjligt att övervaka detaljerad affärsverksamhet.

Negativ: Problem med prestanda vid sökning. Svårt att tolka trendbeteenden vid fasta tidpunkter.

Ett exempel på en transaktionsfaktatabell

Vi tittar på ett typiskt finansiellt stjärnschema:

Vi registrerar varje enskild kontoåtgärd i faktatabellen. Exempel på data från den här tabellen är:

Notera: time_date representeras som det visas i dim_time-dimensionen, och den tekniska kolumnen ignoreras.

När kontoinnehavaren genomför transaktioner, t.ex. insättning och uttag av pengar, registrerar vi varje åtgärd.

Med den här strukturen kan vi besvara frågor som t.ex: Hur många transaktioner per dag hanterar vår verksamhet? Vad är det genomsnittliga beloppet som tas ut på en dag? etc.

Periodiska snapshotfaktatabeller

Periodiska snapshottabeller registrerar verksamhetens kumulativa prestanda vid fördefinierade tidsperioder. Ett förutbestämt intervall för att ta ögonblicksbilder är nyckeln: dagligen, veckovis, månadsvis osv. Resultaten sparas i den periodiska snapshot-faktatabellen.

Den här typen av faktatabell ger oss stort handlingsutrymme: vi kan införliva vilken information som helst som beskriver aktiviteten under en tidsperiod.

Det är möjligt att gå från en transaktionstabell till en periodisk tabell. (Det finns några snygga algoritmer i SQL som hanterar detta, vilket vi kommer att se i kommande artiklar). Det handlar bara om att summera transaktionerna från en ögonblicksbild till en annan. Precis som transaktionsfaktatabeller har många dimensioner har ögonblicksbilden vanligtvis färre dimensioner.

Pros: Prestationsvinst, longitudinell bild av verksamheten, fysisk kompakthet.

Negativ: Lägre korn.

Ett exempel på en periodisk snapshot-faktabell

Vi kan tänka oss ett annat finansiellt stjärnschema:

Vi kan anta att vi byggde den här faktabellen och dess datamart från samma källa som i det föregående exemplet. Vi kan till och med ha byggt den här faktatabellen från data i den tidigare faktatabellen.

Vi måste visa balance_amount på varje ögonblicksbildsdatum; i det här fallet är ögonblicksbildsgranulariteten en dag. Vi aggregerar alla utgående transaktioner på daglig basis, och insättningstypen adderar och uttag subtraherar. Resultatet är:

Notera: Vi presenterar time_date så som den visas i dimensionen dim_time och den tekniska kolumnen ignoreras.

Nu kan vi besvara några vanliga affärsfrågor, till exempel: Hur många dagar har en kund ett positivt kontosaldo under en månad? Vad är det genomsnittliga saldot för en viss typ av kund varje månad? etc.

Reorganisering av en faktatabell för periodiska ögonblicksbilder

Du kanske har lagt märke till en viss redundans i de data som visades i det föregående exemplet. Ibland är till och med snapshotfaktatabellen för stor och prestandan går ner. Vad gör man då? Vi kan omorganisera vår snapshot fact table till något mer kompakt.

Vi har lagt till två nya kolumner, date_from och date_to. De representerar den tidsperiod under vilken balance_amount var giltig.

Data i den här tabellen skulle se ut så här:

Varje gång balance_amount ändras stänger vi tidsperioden och öppnar en ny.

Bemärk att kolumnen date_to innehåller en icke-inkluderande tidsavgränsare. Vi måste ta hänsyn till detta när vi frågar efter data. Vi hittar också ett intressant datum – 31/12/9999. Detta datum visar att den sista raden är det senast kända saldot. Vi kommer att uppdatera den när saldot ändras.

Nedan kan vi se den SQL som vi använder för att fråga efter en ögonblicksbild vid ett visst datum (: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
Vårt svar returneras i den första raden i frågan.
Fakttabeller är köttet i datalagringsvärlden. Deras storlek kan motsvara många terabyte och de tar mest plats i ett datalager. Ett fel tidigt i utformningen av faktatabeller genererar många problem, som inte blir lättare att lösa när lagret utvecklas! 
Det är möjligt att omorganisera data från en typ av faktatabell till en annan. Vi kommer att utforska tekniker för att göra detta i ett kommande inlägg. Hur är det med dina erfarenheter av olika typer av faktatabeller? Har du lyckats blanda dem på något nytt och spännande sätt? Om du gjorde det, låt mig veta.