Executing Native Queries in Power Query

This post is also available in: German

Hits: 10890

Power Query’s access to so-called “native queries” is often denied. In this short article I show how this limitation can be overcome.

If you want to read more about Power BI, subscribe to our newsletter.


The problem

Power BI undercuts the execution of native queries for security reasons to protect data integrity on the source database. Nevertheless, it can make sense for such queries to be executed anyway.

Example

In this example, a user tries to execute a stored procedure. The stored procedure starts an SSIS package and imports data from any source into an SQL table.

Since a corresponding stored procedure already exists in the SQL Server for this use case, it is not clear why the execution of the stored procedure should be prevented in Power Query or Power BI. As my esteemed colleague Pascal rightly stated, the sense of running SSIS packages from Power BI is another story 😉….. (unless the database runs on an SQL Express Server, which as you know does not have the SQL Server Agent jobs, but let’s leave that out……….).

Back to our example. First, we connect in Power BI Desktop with the SQL database on which the SSIS package is executed and enter the execution command for the stored procedure under “SQL statement”:

SQL Query Stored Procedure
SQL Query Stored Procedure

As a result, we get an error message back as expected:

Error message
Error message

Power BI does not like this. But we have a solution – otherwise I wouldn’t write an article about it ….😉

Solution

The SQL statement must be completed as follows:

SELECT 1 as DONE

With this additional row, Power BI does not “notice” that a native query is being executed here, but will return the column “Done” with the value “1” as the result.

ajdusted SQL Statement
adjusted SQL Statement

and…. BINGO!

Result

We have just successfully outwitted Power Query and executed the stored procedure. But did it really work?

Control in the SQL Server Profiler

In the SQL Server we can see that the corresponding stored procedure or SSIS package was actually executed. Open the SQL Server Profiler, run the Power Query again and you should see the following result:

Trace in SQL Profiler
Trace in SQL Profiler

It can be seen that the Mashup engine (aka Power Query) started the stored procedure in the PBI_Demos database and then switched to the SSISDB where the corresponding SSIS package was executed.

Conclusion and hint

As shown, native queries with Power Query can also be used using the workaround. But there are reasons why Microsoft prevents the execution of native queries. Therefore, it is your responsibility as a user to decide whether it makes sense and is permissible to use the described workaround or not. Just don’t punish me as the bearer of the message.

What other methods do you know to run native Queries in Power Query or Power BI? Share your knowledge with us and leave a comment.

You can read more about Power BI here.

Happy Querying!

If you want to read more about Power BI, subscribe to our newsletter.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: