Recently I wrote about things you can’t do on the Power platform when querying an on-premises SQL database. Specifically, I wanted to recreate views in a database that gets wiped and recreated every day. Somehow the back of my mind wouldn’t let go so I did some more research for a workaround and found the following:
- You can run a stored procedure as a native query in a Power BI dataset or dataflow – even if it’s not returning data – by generating a fake row of data as explained here. The actual stored procedure is executed on the SQL server while Power Query is happy because “must consume data num-num.”
- You can create a view using a stored procedure using dynamic SQL as shown here.
- You can create views in another database as explained here.
By combining the above, I was able to create a native SQL query in Power Query that triggers a stored procedure in database B (the one which is persisted) which then creates a view in database A (the one which is recreated anew once a day). That view is now available for querying, which was the whole point. And this is refreshable in the Power BI service like any regular query.
Then I found that you can’t create indexed views when the query refers to a derived table, so I can’t use this for my intended use case. So close yet so far!
This must be one of the hackiest things you can do with Power BI and there are plenty of good reasons not to do it, what with the risks of running native queries that create objects in a database. Also there are very few reasons why you would want to do it, though I do think my use case is legit, even if a bit stretched (that never stopped me before).
Do NOT use this on a production server, in my specific case it is safe to use but again, don’t sue me if you get fired/excommunicated/dumped by your significant other for doing this. If you want more commonly-used knowledge to improve Power BI performance from SQL sources, read this entry.
Edit: you can execute native queries from Power Query like described here and shown in the video below: