Compartir a través de

Instrucciones para relaciones de varios a varios

  • Artículo

Este artículo está dirigido a un modelador de datos que trabaja con Power BI Desktop. Describe tres escenarios diferentes de modelado de varios a varios. También te proporciona orientación sobre cómo diseñar con éxito para ellos en tus modelos.

Nota

En este artículo no se trata una introducción a las relaciones entre modelos. Si no está familiarizado con las relaciones, sus propiedades o cómo configurarlas, se recomienda que primero lea el artículo Relaciones de modelos en Power BI Desktop.

También es importante que tenga conocimientos sobre el diseño de esquemas de estrella. Para más información, vea Descripción de un esquema de estrella e importancia para Power BI.

Hay tres escenarios diferentes de varios a varios. Pueden ocurrir cuando se requiere que:

Relación de dimensiones varios a varios

El escenario clásico de muchos a muchos relaciona dos entidades, por ejemplo, los clientes bancarios y las cuentas bancarias. Tenga en cuenta que los clientes pueden tener varias cuentas y las cuentas pueden tener varios clientes. Cuando una cuenta tiene varios clientes, normalmente se denominan titulares de cuentas conjuntas .

El modelado de estas entidades es sencillo. En una tabla de dimensión se almacenan las cuentas y en otra los clientes. Como es característica de las tablas de dimensiones, hay una columna de identificador único (ID) en cada tabla. Para modelar la relación entre las dos tablas, se requiere una tercera tabla. Esta tabla se conoce normalmente como una tabla de interconexión . En este ejemplo, su propósito es almacenar una fila para cada asociación entre cliente y cuenta. Curiosamente, cuando esta tabla solo contiene columnas de identificador, se denomina tabla de hechos sin datos.

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

Diagrama que muestra tres tablas de modelo. El diseño se describe en el párrafo siguiente.

La primera tabla se denomina Accounty contiene dos columnas: AccountID y Account. La segunda tabla se denomina AccountCustomery contiene dos columnas: AccountID y CustomerID. La tercera tabla se denomina Customery contiene dos columnas: CustomerID y Customer. No existen relaciones entre ninguna de las tablas.

Se agregan dos relaciones uno a varios para relacionar las tablas. Este es un diagrama de modelos actualizado de las tablas relacionadas. Se ha agregado una tabla de hechos denominada Transaction. Registra transacciones de cuenta. Se han ocultado la tabla de puente y todas las columnas de identificador.

Diagrama que muestra que un diagrama de modelo consta de cuatro tablas. Se han agregado relaciones uno a varios para relacionar todas las tablas.

Para ayudar a describir cómo funciona la propagación del filtro de relación, el diagrama del modelo se ha modificado para mostrar las filas de la tabla.

Diagrama que muestra las tablas de modelo y sus filas. Los detalles de fila de las cuatro tablas se describen en el párrafo siguiente.

Los detalles de las filas de las cuatro tablas se presentan en la siguiente lista en 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:
    • AccountID1 está asociado a CustomerID91
    • AccountID1 está asociado a CustomerID92
    • AccountID2 está asociado a CustomerID92
  • La tabla Transaction tiene tres filas:
    • Date1 de enero de 2019, AccountID1, Amount100
    • Date2 de febrero de 2019, AccountID2, Amount200
    • Date3 de marzo de 2019, AccountID1, Amount-25

Veamos lo que sucede cuando se consulta el modelo.

En la imagen siguiente, hay dos objetos visuales de tabla 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.

Diagrama que muestra dos objetos visuales de tabla sentados en paralelo. Los objetos visuales se describen en el párrafo siguiente.

El primer objeto visual de tabla (Saldo de cuenta) tiene dos columnas: Account y Amount. Muestra el resultado siguiente:

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

El segundo objeto visual de la tabla (Customer Balance) tiene dos columnas: Customer y Amount. Muestra el resultado siguiente:

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

Un vistazo rápido a las filas de la tabla y el visual del Saldo de Cuenta revela que el resultado es correcto, para cada cuenta y el importe total. Esto se debe a que cada agrupación de cuentas da como resultado 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 en esta visualización tiene el mismo saldo que el saldo total. Este resultado solo podría ser correcto si todos los clientes eran titulares conjuntos de cada cuenta. No es así en este ejemplo. Hay un problema y está relacionado con la propagación de filtros. Los filtros no fluyen hasta la tabla Transaction .

