Optimizar los filtros OData para mejorar el rendimiento

Ir a la versión en inglés

La especificación OData ofrece muchas posibilidades para enviar filtros al servidor OData. Power BI hace un buen trabajo al mapear los pasos de filtrado a filtros OData gracias al plegado de consultas (query-folding), pero especialmente en un entorno grande de Power BI con decenas o cientos de empresas, puede ser necesario controlar y mejorar el rendimiento de las consultas. Mejorar el rendimiento de las consultas reduce el tiempo de ejecución, los costes de suscripción y los costes de ancho de banda, capacidad de procesamiento y tiempo del usuario.

Invantive Bridge Online, parte de Invantive Cloud, es un proxy OData4 que gestiona su OData4 y lo traduce en solicitudes en tiempo real para más de 70 plataformas (en la nube). Invantive Bridge Online puede gestionar solicitudes OData que abarcan cientos de empresas en paquetes de contabilidad populares como Twinfield, AFAS, Exact Online o Visma.net Financials.

En este tema se explica el impacto de diferentes filtros en el consumo de recursos.

Guía

Antes de continuar, asegúrese de comprender la estructura de Invantive Cloud estudiando el contenido en Invantive Cloud Structure.

Una lista de técnicas de rendimiento y consejos sobre cómo optimizar el rendimiento y el tamaño de descarga para Power BI está disponible en Overzicht van Power BI-technieken om prestaties en downloadtijd te verbeteren para ayudarle a mejorar aún más el rendimiento de los informes de Power BI.

Consulta para mediciones

Conjunto de pruebas de Exact Online

El entorno de Power BI utilizado en estas mediciones era un entorno de prueba con 200 pequeñas empresas de Exact Online. Se utilizó la tabla TransactionLinesIncremental, ya que es la tabla más utilizada intensivamente en Exact Online.

Un recuento de líneas de transacción en las 200 empresas es de solo 217.668 filas. El tamaño de descarga comprimido a través de OData4 es de 16 MB (sin comprimir 245 MB).

Todas las pruebas se ejecutaron dos veces para asegurarse de que todas las cachés estuvieran cargadas y actualizadas. Solo se incluyó la medición de la segunda prueba.

Las mediciones de consultas se determinaron utilizando Invantive Bridge Online Monitoring.

Descarga completa

Primero se descargaron todas las transacciones sin filtrar (sin opción de búsqueda):

  • Duración: 30 segundos
  • Número de filas de datos: 217.668
  • Tamaño de los datos descargados: 15.522.956
  • URL: BASE

donde “BASE” es la URL del servidor más el nombre de la tabla ExactOnlineREST.Incremental.TransactionLinesIncremental@url.

El SQL generado es:

select t.* 
from   ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t

El motor Invantive SQL que se ejecuta en Invantive Cloud realizará múltiples solicitudes, al menos una por empresa de Exact Online, para recuperar todos los datos relevantes. Power BI solo necesita ejecutar una solicitud en el endpoint OData; Invantive Cloud combina los datos de todas las empresas en un único conjunto de datos.

En este escenario, el uso de una única solicitud en OData por conjunto de datos aumenta significativamente el rendimiento de OData: Invantive Cloud devuelve datos de forma continua (streaming) sobre todas las empresas de Exact Online seleccionadas.

Otros conjuntos de datos pueden recuperarse en paralelo para mejorar aún más el rendimiento de OData. Estos también mezclan datos de múltiples administraciones de Exact Online.

Descargar una sola administración de Exact Online

En el Editor de Power Query se seleccionó solo una división de Exact Online, 868045:

