Business Intelligence

Session sur le Data Stewardship au #SQLBits

Voilà, ça c’est fait ! Notre tournée internationale en tant que speakers s’achève avec une session au SQLBits, en Angleterre.

Le SQLBits, c’est la plus grosse conférence SQL Server en Europe. Organisée depuis 2007 par la communauté du Royaume-Uni (très riche et très active), ils en sont à leur 12 édition, gage de sérieux. Je participais à mon premier SQLBits et je dois dire que j’ai été bluffé par la qualité et l’importance de l’événement.

  • 1300 participants (!!)
  • 3 jours de conférences dont 1 journée gratuite consacrée à la communauté
  • 8 tracks, + de 100 sessions
  • 1 “Distingued Engineer” de l’équipe SQL Server en Keynote : Niguel Ellis
  • Des speakers venus des 4 coins du monde
  • 1 soirée absolument A-WE-SO-ME !!

 

Pour ma part, je présentais une session avec Florian Eiden (b|t) sur le Data Stewardship : “Fasten your seat belt and listen to the (Data) Steward

image

Nous avons parlé de Self-Service BI et de Gouvernance des données en entreprise autour de Power BI (et au delà). Nous avons apporté un éclairage sur le profil et le rôle d’un Data Steward dans une organisation qui développe la BI personnelle pour ses utilisateurs.

 

imageimageimage

Etant donné que la session était tôt le matin (8h15) et le sujet non-technique, la salle était peu remplie mais nous sommes ravis d’avoir pu partager notre approche et notre expérience. Et le tout…en anglais ; et je dois dire que ça a été la partie la plus compliquée, n’étant pas bilingues. Mais on a fait le job et on s’excuse encore pour les fautes de grammaire, le vocabulaire pas forcément précis et notre French-accent Sourire.

Je remercie fortement les organisateurs de nous avoir fait confiance pour cette session.

SSRS sur Power Pivot dans SharePoint – Configuration Kerberos

Dans l’article précédent, je présentais un scénario de Self-Service BI : faire des rapports Reporting Services sur des modèles Power Pivot.

Techniquement, c’est un peu comme ouvrir un classeur Power Pivot dans SharePoint dont vous avez le schéma d’exécution ci-dessous. En requêtant le fichier XLSX, ce sont d’abord les services Excel qui répondent puis passent le relai au service Power Pivot.

image
source MSDN

Dans le cadre d’un appel via EWA (Excel Web Access), la sécurité est gérée par SharePoint lui-même. En effet, la sécurité est au niveau du classeur et dès que EWA l’a vérifié, c’est lui qui fait le boulot et s’arrange avec l’instance SSAS de PowerPivot (je l’ai fait courte).

Avec SSRS en tête de chaine, c’est un peu différent puisque que SSRS se présente avec l’identité de l’utilisateur directement au service Power Pivot (après avoir que EWA ait fait le forwarde vers les Web Services Power Pivot). Le problème c’est que le service Power Pivot est configuré pour fonctionner en NTLM et là, on est confronté au problème du double-hop si SSAS et la ServiceApp sont sur des machines différentes de la ferme.

 

Kerberos ?

Oui, la réponse est Kerberos, comme j’ai déjà pu en parler lors de différentes conférences.

Les étapes (en résumé) :

  1. Configurer le service Power Pivot pour supporter Kerberos
  2. Créer les SPN et les délégations entre la ServiceApp Power Pivot et l’instance SSAS

 

Le point noir, c’est que la première étape nécessite la modification de fichier Web.config  dans la ferme, chose que les administrateurs SharePoint autorisent rarement…

 

Sources :

http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx
http://sharepointalldocs.blogspot.fr/2011/12/ssrs-reports-or-excel-services.html

Rapports SSRS sur un modèle Power Pivot dans SharePoint

Un pur scénario de Self-Service BI

Un scénario que j’aime beaucoup dans une plate-forme BI est la possibilité de créer des rapports Reporting Services (SSRS) avec comme source de données un classeur Power Pivot.

Ce scénario est essentiel dans un cadre de Self-Service BI puisqu’il permet à un utilisateur métier de rapidement mettre en ligne un modèle multidimensionnel (ou tout simplement une table de données) et de disposer d’un outil de reporting mature (ou Old-School selon le point de vue Sourire).