Si sigue las instrucciones de filtro de relación de la tabla Customer a la tabla Transaction, puede determinar que la relación entre las tablas Account y AccountCustomer se propaga en dirección incorrecta. La dirección del filtro para esta relación debe establecerse en Both.

Diagrama que muestra que el modelo se ha actualizado. Ahora filtra en ambas direcciones.

Diagrama que muestra los mismos dos objetos visuales de informe colocados uno al lado del otro. El primer objeto visual no ha cambiado, mientras que el segundo objeto visual lo ha hecho.

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

No obstante, el gráfico de Customer Balance ahora muestra el siguiente resultado:

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

El objeto visual Customer Balance ahora muestra un resultado correcto. Siga las instrucciones del filtro y observe cómo se calcularon los saldos de los clientes. 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. Pueden preguntar: ¿Por qué no es el saldo total de Customer-91 y Customer-92 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

Cuando tenga una relación de varios a varios entre tablas de dimensiones, siga estas instrucciones:

  • Agregue cada entidad relacionada de varios a varios como una tabla de modelo, asegurándose de que tiene una columna de identificador.
  • Agregue una tabla intermedia para almacenar las entidades asociadas.
  • Cree relaciones de uno a varios entre las tres tablas
  • Establezca una relación bidireccional para permitir que la propagación de filtros continúe con la tabla de hechos.
  • Cuando no sea adecuado tener valores de identificador que faltan, deshabilite la propiedad Is Nullable: se producirá un error en la actualización de datos cuando se produzcan valores que faltan.
  • Oculte la tabla de conexión (a menos que contenga otras columnas o medidas necesarias para la elaboración de informes).
  • Oculte las columnas de identificador que no sean adecuadas para los informes (por ejemplo, cuando las columnas almacenan valores de clave 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". Esto se debe a que los filtros aplicados a la diapositiva "uno" dan como resultado un mejor rendimiento del filtro.
  • 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 dimensiones de varios a varios. Este enfoque de diseño requiere configurar una relación con una cardinalidad de varios a varios. Conceptualmente se puede lograr, pero implica que las columnas relacionadas pueden contener valores duplicados. Es una práctica de diseño bien aceptada, sin embargo, que las tablas de dimensiones tienen una columna de ID. Las tablas de dimensiones siempre deben usar la columna ID como el lado "uno" de una relación.

Relación varios a varios de hechos

Un tipo de escenario de varios a varios diferente implica relacionar dos tablas de hechos. Dos tablas de hechos pueden estar relacionadas directamente. Esta técnica de diseño puede ser útil para la exploración rápida y sencilla de datos. Sin embargo, y para ser claro, generalmente no se recomienda este enfoque de diseño. Explicaremos por qué más adelante en esta sección.

Veamos un ejemplo que implica dos tablas de hechos: Order y Fulfillment. La tabla Order contiene una fila por línea de pedido y la tabla de Fulfillment puede contener cero o más filas por línea de pedido. Las filas de la tabla Order representan pedidos de ventas. Las filas de la tabla Fulfillment representan los elementos de pedido que se han enviado. Una relación de varios a varios relaciona las columnas de OrderID de cada tabla, con propagación de filtros solo de la tabla de Order (lo que significa que la tabla Order filtra la tabla Fulfillment).

Diagrama que muestra un modelo que contiene dos tablas: Pedido y Suministro.

La cardinalidad de la relación se establece en Many-to-many para admitir el almacenamiento de valores de columna duplicados OrderID en ambas tablas. En la tabla Order, pueden existir valores de identificador duplicados porque un orden puede tener varias líneas. En la tabla Fulfillment, pueden existir valores de identificación duplicados porque los pedidos pueden tener varias líneas, y las líneas de pedido pueden ser cumplimentadas por muchos envíos.

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

Diagrama que muestra las filas de la tabla del modelo. Los detalles de fila de las dos tablas se describen en el párrafo siguiente.

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

  • La tabla Order tiene cinco filas:
    • OrderDate1 de enero de 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
    • OrderDate1 de enero de 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
    • OrderDate2 de febrero de 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
    • OrderDate2 de febrero de 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
    • OrderDate3 de marzo de 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
  • La tabla Fulfillment tiene cuatro filas:
    • FulfillmentDate1 de enero de 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
    • FulfillmentDate2 de febrero de 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
    • FulfillmentDate2 de febrero de 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
    • FulfillmentDate1 de enero de 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Veamos lo que sucede cuando se consulta el modelo. Aquí tienes una representación visual en tabla que compara las cantidades de pedidos y cumplimiento por la columna OrderID de la tabla Order.

Diagrama que muestra una visualización de tabla con tres columnas: ID de Pedido, Cantidad de Pedido y Cantidad de Cumplimiento.

El objeto visual presenta un resultado exacto. Sin embargo, la utilidad del modelo está limitada porque solo puede filtrar o agrupar por Order tabla OrderID columna.

Instrucciones para la relación de hechos varios a varios

Por lo general, no se recomienda relacionar dos tablas de hechos directamente mediante cardinalidad de varios a varios. La razón principal es que el modelo no proporcionará flexibilidad en las maneras en que los objetos visuales del informe filtran o agrupan. En el ejemplo, solo es posible que los objetos visuales filtren o agrupe por la columna OrderID de la tabla Order. Otro motivo se relaciona con la calidad de los datos. Si los datos tienen problemas de integridad, es posible que se omitan algunas filas durante la consulta debido a la cardinalidad de varios hombres y a las relaciones limitadas.

En lugar de relacionar las tablas de hechos directamente, le recomendamos que implemente un diseño de esquema estrella. Esto significa que agrega tablas de dimensiones. A continuación, estas tablas de dimensiones se relacionan con las tablas de hechos mediante relaciones de uno a varios. Este enfoque de diseño es sólido, ya que ofrece de forma eficaz opciones de informes flexibles. Permite filtrar o agrupar mediante cualquiera de las columnas de la tabla de dimensiones y resumir columnas de cualquier tabla de hechos relacionada.

Consideremos una mejor solución.

Diagrama que muestra un modelo que consta de seis tablas: OrderLine, OrderDate, Order, Fulfillment, Product y FulfillmentDate.

Observe los siguientes cambios de diseño:

  • El modelo ahora tiene cuatro tablas adicionales: OrderLine, OrderDate, Producty FulfillmentDate.
  • Las cuatro tablas adicionales son todas las tablas de dimensiones en las que las relaciones de uno a varios las relacionan con las tablas de hechos.
  • La tabla OrderLine contiene la columna OrderLineID, que almacena el valor de OrderID multiplicado por 100, además del valor de columna OrderLine, un identificador para cada línea de pedido.
  • Las tablas Order y Fulfillment ahora contienen una columna de OrderLineID y ya no contienen las columnas OrderID y OrderLine.
  • La tabla Fulfillment ahora contiene columnas OrderDate y ProductID.
  • La tabla FulfillmentDate tiene una relación solo con la tabla Fulfillment.
  • Todas las columnas de identificador están ocultas.

El tiempo necesario para adoptar un diseño de esquema de estrella ofrece las siguientes ventajas:

  • Los objetos visuales del informe pueden filtrar o agrupar por cualquier columna visible de las tablas de dimensión.
  • Los objetos visuales del informe pueden resumir por cualquier columna visible de las tablas de hechos.
  • Los filtros aplicados a las tablas OrderLine, OrderDateo Product se aplican también a ambas tablas de hechos.
  • Todas las relaciones son de uno a varios, y cada una es una relación sólida. Los problemas de integridad de datos no se enmascararán. Para obtener más información sobre la evaluación de relaciones, consulte Relaciones de modelo en Power BI Desktop.

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 describen en este artículo.

Veamos un ejemplo que implica cuatro tablas: Date, Sales, Producty Target. Las tablas Date y Product son tablas de dimensiones y las relaciones de uno a varios conectan cada una con la tabla de hechos Sales. Hasta ahora, representa un buen diseño de esquema de estrella. Sin embargo, la tabla Target todavía no está relacionada con las otras tablas.

Diagrama que muestra un modelo que consta de cuatro tablas: Fecha, Ventas, Producto y Destino.

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

Diagrama que muestra las tablas de hechos Sales y Target. La tabla de hechos Target tiene tres columnas: TargetYear, Category y TargetQuantity.

Dado que la tabla Target almacena datos en un nivel superior a las tablas de dimensiones, no se puede crear una relación uno a varios. Bueno, es cierto para una sola de las relaciones. Vamos a explorar cómo la tabla Target puede estar relacionada con las tablas de dimensiones.

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

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

Sugerencia

Cuando se almacenan hechos con una granularidad de tiempo superior al día, establezca el tipo de datos de la columna en Fecha (o Número entero si utiliza claves de fecha). En la columna, almacene un valor que represente el primer día del período de tiempo. Por ejemplo, un período de año se registra como el 1 de enero del año y un período de mes se registra como el primer día de ese mes.

Sin embargo, se debe tener cuidado para asegurarse de que los filtros de nivel de mes o fecha produzcan 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 la siguiente visualización de matriz se muestra lo que sucede cuando el usuario del informe desglosa un año en sus meses. El objeto visual resume la columna TargetQuantity. (La opción Mostrar elementos sin datos se ha habilitado para las filas de matriz).

Diagrama que muestra un objeto visual de matriz que revela la cantidad objetivo del año 2020 como 270. Genera valores incorrectos por fecha.

Para evitar este comportamiento, se recomienda controlar el resumen de los datos de hechos mediante medidas. Una manera de controlar el resumen es devolver BLANK cuando se consultan períodos de tiempo de nivel inferior. Otra manera, definida con algunos DAX sofisticados, es distribuir valores en períodos de tiempo de menor nivel.

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

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

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

Diagrama que muestra dos visualizaciones de matriz. La primera revela el objetivo del primer mes de 2020 como 270, y el segundo se muestra 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 dimensión con una tabla de hechos (y en un nivel de detalle más alto que la tabla de dimensión) se requiere otro enfoque de diseño.

Las columnas Category (de las tablas Product y Target) contienen valores duplicados. Por lo tanto, no hay "un" lado 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 filtros en una sola dirección, desde la tabla de dimensiones hasta la tabla de hechos.

Diagrama en el que se muestra un modelo de las tablas Target y Product. Una relación de varios a varios relaciona las dos tablas.

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

Diagrama en el que se muestra un modelo que contiene dos tablas: Target y Product. Una relación de varios a varios relaciona las dos columnas Category.

En la tabla Target, hay cuatro filas: dos filas para cada año objetivo (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 una pertenece a la categoría de accesorios. Uno de los colores de la ropa es verde, y los dos restantes son azules.

Una tabla visual agrupada por la columna Category de la tabla Product genera el siguiente resultado. Sin embargo, esta visualización genera el resultado correcto. Ahora vamos a considerar lo que sucede cuando la columna Color de la tabla Product se usa para agrupar la cantidad objetivo.

Diagrama que muestra dos objetos visuales de tabla. El primero agrupa por Categoría y el segundo por Color. El segundo objeto visual genera un resultado incorrecto.

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

Un filtro de la columna Color de la tabla Product da como resultado dos filas. Una de las filas es para la categoría Ropa, y la otra es para la categoría Accesorios. 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 anteriormente, se recomienda controlar el resumen de los datos de hechos mediante medidas.

Tenga en cuenta la siguiente definición de medida. Tenga en cuenta que todas las columnas de la tabla Product que están debajo del nivel de categoría son evaluadas teniendo en cuenta 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 se usa la medida Target Quantity. Muestra que todas las cantidades de destino de color están en blanco.

Diagrama que muestra dos objetos visuales de tabla. Los primeros grupos por Categoría y el segundo por Color. El segundo objeto visual genera un resultado correcto en blanco.

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

Diagrama que muestra un modelo con las tablas Date y Target relacionadas por una relación de uno a varios.

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

Cuando necesite relacionar una tabla de dimensiones con una tabla de hechos y la tabla de hechos almacena filas en un nivel más alto que las filas de la tabla de dimensiones, siga estas instrucciones:

  • Para fechas de hechos con un nivel de detalle más alto
    • En la tabla 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 hechos.
  • Para otros hechos con un nivel de detalle más alto
    • Cree una relación de varios a varios entre la tabla de dimensiones y la tabla de hechos.
  • Para ambos tipos
    • Resumen de controles con lógica de medida: devuelve BLANK cuando se usan columnas de dimensión de nivel inferior para filtrar o agrupar.
    • Ocultar columnas resumibles en la tabla de hechos, lo que garantiza que solo se puedan usar métricas para resumir la tabla de hechos.

Contenido relacionado

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