Guia Definitivo sobre RELACIONAMENTOS entre tabelas e Modelagem de Dados

Leonardo Karpinski

Leonardo Karpinski

Mestre do Power BI, criador do Curso Express de Power BI e Curso Completo de Power BI. Formou mais de 16 mil alunos nos últimos anos e participou de projetos em grandes empresas nacionais e multinacionais.

Fala galera, tudo bem?

Hoje nosso post é referente a Live #35, onde falei tudo sobre relacionamentos entre tabelas e modelagem de dados. Então, acompanha aqui comigo até o final que tenho certeza que vou tirar várias dúvidas que você tem no dia-a-dia sobre o tema.

7 Pilares

Dentro do desenvolvimento de projetos com Power BI nos temos 7 pilares de construção:

Figura 1: Os 7 Pilares

Hoje nosso foco vai estar no 3º Pilar! Mas não tem como ter uma boa modelagem sem você ter uma boa estruturação. Então, quero que fique claro para você que esses 2 processos estão intimamente ligados.

Base de dados – Planilha de Vendas

Para nosso exemplo, eu decidi pegar a base de dados no formato mais comum com quem está começando a trabalhar com BI. O famoso “tabelão”…nele, normalmente a informação é extraída no sistema com diversas colunas (com algumas até inúteis) e que podem ser estruturadas de uma forma melhor.

O nosso primeiro “tabelão” é uma planilha de vendas de uma empresa fictícia:

Figura 2: Planilha de vendas

Então, essa será a primeira planilha que vamos obter os dados no Power BI:

Figura 3: Importando o arquivo Excel para Power BI Desktop

Aparecerá a tela a seguir e você deverá selecionar a tabela Vendas e clicar em Transformar dados:

Figura 4 : Seleção da tabela

Essa etapa que acabamos de fazer é o Pilar 1 – Extração, e quando clicamos em “Transformar Dados” vamos para o ambiente do Power Query onde fazemos o Pilar 2 – Estruturação:

Figura 5: Tabela no Power Query

Nesse caso, nossa tabela está bem estruturada e não precisamos aplicar nenhuma estruturação nela.

Para fazer uma análise inicial, vamos montar um visual demonstrando a quantidade de vendas por categoria de produto:

Figura 6: Gráfico de barras vendas por produto

Nesse caso, como estamos trabalhando somente com um “tabelão” os dados de vendas são filtrados por categoria de produto sem termos que criar um relacionamento porque estão na mesma tabela.

Bom, mas e agora se a gente quiser trabalhar tanto com a informação de compras quanto a de vendas? Vamos ver o que fazer…

Base de dados – Planilha de Compras

Nossa planilha de compras está no mesmo arquivo que a de vendas em .xlsx, porém em uma aba diferente.

Figura 7: Base de Compras

Para importá-la basta fazer as mesmas etapas que para a de compras e também selecioná-la antes de transformar dados:

Figura 8: Selecionando a tabela de Compras

No ambiente do Power Query, vemos que esse “tabelão” de compras também está bem estruturado.

Figura 9: Visualizando os dados no Power Query

Então, da mesma forma que fizemos para a tabela de vendas fechamos e aplicamos no editor do Power Query para voltar ao ambiente do Power BI.

Relacionamentos

Com as duas tabelas importadas, podemos simular o relacionamento entre elas montando uma tabela única onde ilustramos a quantidade vendida e comprada por categoria de produto:

Figura 10: Tabela vendida e comprada por categoria de produto (coluna vendas)

Veja que os valores de compras não estão sendo filtrados pela categoria de produtos assim como de vendas. Isso acontece, pois não existe uma relação da coluna de categoria da tabela de compras com a coluna de categoria da tabela de vendas assim os valores ficam igual ao total de compras para todas as categorias.

Podemos fazer uma outra simulação, trocando a categoria de produtos selecionada de vendas para a coluna da de compras. Consegue imaginar o que vai acontecer?

Figura 11: Tabela vendida e comprada por categoria de produto (coluna compras)

Já vimos que se tentarmos dessa maneira, somente jogamos o problema para o outro lado.

A forma correta de se trabalhar para resolver esse problema é utilizando relacionamentos. Abrindo a aba “Modelos”, podemos ver que não existe nenhum relacionamento criado entre as tabelas.

Nesse caso, vamos criar um relacionamento entre as tabelas pela coluna idProduto para testar o que acontece:

Figura 12: Relacionamento entre as tabelas

Após criar o relacionamento, vamos ver os valores da tabela:

Figura 13: Resultado após adição do relacionamento

