Unir tablas en 2 columnas en Power BI

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Recientemente estaba impartiendo una clase avanzada de DAX y estaba charlando con los estudiantes brillantes de la clase sobre varios temas. A través de las discusiones se me ocurrió que tal vez sea posible crear una unión compuesta entre 2 tablas usando una combinación de característica de relación inactiva y el característica de relación física de muchos a muchos. Lo probé y funciona. El resto del artículo a continuación explica cómo hacerlo y por qué puede o no querer hacerlo. Es principalmente una discusión de aprendizaje en lugar de un artículo del que inmediatamente querrá aprender y luego implementar.

Tengo algunos otros artículos sobre el uso de relaciones que puede que le guste leer. El primero es el uso más común de Relaciones múltiples y tengo otro que explica cómo usar TREATAS como filtro virtual.

Antes de continuar, déjame explicarte los antecedentes.

Relaciones de uno a muchos y el esquema de estrella

La forma estándar de unir 2 tablas en Power BI es con una relación única, activa, de uno a varios. Una vez que tiene algunas tablas unidas de esta manera, se denomina esquema en estrella (el ejemplo se muestra en la imagen a continuación).

021720_0153_joiningtabl1-5587077

El esquema en estrella es la forma preferida de cargar y modelar datos en Power BI. Tengo un artículo anterior que explica el esquema en estrella que puede leer si está interesado en más detalles. El punto clave relevante para este artículo es que una de las tablas en este tipo de relaciones debe tener una clave primaria, es decir, debe contener una columna que identifique de forma única cada fila de la tabla. No se permiten duplicados en al menos una de las dos tablas.

Relaciones de muchos a muchos

En algún momento de 2018 (de la memoria), Microsoft introdujo la capacidad de crear relaciones físicas de muchos a muchos en Power BI también.

021720_0153_joiningtabl2-4224859

Esta función se incluyó como parte de la capacidad del modelo de datos compuestos. Las relaciones físicas de muchos a muchos son un gran tema por derecho propio y no voy a cubrir eso con demasiado detalle en este artículo. La característica clave de este tipo de relación es que le permite unir 2 tablas incluso si hay valores repetidos en las columnas de combinación en ambas tablas, es decir, no requiere una clave principal en una de las columnas de una de las tablas. Una advertencia aquí: solo porque puedas, no significa que debas hacerlo.

Unir tablas con 2 relaciones

Ha sido posible crear múltiples relaciones de 1 a muchas entre 2 tablas desde el principio. Cuando tiene 2 relaciones entre tablas, al menos una de ellas debe estar inactiva (como se muestra a continuación). La relación punteada está inactiva.

021720_0153_joiningtabl3-2362896

Un caso de uso común para dicha configuración es cuando tiene 2 fechas en su tabla de datos (por ejemplo, fecha de pedido y fecha de entrega) y desea vincular ambas fechas a su tabla de calendario para generar informes. La relación inactiva se puede ‘habilitar’ dentro de una función CALCULAR (en una medida) para que pueda habilitar la relación bajo demanda. Puedes leer más sobre eso en mi artículo sobre múltiples relaciones.

Unir 2 tablas con combinaciones compuestas en 2 columnas

Bien, déjeme mostrarle cómo aprovechar muchas a muchas relaciones y relaciones inactivas para crear una clave compuesta en dos columnas entre 2 tablas. Tendrá más sentido con un ejemplo. Voy a utilizar algunos datos de entrenamiento que mi compañero Ken Puls me dio hace unos años. A continuación, puede ver una tabla de plan de cuentas que contiene una clave compuesta en las columnas AccNumber y AccDept.

Catálogo de cuentas

021720_0153_joiningtabl4-4816089

Es la combinación de AccNumber y AccDept lo que identifica de forma única cada fila en esta tabla. Dicho de otra manera, la tabla COA anterior no tiene una clave primaria como tal, pero es la combinación de las 2 columnas mencionado anteriormente que identifican de forma única cada fila.

Libro mayor

La otra tabla en mis datos de demostración es un extracto del libro mayor (ver más abajo). Tenga en cuenta que esta tabla también tiene 2 columnas (Cuenta y Departamento) para asignar cada transacción al COA.

021720_0153_joiningtabl5-3476196

La solución tradicional

La forma tradicional (estándar) de unir estas tablas en Power BI es crear una clave principal en la tabla COA concatenando AccNumber y AccDept en una nueva columna de clave principal, como esta.

021720_0153_joiningtabl6-2685196

Tenga en cuenta la nueva columna concatenada al final de la tabla.

Lo mismo debe hacerse con la tabla GL. Una vez que se crea la clave en la tabla GL, las 2 columnas originales para la cuenta y el departamento se pueden eliminar dejando solo la clave externa en la tabla GL (como se muestra a continuación).

021720_0153_joiningtabl7-9786047

La solución de unión compuesta

