Primeiro Relatório com o Microsoft Power BI

Caso você não saiba o que é o Power BI, ou então mesmo o que é Business Intelligence, estou deixando aqui uma postagem que eu fiz falando sobre o assunto.

Agora que você já sabe o conceito básico, vamos começar.

O primeiro passo, caso você ainda não tenha o Power BI instalado em seu computador é baixar e instalar o programa através desse link do site oficial.

Bem, mas antes de começar a usar o Power BI, você precisa entender alguns conceitos sobre ETL, e também sobre o o que são tabelas Fatos e Dimensões.

ETL(Extract, Transform, Load)

O significado de ETL em português é “Extrair, Transformar, Carregar”. É necessário sabermos um pouco sobre ETL pois na maioria das vezes que iremos trabalhar com dados, eles estão em uma forma não estruturada, normalmente chamamos de esses dados como dados desnormalizados. Para começarmos a criar os primeiros relatórios temos que organizar os dados de forma que siga nossas regras de negocio, além de limpar pequenas “sujeiras”, como por exemplo: espaços em branco,valores nulos,mesma palavra escrita de formas diferentes(PALAVRA <> palavra), entre outros. Existem várias maneiras de realizar o processo de ETL, como a criação de cubos de dados, mas nesse artigo vou comentar apenas utilizando as ferramentas disponibilizadas pelo Power BI.

Para realizarmos o processo de ETL dentro do Power BI, possuímos uma ferramenta dentro do editor de consultas, que se chama Power Query, após a importação dos dados necessários para as nossas analises, podemos utilizar o Power Query para realizar algumas funções como: Limpar e filtrar dados,Substituir valores,Combinar dados,Remover valores duplicados,Formatar dados,Criar colunas personalizadas,Pivotar colunas e Remover colunas. E é dentro dele também que iremos criar nossas tabelas de Dimensões e Fatos.

Tabelas Dimensões e Tabelas Fatos

Para conseguirmos gerar nossos relatórios de BI, dividimos nossa base de dados em tabelas de Dimensões e Fatos. Utilizamos essa organização, pois desse modo, conseguimos enxergar um mesmo fato de varias dimensões diferentes.

Esquema Estrela

Dimensão: Consideramos parte da tabela dimensão os registros descritivos referente aos fatos. Normalmente é na Tabela dimensão que encontramos os elementos que constituem um fato.

Fato: Consideramos parte da tabela fato, aqueles elementos que constituem o fato propriamente dito, como por exemplo uma tabela de vendas.

Para ficar mais fácil o entendimento, vamos para um exemplo pratico.

Possuímos quatro tabelas no exemplo abaixo. Conseguimos identificar que a tabela Fato é referente as vendas, ou seja o fato em questão é referente as vendas realizadas por um determinado comércio. E sendo as tabelas dimensões(ou tabelas auxiliares) podemos identificar as dimensões de Tempo,Produto e Loja. Ou seja usando esse mesmo modelo, podemos ver o mesmo cenário por 3 perspectivas diferentes. Podemos identificar as vendas realizadas dentro de um determinado período(“Qual foi meu número de vendas no mês de Janeiro?”), a perspectiva pelo produto(“Quais são cinco produtos que eu mais vendo?”) e por último enxergando pela loja(“Quais são as Lojas que mais faturam?”).

Organização feita para BI de Vendas

Agora vamos começar !

Para este primeiro relatório irei começar com um exercício bem simples. Na verdade é bem semelhante ao exemplo acima referente as vendas de uma determinada franquia de supermercados, onde atualmente todo controle é feito através de uma planilha do Excel.

Planilha do Excel com os Registros das Vendas

Podemos ver que atualmente só são salvos as informações da Data da Venda, Nome do Produto, Valor Unitário, Quantidade vendida e o Valor Total da Venda. Sem distinção entre clientes que realizaram a compra. E esse mesmo esquema de tabelas é repetido na mesma aba, onde cada tabela é referente a uma loja da franquia. Você pode realizar o download desse arquivo clicando aqui.

Hora de Organizar as Coisas!

Assim que abrimos o arquivo de vendas, podemos ter a impressão que o arquivo já está organizado e arrumado. Mas lembre-se dos conceitos que mencionei para podermos realizar o BI.

Antes de importarmos o arquivo para dentro do Power BI e começar a modelar nossa base de dados, vamos tentar montar e visualizar quais tabelas Fatos e Dimensões que iremos obter.

