Petite astuce SQL : génération de requêtes SQL avec Excel

Si c’est une évidence pour les vieux de la vieille, je me rends compte que tous ne sont pas forcément au courant de cette technique assez simple qui permet de générer des requêtes SQL avec Excel. Ça a surement déjà dû être bloggé 10 fois, mais ça va plus vite d’écrire l’article que de le chercher 😉

Vous partez depuis Excel, avec le tableau qu’on souhaiterait importer côté SQL:

Un tableau dans Excel

Vous y ajoutez la formule Excel suivante :

Tableau plus la formule qui suit

="SELECT '"&Tableau1[@[Colonne 1]]&"' AS Colonne1, "&Tableau1[@[Colonne 2]]&" AS Colonne2, '"&Tableau1[@[Colonne 3]]&"' AS Colonne3 UNION ALL"

Le principe vous l’avez compris : on compose une chaîne de caractère (= »SELECT… ») qui va correspondre à un SELECT simple (qui n’a pas besoin de FROM sur SQL Server, on rajoute FROM DUAL sur du Oracle) qui seront enchainés les uns aux autres par le UNION ALL (= »SELECT … UNION ALL »). Dans cette chaîne, on n’oublie pas de mettre les champs texte entre quote (‘ ).

Voilà ce que ça donne :

Tableau avec le résultat de la formule sur la première ligne

Ce qu’on va pouvoir recopier pour tout le tableau :

Tableau avec la formule recopié pour toutes les lignes

Direction SQL Server Management Studio, où on supprime le dernier UNION ALL, puis on encapsule le tout dans un SELECT imbriqué, et hop, mes valeurs sont prêtes à être intégrées avec un INSERT INTO, ou utilisées pour définir une vue :

La requête SQL en résultat

En SQL:

SELECT
        Colonne1,
        Colonne2,
        Colonne3
FROM (
        SELECT 'A' AS Colonne1, 12 AS Colonne2, 'XART' AS Colonne3 UNION ALL
        SELECT 'B' AS Colonne1, 15 AS Colonne2, 'CFSE' AS Colonne3 UNION ALL
        SELECT 'C' AS Colonne1, 42 AS Colonne2, 'SJRU' AS Colonne3 --UNION ALL
) A

C’est quand même bien fait hein ?

Webcast : Pratiques et Outils pour la BI Agile

Voici le webcast de ma session co-animée par Jordan Mootoosamy et Thomas Morisson, présentée lors des Journées SQL Server 2013 organisées par le GUSS. Après une courte intro de ma part c’est Thomas qui prend la parole sur SCRUM, suivi de Jordan sur Biml et MIST, puis je conclue sur NBi.

Vous trouverez tous les liens dont nous parlons dans les slides.

Si ces sujets vous intéressent, je vous laisse creuser sur ce blog, et particulièrement autour la réflexion qui avait motivé cette session, ou du Kanban, une autre méthode novatrice de gestion de projet.

Enfin, notez que tous les webcasts et les slides des sessions des JSS2013 sont dispo sur le site du GUSS (si besoin il faut s’inscrire, c’est évidemment gratuit).

Bon visionnage 😉

BI.Quality : tests automatisés pour comparer des données entre SSAS et SQL Server

Comme je vous le disais tantôt, l’amélioration continue des équipes et des solutions passent par une automatisation des activités qui peuvent l’être. Ce n’est pas moi qui l’invente, c’est à la base de l’Agilité et du Lean. Notez que ce n’est pas une fin en soi, mais plutôt dans l’objectif de tendre vers un temps de production le plus court possible, toujours en qualité optimale.

Et une des principales manières d’avancer sur sujet c’est bien par l’automatisation des tests. C’est la raison pour laquelle je teste depuis peu BI.Quality, l’outil de test automatique gratuit développé par ORAYLIS et disponible sur le CodePlex.

BI.Quality

Ce qui est particulièrement intéressant avec cet outil c’est la possibilité de comparer des datasets en provenance de SSAS, de SQL Server, ou d’un fichier CSV. Vous comprenez l’intérêt immédiatement : avec ça on va pouvoir comparer de manière automatique son cube (via requêtes MDX) avec son DWH et/ou son ODS (via requêtes SQL). On pourra également comparer le résultat d’une même requête contre plusieurs environnements (production, intégration, développement…) pour valider que tout est bien synchro une fois la livraison terminée.

Joie !

Alors ok, l’interface ne fait pas rêver, et quasiment toute la « programmation » se fait dans des fichiers XML, donc à la main dans NotePad++. Mais la fonction rendue est tellement excellente qu’on apprend à vivre avec.