Como probablemente ya haya resuelto, es simplemente una cuestión de unir la tabla del plan de cuentas a la tabla del libro mayor con 2 relaciones como se muestra a continuación. Ambos serán relaciones de muchos a muchos porque la tabla de búsqueda del plan de cuentas (también conocida como tabla de dimensiones) contiene valores duplicados en ambas columnas que participan en la combinación. Debido a que hay 2 relaciones, una o ambas deben estar inactivas.

021720_0153_joiningtabl8-5647744

Cuando te unes a las mesas, es importante hacer 2 cosas

  1. Configure la dirección del filtro cruzado para que la tabla de búsqueda (dimensión) filtre los datos (tabla de hechos) como se muestra a continuación (desactive el filtrado bidireccional).
    021720_0153_joiningtabl9-3622485
  2. Asegúrese de que las columnas de la tabla de búsqueda (tabla de dimensiones) contengan un superconjunto de todos los registros de la tabla de datos (tabla de hechos).

Si no hace estas 2 cosas, se está metiendo en problemas; confíe en mi palabra. Las razones son otro de esos puntos de discusión más importantes que dejaré para otro día.

Escribiendo el DAX para unirlo todo

El paso final en el proceso para aprovechar este diseño de relación es escribir fórmulas DAX que utilizarán AMBAS relaciones al mismo tiempo. Esto es fácil y directo e involucra la función de USUARIO como se muestra a continuación.

Total Value =
     CALCULATE(
          SUM(GeneralLedger[Amount]),
          USERELATIONSHIP(ChartOfAccounts[AccDept],GeneralLedger[Dept]),
          USERELATIONSHIP(ChartOfAccounts[AccNumber],GeneralLedger[Account])
      )

El código anterior activa explícitamente ambas relaciones para que ambas estén activas. Los parámetros de filtro en CALCULATE operan como un Y lógico, por lo tanto, cuando se agregan 2 parámetros de filtro, se usan de manera aditiva. Es necesario agregar la relación activa así como la relación inactiva porque el comportamiento predeterminado para USERELATIONSHIP es activar la relación inactiva Y desactivar la relación activa. Al agregar ambos dentro de CALCUATE, ambos se aplican juntos.

Una palabra de precaución

Como muchas cosas en DAX y Power BI, el hecho de que pueda hacerlo no significa que deba hacerlo. Las relaciones de muchas a muchas no son como relaciones de una a muchas. Las relaciones de una a muchas se optimizan, materializan y almacenan en el modelo. Están diseñados para un filtrado cruzado de alto rendimiento entre tablas. Muchas a muchas relaciones no se materializan ni almacenan en el modelo, lo que significa que generalmente son menos eficientes y de menor rendimiento. Si tiene un modelo pequeño y es «lo suficientemente rápido», entonces, por supuesto, puede construir su modelo de esta manera.

Valores únicos

En términos generales, cuanto más valores únicos en una columna, peor será la compresión. Por definición, cuando concatenas 2 columnas juntas, estás aumentando significativamente la cardinalidad de la nueva columna (en varios órdenes de magnitud). Esto tiene el potencial de hacer que sus libros de trabajo sean más grandes y lentos. Pero tiene que sopesar eso con la compensación, que muchas relaciones son menos eficientes que 1 a muchas relaciones.

Algunos pensamientos de despedida

Edición: 19 de febrero de 2020

Algunas personas han hablado bastante en Twitter sobre esta publicación, así que pensé en volver y compartir mis pensamientos sobre por qué las uniones compuestas no son compatibles directamente.

El motor de almacenamiento de Power BI (Vertipaq) es una base de datos de almacén de columnas. Fue construido desde cero para estar altamente optimizado para la velocidad de recuperación de datos, compresión y agregación sobre la marcha. Siempre he creído que los diseñadores tomaron algunas decisiones arquitectónicas y compromisos para obtener los mejores resultados, y una de estas decisiones fue admitir solo una relación física única, de 1 a muchas entre dos tablas (no lo sé a ciencia cierta – Actualizaré esta publicación si puedo confirmar). Esto es muy diferente a SQL Server, que admite múltiples (combinaciones compuestas) entre dos tablas. Creo que existe una compensación con las uniones compuestas: solo se pueden admitir si hay compromisos en la velocidad de operación. Por lo tanto, la versión original de Vertipaq solo admitía de 1 a muchas relaciones activas únicas. Las uniones compuestas se hicieron posibles después de que se introdujera el tipo de relación de muchos a muchos a mediados de 2018.

¿Cuál usar?

Creo firmemente en el esquema en estrella como base de la mayoría de los buenos modelos de datos de Power BI. Por lo tanto, mi posición predeterminada es preferir el enfoque de esquema en estrella con una relación única y activa entre tablas. Pero también creo que puede desarrollar significativamente su profundidad de conocimiento, habilidades y superpoderes al explorar las capacidades de la herramienta y el lenguaje DAX. Entonces, con eso en mente, espero que este artículo le haya resultado útil para ampliar sus conocimientos.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.