Prise en charge native des requêtes dans les connecteurs personnalisés Power Query

Dans Power Query, vous pouvez exécuter des requêtes natives personnalisées sur votre source de données pour récupérer les données que vous recherchez. Vous pouvez également activer la possibilité de maintenir le pliage des requêtes tout au long de ce processus et les processus de transformation suivants effectués à l’intérieur de Power Query.

Le but de cet article est de présenter comment vous pouvez mettre en œuvre une telle capacité pour votre connecteur personnalisé.

Prérequis

Cet article utilise comme point de départ un exemple qui utilise le pilote ODBC SQL pour sa source de données. La mise en œuvre de la capacité de requête native est actuellement prise en charge uniquement pour les connecteurs ODBC qui respectent la norme SQL-92.

L’exemple de connecteur utilise le pilote SQL Server Native Client 11.0. Assurez-vous que ce pilote est installé pour suivre ce didacticiel.

Vous pouvez également afficher la version finale de l’exemple de connecteur à partir du dossier Finish dans le dépôt GitHub.

Modifier les SQLCapabilities de votre connecteur

Dans l'enregistrement SqlCapabilities de l’exemple de connecteur, vous pouvez trouver un champ d’enregistrement portant le nom Sql92Translation et la valeur PassThrough pour celle-ci. Ce nouveau champ est nécessaire pour que la requête native soit passée en utilisant Power Query sans aucune validation.

SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
    // Place custom overrides here
    // The values below are required for the SQL Native Client ODBC driver, but might
    // not be required for your data source.
        SupportsTop = false,
        SupportsDerivedTable = true,
        Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
        GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
        FractionalSecondsScale = 3,
        Sql92Translation = "PassThrough"
]),

Assurez-vous que ce champ apparaît dans votre connecteur avant de continuer. Si ce n’est pas le cas, vous rencontrerez des avertissements et des erreurs plus tard lorsqu’il s’agira d'utiliser une capacité qui n’est pas prise en charge, car elle n’est pas déclarée par le connecteur.

Générez le fichier de connecteur (en tant que .mez ou.pqx) et chargez-le dans Power BI Desktop pour les tests manuels et définissez la cible pour votre requête native.

Tester manuellement les fonctions de requête native de votre connecteur

La façon dont la prise en charge des requêtes natives sera mise en œuvre dans cet article est que l’utilisateur sera invité à saisir trois valeurs :

• Nom du serveur
• Nom de la base de données
• Requête native au niveau de la base de données

À présent dans Power BI Desktop, accédez à l’expérience Obtenir des données et recherchez le connecteur avec le nom Exemple SqlODBC.

Pour la boîte de dialogue du connecteur, entrez les paramètres de votre serveur et du nom de votre base de données. Sélectionnez ensuite OK.

Une nouvelle fenêtre de navigateur s’affiche. Dans Le navigateur, vous pouvez afficher le comportement de navigation natif à partir du pilote SQL qui affiche la vue hiérarchique du serveur et des bases de données qu’il contient. Cliquez avec le bouton droit sur la base de données AdventureWorks2019, puis sélectionnez Transformer des données.

Cette sélection vous amène à l’éditeur Power Query et un aperçu de ce qui est effectivement la cible de votre requête native, car toutes les requêtes natives doivent s’exécuter au niveau de la base de données. Inspectez la barre de formule de la dernière étape pour mieux comprendre comment votre connecteur doit accéder à la cible de vos requêtes natives avant de les exécuter. Dans ce cas, la barre de formule affiche les informations suivantes :

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

La source est le nom de l’étape précédente qui, dans ce cas, est simplement la fonction publiée de votre connecteur avec les paramètres passés. La liste et l’enregistrement à l’intérieur de celui-ci permettent simplement de naviguer dans une table vers une ligne spécifique. La ligne est définie par les critères de l’enregistrement où le nom du champ doit être égal à AdventureWorks2019 et le genre de champ doit être égal à la base de données. Une fois la ligne située, l’extérieur [Data] de la liste {} permet à Power Query d’accéder à la valeur à l’intérieur du champ Données, qui, dans ce cas, est une table. Vous pouvez revenir à l’étape précédente (Source) pour mieux comprendre cette navigation.

Tester la requête native

Avec la cible maintenant identifiée, créez une étape personnalisée après l’étape de navigation en sélectionnant l’icône fx dans la barre de formule.

Remplacez la formule à l’intérieur de la barre de formule par la formule suivante, puis sélectionnez Entrer.

= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
  FROM [Person].[Address]")

Après avoir appliqué cette modification, un avertissement doit apparaître sous la barre de formule demandant l’autorisation d’exécuter la requête native sur votre source de données.

Sélectionnez Modifier les autorisations. Une nouvelle boîte de dialogue Requête de base de données native s’affiche qui tente de vous avertir des possibilités d’exécution de requêtes natives. Dans cet incident, nous savons que cette instruction SQL est sécurisée. Sélectionnez Exécuter pour exécuter la commande.

