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 😉

Power Pivot vers Tabular : I need you!

J’ai besoin d’un coup de main de votre part à tous ! Hier j’écrivais un édito sur le futur de la BI Microsoft, et l’une des idées (améliorer la montée de Power Pivot vers SSAS Tabular) me tient particulièrement à cœur.

J’ai donc écrit un petit message sur le sujet aux copains MVP, et Chris Webb m’a rappelé l’existence d’un ticket Connect de Marco Russo en plein dedans.

Alors vous me voyez arriver avec mes gros sabots, ce serait vraiment vraiment cool si vous alliez voter sur ces différents items pour les faire monter au plus haut dans la liste. Ca aura un vrai impact, les items bien notés étant effectivement lus et considérés par l’équipe de développement de SQL Server.

Une vraie équipe!

Pour voter il suffit juste de disposer d’un compte « Microsoft » (Live ID/Hotmail/celui utilisé pour vos certifs/moi c’est une adresse gmail…), vous pourrez en créer un sur place si besoin, ça prend 2 minutes.

Voici les items concernés:

Merci de voter, et merci de faire passer le mot !

Mettre bout à bout (enchainer) des sources de données dans Excel avec Power Query (Data Explorer)

Un cas d’usage qui pouvait être reproché à Excel, et à Power Pivot également, c’était l’impossibilité de prendre 2 ou plus sources de données identiques et de les mettre bout à bout (en mode UNION pour les SQLeux). Typiquement : j’ai un fichier CSV par mois dans un répertoire, et j’aimerais tous les mettre dans le même tableau Excel pour travailler dessus. Jusqu’alors, la seule solution c’était les copiés/collés successifs à la main, avec tous les risques que ça comporte.

Heureusement Power Query (anciennement Data Explorer) vient à la rescousse ! C’est quoi ? C’est un add-in Excel gratuit, édité par Microsoft, qui sert de mini-ETL à Excel. Sincèrement, c’est peut-être l’outil de BI Self-Service qui enflamme le plus les consultants décisionnel Microsoft en ce moment, alors allez l’installer!

Le mieux pour vous convaincre étant sans doute de passer à la pratique, voici comment employer la chose :

Scénario 1 : 3 tableaux dans le même fichier Excel

  • Pour l’exemple j’ai créé 3 tableaux dans Excel 2013, dans la même feuille ou pas, ça ne change rien, qui contiennent des données à la structure identique:

Append_1

  • Le but : créer une « Query », une requête dans Power Query, sur chacune des tables, pour ensuite les associer en un seul résultat
  • Etape 1 (à répéter 3 fois, une fois par tableau) : a. Je clique dans un tableau (si ce n’est pas fait, n’oubliez pas de « Mettre sous Forme de Tableau » vos données), b. Je vais dans l’onglet Power Query, c. Je créé une Query depuis un Tableau (From Table). Notez que si l’onglet Power Query n’apparait pas, le mieux est de valider qu’il est bien activé (Fichier > Options > Compléments > Gérer : Compléments COM : « Microsoft Power Query for Excel » doit être coché)

Append_2

  • En bleu : l’interface de transformation de données de Power Query, ici on a besoin de rien, donc on valide (Done):

Append_3

  • La Query est créée, elle correspond à un tableau dans un nouvel onglet Excel:

Append_4

  • On répète l’opération 3 fois, une fois par tableau pour obtenir les onglets suivants (oui mon Excel est en anglais, Sheet ou Feuille c’est pareil ;)) :

Append_5

  • Etape 2 : a. On va dans l’onglet Power Query, et b. on choisit « Append »

Append_6

  • Dans l’interface, je choisis 2 Queries, l’ordre n’importe pas ici. Vous noterez que les noms affichés sont les noms des Queries, qui par hasard correspondent ici aux noms des Tableaux. Si vous avez renommé vos Queries, vous retrouverez les nouveaux noms ici:

