Diretrizes de relação muitos para muitos

Este artigo tem como destino você como um modelador de dados que trabalha com o Power BI Desktop. Ele descreve três diferentes cenários de modelagem de muitos-para-muitos. Ele também fornece diretrizes sobre como criar com êxito para esses cenários em seus modelos.

Nota

Uma introdução às relações de modelo não é abordada neste artigo. Se você não estiver completamente familiarizado com relacionamentos, suas propriedades ou como configurá-los, recomendamos que você leia primeiro o artigo Model relationships do Power BI Desktop.

Também é importante que você compreenda o design do esquema em estrela. Para obter mais informações, confira Entender o esquema em estrela e a importância dele para o Power BI.

Existem três diferentes cenários muitos para muitos. Elas podem ocorrer quando você precisa:

• Relacionar duas tabelas de dimensão
• Relacionar duas tabelas de fatos
• Relacionar tabelas de fatos com granularidade mais alta, quando a tabela de fatos armazena linhas em uma granularidade mais alta em relação às linhas da tabela de dimensão

Relacionar dimensões muitos para muitos

O cenário clássico de muitos-a-muitos envolve duas entidades, por exemplo, clientes bancários e contas bancárias. Considere que os clientes podem ter várias contas e as contas podem ter vários clientes. Quando uma conta tem vários clientes, eles costumam ser chamados de titulares de conta conjunta.

A modelagem dessas entidades é simples. Uma tabela de dimensões armazena contas e outra tabela de dimensões armazena os clientes. Como é característica das tabelas de dimensão, há uma coluna ID (identificador exclusivo) em cada tabela. Para modelar a relação entre as duas tabelas, uma terceira tabela é necessária. Essa tabela é conhecida como tabela de ponte. Neste exemplo, sua finalidade é armazenar uma linha para cada associação de conta de cliente. Curiosamente, quando esta tabela contém apenas colunas de identificador, ela é chamada de tabela sem fatos.

Aqui está um diagrama simplista das três tabelas de modelo.

A primeira tabela é denominada Accounte contém duas colunas: AccountID e Account. A segunda tabela é denominada AccountCustomere contém duas colunas: AccountID e CustomerID. A terceira tabela é denominada Customere contém duas colunas: CustomerID e Customer. As relações não existem entre nenhuma das tabelas.

Duas relações um-para-muitos são adicionadas para relacionar as tabelas. Aqui está um diagrama de modelo atualizado das tabelas relacionadas. Uma tabela de fatos chamada Transaction foi adicionada. Ele registra transações de conta. A tabela de ponte e todas as colunas do identificador foram ocultadas.

Para ajudar a descrever como a propagação do filtro de relação funciona, o diagrama de modelo foi modificado para revelar as linhas da tabela.

Os detalhes das linhas das quatro tabelas são apresentados na seguinte lista com marcadores:

• A tabela Account tem duas linhas:
  • AccountID 1 é para Account-01
  • AccountID 2 é para Account-02
• A tabela Customer tem duas linhas:
  • CustomerID 91 é para Customer-91
  • CustomerID 92 é para Customer-92
• A tabela AccountCustomer tem três linhas:
  • AccountID1 está associado a CustomerID91
  • AccountID1 está associado ao CustomerID92
  • AccountID2 está associado ao CustomerID92
• A tabela Transaction tem três linhas:
  • Date January 1 2019, AccountID1, Amount100
  • Date February 2 2019, AccountID2, Amount200
  • Date March 3 2019, AccountID1, Amount-25

Vamos conferir o que acontece quando o modelo é consultado.

Na imagem a seguir, há dois visuais de tabela que resumem a coluna Amount da tabela Transaction. O primeiro visual é agrupado por conta e, portanto, a soma das colunas Amount representa o saldo da conta. O segundo grupo visual é por cliente e, portanto, a soma das colunas Amount representa o saldo do cliente .

