Web Data Connection

Power Query – OData, Web et VSTS

Par Simon-Pierre Morin, ing.
(niveau intermédiaire)

Dans le cadre de l’Agile tour Québec 2023 et de la communauté PowerBI de l’Estrie, j’ai présenté de façon sommaire des solutions afin de se connecter à des sources web. Dans ce billet, je vais présenter le détail technique de ces connecteurs en indiquant les avantages et les points à faire attention.

OData.Feed

Il s’agit d’un connecteur pour lire un flux OData. Ces flux de données (appelé feed) sont encore assez répandus lorsqu’il y a une grande quantité de données, notamment pour des données journalisées ou pour des instruments historisant leurs mesures en temps réel. Il existe également plusieurs services utilisant des cubes analytiques ou technologies similaires qui retournent leurs données en utilisant un feed plutôt qu’une réponse HTTP-GET en JSON.

L’un des principaux avantages des feeds est que la réponse reçue utilise une pagination naturelle. La réponse ne renvoyant que les premiers résultats pour économiser en ressources, dès que nous tentons d’afficher la suite, nous l’obtenons grâce au mécanisme qui nous donne la prochaine page. Soyez cependant averti que si votre système demande l’entièreté des données d’un seul coup, vous perdrez ce bénéfice. À l’interne, PowerQuery et PowerBI conserveront en cache les résultats déjà obtenus et n’interrogeront le flux que pour obtenir les informations de la page suivante.

La syntaxe est très simple

OData.Feed(queryURL, header, options)

Même si elle est optionnelle, plusieurs services requièrent de spécifier l’implémentation :

OData.Feed(queryURL, null, [Implementation="2.0"])

La structure de l’URL d’un feed est la suivante :

  • protocole
  • domaine
  • chemin d’accès OData et version
  • feed
  • requête

Donc pour la requête suivante

https://monserveur.ca/_odata/v4.0-preview/Employee?$select=id,firstname,lastname,email
StructureÉlément de l’URL
ProtocoleHTTPS
Domainemonserveur.ca
chemin OData/_odata/v4.0-preview/
Flux (feed)Employee
Requête$select=id,firstname,lastname,email
association de la structure de la requête à la structure URL

Dans cet exemple, le chemin d’accès OData a été simplifié. Certains serveurs ont un chemin d’accès entre le domaine et la portion notée _odata de l’URL. À noter que le OData est offert en plusieurs versions et que chaque service peut implémenter une ou plusieurs versions. De manière générale, la version utilisée est spécifiée dans l’URL (bien qu’il peut y avoir des exceptions).

À noter que la syntaxe de la requête ne lui permet pas d’être mise dans l’option Query. À noter également que les connexions se font par feed  et non par requête. Donc si vous avez plus d’une requête à un même flux de données, les informations d’authentification ne seront demandées qu’une seule fois. Par contre, si vous avez différents feeds, vous devrez vous authentifier pour chacun d’eux.

Pour plus d’information, visitez la documentation officielle.

Avantages

  • Acquisition rapide des données
  • Pagination automatique et fluide

Inconvénient

  • À tendance à multiplier les connexions

Web.Contents

Il s’agit du connecteur générique pour les API REST. Polyvalent, il permet d’invoquer toutes requêtes de type GET ou POST. De plus, grâce à la propriété « RelativePath », il permet d’avoir une seule connexion par URL de base (la partie non changeante de nos requêtes).

Attention à ce que votre URL de base ne nécessite qu’une seule autorité de connexion (incluant les connexions anonymes). Par exemple, si vous voulez vous connecter à un serveur Jira Cloud et que l’adresse avait ce modèle https://monentreprise.com/jira/Projet1, il est possible que les règles de votre entreprise limitent les informations d’authentifications non pas au domaine, mais plutôt à certains chemins d’accès. Dans ce cas, votre URL de base ne pourra pas être https://monentreprise.com, mais devra plutôt être https://monentreprise.com/jira ou voir même inclure le nom du projet.

Quelques exemples d’utilisation de Web.Contents retournant la liste des résultats de recherches « Power Query » avec une requête de type GET

  1. La méthode directe
Web.Content("https://bing.com/search?q=Power+Query")
  • En utilisant RelativePath uniquement
Web.Contents(
    "https://www.bing.com",
    [
        RelativePath = "search?q=Power+Query"
    ]
)
  • En utilisant RelativePath et Query
Web.Contents(
    "https://www.bing.com",
    [
        RelativePath = "search",
        Query = [q = "Power Query"]
    ]
)

Et voici un exemple de requête de type POST

