Leer en inglés

Compartir a través de


Plan de consulta para Power Query (versión preliminar)

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

Por medio de un ejemplo práctico, este artículo mostrará el caso de uso principal y las posibles ventajas del uso de la característica del plan de consulta para revisar los pasos de la consulta. Los ejemplos usados en este artículo se han creado con la base de datos de ejemplo AdventureWorksLT para Azure SQL Server, que se 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.

Proceso sugerido para usar la característica de plan de consulta en Power Query mediante la revisión de los indicadores de plegado de consultas y, a continuación, revisar el plan de consulta de un paso seleccionado y, por último, implementar los cambios derivados de revisar el plan de consulta.

Este artículo se ha dividido en una serie de pasos recomendados para interpretar el plan de consulta. Los pasos son los siguientes:

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

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

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

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

    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 para el origen de datos.

Nota

Para obtener más información acerca de cómo conectarse a un SQL Server, vaya a Base de datos SQL Server.

Después de seguir estos pasos, la consulta será similar a la de la imagen siguiente.

Consulta de ejemplo con indicadores de plegado de consultas habilitados. Esta consulta se conecta a la tabla SalesOrderHeader y selecciona algunas columnas de los últimos cinco pedidos con un valor de TotalDue superior a 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. Antes de leer el plan de consulta, se recomienda tener un buen conocimiento sobre el plegado de consultas. Para obtener más información sobre el plegado de consultas, vaya a Conceptos básicos del plegado de consultas.

1. Revise los indicadores de plegado de consultas

Nota

Antes de leer esta sección, le recomendamos que revise el artículo sobre los 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. Luego puede ver si al realizar cambios en la consulta general se podría lograr que esas transformaciones se plegasen por completo.

Indicadores de plegado de consultas para la consulta de ejemplo dentro del panel Pasos aplicados.

En este ejemplo, el único paso que no se puede plegar es Kept bottom rows, lo que resulta fácil de identificar mediante al indicador de paso no plegado. Este paso también es el último paso de la consulta.

El objetivo ahora es revisar este paso y saber qué es lo que se está plegando de vuelta 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 Kept bottom rows como un paso de interés, ya que no se pliega de vuelta 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 correspondiente al plan de consulta del paso seleccionado.

Cuadro de diálogo Plan de consulta que muestra una vista de diagrama para el plan de consulta con nodos conectados por líneas. Power Query intenta optimizar la consulta aprovechando la evaluación diferida y el plegado de consultas, como se mencionó en Conceptos básicos del 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 que aparecen los nodos sigue el orden de la consulta a partir del último paso o la salida de la consulta, que se representa en el extremo izquierdo del diagrama y, en este caso, es el nodo Table.LastN que representa el paso Kept bottom rows.

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

Cuadro de diálogo de plan de consulta con los nodos ampliados para obtener una mejor vista.

Nota

El plan de consulta representa el plan optimizado. Cuando el motor está evaluando una consulta, intenta plegar 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 esto en cuenta, los nodos u operadores que quedan en este plan de consulta optimizado suelen contener la consulta de origen de datos "plegada" y los operadores que no se pudieron plegar y 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. También se pueden identificar con la etiqueta remoto bajo su nombre de función.
  • Nodos no plegados: otros operadores de tabla, como Table.SelectRows, Table.SelectColumns y otras funciones que no se han podido plegar. 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 plegar de vuelta al origen de datos. Tendrá que revisar el resto de los nodos, ya que el objetivo es intentar que esos nodos se plieguen de vuelta al origen de datos.

Controles de vista del plan de consulta en la parte inferior del cuadro de diálogo con la opción ajustar para ver seleccionada. Puede seleccionar Ver detalles en la parte inferior de algunos nodos para ver información ampliada. Por ejemplo, los detalles del nodo Value.NativeQuery muestran la consulta nativa (en SQL) que se enviará al origen de datos.

Vista de detalles del nodo Value.NativeQuery en el plan de consulta. 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 consultarán desde la tabla SalesOrderHeader y, a continuación, cómo filtrará esa tabla mediante el campo TotalDue para obtener solo filas donde el valor de ese campo sea superior a 1000. El motor de Power Query calcula localmente el nodo situado al lado, Table.LastN, 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 plantéese acciones para lograr que la transformación se pliegue.

Ahora ha determinado qué nodos no se pudieron plegar y se evaluarán 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 el paso se pueda plegar. Algunos de los cambios que podría implementar podrían variar desde la reorganización de los pasos hasta la aplicación de una lógica alternativa a la consulta que sea más explícita para el origen de datos. Esto no significa que todas las consultas y todas las operaciones se puedan plegar aplicando algunos cambios. Pero es recomendable determinar mediante ensayo y error si la consulta se podría plegar de vuelta.

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

  • Un paso descendente de ordenación por la columna SalesOrderID de la tabla, ya que esta columna determina qué pedido va primero y cuál es el último que se ha introducido.
  • Seleccione las cinco primeras filas desde que se ha ordenado la tabla, esta transformación logra lo mismo que si fuera Kept bottom rows (Table.LastN).

Esta alternativa es equivalente a la consulta original. Aunque esta alternativa en teoría parece buena, es necesario realizar los cambios para ver si esta alternativa hará que este nodo se pliegue por completo de vuelta al origen de datos.

3. Implemente los 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. Elimine el paso Kept bottom rows.

  3. Ordene la columna SalesOrderID en orden descendente.

    Ordenar la columna SalesOrderID en orden descendente mediante el menú de filtro automático.

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

    Con el menú contextual de la tabla para seleccionar la transformación Mantener las filas superiores para mantener solo las cinco primeras filas.

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

Todos los indicadores de plegado de consulta son verdes y muestran que se pueden plegar. La tabla final proporciona las mismas filas, pero en un orden diferente. Ahora es el momento de revisar el plan de consulta del último paso, que ahora es Conservar filas superiores. Ahora solo hay nodos plegados. Seleccione Ver detalles en Value.NativeQuery para comprobar qué consulta se envía a la base de datos.

Nuevo plan de consulta después de realizar los cambios en la consulta, que ahora muestra solo los nodos plegados, con Value.NativeQuery mostrando la instrucción SQL completa que evalúa la consulta.

Aunque en este artículo se sugiere qué alternativa aplicar, el objetivo principal es aprender 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 llevarán a cabo 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 obtendrá una mejor idea de qué pasos impiden que la consulta se pliegue.