Power BI Get Data (Obter Dados)

Uma coisa que a Microsoft fez na prova DA-100 é trabalhar com cenários, contextos. Eu já vi várias pessoas falando que as duas últimas perguntas na prova são a interpretação de 2 cenários grandes onde vc tem que identificar o que está acontecendo e dar as melhores respostas.

Pensa num cenário como da imagem abaixo, onde uma empresa tem variadas e diferentes fontes de dados:

Beleza, o que esse cenário mostra é que precisamos buscar dados de várias fontes de dados diferentes. Vou mostrar aqui pra vocês então como que podemos fazer isso, mas pra isso vou continuar usando os cenários (pq eles são importantes para prova DA-100) do learning path.

Obter dados de arquivos

A Microsoft considera “dados de arquivos” o que vem de txt, csv, excel, json, xml, pdf.

EXCEL

No cenário o cara diz o seguinte “(…) dados de funcionários da organização, como nome do funcionário, data de contratação, cargo e gerente.” Com base nisso, criei um excel assim:

Salvei esse arquivo excel no meu computador em “D:\Arquivos do Pedro – 2020 07 03\Blog PowerBI Expert\Posts”. Pra obter esses dados no Power BI só ir em Obter dados e informar o caminho.

Daí vc precisa escolher o que vc quer carregar, no meu caso vai ser Table1 pq eh o nome da tabela que eu criei lá. E aí vc pode escolher LOAD ou TRANSFORM DATA.

Se vc escolher LOAD, o arquivo vai carregar direto no seu power bi.

Se vc escolher TRANSFORM DATA, o power bi vai abrir o Power Query pra vc fazer as modificações que desejar no seu aquivo.

Vou escolher LOAD. Beleza, tá carregado:

Seu eu importar um arquivo CSV ou TXT vai ser basicamente a mesma coisa, tem alguns pontos diferentes, mas teste aí no seu pc pra vc ver, não tem mistério.

O que eu quero que vc preste atenção aqui é o seguinte. Quando vc escolher arquivos que estão dentro do seu computador, ou seja, arquivos locais no seu PC, esses arquivos não serão atualizados automaticamente quando vc enviar esse report pro Power BI Service. Pra que esses arquivos fiquem atualizados vc precisa de um Gateway, mas não vou falar de gateway aqui, isso é assunto pra um outro post.

• As pastas de trabalho devem ser salvas como os tipos de arquivo .xlsx ou .xlsm e ter menos de 1 GB
• O Power BI dá suporte a todas as propriedades de modelo de dados como relações, medidas, hierarquias e KPIs.
• Fontes de dados externas configuradas no Excel são automaticamente importadas para o Power BI quando vc importa seu excel no power bi

Para mais info sobre importar excel no power bi: https://docs.microsoft.com/pt-br/power-bi/connect-data/service-excel-workbook-files

Arquivos armazenados em diferentes locais

Seu aquivo pode estar diretamente no seu computador, mas pode estar em outros lugares também, como no sharepoint ou no onedrive. O lugar em que esse arquivo está muda a forma como o power bi se comunica com ele. Se liga:

Local – o arquivo está no seu pc, ou na rede dentro da sua empresa. Vc consegue importar de boa e se precisar atualizar no Power BI Service é só configurar um gateway.

OneDrive – Business – Se seu arquivo estiver no OneDrive Business ele estará diretamente conectado com o Power BI, então se vc publicar um report que vc criou no Power BI Desktop e que esteja usando um arquivo do OneDrive Business, esse arquivo vai ser automaticamente atualizado a cada hora (vc pode mudar esse tempo, e pra funcionar tem q colocar as credenciais na configuração do dataset, mas não precisa do gateway).

OneDrive – Personal – Mesma coisa do onedrive Business, mas pra funcionar da mesma forma vc tem que ligar o “Mantenha-me conectado”.

SharePoint – Sites de Equipe – Quase a mesma coisa que o Onedrive Busines, diferença aqui é que vc se conecta usando o conector do sharepoint online ou web (aqui, diferente do onedrive, vc vai precisar do gateway funcionando também pra manter sua fonte de dados atualizada no Power BI Online Service).

SHAREPOINT ONLINE LIST

Então beleza, vc já viu aí que eu importei um arquivo do excel, na verdade uma tabela de dentro do meu excel, agora vou importar uma tabela do meu Sharepoint, olha só:

Vai aparecer uma telinha pra colocar o endereço, eu vou colocar tipo https://meushp.sharepoint.com/sites/NomedaMinhaLista, daí ele vai me dar uma tela pra escolher dentro desse site o que eu quero, ou seja, qual tabela que eu quero importar:

Feito isso os dados serão importados pra dentro do power bi e já pode começar a tratar ou trabalhar com eles.

JSON

