Instrucciones para relaciones de varios a varios

Este artículo está dirigido a modeladores de datos como usted que trabajan con Power BI Desktop. Describe tres escenarios diferentes de modelado de varios a varios. También se proporcionan instrucciones sobre cómo diseñarlos correctamente en los modelos.

En realidad, hay tres escenarios de varios a varios. Se pueden producir cuando tiene que:

• Relacionar dos tablas de tipo de dimensión
• Relacionar dos tablas de tipo de hechos
• Relacionar tablas de tipo de hechos con un nivel de detalle más alto, cuando la tabla de tipo de hechos almacena filas con un nivel de detalle más alto que las de la tabla de tipo de dimensión

Relación de dimensiones varios a varios

A continuación se considerará el primer tipo de escenario de varios a varios con un ejemplo. En el escenario clásico se relacionan dos entidades: clientes de banco y cuentas bancarias. Imagine que los clientes pueden tener varias cuentas y que las cuentas pueden tener varios clientes. Cuando una cuenta tiene varios clientes, normalmente se denominan titulares conjuntos de la cuenta.

El modelado de estas entidades es sencillo. En una tabla de tipo de dimensión se almacenan las cuentas y en otra los clientes. Como es característico de las tablas de tipo de dimensión, en cada una hay una columna de identificador. Para modelar la relación entre las dos tablas, se requiere una tercera. Esta tabla se conoce comúnmente como tabla de puente. En este ejemplo, su finalidad es almacenar una fila por cada asociación de cuenta y cliente. Curiosamente, cuando esta tabla solo contiene columnas de identificador, se denomina tabla de hechos sin hechos.

Este es un sencillo diagrama de modelo de las tres tablas.

La primera tabla se denomina Account (Cuenta) y contiene dos columnas: AccountID (IdDeCuenta) y Account (Cuenta). La segunda tabla se denomina AccountCustomer (ClienteCuenta) y contiene dos columnas: AccountID (IdDeCuenta) y CustomerID (IdDeCliente). La tercera tabla se denomina Customer (Cliente) y contiene dos columnas: CustomerID (IdDeCliente) y Customer (Cliente). No existen relaciones entre ninguna de las tablas.

Se agregan dos relaciones uno a varios para relacionar las tablas. Este es un diagrama de modelo actualizado de las tablas relacionadas. Se ha agregado una tabla de tipo de hechos denominada Transaction (Transacción). Registra las transacciones de las cuentas. Se han ocultado la tabla de puente y todas las columnas de identificador.

Para facilitar la descripción del funcionamiento de la propagación del filtro de relaciones, se ha modificado el diagrama del modelo para mostrar las filas de la tabla.

Los detalles de las filas de las cuatro tablas se describen en la siguiente lista con viñetas:

• La tabla Account tiene dos filas:
  • AccountID 1 es para Account-01
  • AccountID 2 es para Account-02
• La tabla Customer tiene dos filas:
  • CustomerID 91 es para Customer-91
  • CustomerID 92 es para Customer-92
• La tabla AccountCustomer tiene tres filas:
  • AccountID 1 se asocia a CustomerID 91
  • AccountID 1 se asocia a CustomerID 92
  • AccountID 2 se asocia a CustomerID 92
• La tabla Transaction tiene tres filas:
  • Date January 1 2019, AccountID 1, Amount 100
  • Date February 2 2019, AccountID 2, Amount 200
  • Date March 3 2019, AccountID 1, Amount -25

A continuación se verá lo que sucede cuando se consulta el modelo.

A continuación se muestran dos objetos visuales que resumen la columna Amount de la tabla Transaction. El primer objeto visual agrupa por cuenta y, por tanto, la suma de las columnas Amount representa el saldo de cuenta. El segundo objeto visual agrupa por cliente y, por tanto, la suma de las columnas Amount representa el saldo del cliente.

El nombre del primer objeto visual es Account Balance (Saldo de la cuenta) y tiene dos columnas: Account (Cuenta) y Amount (Cantidad). Muestra el resultado siguiente:

• El saldo de Account-01 es 75
• El saldo de Account-02 es 200
• El total es 275

