Data Explorer

SSIS On-demand – publier un package en OData Feed

 

Lors des dernières Journées SQL Server, j’ai présenté une session sur la gestion des données avec Power BI.

La majeure partie de la session était consacrée à Power Query et j’ai fait une démonstration de la consommation de données en provenance d’un package SSIS, tout cela dans Excel. Explication de texte…

 

Préambule

La solution technique repose sur un composant SSIS & un composant SQL Server fournis par Microsoft qui sont passés totalement inaperçus (sauf si vous suivez le blog de Matt Masson, la référence internationale sur SSIS).

Ce composant est :

SSIS Data Feed Publishing Components

Vous pouvez le télécharger ici : Télécharger SSIS Data Feed Components

Vous avez également un tutorial sur le site de Microsoft (mais je vous recommande le mien Tire la langue) : Publish SSIS packages as OData Feeds

J’en avais également parlé brièvement lors de ma session Power BI du dernier SQLSaturday Paris.

 

Etape 1 : SSIS

Autant vous le dire tout de suite, il va falloir redévelopper vos packages. Pas de panique, c’est juste la destination de vos packages qu’il va falloir modifier.
(En même temps, si vous y repensez, c’est tout à fait logique car il faut bien rediriger le flux de données.)

imageAprès l’installation de SSIS Data Feed Components, vous avez un nouveau composant destination de Data Flow : Data Streaming Destination

Il suffit juste d’envoyer les données que vous souhaitez rendre disponibles sous forme de Feed dedans.

image

Ici, je fais un bête transcodage entre une table et un fichier CSV, rien de bien passionnant mlais suffisant pour la démo.

 

Etape 2 : publication

Microsoft focalise sur la publication sous la forme de Data Feed (ou OData Feed). En réalité, techniquement, le composant SSIS Data Feed n’a rien à voir avec un feed.
Il publie tout simplement un package SSIS (je devrais dire un Data Flow) sous la forme d’une vue SQL.

Le composant que vous avez installé propose également un assistant que vous trouverez dans votre menu écran Démarrer sous le nom de Data Feed Publishing Wizard.

image

Ce wizard vous demande en premier lieu le package à publier. Il faudra donc au préalable déployer votre package (votre .ispac) dans un catalogue SSIS.

image

Ensuite, l’assistant vous propose de saisir une configuration pour votre package.

image

Ensuite, il vous demande où vous souhaitez publier le flux. On voit bien ici que la publication est sous la forme d’une Vue dans une base SQL Server.

image

Quelques paramètres comme la version du Runtime SSIS (32 ou 64 bits) ou le timeout mais aussi, l’assistant parle de Linked Server (??!?).

On voit d’ailleurs dans l’étape de validation qu’il installe et configure un serveur lié dans votre SQL Server

image

C’est là toute la magie de cette technologie. C’est en fait un driver OLEDB, utilisable comme un banal serveur lié, qui lance notre package et récupère les données streamées par le composant Data Streaming Destination.

En faisant un tour dans Management Studio, on voit bien les éléments :

image

Pour requêter mes données, je fais juste un simple SELECT :

SELECT * FROM dbo.MaVueSSIS

Cela permet un usage purement SQL Server et pas forcément avec un Feed OData.

 

Etape 3 : transformer en OData Feed

La troisième étape, c’est de transformer notre vue SQL en OData Feed. Pour cela nous allons avoir besoin de Power BI car il contient un module permettant d’exposer des données “simplement” à des utilisateurs. Ce module, c’est la Data Management Gateway mais pour que l’expérience soit complète, nous allons avoir besoin de Power Query et d’un site Power BI.

En raccourci, ça donne cela :

image

Je vous la fait courte sur la création de la Gateway entre votre tenant Power BI et votre SI (votre SQL Server si vous préférez).

  1. Téléchargement et Installation de la Data Management Gateway
  2. Configuration et ouverture de flux (port 8051 par défaut)
  3. Création d’une Gateway sur l’IT Admin Portal de votre tenant Power BI
  4. Configuration de la clé pour que votre DMG et Power BI communique

image

Pour publier notre package sous la forme d’un OData Feed, il suffit simplement de configurer une nouvelle source de données sur votre Gateway dans l’IT Admin Portal de votre site Power BI et ensuite d’exposer la vue SQL.

