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 ?

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 😉

Gestion des dates en Transact SQL

Ceci est un article technique (bases de données SQL), n’hésitez pas à l’ignorer si ça ne vous intéresse pas 🙂

Depuis quelques jours nous galérons avec mon équipe à identifier une anomalie de traitement plutôt pénible dans l’ETL qui charge tous les soirs le chiffre d’affaire.

Nous avons enfin identifié l’erreur: le compte de service qui exécute le chargement le soir est configuré en américain sur la production, alors qu’il est en français sur les plateformes de développement. C’est dommage lorsqu’une date de bascule au 1er mai est interprété comme le 5 janvier en production. En effet le 05/01/2011 devient 01/05/2011 avec un format de notation américain.

Sachant que changer le paramétrage d’un compte de service en production peut prendre 3 ans de conflits politiques entre les différentes équipes (pour 2 minutes d’implémentation), nous avons décidé de faire évoluer les requêtes SQL pour qu’elles n’utilisent plus aucune conversion implicite.  L’objectif c’est de rendre le code insensible aux paramètres de langue.

En gros en SQL ça veut dire que ça c’est interdit: CAST(‘2011-05-01’ AS DATETIME), de même que référencer directement une date en chaîne de caractère : WHERE MonDateTime = ’05/01/2011′

A la place on doit faire ça : CONVERT(DATETIME,’01/05/2011′,103), ou ça : CONVERT(DATETIME,’2011-05-01′,102). En utilisant CONVERT, on peut préciser le format de la date de manière explicite: 103 pour JJ/MM/AAAA et 102 pour AAAA-MM-DD.

Ne laisser aucune conversion implicite (date ou autre) est définitivement une bonne pratique qu’il ne faut pas oublier. Pour les dates, c’est aussi une info qu’il faut savoir transmettre aux utilisateurs et MOA qui requètent les bases en SQL.

Pour plus de détail:

Recherche textuelle sur SQL Server, c’est dur!

Pour faire suite à l’article d’avant-hier, celui sur la déformation de notre vision du monde que cause les outils que l’on emploie, je voulais vous parler de la recherche textuelle sur la plateforme SQL Server. Désolé si certains sont déçus mais oui c’est un point technique 🙂

Alors pourquoi faire le lien entre cette problématique et la recherche textuelle ?

Dans une base de données on a une obsession : tout mettre dans des tables composées de lignes et des colonnes. Naturellement dans ce modèle l’unité de traitement minimale est la cellule, l’intersection d’une ligne et d’une colonne.

Très bien, mais que faire quand la cellule contient une entité unique, comme demandée par la modélisation, mais que cette entité se décompose de manière complexe ? Je pense par exemple à un descriptif produit ou un commentaire client. Comment exploiter une information humaine, un avis, une description, comprise dans une chaîne de caractère, avec des outils qui ne savent pas vraiment travailler à ce niveau de granularité ?

Et bien on fait comme on peut, mais en général ce n’est pas très joli !

Pour parler d’une situation précise, je monte actuellement une solution décisionnelle qui stocke et analyse pratiquement toutes les informations concernant le parc informatique d’un grand groupe. On pourrait penser que sur ce domaine fonctionnel on n’aurait pas de surprises dans les données: que du technique ou du numérique. Et bien détrompez-vous, remonter l’ensemble des applications installées sur les postes, sur un parc de 30’000 machines, dans 10 langues (vive l’Unicode), ça donne 3 millions de lignes à brasser par jour…

Pour vous donner un exemple : j’ai environ 1500 valeurs distinctes par jour d’applications qui contiennent le mot ‘Microsoft’, dont 500 qui contiennent le mot ‘Office’… Là dedans je dois retrouver les différentes éditions d’Office 2003 et 2007 pour faire le suivi du licensing.

Miam !

Le minimum qu’on puisse dire c’est que j’ai un problème de qualité de données. L’approche que je préfère sur ce genre de problème c’est d’utiliser les outils décisionnels pour instaurer un cycle d’amélioration des données (Tip 131):

  1. On récupère tout dans le datawarehouse avec un premier cycle d’import
  2. On flag ce qui n’est pas bon / pas encore revu
  3. On génère des rapports pour que les opérationnels puissent corriger les systèmes sources et / ou proposer des nouvelles règles d’alimentation
  4. On implémente les changements, puis retour à l’étape 1

Bien ! Le problème c’est que pour faire l’étape 2 il faut pouvoir interagir sur la donnée : est-ce que mon descriptif d’application contient ‘Office’ ? Enfin, est ce qu’il contient ‘%Office’, ‘Office%’, ‘% Office %’, ‘%Office%’… La différence ?

  • Office’ : le seul résultat qui passe c’est « Office »
  • %Office’ : « Office », « LibreOffice », « Microsoft Office »
  • Office%’ : « Office », « Office 2003 », « OfficeCracker »
  • % Office %’ : « Microsoft Office 2003», la subtilité est dans les espaces autour du mot
  • %Office%’ : toutes les possibilités

Presque facile pour Microsoft Office, mais pas pour Adobe et toutes les variations de flash, ni pour IBM, ni Mozilla, ni pour toutes les autres en fait…Et ‘0ffice’ ? ‘Offisse’ ? On peut les oublier pour des champs fournis par les éditeurs eux-même, mais pour une recherche dans des champs saisis à la main ?