El nombre del segundo objeto visual es Customer balance (Saldo del cliente) y tiene dos columnas: Customer (Cliente) y Amount (Cantidad). Muestra el resultado siguiente:

• El saldo de Customer-91 es 275
• El saldo de Customer-92 es 275
• El total es 275

Un examen rápido de las filas de tabla y el objeto visual Account Balance muestra que el resultado es correcto, para cada cuenta y la cantidad total. Se debe a que cada agrupación de cuentas genera una propagación de filtro a la tabla Transaction de esa cuenta.

Pero parece que algo no es correcto en el objeto visual Customer Balance. Cada cliente del objeto visual Customer Balance tiene el mismo saldo que el saldo total. Este resultado solo podría ser correcto si todos los clientes fueran titulares conjuntos de todas las cuentas. Pero en este ejemplo no es el caso. El problema está relacionado con la propagación del filtro. No fluye de forma completa hasta la tabla Transaction.

Siga las direcciones del filtro de relaciones desde la tabla Customer a la tablaTransaction. Debe ser evidente que la relación entre la tabla Account y AccountCustomer se propaga en la dirección equivocada. La dirección del filtro para esta relación se debe establecer en Ambos.

Como se esperaba, no ha habido ningún cambio en el objeto visual Account Balance.

Pero ahora los objetos visuales Customer Balance muestran el siguiente resultado:

• El saldo de Customer-91 es 75
• El saldo de Customer-92 es 275
• El total es 275

Ahora el objeto visual Customer Balance muestra un resultado correcto. Siga personalmente las direcciones del filtro y vea cómo se han calculado los saldos del cliente. Además, recuerde que el total visual significa todos los clientes.

Alguien que no esté familiarizado con las relaciones del modelo podría concluir que el resultado es incorrecto. Se podría preguntar: ¿Por qué el saldo total de Customer-91 y Customer-92 no es igual a 350 (75 + 275)?

La respuesta a su pregunta radica en comprender la relación varios a varios. Cada saldo de cliente puede representar la suma de varios saldos de cuenta, y los saldos del cliente no son aditivos.

Instrucciones para la relación de dimensiones varios a varios

Si tiene una relación de varios a varios entre tablas de tipo de dimensión, siga las instrucciones siguientes:

• Agregue cada entidad relacionada de varios a varios como una tabla de modelo, y asegúrese de que tiene una columna de identificador único (ID)
• Agregue una tabla de puente para almacenar entidades asociadas
• Cree relaciones de uno a varios entre las tres tablas
• Configure una relación bidireccional para permitir que la propagación del filtro continúe a las tablas de tipo de hechos
• Si no es adecuado tener valores de identificador que faltan, establezca la propiedad Admite valores NULL de las columnas ID en FALSE; después, se producirá un error en la actualización de datos si hay valores que faltan en el origen
• Oculte la tabla de puente (a menos que contenga columnas o medidas adicionales necesarias para la generación de informes)
• Oculte las columnas de identificador que no sean adecuadas para los informes (por ejemplo, cuando los identificadores son claves suplentes)
• Si tiene sentido dejar visible una columna de identificador, asegúrese de que se encuentra en el lado "uno" de la relación; oculte siempre la columna del lado "varios". El resultado será el rendimiento óptimo de los filtros.
• Para evitar confusiones o malinterpretaciones, comunique las explicaciones a los usuarios del informe: puede agregar descripciones con cuadros de texto o información sobre herramientas de encabezado a los objetos visuales

No se recomienda relacionar directamente las tablas de tipo de dimensión de varios a varios. Este enfoque de diseño requiere la configuración de una relación con una cardinalidad de varios a varios. Conceptualmente se puede lograr, pero implica que las columnas relacionadas contendrán valores duplicados. Pero es un procedimiento recomendado de diseño que las tablas de tipo de dimensión tengan una columna ID. Las tablas de tipo de dimensión siempre deben usar la columna ID como el lado "uno" de una relación.

Relación varios a varios de hechos

