BLOG DOS
INCOMPARÁVEIS

Publicações

Conectando em Bancos de Dados e Integrando Bases

No conteúdo de hoje, vamos falar sobre como conectar em bancos de dados e integrar bases de diferentes sistemas. Você que está no dia a dia de Business Intelligence, análise de dados e criação de relatórios sabe o quanto é importante esse assunto! Aqui, de forma muito completa e detalhada vou compartilhar com você:

  • O que é data warehouse e como visualizar um banco de dados
  • Como obter dados de diferentes fontes no Power BI
  • Como fazer integração de bases;
  • O que é e como funciona o data flow (fluxo de dados)

Clique Aqui para aprender mais sobre SQL, a principal linguagem de programação utilizada pelas empresas e se destacar no mercado de trabalho.

Data Warehouse e Visualização de Banco de Dados

Primeiramente, vamos analisar o processo tradicional de Power BI na imagem abaixo.  É muito interessante terem essa imagem do processo na cabeça, para facilitar o entendimento fluxo do processo de BI.

Figura 1: Fluxo do Processo de BI

Aqui, vemos que todo processo começa sempre na base de dados (OLTP – Online Transaction Processing). A partir dessa informação, fazemos a ETL (extração, transformação e carga) e enviamos para a Data Warehouse (OLAP – Online Analitycal Processing). Os dados podem vir de diversas fontes (pastas Excel, SQL, ERP, Web). A ideia do Data Warehouse (DW) é armazenar um grande volume de dados, deixando melhor condicionados e possibilitando as análises desses que são coletados dos sistemas transacionais (OLTP). Então, porque nem todos tem uma Data Warehouse? São alguns fatores que levam a não ter uma DW:  falta de gente, dinheiro para investimento, ou tempo. Aqui é  onde entra o Power BI, pois ele possibilita o usuário integrar diversas fontes de dados podendo então fazer a conexão dessas informações.

No exemplo de hoje, vamos utilizar as informações do banco de dados da empresa Adventure Works (fictícia) para simular uma situação de necessidade dessa conexão. Onde estes dados podem ser visualizados? Em um gerenciador! (por exemplo, o SQL Server).  Vamos utilizar o SQL para uma demonstração bem básica de visualização nesse tipo de software.

Importante:
 Existem 2 pontos importantes para fazer o gerenciamento do banco de dados!
· Ter acesso ao banco de dados (normalmente esse ponto é alinhado com a equipe de TI, pois pode ser necessário acesso liberado pelo admin às informações)
· Ter o gerenciador
Dica:
Se você não possui o SQL, pode ter acesso ao download da versão gratuita de desenvolvedor em https://www.microsoft.com/pt-br/sql-server/sql-server-downloads. Escolha a opção Developer (gratuita para teste). Para detalhes de instalação e do software, acesse este link https://docs.microsoft.com/pt-br/sql/database-engine/install-windows/install-sql-server?view=sql-server-ver15 ou algum dos diversos vídeos no YouTube demonstrando o passo a passo da instalação.

No nosso caso, iremos utilizar um banco local sem a necessidade da autenticação. Abaixo veremos as informações dos bancos de dados que iremos utilizar dentro do servidor. Para entender de uma maneira simples, tente imaginar que são diversos arquivos de Excel com tabelas dentro e que cada arquivo do Excel seria um banco de dados.

Figura 2: Visualização de Banco de Dados no SQL Server

A visualização que vamos fazer é bem simples com base no banco utilizado no curso de DAX avançado. Por exemplo, como visualizar todas as informações que tenho na tabela cliente? Para isso é só clicar em:

Comando: Nova Consulta >  SELECT * FROM Cliente

Também podemos visualizar somente as colunas desejadas, e para isso deve-se explicitar depois de “SELECT” da seguinte forma:

Figura 3: Visualização de Tabela Completa no SQL Server

Comando: Nova Consulta > SELECT cdCliente, RazaoSocial FROM Cliente

Figura 4: Visualização de Colunas Selecionadas no SQL Server

Outra forma de visualizar é:

Comando: Clicar com o botão direito na tabela > Selecionar 1000 linhas superiores

Figura 5: Comando no mouse para visualização de Tabela no SQL Server

Obter Dados – Power BI