NUnit_1

Je vous fais un guide de démarrage rapide, vous allez voir c’est assez simple :

  1. Télécharger et installer NotePad++, si ce n’est pas déjà fait !
  2. Télécharger et installer NUnit (framework de test, c’est lui qui exécutera les tests créés dans BI.Quality), la version courante c’est la NUnit-2.6.2.msi
  3. Télécharger et installer BI.Quality (pas de panique, il termine sans prévenir, c’est un peu artisanal)
  4. Télécharger et lire la documentation :p
  5. Suite à ça, on dispose dans le menu démarrer :
    1. D’un répertoire BI.Quality, qui contient principalement un ZIP contenant la solution template, qui sera le modèle de départ pour tous les projets de tests. A dézipper là où vous le souhaitez pour chaque nouveau projet.
    2. D’un répertoire NUnit, qu’il va falloir associer avec votre projet de test (le répertoire dézippé), dans NUnit : File> Open Project > …\Lib\BI.Quality.dll

Une solution de test BI.Quality c’est donc un dossier composé de 4 sous-répertoires (le contenu du ZIP) :

  • \Bin\ et \Lib\ : on ne touche pas
  • \Connections\ : on va définir nos connexions là-dedans, un fichier XML correspondant à une connexion. On ne peut y utiliser que des belles chaînes de connexions OLE DB (SSAS, SQL Server < 2008R2, SQL Server 2012, à tester sur Excel, Access et SharePoint) :

BI.Quality_Connections

  • \Queries\ : on va définir nos tests là-dedans, un sous-répertoire correspondant à un test, avec :
    • des fichiers SQL, MDX ou CSV qui définissent les requêtes à utiliser dans le test
    • un fichier XML qui définit le test en lui-même

BI.Quality_Query

Franchement c’est pas sauvage non ? Je définis 2 sets de données, les <Query/>, que je compare dans un test <AssertTable/>.

Alors il existe plein de tests possibles, avec plein d’options, à voir dans le PDF de documentation ainsi que dans l’ensemble de tests livrés dans le template (1-Tutorial, 2-TechnicalTests, 3-BestPractices) que vous pouvez d’ailleurs enlever de votre projet si vous voulez avoir une solution bien propre.

Une fois que c’est fait, on retourne dans NUnit (on charge le projet si ce n’est pas déjà fait : File> Open Project > …\Lib\BI.Quality.dll), et on peut exécuter ses tests d’un simple RUN :

NUnit_2

Si la partie « Configuration Test » est gérée toute seule, NUnit va parser les XMLs de définition des connexions et des tests pour valider leur format, la partie « Query Test » est bien celle pilotée par vos tests du répertoires \Queries\ . Notez que si vous ne passez pas le « Configuration Test », c’est que vos XMLs sont mal montés : direction NotePad++ pour corriger tout ça. Si tout va bien, c’est parti pour vos tests à vous 🙂

Juste une petite remarque en passant : je n’ai définitivement pas réussi à ajouter un Delta sur un AssertTable, n’hésitez donc pas à faire plutôt des ROUNDs dans vos requêtes SQL ou MDX, si par exemple vous changez de précision entre les 2 sources. [MàJ 2013-08-19] En fait il est possible de définir un Delta dans un AssertTable, mais en utilisant une virgule plutôt qu’un point dans la valeur: delta=« 0,1%« . Trop bien 🙂

Et une deuxième petite remarque : pensez bien à recharger vos tests si vous les modifiez (File> Reload Project ou Reload Test) sinon ce ne sera pas pris en compte par NUnit.

D’un point de vue stratégie de tests, je suis partie sur les éléments suivants :

  • D’abord des agrégations de haut niveau (mon CA par an sur les 5 grands pôles d’activité), qui valident que le total général est le même partout
  • Des tests sur chaque dimension, indépendamment des faits, pour valider qu’on n’oublie personne en route et que les hiérarchies tiennent la route
  • Des tests portant sur les valeurs des mesures clefs pour chaque code atomique de chaque dimension (autant de tests que de dimensions). On a vérifié les hiérarchies dans l’étape précédente, il suffit donc de valider que chaque code dispose des bons montants unitairement pour chaque dimension pour valider quasiment tout
  • Des scénarios de référence (SQL/MDX vs valeurs en dur dans des CSV). Si les valeurs historiques (2009,2010…) n’évoluent plus, on peut se faire quelques extraits, les stocker en CSV, et les comparer régulièrement contre le cube ou le DWH. Histoire de prendre en flagrant délit la régression sur l’historique
  • Les requêtes des rapports SSRS, qu’on peut valider contre des valeurs de références ou entre plusieurs environnements
  • Enfin, toutes les requêtes issues des analyses d’anomalie, écrites pour renvoyer du vide si tout va bien

