Compartir a través de

Compatibilidad con consultas nativas en conectores personalizados de Power Query

  • Artículo

Nota:

En este artículo se tratan temas avanzados sobre la implementación de la compatibilidad de consultas nativas con conectores personalizados, así como el plegado de consultas sobre ellos. En este artículo se asume que ya tiene un conocimiento práctico de estos conceptos.

Para más información sobre los conectores personalizados de Power Query, consulte Información general del SDK de Power Query.

En Power Query, puede ejecutar consultas nativas personalizadas en el origen de datos para recuperar los datos que está buscando. También puede habilitar la capacidad de mantener el plegado de consultas a lo largo de este proceso y los posteriores procesos de transformación realizados dentro de Power Query.

El objetivo de este artículo es mostrar cómo puede implementar esta funcionalidad para el conector personalizado.

Requisitos previos

En este artículo se usa como punto de partida un ejemplo que usa el controlador ODBC de SQL para su origen de datos. Actualmente, la implementación de la funcionalidad de consulta nativa solo se admite para los conectores ODBC que cumplen el estándar SQL-92.

El conector de ejemplo usa el controlador SQL Server Native Client 11.0. Asegúrese de que tiene instalado este controlador para seguir este tutorial.

También puede ver la versión finalizada del conector de ejemplo desde la carpeta Finalizar en el repositorio de GitHub.

Modificación de SQLCapabilities del conector

En el registro SqlCapabilities del conector de ejemplo, puede encontrar un campo de registro con el nombre Sql92Translation y el valor PassThrough. Este nuevo campo es necesario para que la consulta nativa se transfiera mediante Power Query sin ninguna validación.

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"
]),

Asegúrese de que este campo aparece en el conector antes de avanzar. Si no es así, verá advertencias y errores más adelante cuando tenga que utilizar una funcionalidad que no es compatible porque no está declarada por el conector.

Compile el archivo del conector (como .mez o.pqx) y cárguelo en Power BI Desktop para realizar pruebas manuales y definir el destino de la consulta nativa.

Prueba manual de las funcionalidades de consulta nativas del conector

Nota:

Para este artículo, usaremos la base de datos de muestra AdventureWorks2019. Pero puede seguir con cualquier base de datos SQL Server de su elección y hacer los cambios necesarios en las características específicas de la base de datos elegida.

En este artículo, la forma en que se implementará la compatibilidad de consultas nativas es solicitando al usuario que introduzca tres valores:

  • Nombre del servidor
  • Nombre de la base de datos
  • Consulta nativa en el nivel de base de datos

Ahora, en Power BI Desktop, vaya a la experiencia Obtener datos y busque el conector con el nombre SqlODBC Sample.

Screenshot of the connector found inside the get data experience of Power BI Desktop.

En el cuadro de diálogo del conector, escriba los parámetros del servidor y el nombre de la base de datos. A continuación, seleccione Aceptar.

Screenshot of connector dialog with server and database as parameters.

Aparece una nueva ventana del navegador. En Navegador, puede ver el comportamiento de navegación nativo desde el controlador SQL que muestra la vista jerárquica del servidor y las bases de datos que contiene. Haga clic con el botón derecho en la base de datos AdventureWorks2019 y seleccione Transformar datos.

Screenshot of the transform data option from the contextual menu inside the Navigator window.

Esta selección le lleva al editor de Power Query y a una vista previa de lo que es efectivamente el destino de la consulta nativa, ya que todas las consultas nativas deben ejecutarse en el nivel de base de datos. Inspeccione la barra de fórmulas del último paso para comprender mejor cómo debe navegar el conector al destino de las consultas nativas antes de ejecutarlas. En este caso, la barra de fórmulas muestra la siguiente información:

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

Origen es el nombre del paso anterior que, en este caso, es simplemente la función publicada del conector con los parámetros transferidos. La lista y el registro que contiene solo ayudan a navegar por una tabla a una fila específica. La fila se define mediante los criterios del registro, donde el campo Nombre debe ser igual a AdventureWorks2019 y el campo Tipo debe ser igual a Base de datos. Una vez que se encuentra la fila, el valor de [Data] que queda fuera de la lista {} permite que Power Query acceda al valor dentro del campo Datos, que en este caso es una tabla. Puede volver al paso anterior (Origen) para comprender mejor esta navegación.

Screenshot of a table that shows the values and fields that were used for the navigation step.

Consulta nativa de prueba

Ahora que tiene identificado el destino, cree un paso personalizado después del paso de navegación seleccionando el icono fx en la barra de fórmulas.

Screenshot of the fx button inside the formula that's used to create a custom step.

Reemplace la fórmula dentro de la barra de fórmulas por la fórmula siguiente y, a continuación, seleccione Intro.

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

Después de aplicar este cambio, debe aparecer una advertencia debajo de la barra de fórmulas que solicita permiso para ejecutar la consulta nativa en el origen de datos.

Screenshot of the permission is required to run this native database query warning message.