De maneira simples podemos importar para o Power BI essas informações em “Obter dados”, lá vemos uma ampla variedade de bases que podemos importar. A lista é grande e você pode verificar ela completa aqui nesse link da MS https://docs.microsoft.com/pt-br/power-bi/connect-data/desktop-data-sources.  E  caso não encontre um tipo especifico para seu banco de dados, ainda tem a opção ODBC. Você encontra uma explicação mais detalhada de ODBC nesse link da MS https://docs.microsoft.com/pt-br/sql/odbc/reference/what-is-odbc?view=sql-server-ver15.

Voltando para nosso caso, vamos importar a base que acabamos de ver no SQL para o Power BI. Para isso temos duas opções: importar ou utilizar DirectQuery. Mas e aí? Qual utilizar?? Por experiência, importar resolve quase todos os casos, a não ser que tenha uma real necessidade de atualização online das informações. Quando em contato com quem irá utilizar as informações (gestores, alta liderança ou clientes internos) provavelmente, será solicitado as informações Online. Nesse caso, é importante o questionamento para verificar a real necessidade disso. Lembre-se, análise em tempo real e BI são conceitos diferentes. Enfim, vamos obter os dados em: 

Comando: Obter dados > Banco de dados SQL Server

Figura 6: Obtendo dados do SQL Server

 Vemos também a opção de utilizar a Query nessa parte. Para quem é de negócios, pode ter  tido contato com a equipe de TI nessa parte onde colocamos  os comandos antes de executar a importação.

Importante:
Nesse caso, a base de dados é local! Caso fosse conectar a uma fonte do servidor, entraria em outra etapa onde seriam necessárias informações para a conexão.

Figura 7: Visualização do Servidor e escolha do Banco de Dados

Depois de escolher o banco de dados que vamos trabalhar (CursoAvancadoDAX), temos que escolher as tabelas de informações que vamos importar. Para finalizar, temos a opção de carregar ou transformar os dados. E aí, o que fazer? Lembre-se sempre da sequência ETL (Extração, Transformação e por fim Carga) para não pular etapas. Então, temos que transformar o arquivo.

Transformação dos Dados

Qual o primeiro passo que vocês fazem no processo de transformação? O primeiro passo deveria ser sempre remover o desnecessário das tabelas! Ou seja, escolher as colunas que serão necessárias para trabalhar. Ah, mas Leonardo, eu vou precisar de tudo! Será mesmo? Duvido que realmente precise de todas as colunas de todas as tabelas. Pelo menos, geralmente não é assim! Você deve remover, principalmente, a informação que não agrega na análise. Precisar de tudo, normalmente é um mito. Lembre sempre da visão performática e otimização na sua análise

Dica:
Uma maneira que ajuda bastante para fazer esse processo corretamente é marcar as colunas necessárias! Isso é diferente de desmarcar as colunas que não precisa, pois força você a escolher uma a uma quais irão para a análise.

Figura 8: Seleção de Colunas para o Modelo

Comandos:
VendasCabecalho > Página Inicial > Escolher Colunas > Seleção (“NFe, DataEmissao, cdCliente, cdVendedor, Valor Total”)
Cliente > Página Inicial > Escolher Colunas > Seleção (“cdCliente, RazaoSocial, Status”)

Nesse caso, a “tipagem” dos dados das tabelas foi feita automaticamente e de forma correta. Então, com isso podemos fechar e aplicar! Assim, estaremos fazendo a carga dos dados para a memória da máquina. Aliás, esse é um dos motivos do BI ser rápido.

Análise Inicial

Com tudo pronto, vamos começar a análise? Primeiramente, vamos criar a medida de faturamento e criar um cartão e um gráfico para visualização.

Figura 9: Medida Faturamento

Comando: Faturamento  = SUM (VendasCabecalho[Valor Total])

Figura 10: Cartão e Gráfico (Data e Faturamento)

Formatação:
Cartão:  Campos > Faturamento
Gráfico de Colunas Empilhadas:  Eixo > Data
Valores > Faturamento
Rótulos de Dados > Ligado

