Facts about Facts: Organizarea tabelelor de fapte în sistemele de stocare a datelor

Procesul de definire a sistemului dumneavoastră de stocare a datelor (DWH) a început. Ați conturat tabelele de dimensiuni relevante, care se leagă de cerințele de afaceri. Aceste tabele definesc ceea ce cântărim, observăm și cântărim. Acum trebuie să definim modul în care măsurăm.

Tabele de date sunt locul în care stocăm aceste măsurători. Ele conțin date de afaceri care pot fi agregate între combinațiile de dimensiuni. Dar adevărul este că tabelele de date nu sunt atât de ușor de descris – ele au arome proprii. În acest articol, vom răspunde la câteva întrebări de bază despre tabelele de date și vom examina avantajele și dezavantajele fiecărui tip.

Ce sunt tabelele de date?

În sensul cel mai general, tabelele de date sunt măsurători ale unui proces de afaceri. Ele conțin în principal date numerice și corespund mai degrabă unui eveniment decât unui anumit raport.

Cea mai importantă caracteristică a unui tabel de date, în afară de măsuri, este granulația. Granulația definește ce nivel de detaliu este observat pentru un anumit eveniment. (Comparați acest lucru cu expresia „controale de granulație fină”, care înseamnă că utilizatorii pot controla detalii foarte mici pentru contul lor).

Cu cât este mai detaliată granulația tabelului de date, cu atât mai bine va gestiona cerințele imprevizibile ale afacerii. Dezavantajul unei granulații mai mari este că este necesar mai mult spațiu fizic pentru stocarea datelor. De asemenea, poate cauza o performanță mai lentă.

Cum sunt structurate tabelele de date?

Un tabel de date general constă din două grupuri principale de atribute:

• Caife străine pentru tabelele dimensionale
• Măsuri

Caifele străine sunt de la sine înțelese; dimensiunile degenerate aparțin, de asemenea, acestui grup. O dimensiune degenerată este o cheie dimensională care nu are un tabel dimensional părinte. Acestea apar atunci când toate informațiile importante despre dimensiune se află deja în tabelul de date. Printre exemple se numără diverse numere de antet de control, numere de bilete, numere de comandă etc.

Măsuri (de ex. măsurători sau fapte de afaceri) dintr-un tabel de date pot fi:

• Aditive: însumabile pe orice dimensiune
• Semi-additive: însumabile pe unele dimensiuni
• Non-additive: nu sunt însumabile (de exemplu, diverse rapoarte)

În afară de măsuri și chei străine, pot exista multe coloane tehnice. Coloanele tehnice sunt utile pentru audit și pentru întreținerea de nivel scăzut a modelului. Timestamp-urile, care sunt utilizate pentru a marca momentul în care au loc inserții sau actualizări în tabelul de date, sunt un exemplu comun de coloană tehnică.

Pentru a ilustra o tabelă de date generică, să ne uităm la o schemă stea foarte simplă:

În acest model, avem o singură tabelă de date înconjurată de trei tabele de dimensiuni. Cheile noastre străine pentru tabela de date includ:

• time_id – se referă la tabela de dimensiune timp (dim_time)
• product_id – se referă la tabela de dimensiune produs (dim_product)
• store_id – se referă la tabela de dimensiune magazin (dim_store)
• pos_transaction – o dimensiune degenerată. Acestea sunt toate informațiile despre POS de care avem nevoie, așa că le putem stoca aici. Nu există niciun motiv pentru a construi un alt tabel de dimensiuni.

Grupul nostru de chei de măsură este format din:

• sales_quantity – Vânzările unui produs la un moment dat într-un magazin. Această măsură este aditivă.
• sales_price – Prețul unui articol așa cum este vândut în magazin. Aceasta este o măsură neaditivă.

Există o coloană tehnică, time_inserted, care stochează ora la care este introdus un rând.

