Introduction
Speak guys!
In this post I would like to share with you a situation that happened to me during a consultation with a client, where he had a need to have a Power BI report connected to a SQL Server database, but the data source was a Stored Procedure which did various data transformations internally and returned a set of data.
The problem is that this client wanted the data to be updated in real time, that is, he opened the report, applied a filter or made an interaction with the graph, the data would have to be updated through the execution of this Stored Procedure. And it turns out that Power BI NO support this isso
If you want to learn more about Power BI's DirectQuery, see this content here.
This is the code for the Stored Procedure that I would like to run as DirectQuery in 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 |
Expected result of execution:
Simulating the problem
Click here to view this content
In the list of objects, we can see that it only lists tables, views and functions:
I will go back to the connection screen and try to enter the execution command manually in the SQL statement screen:
But when trying to do this, an error message is returned:
Let's try again, without placing the EXEC statement:
Oops! Looks like it loaded!
It looked like it would work, but when trying to load the data ..
PowerQuery even shows the bank's data, but it doesn't let me load it into the model, showing the message from the previous print.
Solution # 1 - OpenRowSet
Click here to view this content| 1 | SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'EXEC eventos.dbo.stpPBI_DirectQuery_Procedure') |
The first point to consider is this error message below:
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.
This message no longer represents that the OpenRowset / OpenDatasource feature is disabled on the instance (by default, this setting is disabled for security reasons). To use Openrowset, you will need to enable this feature, and your DBA will probably not like this:
| 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 |
Once enabled, now just execute our SELECT command. And we come across the 2nd error message:
VALUES (965578478, 'Confirmed Table'), (1013578649, 'Table' in procedure 'stpPBI_DirectQuery_Procedure' uses a temp table.
SQL Server is now complaining about the temporary tables used in the Stored Procedure. Let's make some changes to the procedure and replace the temporary tables (#tabela) with table type variables (@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 |
And we will try again. Another error message:
Cannot process the object “EXEC events.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.
To solve this problem, we will include the statement “SET NOCOUNT ON” at the beginning of the Stored Procedure, so that the OPENROWSET command is able to correctly identify the returned data and does not suffer any interference with the return of the number of rows returned:
| 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 |
With this change, our SELECT command was executed correctly:
Let's go back to Power BI, repeat the whole process and now we can load the data normally into Power BI:
And there it is! We are consuming a Stored Procedure using DirectQuery in Power BI!
Solution # 2 - Using Table-Valued Function
Click here to view this contentHere is the function code, which does exactly the same thing as the previous Stored Procedure:
| 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 |
After creating the function, select the option “Get Data”> “SQL Server” and write the SELECT command to read the function's data:
After clicking on the “OK” button, your data will be loading normally:
Just click on the well-known “Close and Apply” button and our task is done.
Limitations
The use of Stored Procedures or functions to connect to data with DirectQuery has some limitations which I can highlight:
- It is not possible to pass dynamic parameters to Stored Procedure or Table-valued function dynamically
- When using a slicer, for example, the data is executed in the database to later filter the data returned using the slicers, so this may cause performance problems in very large tables
- Unlike the paged report, which recalculates the entire data set after interactions with users, Power BI does not update the data at the source for each applied filter or interaction in DirectQuery
- The 2 solutions will present performance problems in large volumes of data
- The 2 techniques presented require a certain knowledge in T-SQL and only works on SQL Server. Maybe this is a little complex to work for a non-IT business user
- DirectQuery by itself, already has some limitations that you can enter accessing this link here
That's it folks!
I hope you enjoyed this tip, that it will be useful for you in any specific need and until next time!
Working with BI and doing the data transformation directly in the DB is not correct. This transformation must be done in the BI tool, at most in one view.
William, I have been working with BI for 8 years and would agree with you if we were talking about a Batch Processing scenario, which is not the case, since we are using data import using DirectQuery in Power BI to bring data in time real, in a mixed approach between Batch and Streaming, without using Big Data tools (client didn't want to invest in it).
In scenarios like these, Batch processing using the ETL tool is not enough, because you would need to have a BI load running every 1 second in the production base: I very much doubt that you would be able to run this flow and make all the necessary transformations within 1s and in addition, the DBA would bar his load in less than 1h, when he realized the overload in the production base .. lol
As the client's requirement was to view data in real time when he changed a filter (mix between Batch and Streaming) and needed to make several transformations (which already ran in a SP in SSRS), I thought it best to follow this approach, because the idea was to replace this SSRS report for Power BI, keeping the same query properties in real time.
Another cool point of this article was to challenge me and deliver a solution that, in theory, the tool is not supported (bypassing the system .. kkkk) and that depending on the scenario, especially for real-time cases and that the mass of data it is small and the number of consultations is low, it may be exactly the solution that the client needs.
Remember that our role as a consultant is to deliver the best possible solution within the client's requirements and budget, and not the perfect solution from a technical point of view.
Hugs!