Slow Power Query y rendimiento de actualización con múltiples fusiones/búsquedas posible causa

Estoy trabajando con datos de tickets de la tabla de ayuda del servidor de informes de un proveedor externo, y un flujo de datos maneja la extracción inicial de las tablas necesarias y algunas transformaciones ligeras. Con la actualización incremental, tarda de 3 a 5 minutos en completarse y tiene actualizaciones programadas cada 30 minutos.

El archivo PBIX lo toma desde allí y realiza varias búsquedas de tabla a tabla para extraer datos de nombres de tablas de empleados (buscar nombres en lugar de números de identificación de usuario) y valores de fecha y hora de eventos clave a medida que cada ticket caduca (tabla de escalamiento), antes de una fusión final de todos los tipos de tickets en una tabla de informes que se carga en el informe. Como resultado, estas 20 fusiones totales han llevado a una ralentización dramática tanto de los tiempos de actualización como del trabajo en estas tablas en Power Query… algunos intentos tardaron horas. Este diagrama debe dar una visión general:

Intenté deshabilitar la actualización en segundo plano para que trabajar en PQ fuera más amigable, pero no ayudó mucho. También intenté usar Table.Buffer después de investigar varias publicaciones, pero no estoy seguro de haberlo usado correctamente (la sintaxis era buena, ¿tal vez solo una ubicación incorrecta en la consulta?), ya que no parecía acelerar nada.

Mi pregunta es: ¿Qué puedo hacer para acelerar las cosas? Pensé que usar Table.Buffer en las tablas a las que me fusioné para pasos consecutivos ayudaría ( PASO 1: Fusionar T1 -> Table.Buffer(T2)… PASO 2: Fusionar T1 -> T2 nuevamente. excepto en la columna diff ), pero como dije, no pareció ayudar en nada. Me doy cuenta de que cada fusión cuesta recursos valiosos y solo tengo algunas pequeñas ideas para reducir el número total de fusiones. ¿Alguien ve dónde puedo simplificar esto? La duración de la actualización es tan larga ahora que a veces no se completa antes de que finalice la siguiente media hora de actualización de Dataflow, lo que da como resultado una actualización omitida o una acción de actualización casi constante.

Gracias de antemano a todos los gurús que respondan.

A continuación se muestra una consulta de ejemplo de mi tabla Tareas, que tiene la mayor cantidad de pasos de combinación.

Fuente = PowerBI.Dataflows (nulo),

#»1562f35b-c92a-4b71-a782-27007ddc1a3a» = Fuente{[workspaceId=»1562f35b-c92a-4b71-a782-27007ddc1a3a»]}[Data],

#»abd4997c-cf28-4628-939e-01bae91db5fb» = #»1562f35b-c92a-4b71-a782-27007ddc1a3a»{[dataflowId=»abd4997c-cf28-4628-939e-01bae91db5fb»]}[Data],

Tarea1 = #»abd4997c-cf28-4628-939e-01bae91db5fb»{[entity=»Task»]}[Data],

#»Otras columnas eliminadas» = Table.SelectColumns(Task1,{«RecId», «Tipo de ticket», «ID de asignación», «Número de ticket», «Número de ticket principal», «Creado el», «Creado el – corto», «Creado por», «Resuelto el», «Resuelto el – abreviado», «Resuelto por UID», «Completado el», «Completado el – abreviado», «Estado», «Asunto», «Descripción», «UID del propietario», » Equipo propietario», «ParentObjectDisplayID», «Dirección», «AssignedBy», «Asignado el», «EstimatedEffort», «LastModBy», «LastModDateTime», «ParentLink_RecID», «ParentLink_Category», «TargetDateTime», «Prioridad», » AcknowledgedBy», «AcknowledgedDateTime», «ActualEffort», «IsFinalState», «TeamManager», «Cost», «ResolutionEscLink_RecID», «ResolutionEscLink_Category», «ResponseEscLink_RecID», «ResponseEscLink_Category», «ActualCost», «EstimatedCost», «PlannedStartDate» , «PlannedEndDate», «ActualStartDate», «ActualEndDate», «TaskCategory», «WorkflowInstanceId», «ReadOnly», «CostPerMinute», «Service», «IsInFinalState», «FinalTaskforIncident», «CreateKnowledgeFromTask», «Location» , «PrimaryAssetLink_Category», «URL», «Datos a partir de:», «PrimaryAssetLink_RecID»}),

#»Escalamiento combinado – Resolución» = Table.NestedJoin(#»Otras columnas eliminadas», {«ResolutionEscLink_RecID»}, Frs_data_escalation_watch, {«RecId»}, «Frs_data_escalation_watch», JoinKind.LeftOuter),

