Mesclar Consultas / Relacionamentos entre tabela fato e tabelas dimensão

[André Feliciano]

Olá boa noite, pessoal.

Tenho uma tabela fato de vendas com milhares de linhas, e informações das vendas produto a produto de cada nota fiscal, com a informação do código do representante comercial e o nome do representante.

Estou precisando relacionar/mesclar essa minha fato vendas com duas tabelas dimensões de granularidades diferentes, sendo uma tabela dimensão de Cidades por Representantes, onde minha chave única é o Código UF Municipio. Cada representante é responsável por diversos municípios, porém tenho cerca de 200 representantes a mais na tabela fato em comparação com a tabela dimensão Cidades Representantes.

A outra tabela dimensão é de Endereços, onde a minha chave única é o Código do Representante, porém, tenho cerca de 100 códigos de representantes a mais na tabela fato vendas, em comparação com a tabela dimensão Endereços.

A minha dificuldade é justamente em como eu poderia mesclar essas consultas? da maneira como está, se eu relacionar diretamente pelo código do representante da tabela dimensão Cidades Representantes filtrando a tabela fato, eu criaria um relacionamento de muitos para muitos, o que não é o adequado. E a minha chave única da tabela dimensão Cidades Representantes é o Código UF Municipio, que é uma coluna que eu não tenho na minha tabela fato.

Como na minha tabela dimensão Cidades Representantes não tenho todos os códigos de representantes que estão na minha tabela fato, pensei em talvez fazer uma referência da minha tabela fato pra conseguir buscar as informações de todos os representantes, removendo duplicadas. No entanto, me faltaria a informação das cidades e UF desses representantes não cadastrados na dimensão, tendo em vista que não tenho informações de municipio e UF na tabela fato.

Se eu relacionar a minha tabela dimensão Endereços com a fato vendas pelo código do cliente, conseguiria um relacionamento de 1:muitos, porém os cerca de 100 códigos a menos na dimensão me trariam um valor de faturamento que não está vinculado a nenhum representante / UF. 

O meu objetivo principal é criar uma análise de faturamento por representante em cada região de atuação dos mesmos, por Cidade, Estado, UF.

Segue em anexo uma base resumida exemplificando a situação descrita pra facilitar o entendimento.

Muito obrigado.

BaseTeste.xlsx

[Vitor Peralva]

Bom Dia, @André Feliciano!

O arquivo apresentando parece não representar o que narra no tópico, visto que ambos os códigos da tabela fato estão presentes em ambas as tabelas dimensão.

Sobre a mesclagem, neste caso, ela se demonstra impossível de ser feita sem comprometer os seus dados.

A tabela que usa o Município como referência, não tem correlação com a fato, salvo pelo representante.

Só que como ele pode representar mais de uma cidade, automaticamente, ao filtrar a cidade de código "1300029", você estará, na prática, selecionando todas as vendas do representante 23, independente da cidade, já que seria o filtro de representante que seria passado para a tabela fato.

Veja que o Munícipio é uma caracteristica do cliente e não do representante se formos ver.

Será que não existe uma coluna de clientes que acabe trazendo os mesmos dados?

Sobre os representantes constantes da tabela fato e ausentes na tabela de representantes, você poderia fazer o seguinte:

1) Cria um List.Distinct da sua tabela fato de representantes, algo como:

= List.Distinct(fVendas[Código Representante])

2) Com isto, você teria a listagem distinta de todos os códigos de representantes.

3) Clique no fx na barra de fórmulas para criar um nova etapa e crie um List.Distinct da sua tabela dimensão de representantes, algo como:

= List.Distinct(#"Endereços"[Código Representante])

4) Renomeie as etapas, para, por exemplo, Lista1 e Lista2. Agora, clique em fx novamente e subtraia as listas:

= List.Difference(Lista1, Lista2)

5) Você terá os códigos que estão na fato e não estão na dimensão.

6) Converta a lista para tabela, renomeie a coluna para "Código Representante".

7) Crie uma coluna personalizada e adicione as outras colunas como "Sem Informação" ou outra nomenclatura que prefira:

= [
    Status = "INATIVO",
    Nome Representante = "Sem Informação",
    Cidade  = "Sem Informação",
    UF = "Sem Informação",
    Rua = "Sem Informação",
    Bairro = "Sem Informação"
]

8) Expanda o registro e terá as colunas.

Obs.: Se você sabe que daqui para frente não haverá mais representantes que não estejam na dimensão, pq, por exemplo, estes códigos são de origem de um sistema antigo, copie os dados resultantes e insira com inserir dados, gerando um JSON e evitando ter que recalcular tudo em toda atualização.

 

9) Acrescente esta consulta a sua dimensão original como nova.

Esta consulta resultante deverá ser a sua dimensão representante.

Desabilite a carga das outras.

[André Feliciano]

Olá @Vitor Peralva boa tarde, tudo certo?

Muito obrigado pela ajuda! criei as listas pra trazer a diferença dos códigos da fato que não estavam na dimensão, e acrescentei consultas como uma nova pra criar a dimensão de Representantes, conforme sugerido.

Conversando com o gestor da empresa, chegamos a conclusão de que a análise deverá ser realizada pelos representantes com status "ativo" da tabela de "endereços", mesmo que tenham outros representantes que venderam anteriormente e que constam na tabela fato.. Dessa forma, vou focar a análise nesses representantes, os quais já possuem uma cidade e UF vinculadas.

Obrigado. Tenha uma ótima semana.

Atenciosamente,

André Feliciano.