O primeiro visual de tabela (Saldo da Conta) tem duas colunas: Account e Amount. Ele exibe o seguinte resultado:

• O valor do saldo da Account-01 é 75.
• O valor do saldo da Account-02 é 200.
• O total é 275.

O segundo visual da tabela (Customer Balance) tem duas colunas: Customer e Amount. Ele exibe o seguinte resultado:

• O valor do saldo de Customer-91 é 275.
• O valor do saldo de Customer-92 é 275.
• O total é 275.

Uma rápida olhada nas linhas da tabela e no visual do Saldo da Conta revela que o resultado está correto, para cada conta e o valor total. Isso ocorre porque cada agrupamento de contas resulta em uma propagação de filtro para a tabela Transaction dessa conta.

No entanto, algo não parece correto no visual Customer Balance. Cada cliente nesse visual tem o mesmo saldo que o saldo total. Esse resultado só poderia estar correto se cada cliente fosse titular conjunto de todas as contas. Esse não é o caso neste exemplo. Há um problema e está relacionado à propagação de filtro. Os filtros não estão fluindo até a tabela Transaction.

Se você seguir as instruções do filtro de relação da tabela Customer para a tabela Transaction, poderá determinar que a relação entre as tabelas Account e AccountCustomer está se propagando na direção errada. A direção do filtro para essa relação deve ser definida como Both.

Como esperado, não houve nenhuma alteração no visual do Saldo de Contas.

O visual do Saldo do Cliente, no entanto, agora exibe o seguinte resultado:

• O valor do saldo de Customer-91 é 75.
• O valor do saldo de Customer-92 é 275.
• O total é 275.

O visual do Saldo de Clientes agora exibe um resultado correto. Siga as instruções de filtro por conta própria e descubra como os saldos do cliente foram calculados. Além disso, entenda que o visual total significa todos os clientes.

Alguém que não esteja familiarizado com as relações de modelo pode concluir que o resultado está incorreto. Eles podem perguntar: Por que o saldo total para Customer-91 e Customer-92 não é igual a 350 (75 + 275)?

A resposta a essa pergunta está na compreensão da relação muitos para muitos. Cada saldo do cliente pode representar a adição de vários saldos de conta e, portanto, os saldos do cliente são não aditivos.

Diretrizes para relacionar dimensões muitos para muitos

Quando você tiver uma relação muitos para muitos entre tabelas de dimensão, siga estas diretrizes:

• Adicione cada entidade relacionada muitos para muitos como uma tabela de modelo, garantindo que ela tenha uma coluna de ID.
• Adicione uma tabela de ponte para armazenar entidades associadas.
• Crie relações um para muitos entre as três tabelas.
• Defina uma relação bidirecional para permitir que a propagação de filtro continue para a tabela de fatos.
• Quando não for apropriado ter valores de ID ausentes, desabilite a propriedade Is Nullable– a atualização de dados falhará quando os valores ausentes forem originados.
• Oculte a tabela de ponte (a menos que contenha outras colunas ou medidas necessárias nos relatórios).
• Oculte todas as colunas de ID que não são adequadas para relatórios (por exemplo, quando as colunas armazenam valores de chave alternativos).
• Se fizer sentido deixar uma coluna de ID visível, verifique se ela está no lado "um" da relação. Sempre oculte a coluna do lado "muitos". Isso ocorre porque os filtros aplicados ao primeiro slide resultam em um melhor desempenho dos filtros.
• Para evitar confusão ou interpretação incorreta, comunique explicações aos usuários do relatório, você pode adicionar descrições com caixas de texto ou dicas de ferramentas de cabeçalho visual.

Não recomendamos que você relacione tabelas de dimensão muitos para muitos diretamente. Essa abordagem de design requer a configuração de uma relação com uma cardinalidade muitos para muitos. Conceitualmente, ele pode ser alcançado, mas implica que as colunas relacionadas podem conter valores duplicados. No entanto, é uma prática de design bem aceita que as tabelas de dimensão tenham uma coluna de ID. As tabelas de dimensão sempre devem usar a coluna de ID como o lado "um" de uma relação.

