Tudo sobre Relacionamentos de Muitos-para-Muitos no Power BI

E aí, preparado para mais um artigo?! Hoje falaremos do polêmico relacionamento many to many e darei dicas de como não cair em armadilhas!

Suponha o seguinte cenário: Temos uma tabela fato de vendas e duas tabelas dimensão: Produto e Categoria.

O relacionamento entre Produto e Vendas, é de Um (1) para Muitos (*), certo?! Até aí, tranquilo. Mas e o relacionamento entre Categoria e Produtos, onde eu tenho mais de um produto associado a mais de uma categoria, como seria?! Seria Um para Muitos (1:M) também?!

Durante a live#42, o Ivanel e o Luciano responderam corretamente:

Precisamos de uma Bridge table ou “tabela intermediária”!

Você pode estar lembrando que em outras lives eu dizia para você levar as informações da tabela Categoria para a tabela Produto, ou seja, sugeria o merge entre tabelas dimensões porém, naquelas ocasiões eu não tinha mais de uma categoria para cada produto e vice versa como temos neste nosso exemplo.

No exemplo de hoje, não devemos fazer o merge entre as duas tabelas (com base no ID Produto, por exemplo) porque isso vai duplicar as linhas da tabela Produto. Veja o que aconteceria se adicionássemos a Categoria na tabela Produto utilizando o ID Produto como chave (2 linhas viraram 4):

No final de 2018 a Microsoft adicionou a opção de relacionamento: Muitos para Muitos. Mas muita gente ainda não sente total segurança para utilizá-la. Vou te mostrar exemplos para clarear as coisas!

Base de dados inicial

Imagine que a gente tenha uma tabela fMetas com as seguintes colunas:

Agora, dê uma olhada na Tabela fVendas:

Temos também as seguintes tabelas:

Diferentes granularidades entre dimensões e fatos

Agora, vamos analisar dois casos em que temos granularidades distintas entre dimensões e fatos.

Tabela fato de metas anuais

Nosso primeiro desafio será montar uma matriz com o Valor Venda e Valor Meta por ano.

Primeiro, criaremos essas medidas básicas:

Total Vendas =
SUM ( fVendas[Valor Venda] )

Total Meta =
SUM ( fMetas[Valor Meta] )

Se você só colocar essas duas medidas numa matriz, verá o seguinte:

Ué?! Por que Total Meta ficou igual em todas as linhas?! A nossa medida não estava com a função SUM?!

Isso acontece porque ainda não fizemos relacionamento nenhum com a fMetas e na matriz utilizamos a coluna Ano da tabela dCalendario (que só está relacionada com a fVendas):

Bom, então basta relacionar a dCalendario com a fMetas, certo?!

A tabela fMetas tem uma coluna Ano (número inteiro), ou seja, não é uma coluna de Data (dd/mm/aaaa). Podemos resolver isso facilmente no Power Query, adicionando uma Coluna com a data referente com base na coluna Ano informada, veja:

Agora, basta preencher a primeira linha que o Power BI vai automaticamente identificar o resto dos resultados:

Conforme mostrei acima, basta:
1. Selecionar a coluna Ano
2. Ir para a guia Add Column
3. Clicar em Column From Examples → From Selection
4. Preencher a coluna nova com valores esperados, como “01/01/2017” na primeira linha e por fim, apertar OK

Após adicionar essa coluna com a Data propriamente dita, podemos excluir a coluna Ano sem problemas. A partir de agora, basta voltar na modelagem e relacionar essa coluna com Data da tabela dCalendario:

Bom, voltando na matriz anterior, vemos que os valores estão ok agora:

Vamos ao próximo desafio!

Tabela fato de metas por país

Sabemos que se adicionarmos a coluna País (dCliente) nesta matriz, os valores ficarão repetidos assim:

O motivo desse valor Total metas estar igual independente do país é o mesmo de antes: não relacionamos dCliente com fMetas!

Mas, e agora?! Como relacionar essas tabelas corretamente?!

Repare que na tabela dClientes a única coluna que não pode ter repetição é ID Cliente. Ou seja, a coluna País tem vários valores repetidos. Da mesma forma temos a fMetas, com a coluna País também repetidos. A cardinalidade deve ser então “Muitos para Muitos”. Mas…Em qual sentido ?!

Lembre-se: A direção do filtro deve ser sempre da dimensão para a fato.

Assim você vai conseguir fazer com que a tabela dCliente filtre a tabela fMetas.

Veja como ficou:

Dica:
O objetivo do relacionamento é transmitir o filtro.

Voltando na matriz, veja como ficou:

Então, tenha em mente o seguinte: quando você quer “tratar” diferentes granularidades entre dimensão e fato use a cardinalidade M:M através de um relacionamento entre as tabelas essas tabelas.