Seleccione Editar permiso. Se muestra un nuevo cuadro de diálogo de Consulta de base de datos nativa que intenta avisarle de las posibilidades de ejecutar consultas nativas. En este caso, sabemos que esta instrucción SQL es segura, por lo que puede seleccionar Ejecutar para ejecutar el comando.

Screenshot showing how to approve a native database query dialog.

Después de ejecutar la consulta, aparece una vista previa de la consulta en el editor de Power Query. Esta vista previa valida que el conector sea capaz de ejecutar consultas nativas.

Screenshot of the native query executed in initial connector development and testing.

Implementación de la lógica de consulta nativa en el conector

Con la información recopilada de las secciones anteriores, el objetivo ahora es traducir dicha información en código para el conector.

La forma en que puede realizar esta traducción es agregando un nuevo campo de registro NativeQueryProperties al registro Publicar del conector, que en este caso es el registro SqlODBC.Publish. El registro NativeQueryProperties desempeña un papel fundamental en la definición de cómo interactuará el conector con la función Value.NativeQuery.

El nuevo campo de registro consta de dos campos:

  • NavigationSteps: este campo define cómo debe realizar o gestionar el conector la navegación. Contiene una lista de registros que describen los pasos para navegar a los datos específicos que se desean consultar mediante la función Value.NativeQuery. Dentro de cada registro, define qué parámetros son obligatorios o necesarios para que dicha navegación llegue al destino deseado.
  • DefaultOptions: este campo ayuda a identificar cómo se deben incluir o agregar determinados parámetros opcionales al registro de opciones Value.NativeQuery. Proporciona un conjunto de opciones predeterminadas que se pueden usar al consultar el origen de datos.

Los pasos de navegación se pueden clasificar en dos grupos. El primero contiene los valores especificados por el usuario final, como el nombre del servidor o la base de datos, en este caso. El segundo contiene los valores derivados de la implementación del conector específico, como el nombre de los campos que no se muestran al usuario durante la experiencia de obtención de datos. Estos campos podrían incluir Name, Kind, Data y otros, en función de la implementación del conector.

En este caso, solo había un paso de navegación que constaba de dos campos:

  • Nombre: este campo es el nombre de la base de datos que transfirió el usuario final. En este caso, era AdventureWorks2019, pero este campo siempre debe transferirse tal cual lo introdujo el usuario final durante la experiencia de obtención de datos.
  • Tipo: este campo es información que no es visible para el usuario final y que es específica de la implementación del conector o del controlador. En este caso, este valor identifica el tipo de objeto al que se debe acceder. Para esta implementación, este campo será un valor fijo que consta de la cadena Database.

Esta información se traducirá al código siguiente. Este código debe agregarse como un nuevo campo al registro SqlODBC.Publish.

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

Importante

El nombre de los campos distingue entre mayúsculas y minúsculas y se debe usar como se muestra en el ejemplo anterior. Toda la información transferida a los campos, ya sea ConstantValue, IndexName o FieldDisplayName debe derivarse del código M del conector.

Para los valores que se transferirán a partir de lo introducido por el usuario, puede utilizar el par FieldDisplayName y IndexName. Para los valores fijos o predefinidos que el usuario final no puede transferir, se puede usar el par ConstantValue y IndexName. En este sentido, el registro NavigationSteps consta de dos campos:

  • Indices: define qué campos y valores se deben usar para navegar al registro que contiene el destino de la función Value.NativeQuery.
  • FieldAccess: define qué campo contiene el destino, que suele ser una tabla.

DefaultOptions

El campo DefaultOptions permite transferir parámetros opcionales a la función Value.NativeQuery al usar la funcionalidad de consulta nativa para el conector.

Para conservar el plegado de consultas después de una consulta nativa y suponiendo que el conector tenga funcionalidades de plegado de consultas, puede usar el código de ejemplo siguiente para EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Con estos cambios implementados, compile el conector y cárguelo en Power BI Desktop para realizar pruebas y validación.

Prueba y validación del conector

En Power BI Desktop con su nuevo conector personalizado, inicie el conector desde la experiencia Obtener datos. Al iniciar el conector, observará que el cuadro de diálogo ahora tiene un campo de texto largo con el nombre Consulta nativa y, entre paréntesis, tiene los campos necesarios para que funcione. Escriba los mismos valores para el servidor, la base de datos y la instrucción SQL que escribió anteriormente al probar el conector.

Screenshot of the connector dialog with the native query long text field shown.

Después de seleccionar Aceptar, se muestra una vista previa de la tabla de la consulta nativa ejecutada en un cuadro de diálogo nuevo.

Screenshot of the dialog with a table preview of the executed native query.

Seleccione Aceptar. Una nueva consulta se cargará ahora dentro del editor de Power Query, donde puede realizar más pruebas del conector según sea necesario.

Nota:

Si el conector tiene funcionalidades de plegado de consultas y se ha definido EnableFolding=true explícitamente como parte del registro opcional para Value.NativeQuery, puede probar aún más el conector en el editor de Power Query comprobando si las transformaciones se vuelven a plegar al origen o no.