Au cours des deux dernières années, j'ai vu une augmentation de la demande pour les applications qui ont besoin de poser des questions sur des intervalles de temps. Une personne a le fait quand il dit: "chaque enregistrement dans ma table de fait est un épisode de valeur constante dans un laps de temps". Un intervalle de temps peut démarrer et s'arrêter à des points arbitraires dans le temps. Dans certains cas, des intervalles de temps peuvent être liés pour former une chaîne ininterrompue, dans d'autres cas, ils sont isolés, et dans le pire des cas, ils se chevauchent arbitrairement. Mais chaque intervalle de temps est représenté dans la base de données par un seul enregistrement. Pour faciliter la visualisation de ces variations, imaginons que nous avons une base de données remplie de transactions atomiques, comme les dépôts et les retraits de comptes bancaires. Nous incluons également les transactions d'ouverture et de fermeture de compte. Chaque transaction définit implicitement un épisode de valeur constante dans un intervalle dans le temps. Un dépôt ou un retrait définit une nouvelle valeur pour le solde du compte qui est valable jusqu'à la prochaine transaction. Ce laps de temps pourrait être une seconde ou plusieurs mois. La transaction d'ouverture de compte définit le statut du compte comme actif en permanence sur un laps de temps jusqu'à ce qu'une transaction de fermeture de compte apparaisse.
Avant que nous proposions une conception de base de données, nous allons nous rappeler quelques unes des questions concernant un intervalle de temps que nous voulons poser. Nous allons commencer par limiter nos questions à une granularité au jour, plutôt que des parties de jours comme des minutes et des secondes. Nous reviendrons sur les minutes et les secondes à la fin. Les questions auxquelles il est toujours facile de répondre comprennent:
- Voir toutes les transactions qui ont eu lieu dans un intervalle de temps donné.
- Déterminer si une transaction donnée a eu lieu dans un laps de temps donné.
- Définir des intervalles de temps à l'aide de fonctionnalités de navigation complexes d'un calendrier; par exemple: les saisons, les exercices fiscaux, les numéros des jours, les numéros de semaine, les jours de paie et les jours fériés.
Pour ces cas, tous ce dont nous avons besoin est un horodatage unique sur le fait enregistré dans la table de la transaction. La première question reprend toutes les transactions dont l'horodatage est dans un intervalle spécifié dans la requête de l'utilisateur. La deuxième question récupère l'horodatage à partir d'une transaction sélectionnée et la compare à l'intervalle. La troisième série de questions remplace l'horodatage simple par une dimension Calendrier rempli avec beaucoup d'attributs utiles. Cette dimension est reliée à la table de faits par une jointure standard entre la clé étrangère et la clé primaire. C'est la conception dimensionnelle standard et cela nécessite seulement une simple clé de temps dans l'enregistrement de la table de faits pour représenter l'horodatage requis. Jusqu'ici, tout va bien.
Par ailleurs, lorsque vous utilisez un calendrier complexe pour filtrer, les requêtes deviennent beaucoup plus facile si la dimension Calendrier est enrichie et comprend un marqueur pour le premier jour et le dernier jour pour chaque durée de temps définie, comme "dernier jour du trimestre". Ce champ aura la valeur "N" pour tous les jours, sauf le dernier jour du trimestre. La dernière journée aura la valeur "Y" dans un domaine particulier. Ces marqueurs permettent aux intervalles de temps métiers complexes d'être facilement définis dans les requêtes. Notez que l'utilisation d'une dimension Calendrier enrichie signifie que l'application ne filtre pas sur l'horodatage de la table de faits.
Une deuxième catégorie de questions moyennement difficile sur les intervalles de temps comprend:
- Afficher les personnes qui était clientes à un moment donné dans un intervalle de temps.
- Afficher la dernière transaction d'un client donné dans un intervalle de temps.
- Afficher le solde d'un compte à un moment choisi arbitrairement dans le temps.
Nous allons continuer à utiliser l'hypothèse que tous les intervalles de temps sont décrits par jours et non par minutes et secondes. Il est possible de répondre à toutes ces questions avec la même conception d'horodatage indiquées ci-dessus, mais cette approche nécessite des requêtes complexes et inefficaces. Par exemple, pour répondre à la dernière question, nous aurions besoin de chercher l'ensemble des opérations du compte précédant la dernière transaction qui se trouve à l'endroit désiré dans le temps. En SQL, cela prendrait la forme de sous-requêtes imbriquées. Non seulement cela serait probablement lent à exécuter, mais le SQL risque de ne pas être facilement produit par les outils des utilisateurs finaux.
Pour ce deuxième lot de questions, nous simplifions énormément les applications en fournissant deux champs d'horodatage pour chaque enregistrement de fait, indiquant le début et la fin de la période de temps définie implicitement par la transaction.
Avec cette conception, nous répondons facilement aux trois questions ci-dessus:
1. Recherchez toutes les transactions d'ouverture de compte dont la date de début commence au plus tard à la fin de l'intervalle de temps, et dont la date de fin survient au plus tôt au début de l'intervalle de temps. 2. Recherchez la transaction unique dont la date de début est inférieure ou égale à la fin de l'intervalle de temps et dont la date de fin est supérieure ou égale à la fin de l'intervalle. 3. Recherchez la transaction unique dont la date de début est inférieure ou égale au point arbitraire dans le temps et dont la date de fin est supérieure ou égale à ce même point. Dans tous ces cas, le SQL utilise une simple instruction BETWEEN.
Lorsque nous utilisons l'approche du double horodatage, nous devons être honnêtes sur un inconvénient majeur. Dans presque toutes les situations, nous devons parcourir chaque enregistrement de la table fait deux fois. Une première fois lors de l'insertion (avec un horodatage de fin ouvert), et une seconde fois lorsqu'une transaction survient et qui définit véritablement la fin. L'horodatage de fin ouvert est probablement une valeur réelle, loin dans l'avenir, de sorte que les applications n'ont pas à traiter avec des valeurs NULL quand ils essaient d'exécuter l'instruction BETWEEN.
Nous avons gardé les questions les plus difficiles pour la fin: les intervalles de temps à la seconde. Dans ce cas, nous posons les mêmes questions de base que dans les deux premières sections, mais nous définissons les limites d'intervalle de temps à la seconde près. Dans ce cas, nous allons mettre les deux mêmes horodateurs dans les enregistrements de faits, mais nous devons renoncer à notre connexion à une dimension temporelle robuste. Nos deux champs de début et de fin doivent être de type date/heure classique. Nous devons le faire parce que nous ne pouvons pas créer une dimension de temps unique comprenant toutes les minutes ou toutes les secondes d'un intervalle de temps significatif. Diviser l'horodatage en un composant Jour et un composant Secondes-dans-la-journée rendrait la logique de l'instruction BETWEEN horrible. Alors pour ces intervalles de temps ultra-précis nous faisons avec les limitations que la syntaxe SQL sur la date/heure nous impose et nous renonçons à la possibilité de définir les saisons ou les exercices fiscaux à la seconde près.
Si vous êtes vraiment un pur et dur, vous pourriez envisager de créer quatre champs pour chaque enregistrement de transaction si vos intervalles de temps sont à la seconde. Les deux premiers seraient des champs date/heure comme décrit dans le paragraphe précédent. Mais le troisième et le quatrième seraient des clés étrangères précisant le jour, et se connectant à une dimension Calendrier enrichie au jour comme dans les deux premières parties de cet article. De cette façon, vous pouvez rechercher des intervalles de temps ultra-précis, mais vous pouvez aussi poser des questions comme "Montrez-moi toutes les coupures de courant qui ont eu lieu un jour férié".
Source originale: www.kimballgroup.com
Article original "Kimball Design Tip #33: Modeling the spans", publié le 27 mars 2002.
Aucun commentaire:
Enregistrer un commentaire