Introdução

Fala pessoal!

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 🙂

Caso você queira aprender mais sobre o DirectQuery do Power BI, veja esse conteúdo aqui.

Esse é o código da Stored Procedure que eu gostaria de executar como DirectQuery no Power BI:

CREATE OR ALTER PROCEDURE dbo.stpPBI_DirectQuery_Procedure
AS
BEGIN
    

    IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP TABLE #Tabelas
    SELECT *
    INTO #Tabelas
    FROM sys.tables
    WHERE is_ms_shipped = 0


    IF (OBJECT_ID('tempdb..#Tabelas_Internas') IS NOT NULL) DROP TABLE #Tabelas_Internas
    CREATE TABLE #Tabelas_Internas ( 
        [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  
        is_replicated = 0
    ORDER BY
        [name]


END

Resultado esperado da execução:

Simulando o problema

Clique aqui para visualizar este conteúdo
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.


Solução #1 – OpenRowSet

Clique aqui para visualizar este conteúdo
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):

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:

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!


Solução #2 – Utilizando Table-Valued Function

Clique aqui para visualizar este conteúdo
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:

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.

Limitações

O uso de Stored Procedures ou funções para conectar a dados com DirectQuery tem algumas limitações as quais posso destacar:

  • Não é possível passar parâmetros dinâmicos para Stored Procedure ou Table-valued function dinamicamente
  • Ao utilizar um slicer, por exemplo, os dados são executados no banco de dados para depois filtrar os dados retornados utilizando os slicers, portanto, isso talvez gere problemas de performance em tabelas muito grandes
  • Diferente do relatório paginado, que recalcula todo o conjunto de dados após interações com os usuários, o Power BI não atualiza os dados na fonte a cada filtro aplicado ou interação no DirectQuery
  • As 2 soluções vão apresentar problemas de performance em grandes volumes de dados
  • As 2 técnicas apresentadas exigem um certo conhecimento em T-SQL e só funciona no SQL Server. Talvez isso seja um pouco complexo de trabalhar para um usuário de negócio que não é de TI
  • O DirectQuery por si só, já possui algumas limitações que pode você entrar acessando esse link aqui

É isso aí, pessoal!
Espero que tenham gostado dessa dica, que isso seja útil pra você em alguma necessidade específica e até a próxima!