Eu irei montar seguindo o esquema abaixo, mas sinta-se a vontade se quiser realizar algumas adaptações e/ou melhorias, mas a modelagem minima terá que ser parecida com a minha abaixo.

Tabelas de Fatos e Dimensões

E agora que já temos em mente o que iremos fazer hora de importar nosso arquivo para dentro do Power BI.

Para importar é bem simples.

Basta se certificar que você está na Aba Base , e ir em Obter Dados → Excel.

Assim que você selecionar Excel, o Power BI irá solicitar o caminho do arquivo. Onde você pode seleciona-lo navegando através das pastas do gerenciador de arquivos do Windows.

Após selecionar o arquivo, o Power BI irá abrir uma nova janela que irá demonstrar um breve preview das primeiras linhas dos dados que serão importados. Para confirmar basta selecionar o conjunto de dados através do menu lateral na esquerda e apertar Carregar.

Após carregar nosso conjunto de dados, para começarmos nosso processo de modelagem, iremos na aba Base , e selecionar Editar Consultas.

Dentro dessa tela, podemos ter uma boa noção do dados importados.

Antes de começarmos a modelar, irei ressaltar algumas coisas que podemos encontrar nessa janela.

Consultas: Localizada no lado esquerdo da nossa janela, nesse espaço é onde iremos visualizar os conjuntos de dados que possuímos dentro do nosso projeto.

Passos aplicados: Localizado no lado direito da nossa janela, nesse espaço podemos ver todos os passos a passos que realizamos durante a modelação dos dados, o Power BI salva esse histórico de mudanças, porque toda a vez que houver uma mudança no nosso arquivo e quisermos atualizarmos, o Power BI , automaticamente irá realizar todos esses passos nessa nova versão do arquivo.

Voltando à modelagem, normalmente ao modelar os dados, é mais cômodo desenvolvermos Scripts para fazer o trabalho mais difícil da modelagem, e depois utilizar as funções do Editor de Consultas apenas para realizar os ajustes mais finos.

Seguindo essa ideia, vamos primeiro desenvolver um script para juntarmos todas as sub tabelas da tabela principal para uma tabela unica de dados. O editor de consultas no permite desenvolvermos scripts em R, porém para isso é necessário antes ter instalado em sua maquina a distribuição do R que pode ser baixada diretamente do site Microsoft R Open clicando aqui.

Para executar um script em R, basta ir na Aba Transformar e selecionar Executar Script de R.

Na tela que irá se abrir iremos colar o seguinte script que irá modelar nossos dados.

Se você fizer tudo certo até aqui, você irá obter esse resultado.

Agora que temos uma modelagem minima, vamos ajustar apenas alguns pequenos detalhes. O primeiro deles é especificar o tipo de dado das colunas. Para mudar o tipo de dados basta clicar no ícone que está a esquerda do nome da coluna e selecionar o novo tipo.

Vale ressaltar caso você tente converter o tipo da coluna para um outro, verifique se os seus dados estão prontos para suportar essa transformação de tipo. Por exemplo se tentarmos alterar o Tipo de Dados da coluna Produto do tipo Texto para Numérico todos os valores irão apresentar Error, pois não é possível realizar conversão de letras para um formato numérico. Outra coisa que cabe ressaltar que pode passar despercebido, é que dependendo das suas configurações regionais alguns padrões podem mudar, como por exemplo, no Estados Unidos, é utilizado para delimitar os números decimais o ponto. ), já no Brasil utilizamos a virgula, ) e para delimitar as casas do milhar é ao contrario também, no Estados Unidos eles utilizam a virgula, ), e no Brasil utilizamos o ponto. ). Segue o exemplo de um mesmo número escrito nos Estados Unidos e no Brasil.

Estados Unidos: 6,543.21 / Brasil: 6.543,21

Já sabendo disso podemos ver que nas colunas Valor Unitário e Total a divisão dos números decimais está sendo através do ponto e caso tentemos realizar a conversão para o tipo numérico o Power BI irá realizar a transformação de modo errado como podemos ver abaixo.

Para evitarmos esse problema, antes de transformar o tipo da colunas, vamos substituir os pontos por virgulas. Para realizar isso basta clicarmos com o botão direito encima da(s) coluna(s) em questão e selecionar Substituir Valores.

Na tela que irá se abrir em Valor a Localizar, iremos por o ponto (.) e em Substituir Por iremos por a virgula (,) e selecionar OK.