#»Expanded Frs_data_escalation_watch» = Table.ExpandTableColumn(#»Escalamiento combinado: resolución», «Frs_data_escalation_watch», {«L1DateTime», «L2DateTime», «L3DateTime», «BreachDateTime», «L1DateTime-UTC», «L2DateTime-UTC» , «L3DateTime-UTC», «BreachDateTime-UTC», «L1Passed», «L2Passed», «L3Passed», «BreachPassed»}, {«L1DateTime», «L2DateTime», «L3DateTime», «BreachDateTime», «L1DateTime- UTC», «L2DateTime-UTC», «L3DateTime-UTC», «BreachDateTime-UTC», «L1Passed», «L2Passed», «L3Passed», «BreachPassed»}),

#»Escalamiento combinado – Respuesta» = Table.NestedJoin(#»Expanded Frs_data_escalation_watch», {«ResponseEscLink_RecID»}, Frs_data_escalation_watch, {«RecId»}, «Frs_data_escalation_watch», JoinKind.LeftOuter),

#»Expanded Frs_data_escalation_watch1″ = Table.ExpandTableColumn(#»Escalamiento combinado: respuesta», «Frs_data_escalation_watch», {«BreachDateTime», «BreachDateTime-UTC», «BreachPassed»}, {«Response BreachDateTime», «Response BreachDateTime-UTC» , «Respuesta BreachPassed»}),

#»Fusionado con empleado» = Table.NestedJoin(#»Expanded Frs_data_escalation_watch1″, {«ResolvedByUID»}, #»Employee», {«LoginID»}, «Employee», JoinKind.LeftOuter),

#»Empleado ampliado» = Table.ExpandTableColumn(#»Fusionado con empleado», «Empleado», {«DisplayName»}, {«ResolvedBy»}),

#»Fusionado con incidente» = Table.NestedJoin(#»Empleado ampliado», {«ParentLink_RecID»}, Incidente, {«RecId»}, «Incidente», JoinKind.LeftOuter),

#»Incidente ampliado» = Table.ExpandTableColumn(#»Combinado con incidente», «Incidente», {«Nombre del cliente», «Ubicación del cliente», «ID de la unidad de organización», «Prioridad»}, {«Nombre del cliente-Inc», » CustomerLocation-Inc», «OrganizationUnitID-Inc», «Priority-Inc»}),

#»Fusionado con ServiceReq» = Table.NestedJoin(#»Incidente ampliado», {«ParentLink_RecID»}, ServiceReq, {«RecId»}, «ServiceReq», JoinKind.LeftOuter),

#»Solicitud de servicio ampliada» = Table.ExpandTableColumn(#»Fusionada con solicitud de servicio», «Solicitud de servicio», {«Prioridad», «Urgencia», «ID de unidad de organización», «Nombre del cliente», «Ubicación del cliente»}, {«Requisito de prioridad «, «Requisito de urgencia», «Requisito de ID de unidad de organización», «Requisito de nombre de cliente», «Requisito de ubicación de cliente»}),

#»Fusionado con cambio» = Table.NestedJoin(#»Requisito de servicio ampliado», {«ParentLink_RecID»}, Cambio, {«RecId»}, «Cambio», JoinKind.LeftOuter),

#»Cambio ampliado» = Table.ExpandTableColumn(#»Fusionado con cambio», «Cambio», {«Nombre del cliente», «ID de unidad de organización», «Prioridad»}, {«Nombre de cliente-Cambiar», «ID de unidad de organización-Cambiar» , «Prioridad-Cambiar»}),

#»Fusionado con Empleado1″ = Table.NestedJoin(#»Cambio ampliado», {«UID de propietario»}, #»Empleado», {«ID de inicio de sesión»}, «Empleado», JoinKind.LeftOuter),

#»Valor reemplazado» = Table.ReplaceValue(#»Fusionado con Empleado1″,»»,null,Replacer.ReplaceValue,{«Priority-Chg»}),

#»Expanded Employee1″ = Table.ExpandTableColumn(#»Valor reemplazado», «Empleado», {«DisplayName»}, {«Propietario»}),

#»Columnas renombradas» = Table.RenameColumns(#»Expanded Employee1″,{{«TargetDateTime», «Due On»}, {«Completado el», «Cerrado el»}, {«Completado el – corto», «Cerrado On – short»}, {«Priority», «Priority-OLD»}}),

#»Consultas combinadas» = Table.NestedJoin(#»Columnas renombradas», {«ParentLink_RecID»}, Problema, {«RecId»}, «Problema», JoinKind.LeftOuter),

#»Problema ampliado» = Table.ExpandTableColumn(#»Consultas combinadas», «Problema», {«Nombre del cliente», «ID de unidad de organización», «Prioridad»}, {«Nombre de cliente-Prb», «ID de unidad de organización-Prb», » Prioridad-Prb»}),

