Plan de consulta para Power Query

El plan de consulta para Power Query es una característica que proporciona una mejor vista de la evaluación de la consulta. Resulta útil ayudar a determinar por qué una consulta determinada podría no plegarse en un paso determinado.

A través de un ejemplo práctico, en este artículo se muestran el caso de uso principal y las posibles ventajas de usar la característica de plan de consulta para revisar los pasos de la consulta. Los ejemplos usados en este artículo se crearon con la base de datos de ejemplo AdventureWorksLT para Azure SQL Server, que puede descargar de bases de datos de ejemplo AdventureWorks.

Nota:

La característica de plan de consulta para Power Query solo está disponible en Power Query Online.

Este artículo se divide en una serie de pasos recomendados para interpretar el plan de consulta. Estos pasos son:

1. Revise los indicadores de plegado de consultas.
2. Seleccione el paso de consulta para revisar su plan de consulta.
3. Implementa los cambios en tu consulta.

Siga estos pasos para crear la consulta en su propio entorno de Power Query Online.

1. En Power Query: elija el origen de datos, seleccione Consulta en blanco.

2. Reemplace el script de la consulta en blanco por la consulta siguiente.

   let
     Source = Sql.Database("servername", "database"),
     Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
     #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
     #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
     #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
   in
     #"Kept bottom rows"
   

3. Cambie servername y database por los nombres correctos para su propio entorno.

4. (Opcional) Si intenta conectarse a un servidor y una base de datos para un entorno local, asegúrese de configurar una puerta de enlace para ese entorno.

5. Seleccione Siguiente.

6. En el Editor de Power Query, seleccione Configurar conexión y proporcione las credenciales de su origen de datos.

Nota:

Para obtener más información sobre cómo conectarse a SQL Server, vaya a base de datos de SQL Server.

Después de seguir estos pasos, la consulta tendrá un aspecto similar al de la imagen siguiente.

Esta consulta se conecta a la tabla SalesOrderHeader y selecciona algunas columnas de los últimos cinco pedidos con un valor TotalDue por encima de 1000.

Nota:

En este artículo se usa un ejemplo simplificado para mostrar esta característica, pero los conceptos descritos en este artículo se aplican a todas las consultas. Se recomienda tener un buen conocimiento del plegado de consultas antes de leer el plan de consulta. Para obtener más información sobre el plegado de consultas, vaya a Conceptos básicos de plegado de consultas.

1. Revise los indicadores de plegado de consultas

Nota:

Antes de leer esta sección, se recomienda revisar el artículo sobre indicadores de plegado de consultas.

El primer paso de este proceso es revisar la consulta y prestar mucha atención a los indicadores de plegado de consultas. El objetivo es revisar los pasos marcados como no plegados. A continuación, puede ver si realizar cambios en la consulta general podría hacer que esas transformaciones se doblen por completo.

En este ejemplo, el único paso que no se puede plegar es Mantener filas inferiores, lo que es fácil de identificar a través del indicador de paso no plegado. Este paso también es el último paso de la consulta.

El objetivo ahora es revisar este paso y comprender lo que se vuelve a plegar al origen de datos y lo que no se puede plegar.

2. Seleccione el paso de consulta para revisar su plan de consulta.

Ha identificado el paso Mantener las filas inferiores como un paso de interés, ya que no se vuelve a plegar al origen de datos. Haga clic con el botón derecho en el paso y seleccione la opción Ver plan de consulta. Esta acción muestra un nuevo cuadro de diálogo que contiene un diagrama para el plan de consulta del paso seleccionado.

Power Query intenta optimizar la consulta aprovechando la evaluación diferida y el plegado de consultas, como se mencionó en Conceptos básicos de plegado de consultas. Este plan de consulta representa la traducción optimizada de la consulta M a la consulta nativa que se envía al origen de datos. También incluye las transformaciones realizadas por el motor de Power Query. El orden en el que aparecen los nodos sigue el orden de la consulta a partir del último paso o salida de la consulta, que se representa en el extremo izquierdo del diagrama. En este caso, es el nodo Table.LastN que representa el paso Mantener filas inferiores.

En la parte inferior del cuadro de diálogo, hay una barra con iconos que le ayudan a acercar o alejar la vista del plan de consulta y otros botones para ayudarle a administrar la vista. Para la imagen anterior, la opción Ajustar a la vista de esta barra se usó para apreciar mejor los nodos.

Nota:

El plan de consulta representa el plan optimizado. Cuando el motor está evaluando una consulta, intenta integrar todos los operadores en un origen de datos. En algunos casos, incluso podría realizar alguna reordenación interna de los pasos para maximizar el plegado. Teniendo en cuenta este proceso, los nodos u operadores que quedan en este plan de consulta optimizado suelen contener la consulta de origen de datos "plegado". Los operadores que no se pudieron plegar se evalúan localmente.

