Orientação de relacionamento muitos-para-muitos

Este artigo destina-se a você como um modelador de dados que trabalha com o Power BI Desktop. Ele descreve três diferentes cenários de modelagem muitos-para-muitos. Este recurso também fornece orientações sobre como projetar com êxito para eles nos seus modelos.

Existem três cenários diferentes de muitos-a-muitos. Eles podem ocorrer quando você é obrigado a:

• Relacionar tabelas de duas dimensões
• Relacione duas tabelas de fatos
• Relacionar tabelas de fatos com nível de detalhe mais alto, quando a tabela de fatos armazena linhas num nível de detalhe mais alto do que as linhas da tabela de dimensões.

Relacionar muitas para muitas dimensões

O cenário clássico muitos-para-muitos relaciona 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 são comumente chamados de titulares de contas conjuntas.

Modelar essas entidades é simples. Uma tabela de dimensão armazena contas e outra tabela de dimensão armazena clientes. Como é característico das tabelas de dimensão, há uma coluna de identificador exclusivo (ID) em cada tabela. Para modelar a relação entre as duas tabelas, é necessária uma terceira tabela. Esta tabela é geralmente chamada de 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 identificação, é chamada de tabela de fatos sem fatos.

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

A primeira tabela tem o nome 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. Não existem relações 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. Regista as transações da conta. A tabela de ponte e todas as colunas de identificador foram ocultadas.

Para ajudar a descrever como funciona a propagação do filtro de relacionamento, 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 Conta-01
  • AccountID 2 é para Conta-02
• A tabela Customer tem duas linhas:
  • CustomerID 91 é para Customer-91
  • CustomerID 92 é para Customer-92
• A tabela AccountCustomer tem três linhas:
  • AccountID 1 está associada a CustomerID91
  • AccountID 1 está associada a CustomerID92
  • AccountID 2 está associada ao CustomerID92
• A tabela Transaction tem três linhas:
  • Date 1 de janeiro de 2019, AccountID1, Amount100
  • Date 2 de fevereiro de 2019, AccountID2, Amount200
  • Date março 3, 2019, 1 AccountID, Amount-25

Vamos ver o que acontece quando se consulta o modelo.

Na imagem a seguir, há dois visuais de tabela que resumem a coluna Amount da tabela Transaction. Os primeiros grupos visuais são organizados por conta, e, portanto, a soma das colunas Amount representa o saldo da conta . O segundo gráfico agrupa por cliente e, assim, a soma das colunas Amount representa o saldo do cliente.

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

• Conta-01 tem um saldo de 75.
• Conta-02 valor do saldo é 200.
• O total é de 275.

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

• Customer-91 valor do saldo é 275.
• Customer-92 montante do saldo é 275.
• O total é de 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 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 com a visualização do saldo do cliente. Cada cliente neste visual tem o mesmo saldo que o saldo total. Este resultado só poderia ser correto se cada cliente fosse titular conjunto de todas as contas. Não é o caso neste exemplo. Há um problema, e ele está relacionado à propagação do filtro. Os filtros não estão a passar para a tabela Transaction.

Se você seguir as instruções do filtro de relacionamento 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 alteração no visual do Saldo da Conta.

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

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

O visual Saldo do Cliente agora exibe um resultado correto. Siga as instruções do filtro por si mesmo e veja como os saldos dos clientes foram calculados. Além disso, entenda que o total visual significa todos os clientes.

Alguém não familiarizado com as relações do 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 à sua pergunta está em compreender a relação muitos-para-muitos. Cada saldo de cliente pode representar a adição de vários saldos de conta e, portanto, os saldos de clientes são não aditivos.

Relacionar orientações de muitas para muitas dimensões

Quando você tiver uma relação muitos-para-muitos entre tabelas de dimensão, siga estas orientações:

• Adicione cada entidade relacionada do tipo 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 do filtro prossiga para a tabela de fatos.
• Quando não for apropriado ter valores de ID ausentes, desative 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 para a geração de relatórios).
• Oculte quaisquer colunas de ID que não sejam adequadas para relatórios (por exemplo, quando as colunas armazenam valores de chave substituta).
• Se fizer sentido deixar uma coluna de ID visível, certifique-se de que ela esteja no lado "um" da relação — oculte sempre a coluna do lado "muitos". Isso porque os filtros aplicados a um slide resultam em um melhor desempenho dos filtros.
• Para evitar confusão ou má interpretação, 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ões muitos-para-muitos diretamente. Esta abordagem de design requer a criação de uma relação com uma cardinalidade muitos-para-muitos. Conceitualmente isso pode ser alcançado, mas implica que as colunas relacionadas podem conter valores duplicados. É uma prática de design bem aceita, no entanto, que as tabelas de dimensão tenham uma coluna ID. As tabelas de dimensão devem sempre usar a coluna ID como o lado "um" de uma relação.

