Aujourd’hui, je vais expliquer comment déployer une passerelle PowerBI fiable et fonctionnelle. En cadeau, des extraits de code et un repo GitHub pour déployer le tout en 5min avec Terraform :)

Qu’est-ce qu’une passerelle de données locale PowerBI ? A quoi sert-elle ?

Pourquoi, me demandez-vous, auriez vous même besoin de PowerBI si vous avez à dispo le cloud AWS et son offre complète de services autour de la data (pour en nommer quelques-uns: Glue pour l’ETL, S3 pour le stockage, Athena pour requêter en SQL, Redshift pour l’entrepôt et Quicksight pour la data visualisation) ?

Bon.. vous savez que tout le monde aime Excel? Eh bien, PowerBI est le nouvel Excel :) Sa capacité à fournir de beaux graphiques (si vous avez bossé chez un éditeur de logiciel, vous savez d’expérience qu’aucun soft ne se vend sans dashboard, même si personne ne les utilise à la fin) à partir de fichiers .xlsx locaux et de base de données fait croire aux utilisateurs que le data management est chose simple (en mettant sous le tapis les difficultés liées au data lineage, au cataloging et des difficutlés “mineures” comme la scalabilité, la securité et la gouvernance).

Donc vos données sont dans le cloud, et quelqu’un veut les analyser avec Power BI. Bon professionnel que vous êtes, vous ne les laissez pas exécuter de requêtes en direct sur la BDD de prod (que celui qui n’a jamais péché…). Voici donc à quoi sert la “Passerelles de données locale PowerBI” !

• La passerelle sert de proxy sortant entre votre BDD et le service PowerBI, ce qui limite le besoin de monter un lien réseau entre le service/Azure (ou les utilisateurs) et la base de données

• Si vous ne controllez pas ce que les utilisateurs vont faire dans PowerBI, je vous recommande d’éviter le mode Direct Query *et de paramétrer une synchronisation périodique des données dans ce que PowerBI appelle un *Dataflow (c’est un stockage local au service que les utilisateurs vont attaquer).

Voici ce que nous allons déployer

Le code dans le repo à la fin de cet article déploie ce qui suit :

Pourquoi déployer la Data gateway dans un AutoscalingGroup EC2 ?

• Le coût **(et l’empreinte environnementale) : l’ASG permet de **planifier la création/suppression **de notre instance EC2. Pour faire une synchro incrémentale, nous avons juste besoin de booter l’instance quelques minutes avant le déclenchement du job de synchro et de l’éteindre après. Dans notre cas, j’utilise aussi une **requête spot qui permet encore de réduire le coût de ~30%.. je vais peut-être louper un cycle de synchro si AWS n’a pas de ressource dispo (REX : en 4 mois, j’ai eu 2 cycles manqués). Une m5a.large (type d’instance qui correspond aux exigences assez élevées de Microsoft pour ce cas d’usage) qui tourne 3 fois par jour pendant 20 minutes me coûtera $0.12/j soit moins de $50.

• Fiabilité sans maintenance: l’instance démarre chaque fois de la même image. De cette façon, je n’aurai jamais de souci de type disque plein, fuite mémoire, Windows qui ralentit (oui, la passerelle doit tourner sous Windows server). Un des principes devops (Pets vs. Cattle) est de considérer les machines comme dispensables!

Puisque les experts PowerBI m’ont indiqué que la Passerelle aura besoin de mises à jour régulières pour suivre le cycle de release de PowerBI, mon archi initiale impliquait un pipeline EC2 Image Builder pour générer une image machine en combinant *a. *la dernière image (AMI) Windows *b. *la dernière version du package d’installation de passarelle PowerBI, et *c. *un script d’installation-configuration de la passerelle.

Pauvre fou! Ce serait espérer que Microsoft fournisse un produit fini, installable de façon silencieuse. Au risque de vous décevoir :

• Même si Microsoft a publié un module Powershell pour automatiser le setup, les cmdlets qu’il propose permettent uniquement de créer un nouveau cluster mais **pas d’enregistrer une nouvelle machine comme participante d’un cluster existant. **Autant pour l’idempotence.

• La passerelle dépend de drivers tiers. Le driver pour PostgreSQL est npgsql (dans une version assez obsolète, la 4.0.10) qui doit être installée avec une option non active par default (Global Assembly Cache, ou GAC) qui ne peut l’être via une installation silencieuse. Au temps pour l’automatisation.

Pour ces raisons, j’ai dû me résoudre à devoir générer l’image Windows manuellement.

Déployer l’infrastructure

L’infra elle-même est relativement simple. Dans le moduel Autoscaling :

• On définit le planning de création/destruction
• On dit qu’on prend des instances au prix spot
• On donne à l’instance un rôle qui permet de s’y connecter en RDP via AWS Systems Manager
  On s’assure que l’instance peut joindre tant la BDD que le service PowerBI.
  Et voilà !