Filtro de Power BI en 1 administración de Exact Online|394x305](upload://dIA3Ujm1DeePSWykL18K9HoVXQd.png).

Se añadió un paso de filtrado para recuperar solo una empresa:

Nuevo paso de filtrado en la administración de Exact Online|424x279](upload://7p7be8NWIfldrjVq3KuDEHTox3x.png)

Observe que este paso de filtrado está directamente después de la descarga. Eso evita que Power BI ya no pueda aplicar el plegado de consultas.

Las mediciones fueron:

  • Duración: 1 segundo
  • Número de filas de datos: 172
  • Tamaño de descarga de datos: 17.986
  • URL: BASE?$filter=Divisie eq 868045

La consulta en el editor avanzado es:

let
    Source = OData.Feed("BASEURL", null, [Implementation="2.0"]),
    #"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table", each ([Division] = 868045))
in
    #"Filtered Rows"

El SQL generado para Invantive SQL es:

select t.* 
from   ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t 
where  ([Division] = :w1)

El Invantive SQL tiene un filtro en division, lo que permite que el motor SQL recupere y devuelva solo esta administración.

En este escenario, tanto entre Power BI e Invantive Bridge Online, como entre Invantive Bridge Online y Exact Online, los filtros se transmiten correctamente. El filtrado del lado del cliente todavía se aplica, pero ya no descarta un porcentaje significativo de la carga útil sin usar.

Descargar cuatro administraciones de Exact Online

En el Editor de Power Query se seleccionaron cuatro administraciones de Exact:

Las filas de filtro se muestran como:

Las mediciones fueron:

  • Duración: 1 segundo
  • Número de filas de datos: 1.114
  • Tamaño de descarga de datos: 102.562
  • URL: BASE?$filter=Division eq 868041 or Division eq 868045 or Division eq 868046 or Division eq 868047

La consulta en el Editor Avanzado es:

let
    Source = OData.Feed("BASEURL", null, [Implementation="2.0"]),
    #"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table", each [Division] = 868041 or [Division] = 868045 or [Division] = 868046 or [Division] = 868047)
in
    #"Filtered Rows"

Observe que Power BI reescribe la selección de valores como un OR en lugar de utilizar el operador IN.

El Invantive SQL generado es:

select t.* 
from   ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t 
where  (((([Division] = :w1) or ([Division] = :w2)) or ([Division] = :w3)) or ([Division] = :w4))

El Invantive SQL generado tiene un grupo de filtros, compuesto por elementos unidos con OR en division. El filtro en división no se transmite y se consultan y facturan todas las empresas que están configuradas en el SQL de arranque (On Startup SQL) de la base de datos.

En este escenario, entre Power BI e Invantive Bridge Online, los filtros se transmiten correctamente. Sin embargo, entre Invantive Bridge Online y el servidor API de Exact Online, el motor Invantive SQL aplica un filtrado excesivo del lado del cliente sobre la respuesta de los servidores API. El filtrado del lado del cliente desecha sin usar más del 90% de la carga útil.

Se realizó una mejora de rendimiento en Invantive SQL; a partir de julio de 2021, solo se accederá a las empresas especificadas. Anteriormente, se accedía a todas las empresas con derechos de aplicación. Para más detalles, consulte Betere prestaties op Power BI voor Exact Online accountants.

Cuatro empresas y filtro por año fiscal

La selección de empresas se mejora aplicando un filtro en los años fiscales:

Las mediciones fueron:

  • Duración: 1 segundo
  • Número de filas de datos: 482
  • Tamaño de descarga de datos: 44.809
  • URL: BASE?$filter=(Division eq 868041 or Division eq 868045 or Division eq 868046 or Division eq 868047) and (FinancialYear eq 2011 or FinancialYear eq 2012 or FinancialYear eq 2013)

La consulta en el Editor Avanzado es:

let
    Source = OData.Feed("BASEURL", null, [Implementation="2.0"]),
    #"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table" = Source{[Name="ExactOnlineREST.Incremental.TransactionLinesIncremental@eol",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.Incremental.TransactionLinesIncremental@eol_table", each ([Division] = 868041 or [Division] = 868045 or [Division] = 868046 or [Division] = 868047) and ([FinancialYear] = 2011 or [FinancialYear] = 2012 or [FinancialYear] = 2013))
in
    #"Filtered Rows"

La solicitud OData se traduce en el siguiente Invantive SQL:

OData query was transformed into: select t.*
from   ExactOnlineREST.Incremental.TransactionLinesIncremental@eol t
where  ((((([Division] = :w1) or ([Division] = :w2)) or ([Division] = :w3)) or ([Division] = :w4)) and ((([FinancialYear] = :w5) or ([FinancialYear] = :w6)) or ([FinancialYear] = :w7)))

El Invantive SQL afecta a las mismas empresas de Exact Online que antes, pero aplica por empresa un filtro preciso sobre el año fiscal.