Ejemplos de plegado de consultas

En este artículo se proporcionan algunos escenarios de ejemplo para cada uno de los tres resultados posibles para el plegado de consultas. También incluye algunas sugerencias sobre cómo sacar el máximo partido del mecanismo de estructura de consultas y el efecto que puede tener en tus consultas.

Escenario

Imagine un escenario en el que, con la base de datos Wide World Importers para Azure Synapse Analytics SQL Database, se le encarga crear una consulta en Power Query que se conecte a la fact_Sale tabla y recupere las últimas 10 ventas con solo los siguientes campos:

• Clave de venta
• Clave de cliente
• Clave de fecha de factura
• Description
• Cantidad

En este artículo se muestran tres maneras de lograr el mismo resultado con distintos niveles de optimización de consultas:

• Sin plegado de consultas
• Plegado de consultas parciales
• Plegado completo de consultas

Sin ejemplo de plegado de consultas

Después de conectarse a la base de datos y navegar a la fact_Sale tabla, seleccione la transformación Mantener filas inferiores que se encuentra dentro del grupo Reducir filas de la pestaña Inicio .

Después de seleccionar esta transformación, aparece un cuadro de diálogo nuevo. En este nuevo cuadro de diálogo, puede escribir el número de filas que desea conservar. En este caso, escriba el valor 10 y seleccione Aceptar.

A continuación, seleccione la transformación Elegir columnas que se encuentra dentro del grupo Administrar columnas de la pestaña Inicio . A continuación, puede seleccionar las columnas que desea mantener de la tabla y quitar el resto.

Por último, dentro del cuadro de diálogo Elegir columnas, seleccione las Sale Keycolumnas , Customer KeyInvoice Date Key, Description, y yQuantity, a continuación, seleccione Aceptar.

El ejemplo de código siguiente es el script M completo para la consulta que creó:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Kept bottom rows" = Table.LastN(Navigation, 10),
    #"Choose columns" = Table.SelectColumns(
        #"Kept bottom rows",
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    )
in
    #"Choose columns""

Sin plegado de consultas: Descripción de la evaluación de consultas

En Pasos aplicados en el editor de Power Query, observe que los indicadores de plegado de consulta para Conservar filas inferiores y Elegir columnas están marcados como pasos que se evalúan fuera del origen de datos o, en otras palabras, por el motor de Power Query.

Puede hacer clic con el botón derecho en el último paso de la consulta, el denominado Elegir columnas y seleccionar la opción que lee Ver plan de consulta. El objetivo del plan de consulta es proporcionarle una vista detallada de cómo se ejecuta la consulta. Para más información sobre esta característica, vaya a Plan de consulta.

Cada cuadro de la imagen anterior se denomina nodo. Un nodo representa el desglose de la operación para completar esta consulta. Nodos que representan orígenes de datos, como SQL Server en el ejemplo anterior y el Value.NativeQuery nodo, representan qué parte de la consulta se descarga en el origen de datos. El resto de los nodos, en este caso Table.LastN y Table.SelectColumns resaltados en el rectángulo de la imagen anterior, se evalúan mediante el motor de Power Query. Estos dos nodos representan las dos transformaciones que agregó, Conservar filas inferiores y Seleccionar columnas. El resto de los nodos representan las operaciones que se producen en el nivel de origen de datos.

Para ver la solicitud exacta que se envía al origen de datos, seleccione Ver detalles en el Value.NativeQuery nodo.

Esta solicitud de origen de datos está en el idioma nativo del origen de datos. En este caso, ese lenguaje es SQL y esta instrucción representa una solicitud para todas las filas y campos de la fact_Sale tabla.

Consultar esta solicitud de origen de datos puede ayudarle a comprender mejor el caso que el plan de consulta intenta transmitir:

• Sql.Database: este nodo representa el acceso al origen de datos. Se conecta a la base de datos y envía solicitudes de metadatos para comprender sus funcionalidades.
• Value.NativeQuery: representa la solicitud generada por Power Query para completar la consulta. Power Query envía las solicitudes de datos en una instrucción SQL nativa al origen de datos. En este caso, esto representa todos los registros y campos (columnas) de la tabla fact_Sale. En este escenario, este caso no es deseable, ya que la tabla contiene millones de filas y el interés es solo en los últimos 10.
• Table.LastN: una vez que Power Query recibe todos los registros de la fact_Sale tabla, usa el motor de Power Query para filtrar la tabla y mantener solo las últimas 10 filas.
• Table.SelectColumns: Power Query usa la salida del Table.LastN nodo y aplica una nueva transformación denominada Table.SelectColumns, que selecciona las columnas específicas que desea mantener de una tabla.