Bien évidemment, en 2014, on cible plutôt des tableaux de bord réalisés avec Excel Services ou Power View mais SSRS couvre un certain type de reporting :

  • mise en forme précise / dynamique
  • positionnement précis / dynamique
  • cartographie (avec des shapes ou des fonds de cartes personnalisées)
  • impression, export PDF
  • abonnements, data alerts
  • etc.

Qu’on se le dise, je ne fais pas de plébiscite pour SSRS au détriment de Power View ou Excel. Chacun a son propre usage de prédilection, ses avantages et ses inconvénients. Ils doivent être dans la boite à outils des architectes BI comme des utilisateurs.

 

Comment faire  ?

C’est super simple :

  1. Téléchargez votre classeur Power Pivot dans une bibliothèque de documents (pas forcément dans la galerie Power Pivot)
  2. Créez un nouveau rapport avec Report Builder
  3. Créez une source de données de type Analysis Services (Power Pivot pour SharePoint est un serveur SSAS)
  4. Dans la chaîne de connexion, indiquez l’URL du classeur Power Pivot

image

 

Documentation MSDN : http://msdn.microsoft.com/fr-fr/library/ff713974.aspx

 

Mais attention, sur une ferme SharePoint (ie. plusieurs serveurs) il vous faudra configurer quelques points (en lien avec Kerberos indeed Sourire). Mais ce sera l’objet de l’article suivant.

Utilisation de Power Map avec un proxy

Power Map utilise les cartes de Bing Maps pour le géo-coding (ie. trouver les coordonnées latitude/longitude d’une adresse) et le rendu (fonds de carte). C’est le point principal qui explique la nécessité d’avoir une connexion Internet pour que Power Map fonctionne.

image

Comme dans beaucoup de société, il est possible qu’un proxy se trouvent entre Power Map (ie. Excel) et les API de Bing Maps. Dans la plupart des cas (que j’ai pu constater), il n’y a rien à faire, Power Map se basant sur la configuration par défaut, c’est à dire celle d’IE. Mais dans quelques cas, vous allez devoir paramétrer les informations du proxy.

Pour cela, il vous faut créer un fichier de configuration (façon .NET) avec la section afférente au Proxy.

 

<?xml version="1.0" encoding="utf-8" ?> 
< configuration> 
  <system.net> 
    <defaultProxy useDefaultCredentials="true" /> 
  </system.net> 
< /configuration>

 

Pour trouver la bonne configuration, referez vous à la documentation XML : http://msdn.microsoft.com/en-us/windows/dkwyc043(v=vs.71).aspx

 

Reste à savoir comment nommer le fichier et où le placer.

Le fichier est le fichier de configuration d’Excel, il doit donc se nommer excel.exe.config et se trouver dans le même dossier qu’Excel.exe (C:\Program Files\Microsoft Office 15\root\office15).

Toutefois, certains articles font état du dossier de Power Query (C:\Program Files\Microsoft Power Query for Excel\bin) et le nom du fichier de configuration sera microsoft.mashup.container.exe.config.
Note : Je n’ai pas rencontrer ce cas mais par souci d’exhaustivité, je le retrace ici.

 

Source : http://social.technet.microsoft.com/Forums/en-US/577d7263-4d09-4550-b0fc-3592cd465a55/power-map-and-internet-connectivity?forum=powermap

Nouveautés #PowerBI annoncées à la PASS Business Analytics Conference

