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.
Clique aqui para iniciar seu aprendizado em Power BI, uma das ferramentas mais solicitadas pelo mercado de trabalho.
7 Pilares
Dentro do desenvolvimento de projetos com Power BI nos temos 7 pilares de construção:
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:
Então, essa será a primeira planilha que vamos obter os dados no Power BI:
Aparecerá a tela a seguir e você deverá selecionar a tabela Vendas e clicar em Transformar dados:
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:
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:
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.
Para importá-la basta fazer as mesmas etapas que para a de compras e também selecioná-la antes de transformar dados:
No ambiente do Power Query, vemos que esse “tabelão” de compras também está bem estruturado.
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:
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?
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:
Após criar o relacionamento, vamos ver os valores da tabela:
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:
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:
O resultado ficou:
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):
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):
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:
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:
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):
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?
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:
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:
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:
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:
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”
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
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:
- 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)
- Um para Um (1:1): uma linha da tabela 1 se relaciona somente com uma linha da tabela 2
- 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)
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:
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:
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:
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:
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:
Fórmula DAX:
dCalendario =CALENDARAUTO
E com a dCalendario criada, fazemos o relacionamento tanto com a fVendas quanto com a fCompras:
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:
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:
Vamos importar nossa tabela de metas do mesmo arquivo em Excel da base:
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.
Agora, podemos fechar e aplicar para voltar ao ambiente do Power BI para criarmos o relacionamento dessa tabela – que é onde fica o maior desafio.
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:
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:
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):
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.
Veja como ficou a configuração desse relacionamento:
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:
Lembre-se de mudar aquela etapa de seleção de colunas da fVendas:
Agora, relacione a DataEntrega com Date, e veja que ficará tracejada a conexão:
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:
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:
- Concatenar: criar um código única concatenando os valores das colunas
- 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:
O primeiro passo para criar a surrogate key é criar uma coluna de índice no Power Query:
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:
Depois disso, expandimos somente a coluna com a informação do surrogate key:
Tendo essa informação na tabela de vendas, podemos deletar as outras colunas com informação de produto da tabela:
E fazendo o mesmo processo para a tabela de compras temos o resultado:
Podemos fechar e aplicar, e automaticamente o Power BI irá criar os relacionamentos entre as tabelas que podemos ver em “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.
