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 🙂
Instalando os cmdlets do Power BI
Clique aqui para visualizar o conteúdoAbra 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!.
Criando o script Powershell para atualizar os dados
Clique aqui para visualizar o conteúdo- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#1. Autenticação $Senha = "senha_dificil_123" $SenhaSegura = ConvertTo-SecureString $Senha -AsPlainText -Force $Credencial = New-Object System.Management.Automation.PSCredential($Usuario,$SenhaSegura) Connect-PowerBIServiceAccount -Credential $Credencial #2. Recupera o ID do Workspace e do Dataset $Workspace_ID = "8fda9bda-13c5-45ec-9a4f-3bcfe51ed57f" $Dataset_ID = "fd3df53a-9a3a-40ee-91f0-585c9fb52c67" #3. Monta a URL de atualização do DS $Url_Atualizacao = 'groups/' + $Workspace_ID + '/datasets/' + $Dataset_ID + '/refreshes' #5. Executa a atualização e notifica por e-mail em caso de falha $NotificacaoFalhaPorEmail = @{"notifyOption"="MailOnFailure"} Invoke-PowerBIRestMethod -Url $Url_Atualizacao -Method Post -Body $NotificacaoFalhaPorEmail |
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:
|
1 2 3 |
#2. Recupera o ID do Workspace e do Dataset $Workspace_ID = "ee72886a-26c8-4d59-97f6-e7b811adc507" $Dataset_ID = "3aaf8f29-f54b-4b9c-a171-d8d0ef2fcea7" |
Caso você queira atualizar mais de um dataset no script, basta fazer assim:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#2. Recupera o ID do Workspace e do Dataset $Workspace_ID = "8fda9bda-13c5-45ec-9a4f-3bcfe51ed57f" #3. Monta a URL de atualização do DS $Url_Atualizacao1 = 'groups/' + $Workspace_ID + '/datasets/fd3df53a-9a3a-40ee-91f0-585c9fb52c67/refreshes' $Url_Atualizacao2 = 'groups/' + $Workspace_ID + '/datasets/fd3df53a-9a3a-40ee-91f0-449e4c453c13/refreshes' $Url_Atualizacao3 = 'groups/' + $Workspace_ID + '/datasets/fd3df53a-9a3a-40ee-91f0-fef43a0bc123/refreshes' #5. Executa a atualização e notifica por e-mail em caso de falha $NotificacaoFalhaPorEmail = @{"notifyOption"="MailOnFailure"} Invoke-PowerBIRestMethod -Url $Url_Atualizacao1 -Method Post -Body $NotificacaoFalhaPorEmail Invoke-PowerBIRestMethod -Url $Url_Atualizacao2 -Method Post -Body $NotificacaoFalhaPorEmail Invoke-PowerBIRestMethod -Url $Url_Atualizacao3 -Method Post -Body $NotificacaoFalhaPorEmail |
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údoO 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"
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.
Como executar o script Powershell pelo Integration Services (SSIS)
Clique aqui para visualizar o conteúdoPara 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
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!
Portuguese 
English
Olá Dirceu, estou querendo fazer com executar um script em ps dentro do agente do sql. Meu cenário, faço BKP por um script onde lê de uma tabela depois executo o script logo após chamo o agente de cada servidor “tenho 3” onde cada um lê o que precisa restaurar o BKP na sua instancia, porem a parte de executar o script do powershell parece não estar executando qdo está no agent do sql.
Olá Dirceu bom dia. É possível fazer algo parecido para Power BI Report Server ?
Olá, Dirceu.
Esse processo serve para quem utiliza fluxo de dados no Power BI?
Luciel,
Esse script serve apenas para o conjunto de dados padrão do PowerBI.
O dataflow também possuI API que você pode utilizar com powershell também para atualizar os dados, mas a URL é diferente.
Vou ver se o Rafael vai escrever sobre isso, senão eu crio o artigo.