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 🙂
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:
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:
Transact-SQL
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:
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):
Transact-SQL
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
ALTERPROCEDUREdbo.stpPBI_DirectQuery_Procedure
AS
BEGIN
DECLARE@TabelasTABLE(
[object_id]INT,
[name]VARCHAR(100),
create_dateDATETIME,
lock_escalation_descVARCHAR(100),
temporal_type_descVARCHAR(100),
is_replicatedBIT
)
INSERTINTO@Tabelas
SELECT
A.[object_id],
A.[name],
A.create_date,
A.lock_escalation_desc,
A.temporal_type_desc,
A.is_replicated
FROM
sys.tablesA
WHERE
is_ms_shipped=0
DECLARE@Tabelas_InternasTABLE(
[object_id]INT,
[Descricao]VARCHAR(100)
)
INSERTINTO@Tabelas_Internas
VALUES(965578478,'Tabela de Confirmados'),(1013578649,'Tabela de Casos Recuperados')
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:
Transact-SQL
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
ALTERPROCEDUREdbo.stpPBI_DirectQuery_Procedure
AS
BEGIN
SETNOCOUNTON
DECLARE@TabelasTABLE(
[object_id]INT,
[name]VARCHAR(100),
create_dateDATETIME,
lock_escalation_descVARCHAR(100),
temporal_type_descVARCHAR(100),
is_replicatedBIT
)
INSERTINTO@Tabelas
SELECT
A.[object_id],
A.[name],
A.create_date,
A.lock_escalation_desc,
A.temporal_type_desc,
A.is_replicated
FROM
sys.tablesA
WHERE
is_ms_shipped=0
DECLARE@Tabelas_InternasTABLE(
[object_id]INT,
[Descricao]VARCHAR(100)
)
INSERTINTO@Tabelas_Internas
VALUES(965578478,'Tabela de Confirmados'),(1013578649,'Tabela de Casos Recuperados')
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:
Transact-SQL
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
CREATEFUNCTIONdbo.fncPBI_DirectQuery_Procedure()
RETURNS@RetornoTABLE(
[object_id]INT,
[name]VARCHAR(100),
create_dateDATETIME,
lock_escalation_descVARCHAR(100),
temporal_type_descVARCHAR(100),
descricaovarchar(200)
)
AS
BEGIN
DECLARE@TabelasTABLE(
[object_id]INT,
[name]VARCHAR(100),
create_dateDATETIME,
lock_escalation_descVARCHAR(100),
temporal_type_descVARCHAR(100),
is_replicatedBIT
)
INSERTINTO@Tabelas
SELECT
A.[object_id],
A.[name],
A.create_date,
A.lock_escalation_desc,
A.temporal_type_desc,
A.is_replicated
FROM
sys.tablesA
WHERE
is_ms_shipped=0
DECLARE@Tabelas_InternasTABLE(
[object_id]INT,
[Descricao]VARCHAR(100)
)
INSERTINTO@Tabelas_Internas
VALUES(965578478,'Tabela de Confirmados'),(1013578649,'Tabela de Casos Recuperados'),(338100245,'Tabela Interna de Log')
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!
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!