Opa, parece ótimo! Todos os valores das duas colunas filtrados corretamente. Agora, será que esse é a melhor forma de se trabalhar?? Vamos fazer um teste simulando a quantidade de compras e vendas ao longo do tempo:

Figura 14: Tabela com Datas de Vendas

Os valores para vendas parecem corretos, mas e para compras? Na terceira linha se fizermos a soma já temos a quantidade de 1608 que é maior que o total 1545! Já vemos aqui que não está correto… Vamos tentar fazer o relacionamento entre as colunas de datas no nosso “Modelo” para ver o que acontece:

Figura 15: Criando relacionamento com a data

O resultado ficou:

Figura 16: Tabela de compras e vendas por período

Bom, se estava ruim agora piorou pois nem os valores aparecem. Isso acontece porque existem datas que ocorreram vendas porém nada foi comprado… Dessa forma é óbvio que não vai ter uma quantidade de compra para aquele dia.

Mas qual seria a saída nesse caso? E se criássemos o relacionamento entre as duas colunas (data e idProduto):

Figura 17: Relacionamentos entre duas colunas

Não tem como manter 2 relacionamentos ativos entre as mesmas duas tabelas. Então, essa não é uma solução.

A solução para esse caso é trabalhar com tabelas auxiliares que são as tabelas dimensão.

Tabelas fato e dimensão

Os dados estão sempre armazenados em um ou mais bancos de dados (OLTP). E quando falo isso o pessoal se pergunta: “Mas afinal, o que é um banco OLTP?”

O banco OLTP é um banco transacional. Nele os dados estão em sua forma normalizada, onde cada informação está em uma tabela específica. Assim, temos muitas (muiiiiiiitas mesmo) tabelas envolvidas. E cada tabelas tem informações específicas. Chamamos isso de modelo normalizado. Vou mostrar para vocês um exemplo da Aventure Works (empresa fictícia que a Microsoft disponibiliza dados):

Figura 18: Banco de dados transacional da Adventure Works

Simples? Organizado? Sim…geralmente, o banco de dados não é tão organizado. Nesse formato, para quem trabalha com bancos OLTP esse banco está muito bem estruturado!

Para quem não tem muito conhecimento, quando pega um banco desses não sabe bem ao certo nem como começar. E preferivelmente, quando trabalhamos com o Power BI devemos acessar uma outra camada intermediária que é chamada DataWarehouse (DW).

Nele o esquema é mais simplificado com estruturas de tabelas fato e dimensão. O resultado seria algo dessa forma:

Figura 19: Exemplo de DataWarehouse

Assim, a conexão fica mais simples e mais performático trabalhar dessa forma. Na realidade, nem sempre é utilizado o DataWarehouse, sendo que muita gente conecta direto no banco de dados transacional (OLTP) o que acarreta riscos para o fluxo da informação.

Nosso objetivo, é criar um modelo utilizando o processo de desnormalização. Juntando todas as informações em uma única tabela. Esse não é o melhor formato para se trabalhar, porém é muito realista. O melhor formato para se trabalhar é no modelo dimensional com tabelas fatos e dimensão). A definição dessas tabelas é:

Tabela dimensão: é uma tabela que possui informações relacionadas a alguma entidade de negócio (cadastro de algo)
              – É obrigatório possuir um ID (código / chave primária) único que represente a dimensão

Tabela fato: é uma tabela que possui movimentações, eventos/registros históricos (geralmente tem uma data atrelada)
              – O que eu preciso ter na Tabela Fato que é relativo à dimensão? Apenas o ID (código) da dimensão

Criação de tabelas fato e dimensão

E para gente aqui, vamos criar do zero nossas tabelas fato e dimensão. Tudo começa enxergando quais são as dimensões no seu modelo e isso requer um bom tempo analisando criteriosamente os seus dados.

Nesse nosso exemplo, as tabelas dimensões vão ser:

  • dCliente com as colunas: ID Cliente, Cliente e Tipo do Cliente
  • dProduto com as colunas: idProduto, NomeProduto e CategoriaProduto

Para criar as tabelas dimensões usamos o Power Query como ferramenta principal. Nele iremos juntar as duas tabelas fato, remover as colunas desnecessárias para nossas dimensões e também remover informações duplicadas.

Clique com o botão direito na tabela de Vendas e selecione Duplicar:

Figura 20: Duplicando consultas

Vamos chamar essa nova consulta de dCliente (dê duplo clique nela para renomeá-la). Em seguida, devemos escolher quais colunas permanecerão nessa tabela (somente colunas relacionadas a clientes):

Figura 21: Excluindo demais colunas

Agora você deve remover as duplicatas da tabela dCliente. Para isso basta clicar na coluna idCliente com o botão direito e selecionar “Remover Duplicatas”. Pronto! Temos a tabela dimensão para clientes.

Para obter uma tabela dimensão para fornecedores você deve fazer a mesma coisa, porém, agora precisará duplicar a tabela Compras.

Repita a mesma sequência de passos, mas agora você precisará manter apenas as colunas idFornecedor e Fornecedor. Essa consulta duplicada se chamará dFornecedor, portanto, renomeie-a. Além disso, lembre-se de remover as duplicatas com base na coluna idFornecedor, beleza?

Figura 22: Removendo duplicatas

Para obtermos a tabela dProduto, precisaremos fazer um pouco diferente já que ambas as tabelas (Vendas e Compras) possuem dados sobre produto.

Primeiro, duplique a tabela Vendas e depois Compras. A primeira se chamará dProdutoVendas e a segunda se chamará dProdutoCompras. Remova todas as colunas que não tenha relação com Produto, ou seja, deixe apenas idProduto, NomeProduto e Categoria produto com aquele recurso “Escolher colunas” que tínhamos feito antes.. Agora, vá em Página Inicial → Combinar → Seta do botão Acrescentar Consultas → Acrescentar Consultas como Novas:

Figura 22: Acrescentando consultas

Se você tiver na tabela dProdutoCompras, a segunda tabela será a dProdutoVendas, senão, faça o inverso. Basta selecionar a segunda tabela conforme imagem a seguir:

Figura 22: Acrescentando consultas

Por fim, ainda nessa consulta nova, clique na coluna idProduto com o botão direito e selecione “Remover duplicatas”. Ah, chame essa consulta de dProduto.

Como não precisaremos das consultas dProdutoVendas e dProdutoCompras, podemos desabilitar a carga delas. Basta clicar com o botão direito na consulta e desmarcar o “Habilitar carga”. No final, ambas as tabelas estarão com o nome em itálico, assim:

Figura 23: Desabilitando carga

Com as tabelas dimensões prontas, renomeamos nossas tabelas fato com o prefixo f (fVendas, e fCompras).

Agora para finalizar, removemos as colunas desnecessárias das nossas tabelas fato deixando apenas as colunas com a chave primária para conexão com as tabelas dimensão:

Figura 24: Escolhendo colunas nas tabelas fato

Pronto, com isso temos nossas tabelas fato e dimensão concluídas! Agora vamos criar nossos relacionamentos no modelo.

Criação dos relacionamentos

Quando a gente vem para o ambiente do Power Bi após “Fechar e Aplicar” alguns relacionamentos são construídos automaticamente. No nosso caso, vamos excluir todos eles bem como os que havíamos criado para construir tudo do zero. Clique em cada ligação (conexão entre as tabelas) e dê um “del”

Figura 25: Deletando os relacionamentos automáticos

Após excluirmos todos, vamos criar os novos da seguinte forma: arraste o nome da coluna de uma tabela na direção da outra. A seguir a lista de relacionamentos:

  • dFornecedor com fCompras: pela idFornecedor
  • dCliente com fVendas: pela idCliente
  • dProduto com fCompras: pela idProduto
  • dProduto com fVendas: pela idProduto
Figura 26: Relacionamentos criados

Veja que existe um formato na ponta de cada tabela na linha onde demonstra o relacionamento entre elas (são os 1 e *). Isso ilustra a cardinalidade desse relacionamento. A cardinalidade nos explica a forma na qual estão relacionadas aquelas tabelas, e existem 3 tipos de cardinalidade:

  1. Um para muitos (1:*) ou muitos para um (*:1): existe uma única linha na tabela 1 que se relaciona com várias linhas na tabela 2 (por exemplo: uma única linha de idProduto da nossa dProduto com várias linhas que possuem idProduto na fCompras)
  2. Um para Um (1:1): uma linha da tabela 1 se relaciona somente com uma linha da tabela 2
  3. Muitos para muitos (*:*): diversas linhas da tabela 1 se relacionam com diversas linhas da tabela 2 (esse caso é bem perigoso por poder causar problema de performance e ambiguidade no seu projeto)
Figura 27: Cardinalidade

Outra característica do relacionamento é se ele está ou não ativo, caso não esteja ativo o relacionamento não implicará em criação de contextos de avaliação para suas tabelas. Existem casos onde você pode ativar o relacionamento que está inativo através da função USERELATIONSHIP em uma medida:

Figura 28: flag de ativação do relacionamento

