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:
|
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:
Simulando o problema
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.
SoluĂ§Ă£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!
SoluĂ§Ă£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
É 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!
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!