Para su evaluación, esta consulta tenía que descargar todas las filas y campos de la fact_Sale tabla. Esta consulta tardó un promedio de 6 minutos y 1 segundo en procesarse en una instancia estándar de flujos de datos de Power BI (que tiene en cuenta la evaluación y carga de datos en flujos de datos).

Ejemplo de plegado parcial de consultas

Después de conectarse a la base de datos y navegar a la tabla fact_Sale, comience a seleccionar las columnas que desea mantener de su tabla. Seleccione la transformación Elegir columnas que se encuentra dentro del grupo Administrar columnas en la pestaña Inicio . Esta transformación le ayuda a seleccionar explícitamente las columnas que desea mantener de la tabla y quitar el resto.

Dentro del cuadro de diálogo Elegir columnas , seleccione las Sale Keycolumnas , Customer Key, Invoice Date Key, Descriptiony y Quantity , a continuación, seleccione Aceptar.

Ahora se crea lógica que ordena la tabla para que tenga las últimas ventas en la parte inferior de la tabla. Seleccione la Sale Key columna , que es la clave principal y la secuencia incremental o el índice de la tabla. Ordene la tabla usando solo este campo en orden ascendente desde el menú contextual de la columna.

A continuación, seleccione el menú contextual de la tabla y elija la transformación Mantener filas inferiores .

En Mantener filas inferiores, escriba el valor 10 y, a continuación, seleccione Aceptar.

El ejemplo de código siguiente es el script M completo para la consulta que creó:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
    #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
    #"Kept bottom rows"

Ejemplo de plegado de consultas parciales: Descripción de la evaluación de consultas

Al comprobar el panel de pasos aplicados, verá que los indicadores de plegado de consultas muestran que la última transformación que agregó, Kept bottom rows, se marca como un paso que se evalúa fuera del origen de datos o, en otras palabras, por el motor de Power Query.

Puede hacer clic con el botón derecho en el último paso de la consulta, el denominado Kept bottom rowsy seleccionar la opción Plan de consulta para comprender mejor cómo se puede evaluar la consulta.

Cada cuadro de la imagen anterior se denomina nodo. Un nodo representa todos los procesos que deben producirse (de izquierda a derecha) para que se evalúe la consulta. Algunos de estos nodos se pueden evaluar en el origen de datos, mientras que otros, como el nodo para Table.LastN, representado por el paso Mantener filas inferiores , se evalúan mediante el motor de Power Query.

Para ver la solicitud exacta que se envía al origen de datos, seleccione Ver detalles en el Value.NativeQuery nodo.

Esta solicitud está en el idioma nativo del origen de datos. En este caso, ese lenguaje es SQL y esta instrucción representa una solicitud para todas las filas, con solo los campos solicitados de la fact_Sale tabla ordenadas por el Sale Key campo.

Consultar esta solicitud de origen de datos puede ayudarle a comprender mejor el caso que intenta transmitir el plan de consulta completo. El orden de los nodos es un proceso secuencial que comienza solicitando los datos del origen de datos:

• Sql.Database: se conecta a la base de datos y envía solicitudes de metadatos para comprender sus funcionalidades.
• Value.NativeQuery: representa la solicitud generada por Power Query para completar la consulta. Power Query envía las solicitudes de datos en una instrucción SQL nativa al origen de datos. En este caso, que representa todos los registros, con solo los campos solicitados de la tabla fact_Sale en la base de datos, ordenada en orden ascendente por el campo Sales Key.
• Table.LastN: una vez que Power Query recibe todos los registros de la fact_Sale tabla, usa el motor de Power Query para filtrar la tabla y mantener solo las últimas 10 filas.

Para su evaluación, esta consulta tenía que descargar todas las filas y solo los campos necesarios de la fact_Sale tabla. Se tarda un promedio de 3 minutos y 4 segundos en procesarse en una instancia estándar de flujos de datos de Power BI (que tiene en cuenta la evaluación y la carga de datos en flujos de datos).

Ejemplo de plegado completo de la consulta

Después de conectarte a la base de datos y navegar hasta la tabla fact_Sale, empieza seleccionando las columnas que deseas mantener de tu tabla. Seleccione la transformación Elegir columnas que se encuentra dentro del grupo Administrar columnas en la pestaña Inicio . Esta transformación le ayuda a seleccionar explícitamente las columnas que desea mantener de la tabla y quitar el resto.

