This post is also available in:
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”:
As a result, we get an error message back as expected:
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 DONEWith 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.
and…. BINGO!
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:
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.
