Cómo mejorar el rendimiento de la referencia de consulta para tablas grandes

He leído los Consejos para crear informes y dice:

«Cuando hace referencia a una consulta, solo carga los datos de origen una vez, independientemente del número de consultas que hacen referencia a la consulta inicial. «

Este no parece ser el caso en mi máquina. Tengo una consulta base llamada Ventas que es una tabla de hechos de SQL Server.

Ventas (~ 7 millones de filas de datos)

RegionID

ID del Producto

Identificación del cliente

RepID

DateID

Ingresos netos

Cantidad

Margen

y luego 3 consultas que hacen referencia a Ventas y agrupan por combinaciones de diferentes ID

SalesByRep

SalesByCustomer

SalesByProduct

Todas estas consultas hacen referencia Ventas, agrupe los resultados y sume los campos de ingresos, margen y cantidad.

Cuando ejecuto la actualización de cada una de estas consultas, veo que PowerBI gira durante unos buenos 15 minutos y recupero alrededor de 7 millones de registros de SQL Server.

Esto no parece alinearse con lo que dicen los consejos. ¿Alguien ha experimentado esto y tiene más consejos para intentar mejorar este rendimiento? También configuré los atributos de ventas ‘Cargar en informe’ y ‘Habilitar actualización de esta consulta’ en FALSO.

¿Es esto algo que pueda mejorar con Table.Buffer o DirectQuery? Cualquier idea será muy apreciada.

@jaykilleen Veo que está al tanto de cosas como Table.Buffer, por lo que es un usuario de PowerQuery bastante avanzado.

Puedo entrar en un pequeño detalle sobre tu pregunta. La respuesta desde el principio es que la sugerencia es algo cierta. La regla a recordar aquí es que cada consulta, cuando se cargue para informar, siempre se evaluará de forma aislada. Incluso pueden ser evaluados por diferentes procesos y, por lo tanto, no formar una estructura de árbol dependiente.

Por ejemplo, si tiene una consulta base A, y luego dos nuevas consultas B y C que hacen referencia a A. A no se carga para informar, pero B y C sí. Cuando presiona el botón Aplicar, B y C comenzarán a cargar simultáneamente para informar. En ambas evaluaciones, A se evaluará por separado, porque B no tiene conocimiento de C.

Ahora bien, si tenemos este modelo en mente, puede argumentar que el consejo no es cierto en absoluto. Sin embargo, las evaluaciones de PowerQuery mantienen un caché de datos vistos por las evaluaciones en el disco. Entonces, si está dentro de la misma sesión de caché y utilizó A varias veces, esencialmente solo pagará por ella la primera vez (a menos que, por supuesto, esté utilizando páginas diferentes de A). Esta caché SOLO se aplicará a los datos brutos que provienen de la fuente de datos, cualquier transformación adicional deberá realizarse sobre ella.

Finalmente, dado que cuando carga consultas para informar, siempre desea los datos más recientes. Entonces, cada sesión de carga es esencialmente una nueva sesión de caché. Por lo tanto, el consejo es cierto aquí. Solo tendrá que pagar por los datos provenientes de la fuente de datos UNA UNA VEZ por cargar todas las consultas. (Curiosamente, para la mayoría de las fuentes de datos, esto también es cierto para los duplicados de A). Sin embargo, pagará por las transformaciones además de los tiempos AN donde N = el número de consultas que se han cargado.

Con este conocimiento en mente, puede ver que Table.Buffer tampoco lo ayuda. Lo que hace es garantizar una salida estable desde dentro de una consulta (por ejemplo, entre varios permisos). Entonces, esencialmente, declara un punto en sus datos donde, a partir de ahí, todas las transformaciones se realizarán en la copia local, en lugar de plegarse de forma remota.

Finalmente, sí, DirectQuery sin duda ayudará con el rendimiento aquí. Con DQ siempre opera en la fuente de datos remota y nunca guarda una copia local de los datos. Entonces no hay una fase de «carga». Pagará por los datos a medida que los elementos visuales los necesiten (donde se agregan principalmente).

¿Esto tiene sentido?

Saludos,

PQ

