Uso de Stored Procedures no Power BI

Muitos alunos e empresas tem me questionado se é possível acessar Stored Procedures pelo Power BI! Então, resolvi escrever este post para ilustrar o passo a passo, seja com ou sem parâmetro.

O Power BI é uma ferramenta fantástica para criar relatórios interativos, aceita praticamente tudo o que você imagina de fonte de dados, então, como vários clientes tem o SQL Server como fonte, os quais já existem Stored Procedures criadas, nada mais justo que acessá-las e exibir dados.

Banco de Dados SQL Server
Para este exemplo, instalei o banco de dados Northwind (disponível em https://www.microsoft.com/en-us/download/details.aspx?id=23654 ou https://github.com/cjlee/northwind) que já contém diversas SPs, é fácil de instalar, basta baixar o Backup e fazer o Restore no SQL Server.

Stored Procedure sem parâmetro
Crie um novo projeto no Power BI, selecione o menu Obter Dados / SQL Server. Na janela aberta é preciso informar o nome do servidor, o banco de dados, o tipo de conexão (Importar) e a dica está na instrução SQL, onde basta escrever a palavra chave EXEC seguido do nome da SP, cofnorme a seguir, neste caso usarei a [Ten Most Expensive Products].

EXEC [Ten Most Expensive Products]

Atenção que o modo DirectQuery não suporta Stored Procedure.

Fig 1 — Login no banco com a SP

Clique no botão OK e aguarde o Power BI carregar os dados.

Veja a seguir o código da Stored Procedure o qual retorna 10 linhas com 2 campos da tabela Products, ordenados pelo preço.

ALTER procedure [dbo].[Ten Most Expensive Products] AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

Fig 2 — Dados oriundos da SP

Você poderia clicar no botão Carregar e pronto, mas clique no Editar para que seja aberto o Editor de Consultas. Aqui você pode renomear de Consulta1 para Top10Produtos, visualizar os tipos de dados (alfanumérico, decimal, data, etc) e arrumar, caso precise e ainda saber como que é escrito o comando para acessar a SP:

= Sql.Database(“EARTH”, “NORTHWND”, [Query=”EXEC [Ten Most Expensive Products]”])

Fig 3 — Editor de Consultas

Clique no botão Fechar e Aplicar para efetivar as alterações. No relatório, adicione um título, uma tabela e vincule os 2 campos listados na tabela Top10Produtos. Claro que você pode renomear para facilitar, eu deixei no original para que você visualize melhor, a título de didática apenas.

Fig 4 — Produtos listados da SP

Pronto e rápido, agora basta você simular a mesma coisa na sua aplicação.

Stored Procedure COM parâmetros
Quando temos parâmetros na SP é preciso um procedimento no Power BI de forma a criar os parâmetros antes de invocar a SP. Veja qual SP iremos chamar, note que há 2 parâmetros (Beginning_Date e Ending_Date) do tipo Datetime. Como resultado trará uma listagem dos pedidos dentro de um certo período de datas.

ALTER procedure [dbo].[Sales by Year]
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, “Order Subtotals”.Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN “Order Subtotals” ON Orders.OrderID = “Order Subtotals”.OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

No Power BI, a primeira coisa a fazer é clicar no botão Editar Consultas. Na janela aberta, selecione Gerenciar Parâmetros / Novo. Crie o primeiro parâmetro conforme a figura a seguir, com o nome de DtInicial, Tipo = Data, Valor Atual 01–01–1996 (esta data é antiga porque os dados no banco já existem). Repita o mesmo passo criando o parâmetro chamado DtFinal com Valor Atual de 31–12–1996.

Fig 5 — Novo parâmetro da consulta

É importante definir corretamente o tipo do parâmetro, pois o banco de dados espere um dados daquele tipo, se é Data use Data, se é Número Decimal, Data/Hora, Texto, etc use o respectivo tipo.

Em seguida, clique em Aplicar e Fechar para retornar ao relatório. Clique no botão Obter Dados / SQL Server, informe o servidor, banco e veja como que é escrito a execução da chamada da Stored Procedure com parâmetro, conforme figura a seguir.

Fig 6 — EXEC SP com parâmetro

Clique no botão OK para carregar os dados. Caso houver alguma incompatibilidade de tipo de dado no parâmetro ou falta dele, será exibida a mensagem a seguir com um erro. Lembre-se que neste instante, o T-SQL vai no banco e executa a consulta.

Fig 7 — Erro na execução do T-SQL

O motivo do erro foi a ordem do dia e mês na data final, a expressão EXEC [Sales by Year] ‘1/1/1996’, ‘31/12/1996’ não foi aceita, então altere para EXEC [Sales by Year] ‘1/1/1996’, ‘12/31/1996’ e pronto.

Clique no botão Editar para ajustar o nome da consulta (para PedidosPeriodo) e o tipo de campo para Data no ShippedData.

Fig 8 — Tipo de campo

Ao final, a fonte da consulta ficou:

= Sql.Database(“EARTH”, “NORTHWND”, [Query=”EXEC [Sales by Year] ‘1/1/1996’, ‘12/31/1996’”])

Basta montar a listagem do jeito que quiser, e eu ainda adicionei um Slicer baseado no campo data ShippedData para que o usuário possa escolher a data que quiser.

Fig 9 — SP com parâmetro

Conclusão
O uso de Stored Procedures no banco de dados facilita o processamento porquê toda a carga é processada no banco em si. No Caso do Power BI acessando as SPs, ele jogará tudo no modo Import, ou seja, os dados ficarão na memória para que você possa aplicar filtros. Pelo menos não terá que reescrever as SPs em views.

Boa sorte, sucesso nos projetos e se precisar treinar o seu time, fale comigo rehaddad@msn.com