Si on maintient bien à jour sa base de tests, et qu’on exécute le projet régulièrement, il va devenir vraiment difficile de livrer du code défectueux !

Je conclue en vous livrant l’avis de Chris Webb sur l’outil, et en vous recommandant chaudement de l’essayer sur votre prochain projet ou prochaine recette. C’est simpliste, certes, mais ça fait le job, et pouvoir tous les jours exécuter toute sa batterie de test en 1 clic c’est juste magique ! Seul petit bémol: la dernière mise à jour du projet date de fin 2010, le dernier commentaire des admins de fin 2012… Alors allez le télécharger, histoire de bien montrer qu’on a besoin d’eux 😉

Modélisation dimensionnelle à éviter : La table de faits universelle

Comme vous le savez peut-être, cette année encore je co-animerai la session Modélisation Dimensionnelle aux Journées SQL Server 2012, les 10 et 11 décembre sur Paris, avec mon camarade Charles-Henri. Cette année on passe level 300 (ça commence à causer plus sérieusement) et franchement je pense qu’on va passer un bon moment 🙂

En attendant le jour J, je voulais vous parler d’une technique qui ne sera pas présentée lors de la session : celle de la table de faits universelle. Rencontrée chez un client dernièrement, c’est une modélisation qu’on peut aussi appeler la table de faits unique. Une table de faits pour les gouverner toutes. Une table de faits pour les trouver. Une table de faits pour les amener toutes et dans les ténèbres les lierHumJe divague

Je te vois faire n'importe quoi!

Je te vois faire n’importe quoi!

Si ça avait été fait par un stagiaire, ou un client qui s’essayait au décisionnel en dilettante, je trouverais ça mignon. Sincèrement. J’applaudirais pour l’effort et on prendrait une demi-journée ensemble pour causer modélisation. Mais là c’est réalisé par une équipe de consultants spécialisés dans le décisionnel.  Et c’est facturé. Moins mignon.

Alors voyons à quoi ça ressemble:

La table de faits universelle

Dans cette même table de faits, qui s’appelle juste « Fait » (c’est plus simple) on retrouve :

  • Les ventes quotidiennes
  • L’inventaire hebdomadaire
  • Les budgets trimestriels des magasins
  • Les objectifs trimestriels des commerciaux

C’est quand même bien fait ! On a tout sous les yeux d’un seul coup. Pas besoin de jointures, les requêtes SQL sont simplissimes. Alors que reprocher à cette modélisation ?

Déjà, je vous avoue qu’en 6 ans de missions en décisionnel, je n’ai jamais vu ça. J’en ai même parlé lors d’un afterworks du GUSS, auquel étaient présents des consultants d’à peu près tous les pure-players en décisionnel Microsoft, et personne n’en avait entendu parler non plus.

Mais vous me connaissez, je n’allais pas me limiter à ça. Regardons donc ce qu’en dit la littérature :

  • Wikipedia – Fact Table : “In data warehousing, a fact table consists of the measurements, metrics or facts of a business process.”  Une table de faits pour un processus métier donc, les ventes ou l’inventaire ou les budgets… mais un seul. J’avoue, en effet, ils auraient pu insister et mettre: “ a SINGLE business process”. Mais à mon avis personne ne se doutait qu’on verrait arriver la table de faits unique.
  • Wikipedia – Base de données relationnelle : « Dans une base de données relationnelle chaque enregistrement d’une table contient un groupe d’informations relatives à un sujet (…) ». Même commentaire, et là on parle de toute la technologie de la base de données relationnelle, plus seulement du décisionnel.
  • Ralph Kimball, l’inventeur du schéma en étoile, indique lui que chaque table de faits représente un processus métier, que chacune de ces tables est reliée à des dimensions, les mêmes dimensions pour tout le monde (alors dites conformées), et que toute la valeur de la modélisation en étoile vient justement de là. Parce qu’entre nous, quitte à faire une table de fait unique, autant pas s’embêter à faire des tables de dimensions hein… Et là le lien je le fais par vers un article spécifique, mais vers le bouquin de Kimball, parce qu’à un moment il va falloir le lire ce livre si vous vous dites consultant ou développeur décisionnel.
  • Bill Inmon, l’inventeur du schéma en flocon, indique la même chose. En effet les différences entre les deux modèles se situent au niveau de la structure des dimensions et du processus de génération du modèle, pas des tables de faits.
  • Et quid de Datavault ? La troisième modélisation très contestée du décisionnel ? Là c’est pire puisqu’on normalise complètement et qu’on conserve le format source original (une table pour les clients, une table pour les magasins, une table de relation entre les 2, etc, etc). Pas de table unique en vue.