Vamos analisar a imagem. O que vocês acham dos dados? Os valores de faturamento estão iguais para todos os anos, certo? Porque isso aconteceu?  Se você respondeu que as datas que colocamos no eixo do gráfico não estão relacionadas com as datas do faturamento, você acertou! Vamos ao modelo fazer os relacionamentos entre as tabelas e visualizar as informações após o relacionamento.

Figura 11: Relacionamentos do Modelo

Figura 12: Gráfico após Relacionamento do Modelo

Dica:
Após carregar os dados, é importante ir ao modelo e verificar como estão os relacionamentos das tabelas. Lembre-se de verificar se os relacionamentos estão tipo estrela e verificar se existem relacionamentos: “ambos” e “muitos para muitos”. Nesses casos, deve-se ter atenção e avaliar se tem sentido manter dessa maneira. É importante que você crie uma forma de organizar as tabelas para fácil visualização.

Integração de Bases

Imagine agora que temos que vincular os faturamentos dessa base (vendas locais) com o faturamento de outra base de dados (vendas online). Porém, as informações de vendas online não estão no banco de dados do SQL que utilizamos. Além disso, não temos nessa tabela de vendas online a informação de código de cliente (“Cod Cliente”) com os valores exatos nem do código de vendedor (“Usuário”) para fazermos o relacionamento. Já passou por uma situação dessas? O que fazer?

Figura 13: Tabela Vendas Online

Vamos importar o arquivo via One Drive, ou seja, um caminho da web dessa tabela. Para isso temos que “subir” o arquivo para rede. Com o arquivo na nuvem, vamos abrir no Excel da máquina (isso quer dizer que vamos rodar o arquivo que está na nuvem no PC local).

Dica:
Se você não sabe subir os arquivos na nuvem, acesse esse link com a explicação no site da Microsoft: https://support.office.com/pt-br/article/carregar-fotos-e-arquivos-no-onedrive-b00ad3fe-6643-4b16-9212-de00ef02b586

Figura 14: Copiando caminho do Arquivo One Drive

Vamos importar para o Power BI essa tabela. Mas, qual fonte vou utilizar em “Obter dados”? Excel ou Web? Arquivo ou o caminho? Vamos utilizar o caminho da nuvem (lembre-se de retirar o termo “?web=1” do final do link copiado) quando importar e vou explicar o motivo no fim desse artigo.

Figura 15: Obtendo Dados do One Drive

Após a importação das tabelas, vamos transformar os dados. Vamos retirar o passo inicial de promoção de cabeçalho e tipo alterado. Depois de excluídos esse dois passos, vamos na coluna 2 remover vazios. Então, vamos promover os cabeçalhos e alterar os tipos dos dados.

Veja que na coluna de cliente, as informações não tem o mesmo padrão do nosso código de cliente. O que fazer? Analisando as colunas vemos que temos que extrair o código (os números) da coluna Cod Cliente da tabela faturamento online. Para isso, vamos transformar a coluna extraindo esses números para fazer o relacionamento.

Figura 16: Extração do Código de Cliente

Agora, vamos analisar o usuário. Temos o e-mail, mas não temos o código. Pense assim, se a TI não liberasse importar o email para nossa base de dados, o que fazer? Podemos utilizar o “De –> Para”. E para isso utilizamos uma tabela de apoio. Não conseguimos mesclar as informações sem fazer isso, pois ainda não temos a informação de e-mail nem do código nas duas tabelas para fazer esse relacionamento. Lembre-se, muitas vezes será necessário ter uma tabela de apoio (“De – > Para”) que traz a informação DE uma fonte PARA outra fonte.  Outra opção é utilizar uma coluna de exemplos. Porém, temos que ter cuidado, pois se alterar o padrão da tabela perde-se todo o processo.

Vamos lá, é algo bem simples. Primeiro, vamos subir nossa tabela de apoio para o One Drive como fizemos para a tabela de vendas online (se não sabe como, utilize a dica do artigo… já já você fica fera nisso) e importar pelo caminho como fizemos com a tabela de vendas online.

Normalmente, a tabela “De -> Para” fica com a carga desabilitada no modelo. Utilizamos a informação dessa base mesclando com outra consulta que e a tabela de interesse. É preferível isso a fazer o relacionamento utilizando “Mesclar” no Power Query do que carregar a informação para o modelo (é muito parecido com o “PROCV” do Excel e “JOIN” do SQL).