E por último, temos a propriedade de direção do relacionamento que pode ser em ambas as direções (ou seja, as tabelas podem filtrar tanto da tabela 1 para tabela 2 como da tabela 2 para a tabela 1).  A forma de ambas as direções aparece muito pouco (eu chuto que menos de 1% dos casos) e deve ser evitada nos modelos por poder causar ambiguidade. Em geral, usamos a direção única de filtro:

Figura 29: Direção do filtro

Com isso, temos a explicação de cada atributo dos relacionamentos bem como todos eles criados e podemos ir para os exemplos.

Exemplo de aplicação

O primeiro exemplo que iremos fazer será exatamente o mesmo gráfico de barras que fizemos anteriormente para a quantidade de vendas por categoria de produto. A diferença é que nesse caso a coluna de categoria vem da tabela dProduto e não da fVendas:

Figura 30: Gráfico de barras

O resultado parece bom e bate com o que tínhamos feito anteriormente! Podemos entender o relacionamento aplicado como um filtro que se propaga da seleção feita na tabela dProduto para a tabela fVendas. Visualmente ficaria dessa forma:

Figura 31: Aplicação de relacionamento

Criação da tabela dCalendario

Além das tabelas que criamos de dimensão com base nas tabelas fatos, temos mais uma tabela dimensão que usamos para fazer o relacionamento entre datas. Essa tabela é comumente chamada de dCalendario. Vá em Modelagem e clique no botão a seguir para criar uma nova tabela:

Figura 32: Criando uma tabela via DAX

Fórmula DAX:

dCalendario =
CALENDARAUTO

Figura 33: dCalendario criada via DAX

E com a dCalendario criada, fazemos o relacionamento tanto com a fVendas quanto com a fCompras:

Figura 34: Relacionando a dCalendario

Agora, tenho a tabela dCalendario e com ela relacionada as duas fato podemos colocar em um visual a análise ao longo do tempo das duas tabelas:

Figura 35: Gráfico de colunas no tempo de Vendas e Compras

Então, com  a tabela dCalendario de suporte conseguimos fazer esse tipo de análise entre 2 ou mais tabelas fato.

Esquema de relacionamentos

Existem alguns esquemas de relacionamentos que têm nomes próprios. O principal deles e que deve ser usado de preferência é o esquema estrela (Star Schema) onde as tabelas fato estão relacionadas somente a tabelas dimensão e as tabelas dimensão somente as fato. Outro modelo bem conhecido é o Snow Flake onde têm casos de as tabelas dimensão terem relacionamentos com outras tabelas dimensão. É um formato conhecido e que também é utilizado, porém tem pior performance e maior dificuldade para se criar hierarquias.

Granularidade

Imagine agora que você tem que avaliar os resultados perante as metas. Só que a tabela de metas está com um resultado por categoria de produto (você deve se lembrar que nas fato temos a idProduto). Nesse caso, temos uma granularidade diferente entre as tabelas.

Veja abaixo nossa base de metas:

Figura 36: Base de metas

Vamos importar nossa tabela de metas do mesmo arquivo em Excel da base:

Figura 37: Importando a base de Metas

A tabela de metas é uma tabela fato que ainda não ocorreu, mas é esperado que ocorra. Outra forma de se pensar se ela poderia ser ou não uma tabela fato é analisar as colunas que ela contém. Percebe que ela tem tanto informação de data (mês) quanto de produto (CategoriaProduto). Assim, já temos um ótimo indício de que ela é uma tabela fato. Então, vamos renomear: em Nome nas propriedades da consulta, digite fMetas.

Figura 38: Renomeando a tabela de Metas

Agora, podemos fechar e aplicar para voltar ao ambiente do Power BI para criarmos o relacionamento dessa tabela – que é onde fica o maior desafio.

Figura 39: dCalendario relacionada coma fMetas (colunas Date e Mês)

Para o relacionamento da fMetas com a dCalendario ficou fácil, pois o Power BI mudou automaticamente o mês para a data de início daquele mês.

Agora, para o relacionamento da dProduto com a fMetas, temos que ter muito cuidado na direção do filtro que deixaremos selecionada pois isso pode acarretar erros no seu relatório. Nesse caso, sempre deixar o sentido da tabela dimensão para a tabela fato:

Figura 40: Relacionamento entre fMetas e dProduto

Para avaliar as vendas comparado a meta, vamos criar uma medida de faturamento:

Medida:

Faturamento =
SUMX ( fVendas, fVendas[Quantidade] * fVendas[ValorUnitario] )

Agora, conseguimos comparar lado a lado em uma matriz quanto temos de resultado de faturamento contra a meta estipulada:

Figura 41: Meta versus faturamento

Até aqui tudo bem, o problema se dá quando queremos descer para o nível de produto. Veja como fica quando adicionamos essa hierarquia (arrastando NomeProduto para o campo Linhas do visual):