JSON também é considerado como arquivo, só que ele não pode ser importado direto, ele tem que ser sempre transformado antes. Porque o JSON vem como se fosse uma linha só e em cada linha, em cada célula, tem um monte de dados.

NOTA: como JSON e XML precisam ser tratados antes de serem consumidos, só dá pra dar “get data” nesse tipo de arquivo ou no power bi desktop ou no dataflows… Direto pelo Power BI Online Service não funciona.

Quando vc carregar o JSON o Power BI já vai te levar direto pro Power Query, daí lá vc faz a mágica. Eu nunca trabalhei muito com JSON, mas pelos testes que fiz aqui vc tem que converter ele pra tabela e depois sair expandindo as linhas, que nem na imagem abaixo.

Depois que vc expandir as linhas, deve ficar tipo essa img abaixo:

Alterar origem dos dados

Perceba que até agora só falamos de importação de arquivo, mas a lógica de qualquer tipo de obtenção de dados (get data) do Power BI é essa. Continua lendo que eu vou falar de obter dados de banco de dados, mas antes eu quero te mostrar uma coisa.

Toda e qualquer fonte de dados pode ser alterada. É possível alterar a fonte de dados de 3 maneiras: nas configurações de fontes de dados do power bi, e dentro do power query vc pode alterar também usando o “configuração da consulta” ou o editor avançado.

Imagem 1, no power bi:

Imagem 2, no power query, em data source settings

Imagem 3, no power query, no editor avançado:

Claro que pode dar problema e dar uma porrada de erros quando vc tenta fazer alterações de fontes de dados, então antes de sair trocando suas fontes de dados, faz um backup do seu pbix.

Erros na alteração da fonte de dados estão muito relacionados aos nomes dos campos importados, se o power bi não encontrar o campo esperado, ele vai dar erro. Daí a única forma de resolver é verificando os “applied steps” no power query ou as vezes ajustando direto na sua fonte de dados.

Obtendo dados de um banco de dados

Obter dados de bancos de dados, aqui estamos falando de bancos de dados on-premise… O que isso quer dizer? On-premises é quando o banco tá no seu domínio, ou seja, não está na cloud.

Então pra dar Get Data num banco SQL basta selecionar a opção, e informar os dados pra se conectar no banco. Aqui no meu caso eu tenho um SQL Server Developer Edition instalado no meu pc, pra eu conectar nele preciso passar o server name e mais algumas coisas, antes olha essa imagem:

Veja que eu já informei ali meu server name, e posso também informar database como opcional, mas nesse caso aqui eu não vou informar. Posso selecionar também se é tipo Import ou DirectQuery (depois falo mais disso) e ainda posso colocar ali naquela caixona ali o SQL Statement, que é um código SQL mesmo, tipo “select * from minhaTabela”.

Vou fazer o mais simples aqui, vou só colocar o nome do server e dar OK, como IMPORT mesmo.

Como eu ainda não coloquei minhas credenciais, o Power BI me pergunta qual usuário e senha eu quero pra conectar nesse banco.

Nesse meu caso aqui vou usar essa autenticação de Windows mesmo, mas eu poderia usar outra, a depender de como eu me autentico no banco.

Blz, me conectei, daí o Power BI me mostra essa tela que são as tabelas que eu tenho, vou selecionar TICKETS e dar LOAD e pronto, com isso o Power BI já carregou os dados pra mim (lembrando que esse post é sobre carga de dados, depois em outro post vamos falar sobre tratamento dos dados).

Escrever uma instrução SQL 

Também é possível, ao invés de vc selecionar a tabela, como fizemos no exemplo acima, vc pode informar o SQL mesmo, o seu SELECT, direto na hora de carregar os dados e, com isso, o power bi nem vai te pedir pra selecionar nada, ele já vai te carregar exatamente o que vc passou na query. Só que, se vc quiser usar o código, o SELECT direto ali no editor avançado, então é obrigatório informar a “database” também, ok?

Então, por exemplo, vai que tua tabela tem uma porrada de registros de vários anos anteriores e vc só precisa dos últimos 3 anos… Nesse caso melhor do que selecionar a tabela é mandar um código SQL já com o filtro where, tipo “where ano > 2017”. Isso vai deixar sua consulta e carga de dados mais rápida.

Boas práticas:

• Não use o caractere *, ou seja, não faça select * from… Sempre informe apenas os campos que vc precisa. Quanto menos campos, mais performático seu report
• Sempre utilize a cláusula WHERE para filtrar seus dados. Evite trazer informação que não precisa.
• O mundo ideal é escrever suas queries e criar uma VIEW no banco de dados e usar o Power BI para consumir a view, dessa forma vc garante que seu Query Folding funcione. Query Folding garante maior performance para o seu report.

Obter dados do Azure Analysis Services