imageEn ce moment a lieu la PASS Business Analytics Conference (#passbac pour les intimes) à San José en Californie. Cette conférence organisée par le PASS (que je ne présente plus) fait un focus particulier sur la Business Intelligence.

Cette conférence n’a pas l’ampleur du PASS Summit de novembre mais ce n’est encore que la deuxième édition. Et à la vue des annonces que Microsoft y a fait, ainsi que la qualité des keynotes (par les talentueux Kamal Hathi @kamalh et Amir Netz @amirnetz) et des sessions, il y a fort à parier qu’elle va prendre de l’importance dans le paysage Data dans les années à venir.
Allez, point de suspens, voyons tout de suite ces annonces qui ont floodé mon fil Twitter hier soir (merci la #SQLFamily internationale).

Note : je mettrai des screenshots dès que j’aurai un peu de temps pour jouer avec ces nouveautés. Mais je ferai également des articles dédiés à certaines d’entre elles

SSRS dans Power BI

C’est pour moi la plus grosse annonce ! Plébiscité de longue date par les utilisateurs et la communauté (avec les MVP SQL comme lobbyistes auprès de Microsoft), Reporting Services va bientôt être disponible sur Office 365 (via l’option Power BI). Cela fait sens car Power View n’est pas (encore?) un outil pour faire du reporting de masse et je ne connais pas un client qui n’ait pas besoin de faire un rapport “statique”, que ce soit dans un contexte BI ou pas.

Avec SSRS dans Power BI, le prix de l’option devient vraiment intéressant (et plus que compétitif). Les utilisateurs vont ainsi avoir à disposition un outil de reporting supplémentaire sur leurs modèles Power Pivot mais aussi (et c’est la seconde partie de l’annonce), sur leur données OnPrem puisque SSRS pourra utiliser les sources de la Data Management Gateway !

SSRS est mature et s’intègre très bien à SharePoint depuis 2012 (OnPrem) ; ça aurait été dommage de ne pas le rendre disponible aux utilisateurs. J’entends déjà mes confrères râler sur la “piètre” qualité “vieillissante” des composants visuels de SSRS mais cet outil a le mérite d’exister et d’être simple à appréhender par les utilisateurs/développeurs.

Note : Comme le dit Chris Webb, on espère qu’il y aura un support sur les applications mobiles (Windows 8, iOS et Android)

 

Power View sur SSAS dans Power BI

Dans un site Power BI, Power View ne peut se connecter que sur un modèle Power Pivot. D’ailleurs, le seul moyen de créer un tableau de bord Power View est de le créer dans Excel avant de l’uploader sur le site Power BI.

La nouveauté, c’est qu’il va être possible de créer des tableau de bord Power View sur un site Power BI en se connectant sur une source SSAS Tabulaire OnPrem. (note : aucune info sur le multidim)

 

Un éditeur en ligne de KPI

Hier a été présenté un éditeur de KPI, directement intégré dans un site Power BI permettant aux utilisateurs de créer simplement des KPI mais aussi de les consolider dans un tableaux de bord. Pour réaliser des portails décisionnels depuis longtemps, je trouve cette initiative excellente, en espérant que la performance d’affichage sera au rendez-vous.

Une des dernières fonctionnalités exclusives de PerformancePoint Services (les Scorecards) a trouvé son alternative.

image

 

Edition de Power View en ligne

Un éditeur en ligne de Power View (directement dans un site Power BI) a été présenté hier soir. Avec un mode Data Exploration, cet éditeur offre de nouvelles fonctionnalités comme la possibilité de sortir des points d’un graphe pour créer un autre graphe plus précis. C’est une façon de faire du drill-down et du changement d’axe en live dans un rapport Power View.

J’ai vraiment hâte de jouer avec cette fonctionnalité qui enrichit encore plus l’aspect interactif de Power View, notamment pour un usage en réunion.

 

Time Series Forecasting

On ne dit plus Data Mining mais Predictive Analytics. Predictive Analytics, c’est une tendance à la mode dans l’univers des Data; on dit même que c’est la clé de demain (wait & see). Dans le détail, Data Mining et Predictive Analytics ne sont pas la même chose. Dans le second, on se concentre sur la prédiction et principalement les algorithmes de Forecast.

Faire une projection dans le temps, c’est maintenant une fonctionnalité de Power View. Rien de plus simple : vous choisissez votre courbe ou une partie de votre courbe et vous étirer le graphe pour faire avancer l’axe des abscisses (souvent l’axe temps). Power View fait le reste et exécute l’algorithme de Forecasting pour vous. Vous n’avez plus qu’à ajuster les options (variables) de l’algo.

Et le plus fort, c’est que c’est disponible maintenant sur votre site Power BI en ligne

imageimage

image

Ressources et démos

 

Une Treemap dans Power View

Annonce claire, simple, efficace. Power View se voit doté d’un nouveau composant visuel : la Treemap !

Je ne vais pas polémiquer pour savoir si ce composant est bien ou pas, visuellement utile ou pas, etc. Tout ce que je peux dire c’est que c’est une demande de longue date des mes clients. Et ce que client veut… D’autant plus que, depuis que ce composant a disparu de Performance Point (dans sa version initiale : ProClarity), Microsoft n’avait aucune alternative et les clients devaient se rabattre sur des produits concurrents (comme SAPBI – ex-BO par exemple).

imageimage

 

Note : Avec l’éditeur de KPI, la Treemap et la décomposition de graphe dans Power View, on voit bien que l’ensemble des spécificités de Performance Point se retrouvent dans les différentes briques de Power BI. C’est certainement pourquoi beaucoup proclamaient hier sur Twitter “R.I.P Performance Point, PerfPoint est mort !”

 

Power BI Mobile App sur iOS

On en parle depuis un moment déjà, la date est maintenant officielle : l’application iOS pour consulter ses rapports Power BI sortira à la fin de cet été. Je ne vous en dirai pas plus, de toute façon, je n’ai pas d’iPad Tire la langue

 

Mais encore ?

Quelques jours avant la PASS Business Analytics Conference, il y avait déjà eu quelques annonces de nouveautés, dans le cadre de la mise à jour mensuelle de Power BI.

 

Support de Power Query pour le Data Refresh Power Pivot

Il est maintenant possible de rafraichir les données d’un modèle Power Pivot alimenté par une requête Power Query dans un site Power BI via la Data Management Gateway. Ca parait trivial mais ce n’était pas encore disponible.

Il y a encore quelques limitations, notamment le fait que toutes les sources Power Query ne sont pas encore supportées. On est limité entre autre par la Data Management Gateway qui ne supporte que SQL Server et Oracle à ce jour. Mais on voit bien que les équipes Power BI de Microsoft travaillent d’arrache-pied à enrichir tout cela mois après mois.

A noter aussi plein de nouveautés dans Power Query lui même mais je vous laisse voir cela dans les liens ci-dessous.

Ressources

 

Amélioration de la version HTML5 de Power View

Power View online (ie. dans un site Power BI) existait déjà en version HTML5 (au lieu du Silverlight originel) mais cette version ne supportait pas encore tous les composants visuels. Avec la récente mise à jour, les cartes sont maintenant supportées. Il y a également quelques améliorations plus mineures mais je vous laisse voir ça dans l’article officiel de l’équipe Power BI.

http://blogs.msdn.com/b/powerbi/archive/2014/05/07/new-in-power-view-in-html5-and-the-power-bi-app.aspx

 

PASS_BA14_788x165

Transform Data into Action, la keynote Data des patrons de Microsoft

image

Hier soir, il y avait un événement exceptionnel en ligne : une Keynote par Satya Nadella (CEO de Microsoft), Kevin Turner (COO de Microsoft) et Quentin Clark (Corporate Vice-President pour SQL Server) dont le sujet était les données… et par extension la plate-forme Data de Microsoft avec en son coeur SQL Server.

D’ailleurs, les titres et les accroches ne laissaient planer aucun doute sur le sujet : Transform Data into Action, Accelerate your insights

Je ne vais pas vous faire une retranscription complète de l’événement. Tout d’abord parce que je n’ai pas été attentif pendant toute la conférence (je vous passe de commentaires) mais surtout parce que les journalistes font cela mieux que moi :

Et puis, comme on peut s’en douter, la Com’ de Microsoft s’est activée en temps réel et les blogs officiels ont déjà posté leurs résumés.

Je vais donc vous faire une synthèse toute personnelle, en y mêlant mes avis et mes convictions.

Quelques annonces

Commençons avec le plus croustillant (comme dirait Closer ou Voici), à savoir les annonces.

Intelligent Systems Services

On parle souvent des “capteurs de données” (je dirais plutôt générateur de données personnellement).
On parle des données personnelles (position, fréquence cardiaque, poids, tension artérielle ou encore glycémie) ou des données d’environnement (météo, température, humidité, etc.).
On parle aussi de l’IoT, Internet-of-Things (ie. Internet des Objets), des objets communiquants, qui produisent ces données ou les consomment.
Tout cela est d’ailleurs souvent associé au Big Data, technologies adaptées à dompter cette masse de données générées.

Satya Nadella a annoncé hier la disponibilité en Preview de Microsoft Azure Intelligent Systems Services (ça fait MAISS Sourire) dans le Cloud :

En 2 mots, cette nouvelle technologie a pour but de gérer et collecter les données en provenance de multitudes de capteurs, dans un contexte d’entreprise principalement (…managing and capturing machine-generated data from industry devices, sensors…).

Je ne vous en dirais pas plus, il faut maintenant s’inscrire à la beta et essayer.

 

Analytics Platform System

Après avoir expliqué qu’avoir plein de données c’est bien mais qu’il faut des outils (des systèmes analytiques) pour en tirer quelque chose, Quentin Clark a annoncé la disponibilité de APS pour Analytics Platform System.

Présentée comme l’évolution de PDW, APS traite aussi le Big Data et se présente sous la forme d’une appliance (avec du matos HP, Dell ou Quanta selon la Product Sheet) toute prête pour gérer des tombereaux de données.

La bête : http://www.microsoft.com/en-us/server-cloud/products/analytics-platform-system/

Donc du PDW, du ColumnStore, du HDInsight, du Petabytes… Microsoft a sorti la grosse artillerie pour s’aligner avec les SAP HANA, Oracle Exalytics ou encore SAS HPA. On ne va pas s’en plaindre, surtout qu’en voyant ce qu’il y a dedans, on voit qu’on sait déjà travailler avec et que ça s’intègre très bien avec le reste de la plate-forme de données de Microsoft.

C’est donc du “packaging” des solutions data qui vient enrichir les appliances avec des technos Microsoft. A suivre car ça a l’air prometteur (et super crédible pour un DSI qui veut de la puissance de calcul et qui fait un benchmark des différents éditeurs).

Une philosophie

En dehors des annonces de produits et des démos, j’ai retenu la philosophie de Microsoft autour des données. Ca tombe bien car elle est totalement en phase avec la mienne (même si on n’a pas les mêmes mots ou les mêmes images).

J’ai retenu 3 expressions de la conférence d’hier. Je ferai court sur chacune mais je pense que je vais revenir dessus, quand j’aurai plus de temps, pour vous faire un édito (cet été par exemple).

Data Culture

imagePour tirer partie des données, pour en extraire la valeur ajoutée, les outils ne sont pas suffisants. Il est important d’éduquer les utilisateurs (par extension, tous les acteurs qui gravitent autour) afin que les données soient incluses dans leurs réflexions, dans leur quotidien. Ce doit être une démarche d’entreprise : la culture des données.

Cela passe par tous les acteurs de l’entreprise qui doivent comprendre et maitriser toutes les données disponibles. On est toujours dans le BI for Masses de Ballmer en 2006 chez Microsoft. Mais la phase 2 implique les gens et plus seulement les outils. La plate-forme est là, “Libérez les données, Libérez les usages !”.

Data Dividend

Même s’il y a eu une petite polémique hier soir sur Twitter avec mon ami Florian sur le sens à donner à “Dividend”, ce que j’ai retenu c’est : on peut valoriser ses données mais en les exploitant mieux/plus, on peut augmenter cette valeur (son rendement) de façon spectaculaire.

Satya Nadella estime à plusieurs centaines de milliards le revenu qui pourrait être généré de l’exploitation des données (insights). De mémoire, le chiffre annoncé était $1,76 trillion.

Selon Microsoft, l’équation est :

Data Dividend = [ Data + Analytics + People ] x Speed

image

Ambient Intelligence

imageL’intelligente ambiante… quelle drôle d’idée ? L’idée est de se dire que tout est BI, que chaque donnée contribue à la BI. Et même les données “ambiantes” qui justement, permettent de contextualiser les choses et apporter encore plus de valeur (insights).

Je pense que je vais réécouter la conférence, m’imprégner des transcripts et revenir sur cette notion qui m’intéresse mais que j’ai tout de même du mal à définir.

 

Pour conclure

Sur le fond, Microsoft mets au coeur de sa stratégie la donnée elle-même et fournit les outils pour la gérer. A tous les niveaux, des barbus qui construisent des DataWarehouse monstrueux, aux analystes qui font des camemberts dynamiques dans Power View en passant par les Data Scientists qui passent des algo de folie sur les données.

Une plate-forme intégrée pour transformer les données en valeur ajoutée, depuis le temps que je le dis… (mais n’oubliez pas la Gouvernance au dessus…)

image

TechDays 2014

One More Time !
Je me souviens de mon premier TechDays (en tant que Speaker indeed), c’était en 2008, les T-Shirts étaient bleus et j’étais avec le père Pertus (il a bien changé maintenant, il fait du Windows 8, n’importe quoi ).

Je m’égare…

Donc comme tous les ans mi-février, c’est la grande messe Microsoft, presque aussi importante que les JSS (pour nous les data-guys).

imageLes TechDays auront lieu les 11-12-13 février. La première journée plutôt pour les développeurs et la dernière plutôt pour les DSI.

Inscription ICI

 

Mais quelles sessions j’anime ?

Vous connaissez mon investissement dans la Self-Service BI ? Je vous le donne en mille, ce sera le thème de mes sessions.

Power Query Démos Extravaganza

Level 400, que des démos, du code M…que dire de plus ?
L’idée est de plonger au coeur de Power Query et de voir tout ce qu’on peut faire avec.

Update : finalement, il y aura au moins 1 slide (en dehors des slides de titre et de présentation…) car on me demande de parler un peu de l’architecture de Power BI. Difficile à caser en 45 minutes mais je suis également convaincu que c’est important.
Au pire, je finirai les explications sur le stand AZEO.

Détails et inscription sur le site des TechDays

 

Self-Service BI : Retour d’expérience

Je rejoue avec Olivier Jacquemont notre session des JSS de feedback sur la mise en place de la Self-Service BI à la Banque de France.

Objectif, vous donner plein d’infos si vous voulez mettre en oeuvre de la Self-Service BI dans votre entreprise.
Pas trop de technique mais que du concret.

J’espère que le public sera aussi dynamique qu’au JSS et posera de nombreuses questions car c’est toujours ce qui est le plus intéressant dans ce genre de session : Echanger et Partager.

D’ailleurs, si vous aussi vous avez déjà mis en œuvre de la Self-Service BI, venez comparer votre expérience à la nôtre et apporter vos retours.

Détails et inscription sur le site des TechDays

Webcast Power Query et Data Management Gateway

Il y a plus d’un mois maintenant avaient lieu les Journées SQL Server.
J’ai eu l’occasion de présenter personnellement 2 sessions :

  • Power BI : intégration de données
  • Self-Service BI : un retour d’expérience

Power BI : intégration de données

Dans la track Power BI, cette session était consacrée à l’acquisition des données c’est à dire à Power Query et à la Data Management Gateway.

En 1h, j’ai pu faire des démos de requêtes Power Query, jouer avec le langage M, utiliser la recherche et le partage de requêtes avec le Data Catalog et faire un peu d’architecture hybride en branchant le cloud Power BI avec mes données on-premise.

image

Je ne vais pas refaire la session dans cette article, le mieux est que vous consultiez la vidéo et les slides (il faut être logué) :

image

A votre dispo pour les questions (dans les commentaires).

 

Self-Service BI : un retour d’expérience

Cette session est un peu atypique car pas du tout technique (enfin, un peu quand même ). Pas de démos, juste delà discussion et du feedback.

Cela fait de nombreux mois qu’entres autres missions, j’accompagne la Banque de France à mettre en place de la Self-Service BI dans ses différents métiers (et il y en a un paquet).

L’objectif de la session était de faire un bilan sur la mise en place de Self-Service BI dans une grande entreprise (13 000 agents ici).
Les joies, les peines, les pièges à éviter et les bonnes surprises.

Je présentais la session avec Olivier Jacquemont, l’un des patrons du Service Information Management de la Banque de France et instigateur du projet, et Pierre-Sébastien Malleret, un confrère, artisan comme moi de cette mise en oeuvre.

image

La session s’est même terminée avec une discussion entre quelques DSI qui ont pu poser des questions opérationnelles ou stratégiques mais toujours pertinentes, et qui ont pu échanger entre eux et avec nous.

image

Désolé pour le son, il y a eu des problèmes de micro pendant la session.

Note : nous rejouerons cette session aux TechDays donc si vous voulez participer au débat rendez-vous sur place.

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

Automatiser la création d’un classeur PowerPivot

 

Il y quelques temps déjà, je vous indiquais qu’on avait, avec Excel 2013, accès au DataModel par programmation. Etrangement, personne n’en a parlé dans la communauté, française ou internationale.

La documentation du modèle objet d’Excel contient bien les entrées avec un objet Model.

image
http://msdn.microsoft.com/en-us/library/office/jj230227.aspx

Je me suis donc mis en tête de creuser ce sujet…

Disclaimer

Malgré ma ténacité (et une bonne partie de mon dimanche), je ne suis pas super satisfait de mes trouvailles car il y a encore beaucoup à creuser dans l’API pour avoir une solution totalement fonctionnelle. Et l’API n’est pas beaucoup documentée.

Cet article vous donne les pointeurs pour démarrer et évidemment, si par la suite je trouve plus de choses, je vous en ferai part. Je compte sur vous pour en faire de même dans les commentaires de cet article.

 

Mode DataModel Excel

La manipulation du DataModel (Model) directement dans le modèle objet Excel (en VBA) est en ReadOnly uniquement. Elle est même carrément absente des API en VSTO.

Il y a toutefois une méthode intéressante :

Workbook.Connections.Add2

Elle permet d’ajouter une connexion au DataModel grâce à un paramètre booléen qui pourrait passer inaperçu dans la doc : CreateModelConnection.

image
http://msdn.microsoft.com/en-us/library/office/ff195949.aspx

Ce code ajoute la table Faits provenant de mon SQL Server local dans le modèle :

ThisWorkbook.Connections.Add2 _
	"VBA Connection", "A Description", _
	"OLEDB;Provider=SQLNCLI11;Data Source=localhost;initial catalog=Lab;integrated security=SSPI", _
	"Faits", 6, _
	True, True

Je n’ai pas poussé à récupérer un modèle complet mais il suffit d’indiquer plusieurs tables dans la CommandText.

Il y a aussi tout un tas de spécificités en fonction de votre source. Avec une source OData, ça donne ça comme ConnectionString (source Azure DataMarket)

"OLEDB;Data Source="Inline Service Document";Include Atom Elements=Auto;Include Expanded Entities=False;Persist Security Info=false;Time Out=600;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=False;Refresh Token=xxxxx

Mode Table Liée dans Excel

Dans la lignée de la première méthode, il est tout à fait possible par code de remplir une feuille Excel avec toutes les données (limité à 1M de lignes).

Ensuite, un appel à la méthode Add2 avec d’autres paramètres que ceux présentés ci-dessus permet de l’ajouter au Model.

Par contre, dans ce cas, le Data Refresh ne fonctionnera pas.

ThisWorkbook.Connections.Add2 _
	"LinkedTable_TableClasseur", "", _
	"WORKSHEET;C:\Users\Jean-Pierre\Desktop\Book1.xlsm", _
	"Book1.xlsm!TableClasseur",	7, _
	True, False

Mode OpenXML

Il y a également une autre piste, c’est de passer par le SDK OpenXML qui permet de manipuler le fichier Excel sans ouvrir Excel.

Télécharger le SDK OpenXML 2.5

Le contenu du fichier Excel est un ZIP qui contient des fichiers XML et d’autres données (images, etc.).

Dans le fichier workbook.xml, il y a un nœud <dataModel>
documentation MSDN

Pour le modèle en lui-même, c’est en fait un backup d’un cube SSAS Tabulaire.
Vous pouvez en construire un avec l’API AMO, le sauvegarder et le ré-injecter dans le fichier Excel.

Un projet CodePlex contient toutes les routines nécessaires : http://tabularamo2012.codeplex.com/

clip_image002

Notez que je n’ai pas investigué cette méthode donc impossible de vous dire si cela fonctionne bien.

 

Conclusion

L’API n’est pas documentée, il n’y a aucun exemple sur Internet mais les méthodes sont bien présentes. Je n’arrive pas à la conclusion que c’est impossible mais évidemment, il y a encore du travail pour suivre ces pointeurs et avoir un PoC totalement fonctionnel.

Attention : Point important

De ce que j’ai pu trouver, toutes ces méthodes ne sont pas forcément supportées. L’impact est que, si une macro ou le code qui manipule le OpenXML endommage le classeur Excel, le support Microsoft se désengagera.

 

PS : quand je disais que la communauté n’avait rien écrit sur ces API, j’aimerai toutefois remonter 2 pointeurs :

Notez que ces articles sont vieux et donc ne tiennent pas compte de l’extension de l’API en 2013.