[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é :

• 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 fait un test avec une table de dimension Produit à 50000 lignes et une table de dimension Client à 50000 lignes.
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.

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.

[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 :

• 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.