Figura 17: Mesclando Tabela de Venda Online e De -> Para

O nível de privacidade irá aparecer, porque existe uma troca de informação entre fontes sendo necessária a avaliação de confidencialidade. É importante avaliarmos essa questão na rotina do meio corporativo. Mas, neste caso iremos ignorar!

A tabela “De – > Para” vem completa, mas queremos somente a informação do código de vendedor para vendas online. Então, retiramos a coluna “Usuário” (pois não é necessária), fazemos a tipagem dos dados e desabilitamos a carga da tabela “De -> Para”. Lembrar que o código do vendedor e o do cliente devem estar iguais nas tabelas fato e dimensão para possibilitar o relacionamento. Lembre-se também que ela continua sendo atualizada, porém não será carregada para o modelo.

Figura 18: Seleção de Colunas para Expandir

Figura 19: Desabilitando Carga da Tabela De -> Para

Importante:
Organização no desenvolvimento é muito importante e para isso é interessante identificar as tabelas fatos e dimensão no Power Query. Usualmente identificamos com o prefixo “f” as tabelas fatos e com “d” as tabelas dimensão, como podem ver na imagem acima.

Com todas as informações, fechamos e aplicamos o Power Query e vamos para o modelo fazer todos os relacionamentos das tabelas.

Figura 20: Relacionamento da Tabela de Vendas Online no Modelo

Bom, temos todos os relacionamentos e informações de vendas agora. Isso quer dizer que a análise está pronta? Não, temos que somar essas vendas online. Ela contribui para a medida de faturamento total. Para isso, vamos criar mais duas medidas e alterar a medida que já criamos. Assim, ficaremos com 3 medias: faturamento total, faturamento local e faturamento online. Para finalizar, vamos criar mais um gráfico de colunas empilhadas para comparar o faturamento total com os outros dois.

Figura 21: Medidas – Faturamento Local, Online e Total

Medidas:
Fat Local  = SUM (fVendasLocais[ValorTotal])
Fat Online  = SUM (fVendasOnline[Valor Venda])
Faturamento = [Fat Local] + [Fat Online]

Figura 22: Gráficos com as Medidas Criadas

Formatação:
Gráfico de Colunas Empilhadas:  Eixo > Data
Valores > Fat Local e Fat Online
Rótulos de Dados > Ligado

Bom, análise pronta! Finalizado? E se atualizarmos os arquivos, tanto do banco de dados quanto os que estão na nuvem? O que acontece? O arquivo continua sendo atualizado. Um grande ponto de atenção é manter a padronização dos nomes e informações dentro do arquivo. Outro ponto é lembrar que as informações tem que estar atualizadas na nuvem e devemos, após essa atualização, também atualizar o arquivo pbix.

Data Flow

No artigo, mencionei que importamos os dados da tabela de vendas e da tabela De -> Para por um motivo. Já pensou o que pode ser? Fizemos isso, pois quando vamos utilizar essa informação no Data Flow (fluxo de dados) no serviço online evitamos a gateway!

E porque usar o fluxo de dados? Imagine que você precisa em mais de um de seus projetos da tabela de vendas online (já prontinha, com todas as transformações feitas). E aí, imagina ter que realizar todo o trabalho de novo? Perdemos tempo, certo? Aí vem uma das vantagens do fluxo de dados. Em 2019 a Microsoft lançou esse recurso que funciona como se fosse um Power Query online e é um local onde podemos centralizar a informação. Muito parecido com um Data Lake ou um Data Warehouse!

Vamos entrar no serviço online do Power BI para visualizar melhor! Para a utilização do fluxo de dados você deve estar em um ambiente de workspace pro. Temos que ir em “Novo” e em “Fluxo de Dados” em um desses workspaces para criarmos.

Figura 23: Criando Fluxo de Dados no Power BI Serviço

Na próxima janela, clique em adicionar nova entidade.

Figura 24: Adicionando Novas Entidades

A próxima janela é bem parecida com o ambiente “Obter dados” do Power BI e tem as mesmas opções.

Figura 25: Obtendo Dados do SQL Server no Power BI Serviço

Nesse caso, o banco de dados SQL que vamos importar está na minha máquina (localhost), por isso preciso de um gateway para subir a informação.

