Nesse post eu gostaria de compartilhar com vocês uma situação que aconteceu comigo durante uma consultoria a um cliente, onde ele tinha uma necessidade de ter um relatório do Power BI conectado à uma base de dados SQL Server, mas a fonte de dados era uma Stored Procedure que fazia várias transformações de dados internamente e retornava um conjunto de dados.
O problema é que esse cliente queria que a atualização dos dados fosse em tempo real, ou seja, abriu o relatório, aplicou um filtro ou fez uma interação com o gráfico, os dados teriam que ser atualizados através da execução dessa Stored Procedure. E acontece que o Power BI NÃO tem suporte à isso 🙂
Esse é o código da Stored Procedure que eu gostaria de executar como DirectQuery no Power BI:
O primeiro passo para tentar executar uma procedure com DirectQuery é selecionar esse tipo de armazenamento, digitar o nome do servidor e clicar no botão OK para carregar a lista de objetos:
Na listagem dos objetos, podemos observar que ele só lista tabelas, views e funções:

Vou voltar para a tela de conexão e tentar inserir o comando de execução manualmente na tela de instrução SQL:

Mas ao tentar fazer isso, é retornada uma mensagem de erro:

Vamos tentar de novo, sem colocar a instrução EXEC:

Opa! Parece que carregou!

Parecia que ia funcionar, mas ao tentar carregar os dados..

O PowerQuery até mostra os dados do banco, mas não me deixa carregar para o modelo, mostrando a mensagem do print anterior.

