Fakta om fakta: Fakta om fakta: Organisering af faktatabeller i datawarehousesystemer

Processen med at definere dit datawarehousing-system (DWH) er startet. Du har skitseret de relevante dimensionstabeller, som knytter sig til forretningskravene. Disse tabeller definerer, hvad vi vejer, observerer og vægter. Nu er vi nødt til at definere, hvordan vi måler.

Fakttabeller er der, hvor vi gemmer disse målinger. De indeholder forretningsdata, der kan aggregeres på tværs af dimensionskombinationer. Men faktum er, at faktatabeller ikke er så let at beskrive – de har deres egne smagsvarianter. I denne artikel vil vi besvare nogle grundlæggende spørgsmål om faktatabeller og undersøge fordele og ulemper ved hver type.

Hvad er faktatabeller?

I den mest generelle forstand er faktatabeller målingerne af en forretningsproces. De indeholder for det meste numeriske data og svarer til en begivenhed snarere end til en bestemt rapport.

Den vigtigste egenskab ved en faktatabel, ud over mål, er korn. Grain definerer, hvilket detaljeringsniveau der er observeret for en bestemt hændelse. (Sammenlign dette med udtrykket “fine-grain controls”, som betyder, at brugerne kan kontrollere meget små detaljer for deres konto).

Jo mere detaljeret faktatabellens korn er, jo bedre vil den kunne håndtere uforudsigelige forretningskrav. Ulempen ved større korn er, at der kræves mere fysisk plads til datalagring. Det kan også medføre langsommere ydeevne.

Hvordan er faktatabeller struktureret?

En generel faktatabel består af to hovedattributgrupper:

• Fremme nøgler til dimensionstabeller
• Mål

Fremme nøgler er selvforklarende; degenererede dimensioner hører også til denne gruppe. En degenereret dimension er en dimensionsnøgle uden overordnet dimensionstabel. De forekommer, når alle vigtige oplysninger om dimensionen allerede findes i faktabellen. Eksempler herpå er forskellige kontrolhovednumre, billetnumre, ordrenumre osv.

Mål (dvs. målinger eller forretningsfakta) i en faktatabel kan være:

• Additiv: kan summeres på tværs af alle dimensioner
• Semi-additiv: kan summeres på tværs af nogle dimensioner
• Ikke-additiv: kan ikke summeres (f.eks. forskellige forholdstal)

Bortset fra målinger og fremmednøgler kan der være mange tekniske kolonner. Tekniske kolonner er nyttige til revision og vedligeholdelse af modellen på lavt niveau. Timestamps, som bruges til at markere, hvornår der sker indsættelser eller opdateringer i faktatabellen, er et almindeligt eksempel på en teknisk kolonne.

For at illustrere en generisk faktatabel skal vi se på et meget simpelt stjerneskema:

I denne model har vi en enkelt faktatabel omgivet af tre dimensionstabeller. Vores fremmednøgler til faktabellen omfatter:

• time_id – henviser til tidsdimensionstabellen (dim_time)
• product_id – henviser til produktdimensionstabellen (dim_product)
• store_id – henviser til butiksdimensionstabellen (dim_store)
• pos_transaction – en degenereret dimension. Dette er alle de oplysninger om POS, vi har brug for, så vi kan gemme dem her. Der er ingen grund til at opbygge endnu en dimensionstabel.

Vores foranstaltningnøglegruppe består af:

• sales_quantity – Salget af ét produkt på ét tidspunkt i én butik. Denne foranstaltning er additiv.
• sales_price – Prisen på en vare, som den sælges i butikken. Dette er et ikke-additivt mål.

Der er en teknisk kolonne, time_inserted, der gemmer tidspunktet, hvor en række indsættes.

Nu da vi har en generel idé om faktatabeller, vil vi grave i deres forskellige varianter. Der findes fire typer af faktatabeller: transaktions-, periodiske snapshot-, akkumulerende snapshot- og faktaløse faktatabeller.

Hver smag tjener et formål med at repræsentere den underliggende forretning, som datawarehousing-systemet understøtter. Men før vi går i dybden med, hvad disse forskellige faktatabeller gør, skal vi tale om en vigtig fælles faktor: sparsomhed, eller den proportionelle mængde data, der er gemt i en faktatabel. Sparsity er relateret til korn, og det har en effekt på forespørgselspræstationen.

Hvad er sparsity i faktatabeller?

Når du designer en faktatabel, skal du overveje dens sparsity – dvs. hvor mange af tabellens rækker der er befolket i forhold til hvor mange der er tomme. Hvis vi udfylder faktabellerne fra mange underliggende tabeller, er det klogt at vurdere sparsity. Vi foretager vurderingen på grundlag af kornniveauet i faktabellen. Hvis din faktatabel svarer til den underliggende kildetabel, kan du bruge kardinaliteten af den underliggende tabel til at beregne sparsomhed.

For at beregne sparsomhed finder vi først fyldningsforholdet – dvs. antallet af ikke-tomme celler i forhold til det samlede antal celler. For at finde ud af fyldningsforholdet skal du dividere antallet af rækker i kildetabellen med antallet af særskilte rækker i hver dimensionstabel. Sparsomheden beregnes derefter som 1-(f), hvor f = fyldningsgrad.

Eksempel:

Sæt, at vi fylder tabellen fact_retail_sale fra to kilder: en databasetabel med 100.000 rækker og et regneark med 20.000 rækker. Disse rækker udfyldes på årsbasis. Dimensionstabellerne er: datoer (365 rækker), produkter (100 rækker) og butikker (1.000 rækker).

Vi beregner sparsomheden som:

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