Après avoir exécuté votre requête, un aperçu de votre requête s’affiche dans l’éditeur Power Query. Cette préversion vérifie que votre connecteur est capable d’exécuter des requêtes natives.

Implémenter une logique de requête native dans votre connecteur

Avec les informations collectées à partir des sections précédentes, le but est maintenant de traduire ces informations en code pour votre connecteur.

La façon dont vous pouvez effectuer cette traduction consiste à ajouter un nouveau champ d’enregistrement NativeQueryProperties à l’enregistrement Publish de votre connecteur, qui, dans ce cas est SqlODBC.Publish l'enregistrement. L’enregistrement NativeQueryProperties joue un rôle crucial dans la définition de la façon dont le connecteur interagit avec la fonction Value.NativeQuery.

Le nouveau champ d’enregistrement comprend deux champs :

• NavigationSteps : ce champ définit la façon dont la navigation doit être effectuée ou gérée par votre connecteur. Il contient une liste d’enregistrements qui décrivent les étapes de navigation vers les données spécifiques que vous souhaitez interroger à l’aide de la fonction Value.NativeQuery. Dans chaque enregistrement, il définit les paramètres requis ou nécessaires pour que cette navigation atteigne la cible souhaitée.
• DefaultOptions : ce champ permet d’identifier la façon dont certains paramètres facultatifs doivent être inclus ou ajoutés à l’enregistrement d’options Value.NativeQuery. Il fournit un ensemble d’options par défaut qui peuvent être utilisées lors de l’interrogation de la source de données.

NavigationSteps

Vos étapes de navigation peuvent être classées en deux groupes. Le premier contient ces valeurs entrées par l’utilisateur final, telles que le nom du serveur ou de la base de données, dans ce cas. La deuxième contient ces valeurs dérivées par l’implémentation de connecteur spécifique, telles que le nom des champs qui ne sont pas affichés à l’utilisateur pendant l’expérience d’obtention des données. Ces champs peuvent inclure Name, Kind, Data, et d’autres en fonction de l’implémentation de votre connecteur.

Dans ce cas, il n’y avait qu’une seule étape de navigation composée de deux champs :

• Nom: ce champ est le nom de la base de données passée par l’utilisateur final. Dans ce cas, c'était AdventureWorks2019, mais ce champ doit toujours être passé tel quel à partir de ce que l’utilisateur final a entré pendant l’expérience d'obtention des données.
• Genre : ce champ est des informations qui ne sont pas visibles par l’utilisateur final et qui sont spécifiques à l’implémentation du connecteur ou du pilote. Dans ce cas, cette valeur identifie le type d’objet à accéder. Pour cette implémentation, ce champ est une valeur fixe qui se compose de la chaîne Database.

Ces informations seront traduites dans le code suivant. Ce code doit être ajouté en tant que nouveau champ à votre SqlODBC.Publish enregistrement.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

Pour les valeurs qui seront transmises à partir de ce que l’utilisateur a entré, vous pouvez utiliser la paire FieldDisplayName et IndexName. Pour les valeurs fixes ou prédéfinies et qui ne peuvent pas être passées par l’utilisateur final, vous pouvez utiliser la paire ConstantValue et IndexName. Dans ce sens, l’enregistrement NavigationSteps se compose de deux champs :

• Index : définit les champs et les valeurs à utiliser pour accéder à l’enregistrement qui contient la cible de la fonction Value.NativeQuery.
• FieldAccess : définit le champ qui contient la cible, qui est généralement une table.

DefaultOptions

Le DefaultOptions champ vous permet de transmettre des paramètres facultatifs à la fonction lors de l’utilisation Value.NativeQuery de la capacité de requête native pour votre connecteur.

Pour conserver le pliage des requêtes après une requête native et en supposant que votre connecteur dispose de capacités de pliage de requête, vous pouvez utiliser l’exemple de code suivant pour EnableFolding = true.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    },

    DefaultOptions = [
        EnableFolding = true
    ]
]

Avec ces modifications en place, générez le connecteur et chargez-le dans Power BI Desktop pour le test et la validation.

Test et validation du connecteur

Dans Power BI Desktop avec votre nouveau connecteur personnalisé en place, lancez le connecteur à partir de l’expérience Obtention des données. Lorsque vous lancez le connecteur, vous remarquerez que la boîte de dialogue comporte désormais un champ de texte long avec le nom de requête native et, entre parenthèses, il contient les champs requis pour qu’il fonctionne. Entrez les mêmes valeurs pour le serveur, la base de données et la déclaration SQL que vous avez entrées précédemment lors du test du connecteur.

Une fois que vous avez sélectionné OK, un aperçu de table de la requête native exécutée s’affiche dans une nouvelle boîte de dialogue.

Cliquez sur OK. Une nouvelle requête se charge désormais dans l’éditeur Power Query, où vous pouvez effectuer des tests supplémentaires de votre connecteur en fonction des besoins.