Append_7

  • Encore une fois, on passe par l’interface de transformation des données de Power Query, encore une fois ici on ne change rien, on valide (Done) :

Append_8

  • Et hop, je me retrouve avec une nouvelle Query qui est la mise bout à bout des 2 premières tables :

Append_9

  • Je répète l’opération, cette fois on prenant dans l’Append le résultat de mon premier Append (Append 1) et le tableau que je n’avais pas pris la première fois :

Append_10

  • Je valide dans l’éditeur et hop, me voilà avec la table complète !

Append_11

  • Chouette non? Alors on peut se dire que c’était beaucoup d’étapes, alors que 3 copiés/collers suffisaient à faire la même chose. Certes. Mais l’avantage énorme des Queries, c’est qu’elles se mettent à jour (cliquer pour zoomer) :

Append_12

  • C’est quand même bien fait non ? 🙂

Scénario 1 – Avancé : Optimisation sur 3 tableaux dans le même fichier Excel

Alors ok, c’est mignon, mais avec la manipulation précédente on se retrouve quand même avec 5 feuilles, et si on ajoute d’autres sources ça va rapidement faire beaucoup trop (2n-1 feuilles avec n sources). Évidemment on peut faire bien mieux, mais il va falloir remonter les manches 😉

  • D’abord il va falloir activer le mode avancé

Append_13

  • Et tant qu’on y est, activez le Fast Combine, ça vous évitera des soucis plus tard :

Append_14

  • Désormais, vous allez pouvoir directement éditer les formules que Power Query utilise pour transformer les données. On retourne donc dans l’interface de transformation des données de Power Query en utilisant une de ces 2 méthodes (ab,ab’c’), ou en créant une nouvelle Query :

Append_15

  • Et dans cette interface, si vous êtes bien passé en mode avancé (voir plus haut), vous devez avoir un petit parchemin à cliquer :

Append_16

  • Qui amène à l’interface de saisie des formules :

Append_17

  • Je vous laisse avec le guide de référence pour voir tout ce qui est possible de faire, perso j’ai la même démarche que dans cet article, je fais toutes les étapes unitairement, et j’essaye d’optimiser ensuite à la main. Dans notre cas :

Append_18

  • En version copiable, à adapter en fonction des noms de vos tableaux (Table1/Table2/Table3) :
let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Source = Table.Combine({Source1,Source2,Source3})
in
Source

Avec cette formule on fait tout en une seule Query, donc un seul onglet, peu importe le nombre de source. Pas mal non ? 🙂

Scénario 2 : 3 fichiers Excel distincts, toujours avec le même format de données

  • Alors de la même manière, on peut faire les fichiers Excel. Je vous donne directement le script, à vous de jouer en manuel pour bien comprendre !

Append_19

let
Source2006 = Excel.Workbook(File.Contents("C:\Temp\2006.xlsx")),
Dataset2006 = Source2006{[Name="Sheet1"]}[Data],
FirstRowAsHeader2006 = Table.PromoteHeaders(Dataset2006),
Source2007 = Excel.Workbook(File.Contents("C:\Temp\2007.xlsx")),
Dataset2007 = Source2007{[Name="Sheet1"]}[Data],
FirstRowAsHeader2007 = Table.PromoteHeaders(Dataset2007),
Source2008 = Excel.Workbook(File.Contents("C:\Temp\2008.xlsx")),
Dataset2008 = Source2008{[Name="Sheet1"]}[Data],
FirstRowAsHeader2008 = Table.PromoteHeaders(Dataset2008),
SourceComplete = Table.Combine({FirstRowAsHeader2006 ,FirstRowAsHeader2007 ,FirstRowAsHeader2008 })
in
SourceComplete

Scénario 3 : 3 fichiers CSV, toujours avec le même format de données

  • Je vous donne directement le script :

Append_20