image

Dans les paramètres de la Data Source, on n’oubliera pas d’activer l’option OData pour que la source soit accessible de Power Query

image

 

Etape 4 : consommation depuis Power Query

La dernière étape, c’est de consommer notre package depuis Excel. Pour cela, je me rends dans Power Query, je lance une recherche et je n’ai qu’à “utiliser” la bonne query

image

On remarque bien que la source de données est vue par Power Query comme un flux OData et non comme une vue ou une table SQL Server.

OData, ça veut dire que le données passent par le Cloud ?

On est en droit de se poser la question mais qu’on se le dise de suite, les données ne transitent pas par le Cloud.

Pourtant, l’URL du feed OData est bien sur Azure ?!?

Voici ce qui se passe :

  1. La recherche interroge le Data Catalog qui contient les méta-données des Queries partagées et des Sources publiées, notamment l’URL
  2. Excel (Power Query) contacte le Cloud à l’URL du Feed OData
  3. Le Cloud lui donne les infos de connexion à la Gateway qui référence la source
  4. Excel communique directement avec la Data Management Gateway (et donc ne passe pas par le Cloud)

On peut vérifier cela avec un outil comme Fiddler qui trace les échanges HTTP.

image
(Cliquer pour agrandir)

 

Cela donne à peu près cela :

image

 

Et la sécurité ?

Bonne question. Mais j’y répondrai dans un prochain article.

 


Et concernant la session des JSS 2013, les Slides et vidéo seront bientôt disponibles, suivez lkes news sur http://GUSS.Pro ou Twitter (@GUSS_FRANCE).

#PowerBI for Office 365

SQL 14 est en Preview depuis fin juin et pas un mot sur ce blog ?
Je sais, c’est moche et je n’ai pas d’excuses…

imageAlors pour me rattraper, je n’allais pas louper les grosses annonces concernant la BI faites aujourd’hui à la Worldwide Partner Conference (WPC).

Et pourtant, je suis en retard, car si vous suivez les réseaux sociaux anglophones, vous avez dû voir fleurir de nombreux Power trucs

Allez, faisons un tour rapide des annonces. Moi ou mes confrères de la communauté les détaillerons dans les semaines qui arrivent.

Entrez dans la Power BI

http://office.com/PowerBI

Power BI, c’est le mot magique qui regroupe maintenant les briques de Self-Service BI de Microsoft. Evidemment, la Self-Service est centrée autour de la famille Office.

Le point important des annonces, c’est que tout cela prend forme dans le Cloud avec une offre cohérente et quasi complète pour les utilisateurs d’Office 365.

Avec votre offre O365, vous aller bénéficier des outils de Self-Service BI dans des BI Sites sur SharePoint prêts à l’emploi.
De quoi monter une plate-forme de Self-Service BI en quelques clics (et donc en quelques minutes).

imagePower BI Site

Et mes données Corporate ?

Une plate-forme BI dans le cloud ? Mais mes données sont dans mes DataWarehouse on-premise ?

En effet, on peut s’étonner de cette approche Cloud-First mais qu’on se rassure, vos données maison sont atteignables. Le Cloud sait depuis quelques temps déjà se transformer en une extension de votre Système d’Informations. C’est le cas pour Office 365.

En revanche, cela vous oblige à réfléchir à vos flux de données, à la sécurisation du réseau et des données, etc.

Les rôles d’architectes deviennent Hybrides.

Data Explorer devient Power Query

Data Explorer, la brique de façonnage et de nettoyage de données,  arrive en phase finale avec la mise à disposition de la RTM (??) de l’outil.
Je ne vous fais pas l’affront de revenir sur ce que fait cet outil, je le présente depuis début 2012 : http://blog.djeepy1.net/tag/data-explorer/

Au passage, il prend son nom définitif : Power Query.

Il reste sous la forme d’un addin Excel mais j’ai bien l’impression que les mashups sont prévus pour fonctionner dans votre Office 365 également (à tester).

GeoFlow devient Power Map

GeoFlow, l’addin Excel de représentation et de navigation 3D de données géographiques, présenté lors de la dernière SharePoint Conference prend son nom commercial : Power Map.

Je pense que l’outil restera encore pour l’instant dans Excel sur le poste client étant donné qu’il s’appuie sur les capacités GPU de votre carte graphique.