Identificación de nodos plegados de otros nodos

Puede identificar los nodos de este diagrama como dos grupos:

• Nodos plegados: este nodo puede ser Value.NativeQuery o nodos de "origen de datos" como Sql.Database. Estos nodos también se pueden identificar con la etiqueta remota bajo su nombre de función.
• nodos no plegados: otros operadores de tabla, como Table.SelectRows, Table.SelectColumnsy otras funciones que no se pudieron plegar. Estos nodos también se pueden identificar con las etiquetas Examen completo y Streaming.

En la imagen siguiente se muestran los nodos plegados dentro del rectángulo rojo. El resto de los nodos no se pudieron volver a plegar al origen de datos. Debe revisar el resto de los nodos, ya que el objetivo es hacer que esos nodos se integren de nuevo al origen de datos.

Puede seleccionar Ver detalles en la parte inferior de algunos nodos para mostrar información extendida. Por ejemplo, los detalles del nodo Value.NativeQuery muestran la consulta nativa (en SQL) que se envía al origen de datos.

Es posible que la consulta que se muestra aquí no sea exactamente la misma consulta enviada al origen de datos, pero es una buena aproximación. En este caso, indica exactamente qué columnas se consultan de la tabla SalesOrderHeader. A continuación, cómo filtra esa tabla mediante el campo TotalDue para obtener solo las filas donde el valor de ese campo es mayor que 1000. El nodo situado junto a él, Table.LastN, se calcula localmente mediante el motor de Power Query, ya que no se puede plegar.

Nota:

Es posible que los operadores no coincidan exactamente con las funciones usadas en el script de la consulta.

Revise los nodos no plegados y considere las acciones para hacer que la transformación se pliegue.

Ahora ha determinado qué nodos no se pudieron plegar y se pueden evaluar localmente. Este caso solo tiene el nodo Table.LastN, pero en otros escenarios podría tener muchos más.

El objetivo es aplicar cambios a la consulta para que se pueda plegar el paso. Algunos de los cambios que podría aportar podrían variar desde la reorganización de sus pasos hasta aplicar una lógica alternativa a su consulta que sea más clara para el origen de datos. Esto no significa que todas las consultas y todas las operaciones se pueden plegar aplicando algunos cambios. Sin embargo, es recomendable determinar a través de prueba y error si la consulta se podría volver a plegar.

Dado que el origen de datos es una base de datos de SQL Server, si el objetivo es recuperar los últimos cinco pedidos de la tabla, una buena alternativa sería aprovechar las cláusulas TOP y ORDER BY en SQL. Dado que no hay ninguna cláusula BOTTOM en SQL, la transformación de Table.LastN en PowerQuery no se puede traducir a SQL. Puede quitar el paso Table.LastN y reemplazarlo por:

• Paso descendente de ordenación por la columna SalesOrderID de la tabla, ya que esta columna determina qué orden va primero y cuál se especificó por última vez.
• Seleccione las cinco primeras filas desde que se ordenó la tabla; esta transformación logra lo mismo que si fuera una fila inferior mantenida (Table.LastN).

Esta alternativa es equivalente a la consulta original. Aunque esta alternativa en teoría parece buena, debe realizar los cambios para ver si esta alternativa hace que este nodo vuelva a plegarse completamente al origen de datos.

3. Implementación de cambios en la consulta

Implemente la alternativa descrita en la sección anterior:

1. Cierre el cuadro de diálogo del plan de consulta y vuelva al Editor de Power Query.

2. Quite el paso Filas inferiores mantenidas.

3. Ordene la columna SalesOrderID en orden descendente.

   

4. Seleccione el icono de tabla en la esquina superior izquierda de la vista de vista previa de datos y seleccione la opción que lee Mantener las filas superiores. En el cuadro de diálogo, pase el número cinco como argumento y presione Aceptar.

   

Después de implementar los cambios, vuelva a comprobar los indicadores de plegado de consulta y compruebe si proporciona un indicador plegado.

Ahora es el momento de revisar el plan de consulta del último paso, que ahora es Mantener las filas principales. Ahora solo hay nodos plegados. Seleccione Ver detalles en Value.NativeQuery para comprobar qué consulta se envía a la base de datos.

Aunque en este artículo se sugiere qué alternativa aplicar, el objetivo principal es que aprenda a usar el plan de consulta para investigar el plegado de consultas. En este artículo también se proporciona visibilidad de lo que se envía al origen de datos y de qué transformaciones se realizan localmente.

Puede ajustar el código para ver el impacto que tiene en la consulta. Mediante el uso de los indicadores de plegado de consultas, también tiene una mejor idea de qué pasos impiden que la consulta se doble.