Un code Terraform pleinement fonctionnel est dispo sur mon compte Github : https://github.com/psantus/powerbi-onpremises-data-gateway.terraform

Déployez le dans un premier temps avec l’AMI Windows standard. Puis après avoir fait le setup initial décrit ci-dessous, faites un snapshot de la VM et dites à l’AutoScaling Group de l’utiliser pour les déploiments suivants.

Installation de la passerelle de données PowerBI

Après que vous avez déployé l’image Windows standard, vous pouvez suivre les étapes suivantes pour installer les paquets de la passerelle PowerBI Gateway et les configurer ainsi que le service PowerBI.

• Connectez vous à la machine via AWS Systems Manager

• Installez Powershell 7 (seule version compatible avec le module DataGateway) :

  msiexec.exe /package https://github.com/PowerShell/PowerShell/releases/download/v7.2.6/PowerShell-7.2.6-win-x64.msi /quiet ADD_EXPLORER_CONTEXT_MENU_OPENPOWERSHELL=1 ADD_FILE_CONTEXT_MENU_RUNPOWERSHELL=1 ENABLE_PSREMOTING=1 REGISTER_MANIFEST=1 USE_MU=1 ENABLE_MU=1 ADD_PATH=1

• Lancez une session PowerShell 7 puis installez le module DataGateway

  pwsh
  Install-Module DataGateway -Force
  Import-Module DataGateway -Force

• Créez une application sur l’Azure AD. Pour ce faire vous devez être admin Azure AD. L’App doit avoir les permissions Tenant.ReadWrite.All sur le service PowerBI.

• Générez un secret. Notez les éléments suivants pour la suite:
• App secret
• ClientID

• TenantID

• Connectez vous sur le service PowerBI avec l’App que vous venez de créer : (ici je suppose que le secret a été collé dans un fichier nommé “secret.txt” sur le Bureau — n’oubliez pas de le supprimer après !)

  Set secret in a secure string

  $secureClientSecret = (cat .\Desktop\secret.txt | ConvertTo-SecureString -AsPlainText -Force)

  Connect to the PowerBI Service

  Connect-DataGatewayServiceAccount -ApplicationId $AppId -ClientSecret $secureClientSecret -Tenant $TenantId

• Installez le paquet de la passerelle PowerBI

  Install-DataGateway -AcceptConditions

  Restart the service after installation (removes some random errors)

  net stop PBIEgwService
  net start PBIEgwService

• Installez le driver Postgres Npgsql v4.0.10. Seule cette version fonctionne (elle doit s’appuyer sur la même version de .NET que l’application PowerBI Gateway, cf. Microsoft site). Quand vous lancez le MSI, assurez-vous de cocher “Npgsql GAC Installation”.

• Créez une Passerelle de données sur le service PowerBI et configurez l’application pour créer un cluster:

  $GateWayDetails = Add-DataGatewayCluster -GatewayName "My Gateway" -RecoveryKey $secureClientSecret -OverwriteExistingGateway

  Get gateways and find the one you just created

  Get-DataGatewayCluster

  Put its detail in a variable

  $GateWayDetails = Get-DataGatewayCluster -Id "xxxxxx-xxxxxx-xxxxxx-xxxxx"

• Donnez des droits d’admin sur la passerelle à un utilisateur (ou un groupe)

  Add-DataGatewayClusterUser -GatewayClusterId $GateWayDetails.Id -PrincipalObjectId "Azure AD User or Group ID here" -AllowedDataSourceTypes $null -Role Admin

• Désormais, vous devriez voir la passerelle dans l’interface web PowerBI Gateway https://app.powerbi.com/groups/me/gateways

• (Nb : vous pouvez donner des permissions plus limitées sur la gateway, par exemple juste la possibilité de se connecter à une BDD PostgreSQL mais pas d’autres sources de données)

  $dsTypes = New-Object 'System.Collections.Generic.List[Microsoft.PowerBI.ServiceContracts.Api.DatasourceType]'
  $dsTypes.Add([Microsoft.PowerBI.ServiceContracts.Api.DataSourceType]::PostgreSql)
  Add-DataGatewayClusterUser -GatewayClusterId $GateWayDetails.Id -PrincipalObjectId "Azure AD User or Group ID here" -AllowedDataSourceTypes $dsTypes -Role ConnectionCreator

Ressources

le code Terraform démontré dans ce post est dispo dans un repo sur mon compte Github https://github.com/psantus/powerbi-onpremises-data-gateway.terraform

Si vous avez trouvé ce post utile, ou que vous avez la bonté de suggérer des améliorations (c’est mon premier post de blog, j’imagine donc qu’il est largement perfectible),n’hésitez pas à laisser un commentaire!