Pour pouvoir traiter tous ces cas de figure on va utiliser des critères de recherche compliqués, conditionnels, qui impliquent des traitements unitaires multiples sur chacune des cellules les unes après les autres: les performances s’écroulent. En temps de réponse on passe de moins d’une seconde sur une table bien indexée à plusieurs minutes sur une recherche en ‘%…’. En fait c’est une particularité dans la manière qu’ont les bases de données relationnelles de stocker et traiter l’information qui force une lecture complète de la table à chaque fois.

On retrouve cette limite là dans tous nos outils :

Ça plus le fait qu’il faille créer toutes les règles de gestion une par une de manière exhaustive. Vivement que les IA viennent nous filer un coup de main !

Malheureusement pour le moment il n’existe pas de solution magique, une recherche textuelle étendue sur une base de données relationnelles classique aura forcément un coût important en performance et/ou un résultat foireux. La prochaine fois que vous verrez un site web équipé d’un moteur de recherche bancal, ne cherchez pas pourquoi… Et c’est là que le bas blesse: à force de galérer avec ce type d’opérations on ne les propose plus, on ne les implémente plus, et on en vient à penser qu’elles ne sont pas possibles.

Pourtant Google y arrive, et sur des volumes de données autrement plus important! C’est d’ailleurs pour ça que pour l’avenir j’ai espoir dans la prochaine génération de bases avec moteur de stockage en colonne / vertical. Va bien y avoir un génie quelque part qui va nous révolutionner les recherches textuelles là-dessus!

Des lignes et des colonnes

Je me suis fait une drôle de réflexion ce matin en lisant cet article d’Alex Payne (un des premiers ingés de Twitter, maintenant CTO de BankSimple), et plus particulièrement ce paragraphe:

Even the most bureaucratic of technologies can’t be claimed to be un-opinionated or free from our values. The lowly SQL database, workhorse of dismal trades like accounting and business analytics, is theoretically “value-neutral” to the data it stores. Yet, in structuring data into rows and columns of particular standard types, a set of values emerges that dictates what information is and how it should be stored and queried.

Traduit grossièrement:

Même la plus basique des technologies est affectée par nos valeurs et nos opinions. La simple base de données SQL, moteur de basses besognes telles que la comptabilité ou l’analyse business, est en théorie neutre en valeur vis-à-vis des données qu’elle héberge. Pourtant, en structurant les données en lignes et en colonnes de types standardisés, un ensemble de valeurs apparaît et dicte ce qu’est l’information et comment elle doit être stockée et requêtée.

C’est tellement vrai!

Pour étudier un événement à travers un modèle relationnel, un modèle en étoile, on le force à prendre une forme qui ne lui est pas forcément naturelle. La question devient: quelle est la valeur de l’analyse si pour la réaliser il a fallut tordre les faits et les conformer à un modèle artificiel? On retourne ici en plein problème de « legibility » dont je parlais tantôt.

Alors évidemment, étant donné que la plupart des phénomènes que l’on doit modéliser dans l’entreprise sont artificiels, il est facile de les modéliser en utilisant un processus artificiel. Un flux comptable, un portefeuille financier, une masse salariale, une chaîne de production… ce sont des éléments inventés de toutes pièces par l’homme et qui donc se conforment facilement dans une base de données.

Mais quand on étudie des phénomènes plus libres comme des courants d’idées sur Internet, la manière dont les sociétés s’organisent et se désorganisent, la mode… les relations humaines en somme, et bien cette mécanique se grippe vite. C’est surement la raison pour laquelle la plupart les gros acteurs sur le web, les journalistes et bloggeurs data ou encore les chercheurs en sciences sociales, n’utilisent que très peu les bases de données SQL et préfèrent le NoSQL, BigData et les nouveaux outils de visualisation (R & co).

Il suffit de voir les résultats de leurs études sur FlowingData ou Information is Beautiful, et de considérer l’effort que cela prendrait de faire certaines de ces analyses sur une plateforme décisionnelle classique, quand c’est possible, pour bien prendre conscience du poids que nous impose le modèle relationnel.

C’est une état de fait tellement évident qu’on l’oublie trop souvent lorsque vient le moment de modéliser un nouveau système décisionnel. Or certaines activités de l’entreprise comportent des éléments à la limite du modélisable, des éléments pourtant cruciaux à la compréhension globale de l’activité. Je pense par exemple aux relations clients ou aux ressources humaines. Sur ces domaines il faut donc être particulièrement prudent, se souvenir de ces limitations, et prévenir les utilisateurs des limites de l’outil à analyser un phénomène qui par définition ne peut pas être modélisé correctement.

Un problème de Connection String?

Désolé mais c’est un point purement technique. Faut dire que de temps en temps ça fait pas mal non plus! 😉

Pour tout ceux qui ont un problème pour joindre une base de données, je leur conseille d’utiliser ce site absolument merveilleux qu’est ConnectionStrings.com, c’est magique!

Il référence la plupart des formats et options pour formater sa chaîne de connexion, ça m’a encore tiré du pétrin aujourd’hui!