Introduction
Hey guys!
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:
|
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 |
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:
simulating the problem
Clique aqui para visualizar este conteúdo
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.
Solṳ̣o #1 РOpenRowSet
Clique aqui para visualizar este conteúdo|
1 |
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:
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:
|
1 2 3 4 5 6 7 8 9 |
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:
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:
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!
Solṳ̣o #2 РUtilizando Table-Valued Function
Clique aqui para visualizar este conteúdoSegue 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.
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
And that's it, folks!
Espero que tenham gostado dessa dica, que isso seja útil pra você em alguma necessidade especÃfica e até a próxima!
Portuguese 
English
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!