Relacione factos em relações muitos-para-muitos

Um tipo de cenário diferente de muitos para muitos envolve estabelecer a relação entre duas tabelas de fatos. Duas tabelas de fatos podem ser relacionadas diretamente. Esta técnica de design pode ser útil para uma exploração de dados rápida e simples. No entanto, e para ser claro, geralmente não recomendamos essa abordagem de design. Explicaremos o porquê 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 ordens de venda. As linhas na tabela Fulfillment representam itens de ordem que foram enviados. Uma relação muitos-para-muitos conecta as colunas OrderID em ambas as tabelas, com a propagação do filtro ocorrendo apenas a partir da tabela Order (isto é, a tabela Order filtra a tabela Fulfillment).

A cardinalidade da relação é definida como Many-to-many para dar suporte ao armazenamento de valores de coluna OrderID duplicados em ambas as tabelas. Na tabela Order, valores de ID duplicados podem existir porque um pedido 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 pedidos podem ser atendidas por muitas remessas.

Vamos agora dar uma olhada nas linhas da tabela. Na tabela Fulfillment, observe que as linhas de pedidos podem ser atendidas por várias remessas. (A ausência de uma linha de ordem significa que a ordem ainda não foi cumprida.)

Os detalhes das linhas para as duas tabelas são descritos na seguinte lista com marcadores.

• A tabela Order tem cinco linhas:
  • OrderDate janeiro 1 2019, OrderID1, OrderLine1, ProductIDProd-A, OrderQuantity5, Sales50
  • OrderDate 1 de janeiro de 2019, OrderID1, OrderLine2, ProductIDProd-B, OrderQuantity10, Sales80
  • OrderDate 2 de fevereiro de 2019, OrderID2, OrderLine1, ProductIDProd-B, OrderQuantity5, Sales40
  • OrderDate 2 fevereiro 2019, OrderID2, OrderLine2, ProductIDProd-C, OrderQuantity1, Sales20
  • OrderDate 3 de março 2019, 3 OrderID, OrderLine1, ProductIDProd-C, 5 OrderQuantity, Sales100
• A tabela Fulfillment tem quatro linhas:
  • FulfillmentDate janeiro 1 2019, FulfillmentID50, OrderID1, OrderLine1, FulfillmentQuantity2
  • FulfillmentDate fevereiro 2 2019, FulfillmentID51, OrderID2, OrderLine1, FulfillmentQuantity5
  • FulfillmentDate 2 de fevereiro de 2019, FulfillmentID52, OrderID1, OrderLine1, 3 FulfillmentQuantity
  • FulfillmentDate janeiro 1 2019, FulfillmentID53, OrderID1, OrderLine2, FulfillmentQuantity10

Vamos ver o que acontece quando o modelo é consultado. Aqui está uma representação visual da tabela comparando as quantidades de pedidos e atendimento pela coluna OrderID da tabela Order.

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

Relacione a orientação de muitos para muitos fatos

Geralmente, não recomendamos relacionar diretamente duas tabelas de factos usando cardinalidade muitos-para-muitos. O principal motivo é que o modelo não fornecerá flexibilidade nas formas como as visualizações do relatório filtram ou agrupam. No exemplo, é apenas possível aos elementos visuais filtrar ou agrupar pela coluna OrderID da tabela Order. Outra razão prende-se com a qualidade dos seus dados. Se seus dados tiverem problemas de integridade, é possível que algumas linhas sejam omitidas durante a consulta devido à natureza da cardinalidade de muitos para homem e relações limitadas.

Em vez de relacionar tabelas de factos diretamente, recomendamos que implemente-se um esquema em estrela. Isso significa que você adiciona tabelas de dimensão. Essas tabelas de dimensão então se relacionam com as tabelas de fatos usando relações um-para-muitos. Essa abordagem de design é robusta, pois oferece opções flexíveis de relatórios de forma eficiente. Ele permite filtrar ou agrupar usando qualquer uma das colunas da tabela de dimensões 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 mesas extras: OrderLine, OrderDate, Producte FulfillmentDate.
• As quatro tabelas extras são todas tabelas de dimensão onde as relações um-para-muitos as relacionam com as tabelas de fatos.
• A tabela OrderLine contém a coluna OrderLineID, que armazena o valor OrderID multiplicado por 100, mais o valor da coluna OrderLine — uma ID para cada linha de ordem.
• 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 OrderDate e ProductID colunas.
• A tabela FulfillmentDate tem uma relação apenas com a tabela Fulfillment.
• Todas as colunas ID estão ocultas.

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

• Os visuais de relatório podem filtrar ou agrupar por qualquer coluna visível das tabelas de dimensão.
• Os visuais do relatório podem resumir qualquer coluna visível das tabelas de fatos.
• Os filtros aplicados às tabelas OrderLine, OrderDateou Product propagam-se para ambas as tabelas de factos.
• Todos os relacionamentos são um-para-muitos, e cada relacionamento é um relacionamento regular. Os problemas de integridade de dados não serão mascarados. Para obter mais informações sobre avaliação de relacionamentos, consulte Relacionamentos de modelo no Power BI Desktop.

Relacione fatos de nível de detalhe superior

Este 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 as relações um-para-muitos relacionam-se cada uma com a tabela de fatos Sales. Até agora, representa um bom design de esquema de estrela. A tabela Target, no entanto, ainda não está relacionada com as 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 de 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, não é possível criar uma relação um-para-muitos. Bem, isso é verdade para apenas um dos relacionamentos. Vamos explorar como a tabela Target pode ser relacionada às tabelas de dimensão.

Relacione períodos de tempo de maior granularidade

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

No entanto, é necessário ter cuidado para garantir que os filtros de nível de mês ou data produzam um resultado significativo. Sem qualquer lógica de cálculo especial, os visuais do relatório podem informar que as datas-alvo são literalmente o primeiro dia de cada ano. Todos os outros dias — e todos os meses, exceto janeiro — resumirão a quantidade-alvo como BLANK.

O visual da matriz a seguir mostra o que acontece quando o utilizador do relatório aprofunda de um ano nos seus meses. O gráfico resume a coluna TargetQuantity. (A opção Mostrar itens sem dados foi habilitada para as linhas da matriz.)

Para evitar esse comportamento, recomendamos que você controle a sumarização de seus dados de fato usando medidas. Uma maneira de controlar o resumo é retornar BLANK quando períodos de tempo de nível inferior são consultados. Outra maneira — definida com algum DAX sofisticado — é distribuir valores em períodos de tempo de nível inferior.

Considere a seguinte definição de medida que usa a função ISFILTERED DAX. 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 seguinte visual de matriz utiliza a medida Target Quantity. Mostra que todas as quantidades-alvo mensais estão EM BRANCO.

Relacionar grãos mais altos (sem data)

Uma abordagem de design diferente é necessária ao relacionar uma coluna sem data de uma tabela de dimensões a uma tabela de fatos (e ela está em um grão mais alto do que a tabela de dimensões).

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

Vamos agora dar uma olhada nas linhas da tabela.

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

Um agrupamento visual de tabela pela coluna Category da tabela Product produz o seguinte resultado. No entanto, este visual produz o resultado correto. Vamos agora considerar o que acontece quando a coluna Color da tabela Product é usada para agrupar a quantidade de destino.

O visual produz uma deturpação dos dados. O que está a acontecer aqui?

Um filtro na coluna Color da tabela Product resulta em duas linhas. Uma das linhas é para a categoria Vestuário, 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 é utilizada por produtos de duas categorias, essas categorias servem para filtrar os alvos.

Para evitar esse comportamento, conforme descrito anteriormente, recomendamos que você controle a sumarização de seus dados de fato usando medidas.

Considere a seguinte definição de medida. Observe que todas as colunas da tabela Product que estão abaixo do nível de categoria são testadas em relação aos filtros.

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

A medida Target Quantity é utilizada no visual da tabela a seguir. Isso mostra que todas as quantidades alvo de cor estão EM BRANCO.

O design final do modelo tem a seguinte aparência.

Relacione orientações sobre fatos de grãos de alta qualidade

Quando se precisar relacionar uma tabela de dimensões a uma tabela de fatos e a tabela de fatos armazenar linhas em um grão mais alto do que as linhas da tabela de dimensão, siga essas diretrizes:

• Para datas posteriores relacionadas com fatos sobre grãos
  • 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 detalhes mais precisos
  • Crie uma relação muitos-para-muitos entre a tabela de dimensões e a tabela de factos.
• Para ambos os tipos
  • Controle a sumarização com lógica de medida — retorna BLANK quando colunas de dimensão de nível inferior são usadas para filtrar ou agrupar.
  • Oculte colunas resumidas da tabela de fatos — isso garante que apenas 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:

• Relações de modelo no Power BI Desktop
• Compreender o esquema em estrela e a importância para o Power BI
• Orientação de solução de problemas de relacionamento
• Dúvidas? Tente perguntar à Comunidade Fabric
• Sugestões? Contribua com ideias para melhorar o Fabric