CODEWITHSUNDEEP
powerbi
John Connolly
John Connolly

Reputation: 39

Run a SQL Stored procedure from Power BI using Direct Query

Is there a way to call a SQL server stored procedure from Power BI? Do you have to use Import Query mode only? I need the visual based on the SQLstored procedure to refresh though!! Can you do this with Query Direct Mode? Please help!

Upvotes: 1

Views: 2921

Answers (2)

Wolfmeister
Wolfmeister

Reputation: 11

You can use a multi-statement table-valued function and select from that instead. This works in direct query mode (on the May 24 PBI update). I got this to work by embedding the SELECT statement into the 'SQL Statement' field under the Advanced options of the SQL Server database query source, e.g.

SELECT FROM dbo.TableValuedFunction()

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

It's not possible.

From Microsoft's Documentation:

Limited data transformations

Similarly, there are limitations in the data transformations that can be applied within Query Editor. With imported data, a sophisticated set of transformations can easily be applied to clean and reshape the data before using it to create visuals, such as parsing JSON documents, or pivoting data from a column to a row form. Those transformations are more limited in DirectQuery.

First, when connecting to an OLAP source like SAP Business Warehouse, no transformations can be defined at all, and the entire external model is taken from the source. For relational sources, like SQL Server, it's still possible to define a set of transformations per query, but those transformations are limited for performance reasons.

Any such transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they're limited to those transformations that can reasonably be translated into a single native query. If you use a transformation that is too complex, you receive an error that either it must be deleted or the model switched to import.

Additionally, the query that results from the Get Data dialog or Query Editor will be used in a subselect within the queries generated and sent to retrieve the necessary data for a visual. The query defined in Query Editor must be valid within this context. In particular, it's not possible to use a query using Common Table Expressions, nor one that invokes Stored Procedures.

Upvotes: 1

Related Questions