Une première incursion en attendant des applications Business Intelligence ? On attend tous les outils sur iPad :)
mardi 7 février 2012
Le CRM en attendant la BI
Une petite news sympa où l'on apprend que Microsoft va sortir des applications CRM mobiles pour iOs ou Androïd (en plus bien sûr de Windows Phone 7).
Une première incursion en attendant des applications Business Intelligence ? On attend tous les outils sur iPad :)
Une première incursion en attendant des applications Business Intelligence ? On attend tous les outils sur iPad :)
vendredi 3 février 2012
[SSIS] Data Taps dans SSIS 2012
On continue la série d'articles sur les nouveautés SSIS dans SQL Server 2012 avec les data taps.
On a tous utilisé lors du développement de packages SSIS les data viewers qui nous permettaient de voir les données qui transitaient au sein d'un dataflow. Un des soucis des data viewers était leur utilisation limitée à la phase de développement : on ne pouvait pas l'utiliser sur nos environnements de productions. De plus lors de plantages, il nous arrivait de redescendre les packages sur notre environnement de développement et d'activer ces fameux data viewers, mais la désynchronisation des données entre les deux environnements compliquaient le travail de débogage.
Pour remédier à cela, Microsoft implémente dans SSIS 2012 les data taps. Ils permettent comme les data viewers de récupérer les données qui transitent, mais, en plus de les insérer dans des fichiers CSV. Autre avantage, ils ne nécessitent pas d'intervenir sur le package en lui-même, mais juste sur leur script d'exécution.
On imagine aisément leur utilisation lors d'un plantage en production pour vérifier les données réellement chargées.
En pratique
J'utilise comme support un package tout simple contenant un simple dataflow de chargement de données.
J'ai ainsi dans mon package deux data flow path :
On va voir que les noms des data flow path sont utiles par la suite.
Il ne reste alors qu'à scripter une instance d'exécution de mon package et d'ajouter les data taps et enfin de l'exécuter réellement. Pour cela on utilise 3 nouvelles procédure stockées :
L'architecture de mon projet SSIS est la suivante :
Le script est le suivant :
Conclusion
C'est vraiment une fonctionnalité intéressante qui risque d'être beaucoup utilisée sur les projets en cas d'anomalie de production. C'est le genre de petite nouveauté qui "ne mange pas de pain" mais qui montre l'évolution dans le bon sens d'Integration Services.
On a tous utilisé lors du développement de packages SSIS les data viewers qui nous permettaient de voir les données qui transitaient au sein d'un dataflow. Un des soucis des data viewers était leur utilisation limitée à la phase de développement : on ne pouvait pas l'utiliser sur nos environnements de productions. De plus lors de plantages, il nous arrivait de redescendre les packages sur notre environnement de développement et d'activer ces fameux data viewers, mais la désynchronisation des données entre les deux environnements compliquaient le travail de débogage.
Pour remédier à cela, Microsoft implémente dans SSIS 2012 les data taps. Ils permettent comme les data viewers de récupérer les données qui transitent, mais, en plus de les insérer dans des fichiers CSV. Autre avantage, ils ne nécessitent pas d'intervenir sur le package en lui-même, mais juste sur leur script d'exécution.
On imagine aisément leur utilisation lors d'un plantage en production pour vérifier les données réellement chargées.
En pratique
J'utilise comme support un package tout simple contenant un simple dataflow de chargement de données.
J'ai ainsi dans mon package deux data flow path :
On va voir que les noms des data flow path sont utiles par la suite.
Il ne reste alors qu'à scripter une instance d'exécution de mon package et d'ajouter les data taps et enfin de l'exécuter réellement. Pour cela on utilise 3 nouvelles procédure stockées :
- [catalog].[create_execution]
- [catalog].[add_data_tap]
- [catalog].[start_execution]
L'architecture de mon projet SSIS est la suivante :
J'ai créé ici 2 data taps, les valeurs de paramètres à renseigner sont assez explicites quand on regarde l'arborescence du projet SSIS. Les noms des data flow path sont à renseigner ici.
Il n'y a guère la valeur de la variable @task_package_path qui peut sembler plus obscure. On trouve la valeur à renseigner dans les propriétés du data flow task :
J'ai juste spécifié des noms de fichiers csv pour la variable @data_filename sans spécifier de dossier, il crée donc par défaut les fichiers dans le répertoire C:\Program Files\Microsoft SQL Server\110\DTS\DataDumps
Après exécution, on retrouve bien nos deux fichiers de données dans le répertoire mentionné.
Conclusion
C'est vraiment une fonctionnalité intéressante qui risque d'être beaucoup utilisée sur les projets en cas d'anomalie de production. C'est le genre de petite nouveauté qui "ne mange pas de pain" mais qui montre l'évolution dans le bon sens d'Integration Services.
mercredi 1 février 2012
[SSIS] Change Data Capture (CDC) dans SSIS 2012
Le change data capture est un concept qui n'est pas propre à SQL Server (présent dans d'autres SGBD tels que Oracle), et qui consiste à suivre et à récupérer les changements sur les données dans une table.
Il a été implémenté au niveau de SQL Server 2008, mais exclusivement au niveau du moteur de base de données : beaucoup d'articles de blog en ont parlé à l'époque, je vous invite à vous référer à l'article de François Jehl.
Un des côtés fastidieux du CDC était le traçage par LSN et donc l'écriture des requêtes qui pouvait être pénible sous SSIS.
Dans SQL Server 2012, Microsoft va plus loin dans sa démarche et nous donne 3 composants pour utiliser le CDC directement dans SSIS :
Package d'alimentation quotidien
On crée maintenant le package d'alimentation quotidien : il ressemble à notre package initial dans sa structure (toujours un CDC control task suivi d'un dataflow task et d'un autre CDC control task).
Dans le CDC Control Task Start on sélectionne "Get processing range" dans le "CDC control operation" :
Dans le CDC Control Task End, on sélectionne "Mark processing range" dans le "CDC control operation" :
Le dataflow task est maintenant développé de la manière suivante, à l'aide des deux nouveaux composants SSIS (CDC source et CDC splitter) :
Je m'attarde sur le CDC source, notamment au niveau du CDC processing mode. Je vous laisse consulter l'article suivant de Matt Masson (qui fait partie de l'équipe de développement de SSIS) pour comprendre pourquoi j'ai choisi "Net" :
J'effectue quelques requêtes de modifications de données dans ma table source et j'exécute ensuite mon package.
Dans mon dataflow, on retrouve bien les modifications effectuées sur ma table source, et ma table de destination est bien à la fin de l'exécution semblable à la table source :
Là où ça devient intéressant est que si vous relancez le package, aucune ligne n'est générée dans le dataflow. SSIS a géré tout seul les LSN grâce à nos CDC control task, et sait qu'il n'y a pas eu de changement pas rapport à la précédente exécution.
Conclusion :
SSIS 2012 implémente la gestion en natif du change data capture et simplifie son utilisation. La gestion pénible des LSN pour dater les changements devient implicite : aucune requête à écrire dans mon dataflow task au niveau des sources et aucune manipulation de dates à implémenter :
En revanche, il faut garder à l'esprit que le CDC peut être gourmand en ressources sur vos bases sources, comme c'était déjà le cas sur SQL Server 2008.
Il a été implémenté au niveau de SQL Server 2008, mais exclusivement au niveau du moteur de base de données : beaucoup d'articles de blog en ont parlé à l'époque, je vous invite à vous référer à l'article de François Jehl.
Un des côtés fastidieux du CDC était le traçage par LSN et donc l'écriture des requêtes qui pouvait être pénible sous SSIS.
Dans SQL Server 2012, Microsoft va plus loin dans sa démarche et nous donne 3 composants pour utiliser le CDC directement dans SSIS :
- Une tâche CDC Control Task qui va gérer le cycle de vie des packages CDC, et notamment toute la mécanique des LSN.
- Deux composants pour DataFlow Task, un CDC Source qui, comme son nom l'indique lit les informations d'une table de change data capture, et un CDC Splitter qui, lui va rediriger les lignes selon qu'elles doivent être insérées, mises à jour ou supprimées.
En pratique :
On va créer un package SSIS utilisant les composant CDC pour alimenter une table finale à partir d'une table source.
Je crée une table source de tarifs de produit dans laquelle j'insère un jeu de données test.
Sur cette table j'active le CDC. Ici c'est la même chose que sous SQL Server 2008, on retrouve les mêmes commandes.
Package d'initialisation
Dans SSIS, je crée mon package d'initialisation qui va effectuer la première alimentation de ma table destination et initialiser une variable d'état du CDC.
Le package se présente sous la forme suivante :
Les paramètres à saisir dans le CDC Control Task Start sont les suivants :
- Dans "SQL Server CDC database ADO.NET connection manager", renseigner le connection manager SSIS où est utilisé le change data capture
- Dans "CDC control operation", sélectionner "Mark initial load start"
- Dans "Variable containing the CDC state", donner le nom d'une variable où l'on va stocker l'état du CDC. Cliquer sur New s'il s'agit du premier appel.
- Les 3 champs suivants definissent respectivement la base de données, la table et le champ de la table où l'on stocke l'état du CDC
Le flux de contrôle "CDC Control Task End" est à configurer de la même manière, sauf que l'on sélectionne "Mark initial load end" dans "CDC control operation"
Le dataflow Task consiste en un bête chargement de table :
On peut dès lors exécuter le package et vérifier les données que j'ai dans ma table destination.
Package d'alimentation quotidien
On crée maintenant le package d'alimentation quotidien : il ressemble à notre package initial dans sa structure (toujours un CDC control task suivi d'un dataflow task et d'un autre CDC control task).
Dans le CDC Control Task Start on sélectionne "Get processing range" dans le "CDC control operation" :
Dans le CDC Control Task End, on sélectionne "Mark processing range" dans le "CDC control operation" :
Le dataflow task est maintenant développé de la manière suivante, à l'aide des deux nouveaux composants SSIS (CDC source et CDC splitter) :
Je m'attarde sur le CDC source, notamment au niveau du CDC processing mode. Je vous laisse consulter l'article suivant de Matt Masson (qui fait partie de l'équipe de développement de SSIS) pour comprendre pourquoi j'ai choisi "Net" :
J'effectue quelques requêtes de modifications de données dans ma table source et j'exécute ensuite mon package.
Dans mon dataflow, on retrouve bien les modifications effectuées sur ma table source, et ma table de destination est bien à la fin de l'exécution semblable à la table source :
Là où ça devient intéressant est que si vous relancez le package, aucune ligne n'est générée dans le dataflow. SSIS a géré tout seul les LSN grâce à nos CDC control task, et sait qu'il n'y a pas eu de changement pas rapport à la précédente exécution.
Conclusion :
SSIS 2012 implémente la gestion en natif du change data capture et simplifie son utilisation. La gestion pénible des LSN pour dater les changements devient implicite : aucune requête à écrire dans mon dataflow task au niveau des sources et aucune manipulation de dates à implémenter :
En revanche, il faut garder à l'esprit que le CDC peut être gourmand en ressources sur vos bases sources, comme c'était déjà le cas sur SQL Server 2008.
vendredi 27 janvier 2012
[Modélisation BI] Les Clés Etrangères
Relançons le débat pour un vendredi : faut-il mettre des clés étrangères dans un datawarehouse.
Sujet sensible puisque deux sessions aux dernières journées SQL Server en ont plus ou moins parlé :
Comme Florian le suggère sur son blog, vous devinerez que je suis partisan de ne pas en mettre.
Pourquoi une telle hérésie ?
Je fais de l'informatique décisionnelle : ma base de données se charge via des batchs uniques développés majoritairement avec SSIS (tout du moins pour mon cas). Mes tables n'ont donc qu'une façon unique d'être alimentée et ce qu'on insère doit, de ce fait, être totalement maîtrisé par mon package (voir mon article sur la gestion des orphelins à ce sujet).
C'est un point essentiel de ma démonstration : losrque je fais du développement SQL pour une application .NET par exemple, je mets des clés étrangères car mes tables peuvent dépendre à la fois de batch ou d'écrans de l'application, etc.
L'autre point essentiel est l'optimisation de mes traitements : la fenêtre temporelle pour le chargement des données est par expérience de plus en plus réduite et le volume des données toujours plus grand. Toute optimisation de chargement est donc bonne à prendre.
C'est pour cela également que je ne fais pas un un rejet total de toutes les contraintes : j'implémente des clés primaires et des index uniques bien pensés au niveau de mon datawarehouse
Et l'intégrité des données dans tout ça ?
Garantir l'intégrité des données par des relations de clés étrangères c'est bien, mais ça peut être coûteux en performance. Développer une solution d'alimentation qui ne permet pas de créer des problèmes d'intégrité, c'est mieux.
Il faut garder à l'esprit que vous devrez de toute façon repasser sur votre alimentation et redévelopper vos packages si vous avez des données non intègres. Les clés étrangères ne vous épargneront pas ce travail, donc autant y remédier en amont.
Et concrètement dans la pratique ?
Les conditions de test sont les suivantes :
J'ai imaginé une table de faits avec un idTemps, un IdClient, un IdProduit et une quantité.
Un index unique sur les Id est créé.
Le premier test consiste en un chargement de 250000 lignes dans la table de faits lorsque celle-ci est vide.
Résultats :
Je ne prétends pas clore le débat avec cet article, mais juste exposer mes préférences et les argumenter. Il faut garder à l'esprit que les tests effectués ici sont faits sur une faible volumétrie et seulement deux clés étrangères.
Il convient enfin de garantir que votre solution d'alimentation empêche l'insertion de données non intègres pour pouvoir justifier la non-utilisation des clés étrangères.
Sujet sensible puisque deux sessions aux dernières journées SQL Server en ont plus ou moins parlé :
- La session de Florian Eiden et Jean-Pierre Riehl sur la modélisation dimensionnelle
- La session de Frédéric Brouard sur les contraintes et performances
Comme Florian le suggère sur son blog, vous devinerez que je suis partisan de ne pas en mettre.
Pourquoi une telle hérésie ?
Je fais de l'informatique décisionnelle : ma base de données se charge via des batchs uniques développés majoritairement avec SSIS (tout du moins pour mon cas). Mes tables n'ont donc qu'une façon unique d'être alimentée et ce qu'on insère doit, de ce fait, être totalement maîtrisé par mon package (voir mon article sur la gestion des orphelins à ce sujet).
C'est un point essentiel de ma démonstration : losrque je fais du développement SQL pour une application .NET par exemple, je mets des clés étrangères car mes tables peuvent dépendre à la fois de batch ou d'écrans de l'application, etc.
L'autre point essentiel est l'optimisation de mes traitements : la fenêtre temporelle pour le chargement des données est par expérience de plus en plus réduite et le volume des données toujours plus grand. Toute optimisation de chargement est donc bonne à prendre.
C'est pour cela également que je ne fais pas un un rejet total de toutes les contraintes : j'implémente des clés primaires et des index uniques bien pensés au niveau de mon datawarehouse
Et l'intégrité des données dans tout ça ?
Garantir l'intégrité des données par des relations de clés étrangères c'est bien, mais ça peut être coûteux en performance. Développer une solution d'alimentation qui ne permet pas de créer des problèmes d'intégrité, c'est mieux.
Il faut garder à l'esprit que vous devrez de toute façon repasser sur votre alimentation et redévelopper vos packages si vous avez des données non intègres. Les clés étrangères ne vous épargneront pas ce travail, donc autant y remédier en amont.
Et concrètement dans la pratique ?
Les conditions de test sont les suivantes :
- Machine Virtuelle en Windows 7
- SQL Server 2008
- 4 CPU
- 3 Go de Ram
J'ai imaginé une table de faits avec un idTemps, un IdClient, un IdProduit et une quantité.
Un index unique sur les Id est créé.
J'utilise un package SSIS d'upsert classique développé avec un double lookup.
Le premier test consiste en un chargement de 250000 lignes dans la table de faits lorsque celle-ci est vide.
Résultats :
| Sans clé étrangère | Avec clés étrangères |
| 6s078 | 7s458 |
Le second test consiste en un chargement de 250000 lignes dont la moitié sont en update et l'autre moitié sont en insert.
Résultats :
| Sans clé étrangère | Avec clés étrangères |
| 1min32 | 1min39 |
Conclusion :
Je ne prétends pas clore le débat avec cet article, mais juste exposer mes préférences et les argumenter. Il faut garder à l'esprit que les tests effectués ici sont faits sur une faible volumétrie et seulement deux clés étrangères.
Il convient enfin de garantir que votre solution d'alimentation empêche l'insertion de données non intègres pour pouvoir justifier la non-utilisation des clés étrangères.
jeudi 26 janvier 2012
SQL Server 2012 et Techdays 2012
Un petit message pour faire le point sur SQL Server 2012 :
La date de sortie est prévue pour le 7 Mars 2012
A ce sujet, je vous conseille de jeter un oeil sur le blog de Charles-Henri Sauget si vous souhaitez vous installer une maquette sur une machine virtuelle. Il consacre une série d'articles qui va de l'installation de SQL Server jusqu'à la production des rapports, et ceci de manière très bien détaillée.
Autre actualité autour des technologies Microsoft et donc de SQL Server 2012, la tenue des Techdays 2012, les 7, 8 et 9 Février au Palais des Congrès à Paris.
Beaucoup de sessions autour de SQL Server 2012 de prévues, année de sortie oblige :
- 2012 : les utilisateurs prennent le pouvoir ?
- Analysis Services 2012 : BI Personnelle, couche sémantique, cube, quelle(s) solution(s) pour un nouveau projet décisionnel?
- Business Intelligence ou Intelligence pour le Business avec SQL 2012 ?
- Mettre en Oeuvre une Plateforme d'Intégration et de Gestion des Informations de l'Entreprise (EIM) avec SQL Server 2012 Master Data Services
- Le futur a-t-il besoin de nous ?
- Vous avez dit Power View ?
- BI en libre-service et maîtrise de l'IT, pourquoi choisir ?
- SQL Server 2012: Gérer vos données maitres avec Master Data Services (MDS)
- SQL Server 2012 : Gérez la qualité de vos données avec Data Quality Services (DQS)
Je serai sûrement présent les 8 & 9 Février et j'essaierai d'assister aux sessions précédemment citées. Il y a de fortes chances que je traîne souvent avec Florian Eiden également.
mercredi 25 janvier 2012
[SSAS] Ajouter de l'intelligence à votre cube Analysis Services (Year To Date YTD, Month To Date MTD,...)
Un article que je voulais faire depuis longtemps, donc je profite du temps que j'ai devant moi.
On donne toujours comme avantage à un cube par rapport à une base de données relationnelle, sa facilité à naviguer sur les dates.
De là découle une certaine facilité à créer des membres calculés en MDX tels que des Year To Date, des Month To Date, des 3 Jours Glissants, etc... bien qu'ils ne soient pas intégrés par défaut.
On va montrer comment les intégrer dans un cube SSAS en moins de temps qu'il ne faut pour le dire.
Conseil :
Je recommande de faire la manipulation sur un cube finalisé. Ca vous évitera de mettre les mains dans le code MDX pour rajouter des mesures ou modifier des noms.
Processus :
On donne toujours comme avantage à un cube par rapport à une base de données relationnelle, sa facilité à naviguer sur les dates.
De là découle une certaine facilité à créer des membres calculés en MDX tels que des Year To Date, des Month To Date, des 3 Jours Glissants, etc... bien qu'ils ne soient pas intégrés par défaut.
On va montrer comment les intégrer dans un cube SSAS en moins de temps qu'il ne faut pour le dire.
Conseil :
Je recommande de faire la manipulation sur un cube finalisé. Ca vous évitera de mettre les mains dans le code MDX pour rajouter des mesures ou modifier des noms.
Processus :
- Ouvrir sa solution Analysis Services, et aller sur le cube.
- Cliquer sur "Add Business Intelligence"
- Dans la fenêtre qui s'ouvre, cliquer sur "Next", puis sélectionner "Define Time Intelligence" et cliquer sur "Next"
- Choisir ensuite une hiérarchie et le type de mesures d'analyse temporelle que vous voulez ajouter.
Remarques :
Les indicateurs créés dépendent de la hiérarchie. Il faut donc recommencer l'opération autant de fois que l'on a de hiérarchies date.
Votre dimension doit être définie comme une dimension date en spécifiant bien les niveaux (plus il y a de niveaux définis, plus le choix de mesures à rajouter est grand)
- Dans l'exemple, j'ai choisi la hiérarchie "Calendar", pour un YTD, un MTD et un 12 Mois Glissants.
- Sélectionner les mesures sur lesquelles vous voulez appliquer ses analyses. Dans l'exemple, je choisis "Internet Sales Amount" et "Internet Order Quantity", puis cliquer sur "Finish", puis encore sur "Finish" dans la fenêtre d'après.
Le travail est terminé, on va maintenant regarder comment se présentent les résultats.
Dans notre hiérarchie Temps que l'on a utilisée, un nouvel attribut nommé "Calendar Date Calculations" est disponible. Il prend pour valeur les 3 analyses que l'on a sélectionnées lors de la création (YTD, MTD, 12 mois glissants) plus la période courante (qui correspond à la valeur à date, c'est-à-dire sans analyse)
Mais qu'est-ce qui s'est passé concrètement ?
Dans le DataSourceView, l'assistant a créé une Named Calculation nommé "Calendar Date Calculations" qui correspond à notre attribut d'analyse qui vaut "Current Date".
L'assistant a créé ensuite le code MDX qui initialise notre nouvel attribut pour les valeurs YTD, MTD et 12 mois glissants. Puis fait un scope sur les mesures que l'on a sélectionnées avec les formules adéquates.
On imagine aisément que l'on peut s'appuyer sur ce code MDX pour créer des analyses qui ne seraient pas présentes dans l'assistant.
Conclusion :
Normalement vos utilisateurs seront friands de ce genre d'implémentation. Et vu le temps de mise en oeuvre, il serait dommage de les en priver.
mercredi 14 décembre 2011
Gestion des Orphelins
La gestion des orphelins est une problématique récurrente de tous les projets décisionnels. On va détailler ici les 3 méthodes permettant de gérer tout ça.
Tout d'abord : qu'est-ce qu'un orphelin ? Il s'agit d'une clé dans notre table de fait qui arrive de notre système source, mais qui n'est pas référencée dans la dimension associée. Comment cela peut-il se produire ? Plusieurs hypothèses sont possibles : une erreur à la saisie lors d'une gestion par saisie manuelle d'un code, des systèmes de gestions indépendants n'ayant pas des mises à jours simultanées, ... Les causes peuvent être multiples.
Dans les exemples qui suivent on va partir d'une table de faits de ventes qui contient un code client qui n'est pas dans la dimension client (l'exemple n'est pas innocent, c'est un cas rencontré chez un client. La création de la carte client remontait dans les systèmes sources avec une latence d'une quinzaine de jours alors que la vente était bien saisie sur la carte non encore créée)
On a donc en tables sources les tables suivantes :
Une table source Client :
Une table source Ventes :
qui contient bien un client non référencé (ici le CodeClient 40)
1ère méthode : Le rejet
La première méthode est la plus simple : elle consiste à dire que cette ligne de ventes avec un client inconnu est une anomalie, et à rejeter de ce fait la ligne.
Attention, il faut penser à insérer la ligne rejetée dans une table de rejet pour pouvoir retenter une intégration une fois notre système source client mis à jour.
On a donc au final les 3 tables suivantes :
Une dimension Client :
Une table de faits Ventes :
Et une table de rejet :
Cette méthode est une des plus simples à mettre en place. Il faut juste penser à rejouer l'intégration des lignes de la table de rejet de manière régulière.
2ème méthode : Création d'un membre inconnu
La méthode vue précédemment a un inconvénient : à un instant t, on peut avoir au niveau global des différences de chiffres. En effet des lignes sont rejetées, ce n'est pas la meilleure manière de gérer l'intégrité des données.
La solution consiste donc à définir un client inconnu sur lequel on rattachera les lignes contenant un code client absent du système source.
On crée donc un client inconnu dans notre nouvelle table de dimension Client (souvent en id -1, ligne que l'on créera une bonne fois pour toutes au moment de la création de la table) :
Et au moment du chargement de notre table de faits Ventes, on met comme IdClient -1 lorsqu'on trouve un client non présent dans la table de dimension :
Cette méthode est la plus répandue sur les projets décisionnels. Elle a cependant un inconvenient majeur, c'est qu'on a une perte d'information. Si le client est créé ultérieurement notre ligne sera toujours rattaché au client inconnu dans notre table de faits.
Pour cela, on peut utiliser une variation de cette deuxième méthode (méthode 2bis) qui consiste à rajouter dans notre table de faits le CodeClient afin de pouvoir mettre à jour l'IdClient une fois le client créé dans le système source.
La table de faits Ventes ressemblerait donc à :
Cela nécessite comme la première méthode le passage d'un script de mise à jour que l'on planifierait par exemple toutes les semaines ou tous les mois.
Je n'aime pas trop cette variation car elle crée dans la table de faits des données qui n'ont pas grand chose à y faire.
3ème méthode : la méthode "ultime"
La dernière méthode empêche toute perte d'informations mais peut être coûteuse en performance.
Elle consiste à scanner en amont du chargement de la table de faits les codes client non présents dans la table de dimension, et à les insérer dans la table de dimension, avec des attributs neutres qui seront mis à jour lors de l'arrivée du client dans le système source.
On obtient alors la table de dimension suivante :
Le nom inconnu et le prénom inconnu seront mis à jour lors de l'arrivée dans le système source du client ayant le CodeClient 40.
Et notre chargement de la table de faits donne la table suivante :
Cette méthode est la plus robuste (d'où le nom "ultime") dans le sens où vous pouvez alimenter votre table de faits sans lancer l'alimentation de votre table de dimension et ne pas perdre d'informations.
Conclusion :
Le méthode 2bis et 3 sont les plus robustes en terme de conservation de l'information. En revanche, elles n'ont d'intérêt que dans le cas où on a une chance de recevoir ultérieurement le client dans le système source. Dans les faits, ce sont la méthode 2 et dans une moindre mesure la méthode 3 qui sont les plus utilisées sur les projets.
Gardez à l'esprit que la gestion des orphelins doit se faire en fonction du souhait de votre client car il s'agit avant tout d'une problématique fonctionnelle. Ce n'est pas au développeur de décider quelle méthode implémenter, mais c'est à lui d'informer son client quand aux différentes gestions possibles.
Tout d'abord : qu'est-ce qu'un orphelin ? Il s'agit d'une clé dans notre table de fait qui arrive de notre système source, mais qui n'est pas référencée dans la dimension associée. Comment cela peut-il se produire ? Plusieurs hypothèses sont possibles : une erreur à la saisie lors d'une gestion par saisie manuelle d'un code, des systèmes de gestions indépendants n'ayant pas des mises à jours simultanées, ... Les causes peuvent être multiples.
Dans les exemples qui suivent on va partir d'une table de faits de ventes qui contient un code client qui n'est pas dans la dimension client (l'exemple n'est pas innocent, c'est un cas rencontré chez un client. La création de la carte client remontait dans les systèmes sources avec une latence d'une quinzaine de jours alors que la vente était bien saisie sur la carte non encore créée)
On a donc en tables sources les tables suivantes :
Une table source Client :
| Code Client | Nom | Prénom | … |
| 12 | Pierre | Jean | |
| 15 | Dupont | Jacques | |
| 25 | Martin | Louis |
Une table source Ventes :
| DateVente | CodeClient | CodeProduit | Quantité | … |
| 12/12/11 | 12 | FR1234 | 2 | |
| 12/12/11 | 12 | FR3421 | 5 | |
| 13/12/11 | 25 | GR0987 | 10 | |
| 15/12/11 | 40 | RF5483 | 9 |
qui contient bien un client non référencé (ici le CodeClient 40)
1ère méthode : Le rejet
La première méthode est la plus simple : elle consiste à dire que cette ligne de ventes avec un client inconnu est une anomalie, et à rejeter de ce fait la ligne.
Attention, il faut penser à insérer la ligne rejetée dans une table de rejet pour pouvoir retenter une intégration une fois notre système source client mis à jour.
On a donc au final les 3 tables suivantes :
Une dimension Client :
| IdClient | CodeClient | Nom | Prénom | … |
| 1 | 12 | Pierre | Jean | |
| 2 | 15 | Dupont | Jacques | |
| 3 | 25 | Martin | Louis |
Une table de faits Ventes :
| IdDate | IdClient | IdProduit | Quantité | … |
| 20111212 | 1 | 1 | 2 | |
| 20111212 | 1 | 2 | 5 | |
| 20111213 | 3 | 3 | 10 |
Et une table de rejet :
| DateVente | CodeClient | CodeProduit | Quantité | … |
| 15/12/11 | 40 | RF5483 | 9 |
Cette méthode est une des plus simples à mettre en place. Il faut juste penser à rejouer l'intégration des lignes de la table de rejet de manière régulière.
2ème méthode : Création d'un membre inconnu
La méthode vue précédemment a un inconvénient : à un instant t, on peut avoir au niveau global des différences de chiffres. En effet des lignes sont rejetées, ce n'est pas la meilleure manière de gérer l'intégrité des données.
La solution consiste donc à définir un client inconnu sur lequel on rattachera les lignes contenant un code client absent du système source.
On crée donc un client inconnu dans notre nouvelle table de dimension Client (souvent en id -1, ligne que l'on créera une bonne fois pour toutes au moment de la création de la table) :
| IdClient | CodeClient | Nom | Prénom | … |
| -1 | N/A | N/A | N/A | |
| 1 | 12 | Pierre | Jean | |
| 2 | 15 | Dupont | Jacques | |
| 3 | 25 | Martin | Louis |
Et au moment du chargement de notre table de faits Ventes, on met comme IdClient -1 lorsqu'on trouve un client non présent dans la table de dimension :
| IdDate | IdClient | IdProduit | Quantité | … |
| 20111212 | 1 | 1 | 2 | |
| 20111212 | 1 | 2 | 5 | |
| 20111213 | 3 | 3 | 10 | |
| 20111215 | -1 | 4 | 9 |
Cette méthode est la plus répandue sur les projets décisionnels. Elle a cependant un inconvenient majeur, c'est qu'on a une perte d'information. Si le client est créé ultérieurement notre ligne sera toujours rattaché au client inconnu dans notre table de faits.
Pour cela, on peut utiliser une variation de cette deuxième méthode (méthode 2bis) qui consiste à rajouter dans notre table de faits le CodeClient afin de pouvoir mettre à jour l'IdClient une fois le client créé dans le système source.
La table de faits Ventes ressemblerait donc à :
| IdDate | IdClient | CodeClient | IdProduit | Quantité | … |
| 20111212 | 1 | 12 | 1 | 2 | |
| 20111212 | 1 | 12 | 2 | 5 | |
| 20111213 | 3 | 25 | 3 | 10 | |
| 20111215 | -1 | 40 | 4 | 9 |
Cela nécessite comme la première méthode le passage d'un script de mise à jour que l'on planifierait par exemple toutes les semaines ou tous les mois.
Je n'aime pas trop cette variation car elle crée dans la table de faits des données qui n'ont pas grand chose à y faire.
3ème méthode : la méthode "ultime"
La dernière méthode empêche toute perte d'informations mais peut être coûteuse en performance.
Elle consiste à scanner en amont du chargement de la table de faits les codes client non présents dans la table de dimension, et à les insérer dans la table de dimension, avec des attributs neutres qui seront mis à jour lors de l'arrivée du client dans le système source.
On obtient alors la table de dimension suivante :
| IdClient | CodeClient | Nom | Prénom | … |
| 1 | 12 | Pierre | Jean | |
| 2 | 15 | Dupont | Jacques | |
| 3 | 25 | Martin | Louis | |
| 4 | 40 | Inconnu | Inconnu |
Le nom inconnu et le prénom inconnu seront mis à jour lors de l'arrivée dans le système source du client ayant le CodeClient 40.
Et notre chargement de la table de faits donne la table suivante :
| IdDate | IdClient | IdProduit | Quantité | … |
| 20111212 | 1 | 1 | 2 | |
| 20111212 | 1 | 2 | 5 | |
| 20111213 | 3 | 3 | 10 | |
| 20111215 | 4 | 4 | 9 |
Cette méthode est la plus robuste (d'où le nom "ultime") dans le sens où vous pouvez alimenter votre table de faits sans lancer l'alimentation de votre table de dimension et ne pas perdre d'informations.
Conclusion :
Le méthode 2bis et 3 sont les plus robustes en terme de conservation de l'information. En revanche, elles n'ont d'intérêt que dans le cas où on a une chance de recevoir ultérieurement le client dans le système source. Dans les faits, ce sont la méthode 2 et dans une moindre mesure la méthode 3 qui sont les plus utilisées sur les projets.
Gardez à l'esprit que la gestion des orphelins doit se faire en fonction du souhait de votre client car il s'agit avant tout d'une problématique fonctionnelle. Ce n'est pas au développeur de décider quelle méthode implémenter, mais c'est à lui d'informer son client quand aux différentes gestions possibles.
Inscription à :
Articles (Atom)