let
Source2006 = Csv.Document(File.Contents("C:\Temp\2006.csv")),
FirstRowAsHeader2006 = Table.PromoteHeaders(Source2006),
Source2007 = Csv.Document(File.Contents("C:\Temp\2007.csv")),
FirstRowAsHeader2007 = Table.PromoteHeaders(Source2007),
Source2008 = Csv.Document(File.Contents("C:\Temp\2008.csv")),
FirstRowAsHeader2008 = Table.PromoteHeaders(Source2008),
SourceComplete = Table.Combine({FirstRowAsHeader2006 ,FirstRowAsHeader2007 ,FirstRowAsHeader2008 }),
ChangedType = Table.TransformColumnTypes(SourceComplete ,{{"Region", type text}, {"Categorie", type text}, {"Annee", type number}, {"Valeur", type text}})
in
ChangedType
  • Une petite remarque en passant : attention avec les CSV, pour le moment l’interface de Power Query ne permet pas de gérer les séparateurs différents de la virgule, ni nos encodages européens facilement. Pour se faire, il faut modifier sa formule source comme suit:
Csv.Document(File.Contents("C:\Users\Florian\Desktop\2006.csv"), null, ";" , null, 1252)

Avec : votre séparateur, et votre encodage (par défaut 1252 sur Windows, voir le PDF de référence pour les autres – recherchez TextEncoding).

Et après ?

Soit on utilise directement le tableau résultat des Queries Append dans un tableau croisé dynamique, soit on l’ajoute au Data Model et c’est parti pour Power Pivot !

Amusez-vous bien, et n’hésitez pas à venir partager vos trouvailles 😉

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 😉

Petits pièges de la colonne de texte libre en attribut de dimension dans SSAS

La petite anomalie retorde de la semaine nous vient de la colonne de texte libre montée en attribut de dimension dans SSAS.

Exemple de texte libre par David Stewart

Pour un peu de contexte sur le sujet, voir cet article de David Stewart, ou ce topic sur StackOverflow.

L’erreur usuelle dans cette situation c’est d’avoir la valeur NULL corrigée automatiquement par SSAS en chaîne vide (‘’), en conflit avec une éventuelle chaîne vide déjà existante. SSAS crie alors au loup à travers un message relativement clair (« Duplicate attribute key ») qu’on apprend vite à reconnaître.

Aujourd’hui j’ai eu droit à un bonus (« Attribute key not found »), dans le même contexte. Le conflit venait de la chaîne composée d’un espace ‘ ‘ avec la chaîne vide ‘’. En effet malgré la propriété de Key Column Trimming de l’attribut passé à « None » (indiquant en théorie à SSAS de préserver les espaces), l’attribut ‘ ’ disparaissait lors du process de la dimension.

A mon sens un trimming implicite a lieu durant l’une des opérations du process, mais pas toutes. Arrive un moment où SSAS ne retrouve plus ses petits, et c’est le drame. Si un ninja de SSAS veut nous en dire plus, c’est avec plaisir 🙂

La correction est  simple : effectuer le Trimming explicitement (Right ou LeftRight), ainsi tous les accès à la valeur se feront de la même manière et SSAS arrivera à linker tout le monde. S’il vous faut préserver la valeur espace, je vous laisse jouer avec les différentes valeurs des propriétés de colonne clef, ainsi qu’avec les différentes collations de vos serveurs, mais j’ai bien peur que ce soit un bug (j’ai la flemme de chercher sur le connect…). Si par contre c’est juste la différenciation entre les 2 valeurs que vous voulez préserver, n’hésitez pas à corriger la valeur ‘ ’ en par exemple ‘(space)’ dès l’ETL.

D’une manière générale, je vous recommande les settings suivants pour vos colonnes d’attributs en texte libre (sauf la collation, évidemment, il faut s’adapter à la source ;)) :

SSAS - Free Text Dimension Attribute Properties

N’hésitez pas à m’indiquer vos astuces, et amusez-vous bien 😉