Após realizar a substituição de valores nas colunas Total e Valor Unitário, podemos definir os novos tipos de dados das colunas.

Iremos definir da seguinte maneira:

Data:Data, Produto:Texto, Valor.Unitario:Número Decimal Inteiro, Quantidade:Número Inteiro, Total:Número Decimal Inteiro, Loja: Texto, Endereco:Texto.

Agora que já possuímos a base de dados pronta, podemos começar a dividi-la entre nossas tabelas de Fatos e Dimensões, para evitar ter que fazer todo esse trabalho novamente, sugiro que em Consultas, você sempre duplique a tabela para já ter uma nova com todos os passos gravados. Para duplicar a tabela basta clicar com o botão direito encima dela e selecionar Duplicar.

Vamos começar com as nossas tabelas dimensões, que são dim_lojas,dim_produtos e dim_calendário. Porém dentro do editor de consultas iremos construir apenas a dim_lojas e dim_produtos, a dim_calendário explicarei melhor mais pra frente pois ela sera uma tabela dinâmica.

Primeiro passo é renomear a nossa tabela duplicada para dim_lojas.

Nessa tabela a unica coisa que sera útil, são as colunas com os nomes das Lojas e seu respectivo endereço.

Então o primeiro passo é excluir todas as colunas que estão fora desse escopo.

Este será o resultado obtido, agora o que iremos fazer é remover todos os registros duplicados.

Para remover os duplicados, basta selecionar ambas as colunas, clicar com o botão direito e selecionar Remover Duplicados.

Como resultado iremos obter uma lista unica das lojas e seus endereços.

A única coisa que nos falta é adicionar uma coluna com um ID para cada loja. O próprio editor de Consulta nos fornece essa opção para criar uma coluna de índices, para isso vá na Aba Adicionar Colunas, e selecione Coluna de Índice → De 1

E como resultado final podemos alterar o nome da coluna que por padrão é Índice renomeá-la para id_loja.

Feito isso, nossa tabela dimensão dim_lojas está pronta, agora vamos construir a tabela dim_produtos.

Realize os seguintes passos para a construção de dim_produtos:

  1. Duplique a tabela Lojas
  2. Renomeie Lojas(2) para dim_produtos
  3. Remova as colunas Data, Quantidade, Total, Loja e Endereco
  4. Selecione as colunas restantes(Produto e Valor Unitário) e remova as linhas duplicadas
  5. Adicione uma coluna de índice
  6. Renomeie a coluna Índice para id_produto, e altere seu tipo para Número Inteiro.

Como resultado final você deve obter essa tabela, contendo o id do produto, o nome do produto e seu respectivo valor unitário.

Agora que construímos nossas tabelas dimensões de produtos e lojas, vamos montar a tabela fato da venda.

O que precisamos alterar nessa tabela, é substituirmos a coluna Produto e Valor Unitário, por id_produto da nossa dim_produto, e substituir nossas colunas Loja e Endereço por id_loja.

Para fazer isso vamos na aba Base, e selecionar Combinar → Intercalar Consultas

Na nova janela, vamos selecionar através do menu de seleção, com qual tabela vamos intercalar, e qual é a coluna correspondente em ambas as tabelas para intercalar as nossas consultas. Nesse caso queremos obter informações da tabela dim_produtos, onde fat_vendas(Produto) é igual à dim_produtos(Produto)

Após isso, iremos expandir a nova coluna que será criada com as informações intercaladas e selecionar apenas os dados que queremos ter na tabela fato, que no caso é apenas o Id do Produto.

Depois de intercalar os dados com a tabela dim_produtos, iremos realizar os mesmos passos com a tabela dim_lojas, onde nesse caso fat_vendas(loja) é igual à dim_lojas(loja).

Agora que já temos o ID de produto e loja, podemos excluir as colunas produto,valor unitário,loja e endereço, pois já é possível obtermos esses dados através dos IDs. Também podemos excluir a coluna Total, pois como já possuímos o valor unitário e a quantidade vendida, não é necessário guardar essa informação, já que conseguimos calcula-la multiplicando o valor unitário pela quantidade vendida. Após todos esses passos aplicados em fat_vendas você deve obter uma tabela como essa.

EDIT: Nesse caso também seria interessante adicionarmos uma coluna de índice(id_venda) em fat_vendas.