Relacionar fatos muitos para muitos

Um cenário do tipo muitos para muitos diferente envolve a relação de duas tabelas de fatos. Duas tabelas de fatos podem ser relacionadas diretamente. Essa técnica de design pode ser útil para exploração rápida e simples de dados. No entanto, e para ser claro, geralmente não recomendamos essa abordagem de design. Explicaremos por que mais adiante nesta seção.

Vamos considerar um exemplo que envolve duas tabelas de fatos: Order e Fulfillment. A tabela Order contém uma linha por linha de ordem e a tabela Fulfillment pode conter zero ou mais linhas por linha de ordem. As linhas na tabela Order representam pedidos de vendas. As linhas na tabela Fulfillment representam itens de pedido que foram enviados. Uma relação muitos para muitos relaciona as colunas OrderID em cada tabela, com a propagação de filtro ocorrendo apenas da tabela Order (isso significa que a tabela Order filtra a tabela Fulfillment).

A cardinalidade da relação é definida para Many-to-many para dar suporte ao armazenamento de valores duplicados da coluna OrderID nas duas tabelas. Na tabela Order, valores de ID duplicados podem existir porque uma ordem pode ter várias linhas. Na tabela Fulfillment, valores de ID duplicados podem existir porque os pedidos podem ter várias linhas e as linhas de pedido podem ser atendidas por muitas remessas.

Agora, vamos conferir as linhas da tabela. Na tabela Fulfillment, observe que as linhas de pedido podem ser atendidas por várias remessas. (A ausência de uma linha de pedido significa que a ordem ainda não foi atendida.)

Os detalhes de linha para as duas tabelas são descritos na seguinte lista com marcadores:

• A tabela Order tem cinco linhas:
  • OrderDate January 1 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate January 1 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate February 2 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate February 2 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate March 3 2019, OrderID3, OrderLine1, ProductIDProd-C, OrderQuantity5, Sales100
• A tabela Fulfillment tem quatro linhas:
  • FulfillmentDate January 1 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate February 2 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate February 2 2019, FulfillmentID52, OrderID1, OrderLine1, FulfillmentQuantity3
  • FulfillmentDate January 1 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Vamos ver o que acontece quando o modelo é questionado. Este é um visual de tabela que compara as quantidades de pedidos e de processamento na coluna OrderID da tabela Order.

O visual fornece um resultado preciso. No entanto, a utilidade do modelo é limitada porque você só pode filtrar ou agrupar pela tabela OrderOrderID coluna.

Diretrizes para relacionar fatos muitos para muitos

Geralmente, não recomendamos que você relacione duas tabelas de fatos diretamente usando cardinalidade de muitos para muitos. O principal motivo é porque o modelo não fornecerá flexibilidade na maneira como seus visuais de relatório filtram ou agrupam. No exemplo, só é possível que os visuais filtrem ou agrupem pela coluna OrderID da tabela Order. Outro motivo está relacionado à qualidade dos seus dados. Se seus dados têm problemas de integridade, é possível que algumas linhas possam ser omitidas durante a consulta devido à cardinalidade muitos para muitos e a relações limitadas.

Em vez de relacionar tabelas de fatos diretamente, recomendamos que você implemente um esquema em estrela. Isso significa que você adiciona tabelas de dimensão. Essas tabelas de dimensão se relacionam com as tabelas de fatos usando relações um para muitos. Essa abordagem de design é robusta, pois fornece com eficiência opções de relatórios flexíveis. Ele permite filtrar ou agrupar usando qualquer uma das colunas da tabela de dimensão e resumir colunas de qualquer tabela de fatos relacionada.

Vamos considerar uma solução melhor.