let
    headers = [#"Content-Type" = "application/json"],
    postData = Json.FromValue(
        Json.Document(
           "{
               ""queryInfo"": {
               ""problemType"": ""Polynomial"",
               ""stepTypes"": [
                  ""quadratic expression with fraction with 1 variables"",
                   ""1 variables""
               ],
               ""market"": ""en""
             }
           }"
      )
   ),
    relPath = "cameraexp/api/v1/generateCustomBingAnswers",
    response = Web.Contents(
        "https://mathsolver.microsoft.com/ ",
        [
            Headers = headers,
            Content = postData,
            RelativePath = relPath
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

Il est à remarquer que l’identificateur GET ou POST n’est jamais mentionné dans Web.Contents. La présence de la propriété Content signifie à la fonction qu’il s’agit d’un POST. Il est également à noter qu’il est possible d’utiliser RelativePath, bien qu’il soit optionnel. Il est cependant recommandé de l’utiliser si plusieurs requêtes à ce même serveur sont faites pour éviter de multiplier les connecteurs.

Pour plus d’information, visitez la documentation officielle.

Il est bon de savoir qu’il est également possible d’invoquer les flux OData en utilisant la méthode GET. Cette technique permet de limiter le nombre de connecteurs pour un même serveur, mais perdra en contrepartie la faculté de performance du OData. Il est également important de savoir que les réponses seront tout de même paginées et renverront une réponse «nextLink» qui devra être gérée manuellement en utilisant List.Generate.

La technique décrite ci-dessous est donc à utiliser avec précaution en établissant les avantages et les inconvénients. Dans certains cas, il est préférable de continuer à utiliser OData.Feed pour la performance, mais si vos flux de données ne retournent qu’un petit ensemble de données et que vous voulez simplement éviter de gérer plusieurs connecteurs, voici comment procéder.

Pour ce faire, nous allons continuer d’utiliser RelativePath, pour gérer la portion du feed et de la requête de l’URL.

Supposons donc que nous ayons la requête suivante :

https://monserveur.ca/path/_odata/v4.0-preview/Employee?$select=id,firstname,lastname,email

Dans cet exemple, considérons https://monserveur.ca/path/_odata/v4.0-preview/ comme étant notre URL de base, mais nous pourrions très bien choisir de nous arrêter après path/. L’important est de ne pas inclure la partie « flux de données » et de s’assurer que la concaténation finale nous redonne l’URL complète. Notre flux (feed) est Employee et la requête est $select=id,firstname,lastname,email.

Étant donnée la structure d’une requête OData, il n’est pas possible d’utiliser Query pour la requête. Nous devons donc tout mettre dans le RelativePath. Cependant, ce qui nous sera retourné dans nextLink est une URL complète. Pour éviter de recevoir un message de connexion dynamique, nous devrons retirer la portion de notre URL de base pour retourner ce qui reste dans le prochain RelativePath.

URL = " https://monserveur.ca/path/_odata/v4.0-preview/",
feed = "Employee",
query = "?$select=id,firstname,lastname,email ",
FeedContentFix = List.Generate(
    () => Web.Content(url, [RelativePath=feed&query]),
    each Record.HasFields(_, "value"),
    each (
        if (
            Record.HasFields(_, "@odata.nextLink")
        ) then (
            let
                nextstep = _[#"@odata.nextLink"],
                nextquery = Text.Range(nextstep,Text.PositionOf(nextstep,feed))
             in
                 Web.Content(url, [RelativePath=nextquery]),
        ) else (
             [context="EoF"]
        )
    ),
    each _[value]
),

Avantages

  • Permet d’utiliser RelativePath
  • Fonctionne avec presque tous les API, incluant OData
  • Minimise les connecteurs à un même serveur/service

Inconvénients

  • Impossible à utiliser pour les services Azure
  • Les réponses paginées (> 10 000)

VSTS.AccountContents

Il s’agit d’un connecteur d’API spécifique à Microsoft. Il utilise la même syntaxe que Web.Contents, en ajoutant la propriété «Version» qui n’est pas supportée par Web.Contents. (cette propriété est optionnelle, mais est présente dans les enveloppes (wrappers) développées par Microsoft pour se connecter aux ressources wiql de Azure DevOps).

Il est à noter que l’ensemble de VSTS (qui inclue autant Feed que AccountContents) détecte l’organisation (sous-domaine) sans la nécessité de recourir à la technique de l’URL de base et qu’il ne demandera les données d’authentification qu’une seule fois par organisation.

Il est malheureusement impossible d’utiliser VSTS dans un flux de données (data flow) hébergé sur le service de Power BI. Toutefois, il est utilisable dans les autres versions de Power Query, que ce soit dans Excel ou dans Power BI directement et peut être publié sans problème.

Un autre irritant vient du fait que la connexion s’appellera VSTS sans autres détails. Ainsi, si vous faites un rapport se connectant à deux sources VSTS distinctes, les deux auront le même nom sans possibilité de distinguer les deux dans le gestionnaire de connexion. Il en sera de même pour les connexions de votre espace de travail. Si vous avez plusieurs rapports utilisant un connecteur VSTS, ceux-ci seront difficiles à distinguer dans le gestionnaire de connexion et de passerelle.

Avantages

  • Mêmes avantages que Web.Contents
  • Authentification unique pour l’ensemble de l’organisation ADO

Inconvénient

  • Se limite aux services Microsoft
  • Nom unique identique pour toutes les connexions
  • Ne fonctionne pas dans les flux de données (data flow)

VSTS.Feed

Il s’agit d’un connecteur spécifique de Microsoft qui s’utilise essentiellement comme OData.Feed et ayant les mêmes spécifications.

Tout comme VSTS.AccountContents, ce connecteur détecte l’organisation et ne créera qu’une seule connexion pour l’ensemble de l’organisation ou sous-domaine (incluant des connexions utilisant VSTS.AccountContents). Il a toutefois les mêmes inconvénients que ce dernier.

Avantages

  • Mêmes avantages de OData.Feed
  • Une seule connexion par organisation

Inconvénient

  • Se limite aux services Microsoft
  • Nom unique identique pour toutes les connexions
  • Ne fonctionne pas dans les flux de données (data flow)

Toujours curieux?

Pour ceux qui souhaitent poursuivre et en apprendre plus, je les invite à suivre la série de blogs intitulée « Série – La création d’un tableau de bord agile à l’aide de Power BI » de Simon-Pierre Morin et Mathieu Boisvert.

À lire également