Pas de chance, la littérature ne fait donc aucune mention de cette technique, et c’est même plutôt l’inverse qui est recommandé : créer une table de faits par processus métier. Soit dans notre cas, 4 tables : ventes, inventaires, budgets et objectifs.

Je précise au passage que dans ces sources, il ne faut pas interpréter la phrase « la table de faits est au centre du schéma en étoile » comme une indication qu’il n’y en ait qu’une seule. En effet un datawarehouse ce n’est pas un mais plusieurs schémas en étoile, plusieurs datamarts, autant qu’il y a de processus métier. Et en théorie l’ensemble de ces étoiles s’appelle une constellation, mais ça devient trop poétique donc on emploie rarement le terme.

D’une manière plus pratique, si on abandonne la littérature et qu’on s’interroge sur les mérites d’une telle modélisation, on peut se faire les réflexions suivantes :

  • Performances
    • A priori elles ne seront navrantes. En effet pour aller chercher un élément particulier de la table (les budgets), le moteur doit parcourir toutes les lignes de la table (les ventes, les inventaires…). C’est largement inefficace.
    • L’index le plus rapide de tous est l’index cluster (celui qui dicte comment les données sont écrites sur le disque). Comme vous le savez, on ne peut en définir qu’un seul par table (par définition). Tout mettre dans la même table c’est donc se priver d’un des meilleurs outils d’optimisation de la base de données. A la place d’en avoir un par processus métier, il n’y en aura qu’un seul, qui en plus ne sera pas très bon. Car évidemment, l’index s’optimise différemment en fonction du sujet. On indexe les ventes (par jour/magasin/produit) différemment qu’on indexe les objectifs (par trimestre/commerciaux). Et croisez les doigts pour que l’unicité des lignes des 4 processus métiers tiennent en moins de 16 colonnes.
    • Même remarque pour le partitionnement.
  • Confort d’utilisation / Qualité du requêtage
    • Si on s’économise les jointures en SQL, j’ai peur de ce à quoi vont ressembler les clauses WHERE. Et on n’a pas intérêt à se tromper sur ces filtres, sans quoi on va additionner des choux et des carottes (des quantités de ventes et des quantités d’inventaires). Le risque métier est important avec cette approche, il est inexistant avec la modélisation classique.
    • Et là où les jointures reviendront en force, c’est si on veut obtenir un état avec par exemple du budget et du facturé. Il faudra en effet faire une auto jointure (en FULL OUTER JOIN) de la table unique sur elle-même. Ce sera douloureux en écriture de requête et en performance.
    • Enfin, on l’a bien compris, impossible d’exposer ce modèle directement à un utilisateur. Il faudra définir un modèle de métadonnées devant chaque outil de reporting (Excel, Tableau, SSAS, SSRS…). Attention au coût de développement masqué.
  • Maintenabilité / Evolutivité
    • J’ai peur que l’ajout d’un nouveau processus métier (comme il est prévu dans le lot 2 j’imagine ?) ne se traduise par l’ajout de nouvelles colonnes dans cette table. Dans ce cas il faudra changer toutes les requêtes déjà développées (clauses WHERE, agrégations), toutes les métadonnées, et toutes les optimisations déjà réalisées. En somme il faudra tout refaire. A chaque évolution.
    • Enfin, si on s’enferme dans cette architecture, impossible de trouver un prestataire digne de ce nom qui assurera la TMA ou les évolutions sans d’abord tout refondre.

Bon et bien on le voit, si c’est une nouvelle théorie, c’est l’équivalent de remplacer les groupes sanguins par les signes du zodiaque pour déterminer la compatibilité dans les transfusions sanguines. De temps en temps ça va marcher, certes, mais sur le long terme…

Et sinon, comment modéliser ça de manière satisfaisante ?

En identifiant les dimensions utilisées pour chaque processus métier, leur grain, et en construisant les tables de fait en fonction (c’est dans le livre, ou dans le webcast) :

Oh la jolie étoile!

PS : Les périodes temporelles diverses (semaines, trimestres) sont gérées directement dans la dimension temps.