Ah, também poderíamos ter realizado um relacionamento virtual ao invés desse físico que fizemos entre a dCliente e fMetas utilizando funções específicas em DAX. Porém, tenha em mente que o relacionamento físico é sempre preferível por ser mais performático.

Vamos supor agora, que você queira analisar a meta por cliente. Vamos mudar os campos da matriz e deixar apenas Cliente e Total Meta:

Repare que quando ordeno o valor Total Meta do maior para o menor na matriz, aparecem vários valores com 148 mil e pouco. Será que esse número faz sentido?! Nós não temos meta por cliente, certo?! Além disso a linha Total está incorreta também!

Isso ocorre porque relacionamos a coluna País da tabela fMetas com a dCliente, então você só vai conseguir analisar por essa perspectiva considerando a forma que fizemos.

Vamos observar mais um cenário: Total Meta por Tipo Cliente. Vamos ‘debugar’ esse valor Total Meta quando o Tipo Cliente for igual a Online.

Primeiro vá para a tabela dCliente e filtre Tipo Cliente para obter apenas linhas com “Online”:

Quando fizer isso, notará que a coluna País só possuirá valores: Germany e France.

Agora, filtre a coluna País da tabela fMetas e mostre apenas Germany e France:

Comparando lado a lado, temos:

Coloquei este cartão apenas para mostrar que a soma do Valor Meta para France e Germany da tabela esquerda é igual a essa linha destacada da tabela da direita (Online). Porém, note que a soma de todos os itens da tabela da direita não resulta no Total.

Então, ao usar o relacionamento Muitos para Muitos lembre-se: a partir desse momento sua medida pode não ser mais aditiva.

Bom, bora para o próximo Cenário!

Bridge Tables

Imagine um cenário onde há uma lista de perfumes e perfumistas:

Este é um típico exemplo de relacionamento entre duas dimensões porque podemos ter um perfume sendo produzido por mais de um perfumista e cada perfumista pode produzir mais de um perfume. Ou seja, para relacionar a venda de um perfume (ID Produto) com o perfumista (ID Pessoa), precisaríamos de uma Bridge Table que tivesse esse de-para entre produto e pessoa.

Na literatura você também encontrará o nome de Factless fact table (tabela fato sem fato).

Voltando ao Power BI, temos esse mesmo exemplo. Observe que a nossa Bridge table será a tabela ProdutoPessoa:

O relacionamento ficou assim:

Se fizermos uma matriz com os valores de vendas por pessoa, qual seria o resultado? Teríamos aquele mesmo problema! Valores iguais! Isso ocorre porque dPessoa filtra ProdutoPessoa mas ProdutoPessoa não filtra dProduto (verifique as setinhas da figura acima).

Para resolver isso, precisamos alterar a direção do relacionamento entre a tabela ProdutoPessoa e a tabela dProduto para “ambos” os sentidos. Essa é uma forma segura de fazer o relacionamento bidirecional porque sabemos que não usaremos a Bridge Table para nada além disso. Isso evitará riscos do seu modelo ter ambiguidade.

Você deve alterar ali em Cross filter direction para Both:

Veja o resultado da matriz com Total Vendas por Pessoa:

Durante a live#42, nós somamos todas as linhas da coluna Total Vendas e percebemos que ela não é igual a linha totalizadora dessa tabela acima. E isso está certo! Não foi aquela pessoa que vendeu o perfume, ela é o “perfumista”, lembra?! E podem ter vários perfumistas para cada perfume. Beleza?! Esse valor não é aditivo, logo essa linha Total não faz sentido estar nessa tabela, ok?! Basta removê-lo aqui:


Figura 25: Removendo o Total

Digamos que você esteja inseguro de usar esse relacionamento M:M. Podemos fazer isso de outra forma! Deixaremos a direção do filtro como “Single”:

Figura 26: Direção do filtro cruzado

Não se assuste! A tabela vai ficar com um valor fixo de 252.540,16 em todas as linhas! Precisaremos criar uma medida para corrigir isso:

Total Vendas por Pessoa =
CALCULATE (
[Total Vendas],
CROSSFILTER ( ProdutoPessoa[ID Produto], dProduto[ID Produto], BOTH )
)

O objetivo aqui é ativar o modo bidirecional diretamente na medida, mais seguro porque você sabe onde exatamente está sendo aplicado o M:M entre dimensões.

Figura 27: Usando Crossfilter

Bom, pessoal, é isso!

Se tiver dúvida, comente aqui embaixo! E não se esqueça: toda terça-feira tem live nova em Português e nas quintas: in english!

Abraços,
Leonardo.