#»Solicitud fusionada+Prioridad incremental» = Table.AddColumn(#»Problema ampliado», «Prioridad», each Text.Combine({[#»Priority-Req»], [#»Priority-Inc»], [#»Priority-Prb»], [#»Priority-Chg»]}, «»), teclee el texto),

#»Columnas de unidades de organización fusionadas» = Table.AddColumn(#»Solicitud fusionada+Prioridad de incremento», «Unidad de organización», cada Text.Combine({[#»OrganizationUnitID-Inc»], [#»OrganizationUnitID-Req»], [#»OrganizationUnitID-Chg»], [#»OrganizationUnitID-Prb»]}, «»), teclee el texto),

#»Columnas de clientes fusionadas» = Table.AddColumn(#»Columnas de unidades organizativas fusionadas», «Nombre del cliente», cada Text.Combine({[#»Customer Name-Prb»], [#»Customer Name-Chg»], [#»Customer Name-Req»], [#»Customer Name-Inc»]}, «»), teclee el texto),

#»Columnas de CustomerLoc combinadas» = Table.AddColumn(#»Columnas de clientes combinadas», «CustomerLocation», cada Text.Combine({[#»CustomerLocation-Req»], [#»CustomerLocation-Inc»]}, «»), teclee el texto),

#»Columnas eliminadas» = Table.RemoveColumns(#»Columnas combinadas de CustomerLoc»,{«Prioridad-Inc», «OrganizationUnitID-Inc», «Nombre del cliente-Inc», «Prioridad-Req», «OrganizationUnitID-Req», » Nombre de cliente-Req», «Nombre de cliente-Cambiar», «ID de unidad de organización-Cambiar», «Prioridad-Cambiar», «Nombre de cliente-Prb», «ID de unidad de organización-Prb», «Prioridad-Prb», «Ubicación de cliente-Inc», «Requisito de ubicación del cliente»}),

#»Valor1 reemplazado» = Tabla.ReplaceValue(#»Columnas eliminadas»,»»,null,Replacer.ReplaceValue,{«Prioridad»})

en

#»Valor reemplazado1″

Si bien solo puede tener una relación activa en una tabla, puede tener cualquier número de relaciones inactivas (líneas de puntos) entre tablas. Crearía las relaciones inactivas en el modelo y luego usaría DAX USERRELATIONSHIP en medidas para intercambiar entre ellos.

Estuardo

Voy a adoptar un enfoque diferente. Necesita esas fusiones para construir un modelo adecuado como yo lo entiendo. No traería esas tablas por separado y luego crearía relaciones de filtro.

Sin embargo, Power Query atascará un servidor con múltiples fusiones. PQ intentará crear el SQL y no siempre lo hace tan eficientemente como lo puede hacer un codificador SQL en SSMS. Sí, Table.Buffer puede ayudar, pero si los datos son grandes, puede ser más perjudicial que útil.

Mi recomendación es crear una vista en el servidor donde se realizan las fusiones y luego traer esa vista a PQ. Puede intentar traerlos todos por separado y usar LOOKUPVALUE y otras medidas para modelar sus datos en DAX, pero su rendimiento general en el informe será peor que si las fusiones se hubieran realizado en la fuente, y su DAX seguramente será tan diablos ser más complejo de escribir y mantener.

Gracias por el enfoque diferente, ¡lo aprecio! Normalmente, mi primer paso sería tratar de hacer todo lo posible modelando los datos de origen tanto como sea posible antes de cargarlos. Desafortunadamente, en mi caso, el servidor SQL de origen pertenece a un proveedor externo, por lo que no tenemos la libertad de crear vistas o incluso conectarnos directamente con SSMS debido a sus políticas de seguridad. Los flujos de datos terminaron siendo nuestra mejor opción de conexión y, si bien es posible ingresar una consulta SQL, ese flujo de trabajo le solicita la consulta. antes de incluso puede ver las 1.095 tablas de datos, lo que hace que desarrollar tales consultas sea un poco de prueba y error, algo para lo que no hay mucho tiempo libre.

Parece que mi mejor solución es la opción USERELATIONSHIP, al menos hasta que podamos desarrollar consultas que hagan retroceder el trabajo en ese sólido motor SQL.

@MTracy: una cosa más que puedes probar. Si es posible, rompa intencionalmente el plegado después del punto en el que se está produciendo una actualización incremental.

Puede agregar arbitrariamente una columna que use Table.TextBetweenDelimiters() o algo que no se doble. Hice esto donde Table.Buffer no ayuda, que es más de 100,000 registros o algo así.