En Elegir columnas, seleccione las Sale Keycolumnas , Customer Key, Invoice Date Key, Descriptiony y Quantity , a continuación, seleccione Aceptar.

Ahora se crea lógica que ordena la tabla para que tenga las últimas ventas en la parte superior de la tabla. Seleccione la Sale Key columna , que es la clave principal y la secuencia incremental o el índice de la tabla. Ordene la tabla solo usando este campo en orden descendente desde el menú contextual de la columna.

A continuación, seleccione el menú contextual de la tabla y elija la transformación Mantener las filas superiores .

En Mantener las filas superiores, escriba el valor 10 y, a continuación, seleccione Aceptar.

El ejemplo de código siguiente es el script M completo para la consulta que creó:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
    #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
    #"Kept top rows"

Ejemplo completo de plegado de consultas: Descripción de la evaluación de consultas

Al comprobar el panel de pasos aplicados, tenga en cuenta que los indicadores de plegado de consultas muestran que las transformaciones que agregó, Elegir columnas, Filas ordenadas y Mantener las primeras filas, están marcadas como pasos que se evalúan en el origen de datos.

Puede hacer clic con el botón derecho en el último paso de la consulta, el denominado Mantener las filas superiores y seleccionar la opción que lee Plan de consulta.

Esta solicitud está en el idioma nativo del origen de datos. En este caso, ese lenguaje es SQL y esta instrucción representa una solicitud para todas las filas y campos de la fact_Sale tabla.

Consultar esta consulta de origen de datos puede ayudarle a comprender mejor el caso que el plan de consulta completo intenta transmitir:

• Sql.Database: se conecta a la base de datos y envía solicitudes de metadatos para comprender sus funcionalidades.
• Value.NativeQuery: representa la solicitud generada por Power Query para completar la consulta. Power Query envía las solicitudes de datos en una instrucción SQL nativa al origen de datos. En este caso, se solicita solo los 10 registros principales de la tabla fact_Sale, con solo los campos necesarios después de haber sido ordenados en orden descendente mediante Sale Key.

Para su evaluación, esta consulta solo descarga 10 filas, con solo los campos que solicitó de la fact_Sale tabla. Esta consulta tardó un promedio de 31 segundos en procesarse en una instancia estándar de flujos de datos de Power BI (que tiene en cuenta la evaluación y la carga de datos en flujos de datos).

Comparación del rendimiento

Para comprender mejor el efecto que tiene el plegado de consultas en estas consultas, puede actualizar las consultas, registrar el tiempo necesario para actualizar completamente cada consulta y compararlas. Para simplificar, en este artículo se proporcionan los tiempos de actualización promedio capturados mediante el mecánico de actualización de flujos de datos de Power BI al conectarse a un entorno dedicado de Azure Synapse Analytics con DW2000c como nivel de servicio.

El tiempo de actualización de cada consulta era el siguiente:

A menudo, una consulta que se pliega completamente al origen de datos supera a las consultas similares que no logran hacerlo. Puede haber muchas razones por las que este es el caso. Estos motivos van desde la complejidad de las transformaciones que realiza la consulta hasta las optimizaciones de consulta implementadas en el origen de datos, como índices y computación dedicada y recursos de red. Aún así, hay dos procesos clave específicos que el plegado de consultas intenta usar para minimizar el efecto que ambos procesos tienen con Power Query.

• Datos en tránsito
• Transformaciones ejecutadas por el motor de Power Query

En las secciones siguientes se explica el efecto que estos dos procesos tienen en las consultas mencionadas anteriormente.

Datos en tránsito

Cuando se ejecuta una consulta, intenta capturar los datos del origen de datos como uno de sus primeros pasos. El mecanismo de plegado de consultas define los datos que se capturan del origen de datos. Este mecanismo identifica los pasos de la consulta que se pueden delegar al origen de datos.

En la tabla siguiente se muestra el número de filas solicitadas desde la fact_Sale tabla de la base de datos. La tabla también incluye una breve descripción de la instrucción SQL enviada para solicitar dichos datos desde el origen de datos.

Cuando se solicitan datos desde un origen de datos, el origen de datos debe calcular los resultados de la solicitud y, a continuación, enviar los datos al solicitante. Aunque ya se han mencionado los recursos informáticos, los recursos de red de mover los datos del origen de datos a Power Query y, a continuación, hacer que Power Query pueda recibir eficazmente los datos y prepararlos para las transformaciones que se producen localmente pueden tardar algún tiempo en función del tamaño de los datos.