Denne faktatabel er sparsom, fordi mindre end 1 % af dens rækker har værdier, der ikke er nul.

En god tommelfingerregel er, at vi betragter alt under 2 % som meget sparsomt; alt derover er ikke så sparsomt.

Transaktionsfaktatabeller

Dette er den enkleste og mest almindelige type faktatabel. Kornet for denne type er én række pr. transaktion eller én række pr. linje på en transaktion. Korn i en transaktionsfaktualtabel er et punkt i rum og tid. De indeholder de mindste forretningsdetaljer.

Når en transaktion finder sted, registreres der omfattende kontekst om den. Denne kontekst skaber masser af detaljer i dimensionstabellerne, så vi forventer mange af dem.

Når vi indsætter en række i en transaktionstabel, bliver den sjældent, hvis nogensinde, genbesøgt. Dette får os til at overveje specifikke teknikker til at udfylde disse typer tabeller. Ingen opdateringer betyder en meget enklere ETL-proces (extract, transform and load-proces).

Pros: Højere granularitet giver mulighed for overvågning af detaljerede forretningsaktiviteter.

Cons: Ydelsesproblemer i forespørgsler. Vanskeligheder med at fortolke trendadfærd på faste tidspunkter.

Eksempel på en transaktionsfaktatabel

Lad os se på et typisk økonomisk stjerneskema:

Vi registrerer hver eneste kontohandling i faktabellen. Eksempeldata fra denne tabel omfatter:

Bemærk: time_date er repræsenteret som vist i dim_time-dimensionen, og den tekniske kolonne ignoreres.

Som kontoejeren gennemfører transaktioner, f.eks. ind- og udbetaling af penge, registrerer vi hver handling.

Med denne struktur kan vi besvare spørgsmål som f.eks: Hvor mange transaktioner om dagen behandler vores virksomhed? Hvad er det gennemsnitlige beløb, der hæves på en dag? osv.

Periodiske snapshot-faktatabeller

Periodiske snapshot-tabeller registrerer virksomhedens kumulative præstation på foruddefinerede tidsperioder. Et forudbestemt interval til at tage snapshots er nøglen: dagligt, ugentligt, månedligt osv. Resultaterne gemmes i den periodiske snapshot-faktatabel.

Denne type faktatabel giver os et stort spillerum: Vi kan indarbejde alle oplysninger, der beskriver aktiviteten i en periode.

Det er muligt at gå fra en transaktion til en periodisk tabel. (Der findes nogle smarte algoritmer i SQL, der tager sig af dette, som vi vil se i kommende artikler). Det er blot et spørgsmål om at lægge transaktionerne sammen fra et snapshot til et andet. Ligesom transaktionsfaktatabeller har mange dimensioner, har øjebliksbilledet normalt færre dimensioner.

Pros: Ydelsesgevinst, longitudinelt overblik over forretningen, fysisk kompakthed.

Kontra: Mindre korn.

Eksempel på en periodisk snapshot-faktatabel

Lad os se på et andet finansielt stjerneskema:

Lad os antage, at vi har opbygget denne faktabelle og dens datamart fra samme kilde som i det foregående eksempel. Vi kunne endda have bygget denne faktatabel ud fra dataene i den foregående faktatabel.

Vi skal vise balance_amount på hver øjebliksbillede-dato; i dette tilfælde er øjebliksbillede-granulariteten én dag. Vi aggregerer alle udgående transaktioner på daglig basis, og indbetalingstypen er adderende og tilbagetrækning er subtraherende. Resultatet er:

Bemærk: Vi præsenterer time_date, som det vises i dim_time-dimensionen, og den tekniske kolonne ignoreres.

Nu kan vi besvare nogle almindelige forretningsspørgsmål, som f.eks: Hvor mange dage har en kunde en positiv kontosaldo i en måned? Hvad er den gennemsnitlige saldo for en bestemt type kunde hver måned? osv.

Reorganisering af en faktatabel med periodiske øjebliksbilleder

Du har måske bemærket en vis redundans i de data, der blev vist i det foregående eksempel. Nogle gange er selv snapshot-faktatabellen for stor, og ydelsen tager et fald. Hvad skal man så gøre? Vi kan omorganisere vores snapshot-faktatabel til noget mere kompakt.

Vi har tilføjet to nye kolonner, date_from og date_to. De repræsenterer den tidsperiode, hvor balance_amount var gyldig.

Dataene i denne tabel ville se således ud:

Hver gang balance_amount ændres, lukker vi tidsperioden og åbner en ny.

Bemærk, at kolonnen date_to indeholder en ikke-inkluderende tidsafgrænser. Vi er nødt til at tage hensyn til dette, når vi forespørger data. Vi finder også en interessant dato – 31/12/9999. Denne dato viser, at den sidste række er den sidst kendte saldo. Vi opdaterer den, når saldoen ændres.

Nedenfor kan vi se den SQL, vi bruger til at forespørge på et øjebliksbillede på en bestemt dato (: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
Vores svar returneres i den første linje i forespørgslen.
Faktatabeller er kødet i data warehousing-verdenen. Deres størrelse kan svare til mange terabytes, og de optager mest plads i et datawarehouse. En fejl tidligt i designprocessen af faktatabeller genererer mange problemer, som ikke bliver nemmere at løse, efterhånden som lageret udvikler sig! 
Det er muligt at reorganisere data fra en type faktatabel til en anden. Vi vil undersøge teknikker til at gøre dette i et kommende indlæg. Hvad med dine erfaringer med forskellige typer af faktatabeller? Er det lykkedes dig at blande dem på en ny og spændende måde? Hvis du gjorde det, så lad mig vide det.