¡Hola! Estoy experimentando el mismo problema :(. Tengo 50 csv que se combinan, luego se dividen / transforman y en algún momento se combinan 3 veces.

¡Hola!

Lo sé, es un tema antiguo, pero creo que encontré una buena explicación sobre la referencia, el almacenamiento en caché y el almacenamiento en búfer cuando la fuente es estática (por lo tanto, Excel o CSV, no el servidor SQL).

https: //social.technet.microsoft.com/Forums/en-US/8d5ee632-fdff-4ba2-b150-bb3591f955fb/queries-evalu …

La sugerencia de este artículo proviene de esta publicación en el foro:

https: //community.powerbi.com/t5/Desktop/Using-quot-Reference-quot-in-Query-Editor-does-not-prevent / …

En mi caso actual, tengo archivos PBIX basados en 5 archivos Exel, cada uno de 3 MB. Están basados en Essbase, por lo que están bastante estructurados.

Quiero crear una tabla de fechas dinámica basada en los datos extraídos. Estamos hablando de un máximo de 36 meses.

Entonces, existe la consulta A, ETL para el hecho y la construcción de una columna Fecha. Luego hay una segunda consulta, «Fecha», que hace referencia a A. Parece simple, ¡pero la actualización de «Fecha» está tardando más de 5 minutos!

Por lo tanto, estoy tratando de optimizar esto. Cualquier otra idea es bienvenida.

Salud. GRAMO

Hola, gracias por este valioso intercambio.

Me pregunto si se supone que debemos indexar los campos de la tabla de origen en la que estamos filtrando en Power Query. ¿Tiene este objetivo acelerar la recuperación de los datos «deseados»?

Gracias.

Solo una pregunta, ya que yo mismo estoy aprendiendo.

En estos ejemplos donde hay varias consultas que son básicamente versiones refiltradas de la consulta original, ¿no es más eficiente traer la única consulta principal una vez y luego crear las subconsultas dentro del modelo pbx creando tablas calculadas separadas, filtrando la consulta principal? tabla.

Tengo una situación similar, traigo una consulta de una tabla de hechos, luego he creado otras 2 tablas calculadas que son similares a sus consultas filtradas arriba.

De esta manera, solo se ejecuta una consulta frente a 3.

Probablemente depende de qué tan grande sea la parte de los datos que necesita al final. Si básicamente necesita todos los datos, solo en tablas separadas, entonces el almacenamiento en caché y la referencia serán probablemente los más rápidos.

Pero si solo necesita 2 años diferentes, por ejemplo, de datos de 10 años (en 2 consultas / tablas diferentes), entonces esperaría que 2 consultas paralelas que se están replegando al servidor sean más rápidas.

@jaykilleen Veo que está al tanto de cosas como Table.Buffer, por lo que es un usuario de PowerQuery bastante avanzado.

Puedo entrar en un pequeño detalle sobre tu pregunta. La respuesta desde el principio es que la sugerencia es algo cierta. La regla a recordar aquí es que cada consulta, cuando se cargue para informar, siempre se evaluará de forma aislada. Incluso pueden ser evaluados por diferentes procesos y, por lo tanto, no formar una estructura de árbol dependiente.

Por ejemplo, si tiene una consulta base A, y luego dos nuevas consultas B y C que hacen referencia a A. A no se carga para informar, pero B y C sí. Cuando presiona el botón Aplicar, B y C comenzarán a cargar simultáneamente para informar. En ambas evaluaciones, A se evaluará por separado, porque B no tiene conocimiento de C.

Ahora bien, si tenemos este modelo en mente, puede argumentar que el consejo no es cierto en absoluto. Sin embargo, las evaluaciones de PowerQuery mantienen un caché de datos vistos por las evaluaciones en el disco. Entonces, si está dentro de la misma sesión de caché y utilizó A varias veces, esencialmente solo pagará por ella la primera vez (a menos que, por supuesto, esté utilizando páginas diferentes de A). Esta caché SOLO se aplicará a los datos brutos que provienen de la fuente de datos, cualquier transformación adicional deberá realizarse sobre ella.

Finalmente, dado que cuando carga consultas para informar, siempre desea los datos más recientes. Entonces, cada sesión de carga es esencialmente una nueva sesión de caché. Por lo tanto, el consejo es cierto aquí. Solo tendrá que pagar por los datos provenientes de la fuente de datos UNA UNA VEZ por cargar todas las consultas. (Curiosamente, para la mayoría de las fuentes de datos, esto también es cierto para los duplicados de A). Sin embargo, pagará por las transformaciones además de los tiempos AN donde N = el número de consultas que se han cargado.

Con este conocimiento en mente, puede ver que Table.Buffer tampoco lo ayuda. Lo que hace es garantizar una salida estable desde dentro de una consulta (por ejemplo, entre varios permisos). Entonces, esencialmente, declara un punto en sus datos donde, a partir de ahí, todas las transformaciones se realizarán en la copia local, vs plegado de forma remota.

Finalmente, sí, DirectQuery sin duda ayudará con el rendimiento aquí. Con DQ siempre opera en la fuente de datos remota y nunca guarda una copia local de los datos. Entonces no hay una fase de «carga». Pagará por los datos a medida que los elementos visuales los necesiten (donde se agregan principalmente).

¿Esto tiene sentido?

Saludos,

PQ

Tiene mucho sentido.

@pqian Gracias por todo este detalle. Creo que experimentaré más con DirectQuery y también con la forma en que construyo mis consultas.

Creo que estoy en una posición en la que debería utilizar DAX en la Vista de informes en lugar de crear muchas consultas para cosas básicas como esta.

Esto también debería ayudar a mantener mi caché bajo (actualmente no es difícil acercarse al límite de 4GB que sé que puedo aumentar, pero también ejecuto VM en mi máquina y se detienen cuando no hay suficiente memoria libre).

@pqian muchas gracias por esta explicación, muy apreciada. Especialmente porque no me parece que haya mucho sobre esto disponible en este momento, ¿o podría dar más orientación sobre dónde encontrar información como esta?

¿Tengo entendido que no habría un caché para compartir si B & C se retirara al servidor ?:

Como la primera consulta, A solo está dirigiendo la tabla completa en el servidor y la consulta B está filtrando P&L, por ejemplo, cuentas. B volvería al servidor, por lo que solo las cuentas seleccionadas en B serán devueltas del servidor.

Si la consulta C ahora selecciona diferentes cuentas de A (Balance), solo se devolverán esas.

Por lo tanto: su ejemplo de A que proporciona un caché para compartir para B y C solo funcionaría si el plegado de consultas se rompiera antes de que se ejecuten B y C.

Entonces, en los casos en que la consulta directa no es una opción o se vuelve demasiado lenta para el usuario (cuando hay interacciones en los informes que desencadenarían cálculos costosos durante la carga), el Table.Buffer podría tener sentido: a saber, en aquellos casos en los que el Las alternativas de múltiples (sub-) consultas plegables son más lentas que usar Table.Buffer como una parada de carpeta al final de la consulta A?

Es cierto que este podría no ser el caso muy a menudo.

@ImkeF Tu observación es correcta. Esencialmente, doblar la consulta de manera diferente al servidor podría romper el uso compartido de la caché. Aún no me queda claro si Table.Buffer tiene sentido en este caso. En el ejemplo que dio, si el filtrado de B y C reduce drásticamente el tamaño de los datos, puede ser mejor si envía ambas consultas al servidor y carga los datos por separado.

Estamos hablando de los detalles de implementación de PowerQuery / PowerBI Desktop. No es un contrato público y solo debe servir como guía sobre cómo se debe llevar a cabo la optimización. Por lo tanto, no están documentados en ninguna parte, aunque algunos experimentos deberían revelar rápidamente lo que sucede detrás de escena.

Hola, gracias por los útiles consejos.

Me pregunto si tenemos que indexar todos los campos en nuestra tabla de origen, en la que estamos haciendo filtros en Power Query.

¿Esto acelerará la selección de los datos «correctos» para usar en PBI?

Gracias

No estoy seguro de qué quiere decir exactamente con «Indexación».

Si te refieres a Table.AddIndexColumn, esto ralentizará tus consultas.

Hola ImkeF,

Me refiero a crear un índice en la base de datos MySQL en los campos en los que filtraré más adelante en PowerQuery. Espero que un índice en tales campos ayude a seleccionar las filas que necesito mantener en mi modelo.

Gracias

Sí, eso es correcto (siempre que las consultas de MySQL se plieguen, lo cual no sé).

Hola @ImkeF. Ingresé a Power BI una población de cuentas (una sola columna) usando una consulta SQL nativa. Luego, quiero usar esas cuentas como filtro para otras consultas que llegarán a diferentes tablas en la base de datos. ¿Esperaría que esas fusiones (uniones internas) se produzcan más rápido si hago una tabla en el búfer de la consulta SQL nativa?

(No haré ningún tipo de filtrado adicional en las otras tablas, lo que significa que generalmente espero obtener la misma población de cuentas de cada tabla).

Me sorprendió el rendimiento con tanta frecuencia, que siempre revisaba ambas opciones. 🙂

¡Hola!
Tengo una pregunta sobre el rendimiento, estoy usando como archivos de origen de Excel.
Actualmente se encuentran en nuestro servidor local.
¿Te diste cuenta, sabes que es mejor tenerlos almacenados en alguna nube de MS, como OneDrive, SharePoint o algo así, o si al final no juega un papel?

A pesar de buscar en Google, no pude encontrar una respuesta a eso.

Nota al margen: estamos usando O365 pero los archivos de Excel se almacenan en nuestro servidor local.

Salud

GRAMO.

Es muy poco probable que el cambio al almacenamiento web mejore el rendimiento. Pero si tiene un rendimiento muy lento en la premier, podría valer la pena intentarlo.

Lo que realmente mejoraría el rendimiento en la mayoría de los casos es usar fuentes csv en lugar de xls (x). Entonces, si existe la posibilidad de cambiar a csv, probablemente verá una mejora en la velocidad.

Hola,
¿Un procesador i5 y 8gb de ram también podrían ralentizar las cosas? ¿Qué tipo de especificaciones de PC deberían ayudarme? ¿Quizás, SSD, 16gb, i7 y 256ram video?

¿Realmente ha probado que la actualización con una consulta de referencia es sustancialmente más rápida que con 3 (digamos: 5-7 minutos)?

¿Carga su tabla de hechos en el modelo o solo las consultas de referencia?

¿Sus consultas contienen sentencias SQL personalizadas? Romperían el plegado de consultas: https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/

Entradas relacionadas

Deja un comentario Cancelar respuesta