A primeira “solução” que vou apresentar pra vocês, é a utilização do OpenRowSet para executar a procedure no servidor local e retornar os dados. Como essa acaba sendo uma instrução SELECT, o Power BI deve aceitar a execução:
|
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'EXEC eventos.dbo.stpPBI_DirectQuery_Procedure') |
O primeiro ponto a ser considerado, é essa mensagem de erro abaixo:
Msg 15281, Level 16, State 1, Line 43
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
Essa mensagem nada mais representa que o recurso de OpenRowset/OpenDatasource está desativado na instância (por padrão, essa configuração vem desativada devido à motivos de segurança). Para utilizar o Openrowset, você precisará habilitar esse recurso, e o seu DBA provavelmente não irá gostar isso:
|
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO |
Uma vez habilitado, agora basta executar o nosso comando de SELECT. E nos deparamos com a 2ª mensagem de erro:
Msg 11526, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 53]
The metadata could not be determined because statement ‘INSERT INTO #Tabelas_Internas
VALUES(965578478, ‘Tabela de Confirmados’), (1013578649, ‘Tabela’ in procedure ‘stpPBI_DirectQuery_Procedure’ uses a temp table.
Agora o SQL Server está reclamando das tabelas temporárias utilizadas na Stored Procedure. Vamos fazer algumas alterações na procedure e substituir as tabelas temporárias (#tabela) por variáveis do tipo tabela (@tabela):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
|
ALTER PROCEDURE dbo.stpPBI_DirectQuery_Procedure AS BEGIN DECLARE @Tabelas TABLE ( [object_id] INT, [name] VARCHAR(100), create_date DATETIME, lock_escalation_desc VARCHAR(100), temporal_type_desc VARCHAR(100), is_replicated BIT ) INSERT INTO @Tabelas SELECT A.[object_id], A.[name], A.create_date, A.lock_escalation_desc, A.temporal_type_desc, A.is_replicated FROM sys.tables A WHERE is_ms_shipped = 0 DECLARE @Tabelas_Internas TABLE ( [object_id] INT, [Descricao] VARCHAR(100) ) INSERT INTO @Tabelas_Internas VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados') SELECT A.[object_id], A.[name], A.create_date, A.lock_escalation_desc, A.temporal_type_desc, B.Descricao FROM @Tabelas A LEFT JOIN @Tabelas_Internas B ON B.[object_id] = A.[object_id] WHERE A.is_replicated = 0 ORDER BY [name] END |
E vamos tentar novamente. Outra mensagem de erro:
Msg 7357, Level 16, State 1, Line 54
Cannot process the object “EXEC eventos.dbo.stpPBI_DirectQuery_Procedure”. The OLE DB provider “SQLNCLI11” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.
Para resolver esse problema, vamos incluir a instrução “SET NOCOUNT ON” no começo da Stored Procedure, para que o comando de OPENROWSET consiga identificar corretamente os dados retornados e não sofra interferências com o retorno da quantidade de linhas retornadas:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
ALTER PROCEDURE dbo.stpPBI_DirectQuery_Procedure AS BEGIN SET NOCOUNT ON DECLARE @Tabelas TABLE ( [object_id] INT, [name] VARCHAR(100), create_date DATETIME, lock_escalation_desc VARCHAR(100), temporal_type_desc VARCHAR(100), is_replicated BIT ) INSERT INTO @Tabelas SELECT A.[object_id], A.[name], A.create_date, A.lock_escalation_desc, A.temporal_type_desc, A.is_replicated FROM sys.tables A WHERE is_ms_shipped = 0 DECLARE @Tabelas_Internas TABLE ( [object_id] INT, [Descricao] VARCHAR(100) ) INSERT INTO @Tabelas_Internas VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados') SELECT A.[object_id], A.[name], A.create_date, A.lock_escalation_desc, A.temporal_type_desc, B.Descricao FROM @Tabelas A LEFT JOIN @Tabelas_Internas B ON B.[object_id] = A.[object_id] WHERE A.is_replicated = 0 ORDER BY [name] END |
Com essa alteração, o nosso comando de SELECT foi executado corretamente:

Vamos voltar para o Power BI, repetir todo o processo e agora podemos carregar os dados normalmente pra dentro do Power BI:

E está lá! Estamos consumindo uma Stored Procedure usando DirectQuery no Power BI!

A segunda forma de conseguir consultar objetos complexos utilizando DirectQuery no Power BI, é substituindo a Stored Procedure por uma Table-Valued Function, que acaba sendo uma forma bem mais elegante e correta de realizar essa consulta do que a forma anterior.
Segue o código da função, que realiza exatamente a mesma coisa da Stored Procedure anterior:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
|
CREATE FUNCTION dbo.fncPBI_DirectQuery_Procedure() RETURNS @Retorno TABLE ( [object_id] INT, [name] VARCHAR(100), create_date DATETIME, lock_escalation_desc VARCHAR(100), temporal_type_desc VARCHAR(100), descricao varchar(200) ) AS BEGIN DECLARE @Tabelas TABLE ( [object_id] INT, [name] VARCHAR(100), create_date DATETIME, lock_escalation_desc VARCHAR(100), temporal_type_desc VARCHAR(100), is_replicated BIT ) INSERT INTO @Tabelas SELECT A.[object_id], A.[name], A.create_date, A.lock_escalation_desc, A.temporal_type_desc, A.is_replicated FROM sys.tables A WHERE is_ms_shipped = 0 DECLARE @Tabelas_Internas TABLE ( [object_id] INT, [Descricao] VARCHAR(100) ) INSERT INTO @Tabelas_Internas VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados'), (338100245, 'Tabela Interna de Log') INSERT INTO @Retorno SELECT A.[object_id], A.[name], A.create_date, A.lock_escalation_desc, A.temporal_type_desc, B.Descricao FROM @Tabelas A LEFT JOIN @Tabelas_Internas B ON B.[object_id] = A.[object_id] WHERE A.is_replicated = 0 ORDER BY [name] RETURN END |
Após a criação da função, selecione a opção “Obter Dados” > “SQL Server” e escreva o comando de SELECT para ler os dados da função:

Após clicar no botão de “OK”, seus dados estarão sendo carregados normalmente:

Basta clicar no já conhecido botão “Fechar e Aplicar” e a nossa tarefa está feita.
O uso de Stored Procedures ou funções para conectar a dados com DirectQuery tem algumas limitações as quais posso destacar:
Trabalho com BI e fazer a transformação dos dados direto no BD não é correto. Essa transformação deve ser feita na ferramenta de BI, no máximo em uma view.
William, eu trabalho com BI há 8 anos e concordaria com você se a gente tivesse falando de um cenário de Processamento em Batch, o que não é o caso, já que estamos utilizando importação de dados utilizando DirectQuery no Power BI para trazer dados em tempo real, numa abordagem mista entre Batch e Streaming, sem usar ferramentas de Big Data (cliente não queria investir nisso).
Em cenários como esses, o processamento em Batch utilizando ferramenta de ETL não atende, porque você precisaria ter uma carga de BI rodando a cada 1 segundo na base de produção: Duvido muito que você conseguiria rodar esse fluxo e fazer todas as transformações necessárias dentro de 1s e além disso, o DBA iria barrar sua carga em menos de 1h, quando ele percebesse a sobrecarga na base de produção.. rs
Como o requisito do cliente era visualizar dados em tempo real quando ele alterasse um filtro (mix entre Batch e Streaming) e precisava fazer várias transformações (que já rodavam em uma SP no SSRS), achei melhor seguir essa abordagem, porque a ideia era substituir esse relatório do SSRS para o Power BI, mantendo as mesmas propriedades da consulta ser em tempo real.
Um outro ponto legal desse artigo, foi me desafiar e entregar uma solução que, na teoria, a ferramenta não tem suporte (burlando o sistema.. kkkk) e que dependendo do cenário, especialmente para casos em tempo real e que a massa de dados é pequena e o número de consultas é baixo, pode ser exatamente a solução que o cliente precisa.
Lembre-se que nosso papel como consultor, é entregar a melhor solução possível dentro dos requisitos e orçamento do cliente, e não a solução perfeita do ponto de vista técnico.
Abraços!