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.

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.

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.

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.

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.

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.

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.

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.

   

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.

   

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

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.

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.