How to use SQL Server stored procedures in Microsoft PowerBI?

Question

I want to generate reports from my SQL Server tables.

I have some already made stored procedures that I would like to use to generate reports from.

I haven't found a way to do so.

Only by rewriting the queries.

Thanks :)

Pollute

Check this community.powerbi.com/t5/Desktop/… – Churchill

Answer 1

To execute your SP in Power BI-->

1.In SQL Server right click on your SP and select Execute. Your code is executed and a new query window opens up which was responsible for execution. Copy that Query.

2.In Power BI Query Editor, select New Source-->SQL Server. After giving the server and database, in the same window click on "Advanced Options", paste the query in the "SQL Statement" that opened up. Check "Navigate using full hierarchy" and click OK.

3.You will see data for the parameters you passed in SP only.

4. On Applying these changes, you will see the dataset for this in Power BI Desktop from where you can create reports.

NOTE: This works in"Import Query" option.

Hope this works for you as it did for me, Cheers!

Trillbee

This answer is still showing how to use a sproc- it is pasting in the 'exec' statement for running the sproc, not the contents of the sproc. – Symmetrical

This can't pass dynamic variables to the procedure but at least this answer is the best one to be found on the web so far – Lyrate

what is SP? This way work with changing from Direct query to Import – Spoondrift

Answer 2

You can use openquery. The following syntax will work with both import data and direct query methods in Power BI Desktop.

SELECT *
FROM OPENQUERY ([server name],
'EXEC dbname.dbo.spname @parametername = ''R1''');

Kobarid

Hey niraj, I keep getting an error when executing with OPENQUERY. "Microsoft SQL: 'OPENQUERY' rowset provider not supported in this version of SQL Server." – Pollute

which version of sql server you are using – Kobarid

It's 2021 and it's still not supported in Azure SQL server. Any ideas? I'm receiving the same error as @AsafSavich – Roundabout

I also get this error when trying to DirectQuery Azure SQL, "Microsoft SQL: 'OPENQUERY' rowset provider not supported in this version of SQL Server." – Internist

Answer 3

Expand the Advanced options in the Get Data -> SQL Server dialog, and write a SQL Statement to execute your stored procedure such as:

EXEC [dbo].[usp_NameOfYourStoredProcedure]

Move

On Apply Changes the following error occurs: /!\ "Query name" Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'. any way to get around this? – Veneaux

This is what I ended up doing, and it worked on Azure SQL. I had to switch to Import mode. These documents helped me ensure my use case would be adequately supported in Import mode: Direct Query vs Import community.powerbi.com/t5/Desktop/direct-query-vs-import/m-p/… and Configuring Import Scheduled Caching learn.microsoft.com/en-us/power-bi/connect-data/… – Internist

Answer 4

Firstly I think its best to make the point out that Power BI isn't Reporting Services, it expects to be given existing table data or views that you then model within it's own environment before creating your dashboards.

If you can get Power BI to do the work of your stored procedures. Once you have your data model defined within Power BI it can be reused.

There is a great introduction course for this on the Power BI website:

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-1-intro-modeling-data/

Warden

I just want to point out that it's much easier to edit a large stored procedure in SQL versus trying to modify what Power BI has done to your query, which looks absolutely ridiculous. – Archducal

Answer 5

"Hi,

1. In an Excel workbook, open the ""Power Query"" tab.
2. Then choose the “From Database” drop down button and select “From SQL Server Database”
3. Fill in the Server and Database textboxes and click OK
4. In the Navigator window, double-click the desired table
5. In the Query Editor window, click on to the column to be displayed.
6. Click close and select the ""Load"" button
7. Connect to stored procedures.
8. Create another power window and fill in the server and database textboxes.
9. In the SQL statement textbox, type “EXECUTE  Procedure_Name”
10. Click Close and Load button "

Kaylakayle

How is Excel related to Power BI? – Refined

Power BI is an extension of the work started in PowerPivot and PowerView. – Dyandyana