SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

Views: 508
Reading Time: 7 minutes

Introduction

Hey 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 CAN'T 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:

Expected result of execution:

Simulating the problem

Click here to view this content
The first step in trying to execute a procedure with DirectQuery is to select this type of storage, type the name of the server and click the OK button to load the list of objects:

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
The first “solution” that I will present to you, is the use of OpenRowSet to execute the procedure on the local server and return the data. Since this turns out to be a SELECT statement, Power BI must accept execution:

The first point to consider is this error message below:

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.

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:

Once enabled, now just execute our SELECT command. And we come across the 2nd error message:

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, '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):

And we will try again. Another error message:

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.

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:

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 content
The second way to be able to query complex objects using DirectQuery in Power BI, is to replace the Stored Procedure with a Table-Valued Function, which ends up being a much more elegant and correct way to perform this query than the previous one.

Here is the function code, which does exactly the same thing as the previous Stored Procedure:

After creating the role, select the option “Get Data”> “SQL Server” and write the SELECT command to read the role data:

After clicking on the “OK” button, your data will be loading normally:

Just click on the familiar “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!