El segundo tipo de escenario de varios a varios implica la relación de dos tablas de tipo de hechos. Dos tablas de tipo de hechos se pueden relacionar directamente. Esta técnica de diseño puede ser útil para la exploración de datos rápida y sencilla. Pero para ser claros, generalmente no se recomienda este enfoque de diseño. Más adelante en esta sección se explicarán los motivos.

Considere un ejemplo con dos tablas de tipo de hechos: Order (Pedido) y Fulfillment (Suministro). La tabla Order contiene una fila por línea de pedido y la tabla Fulfillment puede contener cero o más filas por línea de pedido. Las filas de la tabla Order representan pedidos de venta. Las filas de la tabla Fulfillment representan los artículos de pedidos que se han enviado. Una relación varios a varios relaciona las dos columnas OrderID, y el filtro solo se propaga desde la tabla Order (Order filtra Fulfillment).

La cardinalidad de relación se establece en varios a varios para admitir el almacenamiento de valores OrderID duplicados en las dos tablas. En la tabla Order pueden existir valores OrderID duplicados porque un pedido puede tener varias líneas. En la tabla Fulfillment pueden existir valores OrderID duplicados porque los pedidos pueden tener varias líneas, y las líneas de pedido se pueden cumplir con varios envíos.

A continuación se examinarán las filas de la tabla. En la tabla Fulfillment, observe que varios pedidos se pueden cumplir mediante varios envíos. (La ausencia de una línea de pedido significa que el pedido todavía no se ha cumplido).

Los detalles de las filas de las dos tablas se describen en la siguiente lista con viñetas:

• La tabla Order tiene cinco filas:
  • OrderDate January 1 2019, OrderID 1, OrderLine 1, ProductID Prod-A, OrderQuantity 5, Sales 50
  • OrderDate January 1 2019, OrderID 1, OrderLine 2, ProductID Prod-B, OrderQuantity 10, Sales 80
  • OrderDate February 2 2019, OrderID 2, OrderLine 1, ProductID Prod-B, OrderQuantity 5, Sales 40
  • OrderDate February 2 2019, OrderID 2, OrderLine 2, ProductID Prod-C, OrderQuantity 1, Sales 20
  • OrderDate March 3 2019, OrderID 3, OrderLine 1, ProductID Prod-C, OrderQuantity 5, Sales 100
• La tabla Fulfillment tiene cuatro filas:
  • FulfillmentDate January 1 2019, FulfillmentID 50, OrderID 1, OrderLine 1, FulfillmentQuantity 2
  • FulfillmentDate February 2 2019, FulfillmentID 51, OrderID 2, OrderLine 1, FulfillmentQuantity 5
  • FulfillmentDate February 2 2019, FulfillmentID 52, OrderID 1, OrderLine 1, FulfillmentQuantity 3
  • FulfillmentDate January 1 2019, FulfillmentID 53, OrderID 1, OrderLine 2, FulfillmentQuantity 10

A continuación se verá lo que sucede cuando se consulta el modelo. A continuación se muestra un objeto visual de tabla en el que se comparan las cantidades de pedidos y de cumplimiento por la columna OrderID de la tabla Order.

El objeto visual presenta un resultado exacto. Pero la utilidad del modelo es limitada: solo se puede filtrar o agrupar por la columna OrderID de la tabla Order.

Instrucciones para la relación de hechos varios a varios

Por lo general, no se recomienda relacionar directamente dos tablas de tipo de hechos mediante la cardinalidad de varios a varios. La razón principal es que el modelo no proporcionará flexibilidad a los objetos visuales de informe para filtrar o agrupar. En el ejemplo, solo es posible que los objetos visuales filtren o agrupen por la columna OrderID de la tabla Order. Un motivo adicional está relacionado con la calidad de los datos. Si los datos tienen problemas de integridad, es posible que algunas filas se omitan durante las consultas debido a la naturaleza de la relación débil. Para más información, consulte Creación de relaciones de modelos en Power BI Desktop (Evaluación de las relaciones).

En lugar de relacionar directamente tablas de tipos de hechos, se recomienda adoptar principios de diseño de esquema de estrella. Para ello, agregue tablas de tipo de dimensión. Las tablas de tipo de dimensión se relacionan con las tablas de tipo de hechos mediante relaciones de uno a varios. Este enfoque de diseño es robusto, ya que ofrece opciones de informe flexibles. Permite filtrar o agrupar con cualquiera de las columnas de tipo de dimensión y resumir cualquier tabla de tipo de hechos relacionada.

A continuación se considerará una solución mejor.

Tenga en cuenta los siguientes cambios de diseño:

• Ahora el modelo tiene cuatro tablas adicionales: OrderLine, OrderDate, Product y FulfillmentDate
• Las cuatro tablas adicionales son todas de tipo de dimensión y las relaciones uno a varios las relacionan con las tablas de tipo de hechos.
• La tabla OrderLine contiene una columna OrderLineID, que representa el valor OrderID multiplicado por 100, más el valor OrderLine, un identificador único para cada línea de pedido
• Las tablas Order y Fulfillment ahora contienen una columna OrderLineID y ya no contienen las columnas OrderID ni OrderLine.
• La tabla Fulfillment contiene ahora columnas OrderDate y ProductID.
• La tabla FulfillmentDate solo está relacionada con la tabla Fulfillment.
• Todas las columnas de identificador único están ocultas

Dedicar tiempo a aplicar los principios de diseño de esquema de estrella ofrece las ventajas siguientes:

• Los objetos visuales del informe pueden filtrar o agrupar por cualquier columna visible de las tablas de tipo de dimensión.
• Los objetos visuales del informe pueden resumir por cualquier columna visible de las tablas de tipo de hechos.
• Los filtros aplicados a las tablas OrderLine, OrderDate o Product se propagarán a ambas tablas de tipo de hechos.
• Todas las relaciones son de uno a varios, y cada una es una relación sólida. Los problemas de integridad de los datos no se enmascaran. Para más información, consulte Creación de relaciones de modelos en Power BI Desktop (Evaluación de las relaciones).

Relación de hechos con un nivel de detalle más alto

Este escenario de varios a varios es muy diferente de los otros dos que ya se han descrito en este artículo.

A continuación se verá un ejemplo en el que se incluyen cuatro tablas: Date (Fecha), Sales (Ventas), Product (Producto) y Target (Destino). Date y Product son tablas de tipo de dimensión, y las relaciones de uno a varios las relacionan con la tabla de tipo de hechos Sales. Hasta ahora, representa un buen diseño de esquema de estrella. Pero la tabla Target todavía se tiene que relacionar con las demás tablas.

La tabla Target contiene tres columnas: Category, TargetQuantity y TargetYear. Las filas de la tabla revelan una granularidad de año y categoría de producto. En otras palabras, los destinos, que se usan para medir el rendimiento de las ventas, se establecen cada año para cada categoría de producto.

Como en la tabla Target se almacenan datos en un nivel superior al de las tablas de tipo de dimensión, no se puede crear una relación de uno a varios. Bueno, es cierto para una sola de las relaciones. A continuación se verá cómo se puede relacionar la tabla Target con las tablas de tipo de dimensión.

Relación de períodos de tiempo con un nivel de detalle más alto

Una relación entre las tablas Date y Target debe ser de uno a varios. Se debe a que los valores de la columna TargetYear son fechas. En este ejemplo, cada valor de columna TargetYear es la primera fecha del año de destino.

Pero debe tener cuidado para asegurarse de que los filtros de nivel de mes o de fecha producen un resultado significativo. Sin una lógica de cálculo especial, los objetos visuales de informe pueden notificar que las fechas de destino son literalmente el primer día de cada año. Todos los demás días (y todos los meses excepto enero) resumirán la cantidad de destino como en blanco.

En el siguiente objeto visual de matriz se muestra lo que sucede cuando el usuario del informe profundiza en los meses de un año. El objeto visual resume la columna TargetQuantity. (La opción Mostrar elementos sin datos se ha habilitado para las filas de la matriz).