Toutefois, il prend bien sa place comme un outil de DataViz de l’offre de Self-Service BI de Microsoft.

PowerPivot devient Power Pivot

La première brique de la Self-Service BI de Microsoft, posée en 2009 est toujours au cœur de l’offre.

Rien ne change sur l’outil, ses fonctionnalités et son usage. En revanche, son nom prend un caractère de plus avec un espace entre Power et Pivot (pour que tous les noms restent cohérents).

La grande différence vient de son usage Cloud. On pouvait certes déjà télécharger des classeurs Power Pivot dans un site Office 365 (version 2013 de votre tenant) mais avec une limitation rédhibitoire : quelques Mégas seulement.
Maintenant, c’est 200Mo la taille limite pour un classeur dans O365, de quoi faire de beaux modèles.

Power View

Power View semble être mis en avant comme l’outil de restitution de données dans cette offre de Self-Service BI. Associé à Power Map dans les annonces, il ne semble pas y avoir de nouveauté annoncée. (sauf peut-être la mobilité – cf. plus bas)

Enterprise Data Search

La recherche que vous avez pu tous tester dans Data Explorer Power Query s’étoffe avec la possibilité de rechercher dans ses données d’entreprise en plus des données publiques.

…so customers can query data from within their business and from external data…

J’espère pouvoir vous en parler plus en détail dans peu de temps.

Data Stewardship et télémétrie sur les sources de données et les usages

Evidemment, qui dit sources de données dit Gouvernance desdites données. Les sites Power BI proposent des fonctionnalités pour suivre les sources utilisées et avoir des métriques dessus.

On parle donc d’une Data Management Gateway pour les nouveaux pilotes des données (Data Steward) qui voient leur rôle s’accroitre/apparaitre.

Requêtage en langage naturel

Une belle nouveauté qu’il me tarde également de tester: le requêtage de vos données en langage naturel.

Kézako ? En fait, il s’agit d’écrire la BI que vous souhaitez voir comme dans un moteur de recherche : “je veux les ventes en volume et en valeur des cigares cubains par pays européens

La puissance de la fonctionnalité est qu’elle propose via la complétion des propositions ou des variantes (par année, par rang, etc.), ce qui montre qu’il y a une vraie taxonomie et une analyse des données elles-même (faits et membres de dimensions) derrières.

Quand on vous dit depuis quelques temps (Tabular) que les méta-données sémantiques sont importantes dans un modèle dimensionnel !!
Sans nul doute qu’il y a un lien avec la recherche de Power Query.

image

image

BI Mobile