Feito todos esses passos, vá na aba Base e selecione Fechar e Aplicar, desse modo vamos aplicar as alterações feitas e voltar a tela inicial do Power BI.

Agora a única coisa que nos falta é criar a dimensão de data, mas como havia explicado anteriormente, iremos cria-lá de maneira automática.

Dentro da tela original do Power BI vamos na aba Modelação selecionar Nova Tabela, e colar o seguinte código DAX.

A função calendar requer os seguintes parâmetros:

CALENDAR([StartDate];[EndDate])

Então se observamos bem a nossa expressão, vamos reparar que estamos criando uma tabela com todas as datas entre a data mais antiga e a data mais atual de venda.

Se clicarmos no ícone de dados no menu lateral esquerdo e selecionarmos a tabela dim_calendário podemos ver a tabela criada dinamicamente.

Para nos auxiliar podemos criar algumas colunas de apoio à data, como por exemplo: Ano, Mês, Mês Nome,Mês-Ano e Dia.

Para isso basta ir na aba Modelação e selecionar Nova Coluna, e escrever as seguintes funções para cada coluna.

Outras coisa que também já podemos adiantar é a ordenação dos Meses em relação ao conjunto como um todo, onde por exemplo, Jan/2013 é igual ao 1º mês, Jan/2014 é nosso 13º mês, Jan/2015 é 25º mês e assim por diante.

Como resultado você irá obter uma tabela como essa.

A vantagem de criar uma tabela dinâmica, é que todas as vezes que atualizarmos o arquivo essa tabela será criada automaticamente.

Agora que temos todas as tabelas dimensões e fatos, só nós resta editar os relacionamentos. Para isso vá no menu lateral e selecione o ultimo ícone que é referente á relações

O Power BI, já tem a capacidade de identificar alguns relacionamentos de forma automática, mas caso ele não tenha relacionado ou então relacionado de forma errada , você pode simplesmente excluir a relação e criar manualmente. Provavelmente em seu projeto, o Power BI só identificou o relacionamento da fat_vendas com as tabelas dim_produtos e dim_lojas, então para criar um novo relacionamento de forma manual entre dim_calendário e fat_vendas, basta clicarmos em dim_calendário(Date) e arrastar até fat_vendas(Data), Após todas essas etapas estamos prontos para começar a criar os nossos primeiros gráficos e relatórios.

Antes de tudo, vamos criar uma tabela onde iremos guardar nossas medidas. É uma boa pratica para a maioria dos profissionais que utilizam o Power BI, criar uma tabela especialmente para guardar nossas medidas, atualmente não possuímos uma forma direta para criar uma tabela de medidas, então oque vamos fazer é ir na aba Base e selecionar Introduzir Dados. Na janela que irá se abrir apenas altere o nome da tabela para Medidas no campo Nome e clique em carregar.

OBS: Não apague a coluna, logo após criar a Tabela, caso o Power BI não encontre nada dentro da tabela ele automaticamente à excluirá. Primeiro crie suas medidas e após isso sinta-se à vontade para apagar a coluna.

Agora com a nossa tabela de medidas criadas, vamos criar nossas primeiras medidas.

As duas primeiras medidas que iremos criar será as medidas responsáveis de encontrar o Valor Unitario(dim_produto) e Quantidade(fat_vendas). Para criar nossas medidas basta clicar com o botão direito encima da tabela Medidas localizada em Campos(menu lateral direito) e selecionar Nova Medida.

A primeira medida será a do Valor Unitário para isso cole o seguinte código

Valor Unitario = SUM(dim_produtos[Valor.Unitario])

E a segunda será Quantidade

Quantidade = SUM(fat_vendas[Quantidade])

Não precisa se preocupar se as expressões acima estão somando tudo sem distinção, o Power BI durante os relatórios irá agregar os valores de acordo com a granularidade que solicitarmos. Por exemplo, se utilizarmos uma granularidade encima do ID_Vendas ele irá somar apenas os valores referente a aquele ID_Vendas, mas caso criarmos um relatório encima de Lojas, ele irá somar apenas os valores referentes àquele ID_Loja, isso ficará mais claro em breve.

O que iremos construir agora é uma coluna dinâmica do Valor da venda, que é nada mais que [Valor Unitário] x fat_item(Quantidade). Para isso, vamos criar uma nova coluna em fat_venda com a seguinte expressão

Total = fat_vendas[Quantidade] * [Valor Unitario]

E agora que temos o valor de cada venda podemos criar uma nova medida na nossa tabela de medidas para o +.Valor Total

