Power BI est une solution de Business Intelligence composée de plusieurs éléments. Celui que je vais vous présenter aujourd’hui est sûrement un des moins visibles, mais néanmoins important. Surtout dans des scénarios de type reporting en mode Cloud hybride.
Dans ce billet, je vais vous présenter Power BI Data Management Gateway. Ce dernier a pour fonction principale la synchronisation des données d’un rapport, publié dans un site Power BI pour Office 365, dont les données se trouvent sur site. C’est donc bien une passerelle qui va être paramétrée, entre le rapport et la source de données, afin d’assurer la mise à jour du rapport, sans être obligé de monter une infrastructure complexe.
Sur le site Office 365.
Connectez-vous en tant qu’administrateur sur votre site Office 365, puis allez dans votre site Power BI. Cliquez sur l’engrenage en haut à droite puis cliquez sur “Centre d’administration de Power BI”
Dans la partie gauche de l’écran, cliquez sur “Passerelles” puis sur “Nouvelle passerelle”
Renseignez le nom et la description de la passerelle puis cliquez sur le bouton “créer”
Dans la partie “Installer & inscrire”, se trouvent 2 éléments importants :
Cliquez sur le bouton “Téléchargement”
Vous serez redirigez vers la page de téléchargement de la passerelle. C’est un agent qui s’installe sur la machine qui contient la source de données.
Pendant que la passerelle se télécharge, cliquez sur le bouton “terminer”, dans la partie ““Installer & inscrire”. Vous devez voir votre passerelle apparaître dans la liste. Dans mon exemple, la passerelle s’appelle “FritePasserelle”
Cliquez sur les 3 petits points à droite du nom de la passerelle, puis sur “Afficher”, afin de faire apparaître la clef si nécessaire (vous en aurez besoin pour enregistrer la passerelle sur votre serveur).
Ci dessous, une copie d’écran de l’affichage de la clef.
Sur le serveur.
Après avoir installé la passerelle sur votre serveur, exécutez le programme. Par défaut, vous allez arriver sur l’onglet “Home”. Si vous cliquez sur l’onglet “Get Started”, vous aurez l’écran suivant qui reprend les étapes de configuration de la passerelle :
Revenez sur l’onglet “Home” et cliquez sur le bouton “Register Gateway”
Dans la fenêtre “Register Gateway”, entrez la clef de votre passerelle. Puis cliquez sur le bouton “Ok”
Après quelques secondes, votre passerelle doit être validée et enregistrée. Ci-dessous une copie d’écran:
NOTE : Lors d’un test, ma passerelle ne voulait pas s’enregistrer. J’ai tout simplement arrêté et redémarré le service en utilisant le bouton “Stop Service”.
Cliquez sur l’onglet “Settings” afin de paramétrer le point de terminaison nécessaire pour l’exposition des données:
Cliquez sur le bouton “Edit” pour modifier les informations du point de terminaison :
Maintenant que la passerelle est configurée, il ne reste plus qu’a créer la ou les sources de données associées à cette passerelle. Retournez dans le centre d’administration de votre site Power BI et cliquez sur “Sources de données” puis “Nouvelle source de données”.
Dans la fenêtre de création de la source de données, cochez la case “Activer l’accès par le Cloud”. Cliquez sur le bouton “Suivant”.
la fenêtre “informations de connexion apparaît”. donnez un nom à votre source de données et dans la liste déroulante “Passerelle”, sélectionnez la passerelle que vous souhaitez associer à votre source de données. Ensuite cochez la case “Chaine de connexion”, choisissez un fournisseur de connexions et entrez la chaîne de connexion.
Justement, comment définir la chaîne de connexion! Dans le cas de mon exemple, j’ai réalisé un rapport Power Pivot qui récupère les données à partir d’une base SQL. Pour récupérer la chaîne de connexion, dans Power Pivot :
Une fois la chaîne de connexion récupérée, copiez la dans le champ “Chaine de connexion”. Puis cliquez sur le bouton “Information d’identification”
Après avoir cliquez sur le bouton “Information d’identification”, il se peut que vous soyez obligé d’installer l’application “Data Source Manager”
Dans la fenêtre “Data Source Manager”, entrez vos informations d’identification. Puis cliquez sur le bouton “Ok”
De retour dans la fenêtre des informations de connexion, cliquez sur le bouton “Suivant”
La fenêtre “Utilisateurs et groupes”, permet la gestion des utilisateurs. Rajoutez les utilisateurs et cliquez sur le bouton “Terminer”
Votre source de données doit maintenant être prête et apparaître dans la liste :
Maintenant que la passerelle est configurée, et que la source de données est créé et associée à la passerelle, nous allons l’utiliser avec un rapport. Ci dessous une copie d’écran d’un rapport Excel (très simple !), réalisé avec Power Pivot.
Ce rapport; “Démo”, est publié sur mon site Power BI :
J’ouvre le fichier “Démo” en mode Web avec Excel Web App. Ci-dessous une copie d’écran :
Simulons maintenant un changement dans la base de données (sur site). Pour cet exemple, je passe le nombre de Télécaster de 1 à 69.
Maintenant, je reviens sur ma page web Excel Web App et je rafraichis les données du rapport en cliquant sur “Données” et “Actualiser toutes les connexions”
Un message d’avertissement peut apparaître. Cliquez sur le bouton “Oui”
Le rapport est rafraichit et affiche les nouvelles données. Pour rappel, le rapport est dans le Cloud, dans un site SharePoint Online (Power BI for Office 365), et les données sur un serveur dans l’entreprise.
Voilà pour une illustration de Power BI Data Management Gateway. La vidéo est ici.
Au plaisir de vous voir lors d’un IT Camp SQL 2014/Power BI.
Franck Mercier
Pour tester Windows Server 2012, Windows 8, SQL Server 2012 et SQL Server 2014 CTP1, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
· Windows Server 2012 :
· SQL Server 2012 :
· Evaluation SQL Server 2014 CTP2 :
· Testez Azure gratuitement pendant un mois :
Pour compléter mon précédent article sur Power BI Data Management Gateway, voici la vidéo !
SQL 2014 CTP1 est disponible en téléchargement. Cette version est complètement orientée Cloud hybride et propose nativement de nombreuses fonctions permettant l’intégration avec les différents « niveaux » du Cloud, le IaaS, le PaaS et aussi le stockage de Windows Azure. Cet article détail une de ces fonctions, et plus particulièrement l’intégration de SQL 2014 avec le stockage de Windows Azure. Cette possibilité permet de stocker les fichiers de données et de logs directement dans un stockage Azure.
Voici les étapes que l’on va suivre lors de ce billet :
Avant de pouvoir intégrer SQL 2014 avec le stockage Azure, il est de bonne augure de configurer un compte de stockage ainsi qu’un conteneur afin d’y stocker nos fichiers de logs et de données !
Connectez-vous sur le portail Azure et cliquez sur le bouton en bas de l’écran.
Dans le menu qui apparaît, allez dans “Services de données”/”Stockage”/”Création Rapide”
Renseignez les informations concernant le compte de stockage que vous souhaitez créer.
Une fois le compte de stockage créé, il vous faut créer un conteneur afin d’y stocker vos données.
Dans la fenêtre de votre compte de stockage, cliquez sur le menu “Conteneurs” en haut de l’écran, puis cliquez sur “Créer un conteneur”.
La fenêtre ci-dessous apparaît. Renseignez le nom et le type d’accès du nouveau conteneur.
Le conteneur est maintenant créé, mais ne contient pour le moment aucune donnée.
Revenez sur le “Tableau de bord” de votre compte de stockage et cliquez sur “Gérer les clés d’accès”
Dans la fenêtre “Gérer les clés d’accès”, vous y trouverez les informations de sécurité de votre compte de stockage. Ces informations seront utilisées dans la partie suivante de ce billet.
Avant de pouvoir intégrer SQL 2014 avec le stockage azure, il est nécessaire de créer une “Policy” sur le compte de stockage ainsi qu’une “Shared Access Signature”. Il existe plusieurs manières pour réaliser ces opérations, dont les 2 suivantes :
Pour ce billet, je vais réaliser l’opération avec l’outil “Windows Azure explorer tool”.
Après avoir téléchargé et installé l’outil, exécutez le programme. Dans la fenêtre principale de “Windows Azure explorer tool”, cliquez sur le bouton “Add Account”. La fenêtre “Add Storage Account” apparaît. Cliquez sur le bouton “Add Account”.
Renseignez les informations concernant votre compte de stockage (vous pouvez les récupérer à partir du portail Azure, dans votre compte de stockage, en cliquant sur “Gérer les clés d’accès”). Cliquez sur le bouton “Add Storage Account”
Maintenant que le compte de stockage est référencé dans l’outil, sélectionnez-le puis cliquez sur le bouton “Security”.
Dans la fenêtre “Blob & Container Security”, cliquez sur l’onglet “Shared Access Policies”. Créez une nouvelle “Policy” en définissant au moins un accès “Read / Write”, ainsi que les “start” et “expiry” dates.
Cliquez sur le bouton “Save Policies” puis fermez la fenêtre. Ouvrez à nouveau la fenêtre en cliquant sur le bouton “Security”. Dans l’onglet “Shared Access Signature”, sélectionnez votre “Policy” dans la liste déroulante “Policy”. Puis cliquez sur le bouton “Generate Signature”
Une fois la signature générée, cliquez sur le bouton “Copy to Clipboard” afin de récupérer les informations liées au compte de stockage et sa signature.
Copiez les informations dans un éditeur de texte. Vous devez obtenir quelque chose de la forme :
http://mysqldata.blob.core.windows.net/franmersqlfiles?sr=c&si=FranmerPolicy&sig=OlO8M5lBrG5QgD69JtYhc6XHcrR3Hi55cZAoRVqutVw%3D
Pour récupérer la signature, copiez les informations se trouvant à droite du premier point d’interrogation (?) : sr=c&si=FranmerPolicy&sig=OlO8M5lBrG5QgD69JtYhc6XHcrR3Hi55cZAoRVqutVw%3D
Avant de créer la base de données, il est nécessaire de créer un “Credential” permettant à SQL d’accéder au compte de stockage
Dans SQL Server Management Studio (SSMS), cliquez sur le bouton puis copiez le script ci-dessous. Modifiez le script avec les informations de votre compte de stockage et la “Shared Access Signature”.
ATTENTION ! Bien mettre https, même si dans votre lien d’origine c’est http. De plus, le WITH IDENTITY doit absolument être 'SHARED ACCESS SIGNATURE'
-- Création d'un "credential" permettant l'accès au compte de stockage Azure CREATE CREDENTIAL [https://mysqldata.blob.core.windows.net/franmersqlfiles] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'sr=c&si=FranmerPolicy&sig=OlO8M5lBrG5QgD69JtYhc6XHcrR3Hi55cZAoRVqutVw%3D' GO
Après un “Refresh” dans SSMS, votre “credential” apparaît. Notez que son nom correspond à l’adresse de votre conteneur.
Après avoir créez le “credential”, il ne reste plus qu’à créer la base de données. Ci-dessous, un exemple de script permettant la création de la base de données avec ses fichiers stockés dans le conteneur Azure :
--Création d'une base de données utilisant le "credential" CREATE DATABASE FranmerCloudDb ON (NAME = TestDB1_data, FILENAME = 'https://mysqldata.blob.core.windows.net/franmersqlfiles/FranmerCloudDb.mdf') LOG ON (NAME = TestDB1_log, FILENAME = 'https://mysqldata.blob.core.windows.net/franmersqlfiles/FranmerCloudDb.ldf') GO
Si vous allez dans les propriétés de la nouvelle base de données, dans le menu “Files”, on voit bien que les fichiers se trouvent dans Azure (Colonne “Path”)
Si on regarde au niveau du conteneur, on y voit bien nos fichiers :
La vidéo se trouve ici !
Pour illustrer l’article Server 2014 et intégration avec le stockage Azure, voici une vidéo détaillant toutes les étapes de cette intégration :
Dans un article précédent, nous avons vu une fonctionnalité de Power BI Data Mangement Gateway qui permettait de mettre à jour, un rapport se trouvant dans Office 365, avec des données se trouvant sur site. Ce billet va traiter d’une autre fonctionnalité de la passerelle, permettant d’exposer en flux Odata les informations d’une base de données se trouvant sur site. Par ce biais, via Power Query, il sera possible de récupérer les informations de cette base, même en étant en dehors du réseau de l’entreprise...A partir du moment où Excel est connecté à un site Power BI avec une passerelle de configurée.
Je pars du principe que la passerelle est déjà configurée (sinon, tout est expliqué ici).
Dans le centre d’administration de power BI pour Office 365, cliquez sur “Nouvelle source de données”
Décochez la case “Activer l’accès par le Cloud….”, puis cochez la case “Activer le flux Odata…”. Cliquez sur le bouton “Suivant”
Dans la fenêtre “Informations de connexion”, renseignez les informations de connexion de la base que vous souhaitez exposer aux utilisateurs finaux.
Cliquez sur le bouton “Informations d’identification”
Dans la fenêtre “Gestionnaire de source de données”, renseignez les informations d’identification à la source de données que vous souhaitez partager.
Cliquez sur le bouton “Ok”, puis sur le bouton “Suivant”
La fenêtre “Paramètres des données” s’affiche et propose les tables et vues que l’on souhaite exposer. Décochez les cases des tables ou vues que vous ne souhaitez pas rendre disponibles aux utilisateurs finaux. Cliquez sur le bouton “Suivant”.
Dans la fenêtre “Utilisateur et groupes”, définissez les utilisateurs qui auront le droit d’utiliser cette source de données. Cliquez sur le bouton “Terminer”.
Votre nouvelle source de données doit apparaitre dans la liste des sources de données. Dans cet exemple, on retrouve bien “X1_Odata”.
Depuis Excel 2013, connectez-vous à votre site Office 365. Dans cet exemple, mon site Office 365 s’appelle “Frite”.
Depuis le ruban Excel, cliquez sur “Power Query”, puis sur le bouton “Online Search”.
Sur la droite d’Excel, le volet “Online Search” s’ouvre. Entrez les mots clefs de recherche puis cliquez sur la loupe. Le résultat apparait en dessous.
Maintenant, à droite de la loupe, cliquez sur la flèche, à côté du mot “All”. Sélectionnez “Organization” afin de limiter les recherches aux informations se trouvant sur le site Office 365. Dans l’exemple ci-dessous, seules les informations liées à nos sources de données Power BI sont maintenant accessibles. Depuis notre source X1_Odata, on retrouve aussi bien les données liées aux tables que celles liées à la vue :
Sélectionnez les informations souhaitées afin de les rapatrier dans le fichier Excel. Ci-dessous, après avoir cliqué sur la vue, les informations sont affichées dans Excel sous forme d’un tableau.
Un des avantages de la passerelle, c’est de permettre la mise à jour des rapports lorsque les informations, se trouvant sur site, ont été modifiées.
Pour illustrer cette fonctionnalité, je vais modifier les informations directement dans ma base de données. Ci-dessous, je passe les quantités de 7 à 77.
Modification des données :
Au niveau d’Excel, dans le menu “Query”, la mise à jour du rapport se fait en cliquant sur le bouton “Refresh”:
Après quelques secondes, le tableau se met à jour et les ventes passent de 7 à 77. Ci-dessous une copie d’écran après la mise à jour
Voilà pour la description de cette fonctionnalité de Power BI Data Management Gateway. Une vidéo illustrant cet article sera bientôt disponible.
Afin d’illustrer l’article Power BI : Data Management Gateway et flux Odata, voici une vidéo reprenant toutes les étapes de la mise en place de la passerelle Power BI afin d’exposer vos données en flux oData
L’édition 2013 des journées SQL server aura lieu les 2 et 3 décembre au centre de conférence de Microsoft, à Issy-Les-Moulineaux.
Les Journées SQL Server, ce sont 2 jours de conférences gratuites sur SQL Server et tous ses sujets connexes. Ce sont plus de 30 sessions présentées par des experts reconnus. Ci dessous l’agenda presque définitif
Au plaisir de vous voir lors des journées SQL Server ou d’un IT Camp SQL 2014/Power BI.