Là on dispose d’une constellation composée de 4 étoiles, qui utilise des dimensions conformées (partagées), qui répond aux problématiques de performance, de confort d’utilisation et de maintenabilité. Si on souhaite intégrer un nouveau processus métier, on ajoute une nouvelle table de faits, sans avoir à modifier l’existant. Chaque processus peut évoluer indépendamment des autres. Chaque amélioration d’une dimension profite à toutes les analyses.

De tout ça on en reparle lundi 10 décembre, aux Journées SQL Server 2012. Inscrivez-vous 😉

Pour ou contre les clefs étrangères dans le datawarehouse?

Mise à jour 19/12/2012 – Un épilogue intéressant par Fred Brossard!

Durant notre session sur la Modélisation Dimensionnelle, avec Jean-Pierre nous n’avons pas pu nous empêcher de provoquer la foule (en délire :)) en lâchant l’air de rien un « …de toutes façons les foreign keys, en décisionnel, on ne les implémente pas... ». C’était évidemment une petite provocation, qui a bien fonctionné, mais il est temps de faire place aux arguments!

Le pour:

  • Implémentation au niveau du moteur SQL de la contrainte d’existence de la clef étrangère

Le contre:

  • Perte de performance à l’insertion (visible à partir de volumétries « importantes », naturellement dû à la vérification effective de la contrainte)
  • Perte de la commande TRUNCATE sur les tables concernées
  • Implémentation au niveau du moteur SQL de la contrainte d’existence de la clef étrangère

Hum… Il me semble voir un élément en double dans ma liste…

Pourquoi mettre la contrainte d’existence dans les contres? Parce qu’en mode projet, lors de la fabrication du datawarehouse, vous allez vider et remplir des centaines de fois vos tables de faits et vos tables de dimension. Et même très souvent, vous allez recharger vos dimensions sans vous soucier de vos faits. Implémenter à ce moment là vos clefs étrangères, c’est vous obliger à vider dans l’ordre les bonnes tables, sans utiliser un TRUNCATE, ou alors supprimer et recréer vos clefs à chaque fois… Encore du boulot alors qu’on en a déjà assez. C’est pourquoi à mon sens pendant le projet, les clefs étrangères ne sont qu’un frein à l’itération rapide sur le design.

La question devient alors: comment garantir la contrainte d’existence d’un membre de dimension pour un ID présent en table de fait? Pour moi cela doit être fait par design, au niveau de l’ETL:

  1. On dispose initialement d’un « Membre Inconnu« , sur une ID technique comme le -1, dans les dimensions
  2. On alimente ses dimensions à partir des fichiers sources, afin de capturer à la volée les nouveaux membres qui peuvent arriver
  3. Lors du chargement des tables de faits à partir de ces mêmes fichiers, on effectue un LOOKUP sur la table de dimension pour aller chercher l’ID du membre correspondant
    • Soit le membre existe : pas de problème
    • Soit le membre est inconnu : on indique en dur l’ID technique (-1), et on vérifie que la valeur a bien été rejeté lors du chargement de la dimension (sinon c’est qu’on a un vrai problème: pourquoi le membre n’apparaît pas dans la dimension?)

Avec ce petit algorithme, on ne peut pas insérer un NULL dans la table de faits. D’ailleurs, rien ne vous empêche de rendre les colonnes de clefs de dimension NOT NULL dans vos tables de faits. Évidemment, avec cette technique, les DELETE sont interdits dans la table de dimension, sinon rien ne vous protège d’avoir un ID en table de faits qui ne remonte pas sur la table de dimension.

Maintenant que j’ai dit tout ça, ne croyez pas que je sois anti clefs étrangères, bien au contraire. Si vous êtes dans un datawarehouse qui doit effectuer des DELETE sur ses dimensions (chargements exotiques, purge de la profondeur d’historique…), il est plus que conseillé d’utiliser les foreign keys. Et d’une manière plus générale si vous n’avez pas confiance dans votre ETL, appelez les consultants… euh non pardon, utilisez les foreign keys (et appeler aussi les consultants! :)).

Dans tous les cas, le débat est ouvert dans les commentaires, n’hésitez pas à donner votre avis et vos arguments sur le sujet, je suis à l’écoute!

PS : Il faut rendre à César ce qui appartient à César, et celui qui a fait mon éducation sur ce sujet c’est évidemment David Joubert, expert technique de génie 🙂

Tour d’horizon: Techniques BI sur SQL Server

Désolé pour les amateurs de la partie business du blog, mais je suis dans une période technique et ça se sent dans les articles. Promis j’équilibrerai bientôt 😉

Comme la dernière fois, je vous livre quelques articles qui m’ont bien plu chez les bloggeurs BI:

Bonne lecture 🙂