Acum că avem o idee generală despre tabelele de date, vom săpa în diferitele lor arome. Există patru tipuri de tabele de fapte: tabele de tranzacții, instantanee periodice, instantanee de acumulare și tabele de fapte fără fapte.

Care aromă servește unui scop în reprezentarea activității de bază pe care o susține sistemul de stocare de date. Cu toate acestea, înainte de a aprofunda ceea ce fac aceste tabele de date diferite, să vorbim despre un factor comun important: sparsity, sau cantitatea proporțională de date stocate într-un tabel de date. Sparsitatea este legată de granulație și are un efect asupra performanței interogărilor.

Ce este sparsitatea tabelelor de date?

Când proiectați un tabel de date, luați în considerare sparsitatea acestuia – adică ce număr de rânduri ale tabelului sunt populate față de câte sunt goale. Dacă completăm tabelele de date din mai multe tabele de bază, este înțelept să estimăm sparsity. Estimarea se face pe baza nivelului de granulație din tabelul de date. Dacă tabelul de date se potrivește cu tabelul sursă subiacent, puteți utiliza cardinalitatea tabelului subiacent pentru a calcula sparsity.

Pentru a calcula sparsity, mai întâi găsim raportul de umplere – adică numărul de celule nevide la numărul total de celule. Pentru a calcula raportul de umplere, împărțiți numărul de rânduri din tabelul sursă la numărul de rânduri distincte din fiecare tabel de dimensiuni. Apoi se calculează ca 1-(f), unde f = rata de umplere.

Exemplu:

Să presupunem că umplem tabelul fact_retail_sale din două surse: un tabel din baza de date cu 100.000 de rânduri și o foaie de calcul cu 20.000 de rânduri. Aceste rânduri sunt completate pe o bază anuală. Tabelele de dimensiuni sunt: date (365 de rânduri), produse (100 de rânduri) și magazine (1.000 de rânduri).

Calculăm raritatea ca:

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

Acest tabel de date este rar, deoarece mai puțin de 1% din rândurile sale au valori diferite de zero.

O bună regulă empirică este că noi considerăm că tot ceea ce este sub 2% este foarte rarefiat; tot ceea ce este peste această valoare nu este atât de rarefiat.

Tabele de date de tranzacție

Acesta este cel mai simplu și cel mai comun tip de tabel de date. Gradul de granulație al acestui tip este de un rând pe tranzacție, sau un rând pe linie pe o tranzacție. Granulația unui tabel de date de tranzacție este un punct în spațiu și timp. Acestea conțin cele mai mici detalii de afaceri.

Pe măsură ce are loc o tranzacție, se captează un context extins despre aceasta. Acest context creează o mulțime de detalii în tabelele de dimensiuni, așa că ne așteptăm la o mulțime de astfel de tabele.

După ce introducem un rând într-un tabel de tranzacții, acesta este rareori, sau chiar niciodată, revizuit. Acest lucru ne determină să luăm în considerare tehnici specifice pentru popularea acestor tipuri de tabele. Lipsa actualizărilor înseamnă un proces ETL (extragere, transformare și încărcare) mult mai simplu.

Pros: O granularitate mai mare permite monitorizarea activităților comerciale detaliate.

Cons: Probleme de performanță în interogare. Dificultăți în interpretarea comportamentelor de tendință la puncte fixe în timp.

Un exemplu de tabel de date de tranzacție

Să ne uităm la o schemă tipică de stea financiară:

Înregistrăm fiecare acțiune de cont în tabelul de date. Exemplul de date din acest tabel include:

Nota: time_date este reprezentată așa cum este prezentată în dimensiunea dim_time, iar coloana tehnică este ignorată.

În timp ce proprietarul contului efectuează tranzacții, cum ar fi depunerea și retragerea de bani, înregistrăm fiecare acțiune.

Cu această structură putem răspunde la întrebări precum:

: Câte tranzacții pe zi procesează afacerea noastră? Care este suma medie retrasă într-o zi? etc.