Observe as seguintes alterações de design:

• O modelo agora tem quatro tabelas extras: OrderLine, OrderDate, Producte FulfillmentDate.
• As quatro tabelas extras são todas tabelas de dimensão em que relações um para muitos as relacionam às tabelas de fatos.
• A tabela OrderLine contém a coluna OrderLineID, que armazena o valor de OrderID multiplicado por 100, mais o valor da coluna OrderLine, uma ID para cada linha de pedido.
• As tabelas Order e Fulfillment agora contêm uma coluna OrderLineID e não contêm mais as colunas OrderID e OrderLine.
• A tabela Fulfillment agora contém colunas OrderDate e ProductID.
• A tabela FulfillmentDate tem uma relação apenas com a tabela Fulfillment.
• Todas as colunas de ID estão ocultas.

Tomar um tempo para adotar um design de esquema estrela oferece os seguintes benefícios:

• Os visuais do relatório podem filtrar ou agrupar com base em qualquer coluna visível das tabelas de dimensão.
• Os visuais de relatório podem resumir qualquer coluna visível das tabelas de fatos.
• Os filtros aplicados às tabelas OrderLine, OrderDateou Product são propagados para ambas as tabelas de fatos.
• Todas as relações são um para muitos, e cada relação é uma relação regular. Problemas de integridade de dados não serão mascarados. Para obter mais informações sobre avaliação de relação, consulte Relações de modelo no Power BI Desktop.

Relacionar fatos com granularidade mais alta

Esse cenário muitos para muitos é muito diferente dos outros dois já descritos neste artigo.

Vamos considerar um exemplo envolvendo quatro tabelas: Date, Sales, Producte Target. As tabelas Date e Product são tabelas de dimensão e relações um para muitos se relacionam cada uma com a tabela de fatos Sales. Até agora, isso representa um bom design de esquema em estrela. A tabela Target, no entanto, ainda não está relacionada às outras tabelas.

A tabela Target contém três colunas: Category, TargetQuantitye TargetYear. As linhas da tabela revelam uma granularidade de ano e categoria de produto. Em outras palavras, as metas , usadas para medir o desempenho das vendas, são definidas a cada ano para cada categoria de produto.

Como a tabela Target armazena dados em um nível mais alto do que as tabelas de dimensão, uma relação um-para-muitos não pode ser criada. Bem, é verdade para apenas um dos relacionamentos. Vamos explorar como a tabela de Target pode estar relacionada às tabelas de dimensão.

Relacionar períodos com granularidade mais alta

Uma relação entre as tabelas Date e Target deve ser uma relação um-para-muitos. Isso ocorre porque os valores da coluna TargetYear são datas. Neste exemplo, cada coluna TargetYear armazena a primeira data do ano de destino.

Dica

Ao armazenar fatos em uma granularidade de tempo maior do que o dia, defina o tipo de dados de coluna como Data (ou Número inteiro se você estiver usando chaves de data). Na coluna, armazene um valor que representa o primeiro dia do período de tempo. Por exemplo, um período de ano é registrado como 1º de janeiro do ano e um período de mês é registrado como o primeiro dia desse mês.

No entanto, deve-se tomar cuidado para garantir que os filtros de nível de mês ou de data produzam um resultado significativo. Sem nenhuma lógica especial de cálculo, os visuais do relatório podem mostrar que as datas de destino são exatamente o primeiro dia de cada ano. Todos os outros dias, bem como todos os meses, exceto janeiro, resumirão a quantidade de destino como BLANK.

O visual de matriz a seguir mostra o que acontece quando o usuário do relatório analisa detalhadamente um ano e os respectivos meses. O visual resume a coluna TargetQuantity. (A opção Mostrar itens sem dados foi habilitada para as linhas de matriz.)

Para evitar esse comportamento, recomendamos que você controle o resumo de seus dados de fatos usando medidas. Uma maneira de controlar o resumo é retornar BLANK quando períodos de tempo de nível inferior forem consultados. Outra maneira — definida com algumas fórmulas DAX sofisticadas — é distribuir valores em períodos de tempo de nível inferior.

Considere a definição de medida a seguir, que usa a função DAX ISFILTERED. Ele só retorna um valor quando as colunas Date e Month não são filtradas.

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

O visual de matriz a seguir usa a medida Target Quantity. Ele mostra que todas as quantidades de destino mensais equivalem a BLANK.

Relacionar granularidade mais alta (valor que não seja data)

Uma abordagem de design diferente é necessária ao relacionar uma coluna que não é de data de uma tabela de dimensão a uma tabela de fatos (e está em uma granularidade maior do que a tabela de dimensão).

As colunas Category (das tabelas Product e Target) contêm valores duplicados. Portanto, não existe um lado "um" em uma relação um-para-muitos. Nesse caso, você precisará criar uma relação muitos-para-muitos. A relação deve propagar filtros em uma única direção, da tabela de dimensões para a tabela de fatos.

Agora, vamos conferir as linhas da tabela.

Na tabela Target, há quatro linhas: duas linhas para cada ano-alvo (2019 e 2020) e duas categorias (Roupas e Acessórios). Na tabela Product, há três produtos. Dois pertencem à categoria de roupas, e um pertence à categoria de acessórios. Uma das cores de roupas é verde, e as outras duas são azuis.

Um agrupamento visual de tabela pela coluna Category da tabela Product produz o resultado a seguir. No entanto, esse visual produz o resultado correto. Agora vamos considerar o que acontece quando a coluna Color da tabela Product é usada para agrupar a quantidade-alvo.

O visual produz uma distorção dos dados. O que está acontecendo aqui?

Um filtro na coluna Color da tabela Product resulta em duas linhas. Uma das linhas é para a categoria Roupas, e a outra é para a categoria Acessórios. Esses dois valores de categoria são propagados como filtros para a tabela Target. Em outras palavras, como a cor azul é usada por produtos de duas categorias, essas categorias são usadas para filtrar os destinos.

Para evitar esse comportamento, conforme descrito anteriormente, recomendamos que você controle o resumo dos dados de fatos usando medidas.

Considere a definição de medida a seguir. Observe que todas as colunas de tabela Product abaixo do nível da categoria são testadas para filtros.

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

O visual da tabela a seguir usa a medida Target Quantity. Ele mostra que todas as quantidades de destino de cor equivalem a BLANK.

O design do modelo final é semelhante ao seguinte.

Diretrizes para relacionar fatos com granularidade mais alta

Quando você precisar relacionar uma tabela de dimensões a uma tabela de fatos e a tabela de fatos armazenar linhas em um nível de granularidade maior que as linhas da tabela de dimensão, siga estas diretrizes:

• Para datas de fatos com granularidade mais alta
  • Na tabela de fatos, armazene a primeira data do período de tempo.
  • Crie uma relação um-para-muitos entre a tabela de datas e a tabela de fatos.
• Para outros fatos com granularidade mais alta
  • Crie uma relação muitos para muitos entre a tabela de dimensões e a tabela de fatos.
• para ambos os tipos
  • Sumarização de controle com lógica de medida: retorna BLANK quando colunas de dimensão de nível inferior são usadas para filtrar ou agrupar.
  • Ocultar as colunas de tabela de fatos que podem ser resumidas: garante que somente medidas possam ser usadas para resumir a tabela de fatos.

Conteúdo relacionado

Para obter mais informações relacionadas a este artigo, confira os seguintes recursos:

• Modelar relações no Power BI Desktop
• Entenda o esquema estrela e a importância para o Power BI
• diretrizes de solução de problemas de relação
• Perguntas? Tente perguntar à Comunidade do Fabric
• Sugestões? Contribua com ideias para aprimorar o Fabric