Même si l'écart de temps entre les systèmes OLTP de production et l'entrepôt de données a diminué dans la plupart des cas, à 24 heures, les besoins avides de nos utilisateurs du marketing nécessitent que l'entrepôt de données comble cette lacune avec des données en temps réel.
La plupart des concepteurs d'entrepôts de données sont sceptiques quant à exécuter les taches d'alimentation existantes de l'ETL (extract-transform-load) toutes les 15 minutes au lieu de toutes les 24 heures. Les concepteurs d'entrepôt de données répondent à ce problème en construisant une partition en temps réel en face de l'entrepôt de données.
Pour obtenir des rapports en temps réel, nous construisons une partition spéciale qui est physiquement et administrativement séparée des tables de l'entrepôt de données. La partition en temps réel est en fait une table distincte sujette à des règles de mise à jour spéciales et des règles de requêtes spéciales.
La partition en temps réel devrait idéalement répondre à l'ensemble des exigences suivante. Elle doit:
- contenir toute l'activité qui a eu lieu depuis la dernière mise à jour de l'entrepôt de données.
- être reliée aussi fidèlement que possible au grain et au contenu des tables de faits de l'entrepôt de données
- être si légèrement indexée que les données entrantes peuvent être continuellement insérées
- supporter des requêtes complexes
Dans la modélisation dimensionnelle il existe trois principaux types de tables de faits: le grain de transaction, le grain de l'instantané périodique, et le grain de l'instantané récapitulatif. Notre partition en temps réel a une structure différente correspondant à chaque type.
Partition en temps réel avec un grain de type transaction
Si la table de faits de l'entrepôt de données a un grain de transaction, alors il contient exactement un enregistrement pour chaque transaction individuelle présent dans le système source depuis le début de "l'histoire enregistrée". La partition en temps réel a exactement la même structure dimensionnelle que la table de faits de l'entrepôt de données. Elle ne contient que les transactions qui ont eu lieu depuis minuit, lorsque nous avons chargé les tables de l'entrepôt de données. La partition en temps réel devrait presque être complètement non indexée, parce que nous devons maintenir en permanence "la fenêtre ouverte" pour les chargements. Nous évitons aussi de construire des agrégats sur cette table parce que nous voulons un minimum d'administration durant la journée.
Nous attachons la partition en temps réel à nos applications existantes en naviguant à travers la table de faits de l'entrepôt vers la partition en temps réel. Les agrégations sur le temps (par exemple, toutes les ventes pour le mois courant) devront envoyer des requêtes identiques aux deux tables de faits puis ensuite, additionner les deux jeux de résultats.
Dans un contexte de commerce de détail relativement important gérant 10 millions de transactions par jour, la table de faits de l'entrepôt serait très grande. En supposant que chaque enregistrement d'une transaction mesure 40 octets (7 dimensions plus 3 faits, le tout dans des champs de 4 octets), nous accumulons 400 Mo de données chaque jour. En un an cela reviendrait à environ 150 Go de données brutes. Une telle table de faits serait fortement indexée et complétée par des agrégats. Mais le volume quotidien de 400 Mo (la partition en temps réel) pourrait être chargé en mémoire. Oubliez les index, à l'exception peut-être d'un index B-Tree sur la clé primaire pour améliorer les insertions! Oubliez les agrégations! Notre partition en temps réel peut rester disponible pour des chargements très rapide, et dans le même temps répondre rapidement aux requêtes.
Partition en temps réel avec un grain de type instantané périodique
Si la table de faits de l'entrepôt de données possède un grain périodique (par exemple, mensuel), alors la partition en temps réel peut être considéré comme le mois en cours. Supposons que nous sommes une grande banque de détail avec 15 millions de comptes. La table de faits de l'entrepôt a le grain "compte par mois". Une période de 36 mois se traduirait par 540 millions d'enregistrements dans la table de faits. Encore une fois, cette table serait largement indexée et soutenu par des agrégats afin de fournir de bonnes performances. La partition en temps réel, d'autre part, est juste une image du mois en cours, mise à jour et écrasée en permanence au fur et à mesure que le mois progresse. Les balances semi-additives et les faits entièrement additifs sont ajustés aussi souvent qu'ils sont chargés. Dans une banque de détail, la table de faits principale couvrant tous les types de compte est susceptible d'être assez étroite, avec peut-être 4 dimensions et 4 faits, résultant en une partition en temps réel de 480 Mo. La partition en temps réel peut alors être à nouveau montée en mémoire.
Les applications de requêtage naviguant de la table de faits de l'entrepôt à la partition en temps réel ont une logique légèrement différente par rapport au grain à la transaction. Bien que les soldes des comptes et d'autres mesures d'intensité peuvent être directement calculées à travers les tables, les totaux des cumuls pendant la période en cours pourraient être ajustés pour refléter l'équivalent d'un mois complet afin d'éviter que les résultats affichés paraissent anormaux.
Enfin, le dernier jour du mois, la partition en temps réel peut simplement être chargée dans l'entrepôt de données comme étant le mois le plus récent, et le processus peut recommencer avec une partition en temps réel vide.
Partition en temps réel avec un grain de type instantané récapitulatif
Les instantanés récapitulatifs sont utilisés pour les processus éphémères comme les commandes et les livraisons. Un enregistrement est créé pour chaque article d'une ligne de commande ou de livraison. Dans la table de faits principale, cet enregistrement est mis à jour au fur et à mesure que l'activité se déroule. Nous créons l'enregistrement pour un article d'une ligne quand l'ordre est créé, puis nous le mettons à jour lorsque l'article est envoyé, livré à la destination finale, payé, puis peut-être retourné. Les tables de faits de type instantané récapitulatif ont un ensemble caractéristique des clés étrangères pointant vers des dates qui correspondent à chacune de ces étapes.
Dans ce cas, ce type de table de faits est le seul type de table de fait qui est délibérément mis à jour, et souvent à plusieurs reprises. Mais supposons que pour des raisons de performances des requêtes, cette mise à jour se produit uniquement à minuit lorsque les utilisateurs sont déconnectés. Dans ce cas, la partition en temps réel sera composé des seuls enregistrements qui ont été mis à jour aujourd'hui. A la fin de la journée, les enregistrements de la partition en temps réel seront précisément les nouvelles versions des enregistrements qui doivent être écrits dans la table de faits de l'entrepôt soit en les insérant s'ils sont complètement nouveaux, ou en écrasant les enregistrements existants en conservant les mêmes clés primaires.
Dans de nombreux cas de commande et de livraison, le nombre de lignes dans la partition en temps réel sera beaucoup plus petite que les deux premiers exemples. Par exemple, le plus grand fabricant d'aliments pour chiens et chats des États-Unis traite environ 60.000 factures d'expédition par mois. Chaque facture peut avoir 20 lignes d'articles. Si une ligne de facturation a une durée de vie normale de deux mois et est mis à jour cinq fois dans cet intervalle, alors nous verrions environ 7500 lignes d'articles créés ou mis à jour sur une journée moyenne de travail. Même avec des enregistrements de 80 octets typiques des tables de faits de facture d'expédition, nous n'avons que 600 Ko de données dans notre partition en temps réel. Cela pourra bien évidemment tenir en mémoire. Oubliez les index et les agrégations sur cette partition en temps réel.
Les requêtes sur un instantané récapitulatif avec une partition en temps réel ont besoin d'aller chercher les lignes appropriées à la fois dans la table de faits de l'entrepôt et dans la partition, et peuvent soit naviguer à travers les deux tables en effectuant un fusion (jointure externe) sur les entêtes identiques, soit réaliser une union des lignes des deux tables, en présentant les lignes issues de l'entrepôt augmentées de quelques lignes supplémentaires occasionnelles dans le rapport qui représente l'activité du jour.
Dans cette article, j'ai fait ce que j'espère être une étude de cas solide pour satisfaire à la nouvelle exigence du temps réel en construisant de extensions spéciales, mais néanmoins familières, à nos tables de faits existantes. Si vous supprimez presque tous les index et les agrégations de ces nouvelles tables spéciales, et les chargez en mémoire, vous devriez être en mesure d'obtenir les performances dont vous avez besoin pour combiner mises à jour et requêtes de consultation.
Source originale: www.kimballgroup.com
Article original "Kimball Design Tip #31: Designing a real time partition", publié le 19 novembre 2001.
Aucun commentaire:
Enregistrer un commentaire