Je pensais que ce serait le plus gros de l’annonce puisque nous n’avions eu que très peu de nouvelles depuis la démo de Power View sur un iPad au PASS Summit de 2011. (http://blog.djeepy1.net/2012/11/09/bi-mobile-roadmap/)

L’annonce ne fait que confirmer que la réponse Mobile de la Business Intelligence Microsoft arrive. Les 2 applications iOS et Windows RT sont confirmées et nous avons même leur petits noms : Power BI for Windows RT et Power BI for iPad

En plus de ces applications dédiées, c’est maintenant officiel que TOUS les outils Power BI sont compatibles HTML5.
Oui, tous, ce qui inclut Power View Sourire. Par contre, aucune démo…

image

 

Alors, on y va ?

Eh bien non, pas encore Triste.

En effet, les annonces ont eu lieu, le marketing verbatim est défini, le mode d’utilisation aussi et pourtant, ce n’est pas encore en RTM.

J’entends déjà les rabats-joie se plaindre que ça manque de démos, de screenshots, de précisions, de dates, de prix, etc.

Je n’ai pas de date à vous communiquer mais enregistrez vous sur e site Office.com/PowerBI pour être notifié du top départ. Je suis autant frustré que vous et j’ai hâte de pouvoir approfondir tout cela.

 

Quelques liens pour en savoir plus :

Webcasts DataExplorer et GeoFlow

Je ne vous rappelle pas que le GUSS organise des sessions en ligne tous les mois (dernier mercredi).

J’ai présenté la deuxième série consacrée aux récents addin Excel, très liés à la Self-Service BI.

Data Explorer : un addin ajoutant des fonctionnalités de Self-Service ETL à la BI personnelle de Microsoft. La brique qui manquait pour offrir une solution (quasi-)complète aux utilisateurs

GeoFlow : un addin qui permet de visualiser et de naviguer en 3D dans des données géographiques, très orienté Story-Telling.

Les slides et les vidéos sont disponibles sur le (nouveau) site du GUSS.
Il faudra vous connecter pour y accéder (et ainsi mieux vous connaitre).

Webcast et slides Data Explorer

Webcast et slides GeoFlow

image

2 Webcasts du GUSS ce soir

Un court billet pour vous rappeler que ce soir, j’anime au GUSS deux sessions online.
Je vous rappelle le principe, tous les mois (le dernier mercredi), le Groupe des Utilisateurs SQL Server organise 2 webcasts de 30 minutes. Un format court pour découvrir ou approfondir des sujets autour de SQL Server.

Au programme pour finir ce mois de mai

Data Explorer :
Sorti le 27 février en version CTP (Community Public Preview), cet addin Excel est un véritable ETL en mode Self-Service, vous permettant de nettoyer et façonner vos jeux de données avant de les exploiter dans PowerPivot.

Geoflow :
Présenté lors de la PASS Business Alanytics Conference le 11 avril dernier, cet addin Excel permet de visualiser des données géographiques sur une carte en 3D. Vous découvrirez cet outil de visualisation de données très puissant. Vous pourrez poser vos questions dans le chat associé à l’événement.

Mercredi 29 mai – 19h00

Rejoindre la session en ligne

Pont Audio : +33157757766
Conference ID: 903453896

Point technique : pensez à valider votre configuration. Nous vous recommandons d’utiliser le client Lync Attendee 2010.

Une page sur le site du GUSS est à votre disposition pour savoir si votre configuration est compatible et ce que vous devrez installer/désinstaller.

Page d’aide pour se connecter aux sessions en ligne.

Data Explorer–fonction unpivot

Ce matin une bonne nouvelle de la part de l’équipe Data Explorer : la fonction Unpivot arrive !

Blog Data Explorer – Latest update

Dès mes premiers tests sur PowerPivot (Octobre 2011), j’ai été bloqué par l’absence de cette feature. J’ai même essayé de la développer avec une fonction M récursive à base de SelectNames() mais je ne suis pas arrivé au bout.image

Mais aujourd’hui, j’ai juste à faire un update de l’addin directement dans Excel.

La version est la 1.0.3264.4.

Voyons le cas d’usage concret.

Vous savez que je base une bonne partie de mes démos sur des données Open Data fournies par la France (très pauvre sur le sujet mais on a quand même quelques chiffres).
Le jeu de données est une feuille Excel crado contenant la fréquentation des musées d’Ile-de-France :

http://www.data.gouv.fr/DataSet/30382387

image
On passera sur le fait que l’administration française n’ait aucune notion sur les datasets et la qualité de données (on voit bien qu’un fonctionnaire a juste déposé son fichier Excel 97 sur le site).

Concentrons nous sur le fait que les années sont en colonnes. Pour faire une jolie table de faits et ainsi avoir un modèle facilement exploitable (croissance annuelle, slicer temporel, play axis dans un Scatter Graph ou dans GeoFlow…), il nous faut pivoter la table.

Les utilisateurs d’Excel me diront (m’ont déjà dit) :

“c’est facile, tu fais un Transpose”

En fait, le transpose inverse ligne et colonne, il ne pivote pas une ou plusieurs colonnes. Le seul outil que je connaisse capable de faire cela est SSIS.

C’est parti. Pour commencer, importons les données ; pour cela, pas besoin de mettre le fichier Excel sur votre bureau, il suffit directement de laisser Data Explorer aller le chercher.

image

On fait un peu le ménage dans les lignes et les colonnes pour ne conserver que le tableau de données

image

Dans le détails, ça donne :

  • Remove Top Rows
  • Use First Row as Headers
  • Change Type to Number (colonne 1)
  • Filter Value <> null et Number.NaN (colonne 1)
  • Hide Columns “Evolutions xxx”

Ensuite, on lance la fonction de Unpivot

image

Il faut sélectionner l’ensemble des colonnes que l’ont souhaite pivoter. Le nom de la colonne devient la valeur d’une nouvelle colonne “Attribute” et la valeur de la cellule devient la la valeur de la nouvelle colonne “Value”.

Evidemment, tout cela est paramétrable dans la fonction M:

= Table.Unpivot(FilteredRows,{"TOTAL#(lf) 06", "TOTAL#(lf) 07", "TOTAL#(lf) 08", "TOTAL#(lf) 09", "TOTAL#(lf) 10"},"TOTAL","Fréquentation")

Petite subtilité

Dans notre exemple, nous devons pivoter 2 fois : la fréquentation totale et la fréquentation gratuite. Le problème c’est qu’en pivotant 2 fois, on génère la combinatoire des années “totales” et “gratuites”.

image

L’astuce que je prends est la suivante :

  • je transforme la colonne pour n’avoir que l’année
=Table.ReplaceValue(ReplacedValue1,"GRAT#(lf)","20",Replacer.ReplaceText,{"GRATUIT"})
  • je filtre pour ne garder que les lignes quand les 2 colonnes sont égales
=Table.SelectRows(ReplacedValue2, each [TOTAL] = [GRATUIT])

 

Et voila le résultat :

image

Replay de mes sessions aux TechDays

 

Etat de l’art et Roadmap de la BI Mobile

image

 

Gouvernance des données et Self-Service ETL avec Data Explorer

image

 

Et pour revoir TOUT les Techdays :  Vidéos des sessions 2013

Data Explorer version 2013

Préambule

Si vous aimez connaitre tout l’historique voici mes posts sur la version “alpha” de Data Explorer, quand il était encore incubé par les Azure Labs :

 

Pour démarrer, il vous faut l’add-in Excel :

Data Explorer v1.0.3231

C’est bon, vous l’avez installé, c’est parti.

Recherche online

Le principe de la recherche “online” est super simple : vous tapez juste des mots-clés pour trouver des données, comme vous le feriez dans votre moteur de recherche habituel.

Data Explorer se charge de trouver des DataSets correspondant à votre recherche. Pour le moment, il n’y a que des données en provenance de Wikipedia (en anglais) mais on imagine bien avoir de nombreuses sources “publiques” à disposition dans la version finale et notamment le Azure Data Market (disponible mais pas en mode recherche).

image

Pour utiliser un Dataset comme source de données, il suffit de cliquer sur Use dans le panneau de recherche et la fenêtre de Mashup de Data Explorer s’ouvre.

 

Les nouveaux connecteurs

Data Explorer a conservé ses sources principales : contenu web (HTML, JSon, OData, SharePoint), fichiers locaux ou sur le web (texte, csv, XML, Access, Excel) et SQL Server.

La liste des bases de données supportées s’est considérablement agrandie. On peut se connecter maintenant à Azure, MySQL, Oracle, DB2, PostgreSQL et Teradata. On regrette toutefois qu’il n’y ait pas tout simplement un support de OLEDB ou de ODBC.

On trouve aussi maintenant des sources Active Directory (ce qui montre bien que les données de l’entreprise sont bien la cible), HADOOP (HDFS et HDInsight) et même l’Open Graph de Facebook.

Enfin, très naturellement puisque maintenant on est dans Excel, une simple table Excel devient également une source de données.

 

La nouvelle interface

C’est évidemment du côté de l’interface qu’il y a eu de grands changements.

image
Version Azure Labs

imageVersion Excel

On retrouve toutefois les éléments qui composent notre mashup de données mais organisés de différentes façons pour apporter un maximum d’intuitivité à l’utilisateur dans le façonnage de son Dataset.

  • imageNavigator : la zone à gauche de la fenêtre est une zone de navigation dans la source de données.
    En fonction du “provider” des données choisi comme source, on va pouvoir naviguer pour rechercher le Dataset voulu.
    C’est le cas pour un fichier Excel avec les feuilles de données mais aussi pour une base de données la liste des bases puis des tables.
    Cela montre bien que Data Explorer tire partie de chaque Provider en lui déléguant une partie du travail. J’en reparlerai dans un prochain article mais c’est pareil pour un filtre, une jointure ou une agrégation, elle sera faite au niveau le plus bas possible.
  • Steps : la zone à droite de la fenêtre liste toute les étapes de notre mashup. Elle remplace le “fil d’ariane” qui se trouvait au dessus du Dataset dans la précédente version. Cela permet de revenir à une étape pour la modifier ou la supprimer.
    Je la trouve d’ailleurs plus rapide et pratique que la précédente.
  • La barre d’outils : mais où est-elle passée ? Dans la précédente version, elle occupait tout un bandeau en haut de l’écran et là, plus rien, place aux données…
    En fait, toutes les actions / transformations sont maintenant accessibles dans leur contexte avec un simple clic-droit. Par exemple, si on veut masquer une colonne du dataset qu’on utilise pas, il suffit de faire un clic-droit sur son entête.
    Les zones cliquables sont les entêtes de colonnes, les valeurs (contextualisées) et le tableau lui même (dans le coin en haut à gauche)
    Plus intuitif (ou pas ?), en tout cas on retrouve ses petits assez facilement je trouve.
  • La barre de formules : l’un des atouts de Data Explorer pour un profil ‘”développeur” comme moi, c’est de savoir qu’on peut développer soit même les fonctions de transformations.
    Pour cela, on a une barre de formules, comme celle d’Excel ou de PowerPivot.
    Le langage est toujours le langage M et l’API est plutôt riche.

image

Les transformations

imageComme je le disais, les transformations sont maintenant accessibles directement dans le Dataset. A première vue, je n’ai pas noté de nouvelles transformations ou de transformations en moins. (il me manque toujours le Pivot/Unpivot…)

Toutefois, en jouant un peu avec les données, il y a 2 nouveautés que j’ai bien apprécié.

La première, c’est la possibilité de récupérer le contenu des fichiers d’un répertoire (pas juste les noms) ce qui permet d’adresser des scénarios de concaténation de fichiers textes par exemple.

image

La seconde est dans la même lignée. En fonction de la source de données, on peut suivre les “jointures” possibles. Ca marche avec les relations entre des tables d’une base de données par exemple. On peut donc choisir de récupérer de nouvelles colonnes ou bien agréger des données relatives. On utilisera aussi cette façon de faire pour dérouler du contenu JSON, HTML ou XML.

image

Cette nouveauté est vraiment une tuerie !

 

Mixer les sources

Enfin, pour compléter l’expérience de Mashup, il ne manque que la liaison entre plusieurs Datasets.

imageDans la précédente version, on avait tous nos Datasets sur la gauche et des options pour faire des UNION, des MERGE ou des copies/références.
Data Explorer en version Excel garde toutes ces fonctionnalités mais réparties différemment dans l’interface.

 

Déjà, sachez que chacun de nos Dataset est appelé Query (on verra la traduction qui sera choisie). Pour les retrouvez, c’est simple allez sur la table résultante et vous aurez les toutes les options dans le ruban Excel (Data Explorer ou Table Tools).

imageimage

 

Et mes données, j’en fais quoi après ?

Cette question n’a qu’une réponse : OBVIOUS !

Vous êtes dans Excel, non mais allô quoi ? vous me recevez ? Vous êtes dans Excel ? Genre tu fais de la BI et tu connais pas PowerPivot ? Non mais allô quoi…1

En fait, c’est simple vous injectez votre Dataset dans PowerPivot (ie. dans votre Data Model si vous êtes dans 2013) et vous n’avez plus qu’à faire vos analyses et vos tableaux de bord.
Vous pouvez même faire un rapport Power View.

 

En conclusion

Concernant les fonctionnalités, pas de grandes évolutions par rapport à la version alpha mise à part de nouvelles sources de données. Je retrouve tout ce qui m’a séduit dans l’outil dans sa précédente version.

Toutefois, on doit noter que  la navigation dans les sources de données est vraiment améliorée ce qui simplifiera la vie des utilisateurs qui ne savent pas ce qu’est une jointure.

Le grand changement est évidemment les interfaces très intuitives et bien dans le style Excel 2013. Et justement, le fait d’être dans Excel permet un usage instantané des données. (oubliez le Publish mis en avant dans la précédente version)

 

Pour aller plus loin

Pour rester informé, suivez ce blog (je mettrai d’autres tutoriaux) ou celui de l’équipe produit :

http://blogs.msdn.com/b/dataexplorer/

 

1 : si vous n’avez pas compris ce passage, c’est que vous n’avez pas suivi le buzz du moment.