Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Como utilizar o PowerShell e a API com o Agent ou SSIS para automatizar a atualização de um conjunto de dados do Power BI

Post Views 3,331 views
Reading time 7 minutes

Introduction

Hey guys!
Nesse artigo que estou compartilhando com vocês, gostaria de ajudá-los num problema muito comum de quem trabalha com BI e precisa atualizar os dados de relatórios do Power BI automaticamente mais de uma vez por dia. Geralmente eu vejo muitos cenários onde o time de BI cria a rotina automática para atualizar os dados no banco de dados, configurada para ser executada em horários específicos, e depois agenda a atualização desses dados no serviço do Power BI em um horário onde provavelmente os dados já foram atualizados pela rotina do ETL/DW.

E aí quando a rotina demora mais tempo que o esperado, seus dados ficam sem sincronismo e realmente, manter esse sincronismo entre os horários de execução da rotina do banco e a atualização no Power BI é uma tarefa muito complicada.

Não seria BEM MELHOR se tivesse a possibilidade da própria rotina que faz a carga e processamento dos dados também iniciar a atualização dos conjuntos de dados do Power BI? Chegou a hora de aprender como fazer isso 🙂

A dica desse artigo se refere a conjuntos de dados onde o modo de armazenamento é o Import, que copia os dados da fonte e os leva para o serviço do Power BI. Caso você esteja utilizando o DirectQuery, não há necessidade de automatizar a atualização dos dados, pois a consulta é feita dinamicamente na fonte de dados. Assim como a atualização agendada do Power BI só pode atualizar automaticamente um conjunto de dados 8 vezes por dia na conta Pro (e 48 vezes na Premium), a atualização por demanda também está dentro desse mesmo limite
A atualização dos dados utilizando a API do Power BI funciona apenas se o seu usuário for administrador do Workspace. Caso contrário, o script irá apresentar uma mensagem de erro ao ser executado.
Caso esse script não esteja funcionando e apresentando a mensagem de erro “Connect-PowerBIServiceAccount : Failed to populate environments in settings” ao tentar conectar no serviço do Power BI, dê uma lida no artigo Connect-PowerBIServiceAccount : Failed to populate environments in settings

Instalando os cmdlets do Power BI

Clique aqui para visualizar o conteúdo
O primeiro passo para conseguir interagir com o serviço do Power BI através do Powershell é instalar os cmdlets que facilitam a comunicação com a API do serviço. Além da atualização do conjunto de dados, é possível realizar várias automatizações e controles utilizando esse recurso, conforme o Rafael Mendonça, um dos maiores especialistas em Power BI do mundo, nos mostra no artigo [POWER BI] – Cmdlets para PowerShell – Administrando o Power Bi like a Pro!.

Abra o Powershell (ISE ou pelo Prompt de comando) como Administrador:

Na tela do Prompt de Comando, digite o comando “powershell” para inicializar a interface do PowerShell.

Após isso, digite o comando “Install-Module MicrosoftPowerBIMgmt” para instalar os Cmdlets. Provavelmente aparecerá uma confirmação se você confia nesse repositório. Marque a opção “Sim para todos”:

Após isso, os cmdlets já estão instalados e podemos começar a utilizá-los. Caso você queira utilizar a interface do PowerShell ISE para instalar os cmdlets, fique à vontade. A instalação segue os mesmos passos. Caso queira saber mais sobre esses cmdlets e mais alguns detalhes sobre a instalação como a instalação de módulos separadamente e também exemplos utilizando o PowerShell ISE, recomendo a leitura do artigo [POWER BI] – Cmdlets para PowerShell – Administrando o Power Bi like a Pro!.

Caso o seu PowerShell acuse erro ao tentar executar o comando Install-Module, é porque sua máquina ainda está utilizando o PowerShell 4.0 ou anterior. Se esse é o seu caso, você precisará instalar o Windows Management Framework 5.1 para adicionar esse módulo ao seu PowerShell.

Criando o script Powershell para atualizar os dados

Clique aqui para visualizar o conteúdo
No nosso script de atualização dos dados iremos utilizar os seguintes métodos:

  • Connect-PowerBIServiceAccount: Serve para realizar a conexão com o serviço do Power BI utilizando o usuário e senha dessa conta do Power BI
  • Invoke-PowerBIRestMethod: Utilizando para realizar uma chamada à API do Power BI que irá executar determinadas ações de acordo com os parâmetros utilizados

