Des faits sur des faits : Organiser les tables de faits dans les systèmes d’entrepôt de données

Le processus de définition de votre système d’entrepôt de données (DWH) a commencé. Vous avez esquissé les tables de dimensions pertinentes, qui se rattachent aux exigences de l’entreprise. Ces tables définissent ce que nous pesons, observons et mettons à l’échelle. Il faut maintenant définir comment nous mesurons.

Les tables de faits sont l’endroit où nous stockons ces mesures. Elles contiennent des données métier qui peuvent être agrégées à travers des combinaisons de dimensions. Mais le fait est que les tables de faits ne sont pas si faciles à décrire – elles ont des saveurs qui leur sont propres. Dans cet article, nous répondrons à quelques questions de base sur les tables de faits, et nous examinerons les avantages et les inconvénients de chaque type.

Que sont les tables de faits ?

Au sens le plus général, les tables de faits sont les mesures d’un processus métier. Elles contiennent principalement des données numériques et correspondent à un événement plutôt qu’à un rapport particulier.

La caractéristique la plus importante d’une table de faits, outre les mesures, est le grain. Le grain définit quel niveau de détail est observé pour un événement particulier. (Comparez cela avec l’expression « contrôles à grain fin », qui signifie que les utilisateurs peuvent contrôler de très petits détails pour leur compte).

Plus le grain de la table de faits est détaillé, mieux elle gérera les exigences commerciales imprévisibles. L’inconvénient d’un plus grand grain est que plus d’espace physique est nécessaire pour le stockage des données. Il peut également entraîner un ralentissement des performances.

Comment les tables de faits sont-elles structurées ?

Une table de faits générale se compose de deux groupes d’attributs principaux :

• Clés étrangères aux tables dimensionnelles
• Mesures

Les clés étrangères sont auto-explicatives ; les dimensions dégénérées appartiennent également à ce groupe. Une dimension dégénérée est une clé de dimension sans table de dimension parente. Elles apparaissent lorsque toutes les informations importantes sur la dimension se trouvent déjà dans la table de faits. Les exemples incluent divers numéros d’en-tête de contrôle, des numéros de ticket, des numéros de commande, etc.

Les mesures (c’est-à-dire. métriques ou faits d’affaires) dans une table de faits peuvent être :

• Additives : sommables à travers n’importe quelle dimension
• Semi-additives : sommables à travers certaines dimensions
• Non-additives : non sommables (par exemple, divers ratios)

En plus des mesures et des clés étrangères, il peut y avoir de nombreuses colonnes techniques. Les colonnes techniques sont utiles pour l’audit et la maintenance de bas niveau du modèle. Les horodateurs, qui sont utilisés pour marquer le moment où les insertions ou les mises à jour se produisent dans la table de faits, sont un exemple courant de colonne technique.

Pour illustrer une table de faits générique, regardons un schéma en étoile très simple :

Dans ce modèle, nous avons une seule table de faits entourée de trois tables de dimensions. Nos clés étrangères pour la table de faits comprennent :

• time_id – fait référence à la table de dimension temps (dim_time)
• product_id – fait référence à la table de dimension produit (dim_product)
• store_id – fait référence à la table de dimension magasin (dim_store)
• pos_transaction – une dimension dégénérée. C’est toute l’information sur le POS dont nous avons besoin, donc nous pouvons la stocker ici. Il n’y a aucune raison de construire une autre table de dimension.

Notre groupe de clés de mesure se compose de :

• sales_quantity – Les ventes d’un produit à un moment donné dans un magasin. Cette mesure est additive.
• sales_price – Le prix d’un article tel que vendu dans le magasin. Cette mesure est non additive.

Il existe une colonne technique, time_inserted, qui stocke le moment où une ligne est insérée.

Maintenant que nous avons une idée générale des tables de faits, nous allons creuser dans leurs différentes saveurs. Il existe quatre types de tables de faits : les tables de transactions, d’instantanés périodiques, d’instantanés accumulatifs et de faits sans faits.

Chaque saveur sert un objectif de représentation de l’activité sous-jacente que le système d’entreposage de données prend en charge. Cependant, avant de nous plonger dans ce que font ces différentes tables de faits, parlons d’un facteur commun important : la sparsité, ou la quantité proportionnelle de données stockées dans une table de faits. La sparsité est liée au grain, et elle a un effet sur les performances des requêtes.

Qu’est-ce que la sparsité d’une table de faits ?

Lorsque vous concevez une table de faits, tenez compte de sa sparsité, c’est-à-dire du nombre de lignes de la table qui sont remplies par rapport au nombre de lignes vides. Si nous remplissons les tables de faits à partir de nombreuses tables sous-jacentes, il est judicieux d’estimer la sparsité. Nous faisons cette estimation en nous basant sur le niveau de grain de la table de faits. Si votre table de faits correspond à la table source sous-jacente, vous pouvez utiliser la cardinalité de la table sous-jacente pour calculer la sparsité.

Pour calculer la sparsité, nous trouvons d’abord le ratio de remplissage – c’est-à-dire le nombre de cellules non vides par rapport au total des cellules. Pour calculer le taux de remplissage, divisez le nombre de lignes de la table source par le nombre de lignes distinctes dans chaque table de dimension. La spartialité est alors calculée comme 1-(f), où f = fill ratio.

Exemple:

Supposons que nous remplissons la table fact_retail_sale à partir de deux sources : une table de base de données avec 100 000 lignes et une feuille de calcul avec 20 000 lignes. Ces lignes sont remplies sur une base annuelle. Les tables de dimension sont : les dates (365 lignes), les produits (100 lignes) et les magasins (1 000 lignes).

