SQL Server e Power BI: Como carregar dados de Stored Procedure no SQL Server com DirectQuery
Visualizações: 6.548 views
Neste artigo
Tempo de Leitura: 7minutos
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.
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')
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
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!