Contenidos
Esta semana estaba trabajando para un cliente: tenían un problema de rendimiento con un informe de Power BI. Los datos en el workbook no eran demasiado grandes, alrededor de 400.000 filas, pero el tamaño del archivo era de 110 megabytes y el rendimiento del modelo era relativamente lento dada la cantidad de registros. Cuando miré el informe, noté que el informe estaba usando GUID entre las claves primaria y externa en varias tablas. En términos generales, no es una buena práctica usar un GUID para unir tablas, ya que los GUID no se comprimen bien y tienen un efecto negativo en la eficiencia de las relaciones físicas de 1 a muchas.
¿Qué es un GUID?
GUID es un acrónimo de Globally Unique IDentifier. En resumen, un GUID es un número hexadecimal que es equivalente a un número decimal con 39 dígitos, algo así como esto 1,000,000,000,000,000,000,000,000,000,000,000,000,000 (un Duodecillion). Dicho de otra manera, un GUID es un ID muy exclusivo que es poco probable (según la probabilidad) que se reproduzca incluso si se generó de forma aleatoria, lo que es. Lea más sobre esto aquí:
https://betterexplained.com/articles/the-quick-guide-to-guids/
¿Qué hay de malo en usar GUID en las relaciones?
Una relación física de 1 a muchos en Power BI es una parte fundamental de la estructura de la base de datos subyacente. El motor de Power BI (Vertipaq) materializa y almacena estas relaciones en la base de datos y luego las usa para propagar filtros rápidamente de una tabla a otra (a través de la relación de 1 a muchos). Es muy común (incluso deseable) que la lógica de relación se cargue en la memoria caché rápida L1 o L2 en el chip de su PC para que pueda hacer su trabajo súper rápido. Si la relación no encaja en la memoria caché, todo el proceso será más lento. Las cosas que pueden aumentar el tamaño de la relación son el número de valores únicos en las columnas utilizadas en la relación, pero también el tipo de datos utilizado en la relación. Basta decir que un GUID es mucho menos eficiente que un valor entero como columna clave en Power BI.
Ingrese una clave sustituta
Una forma de resolver este problema es reemplazar el GUID con una clave sustituta. Una clave sustituta, como su nombre indica, es una nueva «columna de clave» que es una «sustituta» (o un reemplazo) de la columna de clave original. En un mundo perfecto donde tiene un servidor SQL en el back-end y tiene un departamento de TI que puede hacer el trabajo por usted, le sugiero que haga que su departamento de TI cree la clave sustituta para usted y la ponga a disposición en una vista. para que no tenga que utilizar el GUID. Pero el mundo en el que vivimos no es perfecto, por lo tanto, este artículo le mostrará cómo reemplazar el GUID con una clave sustituta mediante Power Query.
Pasos para completar
Suponiendo que no puede realizar el trabajo en la fuente de datos, esto es esencialmente un problema que Power Query debe resolver. Los pasos para completar este proceso son los siguientes
- crear una conexión a la tabla de dimensiones sin formato
- crear una rama en la consulta de energía
- agregar una nueva columna de ID de entero (clave sustituta)
- Vuelva a unir la tabla de claves sustitutas con la tabla de dimensiones original y reemplace el GUID mediante la combinación
- repita el paso para reemplazar el GUID en la tabla de hechos
Este proceso asume que su tabla de dimensiones contiene una lista completa de claves que existen en su tabla de hechos. Si este no es el caso, tiene un problema de todos modos. Es posible variar este patrón para incluir los GUID que provienen de la tabla de hechos también, sin embargo, en realidad no resuelve el problema de raíz (es posible que tenga ID en la tabla de hechos que faltan en la tabla de dimensiones) por lo tanto, no he proporcionó / sugirió esto como parte de la solución. Además, hacer esto ciertamente ralentizará el tiempo de actualización sin resolver el problema de raíz.
Conectarse a los datos
Como puede ver en la imagen a continuación, tengo dos consultas (RawCustomer, RawSales que se muestran como el n. ° 1 a continuación) que se conectan directamente a mis datos de muestra (modifiqué Adventure Works para que el número de cliente use un GUID n. ° 2 a continuación). Tenga en cuenta que mis dos consultas de datos sin procesar son conexiones simples. Esta es la técnica que Ken enseña en el Capacitación de Power Query Academyy creo que es una gran práctica. A partir de ahí, creé dos consultas de prueba (que se muestran como el n. ° 3 a continuación) que son referencias simples a las consultas de datos sin procesar
Entonces, en este punto, simplemente se ve así
Crear una rama en Power Query en la tabla de dimensiones
- El siguiente paso que seguí fue hacer clic derecho en la consulta de preparación del cliente y seleccionar «referencia» para crear una nueva sucursal. Llamé a esta nueva consulta CustomerGUIDs. En esta consulta, simplemente guardé la columna GUID y eliminé todo lo demás. Como paso de seguridad, elimino los duplicados en caso de que haya un duplicado en la columna GUID (aunque es poco probable).
- Luego agregué una columna de índice comenzando en 1 y la llamé CustomerID.
La nueva columna CustomerID es la clave sustituta. La belleza de este enfoque es que las claves sustitutas crecerán con el tiempo si crece la lista GUID original (lo cual es muy probable). Además, si hay eliminaciones de la tabla de clientes, la clave sustituta simplemente se reconstruirá a sí misma con el nuevo conjunto de datos.
Vuelva a unir la tabla de claves sustitutas para formar una nueva tabla de dimensiones
Puede ver la versión antes (n. ° 1) y después (n. ° 2) de la tabla de clientes a continuación.
Repita el proceso para reemplazar el GUID en la tabla de hechos
- Creé una nueva consulta a partir de la consulta SalesStaging (clic derecho, «referencia») y la fusioné con la tabla CustomerGUIDs.
- Luego extraje la clave sustituta
- Luego eliminé el GUID original de la tabla de ventas final.
Mi vista de dependencia de consulta final se ve así.
Me aseguré de que todas las tablas estuvieran configuradas para que no se cargaran aparte de las tablas Clientes y Ventas.
Resultados
La demostración que les he mostrado aquí es con un conjunto de datos muy pequeño (19.000 filas). En mi caso, el enfoque de clave sustituta redujo el tamaño del archivo en más del 30%, y solo había una tabla de claves sustitutas en mi archivo. En el caso de mi cliente que tenía alrededor de 400,000 filas de datos, 3 columnas de clave GUID y un número mucho mayor de GUID únicos, la reducción del tamaño del archivo fue mucho mayor, de 110 MB a solo 11 MB (reducción del 90% con la clave sustituta). Probablemente, lo más importante es que hubo mejoras notables en el rendimiento después del cambio.
¿Hay algún impacto negativo?
Tiempo de actualización
En este punto, es posible que se pregunte «¿no ralentizará esto el tiempo de actualización?». Si estaba pensando esto, tiene toda la razón, lo más probable es que ralentice el rendimiento de la actualización. Sin embargo, es mucho mejor tener un tiempo de actualización más lento y un rendimiento de tiempo de ejecución más rápido que al revés.
¿Qué sucede si necesito mis GUID para auditoría?
Un segundo problema es que es posible que necesite los GUID para poder rastrear los datos en su informe hasta la transacción en el sistema de origen. Si esta es una necesidad común con sus datos, le sugiero que aún cree y use la clave sustituta, pero también cargue el GUID en la tabla de dimensiones como una columna adicional. De esa forma, el GUID está disponible pero no se usa en la relación. Mejor aún, elimine el GUID de la tabla de dimensiones y luego vuelva a traerlo más tarde solo si tiene un problema que necesita que rastree la fuente.
Conclusión y dónde aprender más
Aquí hay un copia de los libros de trabajo Utilicé en este artículo en caso de que esté interesado en echar un vistazo más de cerca.
Si desea aprender a ser excelente con Power Query, le recomiendo que eche un vistazo a la Academia de Power Query capacitación en línea en Skillwave.training. Ken, Miguel y yo hemos unido fuerzas para crear el mejor y más completo curso de capacitación de Power Query disponible.