Logo abaixo, vou disponibilizar um script para atualização de um dataset específico:

Para identificar o ID do Workspace e do conjunto de dados (Dataset), basta acessar o menu lateral do Power BI, abrir o workspace onde está esse conjunto de dados e depois clicar no dataset que você deseja atualizar:

Ao abrir o conjunto de dados, você poderá obter o ID do Workspace e do conjunto de dados a partir da URL:

Substitua o script que disponibilizei por esses 2 ID’s:

Caso você queira atualizar mais de um dataset no script, basta fazer assim:

Após realizar essa alteração, salve o script com o nome que você desejar, mas a extensão do arquivo deve ser .ps1.

Como executar o script Powershell pelo SQL Server Agent

Clique aqui para visualizar o conteúdo
Depois de criar o script de atualização, agora basta executá-lo a partir de alguma ferramenta para automatizar essa execução. Essa ferramenta por ser o SQL Server Agent, Agendador de Tarefas do Windows, SQL Server Integration Services (SSIS), Pentaho ou qualquer outra ferramenta que consiga executar comandos a nível de sistema operacional. Nesse artigo vou demonstrar como fazer isso direto por um job do SQL Server Agent e dentro de um pacote do SSIS.

O ideal é que esse processo de atualização do Power BI fique no mesmo job que atualiza e processa os dados no banco:

E nesse novo step, que será do tipo “Operating system (CmdExec)”, você colocará o seguinte comando para executar:
powershell.exe -ExecutionPolicy Unrestricted -File "C:\Scripts\Atualiza Power BI.ps1"

O switch -ExecutionPolicy serve para que o Powershell consiga executar o script em um contexto de execução sem qualquer restrição que possa causar algum erro durante o processamento do script.

Lembre-se de alterar o caminho do arquivo .ps1 para o local onde você salvou o seu script. Após agendar o job, seu relatório Power BI já estará sendo atualizado automaticamente ao final do processamento.

O usuário que roda o serviço do SQL Agent precisa ter as permissões necessárias para executar esse script Powershell. Caso seja uma conta de serviço padrão, você pode alterar a conta do SQL Agent para um usuário em que você consiga logar e instalar os cmdlets ou pode também utilizar o comando Install-Module MicrosoftPowerBIMgmt -Force -Scope AllUsers (executar como Administrador) para que todos os usuários, inclusive as contas locais, consigam utilizar os cmdlets do Power BI

Como executar o script Powershell pelo Integration Services (SSIS)

Clique aqui para visualizar o conteúdo
Além do SQL Server Agent, você pode também executar o script PowerShell direto pelo SSIS para atualizar os dados dos seus relatórios.

Para fazer isso, adicione uma tarefa de “Executar Processo”, que é utilizada para executar comandos a nível de sistema operacional:

Na tela de configuração dessa tarefa, digite powershell.exe no parâmetro “Executable” e -ExecutionPolicy Unrestricted -File “C:\Scripts\Atualiza Power BI.ps1” no parâmetro “Arguments”. Opcionalmente, altere a configuração “Window Style” para “Hidden”, para que a janela não fique aparecendo na tela durante a atualização:

Exemplo:

Caso seu package do SSIS já esteja sendo executado automaticamente pelo SQL Server Agent, não precisa fazer mais nada. Caso contrário, adicione no seu job do SQL Server Agent mais um step para a execução do package que irá fazer a atualização do Power BI:

Quando o job for executado, os dados do Power BI serão atualizados ao final da execução

O usuário que roda o serviço do SQL Agent precisa ter as permissões necessárias para executar esse script Powershell

Conclusão

Utilizando esse script Powershell que compartilhei no artigo, agora você poderá atualizar automaticamente os dados do Power BI logo após o processamento dos dados no banco de dados, sem a necessidade de tentar sincronizar o horário que o processamento dos dados acaba com o horário que o agendamento da atualização do Power BI inicia lá no serviço.

Exemplo de atualização automática utilizando Powershell:

Vale lembrar novamente que as atualizações utilizando a API continuam dentro do limite de 8 atualizações diárias da conta Pro (e 48 na conta Premium). Ou seja, utilizem com sabedoria.. rs

Espero que vocês tenham gostado dessa dica e até a próxima!