Para evitar este comportamiento, se recomienda controlar el resumen de los datos de hechos mediante medidas. Una manera de controlar el resumen consiste en devolver un valor en blanco cuando se consultan los períodos de tiempo de nivel inferior. Otra manera, definida con sofisticadas funciones DAX, consiste en es prorratear los valores en períodos de tiempo de nivel inferior.

Considere la siguiente definición de medida que usa la función DAX ISFILTERED. Solo devuelve un valor cuando las columnas Date o Month no se filtran.

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

El siguiente objeto visual de matriz usa ahora la medida Target Quantity (Cantidad de destino). Muestra que todas las cantidades de destino mensuales están en blanco.

Relación con un nivel de detalle más alto (distinto de fechas)

Al relacionar una columna que no sea de fecha de una tabla de tipo de dimensión con una tabla de tipo de hechos (y en un nivel de detalle más alto que la tabla de tipo de dimensión) se requiere otro enfoque de diseño.

Las columnas Category (de las tablas Product y Target) contienen valores duplicados. Por tanto, no hay ningún "uno" para una relación de uno a varios. En este caso, tendrá que crear una relación de varios a varios. La relación debe propagar los filtros en una sola dirección, desde la tabla de tipo de dimensión a la de tipo de hechos.

A continuación se examinarán las filas de la tabla.

En la tabla Target hay cuatro filas: dos para cada año de destino (2019 y 2020) y dos categorías (ropa y accesorios). En la tabla Product hay tres productos. Dos pertenecen a la categoría de ropa y uno a la de accesorios. Uno de los colores de la ropa es el verde y los dos restantes son azules.

Un objeto visual de tabla que agrupe por la columna Category de la tabla Product genera el resultado siguiente.

Este objeto visual genera el resultado correcto. Ahora se verá qué sucede cuando se usa la columna Color de la tabla Product para agrupar la cantidad de destino.

El objeto visual genera una representación incorrecta de los datos. ¿Qué sucede aquí?

Un filtro en la columna Color de la tabla Product genera dos filas. Una de las filas es para la categoría Clothing y la otra para la categoría Accessories. Estos dos valores de categoría se propagan como filtros a la tabla Target. En otras palabras, como los productos de dos categorías usan el color azul, esas categorías se usan para filtrar los destinos.

Para evitar este comportamiento, como se ha descrito antes, se recomienda controlar el resumen de los datos de hechos mediante medidas.

Considere la siguiente definición de medida. Observe que todas las columnas de la tabla Product que están debajo del nivel de categoría se prueban para los filtros.

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

El siguiente objeto visual de tabla usa ahora la medida Target Quantity (Cantidad de destino). Muestra que todas las cantidades de destino de color están en blanco.

El diseño final del modelo es similar al siguiente.

Instrucciones para la relación de hechos con un nivel de detalle más alto

Si necesita relacionar una tabla de tipo de dimensión con una de tipo de hechos, y la tabla de tipo de hechos almacena filas con un nivel de detalle más alto que las filas de la tabla de tipo de dimensión, siga estas instrucciones:

• Para fechas de hechos con un nivel de detalle más alto:
  • En la tabla de tipo de hechos, almacene la primera fecha del período de tiempo
  • Cree una relación de uno a varios entre la tabla de fechas y la de tipo de hechos
• Para otros hechos con un nivel de detalle más alto:
  • Cree una relación de varios a varios entre la tabla de tipo de dimensión y la de tipo de hechos
• Para los dos tipos:
  • Controle el resumen mediante lógica de medida: devuelva valores en blanco cuando se usen columnas de tipo de dimensión de nivel inferior para filtrar o agrupar
  • Oculte las columnas de tabla de tipo de hechos que se puedan resumir: de esta forma, solo se podrán usar medidas para resumir la tabla de tipo de hechos.

Contenido relacionado

Para obtener más información sobre este artículo, consulte los recursos siguientes:

• Relaciones de modelos en Power BI Desktop
• Descripción de un esquema de estrella e importancia para Power BI
• Instrucciones para solución de problemas de relaciones
• ¿Tiene alguna pregunta? Pruebe a preguntar a la comunidad de Power BI
• ¿Sugerencias? Ideas para contribuir a mejorar Power BI