Nous calculons la sparsité comme suit :

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

Cette table de faits est clairsemée, car moins de 1% de ses lignes ont des valeurs non nulles.

Une bonne règle empirique est que nous considérons que tout ce qui est inférieur à 2% est très clairsemé ; tout ce qui est supérieur à cela n’est pas si clairsemé.

Tables de faits de transactions

C’est le type de table de faits le plus simple et le plus courant. Le grain de ce type est une ligne par transaction, ou une ligne par ligne sur une transaction. Le grain d’une table de faits de transaction est un point dans l’espace et le temps. Elles contiennent les plus petits détails commerciaux.

A mesure qu’une transaction se produit, un contexte étendu à son sujet est capturé. Ce contexte crée beaucoup de détails dans les tables de dimension, donc nous nous attendons à ce qu’il y en ait beaucoup.

Une fois que nous avons inséré une ligne dans une table de transaction, elle est rarement, voire jamais, revisitée. Cela nous amène à envisager des techniques spécifiques pour alimenter ces types de tables. L’absence de mises à jour signifie un processus ETL (extract, transform, and load) beaucoup plus simple.

Pros : Une granularité plus élevée permet de surveiller des activités commerciales détaillées.

Cons : Problèmes de performance dans les requêtes. Difficulté à interpréter les comportements de tendance à des points fixes dans le temps.

Un exemple de tableau de faits de transaction

Regardons un schéma financier typique en étoile:

Nous enregistrons chaque action de compte unique dans la table de faits. Voici un exemple de données de cette table :

Note : time_date est représentée telle qu’elle apparaît dans la dimension dim_time, et la colonne technique est ignorée.

A mesure que le propriétaire du compte effectue des transactions, comme le dépôt et le retrait d’argent, nous enregistrons chaque action.

Avec cette structure, nous pouvons répondre à des questions comme : Combien de transactions par jour notre entreprise traite-t-elle ? Quel est le montant moyen retiré en un jour ? etc.

Tables de faits d’instantanés périodiques

Les tables d’instantanés périodiques enregistrent les performances cumulées de l’entreprise à des périodes de temps prédéfinies. Un intervalle prédéterminé pour prendre des instantanés est la clé : quotidien, hebdomadaire, mensuel, etc. Les résultats sont enregistrés dans la table de faits des instantanés périodiques.

Ce type de table de faits nous donne une grande marge de manœuvre : nous pouvons incorporer toute information qui décrit l’activité sur une période de temps.

Le passage d’une transaction à une table périodique est possible. (Il y a quelques algorithmes soignés dans SQL qui traitent de cela, comme nous le verrons dans les prochains articles). Il s’agit seulement d’additionner les transactions d’un instantané à un autre. De même que les tables de faits des transactions ont de nombreuses dimensions, le snapshot en a généralement moins.

Pros : Gain de performance, vue longitudinale de l’activité, compacité physique.

Cons : Grain inférieur.

Un exemple de table de faits à instantané périodique

Prenons un autre schéma financier en étoile:

Supposons que nous avons construit cette table de faits et son data mart à partir de la même source que l’exemple précédent. Nous aurions même pu construire cette table de faits à partir des données de la table de faits précédente.

Nous devons montrer le balance_amount à chaque date d’instantané ; dans ce cas, la granularité de l’instantané est d’un jour. Nous agrégeons toutes les transactions sortantes sur une base quotidienne, et le type de dépôt est une addition et le retrait est une soustraction. Le résultat est :

Note : Nous présentons time_date comme il est indiqué dans la dimension dim_time et la colonne technique est ignorée.

Maintenant, nous pouvons répondre à certaines questions commerciales courantes, comme : Combien de jours un client porte-t-il un solde de compte positif dans un mois ? Quel est le solde moyen d’un type spécifique de client chaque mois ? etc.

Réorganiser un tableau de faits instantanés périodiques

Vous avez peut-être remarqué une certaine redondance dans les données affichées dans l’exemple précédent. Parfois même, la table de faits de l’instantané est trop grande et les performances en prennent un coup. Que faire alors ? Nous pouvons réorganiser notre tableau de faits instantanés en quelque chose de plus compact.

Nous avons ajouté deux nouvelles colonnes, date_from et date_to. Elles représentent la période de temps pendant laquelle le balance_amount était valide.

Les données de cette table ressembleraient à ceci:

Chaque fois que le balance_amount change, nous fermons la période de temps et en ouvrons une nouvelle.

Notez que la colonne date_to contient un délimiteur de temps non inclusif. Nous devons en tenir compte lorsque nous interrogeons des données. Nous trouvons également une date intéressante – 31/12/9999. Cette date démontre que la dernière ligne correspond au dernier solde connu. Nous la mettrons à jour lorsque le solde changera.

Ci-après, nous pouvons voir le SQL que nous utilisons pour interroger un instantané à une date particulière (: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
Notre réponse est retournée dans la première ligne de la requête.
Les tables de faits sont la viande du monde de l'entreposage de données. Leur taille peut égaler plusieurs téraoctets, et elles prennent le plus d'espace dans un entrepôt de données. Une erreur au début du processus de conception des tables de faits génère de nombreux problèmes, qui ne deviennent pas plus faciles à résoudre à mesure que l'entrepôt évolue ! 
Il est possible de réorganiser les données d'un type de table de faits à un autre. Nous explorerons les techniques pour le faire dans un prochain billet. Qu'en est-il de vos expériences avec différents types de tables de faits ? Avez-vous réussi à les mélanger d'une manière nouvelle et passionnante ? Si vous l'avez fait, faites-le moi savoir.