He usado esto en varios modelos en los que necesitaba 5-6 uniones, y después de la 4.ª o 5.ª unión, el servidor se atascó. Entonces, después de la tercera unión/fusión, por ejemplo, haga algo que rompa el plegado, luego continúe con las fusiones, luego elimine la columna arbitraria que creé. Power Query sigue siendo muy eficiente en las fusiones, incluso si el motor de mashup lo está haciendo en lugar de enviarlo de vuelta al servidor.

Espero lo mejor para tu proyecto. A veces se trata de probar 10 cosas diferentes para descubrir cuál es el menor problema. 😁

Si todas estas fusiones son solo para búsquedas, disfrutará mucho una vez que elimine todas esas fusiones, establezca las relaciones adecuadas y las medidas USERRELATIONSHIP DAX. Esta es la situación de datos exacta para la que el modelo de datos tabulares fue diseñado para manejar de manera extremadamente eficiente. Estas fusiones están creando muchos más datos y desperdiciando innecesariamente sus recursos. Simplemente cargue esas tablas de búsqueda en el modelo de datos, haga que la relación se envíe, cree las medidas, ¡sorpréndase con los resultados!

–Nate

Gracias… parece que el consejo prevaleciente es seguir lo que también has sugerido. ¡Es hora de arremangarse y sumergirse en la remodelación de este modelo de datos!

Disculpe lo que puede ser una pregunta estúpida, pero ¿por qué está haciendo todas esas fusiones en Power Query? Normalmente traería esas tablas a Power BI y luego establecería relaciones entre ellas en el modelo de datos. Power Query siempre será una combinación lenta de datos. Desde una vista superficial, todas esas tablas se pueden relacionar en el modelo de datos, lo que dará como resultado un modelo mucho mejor y más rápido. ¿Por qué necesita fusionarse en Power Query?

Estuardo

Gracias por señalar ese método, y trato de hacerlo cuando sea posible. Sin embargo, en algunos casos tengo más de dos columnas que necesitan búsquedas de datos de la otra tabla… por ejemplo, un ticket de ayuda tiene un número de empleado para las columnas Abierto por, Propietario, Resuelto por y Cerrado por. Entonces, cuatro columnas diferentes con números de empleados y una tabla para buscarlos. Con un máximo de una relación activa y una relación inactiva, no puedo hacer que funcione para conectar esas cuatro columnas diferentes a la columna clave del empleado.

Si bien solo puede tener una relación activa en una tabla, puede tener cualquier número de relaciones inactivas (líneas de puntos) entre tablas. Crearía las relaciones inactivas en el modelo y luego usaría DAX USERRELATIONSHIP en las medidas para intercambiar entre ellas.

Estuardo

Por alguna razón, pensé que el máximo eran dos relaciones, así que definitivamente es bueno aprenderlo, ¡gracias! Mi informe ya tiene más de 25 medidas, así que me pregunto si agregar dos docenas más pone el informe en riesgo de ralentizarse por parte del usuario. De acuerdo, dado que principalmente realizo búsquedas y no cálculos/agregaciones, puede ser más inteligente para mí aprovechar la función DAX ‘RELACIONADA’ para dibujar los valores de columna necesarios en lugar de una medida. Mi razón principal para enviar gran parte del trabajo a PQ fue evitar que el lado del informe se volviera demasiado pesado y afectara la experiencia del usuario.

Las medidas solo se ejecutan cuando se colocan en el lienzo, por lo que puede tener tantas medidas como desee, pero si solo coloca algunas de ellas en las visualizaciones, no afectará el rendimiento. Ciertamente, he visto informes de Power BI con cientos de medidas y no afecta la velocidad de carga ni el rendimiento. DAX es un lenguaje eficiente si está respaldado por un buen modelo de datos, la clave es usar el esquema en estrella para hacer el trabajo, luego las medidas de DAX pueden aprovechar el contexto del filtro y solo necesitan ejecutarse en un subconjunto de registros. Por supuesto, es posible escribir DAX ineficiente con demasiado uso de iteradores que omiten el contexto del filtro y revuelven todos los registros de datos, pero si obtiene el modelo de datos correcto, eso no debería ser necesario.

Estuardo

De nuevo, gracias Estuardo. Pensé que tal vez las medidas estaban «precalculadas» a medida que se carga el modelo, por lo que saber que no hay un «costo» de recursos hasta que se colocan en imágenes me ayuda a sentirme más cómodo creando más de ellos. A pesar de mi uso atroz de fusiones, el resto de mi modelo se parece a un esquema en estrella, y definitivamente trato de escribir DAX eficiente (aunque desearía que fuera más fácil reconocer una mejora en la sintaxis). Realmente aprecio su orientación en esto!

Entradas relacionadas

Deja un comentario Cancelar respuesta