Compartir a través de

Compatibilidad con consultas nativas en conectores personalizados de Power Query

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 supone que ya tiene conocimientos prácticos de estos conceptos.

Para más información sobre los conectores personalizados de Power Query, vaya a Introducción al 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.

Prerrequisitos

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.

Modifique las SQLCapabilities de su conector

En el SqlCapabilities registro del conector de ejemplo, puede encontrar un campo de registro con el nombre Sql92Translation y el valor PassThrough para él. Este nuevo campo es necesario para que la consulta nativa se pase 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 se trate de usar una funcionalidad que no se admite porque el conector no lo declara.

Compile el archivo del conector (como .mez o.pqx) y cárgelo 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:

En este artículo, usaremos la base de datos de ejemplo AdventureWorks2019. Pero puede seguir con cualquier base de datos de SQL Server de su elección y realizar los cambios necesarios cuando se trata de los detalles de la base de datos elegida.

La forma en que se implementará la compatibilidad con consultas nativas en este artículo es que se solicitará al usuario que escriba 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.

Captura de pantalla del conector que se encuentra dentro de la experiencia de obtención de datos de 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. Después, seleccione Aceptar.

Captura de pantalla del cuadro de diálogo del conector con el servidor y la base de datos como parámetros.

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 dentro de él. Haga clic con el botón derecho en la base de datos AdventureWorks2019 y seleccione Transformar datos.

Captura de pantalla de la opción transformar datos desde el menú contextual dentro de la ventana Navegador.

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]

Source es el nombre del paso anterior que, en este caso, es simplemente la función publicada del conector con los parámetros pasados. La lista y el registro dentro de ella solo ayudan a navegar por una tabla a una fila específica. La fila se define mediante los criterios del registro donde el campo Name debe ser igual a AdventureWorks2019 y el campo Kind debe ser igual a Database. Una vez que se encuentra la fila, el [Data] exterior 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.

Captura de pantalla de una tabla que muestra los valores y campos que se usaron para el paso de navegación.

Prueba de una consulta nativa

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

Captura de pantalla del botón fx dentro de la fórmula que se usa para crear un paso personalizado.

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

= 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.

Captura de pantalla del permiso necesario para ejecutar este mensaje de advertencia de consulta de base de datos nativa.

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

Captura de pantalla que muestra cómo aprobar un cuadro de diálogo de consulta de base de datos nativa.

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

Captura de pantalla de la consulta nativa ejecutada en el desarrollo y pruebas iniciales del conector.

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 Publish del conector, que en este caso es el SqlODBC.Publish registro. El NativeQueryProperties registro desempeña un papel fundamental en la definición de cómo interactuará el conector con la Value.NativeQuery función.

El nuevo campo de registro consta de dos campos:

  • NavigationSteps: este campo define cómo el conector debe realizar o controlar la navegación. Contiene una lista de registros que describen los pasos para navegar a los datos específicos que desea consultar mediante la Value.NativeQuery función . Dentro de cada registro, define qué parámetros son necesarios o necesarios para que dicha navegación alcance el destino deseado.
  • DefaultOptions: este campo ayuda a identificar cómo se deben incluir o agregar determinados parámetros opcionales al registro de Value.NativeQuery opciones. 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. La primera 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 pasó el usuario final. En este caso, era AdventureWorks2019, pero este campo siempre debe pasarse tal cual lo escribió el usuario final durante el proceso 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 tener acceso. 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 SqlODBC.Publish registro.

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

Importante

Los nombres de los campos distinguen mayúsculas de minúsculas y se deben usar como se muestra en el ejemplo anterior. Toda la información que se pasa a los campos, ya sea ConstantValue, IndexNameo FieldDisplayName debe derivarse del código M del conector.

Para los valores que se pasarán de lo especificado por el usuario, puede usar el par FieldDisplayName y IndexName. En el caso de los valores fijos o predefinidos y que no se pueden pasar por el usuario final, puede usar el par ConstantValue y IndexName. En este sentido, el registro NavigationSteps consta de dos campos:

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

DefaultOptions

El DefaultOptions campo permite pasar parámetros opcionales a la Value.NativeQuery función 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árgelo en Power BI Desktop para realizar pruebas y validación.

Prueba y validación del conector

En Power BI Desktop con el nuevo conector personalizado en su lugar, inicie el conector desde Obtener datos de la experiencia. 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.

Captura de pantalla del cuadro de diálogo del conector con el campo de texto largo de consulta nativa que se muestra.

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.

Captura de pantalla del cuadro de diálogo con una vista previa de la tabla de la consulta nativa ejecutada.

Selecciona 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.

  • Last updated on