O Azure Analysis Services é a base do Power BI, na verdade não o Azure Analysis Services, apenas o Analysis Services, mas então, o Power BI se comunica muito bem com AS.

Caso precise obter dados do AS vc tem a opção de Import e de LiveConnection. Uma coisa aqui que vc precisa saber é que, se no seu report já tem outras fontes de dados então não será possível se conectar como AS, ele é assim, exclusivo. Por isso, uma das sugestões da Microsoft é vc levar tudo pro AS e conectar seu Power BI direto lá.

Quando vc se conecta no AS vc ainda pode criar measures, mas elas ficam apenas no report, elas não são efetivamente escritdas no AS, porém, os cubos do AS importados no Power BI podem já conter measures criadas.

As diferenças notáveis entre os cubos do Azure Analysis Services e o SQL Server são:

• Se você não precisar de uma tabela inteira, poderá consultar os dados diretamente. Em vez de usar a T-SQL (Transact-SQL) para consultar os dados, como você faria no SQL Server, você pode usar MDX (expressões MDX) ou DAX (Data Analysis Expressions).
• Depois de conectar no AS o botão Obter Dados fica desabilitado no Power BI Desktop.

Conectar em tempo real é uma nova opção no Azure Analysis Services. O Azure Analysis Services usa o modelo de tabela e o DAX para criar cálculos, semelhante ao Power BI. Esses modelos são compatíveis entre si. O uso da opção Conectar em tempo real ajuda a manter os dados e os cálculos de DAX em seu local original, sem a necessidade de importá-los para o Power BI. O Azure Analysis Services pode ter um agendamento de atualização rápida, o que significa que, quando os dados forem atualizados no serviço, os relatórios do Power BI serão imediatamente atualizados, sem a necessidade de iniciar um agendamento de atualização do Power BI.

Selecionar um modo de armazenamento

A maneira mais popular de usar dados no Power BI é importá-los para um conjunto de dados do Power BI. No entanto, essa abordagem pode não funcionar para todas as organizações.

O Power BI resolve esses problemas usando o modo de armazenamento DirectQuery, que permite consultar os dados diretamente na fonte de dados e não importar uma cópia para o Power BI. O DirectQuery é útil porque garante que você esteja sempre exibindo a versão mais recente dos dados.

Os três tipos diferentes de modos de armazenamento que podem ser escolhidos:

• Importação
• DirectQuery
• Duplo (composto)

Você pode acessar os modos de armazenamento alternando para a exibição Modelo, selecionando uma tabela de dados e, no painel Propriedades resultante, selecionando qual modo da lista suspensa Modo de armazenamento você deseja usar, conforme mostrado no visual a seguir.

Modo de importação 

O modo de Importação permite que você crie uma cópia local do Power BI de seus conjuntos de dados provenientes da sua fonte de dados. Você pode usar todos os recursos de serviço do Power BI com esse modo de armazenamento, incluindo Q&A e Insights Rápidos. No entanto, as atualizações de dados devem ser feitas manualmente dentro do Power BI Desktop (ou agendada no Power BI Service). O modo de Importação é o padrão para a criação de relatórios do Power BI.

Modo DirectQuery 

A opção DirectQuery é útil quando você não deseja salvar cópias locais de seus dados, pois seus dados não serão armazenados em cache. Em vez disso, você pode consultar as tabelas específicas que serão necessárias usando consultas nativas do Power BI e os dados necessários serão recuperados da fonte de dados subjacente. Essencialmente, você está criando uma conexão direta com a fonte de dados. O uso desse modelo garante que você sempre veja os dados mais atualizados e que todos os requisitos de segurança sejam satisfeitos. Além disso, esse modo é adequado para quando você tem grandes conjuntos de dados para efetuar pull. Em vez de reduzir o desempenho, tendo que carregar grandes quantidades de dados no Power BI, você pode usar o DirectQuery para criar uma conexão com a fonte, resolvendo problemas de latência de dados também.   

Duplo (modo composto) 

No modo Duplo, você pode identificar alguns dados a serem importados diretamente e outros dados que devem ser consultados. Qualquer tabela que é colocada em seu relatório é um produto dos modos de Importação e DirectQuery. O uso do modo Duplo permite que Power BI escolha a forma mais eficiente na recuperação de dados.

Get Data do Power Plataform

Além de tudo que já falamos acima também é possível obter dados diretamente de datasets do Power BI, ou de DataFlows e de Common Data Services (CDS).

Conectar a Datasets é bem simples, mas é como conectar num Analysis Services, ou seja, é uma Live Connection e só funciona se vc não tiver mais nada já previamente conectado no seu report. Essa opção é muito útil quando existe um dataset único na empresa e várias pessoas querem usar ele pra criar seus próprios reports, ou seja, eh um dataset jah tratado, pronto pra ser utilizado.

Dataflows e CDS devo falar em outro post, são assuntos mais extensos.