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.