Figura 42: Tabela com hierarquia

Nesse caso, a meta ficou igual dentro do subnível e para corrigir isso temos que criar uma regra de negócio, por exemplo, dividir o valor total da categoria para cada um dos produtos.

Relacionamento único e ambos

Como eu disse anteriormente é bem arriscado utilizar um relacionamento para ambos os lados, pois pode ocorrer o problema de ambiguidade. Uma forma de analisar se você pode ter esse problema é tentar chegar na mesma tabela a partir de uma tabela base por mais de um caminho seguindo o fluxo dos relacionamentos criados.

Vamos para um exemplo que você vai entender melhor, onde eu modifico o relacionamento entre a fMetas e a dCalendario para ambos e partimos a análise da dProduto para chegar na fCompras.

Figura 43: Relacionamento ambas as direções

Veja como ficou a configuração desse relacionamento:

Figura 44: Direção do filtro cruzado modificado para ambas

O Power BI possui uma inteligência que força a utilização do caminho mais curto nesse tipo de análise. Porém, recomendo fortemente que não usem o relacionamento em ambas as direções até porque para casos mais específicos temos a opção de utilizar a função CROSSFILTER em DAX que força virtualmente esse tipo relação. Então, volte para o status anterior do filtro (Único), beleza?!

Relacionamento inativo

Para simular o relacionamento inativo, precisamos ter duas colunas da tabela fato que se relacionem com a tabela dimensão. Aqui, vou criar uma coluna de entrega na tabela fVendas para além de ter a relação entre a data de venda com a dCalendario também ter um relacionamento entre a coluna de entrega (que ficará inativa) e a tabela dCalendario:

Figura 45: Coluna DataEntrega adicionada

Lembre-se de mudar aquela etapa de seleção de colunas da fVendas:

Figura 46: Habilitando coluna DataEntrega na fVendas

Agora, relacione a DataEntrega com Date, e veja que ficará tracejada a conexão:

Figura 47: Relacionando DataEntrega com a dCalendario

Dessa forma, você consegue fazer uma análise com base na data de entrega dos produtos. Porém, precisamos descriminar isso na medida que irá fazer o cálculo porque ela não é a padrão (ativa). Vamos fazer uma medida com essa característica:

Medida:

Quantidade Entregue =
CALCULATE (
    SUM ( Vendas[Quantidade] ),
    USERELATIONSHIP ( fVendas[DataEntrega], dCalendario[Date] )
)

Com a medida criada, podemos analisar em um mesmo visual a quantidade vendida e entregue de produtos para cada data:

Figura 48: Gráfico de venda e entrega

Chave composta

Existem casos onde o mesmo código pode representar coisas diferentes dentro da empresa (por exemplo, em empresa com filiais o mesmo id de cadastro de material pode ser para materiais completamente diferentes). Nesses tipos de casos, temos duas opções:

  1. Concatenar: criar um código única concatenando os valores das colunas
  2. Surrogate key: é a forma mais elegante onde você cria uma chave (vou fazer o exemplo com vocês)

Caso você não faça nenhuma das opções ao tentar criar o relacionamento no Power BI ele vai informar um relacionamento de muitos para muitos:

Figura 49: Relacionamentos muitos para muitos

O primeiro passo para criar a surrogate key é criar uma coluna de índice no Power Query:

Figura 50: Criando uma coluna de índice

Assim, já temos a chave única na nossa dProduto. E como fazer a ligação com a fVendas? Fazendo uma mescla entre as consultas e selecionando as duas colunas que identificam unicamente o produto:

Figura 51: Mesclando consultas

Depois disso, expandimos somente a coluna com a informação do surrogate key:

Figura 52: Expandindo a sk

Tendo essa informação na tabela de vendas, podemos deletar as outras colunas com informação de produto da tabela:

Figura 53: Tabela final de vendas

E fazendo o mesmo processo para a tabela de compras temos o resultado:

Figura 54: Tabela final de compras

Podemos fechar e aplicar, e automaticamente o Power BI irá criar os relacionamentos entre as tabelas que podemos ver em “Modelo”:

Figura 55: Modelo

Bom galera, aqui nesse guia definitivo cobri vários exemplos que o pessoal tem na rotina real de trabalho com Power BI com relacionamentos! Espero que tenha ajudado vocês e se tiver qualquer comentário ou dúvida deixa aqui embaixo.

Abraços,
Leonardo.

Compartilhe este post:
Compartilhar no facebook
Compartilhar no linkedin
Compartilhar no twitter
Compartilhar no pinterest