Figura 26: Configurando Conexão

Vamos em importação, escolher as tabelas que queremos trabalhar e em transformar dados. Veja que são as mesmas telas e mesmo processo antes do ambiente Power Query do Power BI Desktop. O que acontece é que nesse caso as informações são salvas em arquivos CSV na Azure como uma camada a mais no processo do modelo. Essa camada não atrapalha a performance do modelo e funciona muito bem!

Figura 27: Escolhendo tabelas para Importação

Figura 28: Visualização do Power Query no serviço de Fluxo de Dados

Figura 29: Salvando Fluxo de Dados

Atenção após finalizar a criação do fluxo de dados, pois irá aparecer a opção de agendar a atualização do fluxo de dados! Vou explicar essa etapa no fim do artigo com mais detalhes.

Figura 30: Seleção para Atualização de Dados do Fluxo

Vamos abrir um novo arquivo no BI e obter dados desses fluxos que acabamos de criar. Aqui vai ser necessário conectar na conta organizacional. Após conectar com sua conta,  mostra os fluxos criados para os workspaces do serviço online. Vamos importar as mesmas informações do nosso modelo de vendas. Veja que podemos tratar novamente essa informação que vieram do fluxo de dados do Power BI…como falamos anteriormente é como se fosse mais uma “camada” no processo.

Figura 31: Obtendo Dados do Fluxo de Dados

Figura 32: Conectando a Conta Organizacional

Figura 33: Seleção de Tabelas para Transformação de Dados no Power Query

Figura 34: Visualização de Tabelas para Transformação de Dados no Power Query

Bom, mas como fica a parte de atualização no serviço online utilizando o data flows? Há etapas sequenciais para fazer a atualização:

1. Programar a atualização do fluxo de dados no serviço online;
2. Programar a atualização do conjunto de dados no serviço online lembrando que o horário agendado deve ser posterior ao da atualização do fluxo de dados e deve-se levar em consideração o tempo que demora a atualização do fluxo de dados para não haver sobreposição (também pode ser agendada 8 vezes ao dia);
3. O modelo é atualizado automaticamente com o agendamento do conjunto de dados.

Figura 35: Seleção para Atualização do Fluxo de Dados no Power BI Serviço

Figura 36: Configuração de Atualização do Fluxo de Dados

Voltando para a pergunta: por que utilizamos o One Drive para nossos arquivos de Excel da tabela de vendas online e a tabela De -> Para? Porque quando utilizamos um arquivo Online não precisamos configurar gateway! Isso ajuda bastante na atualização!!

Vamos publicar esse novo modelo que acabamos de criar no Power BI Desktop. Coloquei um cartão para não ir totalmente em branco.

Figura 37: Publicação do Modelo

O processo até agora foi:

1. Criar o fluxo de dados no Data Warehouse do Power BI;
2. Conectar os dados no modelo;
3. Publicar para o relatório Online.

Agora temos a vantagem do gateway! Não precisamos do gateway para atualizar o conjunto de dados, pois ele vem do fluxo de dados, ou seja, já tem uma conectividade do serviço online. Além disso, os  arquivos de vendas online e tabela De -> Para estão no One Drive, ou seja, também não precisa do gateway. Então, atualizamos o conjunto de dados.

Figura 38: Atualização do Conjunto de Dados

Dica:
Vou abordar com mais detalhes o tema de fluxo de dados em uma das próximas Lives, fique atento! No link https://docs.microsoft.com/pt-br/power-bi/transform-model/service-dataflows-create-use da Microsoft tem bem detalhado também o processo de criação e uso dessa ferramenta.

Acredito que acompanhando todas as etapas e dicas do artigo você terá entendido sobre o que é Data Warehouse e como visualizar um banco de dados. Também que tenha o conhecimento para obter dados de diferentes bases para o Power BI e se necessário como fazer a integração entre elas. Por fim, espero que tenha compreendido como funciona o data flow (fluxo de dados) do Power BI.

Se ficou com alguma dúvida ou tenha outro assunto que queira um conteúdo como esse, entre em contato!

Espero ter ajudado!

Leonardo.

Leia também

Assista agora o Minicurso gratuito de Power BI:

Publicações recentes:
Assuntos: