Conseil 14: Calculer le solde à une date donnée sur une table de faits de type transactionnelle

L’article précédent a montré comment joindre une dimension à évolution lente (la table des comptes clients dans une banque) avec une table de faits dont le volume augmente rapidement (les transactions des comptes clients). Nous avons vu comment la dimension à évolution lente était elle même une sorte de table de faits, puisqu'elle était également la cible d'un ensemble d'opérations qui modifiait les profils des comptes.

Dans cet article, nous allons nous concentrer sur la table de faits qui enregistre tout les mouvements qui ont lieu sur les comptes bancaires. 



Simplifions la structure de cette table de faits pour notre discussion :

Date_Clé (FK)

Compte_Clé (FK)

Type_De_Transaction_Clé (FK)

Numéro_Transaction

Indicateur_Fin

Montant

Solde

Voici la définition des champs :

Date_Clé = clé de substitution pointant vers une dimension Calendrier ayant une granularité au jour

Compte_Clé = clé de substitution pointant vers la dimension des comptes bancaires

Type_De_Transaction_Clé = clé de substitution pointant vers une petite dimension contenant les types de transaction autorisés

Numéro_Transaction = numéro de séquence incrémental qui participe à la vie du compte

Indicateur_Fin = « Vrai » si c’est la dernière transaction de la journée, « Faux » sinon

Montant = montant de la transaction

Solde = solde du compte après la transaction

Comme toutes les tables de transaction, celle-ci a uniquement un enregistrement lorsqu'une transaction a été effectuée. Par exemple, si un compte était inactif durant deux semaines, du 1^er au 14 octobre, il n’y aura aucun enregistrement dans la table de fait pour ce compte et pour la période en question. Mais supposons que nous voulons savoir quel est le solde des comptes au 5 octobre.

Dans ce cas, nous avons besoin, pour chaque compte, de retrouver l’enregistrement le plus récent qui précède ou qui est égal à cette date.

Voici quelques requêtes SQL répondant à notre besoin :

SELECT a.Num_Compte, f.Solde

FROM Faits f, Comptes a

WHERE f.Compte_Clé = a.Compte_Clé

            AND f.Indicateur_Fin = 'True'

            AND f.Date_Clé =

                        (SELECT MAX(g.Date_Clé)

                        FROM Faits g

                        WHERE g.Compte_Clé = f.Compte_Clé

                        AND g.Date_Clé IN

                                   (SELECT t.Date_Clé

                                   FROM Temps t

                                   WHERE t.Date <= 'October 5, 2000'))

A la suite de cette requête, vous avez surement des questions.

Question 1: comment pouvons nous utiliser le champ "Date_Clé" comme contrainte? la caractéristique principale d’une clé de substitution est qu’elle n’a aucun sens fonctionnel.

Réponse 1 : oui, excepté pour la clé de substitution liée au temps qui est une suite de nombre allant de 1 à N. Et qui, pour une toute autre raison, est créé et ordonnée selon un ordre chronologique.
Nous avons besoin que cette clé soit ordonnée. Ainsi, en se basant sur celle-ci, nous pouvons partitionner physiquement les grosses tables de faits. Cela segmente proprement la table physique. Et, par conséquent, nous permet d'effectuer de discrètes taches d’administration sur certaines tranches de temps, comme les déplacer vers un stockage hors-ligne ou supprimer et reconstruire des index. 
Cette dimension Temps est la seule dimension qui a une logique sur sa clé de substitution et est la seule sur laquelle on peut se permettre de placer un filtre. Nous utilisons cette astuce dans la requête précédente, pour retrouver l’enregistrement le plus récent par rapport à une date donnée.

Question 2 : Pourquoi vous embêtez-vous à utiliser une dimension Temps alors que nous pourrions tout simplement filtrer sur un champs timestamp placé dans la table de fait ? Ainsi, nous n'aurions pas besoin d'une clé de substitution.

Réponse 2 : remplacer la clé de substitution Date_Clé par un timestamp entraîne un certain nombre de problèmes que nous pouvons résoudre avec la clé de substitution. Cela comprend les dates nulles, inapplicables ou qui ne sont pas encore passées. Nous avons déjà discuté de cela auparavant, mais le plus important, est qu'un timestamp dans une table de faits ne nous permet pas de réaliser des filtres temporels complexes. 
Que se passerait-il si au lieu du 5 octobre, l'objectif de la requête aurait été d’obtenir les soldes au 3^ème trimestre du calendrier qu'utilise la banque (peu importe ce que cela représente)? Avec notre solution, la requête ci-dessus serait partiellement impactée. Il suffit de remplacer la dernière ligne avec un filtre, sur le champs approprié de la dimension Temps, pour cet évènement spécial.

Question 3 : cette conception n’est elle pas sensible aux transactions insérées de manière rétroactive dans la table de fait ?

Réponse 3 :c’est une bonne question. Cette table de faits doit être complète et exacte. Chaque transaction sur le compte doit figurer dans cette table sinon le solde courant ne pourra pas être calculé. En effet, une transaction arrivée tardivement obligera à balayer tous les enregistrements à partir du point d’insertion pour incrémenter le champs "Numéro_Transaction" et recalculer les soldes. 
Notez que nous n'avons pas utilisé explicitement le numéro de séquence des transactions dans cette discussion. Mais celui-ci est nécessaire à cette conception pour reconstituer correctement la séquence réelle des transactions et fournir une clé unique à la table de faits (Compte_Clé + Date_Clé + Numéro_Transaction). Je préfère le numéro de transaction plutôt qu’un timestamp parce que la différence entre des numéros de séquence est une mesure valide de l'activité d'un compte.

Source originale: www.kimballgroup.com

Article original "Kimball Design Tip #14: Reporting balances on arbritarily chosen days in a transaction fact table", publié le 5 octobre 2000.