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 
) : 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.)
Aprè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.
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.
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.
Ensuite, l’assistant vous propose de saisir une configuration pour votre package.
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.
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
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 :
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 :
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).
- Téléchargement et Installation de la Data Management Gateway
- Configuration et ouverture de flux (port 8051 par défaut)
- Création d’une Gateway sur l’IT Admin Portal de votre tenant Power BI
- Configuration de la clé pour que votre DMG et Power BI communique
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.
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
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
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 :
- 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
- Excel (Power Query) contacte le Cloud à l’URL du Feed OData
- Le Cloud lui donne les infos de connexion à la Gateway qui référence la source
- 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.
(Cliquer pour agrandir)
Cela donne à peu près cela :
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).
T’as fait un test avec un package un peu complexe et une volumétrie « intéressante » ?
Non, mais c’est prévu pour ma session TechDays