Tabele de date cu instantanee periodice

Tabele de date cu instantanee periodice înregistrează performanța cumulativă a afacerii în perioade de timp predefinite. Un interval prestabilit pentru realizarea instantaneelor este cheia: zilnic, săptămânal, lunar etc. Rezultatele sunt salvate în tabelul de date cu instantanee periodice.

Acest tip de tabel de date ne oferă o mare marjă de manevră: putem încorpora orice informație care descrie activitatea pe o perioadă de timp.

Mutarea de la un tabel de tranzacții la un tabel periodic este posibilă. (În SQL există niște algoritmi ingenioși care se ocupă de acest lucru, după cum vom vedea în articolele următoare). Este doar o chestiune de însumare a tranzacțiilor de la un instantaneu la altul. Așa cum tabelele de fapt ale tranzacțiilor au multe dimensiuni, instantaneul are, de obicei, mai puține dimensiuni.

Pros: Câștig de performanță, vedere longitudinală a afacerii, compactitate fizică.

Cons: Granulație mai mică.

Un exemplu de tabel de date instantaneu periodic

Să luăm în considerare o altă schemă star financiară:

Să presupunem că am construit acest tabel de date și mart-ul său de date din aceeași sursă ca și în exemplul anterior. Am fi putut chiar să construim acest tabel de fapte din datele din tabelul de fapte anterior.

Trebuie să afișăm balance_amount la fiecare dată de instantaneu; în acest caz, granularitatea instantaneu este de o zi. Agregăm toate tranzacțiile de ieșire pe o bază zilnică, iar tipul de depozit este adăugare și retragere este scădere. Rezultatul este:

Nota: Prezentăm time_date așa cum este prezentată în dimensiunea dim_time, iar coloana tehnică este ignorată.

Acum putem răspunde la câteva întrebări obișnuite de afaceri, cum ar fi:

: Câte zile are un client un sold pozitiv în cont într-o lună? Care este soldul mediu al unui anumit tip de client în fiecare lună? etc.

Reorganizarea unui tabel de date cu instantanee periodice

S-ar putea să fi observat o anumită redundanță în datele afișate în exemplul anterior. Uneori, chiar și tabelul de date instantanee este prea mare, iar performanța se prăbușește. Ce este de făcut atunci? Putem reorganiza tabelul nostru de fapte instantanee în ceva mai compact.

Am adăugat două coloane noi, date_from și date_to. Ele reprezintă perioada de timp în care balance_amount a fost valabilă.

Datele din acest tabel ar arăta astfel:

De fiecare dată când balance_amount se schimbă, închidem perioada de timp și deschidem una nouă.

Atenție, observați că coloana date_to conține un delimitator de timp neinclusiv. Trebuie să ținem cont de acest lucru atunci când interogăm datele. Găsim, de asemenea, o dată interesantă – 31/12/9999. Această dată demonstrează că ultimul rând este ultimul sold cunoscut. Îl vom actualiza atunci când soldul se va schimba.

Mai jos, putem vedea SQL-ul pe care îl folosim pentru a interoga un instantaneu la o anumită dată (: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
Răspunsul nostru este returnat în prima linie a interogării.
Tabelele de date sunt carnea lumii depozitării de date. Dimensiunea lor poate fi egală cu mulți terabytes și ocupă cel mai mult spațiu într-un depozit de date. O eroare la începutul procesului de proiectare a tabelelor fact generează multe probleme, care nu devin mai ușor de rezolvat pe măsură ce depozitul evoluează! 
Este posibilă reorganizarea datelor de la un tip de tabel de fapte la altul. Vom explora tehnicile pentru a face acest lucru într-o postare viitoare. Ce părere aveți despre experiențele dvs. cu diferite tipuri de fact tables? Ați reușit să le amestecați într-un mod nou și interesant? Dacă ați reușit, anunțați-mă. 
 .