Valor Total = SUM(fat_vendas[Total])

Para esse artigo, vou apenas criar alguns gráficos e tabelas.

Atualmente, o Power BI nos fornece os seguintes elementos visuais para compor nossos relatórios, porém a cada atualizações novos elementos podem ser inseridos, e também existe a possibilidade de baixarmos outros elementos visuais no site da Microsoft.

Vamos tentar criar um relatório onde será possível responder as perguntas de exemplo que dei no inicio desse artigo.

“Qual foi meu número de vendas no mês de Janeiro?”

“Quais são cinco produtos que eu mais vendo?”

“Quais são as Lojas que mais faturam?”

Primeiro vamos definir os elementos que iremos usar, vou chamo-lo pelos nomes e também pela posição(Horizontal :A → F e Vertical: 1 ↓ 5 ) para ficar mais fácil de encontrar.

Vamos primeiro adicionar o nosso relatório as Segmentação de Dados(A5) para filtrar os anos e meses.

Para isso basta arrastar o elemento para dentro do Relatório e no espaço Campo arraste dim_calendário(Ano)

E faço o mesmo passo novamente, mas dessa vez criando para dim_calendario(MesNome)

E por fim vamos adicionar um Cartão(D4) e em Campo iremos adicionar nossa medida Quantidade.

Já temos a estrutura pronta para responder a primeira pergunta, agora vamos para a segunda, os produtos mais vendidos. Para isso iremos criar uma nova medida com a função RANKX, essa função nos pede os elementos e seus respectivos valores para serem ranqueados, para isso crie uma medida Rank Produto com a seguinte expressão:

Rank Produto = RANKX(ALL(dim_produtos[Produto]);[Quantidade])

Onde nessa função estou pedindo para o Power BI ranquear pela quantidade de vendas todos os produtos de dim_produtos.

Agora para exibir vamos incluir uma Tabela(B5) com os valores: Rank Produto, Produto e Quantidade. E ordena-las pela coluna Rank Produto.

E por ultimo falta descobrirmos qual loja fatura mais. Para isso vamos incluir um Gráfico de Colunas Agrupadas(A4) sendo o eixo dim_lojas(loja) e em valores Valor Total

Em tese nosso relatório está pronto, oque podemos fazer aqui é adicionar mais alguns filtros e melhorar o visual dele, para isso não existe muito bem um padrão e vai de cada pessoa, caso você queira saber mais de como estilizar seu relatório clique aqui para ser redirecionado para o site do Power BI, e ver alguns guias de para esse assunto de visualização.

Após algumas modificações no layout o resultado final foi esse relatorio

E agora com nosso relatório em mãos somos capazes de responder tais perguntas

“Qual foi meu número de vendas no mês de Janeiro?”

R. Em Jan/2015 quantidade de vendas em todos os estabelecimentos foi de 104 Produtos.

“Quais são cinco produtos que eu mais vendo?”

R. Em Jan/2015 os produtos que foram mais vendidos em todas as lojas são: Farinha de Mandioca, Chocolate, Sardinha, Arroz e Batata Palha. Em sua respectiva ordem.

“Quais são as Lojas que mais faturam?”

R. Em Jan/2015 as lojas que mais faturaram foram na ordem do maior para menor foram o Estabelecimento C, Estabelecimento B e por ultimo o Estabelecimento A.

Essas perguntas que eu respondi foram somente referente ao mês de janeiro de 2015, mas caso seja necessário vermos em um outro determinado período, ou então de apenas algumas lojas em especifico, basta utilizarmos os filtros presentes no relatório, como no exemplo abaixo, onde estamos filtrando todas as vendas do Ano de 2016 do Estabelecimento A.

Vou deixar aqui, o arquivo do projeto para download. Caso queira abri-lo em seu computador, salve o arquivo vendas.xlsx dentro da pasta C:\BI, ou então, modifique a origem de dados em File → Opções e Definições → Definições da Origem de Dados. Em origens de dados do ficheiro atual selecione o caminho do arquivo vendas.xlsx e clique em Alterar Origem e aponte o caminho onde o arquivo está salvo.

Se você leu esse artigo até aqui, espero que você tenha conseguido acompanhar todos os passos e se interessado pelo assunto.Caso ainda tenha dúvidas ou queira saber algo além, me contate enviando uma mensagem pelo Linkedin que ficarei feliz em ajudá-lo :)