Para los ejemplos mostrados, Power Query tuvo que solicitar más de 3,6 millones de filas del origen de datos, para los ejemplos sin plegado de consultas y de plegado parcial de consultas. En el ejemplo de plegado completo de consulta, se solicitaron solo 10 filas. Para los campos solicitados, el ejemplo de plegado sin consulta solicitó todos los campos disponibles de la tabla. Tanto el plegado de consultas parciales como los ejemplos de plegado de consultas completos solo enviaron una solicitud para exactamente los campos que necesitaban.

Transformaciones ejecutadas por el motor de Power Query

En este artículo se muestra cómo puede usar el plan de consulta para comprender mejor cómo se puede evaluar la consulta. Dentro del plan de consulta, puede ver los nodos exactos de las operaciones de transformación realizadas por el motor de Power Query.

En la tabla siguiente se muestran los nodos de los planes de consulta de las consultas anteriores que el motor de Power Query evaluaría.

Para los ejemplos mostrados en este artículo, el ejemplo de plegado de consultas completo no requiere que se produzcan transformaciones dentro del motor de Power Query, ya que la tabla de salida necesaria procede directamente del origen de datos. Por el contrario, las otras dos consultas requerían que se realice algún cálculo en el motor de Power Query. Debido a la cantidad de datos que deben procesar estas dos consultas, el proceso de estos ejemplos tarda más tiempo que el ejemplo de plegado de consultas completo.

Las transformaciones se pueden agrupar en las siguientes categorías:

Consideraciones y sugerencias

• Siga los procedimientos recomendados al crear una nueva consulta, como se indica en Procedimientos recomendados en Power Query.
• Utilice los indicadores de plegado de consultas para comprobar qué pasos impiden que su consulta se pliegue. Reordénalos si es necesario para aumentar el plegado.
• Use el plan de consulta para determinar qué transformaciones se producen en el motor de Power Query para un paso determinado. Considere la posibilidad de modificar la consulta existente reorganizando los pasos. A continuación, vuelva a comprobar el plan de consulta del último paso de la consulta y compruebe si el plan de consulta es mejor que el anterior. Por ejemplo, el nuevo plan de consulta tiene menos nodos que el anterior, y la mayoría de los nodos son de "flujo" y no de "escaneo completo". En el caso de los orígenes de datos que admiten el plegado, todos los nodos del plan de consulta, con la excepción de Value.NativeQuery y de los nodos de acceso al origen de datos, representan transformaciones que no se sometieron a plegado.
• Cuando esté disponible, puede usar la opción Ver consulta nativa (o Ver consulta de origen de datos) para asegurarse de que la consulta se puede volver a plegar al origen de datos. Si esta opción está deshabilitada para su paso, y está utilizando un origen que normalmente habilita esta opción, ha creado un paso que detiene el plegado de consultas. Si está utilizando un origen que no admite esta opción, puede confiar en los indicadores de plegado de consultas y el plan de consulta.
• Use las herramientas de diagnóstico de consultas para comprender mejor las solicitudes que se envían al origen de datos cuando las funcionalidades de plegado de consultas están disponibles para el conector.
• Al combinar datos procedentes del uso de varios conectores, Power Query intenta insertar tanto trabajo como sea posible en ambos orígenes de datos, al tiempo que cumple con los niveles de privacidad definidos para cada origen de datos.
• Lea el artículo sobre los niveles de privacidad para proteger las consultas frente a un error de Firewall de privacidad de datos.
• Use otras herramientas para comprobar el plegado de consultas desde la perspectiva de la solicitud recibida por el origen de datos. En función del ejemplo de este artículo, puede usar Microsoft SQL Server Profiler para comprobar las solicitudes enviadas por Power Query y recibidas por Microsoft SQL Server.
• Si agrega un nuevo paso a una consulta totalmente plegada y el nuevo paso también se plega, Power Query podría enviar una nueva solicitud al origen de datos en lugar de usar una versión almacenada en caché del resultado anterior. En la práctica, este proceso puede dar lugar a operaciones aparentemente sencillas en una pequeña cantidad de datos que tardan más tiempo en actualizarse en la versión preliminar de la esperada. Esta actualización más prolongada se debe a que Power Query vuelve a consultar el origen de datos en lugar de trabajar con una copia local de los mismos.